|
|
(51 intermediate revisions by 3 users not shown) |
Line 1: |
Line 1: |
− | This page is to efficiently manage our group assignments!
| |
− | == Member List ==
| |
− | *[mailto:,,yjeong@learn.senecac.on.ca,?subject=dbs301 Email All]
| |
| | | |
− | {| class="wikitable sortable" border="1" cellpadding="5"
| |
− | ! First Name !! Last Name !! wiki id !! Learn e-mail !! Phone
| |
− | |-
| |
− | |[[User:Yujin.jeong | YuJin]]||Jeong||[[Special:Contributions/yujin.jeong | yujin.jeong]]||[mailto:yjeong@learn.senecac.on.ca?sujbect=gam666 yjeong]|| 647 - 832 - 6771
| |
− | |-
| |
− |
| |
− | |-
| |
− | |Saad|| || || ||
| |
− | |-
| |
− |
| |
− | |-
| |
− | |James|| || || ||
| |
− | |-
| |
− | |}
| |
− |
| |
− | =Assignment 1=
| |
− | ==Due Date==
| |
− | Thursday, October 21st, 4:00pm
| |
− | Printout submission only( SELECT Statements + ALL outputs)
| |
− | ==Description==
| |
− | [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''
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− | ''Saad''
| |
− | <pre>
| |
− | 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
| |
− | /
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− |
| |
− | ===2.===
| |
− | ''YuJin''
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− | ''Saad''
| |
− | <pre>
| |
− | 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)
| |
− | /
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− |
| |
− | ===3.===
| |
− | ''YuJin''
| |
− | <pre>
| |
− | 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;
| |
− |
| |
− | </pre>
| |
− | ''Saad''
| |
− | <pre>
| |
− | 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
| |
− | /
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− |
| |
− | ===4.===
| |
− | ''YuJin''
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− | ''Saad''
| |
− | <pre>
| |
− | 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
| |
− | /
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− |
| |
− | ===5.===
| |
− | ''YuJin''
| |
− | <pre>
| |
− | 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'));
| |
− |
| |
− | </pre>
| |
− | ''Saad''
| |
− | <pre>
| |
− | 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'
| |
− | /
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | 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'
| |
− | </pre>
| |
− |
| |
− | ===6.===
| |
− | ''YuJin''
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− | ''Saad''
| |
− | <pre>
| |
− | 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
| |
− | /
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− |
| |
− | ===7.===
| |
− | ''YuJin''
| |
− | <pre>
| |
− | 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;
| |
− |
| |
− | </pre>
| |
− | ''Saad''
| |
− | <pre>
| |
− | 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
| |
− | /
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | 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
| |
− | </pre>
| |
− |
| |
− | ===8.===
| |
− | ''YuJin''
| |
− | <pre>
| |
− | 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;
| |
− |
| |
− | </pre>
| |
− | ''Saad''
| |
− | <pre>
| |
− | 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
| |
− | /
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | </pre>
| |