Question

CREATE TABLE Branch (
branchNo VARCHAR(4),
address VARCHAR(50),
city VARCHAR(30),
state VARCHAR(2),
phone VARCHAR(20),
PRIMARY KEY (branchNo));

INSERT INTO Branch VALUES('B001','366 Tiger Ln','Los Angeles','CA','213-539-8600');
INSERT INTO Branch VALUES('B002','18 Harrison Rd','New Haven','CT','203-444-1818');
INSERT INTO Branch VALUES('B003','55 Waydell St','Essex','NJ','201-700-7007');
INSERT INTO Branch VALUES('B004','22 Canal St','New York','NY','212-055-9000');
INSERT INTO Branch VALUES('B005','1725 Roosevelt Ave','Queens','NY','718-963-8100');
INSERT INTO Branch VALUES('B006','1471 Jerrold Ave','Philadelphia','PA','267-222-5252');

CREATE TABLE Staff (
staffNo VARCHAR(4),
fName VARCHAR(20),
lName VARCHAR(20),
position VARCHAR(20),
sex VARCHAR(1),
age INTEGER,
salary NUMBER(8,2),
phone VARCHAR(20),
address VARCHAR(50),
city VARCHAR(20),
state VARCHAR(2),
branchNo VARCHAR(4),
PRIMARY KEY (staffNo));

INSERT INTO Staff VALUES('S001','Krista','Allen','Manager','F','33','48000','917-874-1229','4 Kohler Memorial Dr','Brooklyn','NY','B004');
INSERT INTO Staff VALUES('S002','Charlie','Arnold','Assistant','M','32','36000','347-925-5213','2371 Jerrold Ave','Queens ','NY','B003');
INSERT INTO Staff VALUES('S003','Jonathon','Bowman','Assistant','M','28','36000','213-775-4480','10759 Main St','Scottsdale','AZ','B001');
INSERT INTO Staff VALUES('S004','Joe','Bryan','Manager','M','34','48000','720-870-5536','42754 S Ash Ave','New Haven','NY','B002');
INSERT INTO Staff VALUES('S005','Danny','Chavez','Supervisor','M','41','48000','720-828-7047','46314 Route 130','Fairfield','CT','B002');
INSERT INTO Staff VALUES('S006','Renee','Figueroa','Assistant','F','21','24000','213-420-8970','96541 W Central Blvd','Phoenix','AZ','B001');
INSERT INTO Staff VALUES('S007','Patty','Frazier','Supervisor','F','41','48000','213-639-9887','2409 Alabama Rd','Riverside','CA','B001');
INSERT INTO Staff VALUES('S008','Emmett','Garza','Assistant','M','30','36000','917-877-8409','6 Gilson St','Bronx','NY','B004');
INSERT INTO Staff VALUES('S009','Lana','Hodges','Supervisor','F','35','36000','646-645-3605','18 3rd Ave','New York','NY','B004');
INSERT INTO Staff VALUES('S010','Jesus','Mann','Manager','M','47','60000','201-587-5746','25346 New Rd','Staten Island','NY','B003');
INSERT INTO Staff VALUES('S011','Lorena','Marsh','Manager','F','36','48000','720-634-7158','426 Wolf St','New Haven','CT','B002');
INSERT INTO Staff VALUES('S012','Erik','Olson','Assistant','M','22','24000','720-579-2907','8 S Haven St','New Haven','CT','B002');
INSERT INTO Staff VALUES('S013','Leslie','Pearson','Manager','F','52','60000','646-752-4114','30553 Washington Rd','Plainfield','NJ','B004');
INSERT INTO Staff VALUES('S014','Diane','Rogers','Assistant','F','27','36000','347-504-3552','82 Us Highway 46','Clifton','NJ','B004');
INSERT INTO Staff VALUES('S015','Nina','Scott','Assistant','F','29','36000','201-636-4117','33 Lewis Rd #46','Jersey City','NJ','B003');
INSERT INTO Staff VALUES('S016','Owen','Singleton','Manager','M','31','48000','310-928-5182','4119 Metropolitan Dr','Los Angeles','CA','B001');
INSERT INTO Staff VALUES('S017','Janice','Underwood','Assistant','F','20','24000','720-822-7652','38 Pleasant Hill Rd','New Haven','CT','B002');
INSERT INTO Staff VALUES('S018','Edward','Warner','Assistant','M','23','24000','551-300-1771','993 Washington Ave','Essex','NJ','B003');
INSERT INTO Staff VALUES('S019','Amos','Wilkerson','Assistant','M','20','24000','310-898-2154','34 Raritan Center Pky','Los Angeles','CA','B001');
INSERT INTO Staff VALUES('S020','Kelley','Wood','Assistant','F','22','24000','551-390-2251','13 S Hacienda Dr','Essex','NJ','B003');
INSERT INTO Staff VALUES('S021','Brad','Herman','Manager','M','39','48000','917-577-5488','16 E 108th St','New York','NY','B004');
INSERT INTO Staff VALUES('S022','Alisa','Garcia','Assistant','F','20','24000','646-411-4775','22 Sip Ave','Jersey City','NJ','B004');
INSERT INTO Staff VALUES('S023','Igor','Strovinsky','Assistant','M','24','24000','347-103-4123','1266 Flatbush Ave','Brooklyn','NY','B004');

