|
|
| Line 1: |
Line 1: |
| | | | |
| − |
| |
| − | 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>
| |
