SQL DDL and Oracle utilities

SQL DDL and Oracle utilities Murali Mani

Datatypes in SQL • INT (or) INTEGER • FLOAT (or) REAL • DECIMAL (n, m) • CHAR (n) • VARCHAR (n) • DATE, TIME Murali Mani

SQL DDL • DDL = Data Definition Language • Create tables, columns of tables, types of columns, primary key constraints, unique constraints, foreign key constraints • Drop tables, add/drop columns, add/drop constraints – primary key, unique, foreign key Murali Mani

Creating Tables CREATE TABLE <tableName> ( <col> <type>, <col> <type>, … <col> <type>, [CONSTRAINT <cName>] PRIMARY KEY (…), [CONSTRAINT <cName>] UNIQUE (…), [CONSTRAINT <cName>] FOREIGN KEY (…) REFERENCES <tableName> (…) ); Murali Mani

Dropping tables DROP TABLE <tableName> Murali Mani

Adding/Dropping Columns ALTER TABLE <tableName> ADD <col> <type>; ALTER TABLE <tableName> DROP COLUMN <col>; Murali Mani

Adding/Dropping Constraints ALTER TABLE <tableName> ADD [CONSTRAINT <cName>] … ALTER TABLE <tableName> DROP CONSTRAINT <cName> Murali Mani

SQL DML: Basic • DML = Data Manipulation Language • Consists of Queries and Modification • Modification: Inserting/Deleting values from table • Insert a value into a table INSERT INTO <tableName> VALUES (…) eg: INSERT INTO Student VALUES (1, ‘Dave’); • Delete all values from a table DELETE FROM <tableName> Murali Mani

SQL DDL and Oracle utilities