You are required to build a database in SSMS,create and run several SQL queries on the data stored in the database.

2. Logical design of the database: Use the ERD provided below. Also follow the additional simplification or restrictions added here:

a. You are NOT REQUIRED to use Date Last Performed attribute on SOLOIST and PERFORMANCE relationship.

b. You are NOT REQUIRED to use any MOVEMENT related attributes.

c. You are NOT REQUIRED to use CONCERT SEASON entity.

d. Each concert has ONLY ONE DATE.

e. SOLOIST, COMPOSER and CONDUCTOR names must be stored as First and Last name.

f. NOTE: you need to use additional (associative) entities not shown in the diagram. g. Review Project Part-1 description in case of any doubts about the relationships.

3. (5 points) SQL codes for creating all tables (with all PK and FK constraints as needed). Name each table with prefix DT_, for example, Employee table should be DT_Employee.

4. (5 points) SQL codes for inserting at least 7 rows of data into each table.

a. You must insert data for SOLOIST ID 100, Mark Meyers.

b. Ensure that each composition is performed at least once.

c. You must have enough data so that every query below has some non-zero results.

5. (10 points, 1 point for each query) Create SQL queries for the following scenarios (even if you do not have data in your tables that will produce output, you must write the query correctly). You must show your query and results in your submission. Remember to save all the queries in your local environment with names such as 5a.SQL, 5b.SQL, ... 5j.SQL

a. Retrieve the name of each Conductor who has worked on one or more concerts. Suppress duplicate output and display the values in alphabetical order.

b. Retrieve the Soloist Name, Concert Number for Composition ID 1, 2, or 3;

c. Retrieve the first and last names of those SOLOIST whose last names end with any vowel but not y;

d. Retrieve the last name of each Conductor and the concert number and order them in reverse alphabetical order of conductors first name, and normal order of the concert number.

e. List the first and last name of every SOLOIST who has worked on the same Concert(s) as Mark Meyers (use a subquery);

f. Display each concert id and the total number of Soloist on that concert.

g. Develop your own query that uses an outer join.

h. Develop your own query that uses a group by with the having clause.

i. Find the name(s) of the Soloist without any performance. j. Develop your own query that uses the DISTINCT keyword.