Question: 3. Write SQL statements to realize the following: 3a. List all registered musicians from USA or JAM (where USA and JAM are abbreviations for United

3. Write SQL statements to realize the following: 3a. List all

Write SQL statements to realize the following:

3a. List all registered musicians from USA or JAM (where "USA" and "JAM" are abbreviations for United States and Jamaica). [03]

3b. Give the ENO of every ensemble that includes a SAXAPHONE or CLARINET player. [03]

3c. Give the ENO of every ensemble that includes a SAXAPHONE but not a CLARINET player. [04]

3d. List all compositions (CNO) by MOZART [04]

3e. List all performances (PNO CNO MNO & PCOUNTRY) of compositions in the country of origin. [06]

3f. Give the ENO of every ensemble that includes a SAXAPHONE or CLARINET player, but not both. [06]

3g. Find CNO for compositions which have been performed in USA. [03]

3h. List countries in which MOZART's compositions have been performed. [03]

3i. Give ENAME of ensembles whose manager is AMERICAN, JAMAICAN, or RUSSIAN. [03]

4. Define and create SQL views to realize the above requirements (of question 3). [36]

Music Database Relations Musicians (Mog. MName, MDOB MCountry} PK (Mnol Compositions (Co. CTitle CMno. CDate} PK [Cng]: FK [CMng references Musicians) Ensembles {Eno, EName ECountry, EMnoMor} PK [Eno]; FK [EMnoMar references Musicians) Performances (Png. PDate. Pone PCity. PCountry Peno_CKs (PDate. Penol. Prel; FK [PCno references Compositions); FK [PEno references Ensembles] Ensemble Members (EmEng. EmMno, Emlostrument) PK [EmEng. Emunol: FK [EmEng references Ensembles); FK EmMno references Musicians)

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

1. Prepare a database specification (either a RAL or an ESG showing database-related details only) for the database. This database specification may be refined by introducing a sixth relational...

Individual Case Analysis Four cases are available from Harvard (see first : Case pack of 4 cases available at: http://cb.hbsp.harvard.edu/cbmp/access/49960103 ). Answer each of these questions in...

Tasks: Write a SQL statement to select the database with your last name to store the data. If it does not exist in your MySQL server, use "CREATE DATABASE" command to create one. WRITE SQL statements...

* * * * Please write an sql script with the table provide * * * Objectives: Write SQL statements to define a database, to define tables, to view the table structures, to upload data into the tables...

Lab 2 Relational Database defining tables, viewing the structure of the defined tables, and adding records into tables. The outline of the script file lists as follows: Task 1 : Write a SQL statement...

Question 4 SQL (16 points) Write SQL queries based on the following two tables. For your understanding, the partial contents of the tables are shown, but assumes that there are many more rows (e.g.,...

SQL Relational Database - Your task is to write SQL statements to define tables for the corresponding relations. Make sure that the data type and size for each attribute are appropriate. For example,...

Consider the following database schema for student organizations. Students ( ID , fName, lName, phone, dateOFBirth ) Clubs ( ID , title, establishedDate ) Note that table Clubs has two candidate...

Page 1 of 2 Pages Part 2A. E-R Diagramming Questions. For each of the scenarios described below, create an E-R diagram. If none is described, create an identifier for each entity, otherwise, provide...

SQ in PL / SQL - Use the table named, 'yourfirstname new items' for question 3 through 7 . 3 . Write SQL statements to create a table named 'yourfirstname. new items' and insert the provided data...

Let S be the sample space for an experiment E. If A, B are events from S with A B = S, A B = , Pr(A) = p, and Pr(B) = p2, determine p.

Compare the average duration of unemployment in the United States and Europe.

( Inflation and interest rates ) What would you expect the nominal rate of interest to be if the real rate is 4 . 1 4 . 1 % and the expected inflation rate is 7 . 3 7 . 3 % ? Question content area...

Compared with half a century ago, adoption has become _ _ _ _ _ _ _ _ _ common, but it is more open and acceptabl e , so we probably discuss it _ _ _ _ _ _ _ . fill in the blanks more or much less or...

KEY QUESTION Suppose a bond with no expiration date has a face value of $10,000 and annually pays a fixed amount of interest of $800. Compute and enter in the spaces provided in the accompanying...

KEY QUESTION Why is it reasonable to ignore diversifiable risk and care only about nondiversifiable risk? What about an investor who puts all of his money into only a single risky stock? Can he...

What is inflation targeting, and how does it differ from artful management? What are the main benefits of inflation targeting, according to its supporters? Why do many economists think it is...