Old MathJax webview

Question#1 :Using Live Sql and ABC schema answer the following.

First create the tables with its data that has been provided in Tutorial #2.

create view vu1 as

select * from viewing where clientNo=’CR56′;

3. Make an insert or update statement for the following questions , followed by the necessary select statement to show the changes.

add a row through vu1 and fill out all the columns . The row should be listed into the view.

Update a row through vu1 that will be migrated from vu1 to the base table .

add a row through vu1 without comment. Explain your answer.

Change the view to enable the check option then resolve A and B questions with explanation.

Due date: Monday (27/9/2021) 8:00 AM

Submission polices:

Submit a file that include:

1. A copy for the source code ( described in the prevues lab)

2. A screen shot (full screen ) for the output

Otherwise , the submission will not be graded.

I NEED IT BY ORACLE LIVE

DROP TABLE Registration;

DROP TABLE Viewing;

DROP TABLE PropertyForRent;

DROP TABLE Client;

DROP TABLE PrivateOwner;

DROP TABLE Staff;

DROP TABLE Branch;

CREATE TABLE Branch (

branchNo CHAR(4) NOT NULL,

street VARCHAR(25) NOT NULL,

city VARCHAR(15) NOT NULL,

postcode VARCHAR(8) NOT NULL,

CONSTRAINT branch_PK

PRIMARY KEY (branchNo)

);

CREATE TABLE Staff (

staffNo VARCHAR(5) NOT NULL,

fName VARCHAR(15) NOT NULL,

lName VARCHAR(15) NOT NULL,

oPosition VARCHAR(10) NOT NULL,

sex CHAR

CONSTRAINT Staff_sexCHK

CHECK (sex IN (‘M’,’F’)),

DOB DATE,

salary DECIMAL(9,2) NOT NULL,

branchNo CHAR(4) NOT NULL,

CONSTRAINT staff_PK

PRIMARY KEY (staffNo),

CONSTRAINT Staff_Branch_FK

FOREIGN KEY (branchNo)

REFERENCES Branch(branchNo)

);

CREATE TABLE PrivateOwner (

ownerNo VARCHAR(7) NOT NULL,

fName VARCHAR(15) NOT NULL,

lName VARCHAR(15) NOT NULL,

address VARCHAR(50) NOT NULL,

telNo VARCHAR(13) NOT NULL,

CONSTRAINT privateOwner_PK

PRIMARY KEY (ownerNo)

);

CREATE TABLE PropertyForRent (

propertyNo VARCHAR(8) NOT NULL,

street VARCHAR(25) NOT NULL,

city VARCHAR(15) NOT NULL,

postcode VARCHAR(8) NOT NULL,

propertyType VARCHAR(10) NOT NULL,

rooms SMALLINT NOT NULL,

rent DECIMAL(5, 1) NOT NULL,

ownerNo VARCHAR(7) NOT NULL,

staffNo VARCHAR(5),

branchNo CHAR(4) NOT NULL,

CONSTRAINT propertyForRent_PK

PRIMARY KEY (propertyNo),

CONSTRAINT Property_Owner_FK

FOREIGN KEY (ownerNo)

REFERENCES PrivateOwner(ownerNo),

CONSTRAINT Property_Staff_FK

FOREIGN KEY (staffNo)

REFERENCES Staff(staffNo),

CONSTRAINT Property_Branch_FK

FOREIGN KEY (branchNo)

REFERENCES Branch(branchNo)

);

CREATE TABLE Client (

clientNo VARCHAR(7) NOT NULL,

fName VARCHAR(15) NOT NULL,

lName VARCHAR(15) NOT NULL,

telNo VARCHAR(13) NOT NULL,

prefType VARCHAR(10) NOT NULL,

maxRent DECIMAL(5, 1) NOT NULL,

CONSTRAINT client_PK

PRIMARY KEY (clientNo)

);

CREATE TABLE Viewing (

clientNo VARCHAR(7) NOT NULL,

propertyNo VARCHAR(8) NOT NULL,

viewDate DATE NOT NULL,

comments VARCHAR(50),

CONSTRAINT viewing_PK

PRIMARY KEY (propertyNo, clientNo),

CONSTRAINT Viewing_Propty_FK

FOREIGN KEY (propertyNo)

REFERENCES PropertyForRent(propertyNo),

CONSTRAINT Viewing_Client_FK

FOREIGN KEY (clientNo)

REFERENCES Client(clientNo)

);

