SQL IntroductionSelectionsProjectionsOrdering the ResultsJoinsDisambiguating AttributesTuple VariablesFirst Unintuitive SQLismUnion, Intersection, DifferenceSubqueriesSubqueries Returning RelationsConditions on TuplesCorrelated QueriesRemoving DuplicatesConserving DuplicatesAggregationGrouping and AggregationHAVING ClauseModifying the DatabaseMore Interesting InsertionsDeletionsUpdatesData Definition in SQLData Types in SQLCreating TablesDeleting or Modifying a TableDefault ValuesIndexesCreating IndexesDefining ViewsA Different ViewUpdating ViewsNon-Updatable ViewsSQL IntroductionStandard language for querying and manipulating data Structured Query LanguageMany standards out there: SQL92, SQL2, SQL3.Vendors support various subsets of these, but all of what we’llbe talking about.Basic form: (many many more bells and whistles in addition) Select attributes From relations (possibly multiple, joined) Where conditions (selections)Selections SELECT * FROM Company WHERE country=“USA” AND stockPrice > 50You can use: attribute names of the relation(s) used in the FROM. comparison operators: =, <>, <, >, <=, >= apply arithmetic operations: stockprice*2 operations on strings (e.g., “||” for concatenation). Lexicographic order on strings. Pattern matching: s LIKE p Special stuff for comparing dates and times.Projections SELECT name AS company, stockprice AS price FROM Company WHERE country=“USA” AND stockPrice > 50 SELECT name, stock price FROM Company WHERE country=“USA” AND stockPrice > 50Select only a subset of the attributesRename the attributes in the resulting tableOrdering the Results SELECT name, stock price FROM Company WHERE country=“USA” AND stockPrice > 50 ORDERBY country, nameOrdering is ascending, unless you specify the DESC keyword.Ties are broken by the second attribute on the ORDERBY list, etc.Joins SELECT name, store FROM Person, Purchase WHERE name=buyer AND city=“Seattle” AND product=“gizmo”Product ( name, price, category, maker)Purchase (buyer, seller, store, product)Company (name, stock price, country)Person( name, phone number, city)Disambiguating AttributesSELECT Person.name FROM Person, Purchase, Product WHERE Person.name=buyer AND product=Product.name AND Product.category=“telephony”Product ( name, price, category, maker)Purchase (buyer, seller, store, product)Person( name, phone number, city)Find names of people buying telephony products:Tuple VariablesSELECT product1.maker, product2.maker FROM Product AS product1, Product AS product2 WHERE product1.category=product2.category AND product1.maker <> product2.maker Product ( name, price, category, maker)Find pairs of companies making products in the same categoryFirst Unintuitive SQLismSELECT R.AFROM R,S,TWHERE R.A=S.A OR R.A=T.ALooking for R (S T)But what happens if T is empty?Union, Intersection, Difference(SELECT name FROM Person WHERE City=“Seattle”) UNION(SELECT name FROM Person, Purchase WHERE buyer=name AND store=“The Bon”)Similarly, you can use INTERSECT and EXCEPT.You must have the same attribute names (otherwise: rename).SubqueriesSELECT Purchase.productFROM PurchaseWHERE buyer = (SELECT name FROM Person WHERE social-security-number = “123 - 45 - 6789”);In this case, the subquery returns one value.If it returns more, it’s a run-time error.Subqueries Returning Relations SELECT Company.name FROM Company, Product WHERE Company.name=maker AND Product.name IN (SELECT product FROM Purchase WHERE buyer = “Joe Blow”);Find companies who manufacture products bought by Joe Blow.You can also use: s > ALL R s > ANY R EXISTS RConditions on Tuples SELECT Company.name FROM Company, Product WHERE Company.name=maker AND (Product.name,price) IN (SELECT product, price) FROM Purchase WHERE buyer = “Joe Blow”);Correlated Queries SELECT title FROM Movie AS Old WHERE year < ANY (SELECT year FROM Movie WHERE title = Old.title); Movie (title, year, director, length) Movie titles are not unique (titles may reappear in a later year).Find movies whose title appears more than once.Note scope of variablesRemoving Duplicates SELECT DISTINCT Company.name FROM Company, Product WHERE Company.name=maker AND (Product.name,price) IN (SELECT product, price) FROM Purchase WHERE buyer = “Joe Blow”);Conserving Duplicates(SELECT name FROM Person WHERE City=“Seattle”) UNION ALL(SELECT name FROM Person, Purchase WHERE buyer=name AND store=“The Bon”)The UNION, INTERSECTION and EXCEPT operators operate as sets, not bags.AggregationSELECT Sum(price)FROM ProductWHERE manufacturer=“Toyota”SQL supports several aggregation operations: SUM, MIN, MAX, AVG, COUNT Except COUNT, all aggregations apply to a single attributeSELECT Count(*)FROM PurchaseGrouping and AggregationUsually, we want aggregations on certain parts of the relation.Find how much we sold of every productSELECT product, Sum(price)FROM Product, PurchaseWHERE Product.name = Purchase.productGROUPBY Product.name1. Compute the relation (I.e., the FROM and WHERE).2. Group by the attributes in the GROUPBY3. Select one tuple for every group (and apply aggregation)SELECT can have (1) grouped attributes or (2) aggregates.HAVING ClauseSELECT product, Sum(price)FROM Product, PurchaseWHERE Product.name = Purchase.productGROUPBY Product.nameHAVING Count(buyer) > 100Same query, except that we consider only products that hadat least 100 buyers.HAVING clause contains conditions on aggregates.Modifying the DatabaseWe have 3 kinds of modifications: insertion, deletion, update. Insertion: general form -- INSERT INTO R(A1,…., An) VALUES (v1,…., vn)Insert a new purchase to the database: INSERT INTO Purchase(buyer, seller, product, store) VALUES (Joe, Fred, wakeup-clock-espresso-machine,
View Full Document
Unlocking...