Information Management LAB 1/2557

LAB05.sql

/* 1.	List full details of all properties. */
SELECT *
  FROM PROPERTYFORRENT;
/* 2.	List full details of all properties that are only house type. */
SELECT *
  FROM PROPERTYFORRENT
  WHERE UPPER(TYPE) = 'HOUSE';
/* 3.	List the client number, first name, last name and telephone number of all clients whose preferred property type are Flat. */
SELECT CLIENTNO, FNAME, LNAME, TELNO
  FROM CLIENT
  WHERE UPPER(PREFTYPE) = 'FLAT';
/* 4.	List the client number and view date of all viewings on property CR77 where the comments are provided. */
SELECT CLIENTNO, VIEWDATE
  FROM VIEWING
  WHERE CLIENTNO = 'CR77'
    AND COMMENTS IS NOT NULL;  
/* 5.	List all type of properties that are for rent.  The result should eliminate the duplicate records. */
SELECT DISTINCT TYPE
  FROM PROPERTYFORRENT;
/* 6.	List the staff number, first name, last name and salary of all staffs who are males with salary greater than 10,000 . */
SELECT STAFFNO, FNAME, LNAME, SALARY
  FROM STAFF
  WHERE SEX = 'M'
    AND SALARY > 10000;
/* 7.	List the staff number, first name, last name and birth date of all staffs who were born after January 1, 1960. Arrange the result in ascending order by birth date. */
SELECT STAFFNO, FNAME, LNAME, DOB
  FROM STAFF
  WHERE DOB > '1-Jan-1960'
  ORDER BY DOB;
/* 8.	List the owner number, name (first name concatenated with last name), and address of all private owners who do not live in the Glasgow city. */
SELECT OWNERNO, FNAME || ' ' || LNAME, ADDRESS
  FROM PRIVATEOWNER
  WHERE UPPER(ADDRESS) NOT LIKE '%GLASGOW%';
/* 9.	List the address of all branch offices in Aberdeen or Bristol. */
SELECT STREET || ' ' || CITY || ' ' || POSTCODE ADDRESS
  FROM BRANCH
  WHERE UPPER(CITY) IN('ABERDEEN','BRISTOL');
/* 10.	List the branch no and salary of all staffs. The result should be arranged by branch no, in ascending order , and within branch no, in descending order of salary. */
SELECT BRANCHNO, SALARY
  FROM STAFF
  ORDER BY BRANCHNO, SALARY DESC;

LAB06.sql

/* 1.	How many clients are there? */
SELECT COUNT(*) "CLIENT NUMBER"
  FROM CLIENT;
  
/* 2.	How many clients whose preferred property type are Flat? */
SELECT COUNT(*) "FLAT NUMBER"
  FROM CLIENT
  WHERE PREFTYPE = 'Flat';
  
/* 3.	What is average rent for three-room flat? */
SELECT AVG(RENT)
  FROM PROPERTYFORRENT
  WHERE ROOMS = 3;
  
/* 4.	What are the number of staff and the minimum, maximum and average staff salary in each staff position? */
SELECT COUNT(*) "STAFF NUMBER", MIN(SALARY) "MIN SALARY", MAX(SALARY) "MAX SALARY", AVG(salary) "AVERAGE SALARY", POSITION
  FROM STAFF
  GROUP BY POSITION;
/* 5.	Which clients have viewed the properties more than one time? */
SELECT COUNT(*), CLIENTNO
  FROM VIEWING
  GROUP BY CLIENTNO
  HAVING COUNT(*) > 1;
  
/* TASK2 */

/* 1.	List full details of all properties for rent along with owner’s name (first name and last name). */
SELECT P.*, O.FNAME || ' ' || O.LNAME OWNER
  FROM PROPERTYFORRENT P JOIN PRIVATEOWNER O ON P.OWNERNO = O.OWNERNO ;
/* 2.	List the staff number, first and last names and branch city of all staffs. */
SELECT S.STAFFNO, S.FNAME, S.LNAME, B.CITY
  FROM STAFF S JOIN BRANCH B ON B.BRANCHNO = S.BRANCHNO;
/* 3.	List the name of clients who have viewed the properties more than one time. */    
SELECT COUNT(V.CLIENTNO) AS "VIEW NO." ,CL.FNAME || ' ' || CL.LNAME AS "CLIENT NAME"
  FROM VIEWING V 
  JOIN CLIENT CL ON V.CLIENTNO = CL.CLIENTNO 
  GROUP BY CL.FNAME, CL.LNAME
    HAVING COUNT(V.CLIENTNO) >= 1; 
