J.Y.S
This page is to efficiently manage our group assignments!
Contents
Member List
First Name | Last Name | wiki id | Learn e-mail | Phone |
---|---|---|---|---|
YuJin | Jeong | yujin.jeong | yjeong | 647 - 832 - 6771 |
Saad | ||||
James |
Assignment 1
Due Date
Thursday, October 21st, 4:00pm Printout submission only( SELECT Statements + ALL outputs)
Description
Solution
SQL> SELECT employee_id, RPAD(CONCAT(CONCAT(last_name, ', '),first_name), 25) "Full Name", 2 job_id, 3 TO_CHAR(TRUNC(hire_date, 'MONTH'), 'fmMonth Ddspth "in the year" YYYY') 4 "Start Date" 5 FROM employees 6 WHERE TO_CHAR(hire_date, 'mm') IN (05, 11) 7 ORDER BY 4 DESC; EMPLOYEE_ID Full Name JOB_ID Start Date ----------- ------------------------- ---------- ----------------------------------------- 124 Mourgos, Kevin ST_MAN November First in the year 1999 178 Grant, Kimberely SA_REP May First in the year 1999 174 Abel, Ellen SA_REP May First in the year 1996 104 Ernst, Bruce IT_PROG May First in the year 1991 SQL> SELECT 'Employee named ' || first_name || ' ' || last_name || ' who is ' 2 || job_id || ' will have a new salary of $' || salary * 1.15 "Happy Employees" 3 FROM employees 4 WHERE salary BETWEEN 5000 AND 12000 5 AND job_id IN ('IT_PROG', 'ST_CLERK') 6 ORDER BY employee_id; Happy Employees ---------------------------------------------------------------------------------------------------- Employee named Alexander Hunold who is IT_PROG will have a new salary of $10350 Employee named Bruce Ernst who is IT_PROG will have a new salary of $6900 SQL> SELECT last_name, salary, job_id, 2 RPAD(NVL(TO_CHAR(manager_id), 'No Manager'), 10) "Manager #", 3 LPAD(TO_CHAR(salary * 12, '$999,999'), 12) "Total Income" 4 FROM employees 5 WHERE (job_id LIKE 'MK%' OR manager_id IS NULL) 6 AND salary * (1 + NVL(commission_pct, 0)) + 1000 > 10000 7 ORDER BY 2 DESC; LAST_NAME SALARY JOB_ID Manager # Total Income ------------------------- ---------- ---------- ---------- ------------ King 24000 AD_PRES No Manager $288,000 Hartstein 13000 MK_MAN 100 $156,000 SQL> SELECT RPAD(CONCAT(CONCAT(first_name,' '),last_name), 25) "Employee", job_id, 2 LPAD(TRIM(TO_CHAR(salary, '$999,999')), 12, '=') "Salary", department_name 3 FROM employees e JOIN departments d 4 ON (e.department_id = d.department_id) 5 WHERE UPPER(department_name) IN ('MARKETING', 'SALES') 6 AND salary < 7 (SELECT MAX(salary) 8 FROM employees 9 WHERE SUBSTR(job_id, 4) NOT IN ('PRES','VP','MAN','MGR')) 10 ORDER BY 1; Employee JOB_ID Salary DEPARTMENT_NAME ------------------------- ---------- ------------ ------------------------------ Eleni Zlotkey SA_MAN =====$10,500 Sales Jonathon Taylor SA_REP ======$8,600 Sales Pat Fay MK_REP ======$6,000 Marketing SQL> SQL> SELECT last_name, salary, job_id 2 FROM employees 3 WHERE UPPER(job_id) NOT LIKE '%PRES' 4 AND salary > 5 (SELECT MAX(salary) 6 FROM employees 7 JOIN departments USING (department_id) 8 JOIN locations USING (location_id) 9 WHERE UPPER(city) IN ('TORONTO','OXFORD')); LAST_NAME SALARY JOB_ID ------------------------- ---------- ---------- Kochhar 17000 AD_VP De Haan 17000 AD_VP SQL> SELECT last_name, first_name, job_id, hire_date 2 FROM employees 3 WHERE hire_date > 4 (SELECT MAX(hire_date) 5 FROM employees 6 WHERE department_id = 7 (SELECT department_id 8 FROM departments 9 WHERE UPPER(department_name) = 'IT')) 10 AND (department_id != 11 (SELECT department_id 12 FROM departments 13 WHERE UPPER(department_name) = 'EXECUTIVE') 14 OR department_id IS NULL) 15 ORDER BY 4 DESC; LAST_NAME FIRST_NAME JOB_ID HIRE_DATE ------------------------- -------------------- ---------- --------- Zlotkey Eleni SA_MAN 29-JAN-00 Mourgos Kevin ST_MAN 16-NOV-99 Grant Kimberely SA_REP 24-MAY-99 SQL> SELECT department_id, job_id, AVG(salary) "Average Dept/Job Pay", 2 MIN(salary) "Lowest Dept/Job Pay" 3 FROM employees 4 WHERE UPPER(job_id) NOT LIKE '%VP' 5 AND department_id NOT IN 6 (SELECT department_id 7 FROM departments 8 WHERE UPPER(department_name) IN ('IT','SALES')) 9 GROUP BY department_id, job_id 10 HAVING MIN(salary) BETWEEN 5000 AND 15000 11 ORDER BY 1, 2; DEPARTMENT_ID JOB_ID Average Dept/Job Pay Lowest Dept/Job Pay ------------- ---------- -------------------- ------------------- 20 MK_MAN 13000 13000 20 MK_REP 6000 6000 50 ST_MAN 5800 5800 110 AC_ACCOUNT 8300 8300 110 AC_MGR 12000 12000 SQL> SELECT department_id, department_name, 2 RPAD(TRIM(NVL(city, 'Not Assinged Yet')), 25, ' ') "City", 3 COUNT(DISTINCT job_id) "# of Jobs" 4 FROM employees 5 RIGHT OUTER JOIN departments USING (department_id) 6 LEFT OUTER JOIN locations USING (location_id) 7 GROUP BY department_id, department_name, city; DEPARTMENT_ID DEPARTMENT_NAME City # of Jobs ------------- ------------------------------ ------------------------- ---------- 110 Accounting Seattle 2 50 Shipping South San Francisco 2 80 Sales Oxford 2 10 Administration Seattle 1 190 Contracting Seattle 0 90 Executive Seattle 2 20 Marketing Toronto 2 60 IT Southlake 1 8 rows selected. SQL> spool off
SQL Statements
1.
YuJin
SELECT employee_id, RPAD(CONCAT(CONCAT(last_name, ', '),first_name), 25) "Full Name", job_id, TO_CHAR(TRUNC(hire_date, 'MONTH'), 'fmMonth Ddspth "in the year" YYYY') "Start Date" FROM employees WHERE TO_CHAR(hire_date, 'mm') IN (05, 11) ORDER BY 4 DESC;
Saad
SELECT employee_id, SUBSTR(last_name ||' '|| first_name, 1, 25) "Full Name", job_id, TO_CHAR(hire_date,'fmMonth "First in the year" YYYY') "Start Date" FROM employees WHERE TO_CHAR(hire_date,'fmMON') IN ('MAY','NOV') ORDER BY hire_date DESC /
James
2.
YuJin
SELECT 'Employee named ' || first_name || ' ' || last_name || ' who is ' || job_id || ' will have a new salary of $' || salary * 1.15 "Happy Employees" FROM employees WHERE salary BETWEEN 5000 AND 12000 AND job_id IN ('IT_PROG', 'ST_CLERK') ORDER BY employee_id;
Saad
James
3.
YuJin
SELECT last_name, salary, job_id, RPAD(NVL(TO_CHAR(manager_id), 'No Manager'), 10) "Manager #", LPAD(TO_CHAR(salary * 12, '$999,999'), 12) "Total Income" FROM employees WHERE (job_id LIKE 'MK%' OR manager_id IS NULL) AND salary * (1 + NVL(commission_pct, 0)) + 1000 > 10000 ORDER BY 2 DESC;
Saad
James
4.
YuJin
SELECT RPAD(CONCAT(CONCAT(first_name,' '),last_name), 25) "Employee", job_id, LPAD(TRIM(TO_CHAR(salary, '$999,999')), 12, '=') "Salary", department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE UPPER(department_name) IN ('MARKETING', 'SALES') AND salary < (SELECT MAX(salary) FROM employees WHERE SUBSTR(job_id, 4) NOT IN ('PRES','VP','MAN','MGR')) ORDER BY 1;
Saad
James
5.
YuJin
SELECT last_name, salary, job_id FROM employees WHERE UPPER(job_id) NOT LIKE '%PRES' AND salary > (SELECT MAX(salary) FROM employees JOIN departments USING (department_id) JOIN locations USING (location_id) WHERE UPPER(city) IN ('TORONTO','OXFORD'));
Saad
James
6.
YuJin
SELECT last_name, first_name, job_id, hire_date FROM employees WHERE hire_date > (SELECT MAX(hire_date) FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE UPPER(department_name) = 'IT')) AND (department_id != (SELECT department_id FROM departments WHERE UPPER(department_name) = 'EXECUTIVE') OR department_id IS NULL) ORDER BY 4 DESC;
Saad
James
7.
YuJin
SELECT department_id, job_id, AVG(salary) "Average Dept/Job Pay", MIN(salary) "Lowest Dept/Job Pay" FROM employees WHERE UPPER(job_id) NOT LIKE '%VP' AND department_id NOT IN (SELECT department_id FROM departments WHERE UPPER(department_name) IN ('IT','SALES')) GROUP BY department_id, job_id HAVING MIN(salary) BETWEEN 5000 AND 15000 ORDER BY 1, 2;
Saad
James
8.
YuJin
SELECT department_id, department_name, RPAD(TRIM(NVL(city, 'Not Assinged Yet')), 25, ' ') "City", COUNT(DISTINCT job_id) "# of Jobs" FROM employees RIGHT OUTER JOIN departments USING (department_id) LEFT OUTER JOIN locations USING (location_id) GROUP BY department_id, department_name, city;
Saad
James