Lab2 ddl commands

The second Lab

1. SQL Database Language Lab2: SQL Commands DDL & DML

2. DDL Commands DDL statements are used to build and modify the structure of your tables and other objects in the database. CREATE – Creates objects in the database ALTER – Alters objects of the database DROP – Deletes objects of the database TRUNCATE – Deletes all records from a table and resets table identity to initial value. When you execute a DDL statement, it takes effect immediately .

3. DDL Commands – CREATE TABLE The CREATE TABLE statement is used to create a table in a database. CREATE TABLE   table_name ( column_name1 data_type ( size ) , column_name2 data_type ( size ) , column_name3 data_type ( size ) , .... ); Syntax:

4. DDL Commands – Data Types Data Type Description VARCHAR2( SIZE ) Where  size  is the number of characters to store. Variable-length string. CHAR( SIZE ) Where  size  is the number of characters to store. Fixed-length strings. Space padded. NUMBER( P,S ) Where  p  is the precision and  s  is the scale. For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal. DECIMAL( P,S ) Where  p  is the precision and  s  is the scale. INT Integer value DATE Stores year, month, and day values (DD-MON-YY) TIMESTAMP Stores year, month, day, hour, minute, and second values

5. DDL Commands – Large Objects Data Types Large Objects (LOBs) are a set of data types that are designed to hold large amounts of data. An LOB can hold up to a maximum size ranging from 8 terabytes to 128 terabytes depending on how your database is configured. Storing data in LOBs enables you to access and manipulate the data efficiently in your application. Refer to this site for more information (Oracle Documentation): https:// docs.oracle.com/cd/E18283_01/appdev.112/e18294/adlob_intro.htm

6. DDL Commands – Large Objects Data Types Large Object (LOB) Data Type Description BLOB Binary Large Object Stores any kind of data in binary format . Typically used for multimedia data such as images , audio , and video . CLOB Character Large Object Stores string data in the database character set format. Used for large strings or documents that use the database character set exclusively. Characters in the database character set are in a fixed width format. NCLOB National Character Set Large Object Stores string data in National Character Set format. Used for large strings or documents in the National Character Set. Supports characters of varying width format. BFILE External Binary File A binary file stored outside of the database in the host operating system file system, but accessible from database tables. BFILEs can be accessed from your application on a  read-only  basis. Use BFILEs to store static data , such as image data , that is not manipulated in applications. Refer to this site for more information (Oracle Documentation): https:// docs.oracle.com/cd/E18283_01/appdev.112/e18294/adlob_intro.htm

7. DDL Commands – CREATE TABLE Example on CREATE TABLE :

8. DDL Commands – ALTER TABLE The ALTER TABLE statement is used to add , delete , or modify columns in an existing table . It  is also used to rename a table or a column. To add a column in a table , use the following syntax : ALTER TABLE table_name ADD column_name datatype Syntax: Example:

9. DDL Commands – ALTER TABLE To modify the data type of a column in a table, use the following syntax , also you can modify more than one column at once. ALTER TABLE table_name MODIFY column_name datatype Syntax: ALTER TABLE table_name MODIFY ( column_name1 datatype, column_name2 datatype, ……………….. column_name N datatype ); Syntax: Modify single column Modify multiple columns Example: Example:

10. DDL Commands – ALTER TABLE To delete a column in a table , use the following syntax ALTER TABLE table_name DROP COLUMN column_name Syntax: Example:

11. DDL Commands – ALTER TABLE To rename a column or a table , use the following syntax : ALTER TABLE table_name RENAME COLUMN old_name to new_name ; Syntax: ALTER TABLE table_name RENAME TO new_table_name ; Syntax: Rename a column Rename a table Example: Example:

12. DDL Commands – DROP TABLE   DROP TABLE statement allows you to delete a table from the SQL database . DROP TABLE table_name; Syntax: Example:

13. DDL Commands – Constraints SQL constraints are used to specify rules for the data in a table . If there is any violation between the constraint and the data action, the action is aborted by the constraint . Constraints can be specified when the table is created ( in a CREATE TABLE statement ) or after the table is created ( in an ALTER TABLE statement ).

14. Adding / Deleting Constraints There are two ways of adding constraints: Within table creation Using ALTER query CREATE TABLE student( ID Number(8), CPR Number(9), Name Varchar2(15), GPA Number(3,2), Major Varchar2(5) Not Null , Year INT Default 1, PRIMARY KEY (ID), UNIQUE (CPR) ); Example: CREATE TABLE table_name ( column_name1 data_type(size) constraint_name , column_name2 data_type(size) constraint_name , column_name3 data_type(size) constraint_name , .... ); Syntax:

15. Adding / Deleting Constraints ALTER TABLE student ADD CONSTRAINT student_id_pk PRIMARY KEY (id); ALTER TABLE student ADD CONSTRAINT student_cpr_un UNIQUE ( cpr ); ALTER TABLE student MODIFY major NOT NULL ; ALTER TABLE student MODIFY year DEFAULT 1; Example:

16. Using ALTER query ALTER TABLE table_name DROP CONSTRAINT constraint_name ; Syntax: ALTER TABLE student DROP CONSTRAINT student_id_pk ; Example: Adding / Deleting Constraints

17. Constraints can be enabled or disabled instead of dropping and adding again. Enabling / Disabling Constraints ALTER TABLE table_name DISABLE CONSTRAINT constraint_name ; Syntax: ALTER TABLE student DISABLE CONSTRAINT student_id_pk ; Example: ALTER TABLE table_name ENABLE CONSTRAINT constraint_name ; Syntax: ALTER TABLE student ENABLE CONSTRAINT student_id_pk ; Example:

18. DDL Commands – Constraints - NOT NULL The NOT NULL constraint enforces a column to NOT accept NULL values . The NOT NULL constraint enforces a field to always contain a value . This means that you cannot insert a new record, or update a record without adding a value to this field .

19. DDL Commands – Constraints - UNIQUE The UNIQUE constraint uniquely identifies each record in a database table . The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns . A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it . Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint .

20. DDL Commands – Constraints - PRIMARY KEY The PRIMARY KEY constraint uniquely identifies each record in a database table . Primary keys must contain UNIQUE values . A primary key column cannot contain NULL values . Most tables should have a primary key, and each table can have only ONE primary key.

21. DDL Commands – Constraints - FOREIGN KEY A FOREIGN KEY in one table points to a PRIMARY KEY in another table . The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables . The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to. DEPT table EMP table Primary Key Foreign Key

22. DML Commands – INSERT INTO The INSERT INTO statement is used to insert new records in a table. INSERT INTO table_name VALUES ( value1,value2,value3,... ); Syntax: INSERT INTO table_name ( column1,column3,column6,... ) VALUES ( value1,value3,value6,... ); Syntax: It is also possible to only insert data in specific columns . Example: Example:

23. DML Commands – UPDATE The UPDATE statement is used to update existing records in a table . UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column = some_value ; Syntax: Example: NOTE: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated !

24. DML Commands – DELETE The DELETE statement is used to delete rows in a table. DELETE FROM table_name WHERE some_column = some_value ; Syntax: Example: NOTE: The WHERE clause specifies which record or records that should be deleted . If you omit the WHERE clause, all records will be deleted ! It is possible to delete all rows in a table without deleting the table. DELETE FROM table_name; Syntax: Example:

25. END OF LAB2