SQL Database Problems

Here is two table

staffxx (staffNo, lname, fname, dept, desig, joiningDate, salary, rating, branchCode)

branchxx (branchCode, address, city, phone, yearOfOpening)

Please note the code for rating as

1 = Full time employee

2 = Contract employee

3 = Visiting employee

Now use BRANCHXX TABLES to test the commands to:

a. List the branches where no employees are allocated

b. List the employees who are not given to any branch

c. List the city where an employee is there but there is no branch

d. List the city where there are branches but no employee is living

Hint: Use join commands as discussed

Also experiment it using EXIST clause for a and b.

Here is the code for table(Or you can make your own table):

CREATE TABLE STAFF2

(staffNo int NOT NULL,

LNAME VARCHAR(255),

FNAM VARCHAR(255),

DEP VARCHAR(255),

DESIG VARCHAR2(255),

JOININGDATE DATE,

SALARY NUMBER(7,2),

RATING INT,

BRANCHCODE INT);

CREATE TABLE BRANCH2

(BRANCHCODE INT,

ADDRESS VARCHAR(255),

CITY VARCHAR(255),

PHONE INT,

YEAROFOPEANING DATE

);

INSERT INTO STAFF2(staffno,LNAME,FNAM,DEP,DESIG,SALARY,RATING,BRANCHCODE)

VALUES('1','LI','XIAOLONG','HR','MANAGER','5000','1','1234');

INSERT INTO STAFF2(staffno,LNAME,FNAM,DEP,DESIG,SALARY,RATING,BRANCHCODE)

VALUES('2','LI','GANG','HR','STAFF','2000','1','1');

INSERT INTO STAFF2(staffno,LNAME,FNAM,DEP,DESIG,SALARY,RATING,BRANCHCODE)

VALUES('4','LI','YUGANG','HR','STAFF','2000','2','2');

INSERT INTO STAFF2(staffno,LNAME,FNAM,DEP,DESIG,SALARY,RATING,BRANCHCODE)

VALUES('3','LI','JIAHANG','HR','STAFF','6000','3','2');

INSERT INTO STAFF2(staffno,LNAME,FNAM,DEP,DESIG,SALARY,RATING,BRANCHCODE)

VALUES('5','LI','FUKAI','HR','STAFF','2000','1','3');

INSERT INTO STAFF2(staffno,LNAME,FNAM,DEP,DESIG,SALARY,RATING,BRANCHCODE)

VALUES('6','LI','YI','HR','STAFF','2000','1','4');

INSERT INTO STAFF2(staffno,LNAME,FNAM,DEP,DESIG,SALARY,RATING,BRANCHCODE)

VALUES('7','LI','SAN','HR','STAFF','2000','3','5');

INSERT INTO STAFF2(staffno,LNAME,FNAM,DEP,DESIG,SALARY,RATING,BRANCHCODE)

VALUES('8','LI','ER','HR','STAFF','2000','3','6');

INSERT INTO STAFF2(staffno,LNAME,FNAM,DEP,DESIG,SALARY,RATING,BRANCHCODE)

VALUES('9','LI','SI','HR','STAFF','2000','2','7');

INSERT INTO STAFF2(staffno,LNAME,FNAM,DEP,DESIG,SALARY,RATING,BRANCHCODE)

VALUES('10','LI','WU','HR','STAFF','2000','2','8');

INSERT INTO STAFF2(staffno,LNAME,FNAM,DEP,DESIG,SALARY,RATING,BRANCHCODE)

VALUES('11','LI','LIU','HR','STAFF','2000','1','9');

INSERT INTO STAFF2(staffno,LNAME,FNAM,DEP,DESIG,SALARY,RATING,BRANCHCODE)

VALUES('12','LI','QI','HR','STAFF','3000','3','9');

INSERT INTO BRANCH2(BRANCHCODE,ADDRESS,CITY,PHONE)

VALUES('1234','Dalgleish','Kamloops','2505748888');

INSERT INTO STAFF2(staffno,LNAME,FNAM,DEP,DESIG,SALARY,RATING)

VALUES('13','LI','MEI','HR','STAFF','3000','3');

INSERT INTO BRANCH2(ADDRESS,CITY,PHONE)

VALUES('Dalgleish','Kamloops','2505741111');

INSERT INTO BRANCH2(BRANCHCODE)

VALUES('2234');

ALTER TABLE BRANCH2

ADD PRIMARY KEY (BRANCHCODE);

ALTER TABLE STAFF2

ADD PRIMARY KEY(staffNo);

ALTER TABLE STAFF2

ADD FOREIGN KEY(BRANCHCODE) REFERENCES BRANCH2(BRANCHCODE);

alter table STAFF2

add (constraint rate_check check (RATING = '1' or RATING ='2' or RATING='3'));

alter table STAFF2

add (constraint salary_check check (SALARY >= '2000' and SALARY<='5000'));

SELECT * FROM rate_check;

SELECT* FROM salary_check;

SELECT *

FROM STAFF2

WHERE DESIG='MANAGER';

SELECT *

FROM STAFF2,BRANCH2

WHERE DESIG='MANAGER' AND CITY='Kamloops';

SELECT *

FROM STAFF2

WHERE RATING = '3';

Select*

FROM STAFF2

WHERE RATING='3' AND SALARY > '2000';

SELECT LNAME,FNAM

FROM STAFF2

WHERE BRANCHCODE IS null;