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

21 Group Functions

• ·         Calculates average value excluding nulls- AVG group function
• ·         Returns the number of rows with non-null values for the expression- COUNT  group function
• ·         For two sets of data with approximately the same mean, the greater the spread, the greater the standard deviation.- STDDEV group function
• ·         Operate on sets of rows to give one result per group- any group function
• ·         Returns minimum value ignoring nulls- MIN group function
• ·         Used with columns that store numeric data to calculate the spread of data around the mean- VARIANCE group function
• ·         Calculates the sum ignoring null values- SUM group function
• ·         Returns the maximum value ignoring nulls- MAX group function
• ·         To gather into a sum or whole- Aggregate functions return a single result row based on groups of rows. Aggregate is something that is formed by combining several separate elements.

1.  Define and give an example of the seven group functions: AVG, COUNT, MAX, MIN, STDDEV, SUM, and VARIANCE.

AVG,  COUNT :

·         Calculates average value excluding nulls- AVG group function

·         Returns the number of rows with non-null values for the expression- COUNT  group function

SELECT  AVG(salary) || ' is Average Salary of ' || COUNT(salary) || ' employees. This table has primary key employee_id which won''t be null. So, ' || (COUNT(employee_id) - COUNT(salary)) || ' rows are skipped in  Average Salary calculation.' "Example"

FROM EMPLOYEES;

 (I may use COUNT(*) instead of COUNT(employee_id), if I want to)

Output:

8775 is Average Salary of 20 employees. This table has primary key employee_id which won't be null. So, 0 rows are skipped in Average Salary calculation.

Please note that in this concatenation and minus has same precedence. So () were required.

MAX, MIN:

·         Returns minimum value ignoring nulls- MIN group function

·         Returns the maximum value ignoring nulls- MAX group function

SELECT 'The maximum of ' || COUNT(salary) || ' salaries in employees table is ' || MAX(salary) ||'. The minimum of '|| COUNT(salary) || ' salaries in employees table is '|| MIN(salary) ||'.' "Example2"

FROM EMPLOYEES;

Output:

The maximum of 20 salaries in employees table is 24000. The minimum of 20 salaries in employees table is 2500.

STDDEV, VARIANCE:

·         Used with columns that store numeric data to calculate the spread of data around the mean- VARIANCE group function

·         For two sets of data with approximately the same mean, the greater the spread, the greater the standard deviation.- STDDEV group function

SELECT 'The standard deviation of ' || COUNT(salary) || ' salaries in employees table is ' || ROUND(STDDEV(salary), 4) ||'. The variance of '|| COUNT(salary) || ' salaries in employees table is '|| ROUND(VARIANCE(salary), 4) ||'.' "Example3"

FROM EMPLOYEES;

Output:

The standard deviation of 20 salaries in employees table is 5659.6331. The variance of 20 salaries in employees table is 32031447.3684.

If not rounded the values would have been inconvenient to read.

SUM:

·         Calculates the sum ignoring null values- SUM group function

SELECT 'The sum of ' || COUNT(salary) || ' salaries in employees table is ' || SUM(salary) ||'.' "Example4"

FROM EMPLOYEES;

Output:

The sum of 20 salaries in employees table is 175500.

2.  Create a query that will show the average cost of the DJs on Demand events.  Round to two decimal places.

SELECT ROUND(AVG(cost),2) as "Average Cost"

FROM d_events;

9000

3.  Find the average salary for Global Fast Foods staff members whose manager ID is 19.

SELECT TO_CHAR(ROUND(AVG(salary),2), '$999999.99') as "Average Salary"

FROM f_staffs

WHERE manager_id = 19;

$8.38

4.  Find the sum of the salaries for Global Fast Foods staff members whose IDs are 12 and 9.

SELECT TO_CHAR(ROUND(SUM(salary),2), '$999999.99') as "Total Salary"

FROM f_staffs

WHERE id in (12, 19);

$66.75

5.  Using the Oracle database, select the lowest salary, the most recent hire date, the last name of the person who is at the top of an alphabetical list of employees, and the last name of the person who is at the bottom of an alphabetical list of employees. Select only employees who are in departments 50 or 60.