Lab Tasks: Provide the SQL statements that perform the following tasks: 1- Display the lowest and the highest salaries of all staff members.

2- Display the number of managers in each branch.

3- In each state, display the number of staff members and the average of their salaries.

4- List the staff with salaries greater than the average salary of all staff members.

5- List the staff with salaries greater than the average salary of all female managers.

6- List the youngest staff members. Hint: Use a subquery that returns the smallest age.

7- List the oldest supervisors.

8- List the managers with the lowest salary. Hint: Use a subquery that returns the lowest salary.

9- List the assistants with the highest salary.

10- List the managers of the branch located at '22 Canal St'.

11- Display the count and the average salary of female managers working in the branch located at '22 Canal St'.

12- Using a subquery, list the staff members who work in the branch located at '366 Tiger Ln'.

13- Using a join, list the staff members who work in the branch located at '366 Tiger Ln'.

14- List the first and last names, the salary, and the branch address of all staff sorted in the descending order of the salary.

15- List the first and last names, the age, and the branch phone number of all male managers sorted in the ascending order of the age.

16- List the first name, the last name, the monthly salary, the city, and the state of staff members who live and work in the same state, along with the city of the branch. Hint: The WHERE clause should include two conditions; the first condition joins the tables, and the second compare the state attribute of the staff with the state attribute of the branch.

17- Similarly, list the first name, the last name, the city, the state, and the branch address of staff members who live and work in the same city. Note: Consider the case where two different cities have the same name but from different states. Example: New Haven exists in both Connecticut (CT) and New York (NY) states.

18- List the first name, the last name, and the state of staff members who live in a state and work in a different state; include the state of the branch in your SELECT list as well.

19- List the branches with no staff members. Hint: Use a subquery that returns the branchNo of branches that have staff members.

20- Using a left outer join, list the branchNo, the address, the city, and the state of all branches, and the first and last names of any staff members working in these branches, sorted by the branch address in the ascending order.

21- Using UNION, list the states of the branches and the staff members.

22- List the states that have both staff and branches.

23- List the states where there are branches but no staff members. Note: To express the Set-difference, SQLite uses the operator EXCEPT and Oracle uses the operator MINUS.

24- List the number of staff, the sum and the average of their salaries in each branch that has a salary average between 35000 and 38999.

25- In Task 19 you used a subquery to list the branches with no staff members. List the same content using an outer LEFT JOIN.

26- List the staff members with the second highest salary.

Answer 1

I have used MY SQl

1) select max(salary),min(salary) from Staff;

ouput:       

max(salary)       min(salary)
60000   24000

    

2)select count(staffNo) from Staff where position="Manager";

output:  

count(staffNo)
7

3)select state,count(staffNo),avg(salary) from Staff group by state;

output:

state     count(staffNo)  avg(salary)
NY      8       42000.0000
AZ      2       30000.0000
CT      4       36000.0000
CA      3       40000.0000
NJ      6       34000.0000

4)select staffNo from Staff where salary>ALL(select avg(salary) from Staff);

output:

staffNo
S001
S004
S005
S007
S010
S011
S013
S016
S021

5)select staffNo from Staff where salary>ALL(select avg(salary) from Staff WHERE sex='F' and position='Manager');

output:

staffNo
S010
S013

6)select *from Staff where age=ALL(Select min(age) from Staff );

output:

