Data Modeling Seminar

1. Data ModelingSeminar February 18, 2012 Lesson 3 Standard SQL

2. Lesson 3 Standard SQL

3. Standard SQL • Data Statements – query and modify tables and columns • SELECT Statement – query tables and views in the database • INSERT Statement – add rows to tables • UPDATE Statement – modify columns in table rows • DELETE Statement – remove rows from tables • Schema Statements – maintain schema (catalog) • CREATE TABLE Statement – create tables • CREATE VIEW Statement – create views • DROP TABLE Statement -- drop tables • DROP VIEW Statement -- drop views • Constraints & Programmability • Constraints • Procedures • Function • Triggers

4. Standard SQL – Data Statements • SELECT Statement -- query tables and views in the database • SELECT Statement Basics • SELECT Clause • FROM Clause • WHERE Clause • Extended Query Capabilities • ORDER BY Clause • Value Expressions • Joining Tables • Subqueries • Grouping Queries (GROUP BY) • Aggregate Queries (Set Functions) • Union Queries (UNION) • SQL Modification Statements INSERT Statement -- add rows to tables • VALUES Clause • UPDATE Statement -- modify columns in table rows • SET Clause • DELETE Statement -- remove rows from tables

5. SELECT Statement Basics • The SQL SELECT statement queries data from tables in the database. The statement begins with the SELECT keyword. The basic SELECT statement has 3 clauses: • SELECT • FROM • WHERE The SELECT clause specifies the table columns that are retrieved. The FROM clause specifies the tables accessed. The WHERE clause specifies which table rows are used. • The WHERE clause is optional; if missing, all table rows are used. For example, SELECT name FROM s WHERE city='Rome' • This query accesses rows from the table - s. • It then It then filters those rows where the city column contains Rome. • Finally, the query retrieves the name column from each filtered row.

6. SELECT - Extended Query Capabilities • Sorting Query Results -- using the ORDER BY clause • Expressions -- in the SELECT clause and WHERE clause • Literal -- self-defining values • Function Call -- expression functions • System Value -- builtin system values • Special Construct -- special expression construct • Numeric or String Operator -- expression operators • Joining Tables -- in the FROM clause • Outer Join -- extended join • Self Join -- joining a table to itself • Subqueries -- embedding a query in another • Predicate Subqueries -- subqueries in logical expressions • Scalar Subqueries -- subqueries in scalar expressions • Table Subqueries -- subqueries in the FROM clause • Grouping Queries -- using the GROUP BY clause, Set Function and HAVING clause • GROUP BY Clause -- specifying grouping columns • Set Functions -- summary functions • HAVING Clause -- filtering grouped rows • Aggregate Queries -- using Set Functions and the HAVING clause • Union Queries -- using the query operator, UNION • Union-Compatible Queries -- query requirements for Union

7. Joining Tables • The FROM clause allows more than 1 table in its list, however simply listing more than one table will very rarely produce the expected results. • The rows from one table must be correlated with the rows of the others. • This correlation is known as joining.

8. Join Example An example can best illustrate the rationale behind joins. The following query: SELECT * FROM sp, p Produces:

9. SELECT * FROM sp, p • Each row in sp is arbitrarily combined with each

10. Inner Join Example • A more usable query would correlate the rows from sp with rows from p, for instance matching on the common column -- pno: SELECT * FROM sp, p WHERE sp.pno = p.pno This produces:

11. Outer Joins • An outer join provides the ability to include unmatched rows in the query results. • The outer join combines the unmatched row in one of the tables with an artificial row for the other table. This artificial row has all columns set to null. • The outer join is specified in the FROM clause and has the following general format: table-1 { LEFT | RIGHT | FULL } OUTER JOIN table-2 ON predicate-1 • predicate-1 is a join predicate for the outer join. It can only reference columns from the joined tables. • The LEFT, RIGHT or FULL specifiers give the type of join: • LEFT -- only unmatched rows from the left side table (table-1) are retained • RIGHT -- only unmatched rows from the right side table (table-2) are retained • FULL -- unmatched rows from both tables (table-1 and table-2) are retained

12. Outer Join Example: SELECT pno, descr, color, sno, qty FROM p LEFT OUTER JOIN sp ON p.pno = sp.pno

