Sunday, October 16, 2016

27 Single-Row Subqueries



  •          An inner query that returns only one row to the outer query-single-row subquery         

1.  Write a query to return all those employees who have a salary greater than that of Lorentz and are in the same department as Abel.
SELECT  first_name, last_name
FROM EMPLOYEES
WHERE salary >  (SELECT salary FROM employees WHERE last_name = 'Lorentz') AND department_id = (SELECT department_id FROM employees WHERE last_name = 'Abel');
FIRST_NAME
LAST_NAME
Eleni
Zlotkey
Ellen
Abel
Jonathon
Taylor

2.  Write a query to return all those employees who have the same job id as Rajs and were hired after Davies.
SELECT  first_name, last_name
FROM EMPLOYEES
WHERE  job_id = (SELECT job_id  FROM employees WHERE last_name = 'Rajs') AND hire_date >  (SELECT hire_date FROM employees WHERE last_name = 'Davies') ;
FIRST_NAME
LAST_NAME
Randall
Matos
Peter
Vargas

3.  What DJs on Demand events have the same theme code as event ID = 100?
SELECT id, name
FROM d_events
WHERE theme_code =  (SELECT theme_code FROM d_events WHERE id = 100);
ID
NAME
100
Peters Graduation
105
Vigil wedding

4.  What is the staff type for those Global Fast Foods jobs that have a salary less than those of any Cook staff-type jobs?
SELECT staff_type, MAX(salary)
FROM f_staffs
GROUP BY staff_type
HAVING MAX(salary) < (SELECT MAX(SALARY) FROM f_staffs WHERE staff_type = 'Cook');
STAFF_TYPE
MAX(SALARY)
Order Taker
6.75

5.  Write a query to return a list of department id’s and average salaries where the department’s average salary is greater than Ernst’s salary.
SELECT department_id, TO_CHAR(ROUND(AVG(salary),2),'$999999.99') "Average Salary"
FROM employees
GROUP BY department_id
HAVING AVG(salary) > ( SELECT salary from employees WHERE last_name = 'Ernst');
DEPARTMENT_ID
Average Salary

$7,000.00
90
$19,333.33
20
$9,500.00
110
$10,150.00
80
$10,033.33
60
$6,400.00

6.  Return the department ID and minimum salary of all employees, grouped by department ID, having a minimum salary greater than the minimum salary of those employees whose department ID is not equal to 50.

SELECT department_id, TO_CHAR(ROUND(MIN(salary),2),'$999999.99') "Minimum Salary"
FROM employees
GROUP BY department_id
HAVING MIN(salary) > ( SELECT MIN(salary) from employees WHERE department_id != 50);

DEPARTMENT_ID
Minimum Salary

$7,000.00
90
$17,000.00
20
$6,000.00
110
$8,300.00
80
$8,600.00
10
$4,400.00


26 Fundamentals of Subqueries


·         It accepts a value from the inner query to complete its SELECT statement.-outer query
·         An inner query that is nested within an outer query-subquery
·         Another name for a subquery-inner query

1.  What is the purpose of using a subquery?
To find the intermediate information we need to extract information we want. E.g. extracting right part in WHERE/HAVING/FROM clause.
2.  What is a subquery?
An inner query that is nested within an outer query
3.  What DJs on Demand d_play_list_items song_id’s have the same event_id as song_id 45?
SELECT song_id
FROM d_play_list_items
WHERE event_id IN(SELECT event_id FROM d_play_list_items WHERE song_id =45);

4.  Which events in the DJs on Demand database cost more than event_id = 100?
SELECT id, name
FROM d_events
WHERE cost > (SELECT cost FROM d_events WHERE id = 100);


5.  Find the track number of the song that has the same CD number as “Party Music for All Occasions.”

SELECT track
FROM d_track_listings
WHERE cd_number = (SELECT cd_number FROM d_cds WHERE title = 'Party Music for All Occasions');


