1: | Put the following keywords in order to make a SELECT statement work properly:
|
A1: | Answer:
|
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. |
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 |