staffNo   fName   lName   position        sex     age     salary  phone   address city    state   branchNo
S017    Janice  Underwood       Assistant       F       20      24000   720-822-7652    38 Pleasant Hill Rd     New Haven       CT      B002
S019    Amos    Wilkerson       Assistant       M       20      24000   310-898-2154    34 Raritan Center Pky   Los Angeles     CA      B001
S022    Alisa   Garcia  Assistant       F       20      24000   646-411-4775    22 Sip Ave      Jersey City     NJ      B004

7)select *from Staff where age=ALL(select max(age) from Staff where position='Supervisor') and position='Supervisor';

output:

staffNo   fName   lName   position        sex     age     salary  phone   address city    state   branchNo
S005    Danny   Chavez  Supervisor      M       41      48000   720-828-7047    46314 Route 130 Fairfield       CT      B002
S007    Patty   Frazier Supervisor      F       41      48000   213-639-9887    2409 Alabama Rd Riverside       CA      B001

8)select *from Staff where salary=ALL(select min(salary) from Staff where position="Manager") and position="Manager";

output:

staffNo   fName   lName   position        sex     age     salary  phone   address city    state   branchNo
S001    Krista  Allen   Manager F       33      48000   917-874-1229    4 Kohler Memorial Dr    Brooklyn        NY      B004
S004    Joe     Bryan   Manager M       34      48000   720-870-5536    42754 S Ash Ave New Haven       NY      B002
S011    Lorena  Marsh   Manager F       36      48000   720-634-7158    426 Wolf St     New Haven       CT      B002
S016    Owen    Singleton       Manager M       31      48000   310-928-5182    4119 Metropolitan Dr    Los Angeles     CA      B001
S021    Brad    Herman  Manager M       39      48000   917-577-5488    16 E 108th St   New York        NY      B004

9)select *from Staff where salary=ALL(select max(salary) from Staff where position="Assistant") and position="Assistant" ;

output:

staffNo   fName   lName   position        sex     age     salary  phone   address city    state   branchNo
S002    Charlie Arnold  Assistant       M       32      36000   347-925-5213    2371 Jerrold Ave        Queens  NY      B003
S003    Jonathon        Bowman  Assistant       M       28      36000   213-775-4480    10759 Main St   Scottsdale      AZ      B001
S008    Emmett  Garza   Assistant       M       30      36000   917-877-8409    6 Gilson St     Bronx   NY      B004
S014    Diane   Rogers  Assistant       F       27      36000   347-504-3552    82 Us Highway 46        Clifton NJ      B004
S015    Nina    Scott   Assistant       F       29      36000   201-636-4117    33 Lewis Rd #46 Jersey City     NJ      B003

10)select s.*from Staff s,Branch b where s.branchNo=b.branchNo and b.address="22 Canal St" and s.position="Manager";

output:

staffNo   fName   lName   position        sex     age     salary  phone   address city    state   branchNo
S001    Krista  Allen   Manager F       33      48000   917-874-1229    4 Kohler Memorial Dr    Brooklyn        NY      B004
S013    Leslie  Pearson Manager F       52      60000   646-752-4114    30553 Washington Rd     Plainfield      NJ      B004
S021    Brad    Herman  Manager M       39      48000   917-577-5488    16 E 108th St   New York        NY      B004

11)select count(staffNo),avg(salary) from Staff s,Branch b where s.branchNo=b.branchNo and b.address="22 Canal St" and sex="F" and position="Manager";

output:

count(staffNo)    avg(salary)
2       54000.0000

12)select * from Staff where branchNo=ALL(select branchNo from Branch where address='366 Tiger Ln');

outptut:

staffNo   fName   lName   position        sex     age     salary  phone   address city    state   branchNo
S003    Jonathon        Bowman  Assistant       M       28      36000   213-775-4480    10759 Main St   Scottsdale      AZ      B001
S006    Renee   Figueroa        Assistant       F       21      24000   213-420-8970    96541 W Central Blvd    Phoenix AZ      B001
S007    Patty   Frazier Supervisor      F       41      48000   213-639-9887    2409 Alabama Rd Riverside       CA      B001
S016    Owen    Singleton       Manager M       31      48000   310-928-5182    4119 Metropolitan Dr    Los Angeles     CA      B001
S019    Amos    Wilkerson       Assistant       M       20      24000   310-898-2154    34 Raritan Center Pky   Los Angeles     CA      B001

13)select *from Staff LEFT JOIN Branch On Staff.branchNo=Branch.branchNo where Branch.address='366 Tiger Ln';

output:

