DATABASE DESIGN I - 1DL300 Introduction to SQL Fall 2011 Elmasri/Navathe ch 4,5 Padron-McCarthy/Risch ch 7,8,9 An introductory course on database systems Silvia Stefanova http://www.it.uu.se/edu/course/homepage/dbastekn/ht11 Uppsala Database Laboratory Department of Information Technology, Uppsala University, Erik Zeitler Uppsala, Sweden Uppsala Database Laboratory Department of Information Technology, Uppsala University, Uppsala, Sweden Silvia Stefanova- UDBL - IT - UU 2011-11-17 1 Silvia Stefanova- UDBL - IT - UU 2011-11-17 2 The SQL database language Parts of the SQL language • SQL - (S tructured Q uery L anguage) • SQL (E/N chapter 4,5) • SQL include several subparts – Basic Structure – DDL • SQL was first developed by IBM in the early 70’s at their San Jose Research – Set Operations Lab. It was called Sequel ( S tructured E nglish QUE ry L anguage) and was – Interactive DML – Aggregate Functions implemented as part of their experimental relational database system, called • Queries: SELECT – Null Values System R project. • Updates: INSERT, DELETE, – Nested Subqueries UPDATE – Derived Relations • SQL has become standard language in commercial RDBMS – Embedded DML – Views – View definition – Modification of the Database • Current version of the ISO/ANSI SQL standard is SQL:2008 – Security and authorization – Joined Relations – Integrity constraints – Data Definition • Object Oriented concepts are introduced in SQL-99 (the earlier SQL-92 is a – Transaction control – Schema Evolution subset of the standard). – Additional SQL Features . Silvia Stefanova - UDBL - IT - UU 2011-11-17 3 Silvia Stefanova - UDBL - IT - UU 2011-11-17 4 1
Basic structure Banking example revisited • SQL is based on set and relational operations with certain modifications and enhancements. • Again we use the bank schema in subsequent examples • A typical SQL query has the form: SELECT A 1 ,A 2 ,...,A n branch (branch_name,branch_city,assets) FROM r 1 ,r 2 ,...,r m customer (customer_name,customer_street,customer_city) WHERE P account (branch_name,account_number,balance) – A i ’s represent attributes, whose values are to be retrieved (projection attributes) loan (branch_name,loan_number,amount) – r i ’s represent relations, required to process the query – P is a predicate – Boolean expression, identifying the tuples to be retrieved depositor (customer_name,account_number) (selection and join conditions) borrower (customer_name,loan_number) • This is equivalent to the relational algebra expression: A 1 , A 2 , ..., A n ( P ( r 1 r 2 ... r m )) • The result of an SQL query is a relation. Silvia Stefanova - UDBL - IT - UU 2011-11-17 5 Silvia Stefanova - UDBL - IT - UU 2011-11-17 6 The SELECT clause The WHERE clause • The SELECT clause corresponds to the projection operation of the relational algebra. • The WHERE clause corresponds to the selection predicate of the relational • It is used to list the attributes desired in the result of a query. algebra. It consists of a predicate involving attributes of the relations that Example: F ind the names of all branches in the loan relation: • appear in the FROM clause. SELECT branch_name FROM loan ; • SQL uses the logical connectives AND , OR , (and NOT ). It allows the use of branch_name (loan) arithmetic expressions as operands to the comparison operators. • An asterisk (*) in the select clause denotes “all attributes”: Example: Find all loan numbers for loans made at the Perryridge branch with SELECT * FROM loan ; loan amounts greater than $1200: • SQL allows duplicates in relations as well as in query results. To force the elimination of SELECT loan_number FROM loan duplicates, insert the keyword DISTINCT after select. WHERE branch_name = “Perryridge” AND amount > 1200 ; • Example: Find the names of all branches in the loan relation, and remove duplicates: SELECT DISTINCT branch_name FROM loan ; • SQL includes a BETWEEN comparison operator in order to simplify WHERE clauses that specify that a value is less than or equal to some value and greater than or equal to • The keyword ALL specifies that duplicates will not be removed: some other value. SELECT ALL branch_name FROM loan ; Example: Find the loan number of those loans with loan amounts between • The SELECT clause can also contain arithmetic expressions involving the operators, + , $90,000 and $100,000 (that is, ≥ $90,000 AND ≤ $100,000) - , *, and /, operating on constants or attributes of tuples. SELECT loan_number FROM loan • Example: Return the loan relation where the amount attribute multiplied by 100: WHERE amount BETWEEN 90000 AND 100000 ; SELECT branch_name, loan_number, amount * 100 FROM loan ; Silvia Stefanova - UDBL - IT - UU 2011-11-17 7 Silvia Stefanova - UDBL - IT - UU 2011-11-17 8 2
The FROM clause The RENAME operation • The FROM clause corresponds to the Cartesian product operation of the • The SQL mechanism for renaming relations and attributes is accomplished relational algebra. It lists the relations to be scanned when evaluating the through the AS clause: whole SELECT expression. Example: Find the Cartesian product borrower loan: • old-name AS new-name SELECT * FROM borrower, loan ; • Example: Find the name and loan number of all customers having a loan at • Example: Find the name and loan number of all customers having a the Perryridge branch; replace the column name loan_number with the name loan at the Perryridge branch (tables borrower, loan) lid. SELECT DISTINCT customer_name, borrower.loan_number SELECT DISTINCT customer_name, borrower.loan_number AS lid Join condition FROM borrower, loan FROM borrower, loan WHERE borrower.loan_number = loan.loan_number AND WHERE borrower.loannumber = loan.loan_number AND branch_name = “Perryridge” ; branch_name = “Perryridge” ; Selection condition Silvia Stefanova - UDBL - IT - UU 2011-11-17 9 Silvia Stefanova - UDBL - IT - UU 2011-11-17 10 Tuple variables String operations • SQL includes a string-matching operator for comparisons on character • Tuple variables (aliases) are defined in the FROM clause via the use of the strings. Patterns are described using two special characters: AS clause. – percent ( % ) . The % character matches any substring. – underscore ( _ ). The _ character matches any character. • Example: Find the customer names and their loan numbers for all customers having a loan at some branch. • Example: Find the names of all customers whose street includes the substring “Main”: SELECT DISTINCT customer_name, T.loan_number SELECT customer_name FROM borrower AS T, loan AS S FROM customer WHERE T.loan_number = S.loan_number ; WHERE customer_street LIKE “%Main%” ; • Example: Find the names of all branches that have greater assets than some • Example: Find the names of all customers whose street starts with the branch located in Brooklyn. substring “Main%”: SELECT DISTINCT T.branch_name SELECT customer_name FROM branch AS T, branch AS B FROM customer WHERE T.assets > B.assets AND B.branch_city = “Brooklyn” ; WHERE customer_street LIKE “Main \ %” ; Silvia Stefanova - UDBL - IT - UU 2011-11-17 11 Silvia Stefanova - UDBL - IT - UU 2011-11-17 12 3
Recommend
More recommend
