Chapter 4: SQL!SQL is approximately relational algebra + …Main goal: You understand this.Schema Used in ExamplesBasic StructureThe select ClauseThe select Clause (Cont.)Slide 8The where ClauseINSERT INTOThe where Clause (Cont.)The from ClauseThe Rename OperationTuple VariablesString OperationsOrdering the Display of TuplesDuplicates (Cont.)Set OperationsSlide 19Aggregate FunctionsAggregate Functions (Cont.)Aggregate Functions – Group ByAggregate Functions – Having ClauseNull ValuesNull Values and Three Valued LogicNull Values and AggregatesNested SubqueriesExample QueryExample Query (rewritten)Slide 30Set ComparisonDefinition of Some Clause (optional)Definition of all Clause (optional)Slide 34!Test for Empty RelationsSlide 36!Example Query ExplainedExample Query rewrittenViewsExample QueriesDerived RelationsWith ClauseComplex Query using With ClauseModification of the Database – DeletionSlide 45Modification of the Database – InsertionModification of the Database – UpdatesCase Statement for Conditional UpdatesExampleUpdate!Update of a ViewTransactionsTransactions (Cont.)More About FROMExampleINNER JOINLEFT [OUTER] JOINRIGHT [OUTER] JOINFULL [OUTER] JOINOrder of Execution Matters and Why Use JoinsSome ChoicesA Very General SELECT StatementAppendix: host-language to database Basic ideas (varies a lot with each language)Embedded SQL (Cont.)Updates Through Cursors (generally a really bad idea for performance)Dynamic SQLHost-to-SQL communication: general adviceDirect Path for large insertsBatch SizeRetrieve Needed Columns OnlyCursors are Death (when staying inside database system)©Silberschatz, Korth and Sudarshan4.1Database System ConceptsChapter 4: SQLChapter 4: SQLBasic Structure Set OperationsAggregate FunctionsNull ValuesNested SubqueriesDerived RelationsViewsModification of the Database Embedded SQL©Silberschatz, Korth and Sudarshan4.2Database System Concepts!SQL is approximately relational algebra + …!SQL is approximately relational algebra + …Summary of key differencesRelational algebra talks about sets, SQL about multisets; you have to track whether there are or are not any duplicates as it may matter to some particular query (like aggregtes)SQL has aggregate operatorsSQL has nulls and you need to know whether a particular column may or may not contain NULLsSQL has operators for modifying the database©Silberschatz, Korth and Sudarshan4.3Database System ConceptsMain goal: You understand this.Main goal: You understand this.SELECT [DISTINCT] { { aggregate function .. | value expression [AS] [column name]}.,...} | {qualifier.*} *FROM { {table name [AS] [correlation name] (column name.,..) ] } | {subquery [AS] correlation name [column name.,..]} | joined table }.,..[WHERE predicate]GROUP BY {{[table name | correlation name}.] column name}.}[HAVING predicate ][{UNION | INTERSECT | EXCEPT} [ALL] [CORRESPONDING [BY (column name.,..)] ] select statement | {TABLE table name} | table value constructor][ORDER BY {{output column [ASC | DESC ]}.,..} | {{positive integer [ASC | DESC ]}.,..};©Zvi M. Kedem©Silberschatz, Korth and Sudarshan4.4Database System ConceptsSchema Used in ExamplesSchema Used in Examples©Silberschatz, Korth and Sudarshan4.5Database System ConceptsBasic Structure Basic Structure SQL is based on set and relational operations with certain modifications and enhancementsA simple SQL query has the form:select A1, A2, ..., Anfrom r1, r2, ..., rmwhere 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.©Silberschatz, Korth and Sudarshan4.6Database System ConceptsThe select ClauseThe select ClauseThe select clause corresponds to the projection operation of the relational algebra. It is used to list the attributes desired in the result of a query.Find the names of all branches in the loan relationselect branch-namefrom loanIn the “pure” relational algebra syntax, the query would be: branch-name(loan)An asterisk in the select clause denotes “all attributes”select *from loanNOTE: SQL does not permit the ‘-’ character in names, so you would use, for example, branch_name instead of branch-name in a real implementation. We use ‘-’ since it looks nicer!NOTE: SQL names are case insensitive, meaning you can use upper case or lower case. •You may wish to use upper case in places where we use bold font.©Silberschatz, Korth and Sudarshan4.7Database System ConceptsThe select Clause (Cont.)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 duplicatesselect distinct branch-namefrom loanThe keyword all specifies that duplicates not be removed.select all branch-namefrom loan©Silberschatz, Korth and Sudarshan4.8Database System ConceptsThe select Clause (Cont.)The select Clause (Cont.)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  100from loanwould return a relation which is the same as the loan relations, except that the attribute amount is multiplied by 100.©Silberschatz, Korth and Sudarshan4.9Database System ConceptsThe where ClauseThe where ClauseThe where clause corresponds to the selection predicate of the relational algebra. If consists of a predicate involving attributes of the relations that appear in the from clause.The find all loan number for loans made a the Perryridge branch with loan amounts greater than $1200.select loan-numberfrom loanwhere branch-name = ‘Perryridge’ and amount > 1200Comparison results can be combined using the logical connectives and, or, and not. Comparisons can be applied to results of arithmetic expressions.©Silberschatz, Korth and Sudarshan4.10Database System ConceptsINSERT INTOINSERT INTOWe will talk about this more generally laterIt is useful to know now that one can create a temporary relation, say temp, and storing the result of the query there by writing. insert into tempselect loan-numberfrom loanwhere branch-name = ‘Perryridge’ and amount > 1200©Silberschatz, Korth and