Goals SQL: Data Definition Language IT420: Database Management and - PDF document

Goals � SQL: Data Definition Language IT420: Database Management and � CREATE Organization � ALTER � DROP � SQL: Data Manipulation Language SQL: Structured Query � INSERT Language � DELETE � UPDATE (Chapter 7) � SELECT 1 2 Relational Query Languages SQL DDL and DML � SQL statements can be divided into two � A major strength of the relational model: categories: � supports simple, powerful querying of data � Data definition language (DDL) statements � Queries can be written intuitively, and the � Used for creating and modifying tables, views, and DBMS is responsible for efficient other structures � CREATE, DROP, ALTER evaluation. � Data manipulation language (DML) statements. � Used for queries and data modification � INSERT, DELETE, UPDATE, SELECT 3 4 Creating Tables CREATE TABLE Example � CREATE TABLE Students CREATE TABLE table_name( (StudentNumber integer NOT NULL, column_name1 column_type1 [constraints1], StudentLastName char(18) NOT NULL, …, StudentFirstName char(18) NOT NULL, [[ CONSTRAINT constraint_name] table_constraint] Email char(50), ) PhoneNumber char(18), Table constraints: MajorDepartmentName char(18), � NULL/NOT NULL � PRIMARY KEY (columns) CONSTRAINT PK_Students PRIMARY KEY (StudentNumber), � UNIQUE (columns) CONSTRAINT U_Email UNIQUE (Email), � CHECK (conditions) CONSTRAINT FK_Dept FOREIGN KEY(MajorDepartmentName) � FOREIGN KEY (local_columns ) REFERENCES foreign_table REFERENCES DEPARTMENTS(DepartmentName) ( foreign_columns ) [ON DELETE action_d ON UPDATE action_u ] ON DELETE NO ACTION ON UPDATE CASCADE ) Specify surrogate key in SQL Server: column_name int_type IDENTITY (seed, increment) 5 6 1

FOREIGN KEY Constraints FOREIGN KEY Constraints CREATE TABLE Students � 4 options on DEPARTMENTS DepartmentName Phone Building Room (StudentNumber integer, DepartmentName: char(18) Mathematics 410-293-4573 Michelson Hall 308 StudentLastName char(18) NOT NULL, History 410-293-2255 Sampson Hall 120 deletes and Phone: char(18) Building: char(18) Computer Science 410-293-6800 Michelson Hall 340 StudentFirstName char(18) NOT NULL, Room: integer updates: Email char(50) NULL, D:SN PhoneNumber char(18) NULL, U:C Student Student Student Email PhoneNumber MajorDepartmentName � NO ACTION Number LastName FirstName MajorDepartmentName char(18) NULL, 190 Smith John jsmith@usna.edu 410-431-3456 PRIMARY KEY (StudentNumber), Majors (delete/update is 673 Doe Jane jdoe@usna.edu Computer Science UNIQUE(Email), 312 Doe Bob bred@usna.edu 443-451-7865 Mathematics rejected) FOREIGN KEY (MajorDepartmentName) I:SN U:SN REFERENCES Departments (DepartmentName) CREATE TABLE Departments STUDENTS � CASCADE ON DELETE SET NULL StudentNumber: integer (DepartmentName char(18), ON UPDATE CASCADE StudentLastName: char(18) Phone char(18) NOT NULL, � SET NULL StudentFirstName: char(18) ) Building char(18), Email: varchar(50) PhoneNumber: char(18) Room integer, � SET DEFAULT DepartmentName: char(18) (FK) PRIMARY KEY (DepartmentName) ) 7 8 Modifying Tables ALTER TABLE Examples � ALTER TABLE Students ADD COLUMN BirthDate � ALTER TABLE table_name clause datetime NULL Clauses: � ALTER TABLE Students DROP COLUMN BirthDate ADD COLUMN column_name column_type [constraints] DROP COLUMN column_name � ALTER TABLE Student ADD CONSTRAINT ALTER COLUMN / MODIFY – DBMS specific! FK_Department ADD CONSTRAINT constraint FOREIGN KEY (MajorDepartmentName) DROP CONSTRAINT constraint_name REFERENCES Departments (DepartmentName) ON DELETE NO ACTION ON UPDATE CASCADE 9 10 Removing Tables SQL DDL and DML � DROP TABLE table_name � Data definition language (DDL) statements DROP TABLE Departments; � Used for creating and modifying tables, views, and other structures � If there are constraints dependent on table: � CREATE, ALTER, DROP � Remove constraints � Data manipulation language (DML) � Drop table statements. ALTER TABLE Students � Used for queries and data modification DROP CONSTRAINT FK_Department; � INSERT, DELETE, UPDATE, SELECT DROP TABLE Departments; 11 12 2

