[SOLVED] Assignment #2 COMP 440 

Description

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

Table Structures:

Employee

Department

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

• 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).