Day 4

Quiz

1:Put the following keywords in order to make a SELECT statement work properly:

  • GROUP BY

  • HAVING

  • SELECT

  • DISTINCT

  • WHERE

  • FROM

  • ORDER BY

  • ROLLUP

A1: Answer:

  • SELECT

  • DISTINCT

  • FROM

  • WHERE

  • GROUP BY

  • ROLLUP

  • HAVING

  • ORDER BY

2:What is the difference between COUNT(*) and COUNT(expression)?
A2: COUNT(expression) evaluates the expression in each row. Only non-null values are counted. COUNT(*) counts every row. It does not examine any columns.
3:Is the following query permitted? If not, how would you correct it?
select ProductID, ProductName
  from Products
 where UnitPrice > avg(UnitPrice)

A3: The query is not permitted. An aggregate function (AVG) cannot appear in a WHERE clause unless it is part of a subquery. Here is the corrected query:
select ProductID, ProductName
  from Products where UnitPrice >
      (
      select avg(UnitPrice)
        from Products
      )

4:Is the following query permitted? If not, how would you correct it?
select Country, Region, Count(*) as 'Customers'
  from Customers
 group by Country

A4: All nonaggregate columns in the query need to be included in the grouping clause. Region is included in the select list, but is not listed after GROUP BY. Here is the correct query:
select Country, Region, Count(*) as 'Customers'
  from Customers
 group by Country, Region

5:What is the difference between HAVING and WWHER?
A5: A WHERE clause is evaluated before the results are grouped. A HAVING clause is evaluated after grouping has occurred.
6:What is the difference between ROLLUP and CUBE?
A6: ROLLUP provides a set of subtotals and totals, traversing the hierarchy of the grouping expressions. CUBE provides a complete set of subtotals and totals, representing a complete set of all possible combinations of grouping expressions.

Exercises

1:Who is the oldest employee, and when was he or she born? 
select LastName, FirstName, BirthDate
  from Employees where BirthDate =
       (
       select min(BirthDate)
         from Employees
       )

A1: Margaret Peacock, 9/19/1937
2:How many customers are located outside the United States? 
select count(*) as 'Foreign customers'
  from Customers where Country <> 'USA'

A2: Result: 
78

3:Prepare a report showing a breakdown of suppliers by country. 
select Country, count(*) as 'Suppliers'
  from Suppliers
 group by Country with rollup

A3: Results:
Country         Suppliers
--------------- -----------
Australia                 2
Brazil                    1
Canada                    2
Denmark                   1
Finland                   1
France                    3
Germany                   3
Italy                     2
Japan                     2
Netherlands               1
Norway                    1
Singapore                 1
Spain                     1
Sweden                    2
UK                        2
USA                       4
NULL                     29

4:Which countries have more than one supplier? Sort the countries by number of suppliers, from greatest to least. 
select Country, count(*) as 'Suppliers'
  from Suppliers
 group by Country
having count(*) >= 2
 order by count(*) desc, Country

A4: Results:
Country         Suppliers
--------------- -----------
USA                       4
France                    3
Germany                   3
Australia                 2
Canada                    2
Italy                     2
Japan                     2
Sweden                    2
UK                        2

5:How many different categories of product are there? 
select count(distinct CategoryID)
  from Products

A5: Results:
8

6:Display the top selling product ID (by dollars sold, including discounts) and the total dollar sales. 
select top 1
       ProductID,
       sum(Quantity * UnitPrice * (1.0-Discount))
                 as 'Dollar Sales'
 from
       [Order Details]
 group by
       ProductID
 order by
        sum(Quantity * UnitPrice * (1.0-Discount)) desc

A6: Results:
ProductID   Dollar Sales
----------- -------------------------------
          38 41396.73522949219

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset