Q2(a)

The Lyons Dance Company uses the following database that stores data about ballet dancers, shows and companies.

COMPANY(compID, compname, city, country)

DANCER(dancerID, dname, birthyear, country, annualincome)

SHOW(showID, title, choreographer, composer, year)

ROLE(dancerID, showID, role, compID)

The primary key for each relation is indicated by the attribute underlined. The tables contain the following information:

• COMPANY stores data about dance companies. The attribute compID is a number while the attributes compname, city and country are all strings.
• DANCER stores data about individual dancers. The attribute dancerID is a unique integer ID for each dancer. The attributes dname and country are strings, birthyear is an integer and annualincome can be a decimal point number.
• SHOW stores data about ballet shows (dances). Each show has a unique integer showID, string attributes for the show title, choreographer and composer, and an integer year in which the show was created.
• ROLE stores data about which dancers have been in which shows, the name of the role (part) they danced, and the company where they danced that part in that particular show. The dancerID, showID and compID are integers and the role is a string. A dancer may have danced multiple roles in the same show at the same company, or danced the same role in the same show for different companies. Several attributes in ROLE are foreign keys: dancerID references dancerID in DANCER, showID references showID in SHOW and compID references compID in COMPANY.

(a) i. Write a SQL query to retrieve the following requested information from the dance companys database:

Say each dancer contributes a commission 10% of their annual income to the company each year. List the dancer ID, name, annual income, and the commission the dancer has contributed to his or her company in the past 3 years.

(a) ii. Write a SQL query to retrieve the following requested information from the dance companys database:

Show the dancer ID, name and country for dancers who have performed in a show composed by 'Tchaikovsky' but not choreographed by 'Petipa'. Each dancer should only appear once in the output. List the results based on dancer name in descending order.

(a) iii. Write a SQL query to retrieve the following requested information from the dance companys database:

Show the dancer name and company name of the dancers who earn more than the average annual income for their country. Each dancer name and company name pair should appear only once in the output.