SQL DML INSERT Statement INSERT INTO table_name [ (column_list) ] VALUES (data_values) � Data manipulation language (DML) INSERT INTO table_name [ (column_list) ] select_statement statements. INSERT command: � Used for queries and data modification INSERT INTO Students (StudentNumber, StudentLastName, StudentFirstName) VALUES (190, ‘Smith', ‘John’); � INSERT INSERT INTO Students VALUES(190, ‘Smith’, ‘John’, ‘jsmith@usna.edu’, � DELETE ‘410-431-3456’) � UPDATE � SELECT � Bulk INSERT: INSERT INTO Students (StudentNumber, StudentLastName, StudentFirstName, Email, PhoneNumber) SELECT * FROM Second_Class_Students; 13 14 UPDATE Statement DELETE Statement UPDATE table_name DELETE FROM table_name SET column_name1 = expression1 [ ,column_name2 = expression2,… ] [ WHERE search_condition ] [ WHERE search_condition ] � DELETE command: � UPDATE command: DELETE FROM Students UPDATE Students WHERE StudentNumber = 190; SET PhoneNumber = ‘410-123-4567’ � WHERE StudentNumber = 673; If you omit the WHERE clause, you will delete every row in the table! � BULK UPDATE command: � Another example: UPDATE Students DELETE FROM Departments SET PhoneNumber = ‘410-123-4567’ WHERE StudentLAstName = ‘Doe’; WHERE DepartmentName = ‘ComSci’ Integrity constraints?! Student Student Student Email PhoneNumber Number LastName FirstName 190 Smith John jsmith@usna.edu 410-431-3456 673 Doe Jane jdoe@usna.edu 15 16 312 Doe Bob bred@usna.edu 443-451-7865 Selecting All Columns: The SQL SELECT Statement The Asterisk (*) Keyword � Basic SQL Query: SELECT * FROM Students; SELECT [DISTINCT] column_name(s) | * FROM table_name(s) Student Student Student Email PhoneNumber MajDeptName [WHERE conditions] Number LastName FirstName 190 Smith John jsmith@usna.edu 410-431-3456 ComSci 673 Doe Jane jdoe@usna.edu ComSci 312 Doe Jane jdoe2@usna.edu 443-451-7865 Math 17 18 3

Specific Columns and Rows from The DISTINCT Keyword One Table SELECT StudentNumber, SELECT SName SELECT DISTINCT StudentLastName, SName FROM Students; StudentFirstName FROM Students FROM Students; WHERE MajDeptName = ‘ComSci’; StudentLastName Student Student Student StudentLastName Smith Number LastName FirstName 190 Smith John Doe Smith 673 Doe Jane Doe Doe 19 20 Class Exercise Students, Courses, Enrolled Find the names of students enrolled in IT420 � Division(Name, Building, OfficeNb) � Department(DeptName, ChairName, WebAddress, SELECT SName DivName) FROM Students S, Enrolled E WHERE S.Snb = E.SNb AND E.Cid = ‘IT420’ � Create tables Cid CName CDept SNb SName Email � Modify Department to add a FK constraint for DivName IT420 Database ComSci 190 Smith jsmith@usna.edu � Create table Colleges with same structure as Division IT340 Networks ComSci 673 Doe jdoe@usna.edu � Insert everything from Division into Colleges SM121 Calculus1 Math 312 Doe jdoe2@usna.edu � Remove Division table SNb Cid Semester � Find the name of the Chair of the ‘Math’ Department 190 IT340 Spring2006 312 IT420 Fall2005 21 22 SELECT - Conceptual Evaluation Example Conceptual Evaluation Strategy � Semantics of an SQL query defined in terms of SELECT SName the following conceptual evaluation strategy: FROM Students S, Enrolled E WHERE S.Snb = E.SNb AND E.Cid = ‘IT420’ � Compute the cross-product of table_names � Discard resulting rows if they fail condition S.SNb SName Email E.SNb Cid Semester � Delete columns that are not in column_names 190 Smith jsmith@usna.edu 190 IT340 Spring2006 � If DISTINCT is specified, eliminate duplicate rows 190 Smith jsmith@usna.edu 312 IT420 Fall2005 � This strategy is probably the least efficient way 673 Doe jdoe@usna.edu 190 IT340 Spring2006 673 Doe jdoe@usna.edu 312 IT420 Fall2005 to compute a query! 312 Doe jdoe2@usna.edu 190 IT340 Spring2006 � An optimizer will find more efficient strategies to 312 Doe jdoe2@usna.edu 312 IT420 Fall2005 compute the same answers. 23 24 4