Docstoc

SQL

Document Sample
SQL Powered By Docstoc
					Microsoft Access Road Map
Structured Query Language
  SQL   is a relational calculus where users
   specify WHAT the result is, not how to
   get it. DBMS takes care of query
   optimization.
  SQL is both a Data Definition Language
   (DDL) and a Data Manipulation
   Language (DML).
Complete SELECT command
                                                Specifies which columns
                                                    appear in output
SELECT [DISTINCT | ALL]
  {* | [column_expr [AS new_name]] [,...]}
FROM table_name [[AS] alias] [,...] Filters the groups
[WHERE condition]                 according to condition
[GROUP BY column_list [HAVING condition] ]
[ORDER BY column_list]          Renames columns
                                              Forms groups
Specifies table(s) to be used Force or disallow duplicates of rows
                                              with same
                                  (SQL allows them) column value


       Filters rows according to condition     Specifies the order of the output
                                         Tuple variables
Running example...
     Beers(name, manf)
     Bars(name, addr, license)
     Drinkers(name, addr, phone)
     Likes(drinker, beer)
     Sells(bar, beer, price)
     Frequents(drinker, bar)

 [From Ullman & Widom: A First Course in Database Systems, Prentice-Hall, 1997]
Examples
 Whatbeers are made by Anheuser-Busch?
 Beers(name, manf)
     SELECT name
     FROM Beers
     WHERE manf = 'Anheuser-Busch';

 Note  single-quoted strings (case sensitive)
 Star as list of all attributes
     SELECT *
     FROM Beers
     WHERE manf = 'Anheuser-Busch';
Renaming & expressions
 The   select clause can contain arithmetic
  expressions involving the operators +, -, *,
  and /, and operating on constants or
  attributes of tuples
 Sells(bar, beer, price)
  SELECT bar, beer,
      price*120 AS priceInYen
  FROM Sells;
 Noteno WHERE
 clause OK
                                  Likes(drinker, beer)
                                  Frequents(drinker, bar)
Multi-relation queries
 Thefrom clause corresponds to the Cartesian
 product operation in relational algebra
  – The listed relations are to be scanned in the
    evaluation of the expression
  – Relation-dot-attribute disambiguates attributes
    from several relations
 Find   beers liked by frequenters of Joe's Bar
   SELECT beer
   FROM Frequents, Likes
   WHERE Frequents.drinker = Likes.drinker
   AND bar = 'Joe''s Bar';
Multi-relation queries
 Startfrom product
 One tuple variable per relation in the FROM
 Nested loops
 Explicit tuple variables
 Tuple   variables can be made explicit
  – Work like aliases of the relations
  – The AS keyword of SQL92 is not allowed in every
    implementation
     pairs of beers by the same manufacturer
 Find
 Beers(name, manf)
                          Avoid producing (Bud, Bud) and avoid
                             producing a pair in both orders

 SELECT b1.name, b2.name
 FROM Beers b1, Beers b2
 WHERE b1.manf = b2.manf AND b1.name < b2.name
Ordering the display of tuples
 SQL  must perform a sort to fulfill an
  order by request – since sorting a large
  number of tuples may be costly, it is
  desirable to sort only when necessary
 We may specify desc for descending
  order or asc for ascending order, for
  each attribute; ascending order is the
  default
SELECT name, phone
FROM Drinkers, Frequents
WHERE Drinkers.name = Frequents.drinker
AND bar = 'Joe''s Bar'
ORDER BY name ASC;
Aggregate (built-in) functions
 These functions operate on the multiset
 of values of a column of a relation, and
 return a value
  – avg: average value      SELECT AVG(price)
  – min: minimum value      FROM Sells
                            WHERE beer = ’Bud’
  – max:maximum value
  – sum: sum of values
  – count: number of values
     the average price of a Bud
 Find
 Sells(bar, beer, price)
Aggregate functions & grouping
 Aggregate  functions can be applied to
  groups of tuples:
  – Having the same value for a given attribute
  – Satisfying some other condition
 Attributes in select clause outside of
  aggregate functions must appear in
  group by list        SELECT beer, AVG(price)
 where, if present, FROM Sells
                       GROUP-BY beer
  must be applied      HAVING AVG(price) < 2
  before having
 NULL values
 Tuples   may have a null value, denoted by
  null, for some of their attributes; null signifies
  an unknown or non-existing value
 The result of any arithmetic expression
  involving null is null
  – Any comparison with null returns unknown
  – Result of where clause predicate is treated as
    false if it evaluates to unknown
  – “P is unknown” evaluates to true if predicate P
    evaluates to unknown
  – Null values are ignored by aggregate functions
    except count(*)
 EXISTS & NOT EXISTS
 “EXISTS(relation)”   is true iff the relation is nonempty
  – Scoping rule: to refer to outer table in inner sub-query, we
    need to give the outer a tuple variable
  – A sub-query that refers to values from a surrounding query
    is called correlated
  – Find the beers that are the unique beer by their
    manufacturer
  Beers(name, manf)
 SELECT name
 FROM Beers b1
 WHERE NOT EXISTS(
    SELECT *
    FROM Beers
    WHERE manf = b1.manf AND name <> b1.name);
 Joining
 The   select-from-where construct implements a
   simple join (product + selection + projection)
 SQL92 also provides alternative forms of FROM to
   implement various types of JOIN:        Cartesian product
    – FROM r1 CROSS JOIN r2
                                                   Equi-join
    – FROM r1 JOIN r2 ON conditions
                                                  Natural
    – FROM r1 JOIN r2 USING field                   joins
    – FROM r1 NATURAL JOIN r2
    – FROM r1 {LEFT|RIGHT|FULL} JOIN r2
