# Difference between revisions of "J.Y.S"

## Member List

First Name Last Name wiki id Learn e-mail Phone
YuJin Jeong yujin.jeong yjeong 647 - 832 - 6771
James

# Assignment 1

## Due Date

Thursday, October 21st, 4:00pm Printout submission only( SELECT Statements + ALL outputs)

## 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;

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  salary;

James

### 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;

James

### 4.

YuJin

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;

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'));

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;

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;

James

YuJin