MySQL Tutorial: http://www.mysqltutorial.org/

Employee EmployeeID LastName DeptID 1 Rafferty 31 2 Jones 33 3 Heisenberg 33 4 Robinson 34 5 Smith 34 6 Williams NULL 7 Brown NULL

Department DepartmentID DepartmentName 31 Sales 33 Engineering 34 Clerical 35 Marketing

Table Structures:

Employee

Field Name	Data Type	Constraint	Key
EmployeeID	int	not null	Primary
LastName	varchar(25)	not null
DeptID	int		Foreign (DepartmentID in Department)

Department

Field Name	Data Type	Constraint		Key
DepartmentID	int	not null	Primary
DepartmentName	varchar(25)	not null, unique

Problem 1): Consider the above tables.

1) Write SQL statement to create the above tables in MySQL DBMS.

(Identify the primary key(s), foreign key(s), not null and unique constraints). (5 pts) 2) Write SQL Statement to insert the values into each table (5 pts).

• Write SQL statement to add the FirstName column into the Employee table and add the following first names. The structure of the FirstName is similar to the LastName column (varchar(25), not null) (5 pts).

EmployeeID	FirstName
1	John
2	Mary
3	David
4	Bob
5	Peter
6	Alice
7	Heather

• Write the following join for them (deliver both SQL statements as well as the table result) (5 pts):
  1. Cross Join
  2. Inner Join
  3. Left Join
  4. Right Join
• Delete the employee(s) with no department (Use only ONE SQL statement) (5 pts).
• Delete the Sales department. If you are not able to delete this record, explain why? And how you can solve the problem (5 pts).

Problem 2)

For all the below queries (1-8) write the SQL statement (chapter 3-6). Consider the University database schema (The ER diagram is attached to this assignment if needed): Query 1) Find all instructors earning the salary higher than the average salary (10 pts).

Query 2) Find the minimum, maximum, and average salary for each department (10 pts).

Query 3) Find all the students who take credits between 30 and 100 and order them alphabetically by name (10 pts).

Query 4) Find all the instructors with their department name and department building (10 pts).

Query 5) Find all the students with their taken courses and grades (10 pts).

Query 6) Find the instructor(s) who earns the second highest salary (10 pts).

Query 7) Increase all credits by 1 for those courses that are taught in semester Fall 2010 (10 pts).

Query 8) Delete those instructors who have never taught (10 pts).

Bonus: You will get 20 extra credits if you write equivalent SQL queries for TWO of the above queries (Queries 1-8).