/* 4.	List the number of properties handled by each staff member, along with the branch number of the member of staff */
SELECT COUNT(P.STAFFNO), S.FNAME || ' ' || S.LNAME AS "STAFF NAME", B.BRANCHNO
  FROM PROPERTYFORRENT P
  JOIN STAFF S
    ON P.STAFFNO = S.STAFFNO
  JOIN BRANCH B
    ON S.BRANCHNO = B.BRANCHNO
  GROUP BY S.FNAME,S.LNAME, B.BRANCHNO;
/* 5.	List the branch cities that staffs manage the properties that are home type. Remove the duplicate records. */
SELECT DISTINCT P.CITY
  FROM STAFF S 
  JOIN BRANCH B
    ON S.BRANCHNO = B.BRANCHNO  
  JOIN PROPERTYFORRENT P
    ON S.STAFFNO = P.STAFFNO
  WHERE P.TYPE = 'House';
/* 6.	List the client name and view date of all viewings on properties that are managed by staffs’ branch office in Glasglow. */
SELECT C.FNAME || ' ' || C.LNAME AS NAME,V.VIEWDATE
  FROM CLIENT C
  JOIN  VIEWING V
    ON V.CLIENTNO = C.CLIENTNO
  JOIN PROPERTYFORRENT P
    ON V.PROPERTYNO = P.PROPERTYNO
  JOIN STAFF S
    ON P.STAFFNO = S.STAFFNO
  JOIN BRANCH B
    ON B.BRANCHNO = S.BRANCHNO
  WHERE B.CITY = 'Glasgow';
  
  

LAB07.sql

/* 1.        List staffs who manage properties with three rooms.  */

/* TYPE1 */
SELECT *
  FROM STAFF
  WHERE STAFFNO IN (SELECT STAFFNO
                      FROM PROPERTYFORRENT
                      WHERE ROOMS = 3);

/* TYPE2 */
SELECT *
  FROM STAFF S
  WHERE EXISTS(SELECT STAFFNO
                      FROM PROPERTYFORRENT P
                      WHERE ROOMS = 3
                        AND S.STAFFNO = P.STAFFNO);

/* 2.        List properties that owner’s telephone number starts with 012. */

/* TYPE1 */
SELECT *
  FROM PROPERTYFORRENT
  WHERE OWNERNO IN (SELECT OWNERNO
                      FROM PRIVATEOWNER
                      WHERE TELNO LIKE'012%');

/*TYPE2*/
SELECT *
  FROM PROPERTYFORRENT PO
  WHERE EXISTS(SELECT OWNERNO
                      FROM PRIVATEOWNER PI
                      WHERE TELNO LIKE'012%'
                        AND PO.OWNERNO = PI.OWNERNO);

/* 3.        List branches that do not have any staff. */

/* TYPE1 */
SELECT *
  FROM BRANCH
  WHERE BRANCHNO NOT IN (SELECT DISTINCT BRANCHNO
                          FROM STAFF);

/* TPYE2 */
SELECT *
  FROM BRANCH B
  WHERE NOT EXISTS(SELECT BRANCHNO
                          FROM STAFF S
                          WHERE B.BRANCHNO = S.BRANCHNO);

/* 4.        List staffs whose salary is less than the average salary of all
staffs and who work in the branch at Glasgow. */

/* TPYE1 */
SELECT *
  FROM STAFF
  WHERE BRANCHNO IN (SELECT BRANCHNO
                      FROM BRANCH
                      WHERE CITY = 'Glasgow')
    AND SALARY < (SELECT AVG(SALARY)
                      FROM STAFF);

/* TPYE2 */
SELECT *
  FROM STAFF S
  WHERE EXISTS(SELECT BRANCHNO
                      FROM BRANCH B
                      WHERE CITY = 'Glasgow' AND S.BRANCHNO = B.BRANCHNO)
    AND SALARY < (SELECT AVG(SALARY)
                      FROM STAFF);

/* 5.        List staffs whose salary is larger than the salary of every member
of staff at branch B003. */

/* TPYE1 */
SELECT *
  FROM STAFF
  WHERE SALARY > ALL (SELECT (SALARY)
                      FROM STAFF
                      WHERE BRANCHNO = 'B003');

/* TPYE2 */
SELECT *
  FROM STAFF SI
  WHERE EXISTS(SELECT STAFFNO
                      FROM STAFF SO
                      WHERE SO.BRANCHNO = 'B003'
                        HAVING SI.SALARY > MAX(SO.SALARY));

/* 6.        List properties that are viewed by clients more than two times. */

/* TYPE1 */
SELECT *
  FROM PROPERTYFORRENT
  WHERE PROPERTYNO IN (SELECT PROPERTYNO
                        FROM VIEWING
                        GROUP BY PROPERTYNO
                        HAVING COUNT(PROPERTYNO) > 2);

SELECT *
  FROM PROPERTYFORRENT P
  WHERE EXISTS(SELECT PROPERTYNO
                        FROM VIEWING V
                        WHERE P.PROPERTYNO = v.PROPERTYNO
                        GROUP BY PROPERTYNO
                        HAVING COUNT(PROPERTYNO) > 2);

/* 7.        List clients who registered in the branch at 163 Main Street. */

/* TYPE1 */
SELECT *
  FROM REGISTRATION
  WHERE BRANCHNO IN (SELECT BRANCHNO
                      FROM BRANCH
                      WHERE STREET = '163 Main Street');

/* TYPE2 */
SELECT *
  FROM REGISTRATION R
  WHERE EXISTS(SELECT BRANCHNO
                      FROM BRANCH B
                      WHERE STREET = '163 Main Street'
                        AND R.BRANCHNO = B.BRANCHNO);


/* 8.        In each branch, lists staffs whose salary is greater than the
average salary in their branch. */

/* TYPE1 */
SELECT *
  FROM STAFF SI
  WHERE SALARY > (SELECT AVG(SALARY)
                FROM STAFF SO
                WHERE SI.BRANCHNO = SO.BRANCHNO
                GROUP BY BRANCHNO);

/* TYPE2 */
SELECT *
  FROM STAFF SI
  WHERE EXISTS(SELECT BRANCHNO
                FROM STAFF SO
                WHERE SI.BRANCHNO = SO.BRANCHNO
                GROUP BY BRANCHNO
                HAVING SI.SALARY > AVG(SO.SALARY));

LAB08.sql

/* 1.	List all clients who have viewed a property. Include clients who haven’t viewed any property. The result should show the client number, first name, property number and view date. */

/* LEFT JOIN */
SELECT C.CLIENTNO, C.FNAME, V.PROPERTYNO, V.VIEWDATE
  FROM CLIENT C
  LEFT JOIN VIEWING V
    ON C.CLIENTNO = V.CLIENTNO;
    
/* RIGHT JOIN */    
SELECT C.CLIENTNO, C.FNAME, V.PROPERTYNO, V.VIEWDATE
  FROM VIEWING V
  RIGHT JOIN CLIENT C
    ON C.CLIENTNO = V.CLIENTNO;

/* 2.	List all staffs who are not responsible to any property. */

/* LEFT JOIN */
SELECT *
  FROM STAFF S
  LEFT JOIN PROPERTYFORRENT P
    ON S.STAFFNO = P.STAFFNO
  WHERE P.STAFFNO IS NULL;
  
/* RIGHT JOIN */
SELECT *
  FROM PROPERTYFORRENT P
  RIGHT JOIN STAFF S
    ON S.STAFFNO = P.STAFFNO
  WHERE P.STAFFNO IS NULL;

/* 3.	List all properties that are not viewed by any client and the properties are managed by staffs in the branch at 22 Deer Road. */

SELECT *
  FROM PROPERTYFORRENT P
  LEFT JOIN VIEWING V
    ON P.PROPERTYNO = V.PROPERTYNO
  LEFT JOIN STAFF S
    ON P.STAFFNO = S.STAFFNO
  LEFT JOIN BRANCH B
    ON S.BRANCHNO = B.BRANCHNO
  WHERE V.CLIENTNO IS NULL
    AND B.STREET = '22 Deer Road';


/*
USE SET OPERATIONS
*/

/* 4.	List all people in the Dream Home database. The result should show the person number, first name and last name of the Staff, PrivateOwner, and Client tables. */

(SELECT CLIENTNO "PERSON NO.", FNAME, LNAME
  FROM CLIENT)
UNION  
(SELECT STAFFNO , FNAME, LNAME
  FROM STAFF)
UNION
(SELECT OWNERNO , FNAME, LNAME
  FROM PRIVATEOWNER) 
  ORDER BY 1;
  
/* 5.	List the property number of properties that were never viewed by any clients. (SUBQ) */

/* SET OPT. */
SELECT PROPERTYNO
  FROM PROPERTYFORRENT