CREATE TABLE Registration (

clientNo VARCHAR(7) NOT NULL,

branchNo CHAR(4) NOT NULL,

staffNo VARCHAR(5) NOT NULL,

dateJoined DATE NOT NULL,

CONSTRAINT registration_PK

PRIMARY KEY (clientNo, branchNo),

CONSTRAINT Regist_Client_FK

FOREIGN KEY (clientNo)

REFERENCES Client(clientNo),

CONSTRAINT Regist_Branch_FK

FOREIGN KEY (branchNo)

REFERENCES Branch(branchNo),

CONSTRAINT Regist_Staff_FK

FOREIGN KEY (staffNo)

REFERENCES Staff(staffNo)

);

INSERT INTO Branch VALUES (‘B005′, ’22 King Salamn Rd’, ‘Riyadh’, ‘SW1 4EH’);

INSERT INTO Branch VALUES (‘B007′, ’16 Argyll St’, ‘AlMuzahmiya’, ‘AB2 3SU’);

INSERT INTO Branch VALUES (‘B003’, ‘163 Main St’, ‘AdDiriyah’, ‘G11 9QX’);

INSERT INTO Branch VALUES (‘B004′, ’32 Main Rd’, ‘AlKharj’, ‘BS99 1NZ’);

INSERT INTO Branch VALUES (‘B002′, ’56 King Fahad Rd’, ‘Riyadh’, ‘NW10 6EU’);

INSERT INTO Staff VALUES (‘SL21’, ‘Mohammad’, ‘AlOtaibi’,’Manager’, ‘M’, DATE’1990-10-01′, 30000, ‘B005’);

INSERT INTO Staff VALUES (‘SG37’, ‘Amani’, ‘AlShehri’,’Assistant’, ‘F’, DATE’1990-10-11′, 12000, ‘B003’);

INSERT INTO Staff VALUES (‘SG14’, ‘Majed’,’AlTammami’, ‘Supervisor’,’M’, DATE’1988-11-24′, 18000, ‘B003’);

INSERT INTO Staff VALUES (‘SA9’, ‘Muneerah’, ‘AlHamdan’, ‘Assistant’, ‘F’, DATE’1977-02-19′, 9000, ‘B007’);

INSERT INTO Staff VALUES (‘SG5’, ‘Sara’,’AlGhattani’,’Manager’, ‘F’, DATE’1989-06-03′, 24000, ‘B003’);

INSERT INTO Staff VALUES (‘SL41’, ‘Dina’,’Ahmad’, ‘Assistant’, ‘F’, DATE’1986-06-13′, 9000, ‘B005’);

INSERT INTO PrivateOwner VALUES (‘CO46’, ‘Saad’, ‘AlShahrani’, ‘2 Fergus Dr, AlMuzahmiya AB2 7SX’,’01224-861212′);

INSERT INTO PrivateOwner VALUES (‘CO87’, ‘Salma’,’Fadi’,’6 Achray St, AdDiriyah G32 9DX’, ‘0141-357-7419’);

INSERT INTO PrivateOwner VALUES (‘CO40’, ‘Talal’, ‘AlTamimi’,’63 Well St, AdDiriyah G42′, ‘0141-943-1728’);

INSERT INTO PrivateOwner VALUES (‘CO93’, ‘Thamer’, ‘AlNashwan’, ’12 Park Pl, AdDiriyah G4 0QR’, ‘0141-225-7025’);

INSERT INTO PropertyForRent VALUES (‘PA14′, ’16 Albatara St’, ‘AlMuzahmiya’, ‘AB7 5SU’, ‘House’,6, 650, ‘CO46’, ‘SA9’, ‘B007’);

INSERT INTO PropertyForRent VALUES (‘PL94’, ‘6 ALfala St’, ‘Riyadh’, ‘NW2’, ‘Flat’, 4, 400, ‘CO87’, ‘SL41’, ‘B005’);

INSERT INTO PropertyForRent VALUES (‘PG4’, ‘6 Lawrence St’,’AdDiriyah’, ‘G11 9QX’, ‘Flat’, 3, 350, ‘CO40’, NULL, ‘B003’);

INSERT INTO PropertyForRent VALUES (‘PG36’, ‘2 Sadd bin Zayd Rd’, ‘AdDiriyah’, ‘G32 4QX’, ‘Flat’, 3, 375, ‘CO93’, ‘SG37’, ‘B003’);

