(第7章)2013版mydbSQLforStudents.ppt

Chapter 7Structured Query Language (SQL);Chapter Objectives;SQL DDL and DML;;Sample Database;Sample Data;Sample EMPLOYEE Relation;Sample PROJECT Relation;Sample ASSIGNMENT Relation;CREATE TABLE;类型表示 ;Constraints;CHECK约束;CREATE TABLE: Example;ALTER Statement;FOREING KEY CONSTRAIN: Example;FK CONSTRAIN: ON DELETE CASCADE;DROP Statements;DROP Statements: Example;SQL for Data Retrieval (Queries);Basic format of SELECT Statement;The Results of a Query is a Relation;Showing a Row Only Once: DISTINCT;Example: SELECT DISTINCT;Specifying Search Criteria: WHERE;Match Criteria;Match Operators;Example: SELECT Statement with WHERE Clause;Allowing for Wildcard Searches: LIKE;Example: LIKE/ IS NULL;The Logical NOT Operator;IN: A List of Values;IN: Example;NOT IN：Example ;Finding Data Matching a Range of Values: BETWEEN;Example: BETWEEN;Sorting the Results;Built-in SQL Functions;Built-in SQL Functions (continued);Built-in Functions :Example 1;Built-in Functions :Example 2;Providing Subtotals: GROUP BY;Built-in Functions and Grouping: Example 1;Built-in Functions and Grouping: Example 2;Built-in Functions and Grouping: Example 3: 苗P116;Retrieving Information from Multiple Tables;Subqueries;IN Subqueries: Example1;IN Subqueries: Example1;IN Subqueries ：Example2 苗p113; IN Subqueries ：Example 3 苗p111 ; Subqueries ：Example 苗p112 ;NOT IN Subqueries ：Example 苗p114;Joins;Design for Enrollment Database;Join Condition;Cartesian Product;Error from Omitting a Join;Combining Join with Other Conditions;Analysis: Join and Other Conditions;Analysis: Join and Other Conditions;Analysis: Join and Other Conditions;Incorrectly Using a Join with OR;Use Parentheses with OR;Tables for the Grade Query;Three Table Join;Ambiguous Columns;Solving Ambiguous Names;Table Name Aliases;Joins:Example;Joins ：Example2 苗p112 ;Alternate Join Syntax ;课程表（苗，p109）;Alternate Join Syntax ：Example 苗p109;例：关于课程表的查询及自连接（续）;三表查询结果;讨论：连接与子查询;查询语句总结;Joins (cont, optional);Outer Joins (optional);Example: Left Outer Join