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=dbs301 yjeong]|| 647 - 832 - 6771
|-
|-
|Saad||Mohammad || saadism ||[mailto:smohammad8@learn.senecac.on.ca?subject=db301 smohammad8] ||(647) 894-7223
|-
|-
|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
<pre>
CREATE TABLE AREA
(
AreaID NUMBER(4) CONSTRAINT area_areaid_pk PRIMARY KEY,
AreaName VARCHAR2(15) NOT NULL CONSTRAINT area_areaname_uk UNIQUE,
ElmSchool VARCHAR2(40),
MidSchool VARCHAR2(40),
HighSchool VARCHAR2(40),
Comments VARCHAR2(100)
);
</pre>
2. AGENCY
<pre>
CREATE TABLE AGENCY
(
AgencyID NUMBER(4) CONSTRAINT agency_agencyid_pk PRIMARY KEY,
AgencyName VARCHAR2(30) NOT NULL CONSTRAINT agency_agencyname_uk UNIQUE,
AgencyPhone VARCHAR2(15) NOT NULL CONSTRAINT agency_agencyphone_uk UNIQUE
);
</pre>
3. PROPERTY
<pre>
CREATE TABLE PROPERTY
(
PropertyID NUMBER(6),
Street VARCHAR2(40) NOT NULL,
City VARCHAR2(15) NOT NULL,
Province CHAR(2) NOT NULL,
PostalCode CHAR(6) NOT NULL,
Bedrooms NUMBER(1) NOT NULL,
Bathrooms NUMBER(1) NOT NULL,
ListingPrice NUMBER(7) NOT NULL,
AreaID NUMBER(4) NOT NULL,
AgencyID NUMBER(4),
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)
);
</pre>
4. SINGLE
<pre>
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)
);
</pre>
5. CONDO
<pre>
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)
);
</pre>
6. OUTLET
<pre>
CREATE TABLE OUTLET
(
OutletID NUMBER(4),
OutletName VARCHAR2(20) 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)
);
</pre>
7. ADVERTISEMENT
<pre>
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)
);
</pre>
Before Creating CLIENT table, you should rename the existing CLIENT table so that
we can use that name. Execute following:
<pre>
ALTER TABLE CLIENT RENAME TO CLIENT_2;
</pre>
8. CLIENT
<pre>
CREATE TABLE CLIENT
(
ClientID NUMBER(6),
Fname VARCHAR2(15) NOT NULL,
Lname VARCHAR2(15) NOT NULL,
Phone VARCHAR2(15) NOT NULL,
Email VARCHAR2(30),
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)
);
</pre>
9. DEED
<pre>
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)
);
</pre>
==== DML Statements (INSERT INTO) ====
1. AREA<BR>
<pre>
INSERT ALL
INTO AREA VALUES(1001, 'Bayview Village', 'Adrienne Clarkson Public School', 'Amesbury Middle School', 'Bayview Secondary School', 'Upper middle class to upper class neighborhood in Toronto.')
INTO AREA VALUES(3001,'Laurelwood', 'Elizabeth Ziegler Primary School','Waterloo Middle School','Saint David Catholic Secondary School','Close to University of Waterloo and many popular amenities.')
INTO AREA VALUES(4001,'Silver Heights','Alison Park Primary School','Eastwood Middle School','King Edward Secondary School',null)
INTO AREA VALUES(5001,'Kitsilano','Henry Hudson Elementary School','St George School','Kitsilano Secondary School','Very well-off area.')
SELECT * FROM DUAL;
</pre>
<BR>
2. AGENCY<BR>
<pre>
INSERT ALL
INTO AGENCY VALUES(1055,'Sutton Group Regal Realty','416.875.9873')
INTO AGENCY VALUES(3079,'MacDonald Realty','604.986.0231')
INTO AGENCY VALUES(7024,'Royal LePage Groupe Newton','514.234.9283')
SELECT * FROM DUAL;
</pre>
<BR>
3. PROPERTY<BR>
<pre>
INSERT ALL
INTO PROPERTY VALUES(134982,q'[27 King's College Circle]','Toronto','ON','M5S1A1',2,2,380000,1001,1055)
INTO PROPERTY VALUES(149800,'17 Pleasant View Dr.','Toronto','ON','M2J2H1',3,2,470000,1001, null)
INTO PROPERTY VALUES(152932,'17 Barberry Pl. #1313','Toronto','ON','M8W7V4',1,1,259888,1001, null)
INTO PROPERTY VALUES(495820,'776 Laurelwood Dr.','Waterloo','ON','H3A2T5',2,2,295000,3001,3079)
INTO PROPERTY VALUES(500211,'409 Kelso Cr.','Cambridge','ON','H3Y7S3',3,2,485000,4001, null)
INTO PROPERTY VALUES(139204,'3043 Crown St.','Vancouver','BC','U3J3M6',5,3,1398000,5001,7024)
SELECT * FROM DUAL;
</pre>
<BR>
4. SINGLE<BR>
<pre>
INSERT ALL
INTO SINGLE VALUES(134982,3716)
INTO SINGLE VALUES(149800,1005)
INTO SINGLE VALUES(139204,5400)
SELECT * FROM DUAL;
</pre>
<BR>
5. CONDO<BR>
<pre>
INSERT ALL
INTO CONDO VALUES(152932,250)
INTO CONDO VALUES(495820,320)
INTO CONDO VALUES(500211,550)
SELECT * FROM DUAL;
</pre>
<BR>
6. OUTLET<BR>
<pre>
INSERT ALL
INTO OUTLET VALUES(1000,'Toronto Star','416.923.1782','N')
INTO OUTLET VALUES(2001,'Waterloo Living','519.098.9283','M')
INTO OUTLET VALUES(8001,'Canadian Life','416.982.0192','W')
SELECT * FROM DUAL;
</pre>
<BR>
7. ADVERTISEMENT<BR>
<pre>
INSERT ALL
INTO ADVERTISEMENT VALUES(134982,1000,'25-NOV-10',350)
INTO ADVERTISEMENT VALUES(149800,1000,'27-NOV-10',250)
INTO ADVERTISEMENT VALUES(495820,2001,'27-NOV-10',300)
SELECT * FROM DUAL;
</pre>
<BR>
8. CLIENT<BR>
<pre>
INSERT ALL
INTO CLIENT VALUES(1010,'Andrew','Lee','647.978.0192','andrew11@hotmail.com', null)
INTO CLIENT VALUES(1011,'Daniel','Smith','416.895.9762','dsmith@gmail.com',5010)
INTO CLIENT VALUES(1012,'Olivia','Wilson','647.915.2738','oliviaorange@gmail.com', null)
INTO CLIENT VALUES(1013,'Jayden','Martin','519.225.1928','jmartin@rogers.com', null)
INTO CLIENT VALUES(1014,'Maria','Brown','416.182.0021','maria.brown@hotmail.com', null)
INTO CLIENT VALUES(2010,'Ethan','Roy','226.289.0394','ethanrockstar@bell.com', null)
INTO CLIENT VALUES(2011,'Sophia','Tremblay','647.297.2918','stremblay@gmail.com',5010)
INTO CLIENT VALUES(3010,'Ryan','Wilson','604.837.1029','wilsonryan@gmail.com', null)
INTO CLIENT VALUES(3012,'Luis','Jones','416.832.6771','luis7787@rogers.com', null)
INTO CLIENT VALUES(5010,'Mary','Anderson','647.234.5690','andersonpink@gmail.com', null)
INTO CLIENT VALUES(7010,'James','White','647.298.9182','james.white@bell.com',1010)
SELECT * FROM DUAL;
</pre>
<BR>
9. DEED<BR>
<pre>
INSERT ALL
INTO DEED VALUES(149800,1010,75,5010,352300)
INTO DEED VALUES(139204,1012,100,3010,1394000)
INTO DEED VALUES(134982,2011,50,1014,200000)
SELECT * FROM DUAL;
</pre>
==== CREATE VIEW ====
<pre>
CREATE VIEW PropAreaLocSeller_VU AS
SELECT SUBSTR((ar.AreaName || ', ' || p.City || ', ' || p.Street || ', ' || p.PostalCode), 1, 60) "Property Location",
TO_CHAR(p.ListingPrice,'$9,999,999') "Price", sgl.LotSize || ' sqft' "LotSize",
s.Lname || ', ' || s.Fname "Full Name", s.phone "Phone", d.OwnPct "OwnPct", NVL(ag.AgencyName, 'No Agency') "Agency"
FROM Property p
JOIN Single sgl ON (p.PropertyID = sgl.PropertyID)
JOIN Area ar ON (p.AreaID = ar.AreaID)
LEFT OUTER JOIN Agency ag ON (p.AgencyID = ag.AgencyID)
JOIN Deed d ON (p.PropertyID = d.PropertyID)
JOIN Client s ON (d.SellerID = s.ClientID);
</pre>
==== Display data from VIEW ====
<pre>
SELECT *
FROM PROPAREALOCSELLER_VU
WHERE INSTR("Property Location", 'Bayview Village') !=0
AND INSTR("Agency", 'Sutton') !=0;
</pre>
=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>
''FINAL''
<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>
===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>
''FINAL''
<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>
''FINAL''
<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>
''FINAL''
<pre>
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;
</pre>
