Introduction

1. Introduction

2. Overview of SQL*Plus • Log in to SQL*Plus. • Edit your SQL statement. • Execute SQL from SQL*Plus. • Save SQL statements to files and append SQL statements to files. • Execute saved files.

3. Logging Into SQL*Plus • Use the following username in Lab 60 • User name :scott • Password: csdb • Host string :orcl

4. Logging In to SQL*Plus • From Windows environment:

5. SQL Statements Data retrieval SELECT CREATE ALTER DROP RENAME TRUNCATE INSERT UPDATE DELETE COMMIT ROLLBACK SAVEPOINT GRANT REVOKE Data definition language (DDL) Data manipulation language (DML) Transaction control Data control language (DCL)

6. Writing SQL Statements • SQL statements are not case sensitive. • SQL statements can be on one ormore lines. • Keywords cannot be abbreviated or split across lines. • Tabs and indents are used to enhance readability.

7. Data Definition Language (DDL)

8. Objectives After completing this lesson, you should be able to do the following: • Describe the main database objects • Create tables • Describe the data types that can be used when specifying column definition • Alter table definitions • Drop, rename, and truncate tables

9. Database Objects Object Description Table Basic unit of storage; composed of rows and columns View Logically represents subsets of data from one or more tables Sequence Generates primary key values Index Improves the performance of some queries Synonym Gives alternative names to objects

10. Naming Conventions • Must begin with a letter • Can be 1–30 characters long • Must contain only A–Z, a–z, 0–9, _, $, and # • Must not duplicate the name of another object owned by the same user • Must not be an Oracle Server reserved word

11. Creating Schema

12. Schema • Schema is used to group together tables and other constructs that belong to the same database application. • An SQL schema is identified by a schema name, and includes an authorization to indicate the user or account who owns the schema. CREATE SCHEMA schema AUTHORIZATION user;

13. Creating Tables

14. The CREATE TABLE Statement • You must have : • CREATE TABLE privilege • A storage area • You specify: • Table name • Column name, column datatype, and column size CREATE TABLE [schema.]table (columndatatype [DEFAULT expr];

15. The DEFAULT Option • Specify a default value for a column during an insert. … hiredate DATE DEFAULT SYSDATE, … • Legal values are literal value, expression, or SQL function. • Illegal values are another column’s name or pseudocolumn. • The default datatype must match the column datatype.

16. Creating Tables SQL> CREATE TABLE dept 2 (deptno NUMBER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13)); Table created. • Create the table. • Confirm table creation. SQL> DESCRIBE dept Name Null? Type --------------------------- -------- --------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)

17. Querying the Data Dictionary SQL> SELECT * 2 FROM user_tables; • Describe tables owned by the user. • View distinct object types owned by the user. SQL> SELECT DISTINCT object_type 2 FROM user_objects; • View tables, views, synonyms, and sequences owned by the user. SQL> SELECT * 2 FROM user_catalog;

18. Data types Datatype Description VARCHAR2(size) Variable-length character data CHAR(size) Fixed-length character data NUMBER(p,s) Variable-length numeric data DATE Date and time values LONG Variable-length character data up to 2 gigabytes

19. ALTER TABLE

20. The ALTER TABLE Statement Use the ALTER TABLE statement to: • Add a new column, • Drop existing Column, • Modify an existing column, • Rename existing column.

21. The ALTER TABLE Statement • Add a new column: ALTER TABLE table ADD (column datatype [DEFAULT expr] [, column datatype]...);

22. “…add a newcolumn intoDEPT30 table…” JOB Adding a Column DEPT30 New column EMPNO ENAME ANNSAL HIREDATE ------ ---------- -------- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81 ... JOB DEPT30 EMPNO ENAME ANNSAL HIREDATE ------ ---------- -------- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81 ...

23. Adding a Column • You use the ADD clause to add columns: SQL> ALTER TABLE dept30 2 ADD (job VARCHAR2(9)); Table altered. • The new column becomes the last column. EMPNO ENAME ANNSAL HIREDATE JOB --------- ---------- --------- --------- ---- 7698 BLAKE 34200 01-MAY-81 7654 MARTIN 15000 28-SEP-81 7499 ALLEN 19200 20-FEB-81 7844 TURNER 18000 08-SEP-81 ... 6 rows selected.

24. Modify a Column • Modify an existing column (add a default value, change data type): ALTER TABLE table MODIFY (column datatype [DEFAULT expr] [, column datatype]...);

25. Modifying a Column • Change a column's data type: ALTER TABLE dept30 MODIFY (ename VARCHAR2(15)); Table altered.

26. Rename a Column • Rename existing column: ALTER TABLE table RENAME Column (column to column);

27. Rename a Column You can change a column's name: ALTER TABLE dept30 Rename Column ename to employee_name; Table altered.

