Oracle SQL question

Here is the Oracle Table code

CONNECT prop/prop;

-- Use an anonymous PL/SQL script to

-- drop all tables and sequences in the current schema and

-- suppress any error messages that may displayed

-- if these objects don't exist

BEGIN

EXECUTE IMMEDIATE 'drop table branch cascade constraint';

EXECUTE IMMEDIATE 'drop table client cascade constraint';

EXECUTE IMMEDIATE 'drop table staff cascade constraint';

EXECUTE IMMEDIATE 'drop table viewing cascade constraint';

EXECUTE IMMEDIATE 'drop table property_for_rent cascade constraint';

EXECUTE IMMEDIATE 'drop table registration cascade constraint';

EXECUTE IMMEDIATE 'drop table private_owner cascade constraint';

EXECUTE IMMEDIATE 'drop table lease cascade constraint';

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('');

END;

/

CREATE TABLE branch(

branchno varchar2(5) NOT NULL,

street varchar2(100) NULL,

city varchar2(20) NULL,

postcode varchar2(10) NULL,

PRIMARY KEY ( branchno )) ;

/

CREATE TABLE client(

clientno varchar2(5) NOT NULL,

fname varchar2(8) NULL,

lname varchar2(8) NULL,

address varchar2(35) NULL,

tel_no varchar2(14) NULL,

pref_type char(6) NULL,

max_rent number(4, 0) NULL,

PRIMARY KEY ( clientno )) ;

/

CREATE TABLE lease(

lease_no varchar2(6) NOT NULL,

property_no varchar2(5) NULL,

client_no varchar2(5) NULL,

rent number(4, 0) NULL,

payment_method char(7) NULL,

deposit number(6, 0) NULL,

paid char(1) NULL,

rent_start date NULL,

rent_finish date NULL,

duratn number(18, 0) NULL,

PRIMARY KEY ( lease_no ));

/

CREATE TABLE private_owner(

ownerno varchar2(5) NOT NULL,

fname varchar2(20) NULL,

lname varchar2(20) NULL,

address varchar2(50) NULL,

tel_no varchar2(14) NULL,

PRIMARY KEY ( ownerno ));

/

CREATE TABLE property_for_rent(

propertyno varchar2(5) NOT NULL,

street varchar2(15) NULL,

city varchar2(10) NULL,

postcode varchar2(6) NULL,

type char(7) NULL,

rooms number(2, 0) NULL,

rent number(4, 0) NULL,

ownerno varchar2(5) NULL,

staffno varchar2(5) NULL,

branchno varchar2(5) NULL,

PRIMARY KEY ( propertyno )) ;

/

CREATE TABLE registration(

clientno varchar2(5) NOT NULL,

branchno varchar2(5) NOT NULL,

staffno varchar2(5) NOT NULL,

datejoined date NULL,

PRIMARY KEY ( clientno , branchno , staffno )) ;

/

CREATE TABLE staff(

staffno varchar2(5) NOT NULL,

fname varchar2(15) NULL,

lname varchar2(15) NULL,

position varchar2(15) NULL,

sex char(1) NULL,

dob date NULL,

salary number(7, 2) NULL,

branchno varchar2(5) NULL,

PRIMARY KEY ( staffno ));

/

CREATE TABLE viewing(

clientno varchar2(5) NOT NULL,

propertyno varchar2(5) NOT NULL,

viewdate date NULL,

comments varchar2(200) NULL,

PRIMARY KEY ( clientno , propertyno )) ;

SET DEFINE OFF;

INSERT INTO branch (branchno, street, city, postcode) VALUES ('B008', '12 Paul St', 'Preston', 'PR30JB');

INSERT INTO branch (branchno, street, city, postcode) VALUES ('B009', '32 Elizabeth St', 'Burmingham', 'BB17JE');

INSERT INTO branch (branchno, street, city, postcode) VALUES ('B010', '24 Koleen Dr', 'Manchester', 'KM41SA');

INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR78', 'George', 'Clinto', '55 St Louis Dr London JW10FR', '0121-773-3325', 'House ', 500);

INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR79', 'Muhammad', 'Aslam', '10 Queens Rd London KW40FM', '0171-213-2625', 'House ', 700);

INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR80', 'Sea', 'Hampto', '69 Rosco St Burmingham BI21KL', '0565-773-3032', 'Flat ', 300);

INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR81', 'Cyrus', 'Azarbod', '6 Skyline Dr London IN15ZZ', '0181-362-9325', 'House ', 800);

INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR82', 'Leo', 'Russel', '214 Terrible Dr Glasgow GW18PR', '0891-448-5569', 'Flat ', 450);

INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR83', 'Sameul', 'Sutto', '16 Moscut St Preston PC18JK', '0607-524-4532', 'Flat ', 500);

INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR84', 'Lyndi', 'Clinto', '50 Ceasar Bld London JZ10LR', '0171-793-7325', 'House ', 750);

INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR85', 'Kathree', 'Joshue', '77 Hoffman Dr Manchester MN48NB', '0525-854-4178', 'House ', 900);

INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR86', 'Julia', 'Roberts', '707 Rockford Dr London LK51VD', '0420-568-8544', 'House ', 1500);

INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR87', 'Michael', 'Jackso', '14 Madona Dr Preston DW11FY', '0414-552-6632', 'House ', 1200);

INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR88', 'George', 'Simpso', '25 Charles Dr London YN10BR', '0181-798-7325', 'Flat ', 850);

INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR89', 'Fredo', 'Sati', '7 Nitro Dr London JK89YE', '0181-507-5151', 'Flat ', 550);

INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10087', 'PG76', 'CR78', 400, 'Cheque ', 800, 'Y',to_date( '1995-03-02','yyyy/mm/dd'), to_date('1999-10-31','yyyy/mm/dd'), NULL);

INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10088', 'PG76', 'CR83', 400, 'Master ', 800, 'Y', to_date('1999-11-01','yyyy/mm/dd'), to_date('2002-09-30', 'yyyy/mm/dd'),NULL);

INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10091', 'PG58', 'CR83', 500, 'Cash ', 900, 'Y', to_date('2002-10-15', 'yyyy/mm/dd'),to_date('2003-01-15', 'yyyy/mm/dd'),NULL);

INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10093', 'PG74', 'CR79', 600, 'Master ', 1200, 'Y', to_date('1998-01-01','yyyy/mm/dd'), to_date('1999-09-30', 'yyyy/mm/dd'),NULL);

INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10095', 'PG15', 'CR84', 700, 'Cheque ', 1400, 'Y', to_date('1995-04-15','yyyy/mm/dd'), to_date('2003-03-15', 'yyyy/mm/dd'),NULL);

INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10096', 'PG57', 'CR81', 750, 'Visa ', 1500, 'Y', to_date('1996-01-01', 'yyyy/mm/dd'),to_date('2001-05-30', 'yyyy/mm/dd'),NULL);

INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10100', 'PG56', 'CR87', 1200, 'Cash ', 2400, '', to_date('2001-12-01','yyyy/mm/dd'), to_date('2003-02-28', 'yyyy/mm/dd'),NULL);

INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10101', 'PG59', 'CR86', 1400, 'Visa ', 3000, 'Y', to_date('1997-11-01','yyyy/mm/dd'), to_date('2001-11-30', 'yyyy/mm/dd'),NULL);

INSERT INTO private_owner (ownerno, fname, lname, address, tel_no) VALUES ('CO51', 'Blair', 'Steve', '2 Style Dr London JZ43KL', '0181-750-2551');

INSERT INTO private_owner (ownerno, fname, lname, address, tel_no) VALUES ('CO63', 'Kare', 'Salem', '14 Janet St Bristol BR43KL', '0121-750-6513');

INSERT INTO private_owner (ownerno, fname, lname, address, tel_no) VALUES ('CO64', 'Teetz', 'Alle', '2 Lookup Dr Manchester GA43', '0131-730-4002');