SELECT MIN(salary) "lowest salary", MAX(hire_date) "most recent hire date", MIN(last_name) "top last name", MAX(last_name) "bottom last name"

FROM employees

WHERE department_id in (50, 60);

6.  Your new Internet business has had a good year financially. You have had 1,289 orders this year. Your customer order table has a column named total_sales. If you submit the following query, how many rows will be returned?

SELECT sum(total_sales)

FROM orders;

one

7.  You were asked to create a report of the average salaries for all employees in each division of the company. Some employees in your company are paid hourly instead of by salary. When you ran the report, it seemed as though the averages were not what you expected—they were much higher than you thought! What could have been the cause?

SELECT AVG(NVL(salary, hourly_rate* hrs_worked_in_yr  ))

This way the null fields beings ignored will also be counted in.

8.  Employees of Global Fast Foods have birth dates of July 1, 1980, March 19, 1979, and March 30, 1969. If you select MIN(birthdate), which date will be returned?

March 30, 1969

9.  Create a query that will return the average order total for all Global Fast Foods orders from January 1, 2002, to December 21, 2002.

SELECT 'Average of ' || COUNT(order_number) || '  orders is : ' || AVG(NVL(order_total, 0)) as "Average"

FROM f_orders

WHERE order_date BETWEEN TO_DATE('January 1, 2002', 'fmMonth DD, YYYY') AND TO_DATE('December 21, 2002', 'fmMonth DD, YYYY');

Average of 1 orders is : 103.02

(I may use COUNT(*) instead of COUNT(order_number), if I want to)

10. What was the hire date of the last Oracle employee hired?

SELECT MAX(hire_date) as "the last"

FROM employees;

29-Jan-2000

11. In the following SELECT clause, which value returned by the SELECT statement will be larger?

SELECT SUM(operating_cost), AVG(operating_cost)

SUM must be  be ‘equal or greater than’ average.

12. Refer to the DJs on Demand database D_EVENTS table:

Which clauses represent valid statements?

_______a.  FROM event_date

FALSE, this is a column. It will say ORA-00942: table or view does not exist

_______b.  SELECT SUM(cost)

TRUE

_______c.  SELECT SUM(event_date)

FALSE. This column is not a number.  ORA-00932: inconsistent datatypes: expected NUMBER got DATE

_______d.  SELECT description, AVG(cost) AS "Expense"

FALSE.  ORA-00937: not a single-group group function. Remove either description (to get single row output), or remove avg(cost)

_______e.  WHERE MIN(id) = 100

FASLE.  MIN is a group function.  ORA-00934: group function is not allowed here