6.  List the DJs on Demand events whose theme code is the same as the code for “Tropical.”
SELECT id, name
FROM d_events
WHERE theme_code = (SELECT code FROM d_themes WHERE description = 'Tropical');

7.  What are the names of the Global Fast Foods staff members whose salaries are greater than the staff member whose ID is 12?
SELECT first_name,last_name
FROM f_staffs
WHERE salary > (SELECT salary FROM f_staffs WHERE id = 12);

8.  What are the names of the Global Fast Foods staff members whose staff types are not the same as Bob Miller’s?
SELECT first_name,last_name
FROM f_staffs
WHERE staff_type != (SELECT staff_type FROM f_staffs WHERE first_name = 'Bob' AND last_name ='Miller');

9.  Which Oracle employees have the same department ID as the IT department?
SELECT first_name,last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');

10. What are the department names of the Oracle departments that have the same location ID as Seattle?
SELECT department_name
FROM departments
WHERE location_id = ( SELECT location_id FROM locations WHERE city = 'Seattle');

11. Indicate whether the statement regarding subqueries is True or False.

a.  It is good programming practice to place a subquery on the right side of the comparison operator.
TRUE- this is a guideline, but it doesn’t break it.
b.  A subquery can reference a table that is not included in the outer query’s FROM clause.
TRUE
c.  Single-row subqueries can return multiple values to the outer query.

FALSE

Saturday, October 15, 2016

25 Set Operators


  • ·         operator that returns all rows from both tables and eliminates duplicates-UNION
  • ·         columns that were made up to match queries in another table that are not in both tables-TO_CHAR(NULL) or TO_DATE(NULL) or TO_NUMBER(NULL) –matching the select list
  • ·         operator that returns all rows from both tables, including duplicates-UNION ALL
  • ·         used to combine results into one single result from multiple SELECT statements-set operators
  • ·         operator that returns rows that are unique to each table-MINUS
  • ·         operator that returns rows common to both tables-INTERSECT

1.  Name the different Set operators?
UNION
UNION ALL
MINUS
INTERSECT
2.  Write one query to return the employee_id, job_id, hire_date, and department_id of all employees and a second query listing employee_id, job_id, start_date, and department_id from the job_hist table and combine the results as one single output. Make sure you sup-press duplicates in the output.


SELECT employee_id, job_id, hire_date, TO_DATE(NULL)  start_date, department_id
FROM employees
UNION
SELECT employee_id, job_id, TO_DATE(NULL), start_date,  department_id
FROM job_history
ORDER BY employee_id, hire_date, start_date NULLS FIRST;

But after reading next problem I changed it to:
SELECT employee_id, job_id, hire_date, department_id
FROM employees
UNION
SELECT employee_id, job_id,  start_date,  department_id
FROM job_history
ORDER BY employee_id, hire_date;
29 rows returned

3.  Amend the previous statement to not suppress duplicates and examine the output. How many extra rows did you get returned and which were they? Sort the output by employee_id to make it easier to spot.
SELECT employee_id, job_id, hire_date, department_id
FROM employees
UNION ALL
SELECT employee_id, job_id,  start_date,  department_id
FROM job_history
ORDER BY employee_id, hire_date;

30 rows returned

The duplicate:

This can be verified by:
SELECT employee_id, job_id, hire_date, department_id, COUNT(*)
FROM
(SELECT employee_id, job_id, hire_date, department_id
FROM employees
UNION ALL
SELECT employee_id, job_id,  start_date,  department_id
FROM job_history)
GROUP BY employee_id, job_id, hire_date, department_id
HAVING COUNT(*) > 1;


4.  List all employees who have not changed jobs even once. (Such employees are not found in the job_history table)
SELECT DISTINCT employee_id
FROM employees
MINUS
SELECT DISTINCT employee_id
FROM job_history;
15 rows returned

5.  List the employees that HAVE changed their jobs at least once.
SELECT DISTINCT employee_id
FROM employees
INTERSECT
SELECT DISTINCT employee_id
FROM job_history;
5 rows returned

