Difference between revisions of "J.Y.S"
Yujin.jeong (talk | contribs) (→2.) |
Yujin.jeong (talk | contribs) (→2.) |
||
Line 43: | Line 43: | ||
''YuJin'' | ''YuJin'' | ||
<pre> | <pre> | ||
− | SELECT | + | SELECT 'Employee named ' || first_name || ' ' || last_name || ' who is ' |
− | + | || job_id || ' will have a new salary of $' || salary * 1.15 "Happy Employees" | |
FROM employees | FROM employees | ||
− | WHERE | + | WHERE salary BETWEEN 5000 AND 12000 |
− | AND | + | AND job_id IN ('IT_PROG', 'ST_CLERK') |
− | ORDER BY employee_id; | + | ORDER BY employee_id; |
− | |||
</pre> | </pre> | ||
''Saad'' | ''Saad'' |
Revision as of 20:40, 16 October 2010
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
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, 'Mon') IN ('May', 'Nov') ORDER BY hire_date DESC;
Saad
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