Labs 1: Starting with the Virtual Machine PDF

Structured Query Language Labs 1: Starting with the Virtual Machine Labs 1: Starting with the Virtual Machine Preview The tool First use Some commands Labs 1: Starting with the Virtual Machine The tool The Virtual Machine Name ORA_WI...

Structured Query Language Labs 1: Starting with the Virtual Machine Labs 1: Starting with the Virtual Machine Preview The tool First use Some commands Labs 1: Starting with the Virtual Machine The tool The Virtual Machine Name ORA_WIN_10gR1_v2.5 (or greater) SUPINFO courses Where Labs/Oracle Local Sharing OS version Microsoft Windows XP SP1 VL Oracle Oracle Database 10gR1 version OS Username: oracle credentials Password: oracle Oracle Practices: oracle/oracle credentials Graded exercises: olvoyage/oracle Labs 1: Starting with the Virtual Machine First use Download the Virtual Machine on SUPINFO Sharing Center No configuration needed Just start VMWare Player with files extracted from the RAR file. 1. Log-in yourself into Windows 2. Start either SQL*Plus or iSQL*Plus Your Virtual Machine is fully functional! Labs 1: Structured Query Language Quiz Who created Oracle Corporation? Dr. Edgar F. Codd Linus Torvald Larry Ellison In 1977 with Bob Miner and Ed Oates Jeffrey O. Henley Charles Phillips Labs 1 : Structured Query Language Quiz Spot the odd one out. Why? Oracle Database Microsoft Office Access IBM DB2 Microsoft SQL Server Sybase PostgreSQL Interbase dBase MySQL SQLite All these databases are relational excepted dBase. Labs 1 : Structured Query Language Quiz Which are DML? SELECT ALTER INSERT DROP ROLLBACK Labs 1 : Structured Query Language Quiz Fill in the blanks: To put data in my table, I can use the ___________ INSERT order. Dr Edgar F. Codd defined the _________. RDBMS The CREATE order is a Data Definition _________ Language. S_________ tructured Query Language Basic Orders Labs 2: Basic Orders Labs 2: Basic Orders SELECT Statement - Exercise 1. Does iSQL*Plus commands access the database? 2. Does the following SELECT statement executes successfully? SELECT last_name, job_id, salary AS Sal FROM employees; 3. Does the following SELECT statement executes successfully? SELECT * FROM job_grades; 4. There are four coding mistakes in this statement: SELECT employee_id, last_name sal x 12 ANNUAL SALARY FROM employees; Labs 2: Basic Orders SELECT Statement - Correction 1. FALSE 2. TRUE 3. TRUE 4. The EMPLOYEES table does not contain a column called sal; the multiplication operator is “*”, not “x”; the “ANNUAL SALARY” alias cannot include spaces. The alias should be enclosed in double quotation marks ; a comma is missing after the LAST_NAME column. Labs 2: Basic Orders SELECT Statement - Exercise You have been hired as SQL programmer for OLCorp. Your first task is to create some reports based on data from the Human Resources tables. 5. You first have to determine the structure of the DEPARTMENTS table and its content. 6. Then, you need to determine the structure of the EMPLOYEES table. 7. The HR department wants a query to display the last name, job code, hire date, and employee number for each employee, with employee number appearing first. Provide an alias STARTDATE for the hire_date column. Save your SQL statement to a file name lab_02_01.sql so that you can disperse this file to the HR department. Labs 2: Basic Orders SELECT Statement - Correction 5. DESCRIBE departments SELECT * FROM departments; 6. DESC employees 7. SELECT employee_id, last_name, job_id, hire_date STARTDATE FROM employees; SAVE C:\labs\lab_01_01.sql Labs 2: Basic Orders SELECT Statement - Exercise 8. The HR department needs a query to display all unique job codes from the EMPLOYEES table. 9. The HR department wants more descriptive column headings for its report on employees. Get the statement from lab_02_01.sql in iSQL*Plus. Name the column headings Emp #, Employee and Title (case-sensitive). 10. The HR department has requested a report of all employees and their job IDs. Display the last name concatenated with the job ID (separated by comma and space) and name the column “Employee and Title”. 11. To familiarize yourself with the data in the EMPLOYEES table, create a query to display all the data from the EMPLOYEES table. Separate each column output by a comma. Name the column title THE_OUTPUT. Labs 2: Basic Orders SELECT Statement - Correction 8. SELECT DISTINCT job_id FROM employees; 9. GET C:\labs\lab_01_01.sql SELECT employee_id "Emp #", last_name "Employee", job_id "Job", hire_date "Hire Date" FROM employees; 10. SELECT last_name||' ,'||job_id "Employee and Title" FROM employees; Labs 2: Basic Orders SELECT Statement - Correction 11. SELECT employee_id ||', '|| first_name ||', '|| last_name ||', '|| email ||', '|| phone_number ||', '|| job_id ||', '|| manager_id ||', '|| hire_date ||', '|| salary ||', '|| commission_pct ||', '|| department_id THE_OUTPUT FROM employees; Labs 2: Basic Orders Restricting and sorting data - Exercise 1. Due to budget issues, the HR department needs a report that displays the last name and salary of employees earning more that $12,000. Save your statement in lab_02_02.sql. 2. Create a report that displays the last name and department number for employee number 176. 3. The HR departments needs to find high-salary and low- salary employees. Modify lab_02_02.sql to display the last name and salary for all employees whose salary is not in the range of $5,000 to $12,000. Save your statement in lab_01_03.sql. Labs 2: Basic Orders Restricting and sorting data - Correction 1. SELECT last_name, salary FROM employees WHERE salary > 12000; 2. SELECT last_name, department_id FROM employees WHERE employee_id = 176; 3. SELECT last_name, salary FROM employees WHERE salary NOT BETWEEN 5000 AND 12000; Labs 2: Basic Orders Restricting and sorting data - Exercise 4. Create a report to display the last name, job id, and start date for the employees with the last names of Matos and Taylor. Order the query in ascending order by start date. 5. The HR department needs your assistance with creating some queries. Display the last name and department number of all employees in department 20 or 50 in ascending alphabetical order by name. 6. Modifify lab_02_03.sql to list the last name and salary of employees who earn between $5,000 and $12,000 and are in department 20 or 50. Label the columns “Employee” and “Monthly Salary”, respectively. Resave your script as lab_02_04.sql. Labs 2: Basic Orders Restricting and sorting data - Correction 4. SELECT last_name, job_id, hire_date FROM employees WHERE last_name IN ('Matos', 'Taylor') ORDER BY hire_date; 5. SELECT last_name, department_id FROM employees WHERE department_id IN (20, 50) ORDER BY last_name ASC; 6. SELECT last_name "Employee", salary "Salary" FROM employees WHERE salary BETWEEN 5000 AND 12000 AND department_id IN (20, 50); Labs 2: Basic Orders Restricting and sorting data - Exercise 7. The HR department needs a report that displays the last name and hire date for all employees who were hired id 1994. 8. Create a report to display the last name and job title of all employees who do not have a manager. 9. Display the last name, salary, and commission for all employees who earn commissions. Sort data in descending order of salary and commissions. Labs 2: Basic Orders Restricting and sorting data - Correction 7. SELECT last_name, hire_date FROM employees WHERE hire_date LIKE '%94'; 8. SELECT last_name, job_id FROM employees WHERE manager_id IS NULL; 9. SELECT last_name, salary, commission_pct FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC, commission_pct DESC; Labs 2: Basic Orders Restricting and sorting data - Exercise 10. Create a report that displays the last name and salary of employees who earn more than an amount that the user specifies prompt. Save the query in lab_02_05.sql. 11. Display all employee last names in which the third letter of the name is “a”. 12. The HR department wants to run reports based on a manager. Create a query that prompts the user for a manager id and generates the employee id, last name, salary, and department for that manager’s employees. The HR department wants the ability to sort the report on a selected column. Test with the following values:  manager id = 103, sorted by employees’ last name  manager id = 201, sorted by salary  manager id = 124, sorted by employee id Labs 2: Basic Orders Restricting and sorting data - Correction 10. SELECT last_name, salary FROM employees WHERE salary < &sal_amount; 11. SELECT last_name FROM employees WHERE last_name LIKE '__a%'; 12. SELECT employee_id, last_name, salary, department_id FROM employees WHERE manager_id = &mgr_num ORDER BY &order_col; Labs 2: Basic Orders Restricting and sorting data - Exercise 13. Display the last name of all employees who have both an “a” and an “e” in their last name. 14. Display the last name, job, and salary for all employees whose job is sales representative (SA_REP) or stock clerk (ST_CLERK) and whose salary is not equal to $2,500, $3,500, or $7,000. 15. Modify lab_02_05.sql to display the last name, salary, and commission for all employees whose commission amount is 20%. Save your statement in lab_02_06.sql. Labs 2: Basic Orders Restricting and sorting data - Correction 13. SELECT last_name FROM employees WHERE last_name LIKE '%a%' AND last_name LIKE '%e%'; 14. SELECT last_name, job_id, salary FROM employees WHERE job_id IN ('SA_REP', 'ST_CLERK') AND salary NOT IN (2500, 3500, 7000); 15. SELECT last_name "Employee", salary "Salary", commission_pct FROM employees WHERE commission_pct =.20; Functions in SQL Labs 3: Functions in SQL Labs 3: Functions in SQL Single-row functions - Exercise 1. Write a query to display the current date. Label this column Date. 2. The HR department needs a report to display the employee number, last name, salary, and salary increased by 15.5% (expressed as whole number) for each employee. Label the column “New Salary”. Place your SQL statement in a text file named lab_03_01.sql. 3. Modify your previous script to add a column that subtracts the old salary form the new salary. Label the column Increase. Save as lab_03_02.sql. Labs 3: Functions in SQL Single-row functions - Correction 1. SELECT sysdate "Date" FROM dual; 2. SELECT employee_id, last_name, salary, ROUND(salary * 1.155, 0) "New Salary" FROM employees; 3. SELECT employee_id, last_name, salary, ROUND(salary * 1.155, 0) "New Salary" ROUND(salary * 1.155, 0) – salary "Increase" FROM employees; Labs 3: Functions in SQL Single-row functions - Exercise 4. Write a query that displays the last name (with the first letter uppercased and all other letters lowercased) and the length of the last name for all employees whose name starts with the letter J, A, or M. Give each column an appropriate label. Sort by employees’ last names. 5. Rewrite the query so that the user is prompted to enter a letter that starts the last name. For example, if the user enters H when prompted for a letter, then the output should show all employees whose last name starts with the letter H. 6. The HR dept’ wants to find the length of employment for each employee. Display the last name and calculate the number of months between today and the date on which the employee was hired. Order by the number of months worked. Round up to a whole number. Labs 3: Functions in SQL Single-row functions - Correction 4. SELECT INITCAP(lastname), LENGTH(last_name) FROM employees WHERE SUBSTR(last_name, 0, 1) IN ('J','M','A') ORDER BY last_name; 5. SELECT INITCAP(last_name), LENGTH(last_name) FROM employees WHERE last_name LIKE '&start_letter%' ORDER BY last_name; 6. SELECT last_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) FROM employees ORDER BY 2; Labs 3: Functions in SQL Single-row functions - Exercise 7. Create a report that produces the following for each employee: earns monthly but wants. Label the column “Dream Salaries”. 8. Create a query to display the last name and salary for all employees. Format the salary to be 15 characters long, left-padded with $ symbol. Label the column. 9. Display each employee’s last name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.”. Labs 3: Functions in SQL Single-row functions - Correction 7. SELECT last_name ||' earns '|| TO_CHAR(salary, 'fm$99,999.00') || ' monthly but wants '|| TO_CHAR(salary * 3, 'fm$99,999.00') FROM employees; 8. SELECT last_name, LPAD(salary, 15, '$') SALARY FROM employees; 9. SELECT last_name, hire_date, TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6),'MONDAY'), 'fmDay, "the" Ddspth "of" Month, YYYY') REVIEW FROM employees; Labs 3: Functions in SQL Single-row functions - Exercise 10. Display the last name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week, starting with Monday. 11. Create a query that displays the employees’ last names and commission amounts. If an employee does not earn commission, show “No Commission”. Label the column COMM. 12. Create a query that displays the first eight characters of the employees’ last names and indicates the amounts of their salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Label the column EMPLOYEES_AND_THEIR_SALARIES. Labs 3: Functions in SQL Single-row functions - Correction 10. SELECT last_name, hire_date, TO_CHAR(hire_date, 'DAY') DAY FROM employees ORDER BY TO_CHAR(hire_date-1, 'd'); 11. SELECT last_name, NVL(TO_CHAR(commission_pct), 'No Commission') FROM employees; 12. SELECT RPAD(last_name, 8) ||' '|| RPAD(' ', salary/1000+1, '*') EMPLOYEES_SALARY FROM employees ORDER BY salary DESC; Labs 3: Functions in SQL Single-row functions - Exercise 13. Using the CASE function, write a query that displays the grade of all employees based on the value of the column JOB_ID, using the following data: 14. Rewrite the statement in the preceding exercise using the CASE syntax. Labs 3: Functions in SQL Single-row functions - Correction 13. SELECT job_id, CASE job_id WHEN 'ST_CLERK' THEN 'E' WHEN 'SA_REP' THEN 'D' WHEN 'IT_PROG' THEN 'C' WHEN 'ST_MAN' THEN 'B' WHEN 'AD_PRES' THEN 'A' ELSE '0' END GRADE FROM employees; 14. SELECT job_id, DECODE(job_id, 'ST_CLERK', 'E', 'SA_REP', 'D', 'IT_PROG', 'C', 'ST_MAN', 'B', 'AD_PRES', 'A', '0') GRADE FROM employees; Labs 3: Functions in SQL Group functions - Exercise 1. Does group functions work across many rows to produce one result per group? 2. Does group functions include nulls in calculations? 3. Does the WHERE clause restricts rows prior to inclusion in a group calculation? Labs 3: Functions in SQL Group functions - Correction 1. TRUE 2. FALSE 3. TRUE Labs 3: Functions in SQL Group functions - Exercise 4. Find the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number. Save your SQL statement in lab_03_03.sql. 5. Modify your script to display the minimum, maximum, sum, and average salary for each job type. Save your script as lab_03_04.sql. 6. Write a query to display the number of people with the same job. Labs 3: Functions in SQL Group functions - Correction 4. SELECT ROUND(MAX(salary), 0) "Maximum", ROUND(MIN(salary), 0) "Minimum", ROUND(SUM(salary), 0) "Sum", ROUND(AVG(salary), 0) "Average" FROM employees; 5. SELECT job_id, ROUND(MAX(salary), 0) "Maximum", ROUND(MIN(salary), 0) "Minimum", ROUND(SUM(salary), 0) "Sum", ROUND(AVG(salary), 0) "Average" FROM employees GROUP BY job_id; 6. SELECT job_id, COUNT(*) FROM employees GROUP BY job_id; Labs 3: Functions in SQL Group functions - Exercise 7. Generalize the query so that the user in the HR department is prompted for a job title. Save the script in a file lab_03_05.sql. 8. Determine the number of managers without listing them. Label the column “Number of Managers”. Use the manager_id column to determine the number of managers. 9. Find the difference between the highest and lowest salaries. Label the column DIFFERENCE. Labs 3: Functions in SQL Group functions - Correction 7. SELECT job_id, COUNT(*) FROM employees WHERE job_id = '&job_title' GROUP BY job_id; 8. SELECT COUNT(DISTINCT manager_id) FROM employees; 9. SELECT MAX(salary)-MIN(salary) DIFFERENCE FROM employees; Labs 3: Functions in SQL Group functions - Exercise 10. Create a report to display the manager number and the salary of the lowest-paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is $6,000 or less. Sort the output in descending order of salary. 11. Create a query that will display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings. 12. Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job, for departments 20, 50, 80, and 90, giving each column an appropriate heading. The required result must correspond to the screenshot, on the next slide. Labs 3: Functions in SQL Group functions - Exercise Labs 3: Functions in SQL Group functions - Correction 10. SELECT manager_id, MIN(salary) FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary) > 6000 ORDER BY MIN(salary) DESC; 11. SELECT COUNT(*) total, SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1995,1,0)) "1995", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1996,1,0)) "1996", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1997,1,0)) "1997", SUM(DECODE(TO_CHAR(hire_date, 'YYYY'), 1998,1,0)) "1998" FROM employees; 12. SELECT job_id "Job", SUM(DECODE(department_id, 20, salary)) "Dept 20", SUM(DECODE(department_id, 50, salary)) "Dept 50", SUM(DECODE(department_id, 80, salary)) "Dept 80", SUM(DECODE(department_id, 90, salary)) "Dept 90", SUM(salary) "Total" FROM employees GROUP BY job_id; Labs: Manipulating Database Data Labs 4: Retrieving Data from Several Tables Labs 4: Retrieving Data from Several Tables Exercise 1. Write a query for the HR department to produce the addresses of all departments. Use the LOCATIONS and COUNTRIES tables. Show the location id, street address, city, state or province, and country in the output. Use a NATURAL JOIN to produce the result. 2. Write a query to display the last name, department number, and department name for all employees. 3. The HR department needs a report of employees in Toronto. Display the last name, job, department number, and department name for all employees who work in Toronto. Labs 4: Retrieving Data from Several Tables Correction 1. SELECT location_id, street address, city, state_province, country_name FROM locations NATURAL JOIN countries; 2. SELECT last_name, department_id, department_name FROM employees JOIN departments USING (department_id); 3. SELECT e.last_name, e.job_id, e.department_id, d.department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) WHERE LOWER(l.city) = 'toronto'; Labs 4: Retrieving Data from Several Tables Exercise 4. Create a report to display employees’ last name and employee number along with their manager’s last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively. Place your SQL statement in a text file named lab_04_01.sql. 5. Modify your script to display all employees including King, who has no manager. Order the results by the employee number. Save as lab_04_02.sql. 6. Create a report for the HR department that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label. Save as lab_04_03.sql. Labs 4: Retrieving Data from Several Tables Correction 4. SELECT w.last_name "Employee", w.employee_id, m.last_name "Manager", m.employee_id "Mgr#" FROM employees w JOIN employees m ON (w.manager_id = m.employee_id); 5. SELECT w.last_name "Employee", w.employee_id, m.last_name "Manager", m.employee_id "Mgr#" FROM employees w LEFT OUTER JOIN employees m ON (w.manager_id = m.employee_id); 6. SELECT e.department_id, e.last_name, c.last_name colleague FROM employees e JOIN employees c ON (e.department_id = c.department_id) WHERE e.employee_id c.employee_id ORDER BY 1, 2, 3; Labs 4: Retrieving Data from Several Tables Exercise 7. The HR department needs a report on job grades and salaries. To familiarize yourself with the JOB_GRADES table, first show the structure of the JOB_GRADES table. Then create a query that displays the name, job, department name, salary, and grade for all employees. 8. The HR department wants to determine the names of all employees who were hired after Davies. Create a query to display the name and hire date of any employee hired after employee Davies. 9. The HR department needs to find the names and hire dates for all employees who were hired before their managers, along with their managers’ names and hire dates. Save the script to a file named lab_04_04.sql. Labs 4: Retrieving Data from Several Tables Correction 7. SELECT e.last_name, e.job_id, d.department_name, e.salary, j.grade_level FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN job_grades j ON (e.salary BETWEEN j.lowest_sal AND j.highest_sal); SELECT e.last_name, e.hire_date FROM employees e JOIN employees davies 8. ON (davies.last_name = 'Davies') WHERE davies.hire_date < e.hire_date; SELECT w.last_name, w.hire_date, 9. m.last_name, m.hire_date FROM employees w JOIN employees m ON (w.manager_id = m.employee_id) AND w.hire_date < m.hire_date; Labs 5: Advanced Retrieval www.supinfo.com Copyright © SUPINFO. All rights reserved Labs 5: Advanced Retrieval Course topics Course’s plan: Labs: Using Subqueries to Solve Queries Labs: Using the Set Operators Labs: Hierarchical Retrieval Labs 5: Advanced Retrieval Labs: Using Subqueries to Solve Queries Labs 5: Using Subqueries to Solve Queries Exercise 1. The HR department needs a query that prompts the user for an employee last name. The query then displays the last name and hire date of any employee in the same department as the employee whose name they supply (excluding that employee). For example, if the user enters Zlotkey, find all employees who work with Zlotkey (excluding Zlotkey). 2. Create a report that displays the employee number, last name, and salary of all employees who earn more than the average salary. Sort the results by ascending salary. 3. Write a query that displays the employee number and last name of all employees who work in a department with any employee whose last name contains a “u”. Place your SQL statement in a text file name lab_05_01.sql. Labs 5: Using Subqueries to Solve Queries Correction 1. UNDEFINE Enter_name SELECT last_name, hire_date FROM employees WHERE department_id IN (SELECT department_id FROM employees WHERE last_name = '&&Enter_name') AND last_name '&Enter_name'; SELECT employee_id, last_name, salary 2. FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) ORDER BY salary; SELECT employee_id, last_name 3. FROM employees WHERE department_id IN (SELECT department_id FROM employees WHERE last_name LIKE '%u%'); Labs 5: Using Subqueries to Solve Queries Exercise 4. The HR department needs a report that displays the last name, department number, and job id of all employees whose department location id is 1700. 5. Modify the query so that the user is prompted for a location id. Save this to a file named lab_05_02.sql. 6. Create a report for HR that displays the last name and salary of every employee who reports to Steven King. Labs 5: Using Subqueries to Solve Queries Correction 4. SELECT last_name, department_id, job_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700); 5. SELECT last_name, department_id, job_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = &loc); 6. SELECT last_name, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE last_name = 'King' AND first_name = 'Steven'); Labs 5: Using Subqueries to Solve Queries Exercise 7. Create a report for HR that displays the department number, last name, and job id for every employee in the Executive department. 8. Modify the query in lab_05_01.sql to display the number, last name, and salary of all employees who earn more than the average salary and who work in a department with any employee whose last name contains an “u”. Save as lab_05_03.sql. Labs 5: Using Subqueries to Solve Queries Correction 7. SELECT department_id, last_name, job_id FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Executive'); 8. SELECT employee_id, last_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM employees WHERE last_name LIKE '%u%') AND salary > (SELECT AVG(salary) FROM employees); Labs 5: Advanced Retrieval Labs: Using the Set Operators Labs 5: Using the Set Operators Exercise 1. The HR department needs a list of department IDs for departments that do not contain the job id ST_CLERK. Use set operators to create this report. 2. The HR department needs a list of countries that have no departments located in them. Display the country id and the name of the countries. Use set operators to create this report. Labs 5: Using the Set Operators Correction 1. SELECT department_id FROM departments MINUS SELECT department_id FROM employees WHERE job_id = 'ST_CLERK'; 2. SELECT country_id, country_name FROM countries MINUS SELECT l.country_id, c.country_name FROM locations l JOIN countries c ON (l.country_id = c.country_id); Labs 5: Using the Set Operators Exercise 3. Produce a list of jobs for departments 10, 50, and 20, in that order. Display job id and department id using set operators. Labs 5: Using the Set Operators Correction 3. COLUMN dummy NOPRINT SELECT job_id, department_id, 'x' dummy FROM employees WHERE department_id = 10 UNION SELECT job_id, department_id, 'y' dummy FROM employees WHERE department_id = 50 UNION SELECT job_id, department_id, 'z' dummy FROM employees WHERE department_id = 20 ORDER BY dummy; COLUMN dummy PRINT Labs 5: Using the Set Operators Exercise 4. Create a report that lists the employee IDs and job IDs of those employees who currently have a job title that is the same as their job title when they were initially hired by the company (that is, they changed jobs but have now gone back to doing their original job). 5. The HR department needs a report with the following specifications:  Last name and department id of all the employees from the EMPLOYEES table, regardless of whether or not they belong to a department  Department id and department name of all the departments from the DEPARTMENTS table, regardless of whether or not they have employees working in them. Write a compound query to accomplish this. Labs 5: Using the Set Operators Correction 4. SELECT employee_id, job_id FROM employees INTERSECT SELECT employee_id, job_id FROM job_history; 5. SELECT last_name, department_id, TO_CHAR(null) FROM employees UNION SELECT TO_CHAR(null), department_id,department_name FROM departments; Labs 5: Advanced Retrieval Labs: Hierarchical Retrieval Labs 5: Hierarchical Retrieval Exercise 1. Look at the following output example. Is this input the result of a hierarchical query? Explain why or why not. Labs 5: Hierarchical Retrieval Exercise 2. Look at the following output example. Is this input the result of a hierarchical query? Explain why or why not. Labs 5: Hierarchical Retrieval Exercise 3. Look at the following output example. Is this input the result of a hierarchical query? Explain why or why not. Labs 5: Hierarchical Retrieval Correction 1. This is not a hierarchical query; the report simply has a descending sort on SALARY. 2. This is not a hierarchical query; there are two tables involved in a join. 3. Yes, this is most definitely a hierarchical query because it displays the tree structure representing the management reporting line from the EMPLOYEES table. Labs 5: Hierarchical Retrieval Exercise 4. Produce a report showing an organization chart for Mourgo’s department. Print last names, salaries, and department IDs. 5. Create a report that shows the hierarchy of the managers for the employee Lorentz. Display his immediate manager first. 6. Produce a company organization chart that shows the management hierarchy. Start with the person at the top level, exclude all people with a job id of IT_PROG, and exclude De Haan and those employees who report to De Haan. Labs 5: Hierarchical Retrieval Correction 4. SELECT last_name, salary, department_id FROM employees START WITH last_name = 'Mourgos' CONNECT BY PRIOR employee_id = manager_id; 5. SELECT last_name FROM employees WHERE last_name != 'Lorrentz' START WITH last_name = 'Lorrentz' CONNECT BY PRIOR manager_id = employee_id; 6. SELECT last_name, employee_id, manager_id FROM employees WHERE job_id != 'IT_PROG' START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id AND last_name != 'De Haan'; Labs 5: Hierarchical Retrieval Exercise 7. Create an indented report showing the management hierarchy starting from the employee whose LAST_NAME is Kochhar. Print the employee’s last name, manager id, and department id. Give alias names to the columns as shown in the sample output. Labs 5: Hierarchical Retrieval Correction 7. COLUMN name FORMAT A20 SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)- 2,'_') name, manager_id mgr, department_id deptno FROM employees START WITH last_name = 'Kochhar' CONNECT BY PRIOR employee_id = manager_id / COLUMN name CLEAR Labs 6: Manipulating Data www.supinfo.com Copyright © SUPINFO. All rights reserved Labs 6: Manipulating Data Course objectives By completing this course, you will be able to: Describe each data manipulation language (DML) statement Insert rows into a table Update rows in a table Delete rows from a table Control transactions Labs 6: Global Practices Exercise 1. Run the script to create the MY_EMPLOYEE table, on next slide. 2. Describe the structure of the MY_EMPLOYEE table to identify the column names. 3. Create an INSERT statement to add the first row of data to the MY_EMPLOYEE table from the following sample data. Do not list the column in the INSERT clause. Labs 6: Global Practices Exercise CREATE TABLE my_employee (id NUMBER(4) CONSTRAINT my_employee_id_nn NOT NULL, last_name VARCHAR2(25), first_name VARCHAR2(25), userid VARCHAR2(8), salary NUMBER(9,2)); Labs 6: Global Practices Correction 1. Simply copy and past the script into SQL*Plus. 2. DESC my_employee 3. INSERT INTO my_employee VALUES (1, 'Patel', 'Ralph', 'rpatel', 895); Labs 6: Global Practices Exercise 4. Populate the MY_EMPLOYEE table with the second row of sample data form the preceding list. This time, list the columns explicitly in the INSERT clause. 5. Confirm your addition to the table. 6. Write an insert statement in a dynamic reusable script file named loademp.sql to load rows into the MY_EMPLOYEE table. Concatenate the first letter of the first name and the first seven characters of the last name to produce the user id. Save this script to a file named lab_06_01.sql. Then, populate the table with the next two rows of sample data by running the insert statement in the script that you created. Labs 6: Global Practices Correction 4. INSERT INTO my_employee (id, last_name, first_name, userid, salary) VALUES (2, 'Dancs', 'Betty', 'bdancs', 860); 5. SELECT * FROM my_employee; 6. SET ECHO OFF SET VERIFY OFF INSERT INTO my_employee VALUES (&p_id, '&&p_last_name','&&p_first_name', LOWER(SUBSTR('&p_first_name', 0, 1) || SUBSTR('&p_last_name', 0, 7), &p_sal); SET VERIFY ON ECHO ON UNDEFINE p_first_name UNDEFINE p_last_name Labs 6: Global Practices Exercise 7. Confirm your additions to the table. 8. Make the data additions permanent. 9. Change the last name of employee 3 to Drexler. Labs 6: Global Practices Correction 7. SELECT * FROM my_employee; 8. COMMIT; 9. UPDATE my_employee SET last_name = 'Drexler' WHERE id = 3; Labs 6: Global Practices Exercise 10. Change the salary to $1,000 for all employees with a salary less than $900. Verify your changes to the table. 11. Delete Betty Dancs from the MY_EMPLOYEE table and verify your changes. 12. Commit all pending changes. Labs 6: Global Practices Correction 10. UPDATE my_employee SET salary = 1000 WHERE salary < 900; SELECT last_name, salary FROM my_employee; 11. DELETE FROM my_employee WHERE last_name = 'Dancs'; SELECT * FROM my_employee; 12. COMMIT; Labs 6: Global Practices Exercise 13. Populate the table with the last row of sample data by using the statements in the script that you created (lab_06_01.sql) and confirm your addition to the table. Then, mark an intermediate point in the processing of the transaction. 14. Empty the entire table (using DML, not DDL!) and confirm that your table is empty. 15. Discard the most recent DELETE operation without discarding the earlier INSERT operation and confirm that the new row is still intact. Make the data addition permanent. Labs 6: Global Practices Correction 13. @ C:\labs\lab_09_01.sql SELECT * FROM my_employee; SAVEPOINT after_insert; DELETE FROM my_employee; 14. SELECT * FROM my_employee; ROLLBACK TO after_insert; 15. SELECT * FROM my_employee; COMMIT; Labs 7.1: Creating and Managing Tables www.supinfo.com Copyright © SUPINFO. All rights reserved Labs 7.1: Creating and Managing Table Using DDL Statements Exercise 1. Create the DEPT table based on the following specifications:  ID, Primary Key, NUMBER, 7 digits  NAME, VARCHAR2, 25 characters max. 2. Populate the DEPT table with data from the DEPARTMENTS table, include only columns that you need. 3. Create EMP table based on the following table instance chart. Place the syntax in a script called lab_07_01.sql. Labs 7.1: Creating and Managing Table Using DDL Statements Correction 1. CREATE TABLE dept (id NUMBER(7) CONSTRAINT dept_pk PRIMARY KEY, name VARCHAR2(25)); DESC dept 2. INSERT INTO dept SELECT department_id, department_name FROM departments; 3. CREATE TABLE emp (id NUMBER(7), last_name VARCHAR2(25), first_name VARCHAR2(25), dept_id NUMBER(7) CONSTRAINT emp_dept_id_fk REFERENCES dept(id); Labs 7.1: Creating and Managing Table Using DDL Statements Exercise 4. Create the EMP2 table based on the structure of the EMPLOYEES table. Include only the employee_id, first_name, last_name, salary, and department_id columns. Name the columns in the new table id, first_name, last_name, salary and dept_id respectively. 5. Drop the EMP table. Labs 7.1: Creating and Managing Table Using DDL Statements Correction 4. CREATE TABLE emp2 AS SELECT employee_id id, first_name, last_name salary, department_id dept_id FROM employees; 5. DROP TABLE emp; Labs 7.2: Creating and Managing Other Schema Objects www.supinfo.com Copyright © SUPINFO. All rights reserved Labs 7.2: Creating and Managing Other Schema Objects Course objectives By completing this course, you will be able to: Categorize the main database objects Review the table structure Create simple and complex views Labs 7.2: Create Other Schema Objects Exercise 1. The staff in the HR department wants to hide some of the data in the EMPLOYEES table. They want a view called called EMPLOYEES_VU based on the employee numbers, employee names, and department numbers from the EMPLOYEES table. They want the heading for the employee name to be EMPLOYEE. 2. Confirm that the view works. Display the contents of the EMPLOYEES_VU view. 3. Using your EMPLOYEES_VU view, write a query for the HR department to display all employee names and department numbers. Labs 7.2: Create Other Schema Objects Correction 1. CREATE OR REPLACE VIEW employees_vu AS SELECT employee_id, last_name employee, department_id FROM employees; 2. DESC employees_vu SELECT * FROM employees_vu; 3. SELECT employee, department_id FROM employees_vu; Labs 7.2: Create Other Schema Objects Exercise 4. Department 50 needs access to its employee data. Create a view named DEPT50 that contains the employee numbers, employee last names, and department numbers for all employees in department 50. They have requested that you label the view columns EMPNO, EMPLOYEE, and DEPTNO. For security purposes, do not allow an employee to be reassigned to another department through the view. 5. Display the structure and content of the DEPT50 view. 6. Test your view. Attemps to reassign Matos to department 80. Labs 7.2: Create Other Schema Objects Correction 4. CREATE VIEW dept50 AS SELECT employee_id empno, last_name employee, department_id deptno FROM employees WHERE department_id = 50 WITH CHECK OPTION CONSTRAINT emp_dept_50; 5. DESC dept50 SELECT * FROM dept 50; 6. UPDATE dept50 SET deptno = 80 WHERE employee _ 'Matos'; Labs 7.2: Create Other Schema Objects Exercise 7. You need a sequence that can be used with the primary key column of the DEPT table. The sequence should start at 300 and have a maximum value of 1000. Have your sequence increment by 10. Name the sequence DEPT_ID_SEQ. 8. To test your sequence, write a script to insert two rows in the DEPT table. Name your script lab_07_02.sql. Be sure to use the sequence that you created for the ID column. Add two departments: Education and Administration. Confirm your additions. 9. Create a non-unique index on the NAME column in the DEPT table. Labs 7.2: Create Other Schema Objects Correction 7. CREATE SEQUENCE dept_id_seq START WITH 300 INCREMENT BY 10 MAXVALUE 1000; INSERT INTO dept 8. VALUES (dept_id_seq.nextval, 'Education'); INSERT INTO dept VALUES (dept_id_seq.nextval, 'Administration'); 9. CREATE INDEX dept_name_idx ON dept (name); Labs 7.2: Create Other Schema Objects Exercise 10. Create a synonym for your EMPLOYEES table. Call it EMP. 11. For a specified table, create a script that reports the column names, data types, and data types' lengths, as well as whether nulls are allowed. Prompt the user to enter the table name. Give appropriate aliases to the columns DATA_PRECISION and DATA_SCALE. Save this script in a file named lab_07_03.sql. 12. Create a script that reports the column name, constraint name, constraint type, search condition, and status for a specified table. You must join the USER_CONSTRAINTS and USER_CONS_COLUMNS tables to obtain all of this information. Prompt the user to enter the table name. Save the script in a file named lab_07_04.sql. Labs 7.2: Create Other Schema Objects Correction 10. CREATE SYNONYM emp FOR EMPLOYEES; SELECT column_name, data_type, data_length, data_precision PRECISION, data_scale SCALE, 11. FROM user_tab_columns WHERE table_name = UPPER('&tab_name'); 12. SELECT ucc.column_name, uc.constraint_name, uc.constraint_type, uc.search_condition, uc.status FROM user_constraints uc JOIN user_cons_columns ucc ON uc.table_name = ucc.table_name AND uc.constraint_name = ucc.constraint_name AND uc.table_name = UPPER('&tab_name'); Labs 7.2: Create Other Schema Objects Exercise 13. Add a comment to the DEPARTMENTS table. Then query the USER_TAB_COMMENTS view to verify that the comment is present. 14. Find the names of all synonyms that are in your schema. 15. You need to determine the names and definitions of all of the views in your schema. Create a report that retrieves view information (the view name and text) from the USER_VIEWS data dictionary view. To see more contents of a LONG column, use the SQL*Plus command SET LONG n, where n is the value of the number of characters of the LONG column that you want to see. Labs 7.2: Create Other Schema Objects Correction 13. COMMENT ON TABLE departments IS 'Company department information including name, code, and location.' SELECT COMMENTS FROM user_tab_comments WHERE table_name = 'DEPARTMENTS'; 14. SELECT * FROM user_sysnonyms; SET LONG 600 15. SELECT view_name, text FROM user_views; Labs 7.2: Create Other Schema Objects Exercise 16. Find the names of your sequences. Write a query in a script to display the following information about your sequences: sequence name, maximum value, increment size, and last number. Name the script lab_07_05.sql. Labs 7.2: Create Other Schema Objects Correction 16. SELECT sequence_name, max_value, increment_by, last_number FROM user_sequences; Controlling User Access Labs 8: Controlling User Access Labs 8: Controlling User Access Exercise 1. What privilege should a user be given to log on to the Oracle server? Is this a system or an object privilege? 2. What privilege should a user be given to create tables? 3. If you create a table, who can pass along privileges to other users on your table? 4. You are the DBA. You are creating many users who require the same system privileges. What should you use to make your job easier? 5. What command do you use to change your password? 6. Create user TOTO and allow him to connect the database (ask your trainer or look at the comments). Grant this user access to your DEPARTMENTS table. Labs 8: Controlling User Access Correction 1. The CREATE SESSION system privilege. 2. The CREATE TABLE privilege. 3. You can, or anyone you have given those privileges to, by using the WITH GRANT OPTION. 4. Create a role containing the system privileges and grant the role to the users. 5. The ALTER USER statement. 6. CREATE USER toto IDENTIFIED BY oracle; CONN / AS SYSDBA – See comments GRANT create session TO toto; CONN oracle/oracle – See comments GRANT select ON departments TO toto; Labs 8: Controlling User Access Exercise 7. Query all the rows in your DEPARTMENTS table. 8. Add two new rows to your DEPARTMENTS table:  Education as department number 500  Human Resources as department number 510 Connect as TOTO and query your DEPARTMENTS table. 9. Connect as ORACLE. Create a synonym DEPT2 for your DEPARTMENTS table. Then, query all the rows by using your synonym. Labs 8: Controlling User Access Correction 7. SELECT * FROM departments; INSERT INTO departments (department_id, department_name) 8. VALUES (500, 'Education'); CONN toto/oracle SELECT * FROM oracle.departments; CREATE SYNONYM dept FOR department; 9. SELECT * FROM dept2; Labs 8: Controlling User Access Exercise 10. Query the USER_TABLES data dictionary to see information about the tables that you own. Then query the ALL_TABLES view to see information about all tables you can access. 11. Revoke the SELECT privilege from TOTO. Labs 8: Controlling User Access Correction 10. SELECT table_name FROM user_tables; SELECT table_name, owner FROM departments WHERE owner 'oracle'; REVOKE select 11. ON departments FROM toto; Labs 9: Managing Schema Objects www.supinfo.com Copyright © SUPINFO. All rights reserved Labs 9: Managing Schema Objects Course objectives By completing this course, you will be able to: Drop columns and set column UNUSED Add constraints Create indexes Create indexes using the CREATE TABLE statement Creating function-based indexes Create and use external tables Labs: Managing Schema Objects Labs 9.1: Modifying a Table Labs 9.1: Modifying a Table Exercise 1. Modify the EMP2 table to allow longer employees’ last names. Confirm your modification. 2. Confirm that both DEPT and EMP2 tables are stored in the data dictionary. (Hint: USER_TABLES) 3. Drop the FIRST_NAME column from the EMP2 table. Confirm your modification by checking the description of the table. Labs 9.1: Modifying a Table Correction 1. ALTER TABLE emp2 MODIFY (last_name VARCHAR2(50)); DESC emp2 2. SELECT table_name FROM user_tables WHERE table_name IN ('DEPT', 'EMP2'); 3. ALTER TABLE emp2 DROP COLUMN first_name; Labs 9.1: Modifying a Table Exercise 4. In the EMPLOYEES2 table, mark the DEPT_ID column as UNUSED. Confirm your modification by checking the description of the table. 5. Drop all UNUSED columns from the EMP2 table. Confirm your modification. Labs 9.1: Modifying a Table Correction 4. ALTER TABLE emp2 SET UNUSED (dept_id); 5. ALTER TABLE emp2 DROP UNUSED COLUMNS; Labs: Managing Schema Objects Labs 9.2: Managing Constraints Labs 9.2: Managing Constraints Exercise 1. Add a table-level PRIMARY KEY constraint to the EMP2 table on the ID column. The constraint should be named at creation. Name the constraint my_emp_id_pk. 2. Create a PRIMARY KEY constraint to the DEPT table using the ID column. The constraint should be named at creation: my_dept_id_pk. 3. Add a foreign key reference on the EMP2 table that ensures that the employee is not assigned to a nonexistent department. Name the constraint my_emp_dept_id_fk. Labs 9.2: Managing Constraints Correction 1. ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id); 2. ALTER TABLE dept ADD CONSTRANT my_dept_id_pk PRIMARY KEY(id); 3. ALTER TABLE emp2 ADD CONSTRAINT my_emp_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept2(id); Labs 9.2: Managing Constraints Exercise 4. Confirm that the constraints were added by querying the USER_CONSTRAINTS view. Note the types and names for the constraints. 5. Display the object names and types from the USER_OBJECTS data dictionary view for the EMP2 and DEPT tables. Notice that the new tables and a new index were created. 6. Modify the EMP2 table. Add a COMMISSION column of NUMBER data type, precision 2, scale 2. Add a constraint to the COMMISSION column that ensures that a commission value is greater than zero. Labs 9.2: Managing Constraints Correction 4. SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name IN ('EMP2', 'DEPT'); 5. SELECT object_name, object_type FROM user_objects WHERE object_name LIKE 'EMP%' OR object_name LIKE 'DEPT%'; 6. ALTER TABLE emp2 ADD commission NUMBER(2,2) CONSTRAINT my_emp_comm_ck CHECK (commission>0); Labs 9.2: Managing Constraints Exercise 7. Drop the EMP2 table so that it cannot be restored. Verify the recycle bin. 8. Create the DEPT_NAMES_INDEX table based on the following table instance chart. Name the index for the PRIMARY KEY column as DEPT_PK_IDX. Labs 9.2: Managing Constraints Correction 7. DROP TABLE emp2 PURGE; SELECT original_name, operation, droptime FROM recyclebin; 8. CREATE TABLE dept_named_index (deptno NUMBER(4) PRIMARY KEY USING INDEX (CREATE INDEX dept_pk_idx ON dept_named_index(deptno)), dname VARCHAR2(30)); Labs 10: Advanced Manipulations www.supinfo.com Copyright © SUPINFO. All rights reserved Labs: Advanced Manipulations Course objectives By completing this course, you will be able to: Manipulate data using subqueries Describe the features of multitable inserts Use the following types of multitable inserts Unconditional INSERT Pivoting INSERT Conditional ALL INSERT Conditional FIRST INSERT Merge rows in a table Track the changes to data over a period of time Labs 10: Manipulating Data Using Subqueries Background Execute the following script: DROP TABLE sal_history; CREATE TABLE sal_history (employee_id NUMBER(6), hire_date DATE, salary NUMBER(8,2)); DROP TABLE mgr_history; CREATE TABLE mgr_history (employee_id NUMBER(6), manager_idNUMBER(6), salary NUMBER(8,2)); DROP TABLE special_sal; CREATE TABLE special_sal (employee_id NUMBER(6), salary NUMBER(8,2)); Labs 10: Manipulating Data Using Subqueries Exercise 1. Retrieve the details of the employee id, hire date, salary, and manager id of those employees whose employee id is less than 125 from the EMPLOYEES table. If the salary is more than $20,000, insert the details of employee id and salary into the SPECIAL_SAL table. Else, insert the details of the employee id, hire date, and salary into the SAL_HISTORY table. Insert the details of the employee id, manager id, and salary into the MGR_HISTORY table. 2. Display the records from the three tables (SPECIAL_SAL, SAL_HISTORY, and MGR_HISTORY) Labs 10: Manipulating Data Using Subqueries Correction 1. INSERT ALL WHEN SAL > 20000 THEN INTO special_sal VALUES (EMPID, SAL) ELSE INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id < 125; 2. SELECT * FROM special_sal; SELECT * FROM sal_history; SELECT * FROM mgr_history; Labs 10: Manipulating Data Using Subqueries Background Execute the following script: DROP TABLE sales_source_data; CREATE TABLE sales_source_data (employee_id NUMBER(6), WEEK_ID NUMBER(2), SALES_MON NUMBER(8,2), SALES_TUE NUMBER(8,2), SALES_WED NUMBER(8,2), SALES_THUR NUMBER(8,2), SALES_FRI NUMBER(8,2)); INSERT INTO SALES_SOURCE_DATA VALUES (178, 6, 1750,2200,1500,1500,3000); COMMIT; Labs 10: Manipulating Data Using Subqueries Background Execute the following script: DROP TABLE sales_info; CREATE TABLE sales_info (employee_id NUMBER(6), WEEK NUMBER(2), SALES NUMBER(8,2)); INSERT INTO SALES_SOURCE_DATA VALUES (176, 6, 2000,3000,4000,5000,6000); COMMIT; Labs 10: Manipulating Data Using Subqueries Exercise 3. Write a query to do the following, using a pivoting INSERT statement:  Retrieve the details of the employee id, week id, sales on Monday, sales on Tuesday, sales on Wednesday, sales on Thursday, and sales on Friday from the SALES_SOURCE_DATA table.  Build a transformation such that each record retrieved from the SALES_SOURCE_DATA table is converted into multiple records for the SALES_INFOS table. 4. Display the records from the SALES_INFO table. Labs 10: Manipulating Data Using Subqueries Correction 3. INSERT ALL INTO sales_info VALUES (employee_id, week_id, sales_MON) INTO sales_info VALUES (employee_id, week_id, sales_TUE) INTO sales_info VALUES (employee_id, week_id, sales_WED) INTO sales_info VALUES (employee_id, week_id, sales_THUR) INTO sales_info VALUES (employee_id, week_id, sales_FRI) SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI FROM sales_source_data; SELECT * 4. FROM sales_info; Labs 10: Manipulating Data Using Subqueries Exercise 5. You have the data of past employees stored in a flat file called emp.data. You want to store the names and e- mail IDs of all employees past and present in a table. To do this, first create an external table called EMP_DATA using the emp.dat source file in the emp_dir directory. You can use the script in lab_11_01.sql to do this. Labs 10: Manipulating Data Using Subqueries Background Execute the following script: DROP TABLE emp_hist; CREATE TABLE emp_hist as select first_name, last_name,email from employees; Labs 10: Manipulating Data Using Subqueries Exercise 6. Increase the size of the email column to 45. 7. Merge the data in the EMP_DATA table in the EMP_HIST table. Assume that the data in the external EMP_DATA table is the most up-to-date. If a row in the EMP_DATA table matches the EMP_HIST table, update the email column of the EMP_HIST table to match the EMP_DATA table row. If a row in the EMP_DATA table does not match, insert it into the EMP_HIST table. Rows are considered matching when the employee’s first and last names are identical. 8. Retrieve the rows from EMP_HIST after the merge. Labs 10: Manipulating Data Using Subqueries Correction 6. ALTER TABLE emp_hist MODIFY email VARCHAR2(45); 7. MERGE INTO EMP_HIST f USING EMP_DATA h ON (f.first_name = h.first_name AND f.last_name = h.last_name) WHEN MATCHED THEN UPDATE SET f.email = h.email WHEN NOT MATCHED THEN INSERT (f.first_name, f.last_name, f.email) VALUES (h.first_name, h.last_name, h.email); 8. SELECT * FROM emp_hist; Labs 10: Manipulating Data Using Subqueries Background Execute the following script: DROP TABLE emp3; CREATE TABLE emp3 AS SELECT * FROM employees; Labs 10: Manipulating Data Using Subqueries Exercise 9. In the EMP3 table, change the department for Kochhar to 60 and commit your change. Next, change the department for Kochhar to 50 and commit your change. Track the changes to Kochhar using the Row Version feature. 10. Write a query to display the last name, department number, and salary of any employee whose department number and salary both match the department number and salary of any employee who earns a commission. Labs 10: Manipulating Data Using Subqueries Correction 9. UPDATE emp 3 SET department_id = 60 WHERE last_name = 'Kochhar'; COMMIT; -- Do it again for department_id = 50 SELECT VERSIONS_STARTTIME "START_DATE", VERSIONS_ENDTIME "END_DATE", DEPARTMENT_ID FROM EMP3 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE WHERE LAST_NAME ='Kochhar'; SELECT last_name, department_id, salary FROM employees 10. WHERE (salary, department_id) IN (SELECT salary, department_id FROM employees WHERE commission_pct IS NOT NULL); Labs 10: Manipulating Data Using Subqueries Exercise 11. Display the last name, department name, and salary of any employee whose salary and commission match the salary and commission of any employee located in location ID 1700. 12. Create a query to display the last name, hire date, and salary for all employees who have the same salary and commission as Kochhar. Do not display Kocchar in the result set. Labs 10: Manipulating Data Using Subqueries Correction 11. SELECT e.last_name, d.department_name, e.salary FROM employees e, departments d WHERE e.department_id = d.department_id AND (salary, NVL(commission_pct,0)) IN (SELECT salary, NVL(commission_pct,0) FROM employees e, departments d WHERE e.department_id = d.department_id AND d.location_id = 1700); 12. SELECT last_name, hire_date, salary FROM employees WHERE (salary, NVL(commission_pct,0)) IN (SELECT salary, NVL(commission_pct,0) FROM employees WHERE last_name = 'Kochhar') AND last_name != 'Kochhar'; Labs 10: Manipulating Data Using Subqueries Exercise 13. Create a query to display the employees who earn a salary that is higher than the salary of all of the sales managers (JOB_ID = ‘SA_MAN’). Sort the results on salary from highest to lowest. 14. Display the details of the employee ID, last name, and department ID of those employees who live in cities whose name begins with T. Labs 10: Manipulating Data Using Subqueries Correction 13. SELECT last_name, job_id, salary FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE job_id = 'SA_MAN') ORDER BY salary DESC; 14. SELECT employee_id, last_name, department_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id IN (SELECT location_id FROM locations WHERE city LIKE 'T%')); Labs 10: Manipulating Data Using Subqueries Exercise 15. Write a query to find all employees who earn more than the average salary in their departments. Display last name, salary, department ID, and the average salary for the department. Sort by average salary. 16. Find all employees who are not supervisors. Do this by using the NOT EXISTS operator. Labs 10: Manipulating Data Using Subqueries Correction 15. SELECT e.last_name ename, e.salary salary, e.department_id deptno, AVG(a.salary) dept_avg FROM employees e, employees a WHERE e.department_id = a.department_id AND e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) GROUP BY e.last_name, e.salary, e.department_id ORDER BY AVG(a.salary); 16. SELECT outer.last_name FROM employees outer WHERE NOT EXISTS (SELECT 'X' FROM employees inner WHERE inner.manager_id = outer.employee_id); Labs 10: Manipulating Data Using Subqueries Exercise 17. Rewrite your request using NOT IN operator. 18. Write a query to display the last names of the employees who earn less than the average salary in their departments. Labs 10: Manipulating Data Using Subqueries Correction 17. SELECT outer.last_name FROM employees outer WHERE outer.employee_id NOT IN (SELECT inner.manager_id FROM employees inner); 18. SELECT last_name FROM employees outer WHERE outer.salary < (SELECT AVG(inner.salary) FROM employees inner WHERE inner.department_id = outer.department_id); Labs 10: Manipulating Data Using Subqueries Exercise 19. Write a query to display the last names of employees who have one or more coworkers in their departments with later hire dates but higher salaries. 20. Write a query to display the employee ID, last names, and department names of all employees. Use a scalar subquery to retrieve the department name in the SELECT statement. Labs 10: Manipulating Data Using Subqueries Correction 19. SELECT last_name FROM employees outer WHERE EXISTS (SELECT 'X' FROM employees inner WHERE inner.department_id = outer.department_id AND inner.hire_date > outer.hire_date AND inner.salary > outer.salary); 20. SELECT employee_id, last_name, (SELECT department_name FROM departments d WHERE e.department_id = d.department_id) department FROM employees e ORDER BY department; Labs 10: Manipulating Data Using Subqueries Exercise 21. Write a query to display the department names of those departments whose total salary cost is above one- eighth (1/8) of the total salary cost of the whole company. Use the WITH clause to write this query. Name the query SUMMARY. Labs 10: Manipulating Data Using Subqueries Correction 21. WITH summary AS ( SELECT d.department_name, SUM(e.salary) AS dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name) SELECT department_name, dept_total FROM summary WHERE dept_total > (SELECT SUM(dept_total) * 1/8 FROM summary) ORDER BY dept_total DESC; Labs 11: Advanced Group Functions www.supinfo.com Copyright © SUPINFO. All rights reserved Labs: Advanced Group Functions Course objectives By completing this course, you will be able to: Use the ROLLUP operation to produce subtotal values Use the CUBE operation to produce cross-tabulation values Use the GROUPING function to identify the row values created by ROLLUP or CUBE Use GROUPING SETS to produce a single result set Labs 11: Advanced Group Functions Exercise 1. Write a query to display the following for those employees whose manager ID is less than 120:  Manager ID  Job ID and total salary for every job ID for employees who report to the same manager  Total salary of those managers  Total salary of those managers, irrespective of the job IDs. Labs 11: Advanced Group Functions Correction 1. SELECT manager_id,job_id,sum(salary) FROM employees WHERE manager_id < 120 GROUP BY ROLLUP(manager_id,job_id); Labs 11: Advanced Group Functions Exercise 2. Observe the output from previous question. Write a query using the GROUPING function to determine whether the NULL values in the columns corresponding to the GROUP BY expressions are caused by the ROLLUP Operation. Drop all UNUSED columns from the EMP2 table. Confirm your modification. Labs 11: Advanced Group Functions Correction 2. SELECT manager_id MGR, job_id JOB, SUM(salary), GROUPING(manager_id), GROUPING(job_id) FROM employees WHERE manager_id < 120 GROUP BY ROLLUP(manager_id,job_id); Labs 11: Advanced Group Functions Exercise 3. Write a query to display the following for those employees whose manager ID is less than 120:  Manager ID  Job and total salaries for every job for employees who report to the same manager  Total salary of those managers  Cross-tabulation values to display the total salary for every job, irrespective of the manager  Total salary irrespective of all job titles Labs 11: Advanced Group Functions Correction 3. SELECT manager_id, job_id, sum(salary) FROM employees WHERE manager_id < 120 GROUP BY CUBE(manager_id, job_id); Labs 11: Advanced Group Functions Exercise 4. Observe the output from previous question. Write a query using the GROUPING function to determine whether the NULL values in the columns corresponding to the GROUP BY expressions are caused by the CUBE operation. Labs 11: Advanced Group Functions Correction 4. SELECT manager_id MGR ,job_id JOB, SUM(salary),GROUPING(manager_id), GROUPING(job_id) FROM employees WHERE manager_id < 120 GROUP BY CUBE(manager_id,job_id); Labs 11: Advanced Group Functions Exercise 5. Using GROUPING SETS, write a query to display the following groupings :  department_id, manager_id, job_id  department_id, job_id  manager_id, job_id  The query should calculate the sum of the salaries for each of these groups. Labs 11: Advanced Group Functions Correction 5. SELECT department_id, manager_id, job_id,SUM(salary) FROM employees GROUP BY GROUPING SETS ((department_id,manager_id, job_id),(department_id,job_id),(manager_id,job_id));