Chapter 4: SQLSchema Used in ExamplesBasic StructureThe select ClauseThe select Clause (Cont.)Slide 6The where ClauseThe where Clause (Cont.)The from ClauseThe Rename OperationTuple VariablesString OperationsOrdering the Display of TuplesDuplicatesDuplicates (Cont.)Set OperationsSlide 17Aggregate FunctionsAggregate Functions (Cont.)Aggregate Functions – Group ByAggregate Functions – Having ClauseNull ValuesNull Values and Three Valued LogicNull Values and AggregatesNested SubqueriesExample QuerySlide 27Set ComparisonDefinition of Some ClauseDefinition of all ClauseSlide 31Test for Empty RelationsSlide 33Test for Absence of Duplicate TuplesSlide 35ViewsExample QueriesDerived RelationsWith ClauseComplex Query using With ClauseModification of the Database – DeletionSlide 42Modification of the Database – InsertionSlide 44Modification of the Database – UpdatesCase Statement for Conditional UpdatesUpdate of a ViewTransactionsTransactions (Cont.)Joined RelationsJoined Relations – Datasets for ExamplesJoined Relations – ExamplesJoined Relations – ExamplesSlide 54Data Definition Language (DDL)Domain Types in SQLDate/Time Types in SQL (Cont.)Create Table ConstructIntegrity Constraints in Create TableDrop and Alter Table ConstructsEmbedded SQLSlide 62Embedded SQL (Cont.)Updates Through CursorsDynamic SQLODBCODBC (Cont.)ODBC CodeODBC Code (Cont.)Slide 70More ODBC FeaturesODBC Conformance LevelsJDBCJDBC CodeJDBC Code (Cont.)JDBC Code DetailsPrepared StatementOther SQL FeaturesSchemas, Catalogs, and EnvironmentsProcedural Extensions and Stored ProceduresExtra Material on JDBC and Application ArchitecturesTransactions in JDBCProcedure and Function Calls in JDBCResult Set MetaDataDatabase Meta DataApplication ArchitecturesTwo-tier ModelThree Tier ModelThree-tier Model (Cont.)End of ChapterThe loan and borrower RelationsThe Result of loan inner join borrower on loan.loan-number = borrower.loan-numberThe Result of loan left outer join borrower on loan-numberThe Result of loan natural inner join borrowerJoin Types and Join ConditionsThe Result of loan natural right outer join borrowerThe Result of loan full outer join borrower using(loan-number)SQL Data Definition for Part of the Bank Database©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 Joined RelationsData Definition Language Embedded SQL, ODBC and JDBC©Silberschatz, Korth and Sudarshan4.2Database System ConceptsSchema Used in ExamplesSchema Used in Examples©Silberschatz, Korth and Sudarshan4.3Database System ConceptsBasic Structure Basic Structure SQL is based on set and relational operations with certain modifications and enhancementsA typical 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.4Database 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.5Database 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.6Database 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.7Database 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.8Database System ConceptsThe where Clause (Cont.)The where Clause (Cont.)SQL Includes a between comparison operator in order to simplify where clauses that specify that a value be less than or equal to some value and greater than or equal to some other value.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-numberfrom loanwhere amount between 90000 and 100000©Silberschatz, Korth and Sudarshan4.9Database System ConceptsThe from ClauseThe from ClauseThe from clause corresponds to the Cartesian product operation of the relational algebra. It lists the relations to be scanned in the evaluation of the expression.Find the Cartesian product borrower x loanselect from borrower, loanFind the name, loan number and loan amount of all customers having a loan at the Perryridge branch.select customer-name,