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
select employee_id "Emp#", (last_name ||', '|| first_name) "Full Name", job_id "Job", to_char(trunc(hire_date, 'MONTH'), 'FMMonth Ddspth "in the year" YYYY') "Start Date" from employees where to_char(hire_date, 'fmMonth') in ('May', 'November') order by hire_date desc;
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
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 job_id IN ('IT_PROG','ST CLERK') AND (salary BETWEEN 5000 AND 12000) /
James
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 upper(job_id) in ('IT_PROG', 'ST_CLERK') order by employee_id;
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
SELECT last_name, salary, job_id, NVL(TO_CHAR(manager_id),'No Manager') Manager#, TO_CHAR(salary*12,'$999,999') "Total Income" FROM employees WHERE (manager_id IS NULL OR job_id LIKE 'MK%') AND ( salary* (NVL(commission_pct,0) + 1) + 1000 ) > 10000 ORDER BY salary DESC /
James
select last_name, salary, job_id, nvl(to_char(manager_id),'No Manager') Manager#, to_char(salary*12,'$999,999') "Total Income" from employees where (upper(job_id) like 'MK%' or manager_id IS NULL) and salary * (1 + nvl(commission_pct, 0)) + 1000 > 10000 order by 2 desc;
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
SELECT SUBSTR(first_name||' '||last_name, 25) Employee, job_id, LPAD(TO_CHAR(salary,'$99,999'), 12, '=') Salary, department_name FROM employees e JOIN departments d ON (d.department_id=e.department_id) WHERE salary < (SELECT MAX(salary) FROM employees WHERE UPPER(SUBSTR(job_id,4)) NOT IN ('VP', 'PRES', 'MAN', 'MGR') ) AND UPPER(department_name) IN ('MARKETING','SALES') ORDER BY first_name, last_name /
James
select substr(first_name ||' '|| last_name) "Employee", job_id, 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 employee;
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
SELECT last_name, salary, job_id FROM employees WHERE salary > (SELECT MAX(salary) FROM locations JOIN departments USING (location_id) JOIN employees USING (department_id) WHERE UPPER(city) IN ('TORONTO','OXFORD') ) AND UPPER(job_id) NOT LIKE '%PRES' /
James
select last_name, salary, job_id from employees where salary > (select max(salary) from employees join departments using (department_id) join locations using (location_id) where upper(city) in ('TORONTO','OXFORD')) and upper(job_id) not like '%PRES'
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
SELECT last_name, first_name, job_id, hire_date FROM employees e 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 hire_date DESC /
James
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 hire_date desc;
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
SELECT department_id, job_id, AVG(salary) "Average Dept/Job Pay", MIN(salary) "Lowest Dept/Job Pay" FROM employees WHERE department_id <> ALL (SELECT department_id FROM departments WHERE UPPER(department_name) IN ('IT','SALES') ) AND job_id NOT LIKE '%VP' GROUP BY department_id, job_id HAVING MIN(salary) BETWEEN 5000 AND 15000 ORDER BY department_id, job_id /
James
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 != all(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 department_id, job_id
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
SELECT department_id, department_name, SUBSTR(NVL(city,'Not Assigned Yet'),1,25) City, COUNT(DISTINCT job_id) "# of Jobs" FROM locations l RIGHT OUTER JOIN departments d USING (location_id) LEFT OUTER JOIN employees e USING (department_id) GROUP BY department_id, department_name, city /
James
select department_id, department_name, substr(nvl(city,'Not Assigned Yet'),1,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