Difference between revisions of "J.Y.S"

From CDOT Wiki
Jump to: navigation, search
(Solution: uploaded solution for quesiton 2)
(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.===
<br />
 
 
''YuJin''
 
''YuJin''
 
<pre>
 
<pre>
Line 47: Line 46:
 
<pre>
 
<pre>
 
</pre>
 
</pre>
2.
+
===2.===
<br />
 
 
''YuJin''
 
''YuJin''
 
<pre>
 
<pre>
Line 67: Line 65:
 
<pre>
 
<pre>
 
</pre>
 
</pre>
3.
+
===3.===
2.
 
<br />
 
 
''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.===
2.
 
<br />
 
 
''YuJin''
 
''YuJin''
 
<pre>
 
<pre>
Line 99: Line 100:
 
</pre>
 
</pre>
  
5.
+
===5.===
2.
 
<br />
 
 
''YuJin''
 
''YuJin''
 
<pre>
 
<pre>
Line 115: Line 114:
 
</pre>
 
</pre>
  
6.
+
===6.===
2.
 
<br />
 
 
''YuJin''
 
''YuJin''
 
<pre>
 
<pre>

Revision as of 13:13, 5 October 2010

This page is to efficiently manage DBS301 Group assignments together !

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

Assignment1

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