SQL Project -- 3
Budget: $30 – $250 USD
SQL project
Write the SQL statements in order to create the tables for the database. Use the Entity Relationship Diagram (ERD) .
You need to create the tables as well as the required constraints, including the keys (primary and foreign), and the relationships between tables.
. Write the following queries to retrieve the information detailed below.
1. Display all contents of the Clients table
2. First names, last names, ages and occupations of all clients
3. First and last names of clients that borrowed books in March 2018
4. First and last names of the top 5 authors clients borrowed in 2017
5. Least 5 author nationalities clients borrowed during the years 2015-2017
6. The book that was most borrowed during the years 2015-2017
7. Top borrowed genres for client born in years 1970-1980
8. Top 5 occupations that borrowed the most in 2016
9. Average number of borrowed books by job title
10. Create a VIEW and display the titles that were borrowed by at least 20% of clients
11. The top month of borrows in 2017
12. Average number of borrows by age
13. The oldest and the youngest clients of the library
14. First and last names of authors that wrote books in more than one genre
As you work on these queries, create indexes that will increase your queries' performance.
Include comments in your code that address the purpose of your query and explains each step. Save your queries and results in a plain-text file
Write the SQL statements in order to create the tables for the database. Use the Entity Relationship Diagram (ERD) .
You need to create the tables as well as the required constraints, including the keys (primary and foreign), and the relationships between tables.
. Write the following queries to retrieve the information detailed below.
1. Display all contents of the Clients table
2. First names, last names, ages and occupations of all clients
3. First and last names of clients that borrowed books in March 2018
4. First and last names of the top 5 authors clients borrowed in 2017
5. Least 5 author nationalities clients borrowed during the years 2015-2017
6. The book that was most borrowed during the years 2015-2017
7. Top borrowed genres for client born in years 1970-1980
8. Top 5 occupations that borrowed the most in 2016
9. Average number of borrowed books by job title
10. Create a VIEW and display the titles that were borrowed by at least 20% of clients
11. The top month of borrows in 2017
12. Average number of borrows by age
13. The oldest and the youngest clients of the library
14. First and last names of authors that wrote books in more than one genre
As you work on these queries, create indexes that will increase your queries' performance.
Include comments in your code that address the purpose of your query and explains each step. Save your queries and results in a plain-text file