22 Count, Distinct, NVL

• ·         Returns the number of non-null values in the expression column-COUNT group function
• ·         The keyword used to return only non-duplicate values or combinations of non-duplicate values in a query.-DISTINCT
• ·         Returns the number of unique non-null values in the expression column.-COUNT(DISTINCT expression)

1.   How many songs are listed in the DJs on Demand D_SONGS table?

SELECT COUNT(DISTINCT title)

FROM d_songs; gives 6

SELECT COUNT(*)

FROM d_songs; gives 6

SELECT COUNT(id)

FROM d_songs; gives 6

2.  In how many different location types has DJs on Demand had venues?

Possible venues count: (4)

SELECT COUNT(DISTINCT  loc_type)

FROM d_venues;

Venue types used by events count: (2)

SELECT COUNT(DISTINCT venue_id)

FROM d_events;

3.  The d_track_listings table in the DJs on Demand database has a song_id column and a cd_number column. How many song IDs are in the table and how many different CD numbers are in the table?

SELECT COUNT(song_id) AS "song with possible duplication", COUNT(distinct cd_number)  "cd no. distinct"

FROM d_track_listings;

5 and 4

4.  How many of the DJs on Demand customers have email addresses?

Count will skip nulls anyhow.

SELECT COUNT(email) "count with email"

FROM d_clients;

3

5.  Some of the partners in DJs on Demand do not have authorized expense amounts (auth_expense_amt). How many partners do have this privilege?

The language of question seems to be, the one with mentioned limit as auth_expense_amt are bound by this limit, but the one who don’t have limit are free to spend anything.  Say, a row has  auth_expense_amt has 20,  that partner can spend $20, If it is 0 he can’t spend anything, but if this field is null, it means he is out of bound .

SELECT (COUNT(*) - COUNT(auth_expense_amt)) "Free from limit count"

FROM d_partners;

COUNT(*) – all row count is 3, and COUNT(auth_expense_amt) is 1. That’s why above query says 2 are out of any bound due to   auth_expense_amt specified.

COUNT(auth_expense_amt) is 1 because count skips nulls.

6.  What values will be returned when the statement below is issued?

ID	type	shoe_color
456	oxford	brown
463	sandal	tan
262	heel	black
433	slipper	tan

SELECT COUNT(shoe_color), COUNT(DISTINCT shoe_color)

FROM shoes;

4 and 2

7.  Create a query that will convert any null values in the auth_expense_amt column on the DJs on Demand D_PARTNERS table to 100000 and find the average of the values in this column. Round the result to two decimal places.

SELECT TO_CHAR(ROUND(AVG(NVL(auth_expense_amt,100000)),2), '$999999.99')

FROM d_partners;

$166666.67

8.  Which statement(s) is/are True about the following SQL statement:

SELECT AVG(NVL(selling_bonus, 0.10))

FROM bonuses;

_____ a.  The datatypes of the values in the NVL clause can be any datatype except date data.

FALSE, the data type of null value column and the new value must be the same in NVL. In the example above, its 0.10 which suggests selling_bonus is a number. And one more thing, below mentioned statement is valid too:

SELECT  NVL(order_date,TO_DATE('01/01/2000' , 'MM/DD/YYYY')) from f_orders;

But one point to be noted here, AVG works on only numeric values.

_____ b.  If the selling_bonus column has a null value, 0.10 will be substituted.

TRUE. The field will be assumed to contain 0.10 instead of null and hence won’t be skipped by AVG.

_____ c.  There will be no null values in the selling_bonus column when the average is calculated.

TRUE, it won’t permanently change the column data in table, just in the scope of current query, AVG will get 0.10 in the row where null selling_bonus is encountered.

_____ d.  This statement will cause an error. There cannot be two functions in the SELECT statement.

FALSE, AVG is a group function and NVL is single row function. And such two functions are allowed. Definitely in any case above error depicted is out of the world here in this case.

9.  Which of the following statements is/are TRUE about the following query?

SELECT DISTINCT colors, sizes

FROM items;

_____ a.  Each color will appear only once in the result set.

FALSE

_____ b.  Each size will appear only once in the result set.

FALSE

_____ c.  Unique combinations of color and size will appear only once in the result set.

TRUE

_____ d.  Each color and size combination will appear more than once in the result set.

FALSE