Difference between revisions of "J.Y.S"

From CDOT Wiki
Jump to: navigation, search
(4.)
(Blanked the page)
 
(42 intermediate revisions by 2 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==
 
<pre>
 
SQL> SELECT  employee_id, RPAD(CONCAT(CONCAT(last_name, ', '),first_name), 25) "Full Name",
 
 
  2          job_id,
 
 
  3          TO_CHAR(TRUNC(hire_date, 'MONTH'), 'fmMonth Ddspth "in the year" YYYY')
 
 
  4          "Start Date"
 
 
  5  FROM    employees
 
 
  6  WHERE    TO_CHAR(hire_date, 'mm') IN (05, 11)
 
 
  7  ORDER BY 4 DESC;
 
 
 
 
EMPLOYEE_ID Full Name                JOB_ID    Start Date                                       
 
 
----------- ------------------------- ---------- -----------------------------------------       
 
 
        124 Mourgos, Kevin            ST_MAN    November First in the year 1999                 
 
 
        178 Grant, Kimberely          SA_REP    May First in the year 1999                       
 
 
        174 Abel, Ellen              SA_REP    May First in the year 1996                       
 
 
        104 Ernst, Bruce              IT_PROG    May First in the year 1991                       
 
 
 
 
SQL> SELECT    'Employee named ' || first_name || ' ' || last_name || ' who is '
 
 
  2            || job_id || ' will have a new salary of $' || salary * 1.15 "Happy Employees"
 
 
  3  FROM      employees
 
 
  4  WHERE    salary BETWEEN 5000 AND 12000
 
 
  5  AND      job_id IN ('IT_PROG', 'ST_CLERK')
 
 
  6  ORDER BY  employee_id;
 
 
 
 
Happy Employees                                                                                   
 
 
----------------------------------------------------------------------------------------------------
 
 
Employee named Alexander Hunold who is IT_PROG will have a new salary of $10350                   
 
 
Employee named Bruce Ernst who is IT_PROG will have a new salary of $6900                         
 
 
 
 
SQL> SELECT    last_name, salary, job_id,
 
 
  2    RPAD(NVL(TO_CHAR(manager_id), 'No Manager'), 10) "Manager #",
 
 
  3    LPAD(TO_CHAR(salary * 12, '$999,999'), 12) "Total Income"
 
 
  4  FROM    employees
 
 
  5  WHERE    (job_id LIKE 'MK%' OR manager_id IS NULL)
 
 
  6  AND      salary * (1 + NVL(commission_pct, 0)) + 1000 > 10000
 
 
  7  ORDER BY  2 DESC;
 
 
 
 
LAST_NAME                    SALARY JOB_ID    Manager #  Total Income                           
 
 
------------------------- ---------- ---------- ---------- ------------                           
 
 
King                          24000 AD_PRES    No Manager    $288,000                           
 
 
Hartstein                      13000 MK_MAN    100            $156,000                           
 
 
 
 
SQL> SELECT  RPAD(CONCAT(CONCAT(first_name,' '),last_name), 25) "Employee", job_id,
 
 
  2          LPAD(TRIM(TO_CHAR(salary, '$999,999')), 12, '=') "Salary", department_name
 
 
  3  FROM    employees e JOIN departments d
 
 
  4  ON      (e.department_id = d.department_id)
 
 
  5  WHERE    UPPER(department_name) IN ('MARKETING', 'SALES')
 
 
  6  AND      salary <
 
 
  7                    (SELECT MAX(salary)
 
 
  8                    FROM  employees
 
 
  9                    WHERE  SUBSTR(job_id, 4) NOT IN ('PRES','VP','MAN','MGR'))
 
 
10  ORDER BY 1;
 
 
 
 
Employee                  JOB_ID    Salary      DEPARTMENT_NAME                                 
 
 
------------------------- ---------- ------------ ------------------------------                 
 
 
Eleni Zlotkey            SA_MAN    =====$10,500 Sales                                           
 
 
Jonathon Taylor          SA_REP    ======$8,600 Sales                                           
 
 
Pat Fay                  MK_REP    ======$6,000 Marketing                                       
 
 
 
 
SQL>
 
 
SQL> SELECT last_name, salary, job_id
 
 
  2  FROM  employees
 
 
  3  WHERE  UPPER(job_id) NOT LIKE '%PRES'
 
 
  4  AND    salary >
 
 
  5                (SELECT MAX(salary)
 
 
  6                  FROM  employees
 
 
  7                  JOIN  departments USING (department_id)
 
 
  8                  JOIN  locations  USING (location_id)
 
 
  9                  WHERE  UPPER(city) IN ('TORONTO','OXFORD'));
 
 
 
 
LAST_NAME                    SALARY JOB_ID                                                       
 
 
------------------------- ---------- ----------                                                   
 
 
Kochhar                        17000 AD_VP                                                       
 
 
De Haan                        17000 AD_VP                                                       
 
 
 
 
SQL> SELECT last_name, first_name, job_id, hire_date
 
 
  2  FROM  employees
 
 
  3  WHERE  hire_date >
 
 
  4                    (SELECT MAX(hire_date)
 
 
  5                      FROM  employees
 
 
  6                      WHERE  department_id =
 
 
  7                                            (SELECT department_id
 
 
  8                                            FROM  departments
 
 
  9                                            WHERE  UPPER(department_name) = 'IT'))
 
 
10  AND (department_id !=
 
 
11                    (SELECT department_id
 
 
12                      FROM  departments
 
 
13                      WHERE  UPPER(department_name) = 'EXECUTIVE')
 
 
14  OR  department_id IS NULL)
 
 
15  ORDER BY 4 DESC;
 
 
 
 
LAST_NAME                FIRST_NAME          JOB_ID    HIRE_DATE                               
 
 
------------------------- -------------------- ---------- ---------                               
 
 
Zlotkey                  Eleni                SA_MAN    29-JAN-00                               
 
 
Mourgos                  Kevin                ST_MAN    16-NOV-99                               
 
 
Grant                    Kimberely            SA_REP    24-MAY-99                               
 
 
 
 
SQL> SELECT department_id, job_id, AVG(salary) "Average Dept/Job Pay",
 
 
  2        MIN(salary) "Lowest Dept/Job Pay"
 
 
  3  FROM  employees
 
 
  4  WHERE  UPPER(job_id) NOT LIKE '%VP'
 
 
  5  AND    department_id NOT IN
 
 
  6              (SELECT department_id
 
 
  7                FROM  departments
 
 
  8                WHERE  UPPER(department_name) IN ('IT','SALES'))
 
 
  9  GROUP BY department_id, job_id
 
 
10  HAVING  MIN(salary) BETWEEN 5000 AND 15000
 
 
11  ORDER BY 1, 2;
 
 
 
 
DEPARTMENT_ID JOB_ID    Average Dept/Job Pay Lowest Dept/Job Pay                                 
 
 
------------- ---------- -------------------- -------------------                                 
 
 
          20 MK_MAN                    13000              13000                                 
 
 
          20 MK_REP                    6000                6000                                 
 
 
          50 ST_MAN                    5800                5800                                 
 
 
          110 AC_ACCOUNT                8300                8300                                 
 
 
          110 AC_MGR                    12000              12000                                 
 
 
 
 
SQL> SELECT    department_id, department_name,
 
 
  2            RPAD(TRIM(NVL(city, 'Not Assinged Yet')), 25, ' ') "City",
 
 
  3            COUNT(DISTINCT job_id) "# of Jobs"
 
 
  4  FROM      employees
 
 
  5  RIGHT OUTER JOIN  departments USING (department_id)
 
 
  6  LEFT  OUTER JOIN  locations  USING (location_id)
 
 
  7  GROUP BY  department_id, department_name, city;
 
 
 
 
DEPARTMENT_ID DEPARTMENT_NAME                City                      # of Jobs                 
 
 
------------- ------------------------------ ------------------------- ----------                 
 
 
          110 Accounting                    Seattle                            2                 
 
 
          50 Shipping                      South San Francisco                2                 
 
 
          80 Sales                          Oxford                            2                 
 
 
          10 Administration                Seattle                            1                 
 
 
          190 Contracting                    Seattle                            0                 
 
 
          90 Executive                      Seattle                            2                 
 
 
          20 Marketing                      Toronto                            2                 
 
 
          60 IT                            Southlake                          1                 
 
 
 
 
8 rows selected.
 
 
 
 
SQL> spool off
 
</pre>
 
==SQL Statements==
 
===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, 'mm') IN (05, 11)
 
