1
edit
Changes
→Assignment 1
[https://cs.senecac.on.ca/~nconkic/assign1.doc Assignment1]
==Solution==
<pre>
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
</pre>
==SQL Statements==
===1.===
''YuJin''