staffNo   fName   lName   position        sex     age     salary  phone   address city    state   branchNo        branchNo        address city    state   phone
S003    Jonathon        Bowman  Assistant       M       28      36000   213-775-4480    10759 Main St   Scottsdale      AZ      B001    B001    366 Tiger Ln    Los Angeles     CA      213-539-8600
S006    Renee   Figueroa        Assistant       F       21      24000   213-420-8970    96541 W Central Blvd    Phoenix AZ      B001    B001    366 Tiger Ln    Los Angeles     CA      213-539-8600
S007    Patty   Frazier Supervisor      F       41      48000   213-639-9887    2409 Alabama Rd Riverside       CA      B001    B001    366 Tiger Ln    Los Angeles     CA      213-539-8600
S016    Owen    Singleton       Manager M       31      48000   310-928-5182    4119 Metropolitan Dr    Los Angeles     CA      B001    B001    366 Tiger Ln    Los Angeles     CA      213-539-8600
S019    Amos    Wilkerson       Assistant       M       20      24000   310-898-2154    34 Raritan Center Pky   Los Angeles     CA      B001    B001    366 Tiger Ln    Los Angeles     CA      213-539-8600

14)select s.fName,s.lName,s.salary,b.address from Staff s,Branch b where s.branchNo=b.branchNo order by s.salary desc;

output:

fName     lName   salary  address
Jesus   Mann    60000   55 Waydell St
Leslie  Pearson 60000   22 Canal St
Brad    Herman  48000   22 Canal St
Owen    Singleton       48000   366 Tiger Ln
Joe     Bryan   48000   18 Harrison Rd
Danny   Chavez  48000   18 Harrison Rd
Krista  Allen   48000   22 Canal St
Patty   Frazier 48000   366 Tiger Ln
Lorena  Marsh   48000   18 Harrison Rd
Charlie Arnold  36000   55 Waydell St
Nina    Scott   36000   55 Waydell St
Diane   Rogers  36000   22 Canal St
Lana    Hodges  36000   22 Canal St
Emmett  Garza   36000   22 Canal St
Jonathon        Bowman  36000   366 Tiger Ln
Alisa   Garcia  24000   22 Canal St
Igor    Strovinsky      24000   22 Canal St
Kelley  Wood    24000   55 Waydell St
Amos    Wilkerson       24000   366 Tiger Ln
Edward  Warner  24000   55 Waydell St
Janice  Underwood       24000   18 Harrison Rd
Erik    Olson   24000   18 Harrison Rd
Renee   Figueroa        24000   366 Tiger Ln
fName   lName   age     phone
Owen    Singleton       31      213-539-8600
Joe     Bryan   34      203-444-1818
Brad    Herman  39      212-055-9000
Jesus   Mann    47      201-700-7007

15)select s.fName,s.lName,s.age,b.phone from Staff s,Branch b where s.branchNo=b.branchNo and s.position="Manager" and s.sex="M"order by s.age;

output:

fName     lName   age     phone
Owen    Singleton       31      213-539-8600
Joe     Bryan   34      203-444-1818
Brad    Herman  39      212-055-9000
Jesus   Mann    47      201-700-7007

16)select s.fName,s.lName,s.salary,s.city,s.state from Staff s LEFT JOIN Branch b On s.branchNo=b.branchNo where s.state=b.state;   

output:

fName     lName   salary  city    state
Krista  Allen   48000   Brooklyn        NY
Danny   Chavez  48000   Fairfield       CT
Patty   Frazier 48000   Riverside       CA
Emmett  Garza   36000   Bronx   NY
Lana    Hodges  36000   New York        NY
Lorena  Marsh   48000   New Haven       CT
Erik    Olson   24000   New Haven       CT
Nina    Scott   36000   Jersey City     NJ
Owen    Singleton       48000   Los Angeles     CA
Janice  Underwood       24000   New Haven       CT
Edward  Warner  24000   Essex   NJ
Amos    Wilkerson       24000   Los Angeles     CA
Kelley  Wood    24000   Essex   NJ
Brad    Herman  48000   New York        NY
Igor    Strovinsky      24000   Brooklyn        NY

          

17)select s.fName,s.lName,s.salary,s.city,s.state from Staff s LEFT JOIN Branch b On s.branchNo=b.branchNo where s.branchNo=b.branchNo and s.state=b.state and s.city=b.city;

output:

fName     lName   salary  city    state
Lana    Hodges  36000   New York        NY
Lorena  Marsh   48000   New Haven       CT
Erik    Olson   24000   New Haven       CT
Owen    Singleton       48000   Los Angeles     CA
Janice  Underwood       24000   New Haven       CT
Edward  Warner  24000   Essex   NJ
Amos    Wilkerson       24000   Los Angeles     CA
Kelley  Wood    24000   Essex   NJ
Brad    Herman  48000   New York        NY

18)select s.fName,s.lName,s.salary,s.city,s.state,b.address from Staff s,Branch b where s.branchNo=b.branchNo and s.state<>b.state ;

output:

fName     lName   salary  city    state   address
Charlie Arnold  36000   Queens  NY      55 Waydell St
Jonathon        Bowman  36000   Scottsdale      AZ      366 Tiger Ln
Joe     Bryan   48000   New Haven       NY      18 Harrison Rd
Renee   Figueroa        24000   Phoenix AZ      366 Tiger Ln
Jesus   Mann    60000   Staten Island   NY      55 Waydell St
Leslie  Pearson 60000   Plainfield      NJ      22 Canal St
Diane   Rogers  36000   Clifton NJ      22 Canal St
Alisa   Garcia  24000   Jersey City     NJ      22 Canal St

19)select branchNo from Branch where branchNo not in (select b.branchNo from Branch b,Staff s where b.branchNo=s.branchNo );

branchNo
B005
B006

20)select b.branchNo,b.address,b.city,b.state,s.fName,s.lName from Branch b left outer join Staff s on b.branchNo=s.branchNo;

branchNo  address city    state   fName   lName
B001    366 Tiger Ln    Los Angeles     CA      Amos    Wilkerson
B001    366 Tiger Ln    Los Angeles     CA      Owen    Singleton
B001    366 Tiger Ln    Los Angeles     CA      Patty   Frazier
B001    366 Tiger Ln    Los Angeles     CA      Renee   Figueroa
B001    366 Tiger Ln    Los Angeles     CA      Jonathon        Bowman
B002    18 Harrison Rd  New Haven       CT      Janice  Underwood
B002    18 Harrison Rd  New Haven       CT      Erik    Olson
B002    18 Harrison Rd  New Haven       CT      Lorena  Marsh
B002    18 Harrison Rd  New Haven       CT      Danny   Chavez
B002    18 Harrison Rd  New Haven       CT      Joe     Bryan
B003    55 Waydell St   Essex   NJ      Kelley  Wood
B003    55 Waydell St   Essex   NJ      Edward  Warner
B003    55 Waydell St   Essex   NJ      Nina    Scott
B003    55 Waydell St   Essex   NJ      Jesus   Mann
B003    55 Waydell St   Essex   NJ      Charlie Arnold
B004    22 Canal St     New York        NY      Igor    Strovinsky
B004    22 Canal St     New York        NY      Alisa   Garcia
B004    22 Canal St     New York        NY      Brad    Herman
B004    22 Canal St     New York        NY      Diane   Rogers
B004    22 Canal St     New York        NY      Leslie  Pearson
B004    22 Canal St     New York        NY      Lana    Hodges
B004    22 Canal St     New York        NY      Emmett  Garza
B004    22 Canal St     New York        NY      Krista  Allen
B005    1725 Roosevelt Ave      Queens  NY      NULL    NULL
B006    1471 Jerrold Ave        Philadelphia    PA      NULL    NULL

21)select state from Branch union select state from Staff;

state
CA
CT
NJ
NY
PA
AZ

22)select state from Branch where state IN (select state from Staff ) group by state;

state
CA
CT
NJ
NY

23)select state from Branch where state not in (select b.state from Branch b where state IN (select s.state from Staff s) group by b.state);

state
PA

24)select count(staffNo),sum(salary),avg(salary) from Staff group by branchNo having avg(salary) between 35000 and 38999;

count(staffNo)    sum(salary)     avg(salary)
5       180000  36000.0000
5       180000  36000.0000
5       192000  38400.0000

25)select b.branchNo from Branch b where b.branchNo not in (select b1.branchNo from Branch b1 left outer join Staff s on s.branchNo=b1.branchNo);

branchNo
B005
B006

26)SELECT fName,lName FROM Staff WHERE salary= (SELECT DISTINCT(salary) FROM Staff ORDER BY salary LIMIT 1,1);

fName     lName
Charlie Arnold
Jonathon        Bowman
Emmett  Garza
Lana    Hodges
Diane   Rogers
Nina    Scott