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