Submission file
Please submit a PDF file that, for each query, contains:
1) SQL query: copy and paste your query in the pdf 2) a screenshot executing the query in SQLite Studio, and it should clearly show the output table.
EntertainmentAgency.sqlite
Query1. What is the percentage of male and female entertainer members whose musical style is Jazz? The query should output the percentages of each gender separately and indicate which is which. (The percentage here means percentage out of all members)
Query2. What is the full name (in the form of LastName, FirstName) of the top 3 agents who have the highest average commission per engagement? Print out both the full names and the average commissions. The average commission can be calculated by multiplying the contract price with the agent’s commission rate.
Query3. What is the total income of the Jazz entertainers (i.e.,the sum of all Jazz entertainers’ income across all of their engagements) and the total income of the Salsa entertainers? The income of each entertainer for each engagement is the ContractPrice of the engagement minus the agent’s commission. To receive credit you must not use subqueries anywhere (i.e., no nested SELECT clauses at all).
Query4. What are the top 5 musical styles that have the highest number of customers, and how many customers each of these styles has?
BowlingLeague.sqlite
Query5. Which teams have captains with the same last name? Each such team must be listed exactly once, along with the team captain’s full name (in the form of LastName, FirstName).
Query6. List the TourneyDate,TourneyLocation,TeamNames and Lanes of the teams that have captains with the same last name. You can use only the team name from the results of the previous question. To receive credit you must not use subqueries anywhere (i.e., no nested SELECT clauses at all).
Query7. How many teams have the different players with the same last name? To receive credit you must not use subqueries anywhere (i.e., no nested SELECT clauses at all).
SalesOrders.sqlite
Query8. Print the states that have a customer, but no vendor.
Query9. Print a report of the 5 products that have sold in the greatest total quantity (the most popular products). You should print the name of the product and the total quantity. Be careful to count the quantity in each order, not just the number of times a product was ordered.
Query10. Print all the pair of products whose names have the same number of characters. List each pair only once (ie., don't list both A,B and B,A).(SQLite has a LENGTH() function that can be applied to text. https://sqlite.org/lang_corefunc.html#length)
You can easily download all the schema for GitHub using below link:
https://github.com/CodersArts/database
Comments