6.  Using the UNION operator, write a query that displays the employee_id, job_id, and salary of ALL present and past employees. If a salary is not found, then just display a 0 (zero) in its place.

SELECT employee_id, job_id, NVL(salary, 0)
FROM employees
UNION
SELECT employee_id, job_id, 0
FROM job_history
ORDER BY employee_id;


24 Using ROLLUP and CUBE Operations and GROUPING SETS


  • ·         Used to create subtotals that roll up from the most detailed level to a grand total, following a grouping list specified in the clause-ROLLUP
  • ·         An extension to the GROUP BY clause like ROLLUP that produces cross-tabulation reports-CUBE
  • ·         Used to specify multiple groupings of data- GROUPING SETS

1.  Within the Employees table, each manager_id is the manager of one or more employees who each have a job_id and earn a salary. For each manager, what is the total salary earned by all of the employees within each job_id? Write a query to display the Manager_id, job_id, and total salary. Include in the result the subtotal salary for each manager and a grand total of all salaries.
SELECT  manager_id, job_id, SUM(salary) "total salary", GROUPING(manager_id), GROUPING(job_id)
FROM employees
GROUP BY ROLLUP(manager_id, job_id);

2.  Amend the previous query to also include a subtotal salary for each job_id regardless of the manager_id.
SELECT  manager_id, job_id, SUM(salary) "total salary", GROUPING(manager_id), GROUPING(job_id)
FROM employees
GROUP BY CUBE(manager_id, job_id);
Observe 12 new rows here:


3.  Using GROUPING SETS, write a query to show the following groupings:

• department_id, manager_id, job_id
• manager_id, job_id
• department_id, manager_id

SELECT department_id, manager_id, job_id, SUM(salary) "total salary",  GROUPING(department_id),  GROUPING(manager_id), GROUPING(job_id)
FROM employees
GROUP BY GROUPING SETS((department_id, manager_id, job_id), (manager_id, job_id), (department_id, manager_id));



23 Using GROUP BY and HAVING Clauses


  • ·         Used to specify which groups are to be displayed; restricts groups that do not meet group criteria-HAVING
  • ·         Divides the rows in a table into groups-GROUP BY

1.  In the SQL query shown below, which of the following is true about this query?

_______ a.  Kimberly Grant would not appear in the results set.
TRUE
_______ b.  The GROUP BY clause has an error because the manager_id is not listed in the SELECT clause.
FALSE – GROUP function require that any column listed in the SELECT clause that is not part of group function must be listed in a GROUP BY Clause. But it doesn’t mandate presence of all columns in the group by clause in SELECT.
_______ c.  Only salaries greater than 16001 will be in the result set.
FALSE. Greater than or equal to 16001 may must come here. That too, ‘16001 or greater will come here’ only if the row is not filtered by WHERE before group application, AND also the whole group satisfy the HAVING clause.
_______ d.  Names beginning with Ki will appear after names beginning with Ko.
FALSE,  Names here means the last_name. ‘i’ is before  ‘o’ if DESC is skipped.
_______ e.  Last names such as King and Kochhar will be returned even if they don’t have salaries > 16000.
FALSE. The WHERE is applied even before the GROUP BY.
SELECT last_name, MAX(salary)
FROM employees
WHERE last_name LIKE 'K%'
GROUP BY manager_id, last_name
HAVING MAX(salary) >16000
ORDER BY last_name DESC ;


2.  Each of the following SQL queries has an error. Find the error and correct it. Use Oracle Application Express to verify that your corrections produce the desired results.

a.  SELECT manager_id
FROM employees
WHERE AVG(salary) <16000
GROUP BY manager_id;
Above query will say ORA-00934: group function is not allowed here.
If 'desired' result is getting manger ids, under whom, average salary is less than 16000:

SELECT manager_id,AVG(salary)
FROM employees
GROUP BY manager_id
HAVING AVG(salary) <16000;


b.  SELECT cd_number, COUNT(title)
FROM d_cds
WHERE cd_number < 93;
Above query will say ORA-00937: not a single-group group function