13. INSERT Statement The INSERT Statement adds one or more rows to a table. It has two formats: INSERT INTO table-1 [(column-list)] VALUES (value-list) & INSERT INTO table-1 [(column-list)] (query-specification) • The first form inserts a single row into table-1 and explicitly specifies the column values for the row. • The second form uses the result of query-specification to insert one or more rows into table-1. The result rows from the query are the rows added to the insert table. • Note: the query cannot reference table-1.

14. UPDATE Statement The UPDATE statement modifies columns in selected table rows. It has the following general format: UPDATE table-1 SET set-list [WHERE predicate] The optional WHERE Clause has the same format as in the SELECT Statement. The WHERE clause chooses which table rows to update. If it is missing, all rows are in table-1 are updated.

15. SET Clause The SET Clause in the UPDATE Statement updates (assigns new value to) columns in the selected table rows. It has the following general format: SET column-1 = value-1 [, column-2 = value-2] ... • column-1and column-2 are columns in the Update table. • value-1and value-2 are expressions that can reference columns from the update table. • They also can be the keyword -- NULL, to set the column to null.

16. DELETE Statement The DELETE Statement removes selected rows from a table. It has the following general format: DELETE FROM table-1 [WHERE predicate] • The optional WHERE Clause has the same format as in the SELECT Statement. • The WHERE clause chooses which table rows to delete. If it is missing, all rows are in table-1 are removed. • The WHERE Clause predicate can contain subqueries, but the subqueries cannot reference table-1. • This prevents situations where results are dependent on the order of processing.

17. Schema Statements Maintain Schema (catalog) • CREATE TABLE Statement – create tables • CREATE VIEW Statement – create views • DROP TABLE Statement – drop tables • DROP VIEW Statement – drop views

18. Constraints • Check • Unique • Primary Key • Foreign Key • Not Null

19. CHECK constrains • A constraint is an object which tells the DBMS: don't permit updates of the database which would break rules established for business/integrity reasons. • A CHECK constraint works thus: CREATE TABLE TABLE_1 (COLUMN_1 SMALLINT); -- make the table ALTER TABLE TABLE_1 ADD CONSTRAINT CONSTRAINT_1 CHECK (COLUMN_1 > 0); -- make the constraint INSERT INTO TABLE_1 VALUES (5); -- this succeeds UPDATE TABLE_1 SET COLUMN_1 = -5; -- this fails

20. PRIMARY KEY constraint • A PRIMARY KEY constraint works thus: CREATE TABLE TABLE_1 (COLUMN_1 SMALLINT); ALTER TABLE TABLE_1 ADD CONSTRAINT CONSTRAINT_1 PRIMARY KEY (COLUMN_1); INSERT INTO TABLE_1 VALUES (5); INSERT INTO TABLE_1 VALUES (5); • A table's primary key must contain unique values, so the second INSERT in this example will fail. • Alternative: if all we want to say is "values must be unique", we could say UNIQUE (COLUMN_1) instead of PRIMARY KEY (COLUMN_1).

21. FOREIGN KEY constraint • A FOREIGN KEY constraint works thus: CREATE TABLE TABLE_1 (COLUMN_1 SMALLINT); ALTER TABLE TABLE_1 ADD CONSTRAINT CONSTRAINT_1 PRIMARY KEY (COLUMN_1); INSERT INTO TABLE_1 VALUES (5); CREATE TABLE TABLE_2 (COLUMN_1 SMALLINT); ALTER TABLE TABLE_2 ADD CONSTRAINT FOREIGN KEY (COLUMN_1) REFERENCES TABLE_1; INSERT INTO TABLE_2 VALUES (5); INSERT INTO TABLE_2 VALUES (6); • The first INSERT into TABLE_2 will succeed, because our foreign-key value (5) exists in the primary-key table that we're referencing. • But we never inserted a 6 into TABLE_1, so we can't insert a 6 into TABLE_2, so the second INSERT in this example will fail.

22. Programmability • Stored Procedure • Function • Scalar value • Table value • Trigger

23. Stored Procedure

24. Function – Scalar value

25. Function – Table value

26. Trigger

27. Questions

28. Lab 2 • Class Scheduling System in SQL Server • Create ClassSchedule database • Load data • Execute Queries