ORDER BY 4 DESC;
 
</pre>
 
''Saad''
 
<pre>
 
SELECT employee_id,
 
        SUBSTR(last_name ||' '|| first_name, 1, 25) "Full Name",
 
        job_id,
 
        TO_CHAR(hire_date,'fmMonth "First in the year" YYYY') "Start Date"
 
FROM employees
 
WHERE TO_CHAR(hire_date,'fmMON') IN ('MAY','NOV')
 
ORDER BY hire_date DESC
 
/
 
</pre>
 
''James''
 
<pre>
 
select employee_id "Emp#",
 
    (last_name ||', '|| first_name) "Full Name",
 
          job_id "Job",
 
          to_char(trunc(hire_date, 'MONTH'), 'FMMonth Ddspth "in the year"
 
YYYY') "Start Date"
 
from employees
 
where to_char(hire_date, 'fmMonth') in ('May', 'November')
 
order by hire_date desc;
 
</pre>
 
 
''FINAL''
 
<pre>
 
SELECT  employee_id, SUBSTR(last_name ||' '|| first_name, 1, 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, 'mm') IN (05, 11)
 
ORDER BY hire_date DESC;
 
</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  employee_id;
 
</pre>
 
''Saad''
 
<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 job_id IN ('IT_PROG','ST CLERK')
 
      AND (salary BETWEEN 5000 AND 12000)
 
/
 
</pre>
 
''James''
 
<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 upper(job_id) in ('IT_PROG', 'ST_CLERK')
 
order by employee_id;
 
</pre>
 
 
''FINAL''
 
<pre>
 
SELECT    'Employee named ' || first_name || ' ' || last_name || ' who is '
 
          || job_id || ' will have a new salary of ' || TO_CHAR(salary * 1.15, 'fm$999999') "Happy Employees"
 
FROM      employees
 
WHERE    salary BETWEEN 5000 AND 12000
 
AND      UPPER(job_id) IN ('IT_PROG', 'ST_CLERK')
 
ORDER BY  employee_id;
 
</pre>
 
 
===3.===
 
''YuJin''
 
<pre>
 
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;
 
 
</pre>
 
''Saad''
 
<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 (manager_id IS NULL
 
        OR job_id LIKE 'MK%')
 
        AND ( salary* (NVL(commission_pct,0) + 1) + 1000 ) > 10000
 
ORDER BY salary DESC
 
/
 
</pre>
 
''James''
 
<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 (upper(job_id) like 'MK%' or manager_id IS NULL)
 
and salary * (1 + nvl(commission_pct, 0)) + 1000 > 10000
 
order by 2 desc;
 
</pre>
 
 
''FINAL''
 
<pre>
 
SELECT    last_name, salary, job_id,
 
          SUBSTR(NVL(TO_CHAR(manager_id), 'No Manager'), 1, 10) "Manager #",
 
          LPAD(TO_CHAR(salary * 12, '$999,999'), 12) "Total Income"
 
FROM      employees
 
WHERE    (UPPER(job_id) LIKE 'MK%' OR manager_id IS NULL)
 
AND      salary * (1 + NVL(commission_pct, 0)) + 1000 > 10000
 
ORDER BY  salary DESC;
 
 
</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 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;
 
</pre>
 
''Saad''
 
<pre>
 
SELECT SUBSTR(first_name||' '||last_name, 25) Employee, job_id,
 
        LPAD(TO_CHAR(salary,'$99,999'), 12, '=') Salary, department_name
 
FROM employees e JOIN departments d
 
ON (d.department_id=e.department_id)
 
WHERE salary < (SELECT MAX(salary)
 
                    FROM employees
 
                    WHERE UPPER(SUBSTR(job_id,4)) NOT IN ('VP', 'PRES', 'MAN', 'MGR')
 
                    )
 
        AND UPPER(department_name) IN ('MARKETING','SALES')
 
ORDER BY first_name, last_name
 
/
 
</pre>
 
''James''
 
<pre>
 
select substr(first_name ||' '|| last_name) "Employee", job_id, 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 "Employee";
 
</pre>
 
 
''FINAL''
 
<pre>
 
SELECT  SUBSTR(first_name ||' '|| last_name, 1, 25) "Employee", job_id,
 
        LPAD(TO_CHAR(salary, 'fm$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(UPPER(job_id), 4) NOT IN ('PRES','VP','MAN','MGR'))
 
ORDER BY 1;
 
</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>
 
SELECT last_name, salary, job_id
 
FROM employees
 
WHERE salary > (SELECT MAX(salary)
 
                    FROM locations JOIN departments
 
                    USING (location_id)
 
                    JOIN employees
 
                    USING (department_id)
 
                    WHERE UPPER(city) IN ('TORONTO','OXFORD')
 
                    )
 
        AND UPPER(job_id) NOT LIKE '%PRES'
 
/
 
</pre>
 
''James''
 
<pre>
 
select last_name, salary, job_id
 
from employees
 
where salary > (select max(salary)
 
                      from employees join departments
 
                      using (department_id)
 
                      join locations
 
                      using (location_id)
 
                      where upper(city) in ('TORONTO','OXFORD'))
 
and upper(job_id) not like '%PRES'
 
</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>
 
SELECT last_name, first_name, job_id, hire_date
 
FROM employees e
 
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 hire_date DESC
 
/
 
</pre>
 
''James''
 
<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 hire_date desc;
 
</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>
 
SELECT department_id, job_id, AVG(salary) "Average Dept/Job Pay", MIN(salary) "Lowest Dept/Job Pay"
 
FROM employees
 
WHERE department_id <> ALL (SELECT department_id
 
                                FROM departments
 
                                WHERE UPPER(department_name) IN ('IT','SALES')
 
                                )
 
        AND job_id NOT LIKE '%VP'
 
GROUP BY department_id, job_id
 
HAVING MIN(salary) BETWEEN 5000 AND 15000
 
ORDER BY department_id, job_id
 
/
 
</pre>
 
''James''
 
<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 != all(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 department_id, job_id
 
</pre>
 
 
===8.===
 
''YuJin''
 
<pre>
 
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;
 
 
</pre>
 
''Saad''
 
<pre>
 
SELECT department_id, department_name, SUBSTR(NVL(city,'Not Assigned Yet'),1,25) City,
 
        COUNT(DISTINCT job_id) "# of Jobs"
 
FROM locations l RIGHT OUTER JOIN departments d
 
USING (location_id)
 
LEFT OUTER JOIN employees e
 
USING (department_id)
 
GROUP BY  department_id, department_name, city
 
/
 
</pre>
 
''James''
 
<pre>
 
select department_id, department_name, substr(nvl(city,'Not Assigned Yet'),1,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
 
</pre>
 

Latest revision as of 20:04, 17 September 2011