This preview shows page 1-2-3-4-5-6-7-51-52-53-54-55-56-57-58-102-103-104-105-106-107-108 out of 108 pages.
CMSC424: Database DesignSQL - IntroductionSQL: Basic StructureA Simple SELECT-FROM-WHERE QuerySlide 5NextFormal Semantics of SQLFormal Semantics of SQL: RA*Slide 9Slide 10Slide 11Next: More SQLMore SQL: ASMore SQL: INTOMore SQL: Order byMore SQL: Aggregate OperatorsMore SQL: Aggregates & Group BySlide 18More SQL: GROUP BY (cont.)Slide 20More SQL: HavingMore SQL: Set/Bag OperationsSlide 23SQL: Summary Thus FarSlide 25Slide 26Next: NULLsMore SQL: NullsSlide 29Slide 30Slide 31More SQL: UnknownSlide 33Slide 34More and More…Nested Subqueries: ExampleExample QuerySet ComparisonDefinition of Some ClauseDefinition of all ClauseSlide 41Test for Empty RelationsSlide 43Test for Absence of Duplicate TuplesSlide 45ViewsExample QueriesDerived RelationsModification of the Database – DeletionSlide 50Modification of the Database – InsertionSlide 52Modification of the Database – UpdatesCase Statement for Conditional UpdatesUpdate of a ViewTransactionsTransactions (Cont.)Joined RelationsJoined Relations – Datasets for ExamplesJoined Relations – ExamplesJoined Relations – ExamplesSlide 62Data Definition Language (DDL)Domain Types in SQLDate/Time Types in SQL (Cont.)Create Table ConstructIntegrity Constraints in Create TableDrop and Alter Table ConstructsEmbedded SQLSlide 70Embedded SQL (Cont.)Updates Through CursorsDynamic SQLODBCODBC (Cont.)ODBC CodeODBC Code (Cont.)Slide 78More 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 DatabaseWith ClauseComplex Query using With ClauseCMSC424, Spring 2005 1CMSC424: Database DesignLecture 6CMSC424, Spring 2005 2SQL - IntroductionStandard DML/DDL for relational DB’s•DML = Data Manipulation Language (queries, updates)•DDL = Data Definition Language (create tables, indexes, …)•View definition•Security (Authorization)•Integrity constraints•TransactionsAlso includesCMSC424, Spring 2005 3SQL: Basic StructureSELECT A1, ….., AnFROM r1, ….., rmWHERE PEquivalent to: A1,A2,…,An (σP (r1 … rn ))CMSC424, Spring 2005 4A Simple SELECT-FROM-WHERE QuerySimilar toSELECT bnameFROM loanWHERE amt > 1000bname ( amt > 1000 (loan) )bnameRedwoodPerryDowntownPerryDuplicates are retained(i.e., result not a set)But not quiteIn general, SQL will not remove duplicates unless asked toCMSC424, Spring 2005 5A Simple SELECT-FROM-WHERE QuerySimilar toSELECT DISTINCT bnameFROM loanWHERE amt > 1000bname ( amt > 1000 (loan) )bnameRedwoodPerryDowntownResult:CMSC424, Spring 2005 6NextFormal Semantics of SQLBag or multiset semanticsCMSC424, Spring 2005 7Formal Semantics of SQL•RA can only express SELECT DISTINCT queriescname ccityJohnsonSmithJohnsonSmithBrightonPerryBrightonR.H.•To express SQL, must extend RA to a bag algebraBags (aka: multisets) like sets, but can have duplicatese.g: {5, 3, 3}e.g: homes = •Next: will define RA*: a bag version of RACMSC424, Spring 2005 8Formal Semantics of SQL: RA*.1 *p (r): preserves copies in rcname ccityJohnsonJohnsonBrightonBrightoncnameJohnsonSmithJohnsonSmithe.g: *city = Brighton (homes) = .2 *A1, …, An (r): no duplicate eliminatione.g: *cname (homes) =CMSC424, Spring 2005 9=Formal Semantics of SQL: RA*3. r * s : additive unionA B112231ααβ β ααA B112α αβA B231β ααA B1 α A B3 α 4. r -* s: bag differencee.g: r -* s = s -* r =*rsCMSC424, Spring 2005 10* Formal Semantics of SQL: RA*5. r * s: cartesian productA B C1111 22ααααβ β+-+-+-A B112α αβC+-CMSC424, Spring 2005 11Formal Semantics of SQLQuery:SELECT a1, ….., anFROM r1, ….., rmWHERE pSemantics:*A1, …, An (*p (r1 * … * rm) ) (1)Query:SELECT DISTINCT a1, ….., anFROM r1, ….., rmWHERE pSemantics:What is the only operator to change in (1)? A1, …, An (*p (r1 * … * rm) ) (2)CMSC424, Spring 2005 12Next: More SQLCMSC424, Spring 2005 13More SQL: AS1. Using AS in FROM clausebname acct_no newbalDowntownMianusA-101A-215525735•Introduces tuple variablese.g: SELECT DISTINCT T.bnameFROM branch AS T, branch AS SWHERE T.assets > S.assetsreturns branch names of branches with non-minimal assets2. Using AS in SELECT clause•Renames columns in result (p)e.g: SELECT bname, acct_no, balance * 1.05 AS newbalFROM accountreturns:CMSC424, Spring 2005 14More SQL: INTOUsed to name query results ()e.g: SELECT DISTINCT bnameINTO branchnamesFROM branchIntuitively:BranchNames SELECT DISTINCT bnameFROM branchCMSC424, Spring 2005 15More SQL: Order byExample: cnameAdamsHayesList in alphabetical order the names of all customers with loans at the Perry branchSELECT DISTINCT cnameFROM borrower AS b, loan AS lWHERE b.lno = l.lno AND bname = “Perry”ORDER BY cnameCan also write:ORDER BY cname DESC, or ORDER BY cname ASCLike SELECT DISTINCT, very expensive requires external sort cannot (usually) fit entire relation in memory. instead must sort in chunks.Result =default: ascending order (asc)CMSC424, Spring 2005 16More SQL: Aggregate OperatorsAggregate operators:AVG (col): average of values in colSUM (col): sum of values in colCOUNT (col): number of values in colExamples:1. Find the average acct balance @ PerrySELECT AVG (bal)FROM accountWHERE bname = “Perry”2. Find the number of tuples in customerSELECT COUNT (*)FROM customer3. Find the number of unique depositorsSELECT COUNT (DISTINCT cname)FROM customerMIN (col): minimun value in colMAX (col): maximun value in colCMSC424, Spring 2005 17More SQL: Aggregates & Group ByUsually, aggregates used with “GROUP BY”bname countDowntownMianusPerryR.H.BrightonRedwood111121Example:SELECT bname, COUNT (DISTINCT cname)FROM depositor AS d,
View Full Document
Unlocking...