If ‘desired’ result is getting row count, excluding anything greater than or equal to 93:
SELECT COUNT(*)
FROM d_cds
WHERE cd_number < 93;



c.  SELECT ID, MAX(ID), artist AS Artist
FROM d_songs
WHERE duration IN('3 min', '6 min', '10 min')
HAVING ID < 50
GROUP by ID;

Above query will say ORA-00979: not a GROUP BY expression

id is primary key of the table, so most likely my intention may not be to group by id here.
Desired may have been grouping by type_code and getting maximum duration
but limiting the input to group by with duration IN('3 min', '6 min', '10 min')  and ID < 50

SELECT type_code, MAX(TO_NUMBER(REPLACE(duration,' min',''))) || ' min' as "max duration"
FROM d_songs
WHERE duration IN('3 min', '6 min', '10 min') AND id < 50
GROUP BY type_code;



d.  SELECT loc_type, rental_fee AS Fee
FROM d_venues
WHERE id <100
GROUP BY "Fee"
ORDER BY 2;
1) Above query will say ORA-00904: "Fee": invalid identifier
2) Even if I replace "Fee" alias in group by [don't use alias in group by clause] it will give me error : ORA-00979: not a GROUP BY expression
3) Seems to be question wanted average rental_fee of each loc_type with id less than 100 before grouping.
I assumed rental_fee  is a number field:
SELECT loc_type, AVG(rental_fee) AS Fee
FROM d_venues
WHERE id <100
GROUP BY loc_type
ORDER BY 2;
But above query gives error ORA-01722: invalid number, because rental_fee is a VARCHAR2(50)
4) Now, I need assumptions to view the calculations with same eyes: /hour, /flat fee, /per person.
Let’s assume that we are group of 10 people and event I am organizing occurs for 5 hours.

SELECT loc_type, AVG(
CASE
 WHEN INSTR(rental_fee, '/hour') != 0 THEN  TO_NUMBER(REPLACE(rental_fee,'/hour',''))*5
  WHEN INSTR(rental_fee, '/flat fee') != 0 THEN  TO_NUMBER(REPLACE(rental_fee,'/flat fee',''))
  WHEN INSTR(rental_fee, '/per person') != 0 THEN  TO_NUMBER(REPLACE(rental_fee,'/per person',''))*10
 ELSE 0
 END
) AS Fee
FROM d_venues
WHERE id <100
GROUP BY loc_type
ORDER BY 2;



3.  Rewrite the following query to accomplish the same result:

SELECT DISTINCT MAX(song_id)
FROM d_track_listings
WHERE track IN ( 1, 2, 3);
SELECT track, MAX(song_id)
FROM d_track_listings
WHERE track IN ( 1, 2, 3)
GROUP BY track;



4.  Indicate True or False

_____ a.  If you include a group function and any other individual columns in a SELECT clause, then each individual column must also appear in the GROUP BY clause.
TRUE

_____ b.  You can use a column alias in the GROUP BY clause.
FALSE.  query will say ORA-00904: "Some Alias": invalid identifier
_____ c.  The GROUP BY clause always includes a group function.
FALSE- GROUP BY clause of SQL statement contains the column name- value of which is used to divide input into subgroups, each subgroup represented as a row in output. The group function- goes in SELECT part. HAVING clause may also contain group function. If I put group function in group by clause I get error ‘ORA-00934: group function is not allowed here’ e.g. SELECT AVG(salary) FROM employees GROUP BY AVG(salary) HAVING AVG(salary) <16000; will give error.
5.  Write a query that will return both the maximum and minimum average salary grouped by department from the employees table.
SELECT ROUND(MAX(AVG(salary)),2) as "Maximum Average of Departments", ROUND(MIN(AVG(salary)),2) "Minimum Average of Departments"
FROM employees
GROUP BY department_id;



Verify MAX and MIN working against:



6.  Write a query that will return the average of the maximum salaries in each department for the employees table.

SELECT AVG(MAX(salary))
FROM employees
GROUP BY department_id;




Verify AVG working against: