海南大学：《数据库原理及应用》课程PPT教学课件（英文版）Chapter 4 SQL

Chapter 4: SQL Background Basic Structure Set Operations Aggregate Functions Null values Nested Subqueries Derived relations Views Modification of the database Joined relations Data Definition Language Embedded SQL ODBC and JDBC 标 Database System Concepts 4.1 @Silberschatz, Korth and Sudarshan

Database System Concepts 4.1 ©Silberschatz, Korth and Sudarshan Chapter 4: SQL Background Basic Structure Set Operations Aggregate Functions Null Values Nested Subqueries Derived Relations Views Modification of the Database Joined Relations Data Definition Language Embedded SQL, ODBC and JDBC

Background In 1986, ANSI and iso published an SQL standard, called SQL-86 In 1989, ANSI published an extended standard for SQL, called SQL-89 SQL-92 and sQL-1999(SQL3) The sQL language has several parts: Data-definition language(DDL) Interactive data-manipulation language(DML) View definition Transaction control Embedded SQL and dynamic SQL Integrity Database System Authorization OSilberschatz. Korth and Sudarshan

Database System Concepts 4.2 ©Silberschatz, Korth and Sudarshan Background In 1986, ANSI and ISO published an SQL standard, called SQL-86 In 1989, ANSI published an extended standard for SQL, called SQL-89 SQL-92 and SQL-1999(SQL3) The SQL language has several parts: Data-definition language (DDL) Interactive data-manipulation language (DML) View definition Transaction control Embedded SQL and dynamic SQL Integrity Authorization

Basic structure A typical SQL query has the form: select a1,A2y…,A from r1,fr2y…;,rm where P Ais represent attributes ris represent relations P is a predicate. This query is equivalent to the relational algebra expression. IA,A2,….An(p(1xF2x…xrm) The result of an SQL query is a relation Database System Concepts 4.3 OSilberschatz. Korth and Sudarshan

Database System Concepts 4.3 ©Silberschatz, Korth and Sudarshan Basic Structure A typical SQL query has the form: select A1 , A2 , ..., An from r1 , r2 , ..., rm where P Ais represent attributes ris represent relations P is a predicate. This query is equivalent to the relational algebra expression. A1, A2, ..., An(P (r1 x r2 x ... x rm)) The result of an SQL query is a relation

The select Clause The select clause list the attributes desired in the result of a query corresponds to the projection operation of the relational algebra E.g. find the names of all branches in the loan relation select branch-name from loan In the"pure"relational algebra syntax, the query would be. Lbranch-name (loan) 标 Database System Concepts OSilberschatz. Korth and Sudarshan

Database System Concepts 4.4 ©Silberschatz, Korth and Sudarshan The select Clause The select clause list the attributes desired in the result of a query corresponds to the projection operation of the relational algebra E.g. find the names of all branches in the loan relation select branch-name from loan In the “pure” relational algebra syntax, the query would be: branch-name(loan)

The select Clause(Cont) SQL allows duplicates in relations as well as in query results. To force the elimination of duplicates, insert the keyword distinct after select. Find the names of all branches in the loan relations, and remove duplicates select distinct branch-name from loan The keyword all specifies that duplicates not be removed select all branch-name from loan Database System Concepts 4.5 @Silberschatz, Korth and Sudarshan

Database System Concepts 4.5 ©Silberschatz, Korth and Sudarshan The select Clause (Cont.) SQL allows duplicates in relations as well as in query results. To force the elimination of duplicates, insert the keyword distinct after select. Find the names of all branches in the loan relations, and remove duplicates select distinct branch-name from loan The keyword all specifies that duplicates not be removed. select all branch-name from loan

The select Clause(Cont) An asterisk in the select clause denotes all attributes select from loan The select clause can contain arithmetic expressions involving the operation, + -,* and / and operating on constants or attributes of tuples The query select loan-number, branch-name amount *s 100 from loan would return a relation which is the same as the loan relations, except that the attribute amount is multiplied by100 Database System Concepts 4.6 OSilberschatz. Korth and Sudarshan

Database System Concepts 4.6 ©Silberschatz, Korth and Sudarshan The select Clause (Cont.) An asterisk in the select clause denotes “all attributes” select * from loan The select clause can contain arithmetic expressions involving the operation, +, –, , and /, and operating on constants or attributes of tuples. The query: select loan-number, branch-name, amount  100 from loan would return a relation which is the same as the loan relations, except that the attribute amount is multiplied by 100

The where Clause The where clause specifies conditions that the result must satisfy corresponds to the selection predicate of the relational algebra To find all loan number for loans made at the Perryridge branch with loan amounts greater than $1200 select / oan-number from loan where branch-name Perryridge and amount>1200 Comparison results can be combined using the logical connectives and. or and not Database System Concepts 4.7 OSilberschatz. Korth and Sudarshan

Database System Concepts 4.7 ©Silberschatz, Korth and Sudarshan The where Clause The where clause specifies conditions that the result must satisfy corresponds to the selection predicate of the relational algebra. To find all loan number for loans made at the Perryridge branch with loan amounts greater than $1200. select loan-number from loan where branch-name = ‘Perryridge’ and amount > 1200 Comparison results can be combined using the logical connectives and, or, and not

The where Clause(Cont SQL includes a between comparison operator E.g. Find the loan number of those loans with loan amounts between $90,000 and $100,000 (that is, ≥$90,000and≤$100,000 select /oan-number from loan where amount between 90000 and 100000 标 Database System Concepts 4.8 OSilberschatz. Korth and Sudarshan

Database System Concepts 4.8 ©Silberschatz, Korth and Sudarshan The where Clause (Cont.) SQL includes a between comparison operator E.g. Find the loan number of those loans with loan amounts between $90,000 and $100,000 (that is, $90,000 and $100,000) select loan-number from loan where amount between 90000 and 100000

The from Clause The from clause lists the relations involved in the query corresponds to the Cartesian product operation of the relational algebra Find the cartesian product borrower x loan select from borrower oan Find the name loan number and loan amount of all customers having a loan at the Perryridge branch select customer-name, borrower oan-number amount from borrower loan where borrower oan-number= loan /oan-number and branch-name =Perryridge Database System Concepts 4.9 @Silberschatz, Korth and Sudarshan

Database System Concepts 4.9 ©Silberschatz, Korth and Sudarshan The from Clause The from clause lists the relations involved in the query corresponds to the Cartesian product operation of the relational algebra. Find the Cartesian product borrower x loan select  from borrower, loan Find the name, loan number and loan amount of all customers having a loan at the Perryridge branch. select customer-name, borrower.loan-number, amount from borrower, loan where borrower.loan-number = loan.loan-number and branch-name = ‘Perryridge’

The Rename Operation The SQL allows renaming relations and attributes using the as clause old-name as new-name Find the name, loan number and loan amount of all customers: rename the column name loan-number as loan-jd select customer-name, borrower oan-number as loan-id. amount from borrower loan where borrower. oan-number loan loan-number Database System Concepts 4.10 @Silberschatz, Korth and Sudarshan

Database System Concepts 4.10 ©Silberschatz, Korth and Sudarshan The Rename Operation The SQL allows renaming relations and attributes using the as clause: old-name as new-name Find the name, loan number and loan amount of all customers; rename the column name loan-number as loan-id. select customer-name, borrower.loan-number as loan-id, amount from borrower, loan where borrower.loan-number = loan.loan-number