SQL Database Management and Organization

1. IT420: Database Management and Organization SQL: Structured Query Language 25 January 2006 Adina Crăiniceanu www.cs.usna.edu/~adina

2. Goals • SQL

3. Relational Query Languages • A major strength of the relational model: • supports simple, powerful querying of data • Queries can be written intuitively, and the DBMS is responsible for efficient evaluation. • The key: precise semantics for relational queries • Allows the optimizer to extensively re-order operations, and still ensure that the answer does not change

4. SQL: The Structured Query Language • Developed by IBM (system R) in the 1970s • Need for a standard since it is used by many vendors • Standards: • SQL-86 • SQL-89 (minor revision) • SQL-92 (major revision) • SQL-99 (major extensions, current standard)

5. SQL as a Data Sublanguage • SQL is not a full featured programming language as are C, C#, and Java • SQL is a data sublanguage • Create and process database data • SQL is ubiquitous in enterprise-class DBMS products • SQL programming is a critical skill

6. SQL DDL and DML • SQL statements can be divided into two categories: • Data definition language (DDL) statements • Used for creating and modifying tables, views, and other structures • CREATE, DROP, ALTER • Data manipulation language (DML) statements. • Used for queries and data modification • INSERT, DELETE, UPDATE, SELECT

7. SQL DDL Statements • CREATE • ALTER • DROP

8. CREATE TABLE • CREATE TABLE statement is used for creating relations • Each column is described with three parts: • column name • data type • optional constraints

9. CREATE TABLE Example CREATE TABLE Students (StudentNumber integer, StudentLastName char(18) NOT NULL, StudentFirstName char(18) NOT NULL, Email char(50), PhoneNumber char(18) )

10. Constraints • Constraints can be defined within the CREATE TABLE statement, or they can be added to the table after it is created using the ALTER table statement • Five types of constraints: • NULL/NOT NULL • PRIMARY KEY may not have null values • UNIQUEmay have null values • CHECK • FOREIGN KEY

11. Constraints Examples CREATE TABLE Students (StudentNumber integer, StudentLastName char(18) NOT NULL, StudentFirstName char(18) NOT NULL, Email char(50), PhoneNumber char(18), PRIMARY KEY (StudentNumber), UNIQUE (Email) )

12. Default Values and Data Constraints • Students table • Default value for PhoneNumber: 410-123-4567 • Email like “*@usna.edu” CREATE TABLE Students (StudentNumber integer, StudentLastName char(18) NOT NULL, StudentFirstName char(18) NOT NULL, Email char(50), PhoneNumber char(18) DEFAULT “410-123-4567”, PRIMARY KEY (StudentNumber), UNIQUE(Email), CHECK (Email LIKE “*@usna.edu”) ) Syntax depends on DBMS!!!

13. FOREIGN KEY Constraints

14. FOREIGN KEY Constraints CREATE TABLE Departments (DepartmentName char(18), Phone char(18) NOT NULL, Building char(18), Room integer, PRIMARY KEY (DepartmentName) )

15. FOREIGN KEY Constraints CREATE TABLE Students (StudentNumber integer, StudentLastName char(18) NOT NULL, StudentFirstName char(18) NOT NULL, Email char(50), PhoneNumber char(18), MajorDepartmentName char(18), PRIMARY KEY (StudentNumber), UNIQUE(Email), FOREIGN KEY (MajorDepartmentName) REFERENCES Departments (DepartmentName) ON DELETE SET NULL ON UPDATE CASCADE ) • SQL/92 and SQL:1999 support all 4 options on deletes and updates. • Default is NO ACTION (delete/update is rejected) • CASCADE (also delete all rows that refer to deleted row) • SET NULL / SET DEFAULT (sets foreign key value of referencing row)

16. Implementing Cardinalities

17. ALTER Statement • ALTER statement changes • table structure, • properties, or • constraints after the table has been created

18. Adding and Dropping Columns • The following statement will add a column named BirthDate to the Students table: ALTER TABLE Students ADD COLUMN BirthDate Datetime NULL; • You can drop an existing column with the statement: ALTER TABLE Students DROP COLUMN BirthDate;

19. Adding and Dropping Constraints • ALTER can be used to add a constraint as follows: ALTER TABLE Student ADD CONSTRAINT DepartmentFK FOREIGN KEY (MajorDepartmentName) REFERENCES Departments (DepartmentName) ON DELETE NO ACTION ON UPDATE CASCADE • ALTER can be used to drop a constraint: ALTER TABLE Student DROP CONSTRAINT DepartmentFK;

20. Removing Tables • SQL DROP TABLE: DROP TABLE Departments; • If there are constraints: ALTER TABLE Students DROP CONSTRAINT DepartmentFK; DROP TABLE Departments;

21. Class Exercise

22. SQL DDL and DML • Data definition language (DDL) statements • Used for creating and modifying tables, views, and other structures • CREATE, ALTER, DROP • Data manipulation language (DML) statements. • Used for queries and data modification • INSERT, DELETE, UPDATE, SELECT

23. INSERT Statement • INSERT command: INSERT INTO Students (StudentNumber, StudentLastName, StudentFirstName) VALUES (190, ‘Smith', ‘John’); INSERT INTO Students VALUES(190, ‘Smith’, ‘John’, ‘jsmith@usna.edu’, ‘410-431-3456’) • Bulk INSERT: INSERT INTO Students (StudentNumber, StudentLastName, StudentFirstName, Email, PhoneNumber) SELECT * FROM Second_Class_Students;

24. UPDATE Statement • UPDATE command: UPDATE Students SET PhoneNumber = ‘410-123-4567’ WHERE StudentNumber = 673; • BULK UPDATE command: UPDATE Students SET PhoneNumber = ‘410-123-4567’ WHERE StudentLAstName = ‘Doe’;

25. DELETE Statement • DELETE command: DELETE FROM Students WHERE StudentNumber = 190; • If you omit the WHERE clause, you will delete every row in the table!

26. The SQL SELECT Statement • The fundamental framework for SQL query statement is the SQL SELECT statement: • SELECT {ColumnName(s)} • FROM {TableName(s)} • WHERE {Conditions}

27. Specific Columns on One Table SELECT StudentNumber,StudentLastName FROM Students;

28. Specify Column Order SELECT StudentLastName,StudentNumber FROM Students;

29. The DISTINCT Keyword SELECT StudentLastName FROM Students; SELECT DISTINCT StudentLastName FROM Students;

30. Selecting All Columns: The Asterisk (*) Keyword SELECT * FROM Students;

31. Specific Rows from One Table SELECT * FROM Students WHERE StudentLastName = ‘Doe'; NOTE: SQL wants a plain ASCII single quote: ' NOT ‘ !

32. Specific Columns and Rows from One Table SELECT StudentNumber, StudentLastName, StudentFirstName FROM Students WHERE PhoneNumber NOT NULL;