28. Drop a Column • Drop existing column: ALTER TABLE table Drop COLUMN column;

29. Drop a Column Drop existing column: ALTER TABLE dept30 Drop column ename; Table altered.

30. Drop Table

31. Dropping a Table • All data and structure in the table is deleted. • All indexes are dropped. • You cannot roll back this statement. SQL> DROP TABLE dept30; Table dropped.

32. Changing ObjectName

33. Changing the Name of an Object • To change the name of a table, view, sequence, or synonym, you execute the RENAME statement. • You must be the owner of the object. SQL> RENAME dept TO department; Table renamed.

34. Truncating Tables

35. Truncating a Table • The TRUNCATE TABLE statement: • Removes all rows from a table • Releases the storage space used by that table • Cannot roll back row removal when using TRUNCATE • Alternatively, remove rows by using the DELETE statement SQL> TRUNCATE TABLE department; Table truncated.

36. Summary Statement Description CREATE TABLE Creates a table ALTER TABLE Modifies table structures DROP TABLE Removes the rows and table structure RENAME Changes the name of a table, view, sequence, or synonym TRUNCATE Removes all rows from a table and releases the storage space

37. Practice Overview • Creating new tables • Creating a new table by using the CREATE TABLE AS syntax • Modifying column definitions • Verifying that the tables exist • Dropping tables • Altering tables

38. Constraints

39. What Are Constraints? • Constraints enforce rules at the table level. • Constraints prevent the deletion of a table if there are dependencies. • The following constraint types are valid in Oracle: • NOT NULL • UNIQUE Key • PRIMARY KEY • FOREIGN KEY • CHECK

40. Constraint Guidelines • Name a constraint or the Oracle Server will generate a name by using the SYS_Cnformat. • Create a constraint: • At the same time as the table is created, • After the table has been created. • Define a constraint at the column or table level. • View a constraint in the data dictionary.

41. CREATE TABLE [schema.]table (columndatatype [DEFAULT expr] [column_constraint], … [table_constraint]); CREATE TABLE emp( empno NUMBER(4), ename VARCHAR2(10), … deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_empno_pk PRIMARY KEY (EMPNO)); Defining Constraints

42. Defining Constraints • Column constraint level • Table constraint level column [CONSTRAINT constraint_name] constraint_type, column,... [CONSTRAINT constraint_name] constraint_type (column, ...),

43. NOT NULL constraint (no row may containa null value forthis column) Absence of NOT NULL constraint (any row can containnull for this column) NOT NULL constraint The NOT NULL Constraint Ensures that null values are not permitted for the column EMP EMPNO ENAME JOB ... COMM DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ...

44. The NOT NULL Constraint Defined at the column level SQL> CREATE TABLE emp( 2 empno NUMBER(4), 3 ename VARCHAR2(10) NOT NULL, 4 job VARCHAR2(9), 5 mgr NUMBER(4), 6 hiredate DATE, 7 sal NUMBER(7,2), 8 comm NUMBER(7,2), 9 deptno NUMBER(7,2) NOT NULL);

45. Insert into Not allowed (DNAME-SALES already exists) 50 SALES DETROIT 60 BOSTON Allowed The UNIQUE Key Constraint UNIQUE key constraint DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

46. The UNIQUE Key Constraint Defined at either the table level or the column level SQL> CREATE TABLE dept( 2 deptno NUMBER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13), 5 CONSTRAINT dept_dname_uk UNIQUE(dname));

47. Insert into Not allowed (DEPTNO-20 already exists) 20 MARKETING DALLAS FINANCE NEW YORK Not allowed(DEPTNO is null) The PRIMARY KEY Constraint PRIMARY KEY DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

48. The PRIMARY KEY Constraint Defined at either the table level or the column level SQL> CREATE TABLE dept( 2 deptno NUMBER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13), 5 CONSTRAINT dept_dname_uk UNIQUE (dname), 6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));

49. Not allowed(DEPTNO-9 does not exist in the DEPT table 7571 FORD MANAGER ... 200 9 7571 FORD MANAGER ... 200 Insert into Allowed The FOREIGN KEY Constraint DEPT PRIMARYKEY DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS ... EMP EMPNO ENAME JOB ... COMM DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 ... FOREIGNKEY

50. The FOREIGN KEY Constraint Defined at either the table level or the column level SQL> CREATE TABLE emp( 2 empno NUMBER(4), 3 ename VARCHAR2(10) NOT NULL, 4 job VARCHAR2(9), 5 mgr NUMBER(4), 6 hiredate DATE, 7 sal NUMBER(7,2), 8 comm NUMBER(7,2), 9 deptno NUMBER(7,2) NOT NULL, 10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) 11 REFERENCES dept (deptno));