Description

Lab 03: SQL Data Manipulation

Copy and paste the content of the text file "Lab03_Data.txt" into your SQL Editor, then execute it. This code will create the tables Branch and Staff, then insert sample data into both tables. The structures of the created tables are as follows.
Branch (branchNo, address, city, state, phone)

Staff (stafftNo, fName, lName, position, sex, phone, address, city, state, branchNo)

List the content of the Branch and Staff tables and make sure data were properly inserted.

The total number of records should be 6 in the Branch table, and 23 in the Staff table.

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.

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-  Using a subquery, list the staff members who work in the branch located at '366 Tiger Ln'.

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

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

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

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.

17- 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.

18- List the first name, the last name, the city, the state of staff members who live in a city and work in a different city, along with the branch city and state.

19- List the branches with no 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 states where there are staff members but no branches.

25-  List the number of staff in each branch, along with the sum and average of their salaries. Exclude, from the list the branches that have salary averages less than 37000.

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