Difference between revisions of "J.Y.S"

From CDOT Wiki
Jump to: navigation, search
(7.)
(Blanked the page)
 
(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>
 

Latest revision as of 20:04, 17 September 2011