INSERT INTO PropertyForRent VALUES (‘PG21′, ’18 Ibn Katheer Rd’, ‘AdDiriyah’, ‘G12’, ‘House’,5, 600, ‘CO87’, ‘SG37’, ‘B003’);

INSERT INTO PropertyForRent VALUES (‘PG16’, ‘5 Ibn Katheer Dr’, ‘AdDiriyah’, ‘G12 9AX’, ‘Flat’, 4, 450, ‘CO93’, ‘SG14’, ‘B003’);

INSERT INTO Client VALUES (‘CR76’, ‘Salman’, ‘AlMutairi’, ‘0506-774632’, ‘Flat’, 425);

INSERT INTO Client VALUES (‘CR56’, ‘Ahlam’,’AlHaddad’, ‘0504-848825’, ‘Flat’, 350);

INSERT INTO Client VALUES (‘CR74’, ‘Mazen’, ‘AlSubaie’, ‘0555-392178’, ‘House’, 750);

INSERT INTO Client VALUES (‘CR62’, ‘Maha’, ‘AlKhaledy’, ‘0501-196720’, ‘Flat’, 600);

INSERT INTO Viewing VALUES (‘CR56’, ‘PA14′, DATE’2020-05-24’, ‘too small’);

INSERT INTO Viewing VALUES (‘CR76’, ‘PG4′, DATE’2020-04-20’, ‘too –ote’);

INSERT INTO Viewing VALUES (‘CR56’, ‘PG4′, DATE’2020-05-26’, NULL);

INSERT INTO Viewing VALUES (‘CR62’, ‘PA14′, DATE’2020-05-14’, ‘no dining room’);

INSERT INTO Viewing VALUES (‘CR56’, ‘PG36′, DATE’2020-04-28’, NULL);

INSERT INTO Registration VALUES (‘CR76’, ‘B005’, ‘SL41′, DATE’2020-01-02’);

INSERT INTO Registration VALUES (‘CR56’, ‘B003’, ‘SG37′, DATE’2019-04-11’);

INSERT INTO Registration VALUES (‘CR74’, ‘B003’, ‘SG37′, DATE’2018-11-16’);

INSERT INTO Registration VALUES (‘CR62’, ‘B007’, ‘SA9′, DATE’2019-03-07’);

DATA:

Registration;

CLIENTNO

BRANCHNO

STAFFNO

DATEJOINED

CR76

B005

SL41

02-JAN-20

CR56

B003

SG37

11-APR-19

CR74

B003

SG37

16-NOV-18

CR62

B007

SA9

07-MAR-19

Viewing;

CLIENTNO

PROPERTYNO

VIEWDATE

COMMENTS

CR56

PA14

24-MAY-20

too small

CR76

PG4

20-APR-20

too –ote

CR56

PG4

26-MAY-20

–

CR62

PA14

14-MAY-20

no dining room

CR56

PG36

28-APR-20

–

PropertyForRent;

PROPERTYNO

STREET

CITY

POSTCODE

PROPERTYTYPE

ROOMS

RENT

OWNERNO

STAFFNO

BRANCHNO

PA14

16 Albatara St

AlMuzahmiya

AB7 5SU

House

6

650

CO46

SA9

B007

PL94

6 ALfala St

Riyadh

NW2

Flat

4

400

CO87

SL41

B005

PG4

6 Lawrence St

AdDiriyah

G11 9QX

Flat

3

350

CO40

–

B003

PG36

2 Sadd bin Zayd Rd

AdDiriyah

G32 4QX

Flat

3

375

CO93

SG37

B003

PG21

18 Ibn Katheer Rd

AdDiriyah

G12

House

5

600

CO87

SG37

B003

PG16

5 Ibn Katheer Dr

AdDiriyah

G12 9AX

Flat

4

450

CO93

SG14

B003

Client;

CLIENTNO

FNAME

LNAME

TELNO

PREFTYPE

MAXRENT

CR76

Salman

AlMutairi

0506-774632

Flat

425

CR56

Ahlam

AlHaddad

0504-848825

Flat

350

CR74

Mazen

AlSubaie

0555-392178

House

750

CR62

Maha

AlKhaledy

0501-196720

Flat

600

PrivateOwner;

OWNERNO

FNAME

LNAME

ADDRESS

TELNO

CO46

Saad

AlShahrani