MINUS
SELECT PROPERTYNO
  FROM VIEWING;

/* SUBQ */
SELECT PROPERTYNO
  FROM PROPERTYFORRENT
  WHERE PROPERTYNO NOT IN (SELECT PROPERTYNO FROM VIEWING);

/* 6.	List all cities where there is either a branch office or a property but not both. (SUBQ) */

/* SET OPT. */
(SELECT CITY
  FROM BRANCH
MINUS
SELECT CITY
  FROM PROPE
  RTYFORRENT)
UNION
(SELECT CITY
  FROM PROPERTYFORRENT
MINUS
SELECT CITY
  FROM BRANCH);
  
/* SUBQ */
SELECT CITY
  FROM BRANCH  WHERE CITY NOT IN (SELECT P.CITY , FROM PROPERTYFORRENT P , JOIN BRAN
  WHERE CITY NOT IN (SELECT P.CITY 
                      FROM PROPERTYFORRENT P 
                      JOIN BRANCH B 
                        ON P.CITY = B.CITY);  

/* 7.	List the full details of all private owners whose property was never viewed by clients. (Hint: Use a subquery with a set operator) */
SELECT *
  FROM PROPERTYFORRENT
  WHERE PROPERTYNO IN(SELECT PROPERTYNO
                        FROM PROPERTYFORRENT
                      MINUS
                      SELECT PROPERTYNO
                        FROM VIEWING);

LAB09.sql

CREATE TABLE PROJECT(
  PROJ_NUM NUMBER(3),
  PROJ_NAME VARCHAR2(40)
    CONSTRAINT PROJECT_PROJ_NAME_NN NOT NULL,
  CONSTRAINT PROJECT_PROJ_NUM_PK PRIMARY KEY(PROJ_NUM)
);

CREATE TABLE JOB(
  JOB_ID VARCHAR2(4),
  JOB_NAME VARCHAR2(30),
  CHG_DOLLAR_HOUR NUMBER(6,2),
  CONSTRAINT JOB_JOB_ID PRIMARY KEY(JOB_ID)
);

CREATE TABLE EMPLOYEE(
  EMP_NUM NUMBER(3),
  JOB_ID VARCHAR2(4),
  EMP_NAME VARCHAR(50),
  CONSTRAINT EMPLOYEE_EMP_NUM_PK PRIMARY KEY(EMP_NUM),
  CONSTRAINT EMPLOYEE_JOB_ID_FK FOREIGN KEY(JOB_ID) REFERENCES JOB
);

CREATE TABLE ASSIGNMENT(
  PROJ_NUM NUMBER(3),
  EMP_NUM NUMBER(3),
  HOURS NUMBER(3,1)
    CONSTRAINT ASSIGNMENT_HOURS_NN NOT NULL,
  CONSTRAINT ASSIGNMENT_PROJ_NUM_EMP_NUM_PK PRIMARY KEY(PROJ_NUM,EMP_NUM),
  CONSTRAINT ASSIGNMENT_PROJ_NUM_FK FOREIGN KEY(PROJ_NUM) REFERENCES PROJECT,
  CONSTRAINT ASSIGNMENT_EMP_NUM_FK FOREIGN KEY(EMP_NUM) REFERENCES EMPLOYEE
    
);

LAB11.sql

/* 1. Create staff table */

CREATE TABLE STAFF (
  STAFFNO VARCHAR2(6)
    CONSTRAINT STAFF_STAFFNO_PK PRIMARY KEY,
  FNAME VARCHAR2(25)
    CONSTRAINT STAFF_FNAME_NN NOT NULL,
  LNAME VARCHAR2(30)
    CONSTRAINT STAFF_LNAME_NN NOT NULL,
  POSITION VARCHAR2(25)
    CONSTRAINT STAFF_POSITION_NN NOT NULL,
  SEX CHAR(1)
    CONSTRAINT STAFF_SEX_NN NOT NULL
    CONSTRAINT STAFF_SEX_CK CHECK (SEX IN ('F','M')),
  DOB DATE,
  SALARY NUMBER(8,2)
    CONSTRAINT STAFF_SALARY_NN NOT NULL,
  MGRNO VARCHAR2(6)
    CONSTRAINT STAFF_MGRNO_NN NOT NULL
    CONSTRAINT STAFF_MGRNO_FK REFERENCES STAFF (STAFFNO)
);

/* 1.1 add column to staff table after created */

ALTER TABLE STAFF
ADD(
 MGRNO VARCHAR2(6)
    CONSTRAINT STAFF_MGRNO_NN NOT NULL
    CONSTRAINT STAFF_MGRNO_FK REFERENCES STAFF (STAFFNO)
);

/* 2 Create client table */

CREATE TABLE CLIENT(
  CLIENTNO VARCHAR2(6)
    CONSTRAINT CLIENT_CLIENTNO_PK PRIMARY KEY,
  FNAME VARCHAR2(25)
    CONSTRAINT CLIENT_FNAME_NN NOT NULL,
  LNAME VARCHAR2(30)
    CONSTRAINT CLIENT_LNAME_NN NOT NULL,
  TELNO VARCHAR2(25)
    CONSTRAINT CLIENT_TELNO_NN NOT NULL,
  EMAIL VARCHAR2(50),
  STAFFNO VARCHAR(6)
    CONSTRAINT CLIENT_STAFFNO_FK REFERENCES STAFF (STAFFNO)
    CONSTRAINT CLIENT_STAFFNO_NN NOT NULL
);

/* 3. Add perfType and maxrent column to client table */

ALTER TABLE CLIENT
ADD(
  PREFTYPE VARCHAR2(6)
    CONSTRAINT CLIENT_PREFTYPE_NN NOT NULL,
  MAXRENT NUMBER(8,2)
    CONSTRAINT CLIENT_MAXRENT_NN NOT NULL
);

/* 4. Create table named propertyforrent */

CREATE TABLE PROPERTYFORRENT(
  PROPERTYNO VARCHAR2(6)
    CONSTRAINT PROPERTYFORRENT_PROPERTYNO_PK PRIMARY KEY,
  STREET VARCHAR2(50)
    CONSTRAINT PROPERTYFORRENT_STREET_NN NOT NULL,
  CITY VARCHAR2(15)
    CONSTRAINT PROPERTYFORRENT_VITY_NN NOT NULL,
  POSTCODE VARCHAR(10)
    CONSTRAINT PROPERTYFORRENT_POSTCODE_NN NOT NULL,
  TYPE VARCHAR2(15)
    CONSTRAINT PROPERTYFORRENT_TPYE_NN NOT NULL,
  ROOMS NUMBER(3)
    CONSTRAINT PROPERTYFORRENT_ROOMS_NN NOT NULL,
  RENT NUMBER(8,2)
    CONSTRAINT PROPERTYFORRENT_RENT_NN NOT NULL
);

/* 4.1 Create viewing table, related between client and propertyforrent tables */

CREATE TABLE VIEWING(
  CLIENTNO VARCHAR2(6)
    CONSTRAINT VIEWING_CLIENTNO_FK REFERENCES CLIENT (CLIENTNO),
  PROPERTYNO VARCHAR2(6)
    CONSTRAINT VIEWING_PROPERTYNO_FK REFERENCES PROPERTYFORRENT (PROPERTYNO),
  VIEWDATE DATE
    CONSTRAINT VIEWING_VIEWDATE_NN NOT NULL,
  COMMENTS VARCHAR2(100)
    CONSTRAINT VIEWING_COMMENTS_NN NOT NULL,
  CONSTRAINT VIEWING_PK PRIMARY KEY (CLIENTNO,PROPERTYNO)
);

/* 5.1 Create singer table */

CREATE TABLE SINGER(
  PERSONNO NUMBER(4)
    CONSTRAINT SINGER_PERSONNO_PK PRIMARY KEY,
  PERSONNAME VARCHAR(50)
    CONSTRAINT SINGER_PERSONNAME_NN NOT NULL,
  GENDER CHAR(1)
    CONSTRAINT SINGER_GENDER_NN NOT NULL
    CONSTRAINT SINGER_GENDER_CK CHECK (GENDER IN ('F','M')),
  SINGINGSONG VARCHAR2(100)
    CONSTRAINT SINGER_SINGINSONG_NN NOT NULL
);

/* 5.2 Create coach table */

CREATE TABLE COACH(
   PERSONNO NUMBER(4)
    CONSTRAINT COACH_PERSONNO_PK PRIMARY KEY,
  PERSONNAME VARCHAR(50)
    CONSTRAINT COACH_PERSONNAME_NN NOT NULL,
  GENDER CHAR(1)
    CONSTRAINT COACH_GENDER_NN NOT NULL
    CONSTRAINT COACH_GENDER_CK CHECK (GENDER IN ('F','M')),
  SONGTYPE VARCHAR2(30)
    CONSTRAINT COACH_SONGTYPE_NN NOT NULL
);