Revision as of 14:43, 30 November 2010

This page is to efficiently manage our group assignments!

Member List

Email All

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)

1. AREA

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;

2. AGENCY

INSERT ALL
INTO VALUES(1055,'Sutton Group Regal Realty','416.875.9873')
INTO VALUES(3079,'MacDonald Realty','604.986.0231')
INTO VALUES(7024,'Royal LePage Groupe Newton','514.234.9283')
SELECT * FROM DUAL;

3. PROPERTY

INSERT ALL
INTO VALUES(134982,'27 King's College Circle','Toronto','ON','M5S1A1',2,2,380000,1001,1055)
INTO VALUES(149800','17 Pleasant View Dr.','Toronto','ON','M2J2H1',3,2,470000,1001)
INTO VALUES(152932,'17 Barberry Pl. #1313','Toronto','ON','M8W7V4,1,1,259888,1001)
INTO VALUES(495820,'776 Laurelwood Dr.','Waterloo','ON','H3A2T5',2,2,295000,3001,3079)
INTO VALUES(500211,'409 Kelso Cr.','Cambridge','ON','H3Y7S3',3,2,485000,4001)
INTO VALUES(139204,'3043 Crown St.','Vancouver','BC','U3J3M6',5,3,1398000,5001,7024)
SELECT * FROM DUAL;

4. SINGLE

INSERT ALL
INTO VALUES(134982,3716)
INTO VALUES(149800,1005)
INTO VALUES(139204,5400)
SELECT * FROM DUAL;

5. CONDO

INSERT ALL
INTO VALUES(152932,250)
INTO VALUES(495820,320)
INTO VALUES(500211,550)
SELECT * FROM DUAL;

6. OUTLET

INSERT ALL
INTO VALUES(1000,'Toronto Star','416.923.1782','N')
INTO VALUES(2001,'Waterloo Living','519.098.9283','M')
INTO VALUES(8001,'Canadian Life','416.982.0192','W')
SELECT * FROM DUAL;

7. ADVERTISEMENT

INSERT ALL
INTO VALUES(134982,1000,'25-NOV-10',350)
INTO VALUES(149800,1000,'27-NOV-10',250)
INTO VALUES(495820,2001,'27-NOV-10',300)
SELECT * FROM DUAL;

8. CLIENT

INSERT ALL
INTO VALUES(1010,'Andrew','Lee','647.978.0192','andrew11@hotmail.com')
INTO VALUES(1011,'Daniel','Smith','416.895.9762','dsmith@gmail.com',5010)
INTO VALUES(1012,'Olivia','Wilson','647.915.2738','oliviaorange@gmail.com')
INTO VALUES(1013,'Jayden','Martin','519.225.1928','jmartin@rogers.com')
INTO VALUES(1014,'Maria','Brown','416.182.0021','maria.brown@hotmail.com')
INTO VALUES(2010,'Ethan','Roy','226.289.0394','ethanrockstar@bell.com')
INTO VALUES(2011,'Sophia','Tremblay','647.297.2918','stremblay@gmail.com',5010)
INTO VALUES(3010,'Ryan,Wilson','604.837.1029','wilsonryan@gmail.com')
INTO VALUES(3012,'Luis','Jones','416.832.6771','luis7787@rogers.com')
INTO VALUES(5010,'Mary','Anderson','647.234.5690','andersonpink@gmail.com')
INTO VALUES(7010,'James','White','647.298.9182','james.white@bell.com',1010)
SELECT * FROM DUAL;

9. DEED

INSERT ALL
INTO VALUES(149800,1010,75,5010,352300)
INTO VALUES(139204,1012,100,3010,1394000)
INTO VALUES(134982,2011,50,1014,200000)
SELECT * FROM DUAL;

CREATE VIEW

CREATE VIEW PropAreaLocSeller_VU AS
  SELECT (a.AreaName || ', ' || p.City || ', ' || p.Street || ', ' || p.PostalCode) "Property Location",
          TO_CHAR(p.ListingPrice,'$9,999,999'), sgl.LotSize || ' sqft' LotSize, s.Lname || ', ' || s.Fname, s.phone, d.OwnPct, NVL(ag.AgencyName, 'No Agency')
  FROM   Property p
  JOIN Single sgl ON (p.PropertyID = sgl.PropertyID)
  JOIN Area ar    ON (p.AreaID = a.AreaID)
  JOIN Agency ag  ON (p.AgencyID = ag.AgencyID)
  JOIN Deed d     ON (p.PropertyID = d.PropertyID)
  JOIN Client s   ON (d.SellerID = s.ClientID)

Not Too sure if this will work out

Assignment 1

Due Date

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

Description

Assignment1

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;

Difference between revisions of "J.Y.S"

Revision as of 14:43, 30 November 2010

Contents

Member List

Assignment 3 (worth 9%)

Due Date

Solution

DDL Statements (CREATE TABLE)

DML Statements (INSERT INTO)

CREATE VIEW

Assignment 1

Due Date

Description

Solution

SQL Statements

1.

2.

3.

4.

5.

6.

7.

8.

Navigation menu

Personal tools

Namespaces

Variants

Views

More

Search

Navigation

get involved with CDOT

courses

course projects

links

Tools