2 Fergus Dr, AlMuzahmiya AB2 7SX

01224-861212

CO87

Salma

Fadi

6 Achray St, AdDiriyah G32 9DX

0141-357-7419

CO40

Talal

AlTamimi

63 Well St, AdDiriyah G42

0141-943-1728

CO93

Thamer

AlNashwan

12 Park Pl, AdDiriyah G4 0QR

0141-225-7025

Staff;

STAFFNO

FNAME

LNAME

OPOSITION

SEX

DOB

SALARY

BRANCHNO

SL21

Mohammad

AlOtaibi

Manager

M

01-OCT-90

30000

B005

SG37

Amani

AlShehri

Assistant

F

11-OCT-90

12000

B003

SG14

Majed

AlTammami

Supervisor

M

24-NOV-88

18000

B003

SA9

Muneerah

AlHamdan

Assistant

F

19-FEB-77

9000

B007

SG5

Sara

AlGhattani

Manager

F

03-JUN-89

24000

B003

SL41

Dina

Ahmad

Assistant

F

13-JUN-86

9000

B005

from Branch;

BRANCHNO

STREET

CITY

POSTCODE

B005

22 King Salamn Rd

Riyadh

SW1 4EH

B007

16 Argyll St

AlMuzahmiya

AB2 3SU

B003

163 Main St

AdDiriyah

G11 9QX

B004

32 Main Rd

AlKharj

BS99 1NZ

B002

56 King Fahad Rd

Riyadh

NW10 6EU

Create view of staff details at branch B003 excluding salaries.

Create view of staff who manage properties for rent, including branch number they work at, staff number, and number of properties they manage.

Are the following queries executed successfully ? if not , why ?

SELECT COUNT(cnt)

FROM StaffPropCnt;

SELECT *

FROM StaffPropCnt

WHERE cnt > 2;

INSERT INTO StaffPropList

VALUES (‘B003’, ‘SG5′, ‘PG19’);

What is the output of the following query

select fname , lname

from Registration r , Client c

where c.CLientno= r.clientno and staffno=’SG37′;

For each branch office with more than one member of staff, find the number of

staff working in each branch and the sum of their salaries.

Consider the following query and state if it causes an error or not and Why .

Create or replace view Staff_VU1 (staffNO , FirstName , LastName , Position)

AS SELECT staffNo, fName , lName, oPosition

FROM Staff Natural JOIN branch

WHERE street LIKE ‘163 Main St%’;

STAFFNO

FIRSTNAME

LASTNAME

POSITION

SG37

Amani

AlShehri

Assistant

SG14

Majed

AlTammami

Supervisor

SG5

Sara

AlGhattani

Manager

Create or replace view Manager_VU2 (MAnagerNO , Manager_FName , Manager_LName , Position)

AS Select staffNo, fName , lName , oPosition

From staff

Where oPosition=’Manager’

With check option;

MANAGERNO

MANAGER_FNAME

MANAGER_LNAME

POSITION

SL21

Mohammad

AlOtaibi

Manager

SG5

Sarah

AlGhattani

Manager

Create or replace view Manager_VU3 (MAnagerNO , Manager_FName , Manager_Lname , Position)

AS Select staffNo, fName , lName, oPosition

From staff

Where oPosition=‘Manager’;

#

Query

Cause an Error?

Result or the cause

1

Create or replace view Supervisor_VU4

AS SELECT FirstName || LastName Staff_name, Position JobTitle

FROM Staff_VU1

WHERE Position LIKE ‘Supervisor’;

select * from Supervisor_VU4 ;

2

Create or replace view Supervisor_VU4

AS SELECT FName || LName Staff_name, Position JobTitle

FROM Staff_VU1

WHERE Position LIKE ‘Supervisor’;

3

insert into staff_VU1 values (‘SG77′,’Khulood’ , ‘AlOsaimi’ ,’Assistant’);

4

UPDATE Manager_VU2

SET Manager_FName = ‘Sarah’

WHERE Managerno = ‘SG5’;

5

UPDATE Manager_VU2

SET oPosition = ‘Supervisor’

WHERE Managerno = ‘SG5’;

6

INSERT INTO Manager_VU3

VALUES (‘SL42’, ‘Leena’,’AlShahrany’, ‘Assistant’);

7

UPDATE Manager_VU3

SET Position = ‘Supervisor’

WHERE Managerno = ‘SG5’;