Inner
       ON conditions
joins                                   Outer joins
                                           Q1

SQL
  Giveme a list of our customers‟ names
  and phone numbers.

        [Company Name], Phone
  SELECT
  FROM Customers;
                                                 Q2

SQL

 But,I really only need customers between „B‟
  and „D‟...

 SELECT  [Company Name], Phone
  FROM Customers
     WHERE [Customer ID] > „D‟ and
  [Customer ID] < „D‟
                                          Q3

SQL
  Which customer contacts have “Helen”
  as their first name?

        *
  SELECT
  FROM Customers
  WHERE [Contact Name] = „Helen‟
                                      Q4

SQL
  Whatare the products, prices and
  discounts on order 10001?

  SELECT[Unit Price]
  FROM [Order Details]
  WHERE [Order ID] = 10001
                                                Q8

SQL
 What have we sold to the “Cactus Comidas
 para llevar” company?

 SELECT  [Company Name], [Unit Price]
 FROM Customers, Orders, [Order Details]
 Where Customers.[Customer ID] =
 Orders.[Customer ID] and Orders.[Order ID] =
 [Order Details].[Order ID] and
 [Company Name] like '*cactus*';
                                                 Q9

SQL
 Which   products haven‟t shipped?

 SELECT   [English Name]
 FROM Products, Orders, [Order Details]
 WHERE Products.[Product ID] = [Order
 Details].[Product ID] and Orders.[Order ID] =
 [Order Details].[Order ID] and
 [Shipped Date] is null;
                                                               Q10

SQL

   What are the total, maximum, minimum and average
    amounts we‟ve sold?

   SELECT sum([Unit Price] * Quantity),
    min( [Unit Price] * Quantity), max( [Unit Price] * Quantity),
    avg( [Unit Price] * Quantity)
    FROM [Order Details];
                                                           Q11

SQL
   Oh, sorry. I meant for our Tofu product!

   SELECT sum([Unit Price] * Quantity),
    min( [Unit Price] * Quantity), max( [Unit Price] *
    Quantity), avg( [Unit Price] * Quantity)
    FROM [Order Details], Products, Orders
    WHERE [Order Details].[Order ID]=Orders.[Order ID] and
    [Order Details].[Product ID] = Products.[Product ID] and
    Products.[Product Name] = 'Tofu';
                                       Q12

SQL
  How   many customers do we have?
  SELECT   count(*) FROM Customers;


  How   many orders have we filled?
  SELECTcount([Product ID])
  FROM [Order Details];
                                               Q13

SQL
 How   much have we sold for each product?

 SELECT   [Order Details].[Product ID],
 sum([Order Details].[Unit Price] * [Order
 Details].Quantity), count(*)
 FROM [Order Details]
 GROUP BY [Order Details].[Product ID]
 ORDER BY [Order Details].[Product ID] desc;
                                                                 Q14

SQL
 Can     you label those fields?

   SELECT [Order Details].[Product ID], Sum([Order
    Details].[Unit Price]*[Order Details].Quantity) AS Totals,
    Count(*) AS [Product Count]
    FROM [Order Details], Products
    WHERE [Order Details].[Product ID]=[Products].[Product
    ID]                      GROUP BY [Order
    Details].[Product ID]                 ORDER BY [Order
    Details].[Product ID] DESC;
                                                             Q15

SQL
 Can    you sort by amount sold?

   SELECT [Order Details].[Product ID], Sum([Order
    Details].[Unit Price]*[Order Details].Quantity) AS Totals,
    Count(*) AS Products
    FROM [Order Details], Products
    WHERE [Order Details].[Product ID]=[Products].[Product
    ID]                       GROUP BY [Order
    Details].[Product ID]                ORDER BY
    Sum([Order Details].[Unit Price]*[Order Details].Quantity)
    DESC;
                                                            Q16

SQL
   Wait a minute - aren‟t there some discontinued products?

   SELECT [Order Details].[Product ID], Sum([Order
    Details].[Unit Price]*[Order Details].Quantity) AS Totals,
    Count(*) AS Products
    FROM [Order Details], Products
    WHERE [Order Details].[Product ID] = [Products].[Product
    ID] and not Products.[Discontinued]
    GROUP BY [Order Details].[Product ID]
    ORDER BY Sum([Order Details].[Unit Price]*[Order
    Details].Quantity) DESC;

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:20
posted:3/26/2011
language:English
pages:28