This page is to efficiently manage our group assignments!
Member List
| First Name | Last Name | wiki id | Learn e-mail | Phone |
|---|---|---|---|---|
| YuJin | Jeong | yujin.jeong | yjeong | 647 - 832 - 6771 |
| Saad | ||||
| James |
Assignment 3 (worth 9%)
Due Date
Thursday, December 2nd, 3:00pm Printout of all TIC charts and DDL / DML statements
Solution
DDL Statements (CREATE TABLE)
1. AREA
CREATE TABLE AREA ( AreaID NUMBER(4) CONSTRAINT area_areaid_pk PRIMARY KEY, AreaName VARCHAR2(30) NOT NULL CONSTRAINT area_areaname_uk UNIQUE, ElmSchool VARCHAR2(40), MidSchool VARCHAR2(40), HighSchool VARCHAR2(40), Comments VARCHAR2(100) );
2. AGENCY
CREATE TABLE AGENCY ( AgencyID NUMBER(4) CONSTRAINT agency_agencyid_pk PRIMARY KEY, AgencyName VARCHAR2(40) NOT NULL CONSTRAINT agency_agencyname_uk UNIQUE, AgencyPhone VARCHAR2(15) NOT NULL CONSTRAINT agency_agencyphone_uk UNIQUE );
3. PROPERTY
CREATE TABLE PROPERTY
(
PropertyID NUMBER(6) CONSTRAINT property_propertyid_pk PRIMARY KEY,
Street VARCHAR2(40) NOT NULL,
City VARCHAR2(30) NOT NULL,
Province CHAR(2) NOT NULL,
PostalCode CHAR(6) NOT NULL,
Bedrooms NUMBER(1) NOT NULL
CONSTRAINT property_bedrooms_ck CHECK(Bedrooms BETWEEN 1 AND 9),
Bathrooms NUMBER(1) NOT NULL
CONSTRAINT property_bathrooms_ck CHECK(Bathrooms BETWEEN 1 AND 9),
ListingPrice NUMBER(6) NOT NULL
CONSTRAINT property_listingprice_ck CHECK(ListingPrice BETWEEN 1 AND 4999999),
AreaID NUMBER(4) NOT NULL
CONSTRAINT property_areaid_fk FOREIGN KEY(AreaID) REFERENCES AREA(AreaID)
);
4. SINGLE
CREATE TABLE SINGLE
(
PropertyID NUMBER(6) CONSTRAINT single_propertyID_pk PRIMARY KEY
CONSTRAINT single_propertyID_fk FOREIGN KEY(PropertyID)
REFERENCES PROPERTY(PropertyID),
LotSize NUMBER(8,2) NOT NULL CONSTRAINT single_lotsize_ck CHECK(LotSize > 0)
);
5. CONDO
CREATE TABLE CONDO
(
PropertyID NUMBER(6) CONSTRAINT condo_propertyID_pk PRIMARY KEY
CONSTRAINT condo_propertyID_fk FOREIGN KEY(PropertyID)
REFERENCES PROPERTY(PropertyID),
Fee NUMBER(8,2) NOT NULL CONSTRAINT condo_fee_ck CHECK(Fee > 0)
);
6. OUTLET
CREATE TABLE OUTLET
(
OutletID NUMBER(4),
OutletName VARCHAR2(40) NOT NULL,
OutletPhone VARCHAR2(15) NOT NULL,
OutletType CHAR(1) NOT NULL,
CONSTRAINT outlet_outletid_pk PRIMARY KEY(OutletID),
CONSTRAINT outlet_outlettype_ck CHECK(OutletType IN('N','M','W','F')),
CONSTRAINT outlet_outletname_type_uk UNIQUE(OutletName, OutletType)
);
7. ADVERTISEMENT
CREATE TABLE OUTLET
(
OutletID NUMBER(4),
OutletName VARCHAR2(40) NOT NULL,
OutletPhone VARCHAR2(15) NOT NULL,
OutletType CHAR(1) NOT NULL,
CONSTRAINT outlet_outletid_pk PRIMARY KEY(OutletID),
CONSTRAINT outlet_outlettype_ck CHECK(OutletType IN('N','M','W','F')),
CONSTRAINT outlet_outletname_type_uk UNIQUE(OutletName, OutletType)
);
8. CLIENT
CREATE TABLE CLIENT ( ClientID NUMBER(6), Fname VARCHAR2(30) NOT NULL, Lname VARCHAR2(30) NOT NULL, Phone VARCHAR2(15) NOT NULL, Email VARCHAR2(40), RefID NUMBER(6), CONSTRAINT client_clientid_pk PRIMARY KEY(ClientID), CONSTRAINT client_phone_uk UNIQUE(Phone), CONSTRAINT client_email_uk UNIQUE(Email), CONSTRAINT client_refid_fk FOREIGN KEY(RefID) REFERENCES CLEINT(ClientID) );
9. DEED
CREATE TABLE DEED ( PropertyID NUMBER(6), SellerID NUMBER(6), OwnPct NUMBER(5,2) NOT NULL, BuyerID NUMBER(6), BuyPrice NUMBER(7), CONSTRAINT deed_deedid_pk PRIMARY KEY(PropertyID, SellerID), CONSTRAINT deed_propertyid_fk FOREIGN KEY(PropertyID) REFERENCES PROPERTY(PropertyID), CONSTRAINT deed_sellerid_fk FOREIGN KEY(SellerID) REFERENCES CLIENT(ClientID), CONSTRAINT deed_buyerid_fk FOREIGN KEY(BuyerID) REFERENCES CLIENT(ClientID), CONSTRAINT deed_ownpct_ck CHECK(OwnPct > 0 AND OwnPct <= 100), CONSTRAINT deed_buyprice_ck CHECK(BuyPrice BETWEEN 1 AND 4999999) );
Assignment 1
Due Date
Thursday, October 21st, 4:00pm Printout submission only( SELECT Statements + ALL outputs)
Description
Solution
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
SQL Statements
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, 'mm') IN (05, 11)
ORDER BY 4 DESC;
Saad
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
/
James
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;
FINAL
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;
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
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)
/
James
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;
FINAL
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;
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
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
/
James
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;
FINAL
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;
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
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
/
James
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";
FINAL
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;
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
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'
/
James
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'
FINAL
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'));
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
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
/
James
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;
FINAL
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;
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
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
/
James
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
FINAL
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;
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
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
/
James
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
FINAL
SELECT department_id, department_name,
SUBSTR(NVL(city, 'Not Assinged 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;