Difference between revisions of "J.Y.S"
Yujin.jeong (talk | contribs) (→Solution: uploaded solution for quesiton 2) |
Yujin.jeong (talk | contribs) (→Solution: uploaded solution for quesiton 3) |
||
| Line 28: | Line 28: | ||
[https://cs.senecac.on.ca/~nconkic/assign1.doc Assignment1] | [https://cs.senecac.on.ca/~nconkic/assign1.doc Assignment1] | ||
==Solution== | ==Solution== | ||
| − | 1. | + | ===1.=== |
| − | |||
''YuJin'' | ''YuJin'' | ||
<pre> | <pre> | ||
| Line 47: | Line 46: | ||
<pre> | <pre> | ||
</pre> | </pre> | ||
| − | 2. | + | ===2.=== |
| − | |||
''YuJin'' | ''YuJin'' | ||
<pre> | <pre> | ||
| Line 67: | Line 65: | ||
<pre> | <pre> | ||
</pre> | </pre> | ||
| − | 3. | + | ===3.=== |
| − | |||
| − | |||
''YuJin'' | ''YuJin'' | ||
<pre> | <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> | </pre> | ||
''Saad'' | ''Saad'' | ||
| Line 83: | Line 86: | ||
</pre> | </pre> | ||
| − | 4. | + | ===4.=== |
| − | |||
| − | |||
''YuJin'' | ''YuJin'' | ||
<pre> | <pre> | ||
| Line 99: | Line 100: | ||
</pre> | </pre> | ||
| − | 5. | + | ===5.=== |
| − | |||
| − | |||
''YuJin'' | ''YuJin'' | ||
<pre> | <pre> | ||
| Line 115: | Line 114: | ||
</pre> | </pre> | ||
| − | 6. | + | ===6.=== |
| − | |||
| − | |||
''YuJin'' | ''YuJin'' | ||
<pre> | <pre> | ||
Revision as of 12:13, 5 October 2010
This page is to efficiently manage DBS301 Group assignments together !
Contents
Member List
| First Name | Last Name | wiki id | Learn e-mail | Phone |
|---|---|---|---|---|
| YuJin | Jeong | yujin.jeong | yjeong | 647 - 832 - 6771 |
| Saad | ||||
| James | ||||
| Dale |
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
Dale
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 salary;
Saad
James
Dale
3.
YuJin
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;
Saad
James
Dale
4.
YuJin
Saad
James
Dale
5.
YuJin
Saad
James
Dale
6.
YuJin
Saad
James
Dale
