J.Y.S
Revision as of 15:30, 27 November 2010 by Yujin.jeong (talk | contribs) (→DML Statements (INSERT INTO))
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), 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, Bathrooms NUMBER(1) NOT NULL, ListingPrice NUMBER(6) NOT NULL, AreaID NUMBER(4) NOT NULL, CONSTRAINT property_propertyid_pk PRIMARY KEY(PropertyID), CONSTRAINT property_bedrooms_ck CHECK(Bedrooms BETWEEN 1 AND 9), CONSTRAINT property_bathrooms_ck CHECK(Bathrooms BETWEEN 1 AND 9), CONSTRAINT property_listingprice_ck CHECK(ListingPrice BETWEEN 1 AND 4999999), CONSTRAINT property_areaid_fk FOREIGN KEY(AreaID) REFERENCES AREA(AreaID) );
4. SINGLE
CREATE TABLE SINGLE ( PropertyID NUMBER(6), LotSize NUMBER(8,2) NOT NULL, CONSTRAINT single_propertyID_pk PRIMARY KEY(PropertyID), CONSTRAINT single_propertyID_fk FOREIGN KEY(PropertyID) REFERENCES PROPERTY(PropertyID), CONSTRAINT single_lotsize_ck CHECK(LotSize > 0) );
5. CONDO
CREATE TABLE CONDO ( PropertyID NUMBER(6), Fee NUMBER(8,2) NOT NULL, CONSTRAINT condo_propertyID_pk PRIMARY KEY(PropertyID), CONSTRAINT condo_propertyID_fk FOREIGN KEY(PropertyID) REFERENCES PROPERTY(PropertyID), 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 ADVERTISEMENT ( PropertyID NUMBER(6), OutletID NUMBER(4), AdvDate DATE, AdvCost NUMBER(6) NOT NULL, CONSTRAINT ad_adid_pk PRIMARY KEY(PropertyID, OutletID), CONSTRAINT ad_propertyid_fk FOREIGN KEY(PropertyID) REFERENCES PROPERTY(PropertyID), CONSTRAINT ad_outletid_fk FOREIGN KEY(OutletID) REFERENCES OUTLET(OutletID), CONSTRAINT ad_advcost_ck CHECK(AdvCost > 0) );
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 CLIENT(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) );
DML Statements (INSERT INTO)
== Sample Data ==
write sample data for each table that is separated by comma !
e.g.)
1. AREA 1001, 'Bayview Village', 'Adrienne Clarkson Public School', 'Amesbury Middle School', 'Bayview Secondary School', 'Upper middle class to upper class neighborhood in Toronto.'
CREATE VIEW
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;