Your assignment is based on the following three tables.

Employee:

e_id	first_name	last_name	gender	salary
1	Ellen	Monk	F	60000
2	Joe	Brady	M	50000
3	Dave	Davidson	M	55000
4	Sebastian	Cole	M	35000
5	Michael	Doo	M	25000
6	Ruby	Clark	F	30000
7	Bob	House	M	47500
8	Hannah	Stanley	F	70000
9	Bradley	Wilson	M	75000

 

Departments:                                                                       

dep_id	dep_name	dep_location
1	Research	Indianapolis
2	Human Resource	Chicago
3	Sales	Salt Lake City
4	Marketing	Chicago
5	Finance	Indianapolis

 

Register:

e_id	dep_id
1	1
6	2
4	1
7	5
2	2
5	3
9	4
8	3
3	1

 

1) CREATE TABLE statements to create the three tables given above. Don't forget to specify appropriate primary and foreign keys when you do so! You are welcome to pick data types of your own choice.

For each table, include a justification of why you picked each data type, and any other decisions taken.

2) Next, insert the data you see here into each of the tables created in step 1) using INSERT statements.

Remember, not your own data, but the data that i've presented above.

3) Create appropriate SQL queries to answer the following questions.

•    For each department, list department number, department name and number of employees in these departments

•    List department number, department name, and number of employees in departments that have two or more employees, ordered by department number. 

•    List employee names (first_name and last_name) of each male employee, together with their department name.

•    List the distinct name of employees who are not working in “Research”

•    List the name and number of departments whose employee average salary is between 30000 and 60000.

List the name of employees who works (department location) in Salt Lake City and whose last name start with ‘D’.

 

Save the file as a pdf or word doc

Attachments:

• 1511003969-sql.docx