INSERT INTO private_owner (ownerno, fname, lname, address, tel_no) VALUES ('CO65', 'Leo', 'Russel', '15 Cicero St Bristol BR21UT', '0121-631-6262');

INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG15', '11 Arago', 'Manchester', 'SA32DC', 'House ', 4, 700, 'CO51', 'SG17', 'B008');

INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG56', '1 Jackson Ht', 'Manchester', 'MA32DC', 'House ', 6, 1200, 'CO51', 'SA81', 'B009');

INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG57', '10 Hamilton DR', 'Manchester', 'LO32VC', 'Flat ', 5, 700, 'CO64', 'SL61', 'B008');

INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG58', '65 Lori St', 'Burmingham', 'BR22DZ', 'Flat ', 3, 500, 'CO63', 'SL51', 'B010');

INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG59', '25 River Dr', 'Londo', 'YZ32DC', 'House ', 8, 1400, 'CO63', 'SA10', 'B009');

INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG71', '23 Lori St', 'Burmingham', 'BR22DZ', 'Flat ', 3, 500, 'CO63', 'SL51', 'B010');

INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG74', '304 Michelle St', 'Burmingham', 'BR29ZS', 'House ', 3, 600, 'CO51', 'SL51', 'B010');

INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG76', '7 Dracula St', 'Londo', 'OP32DC', 'Flat ', 3, 400, 'CO64', 'SA8', 'B008');

INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG78', '9 Thames Dr', 'Londo', 'JA32DC', 'House ', 4, 700, 'CO64', 'SG17', 'B010');

INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SA10', 'Bria', 'Sothe', 'Supervisor', 'M', to_date('1964-12-30','yyyy/mm/dd'), 17000.00, 'B008');

INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SA5', 'Mark', 'Smith', 'Assistant', 'M', to_date('1974-11-23', 'yyyy/mm/dd'),10000.00, 'B008');

INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SA8', 'Jake', 'Austi', 'Assistant', 'M', to_date('1979-01-03','yyyy/mm/dd'), 9000.00, 'B009');

INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SG15', 'Angila', 'Jolee', 'Assistant', 'F', to_date('1976-09-23','yyyy/mm/dd'), 12000.00, 'B010');

INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SG16', 'Fredo ', 'Satin ', 'Supervisor', 'M', to_date('1973-04-13','yyyy/mm/dd'), 16000.00, 'B010');

INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SG17', 'Gyneth', 'Paltrow', 'Assistant', 'F', to_date('1980-01-07','yyyy/mm/dd'), 11000.00, 'B010');

INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SL51', 'Maria', 'Bria', 'Assistant', 'F', to_date('1976-10-15','yyyy/mm/dd'), 13000.00, 'B009');

INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SL61', 'Sheela', 'Johnso', 'Manager', 'F', to_date('1969-02-20', 'yyyy/mm/dd'),33000.00, 'B009');

delete viewing ;

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR78', 'PG71', to_date('1993-06-01', 'yyyy/mm/dd'), NULL);

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR78', 'PG57', to_date('1985-02-01','yyyy/mm/dd'), 'We will go for it');

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR79', 'PG57', to_date('2002-01-01','yyyy/mm/dd'), 'Wonderful');

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR79', 'PG76', to_date('2002-10-01','yyyy/mm/dd'), NULL);

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR79', 'PG74', to_date('1999-10-01', 'yyyy/mm/dd'),'Wonderful');

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR79', 'PG15', to_date('1995-06-01', 'yyyy/mm/dd'),'Can you change the color');

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR83', 'PG15', to_date('2000-12-01', 'yyyy/mm/dd'),'Wonderful');

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR78', 'PG59', to_date('1999-10-01','yyyy/mm/dd'), 'It is good');

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR78', 'PG76', to_date('1995-03-02','yyyy/mm/dd'), NULL);

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR79', 'PG59', to_date('1998-01-01', 'yyyy/mm/dd'), NULL);

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR80', 'PG58', to_date('1979-12-01', 'yyyy/mm/dd'), 'It is good');

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR80', 'PG15', to_date('1985-01-01', 'yyyy/mm/dd'), 'Let us think about it');

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR81', 'PG15', to_date('1996-01-01','yyyy/mm/dd'), NULL);

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR81', 'PG59', to_date('2001-06-01', 'yyyy/mm/dd'), 'I want to rent it ASAP');

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR82', 'PG58', to_date('2001-01-15', 'yyyy/mm/dd'), 'I like the kitchen the most');

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR83', 'PG58', to_date('2002-10-15', 'yyyy/mm/dd'), NULL);

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR83', 'PG56', to_date('1999-11-01','yyyy/mm/dd'), NULL);

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR84', 'PG15', to_date('1995-04-15', 'yyyy/mm/dd'), 'It is good');

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR85', 'PG15', to_date('2002-06-01', 'yyyy/mm/dd'), 'I like the kitchen the most');

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR85', 'PG59', to_date('2001-11-01', 'yyyy/mm/dd'), NULL);

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR86', 'PG59', to_date('1997-11-01', 'yyyy/mm/dd'), NULL);

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR87', 'PG56', to_date('2001-12-01', 'yyyy/mm/dd'), 'I want to rent it ASAP');

INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR87', 'PG59', to_date('1999-04-01', 'yyyy/mm/dd'), 'I like the kitchen the most');

ALTER TABLE LEASE ADD CONSTRAINT FK_LEASE_CLIENT FOREIGN KEY(CLIENT_NO)

REFERENCES CLIENT (CLIENTNO)

;

ALTER TABLE LEASE ADD CONSTRAINT FK_LEASE_PROPERTY_FOR_RENT FOREIGN KEY(PROPERTY_NO)

REFERENCES PROPERTY_FOR_RENT (PROPERTYNO)

;

ALTER TABLE PROPERTY_FOR_RENT ADD FOREIGN KEY(BRANCHNO)

REFERENCES BRANCH (BRANCHNO);

ALTER TABLE PROPERTY_FOR_RENT ADD CONSTRAINT FK_PROPERTYFORRENTPRIVATEOWNER FOREIGN KEY(OWNERNO)

REFERENCES PRIVATE_OWNER (OWNERNO)

;

ALTER TABLE REGISTRATION ADD CONSTRAINT FK_REGISTRATION_BRANCH FOREIGN KEY(BRANCHNO)

REFERENCES BRANCH (BRANCHNO)

;

ALTER TABLE REGISTRATION ADD CONSTRAINT FK_REGISTRATION_CLIENT FOREIGN KEY(CLIENTNO)

REFERENCES CLIENT (CLIENTNO)

;

ALTER TABLE REGISTRATION ADD CONSTRAINT FK_REGISTRATION_STAFF FOREIGN KEY(STAFFNO)

REFERENCES STAFF (STAFFNO)

;

ALTER TABLE STAFF ADD FOREIGN KEY(BRANCHNO)

REFERENCES BRANCH (BRANCHNO)

;

ALTER TABLE VIEWING ADD CONSTRAINT FK_VIEWING_CLIENT FOREIGN KEY(CLIENTNO)

REFERENCES CLIENT (CLIENTNO)

;

ALTER TABLE VIEWING ADD CONSTRAINT FK_VIEWING_PROPERTY_FOR_RENT FOREIGN KEY(PROPERTYNO)

REFERENCES PROPERTY_FOR_RENT (PROPERTYNO)

;

COMMIT; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Install PROP database your local account.

You need to submit two documents with the following instruction.

Document 1: Every query should have the following format:

Query in English (as appear in this assignment) (Comment Query)

SQL statement

Document 2: Result- top 10 rows from the generated SQL code

1. List of all cities where there is a branch but no properties.

2. Find all staff who work in a Preston branch.

3. List branches and properties in same city and any unmatched branches or properties.

4. Find number of properties handled by each staff member.

5. Find staff whose salary is larger than salary of at least one member of staff at branch B008.

In class assignment Bonus (4 points)

1. Produce a list of monthly salaries for all staff, showing staff number, first and last names, and salary details.

2. Find number of staff in each branch and their total salaries.