J.Y.S

This page is to efficiently manage our group assignments!

Member List

• Email All

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

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

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