_______f.  SELECT MAX(AVG(cost)

FALSE. ORA-00978: nested group function without GROUP BY

_______g.  SELECT MIN(event_date)

TRUE

20 Oracle Nonequijoins and Outer Joins

1.  Create a join based on the cost of the event between the DJs on Demand tables D_EVENTS and D_PACKAGES. Show the name of the event and the code for each event.

Nonequi join only:

SELECT  d_events.name, d_packages.code

FROM d_events, d_packages

WHERE(d_events.cost BETWEEN d_packages.low_range AND d_packages.high_range);

Above is OK if all the events have cost covered in available ranges.

Nonequi along with left outer equivalent in oracle:

SELECT  d_events.name, d_packages.code

FROM d_events, d_packages

WHERE  d_events.cost >= d_packages.low_range (+) AND d_events.cost <= d_packages.high_range(+);

In this case, if I change cost out of available range, even then event is not skipped.

2.  Using the Oracle database, create a query that returns the employee last name, salary, and job-grade level based on the salary. Select the salary between the lowest and highest salaries.

I am assuming that ranges in job_grades cover all employees, I only need simple nonequi join here.

SELECT employees.last_name, employees.salary, job_grades.grade_level

FROM employees,job_grades

WHERE employees.salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal;

3.  What condition requires the creation of a nonequijoin?

When there is no exact match (=) between columns of two tables, but still there is a relation hidden.

4.  Rewrite the following nonequijoin statement using the logical condition operators (AND, OR, NOT): WHERE a.ranking BETWEEN g.lowest_rank AND g.highest_rank

WHERE  a.ranking >= g.lowest_rank AND a.ranking <= g. highest_rank

5. How do you know when to use a table alias and when not to use a table alias?

·         Alias for table becomes must when query targets recursive relationships. -Must

·         If two columns have similar named columns and I want to identify them separately, even though I can use table full name, but alias is helpful. Alias helps to identify table with smaller word.- good to do

·         But once alias is used for table, it must have to be used throughout.--must take care of

6.  What kind of join would you use if you wanted to find data between a range of numbers?

Nonequi join.

7.  You need to produce a report for Global Fast Foods showing customers and orders. A customer must be included on the report even if the customer has had no orders.

SELECT f_customers.first_name ||' '|| f_customers.last_name "Customer Name", f_orders.order_number, f_orders.order_total, f_orders.order_date

FROM f_customers, f_orders

WHERE  f_customers.id = f_orders.cust_id(+);

This is oracle proprietary equivalent of LEFT OUTER JOIN

8.  Create a query of the Oracle database that shows employee last names, department IDs, and department names. Include all employees even if they are not assigned to a department.

SELECT employees.last_name, employees.department_id, departments.department_name

FROM employees, departments

WHERE employees.department_id = departments.department_id(+);

This is oracle proprietary equivalent of LEFT OUTER JOIN

9.  Modify the query in problem 2 to return all the department IDs even if no employees are assigned to them.

SELECT employees.last_name, employees.department_id, departments.department_name

FROM employees, departments

WHERE employees.department_id(+) = departments.department_id;

This is oracle proprietary equivalent of RIGHT OUTER JOIN

10. There are one or more errors in each of the following statements. Describe the errors and correct them.

a.  WHERE e.department_id(+) = d.department_id (+);

ORA-01468: a predicate may reference only one outer-joined table

There is no direct oracle equivalent for FULL OUTER JOIN.

FIX:

WHERE e.department_id  = d.department_id (+);

WHERE e.department_id(+) = d.department_id;

If I do

WHERE employees.department_id = departments.department_id(+) OR

employees.department_id(+) = departments.department_id;

I will still get

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Fix2 A set operator:

SELECT employees.last_name, employees.department_id, departments.department_name

FROM employees, departments

WHERE employees.department_id = departments.department_id(+)

UNION

SELECT employees.last_name, employees.department_id, departments.department_name

FROM employees, departments

WHERE employees.department_id(+) = departments.department_id ;

b.  SELECT e.employee id, e. last name, d. location id

FROM employees, departments

WHERE e.department_id = d.department_id(+);

·         Column names are wrong,

·         Table alias is used but not assigned to tables.

FIX:

SELECT e.employee_id, e.last_name, d.location_id

FROM employees e , departments d

WHERE e.department_id = d.department_id(+);

11. Create a query that will show all CD titles and song IDs in the DJs on Demand database even if there is no CD number in the track-listings table.

SELECT d_cds.title , d_track_listings.song_id

FROM  d_cds, d_track_listings

WHERE d_cds.cd_number = d_track_listings.cd_number(+) ;

12. How many times has someone asked you: “What do you want to be when you grow up?” For most of us, the first thing that comes to mind is something like business manager, engineer, teacher, game designer, doctor, scientist, computer programmer, or accountant -- all pretty much traditional career choices. Have you ever thought about working in an odd job or nontraditional career? There are people who are professional shoppers for busy executives, directors of zoos, recipe designers, insecticide chemists, golf-course designers, and turf managers. Picture yourself in a dream job or nontraditional career doing something that you think would be interesting, life fulfilling, and profitable.

Use Internet resources to explore your idea. Write a brief description of the job to share with the class.

This is out of scope for current course at virtual college.

19 Oracle Equijoin and Cartesian Product

• ·         Results from an invalid or omitted join condition; all combinations of rows are displayed- Cartesian Product
• ·         Values in a column in one table are equal to a value in another table; also called an inner join or simple join-equijoin
• ·         Connection command exclusive to a specific company-proprietary join
• ·         Gives a table another name to simplify queries and improve performance-alias
• ·         Display data from two or more related tables-join conditions

1.  Create a Cartesian product that displays the columns in the d_play_list_items and the d_track_listings in the DJs on Demand database.

SELECT d_play_list_items.event_id "event id in playlist", d_play_list_items.song_id "song id in playlist", d_play_list_items.comments "comments in playlist", d_track_listings.song_id "song id in tracklist", d_track_listings.cd_number "cd number in tracklist",  d_track_listings.track "track in tracklist"

FROM  d_play_list_items, d_track_listings;

2.  Correct the Cartesian product produced in question 1 by creating an equijoin using a common column.

SELECT d_play_list_items.event_id "event id in playlist", d_play_list_items.song_id "song id in playlist", d_play_list_items.comments "comments in playlist", d_track_listings.song_id "song id in tracklist", d_track_listings.cd_number "cd number in tracklist",  d_track_listings.track "track in tracklist"

FROM  d_play_list_items, d_track_listings

WHERE d_play_list_items.song_id = d_track_listings.song_id;

3.  Write a query to display the title, type, description, and artist from the DJs on Demand database.

SELECT  d_songs.title,  d_songs.type_code type, d_types.description

FROM d_songs, d_types

WHERE d_songs.type_code = d_types.code;

4.  Rewrite the query in question 3 to select only those titles with an ID of 47 or 48.

SELECT  d_songs.title,  d_songs.type_code type, d_types.description

FROM d_songs, d_types

WHERE d_songs.type_code = d_types.code AND d_songs.id in (47, 48);

5.  Write a query that extracts information from three tables in the DJs on Demand database, the d_clients table, the d_events table, and the d_job_assignments table.

SELECT d_clients.email AS "d_clients - email" , d_clients.phone AS "d_clients - phone" , d_clients.last_name AS "d_clients - last_name" , d_clients.first_name AS "d_clients - first_name"  , d_clients.client_number AS "d_clients - client_number"  , d_events.id AS "d_events - id", d_events.name AS "d_events - name", d_events.event_date AS "d_events - event_date", d_events.description AS "d_events - description", d_events.cost AS "d_events - cost", d_events.venue_id AS "d_events - venue_id", d_events.package_code AS "d_events - package_code", d_events.theme_code AS "d_events - theme_code", d_events.client_number AS "d_events - client_number", d_job_assignments.partner_id AS "d_job_assignments - partner_id", d_job_assignments.event_id AS "d_job_assignments - event_id", d_job_assignments.job_date AS "d_job_assignments - job_date", d_job_assignments.status AS "d_job_assignments - status"

FROM d_clients, d_events, d_job_assignments

WHERE d_clients.client_number = d_events.client_number AND d_events.id =  d_job_assignments.event_id;

Why not Cartesian product here (by skipping where): assignments are made for events And events are for client. All the permutation rows, if returned give virtual data, which actually can never happen.

  But what if I do equijoin as above mentioned [ equivalent to INNER JOIN], I loose information that there are few events for which assignment didn’t happen yet and there is a client ,  for which there is no event yet, And I want to list it: for that in oracle propriety conventions (+) will be used in future clusters of practices. E.g.  WHERE d_clients.client_number = d_events.client_number(+) AND d_events.id =  d_job_assignments.event_id(+);

6.  Create and execute an equijoin between DJs on Demand tables d_track_listings and d_cds. Return the song_id and the title only.

SELECT d_track_listings.song_id "song id in tracklist", d_cds.title "title in cds"

FROM d_track_listings, d_cds

WHERE d_track_listings.cd_number = d_cds.cd_number;

7.  Mark T for the statements that are true and F for the statements that is false.

____ a.  A join is a type of query that gets data from more than one table based on columns with the same name. False.  that is only a natural join, statement doesn’t cover everything

____ b.  To join tables using an equijoin, there must be a common column in both tables and that column is usually a primary key in one of the tables. True

____ c.  A Cartesian product occurs because the query does not specify a WHERE clause. TRUE

____ d.  Table aliases are required to create a join condition. FALSE

____ e.  If a table alias is used for a table name in the FROM clause, it must be substituted for the table name throughout the SELECT statement. TRUE

____ f.  Table alias must be only one character in length.-FALSE

____ g.  A simple join or inner join is the same as an equijoin.-TRUE. Study material says: Equijoin- Sometimes called a "simple" or "inner" join; an equijoin is a table join that combines rows that have the same values for specifies columns

8.  What advantage does being able to combine data from multiple tables have for a business?

Business doesn’t care where I store data or how I retrieve it. In Relational DB, we store the data in different tables related to each other. And since we can combine data from multiple tables following these relations, we get useful information as output which is the purpose of DB’s existence. 

18 Self Joins and Hierarchical Queries

·         Joins a table to itself-self join

·         Retrieves data based on a natural hierarchical relationship between rows in a table- hierarchical query

·         Determines the number of steps down from the beginning row that should be returned by a hierarchical query-LEVEL

·         Identifies the beginning row for a hierarchical query-START WITH

·         Specifies the relationship between parent rows and child rows of a hierarchical query-CONNECT BY  ( PRIOR on either side of equal sign e.g. …PRIOR abc = xyz  /  abc = PRIOR  xyz)

For each problem, use the Oracle database.

1.  Display the employee’s last name and employee number along with the manager’s last name and manager number. Label the columns: Employee, Emp#, Manager, and Mgr#, respectively.

SELECT emp.last_name "Employee", emp.employee_id "Emp#", mgr.last_name "Manager", mgr.employee_Id  "Mgr#"

FROM  employees emp LEFT OUTER JOIN  employees  mgr  ON emp.manager_id  = mgr.employee_Id; 

But Reading next question suggests, problem wanted INNER JOIN instead, to demonstrate the difference J

SELECT emp.last_name "Employee", emp.employee_id "Emp#", mgr.last_name "Manager", mgr.employee_Id  "Mgr#"

FROM  employees emp INNER JOIN  employees  mgr  ON emp.manager_id  = mgr.employee_Id; 

2.  Modify question 1 to display all employees and their managers, even if the employee does not have a manager. Order the list alphabetically by the last name of the employee.

SELECT emp.last_name "Employee", emp.employee_id "Emp#", mgr.last_name "Manager", mgr.employee_Id  "Mgr#"

FROM  employees emp LEFT OUTER JOIN  employees  mgr  ON emp.manager_id  = mgr.employee_Id

ORDER BY  "Employee";

3.  Display the names and hire dates for all employees who were hired before their managers, along with their managers’ names and hire dates. Label the columns Employee, Emp Hired, Manager and Mgr Hired, respectively.

If I want to include King who has no manager, and he is the super boss:

SELECT emp.last_name "Employee",  emp.hire_date "Emp Hired",   mgr.last_name "Manager", mgr.hire_date "Mgr Hired"

FROM  employees emp LEFT OUTER JOIN  employees  mgr  ON emp.manager_id  = mgr.employee_Id

WHERE emp.hire_date < NVL( mgr.hire_date, TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'))

ORDER BY  "Employee";

If I don’t care for King, which is the most, probably scenario problem refers:

SELECT emp.last_name "Employee",  emp.hire_date "Emp Hired",   mgr.last_name "Manager", mgr.hire_date "Mgr Hired"

FROM  employees emp LEFT OUTER JOIN  employees  mgr  ON emp.manager_id  = mgr.employee_Id

WHERE emp.hire_date < mgr.hire_date

ORDER BY  "Employee";

4.  Write a report that shows the hierarchy for Lex De Haans department. Include last name, salary, and department id in the report.

SELECT last_name, salary, department_id

FROM employees

START WITH first_name = 'Lex'  AND last_name = 'De Haan'

CONNECT BY PRIOR employee_id = manager_id;

5.  What is wrong in the following statement?

SELECT last_name, department_id, salary

FROM employees

START WITH last_name = 'King'

CONNECT BY PRIOR manager_id = employee_id;

Query in problem says go from tree leave to tree base, but King is itself the base, so the result of query in problem is only one row, the king itself.  So there, is nothing wrong as per SQL in the query in problem, but logically thinking considering the data we have, king is the super boss.

To support my answer, let’s study similar case:

SELECT LEVEL, last_name, department_id, salary

FROM employees

START WITH first_name = 'Lex'  AND last_name = 'De Haan'

CONNECT BY PRIOR manager_id = employee_id;

Gives same result as

SELECT LEVEL, last_name, department_id, salary

FROM employees

START WITH first_name = 'Lex'  AND last_name = 'De Haan'

CONNECT BY  employee_id = PRIOR manager_id;

6.  Create a report that shows the organization chart for the entire employee table. Write the report so that each level will indent each employee 2 spaces. Since Oracle Application Express cannot display the spaces in front of the column, use - (minus) instead.

SELECT LPAD(last_name, LENGTH(last_name) + (LEVEL-1)*2, '-') "organization chart "

FROM employees

START WITH last_name = ( SELECT last_name from employees WHERE manager_id IS NULL)

CONNECT BY PRIOR employee_id = manager_id;

Super boss will be at level 1, not the zero.

7.  Re-write the report from 6 to exclude De Haan and all the people working for him.

SELECT LPAD(last_name, LENGTH(last_name) + (LEVEL-1)*2, '-') "organization chart "

FROM employees

START WITH last_name = ( SELECT last_name from employees WHERE manager_id IS NULL)

CONNECT BY PRIOR employee_id = manager_id AND last_name != 'De Haan';

17 Inner versus Outer Joins

• ·         Performs a join on two tables, retrieves all the rows in the Left table, even if there is no match in the Right table. It also retrieves all the rows in the Right table, even if there is no match in the Left table. FULL OUTER JOIN
• ·         A join that returns the unmatched rows as well as matched rows- outer join
• ·         Performs a join on two tables, retrieves all the rows in the Left table even if there is no match in the Right table. -LEFT OUTER JOIN
• ·         Performs a join on two tables, retrieves all the rows in the Right table even if there is no match in the Left table. –RIGHT  OUTER JOIN
• ·         A join of two or more tables that returns only matched rows-INNER JOIN

Use the Oracle database for problems 1-7.

1.   Return the first name, last name, and department name for all employees including those employees not assigned to a department.

SELECT  emp.first_name "First Name", emp.last_name "Last Name" , dpt.department_name  "Department Name"

FROM  employees emp LEFT OUTER JOIN departments dpt ON emp.department_id = dpt.department_id;

2.  Return the first name, last name, and department name for all employees including those departments that do not have an employee assigned to them.

After reading next problem, I assume, that problem, says to miss an employee, if it doesn’t have a department assigned.

SELECT  emp.first_name "First Name", emp.last_name "Last Name" , dpt.department_name  "Department Name"

FROM  employees emp RIGHT OUTER JOIN departments dpt ON emp.department_id = dpt.department_id;

OR

SELECT  emp.first_name "First Name", emp.last_name "Last Name" , dpt.department_name  "Department Name"

FROM  departments dpt  LEFT OUTER JOIN employees emp ON  dpt.department_id = emp.department_id;

3.  Return the first name, last name, and department name for all employees including those departments that do not have an employee assigned to them and those employees not assigned to a department.

SELECT  emp.first_name "First Name", emp.last_name "Last Name" , dpt.department_name  "Department Name"

FROM  employees emp FULL OUTER JOIN departments dpt ON emp.department_id = dpt.department_id;

4.  Create a query of the DJs on Demand database to return the first name, last name, event date, and description of the event the client held. Include all the clients even if they have not had an event scheduled.

SELECT ct.first_name, ct.last_name, ev.event_date, ev.description

FROM  d_clients ct LEFT OUTER JOIN d_events ev ON ct.client_number = ev.client_number;

5.  Using the Global Fast Foods database, show the shift description and shift assignment date even if there is no date assigned for each shift description.

SELECT f_shifts.description "shift description", f_shift_assignments.shift_assign_date AS "shift assignment date"

FROM  f_shifts LEFT OUTER JOIN f_shift_assignments ON f_shifts.code = f_shift_assignments.code;