|
|
(84 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==
| |
− | ===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, 'Mon') IN ('May', 'Nov')
| |
− | ORDER BY hire_date DESC;
| |
− | </pre>
| |
− | ''Saad''
| |
− | <pre>
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | </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 salary;
| |
− | </pre>
| |
− | ''Saad''
| |
− | <pre>
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | </pre>
| |
− | ===3.===
| |
− | ''YuJin''
| |
− | <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 (job_id LIKE 'MK%' OR NVL(manager_id, 0) = 0)
| |
− | AND salary*(1 + NVL(commission_pct,0)) + 1000 > 10000
| |
− | ORDER BY salary;
| |
− | </pre>
| |
− | ''Saad''
| |
− | <pre>
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | </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 JOIN departments
| |
− | USING (department_id)
| |
− | WHERE SUBSTR(job_id, 4) NOT IN ('PRES','VP','MAN','MGR')
| |
− | AND SUBSTR(job_id, 1, 2) IN ('MK', 'SA')
| |
− | ORDER BY 1;
| |
− | </pre>
| |
− | ''Saad''
| |
− | <pre>
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | </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>
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | </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>
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | </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>
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | </pre>
| |
− |
| |
− | ===8.===
| |
− | ''YuJin''
| |
− | <pre>
| |
− | </pre>
| |
− | ''Saad''
| |
− | <pre>
| |
− | </pre>
| |
− | ''James''
| |
− | <pre>
| |
− | </pre>
| |