Refer to files, I just need the answers to 10 SQL queries, I have most of the queries already. you just need to import them into the database mysql and then screenshot the answers
4/13/23, 1:32 PM Database Programming - Assignment: Creating & Manipulating a Database | Study.com https://study.com/academy/lesson/database-programming-assignment-creating-manipulating-a-database.html 1/21 Database Programming - Assignment: Creating & Manipulating a Database If you have a Study.com College Accelerator membership and are seeking college credit for this course, you must submit an assignment and pass the proctored �nal exam. You must submit your assignment before registering for the �nal. Below you will �nd prompts and instructions for submitting your assignment. About this Assignment This is a Database Programming course. This course covers advanced topics in databases. It starts by reviewing basic knowledge on databases and ends with advanced database concepts like security. In this project, you will use the knowledge you acquired throughout the course to build a simple database and query it to extract information from it. You will create tables and relationships among them, in addition to the necessary keys and indexes. The next step will be to populate the database with suitable data. Populating the tables with su�cient and appropriate example data is an important step in testing and validating your design. When your database is ready, you will write SQL queries to retrieve information. Note: This assignment was created with MySQL in mind. Therefore, dates, numbers, etc. have been set up with that tool in mind. Upon completion of this project, you will be able to: Write SQL queries to create tables Write SQL queries to create relationships among tables Identify indexes and create them in a database Write queries to extract important information from a database Prompt In this project you will build a database for a public library. This database is aimed to collect and analyze information about the clients' reading interests. The project concentrates only on books and the clients' interests in books. The analyses that will result from this project will be used by the library's management to decide on the future purchasing policy. https://study.com/ 4/13/23, 1:32 PM Database Programming - Assignment: Creating & Manipulating a Database | Study.com https://study.com/academy/lesson/database-programming-assignment-creating-manipulating-a-database.html 2/21 A. Write the SQL statements in order to create the tables for the database. Use the Entity Relationship Diagram (ERD) of the database shown in Figure 1. For simplicity, we are assuming in this project that a book cannot be written by more than one author. You need to create the tables as well as the required constraints, including the keys (primary and foreign), and the relationships between tables. Figure 1: ERD for Library Database B. Populate your database with the sample set of data given to you in the tables below the assignment prompts. C. 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. Nationalities of the least 5 authors that 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 4/13/23, 1:32 PM Database Programming - Assignment: Creating & Manipulating a Database | Study.com https://study.com/academy/lesson/database-programming-assignment-creating-manipulating-a-database.html 3/21 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. You must 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 �le that you will submit as your assignment. AuthorID AuthorFirstName AuthorLastName AuthorNationality 1 So�a Smith Canada 2 Maria Brown Brazil 3 Elena Martin Mexico 4 Zoe Roy France 5 Sebastian Lavoie Canada 6 Dylan Garcia Spain 7 Ian Cruz Mexico 8 Lucas Smith USA 9 Fabian Wilson USA 10 Liam Taylor Canada 11 William Thomas Great Britain 12 Logan Moore Canada 13 Oliver Martin France 14 Alysha Thompson Canada 15 Isabelle Lee Canada 16 Emily Clark USA 17 John Young China Author table: 4/13/23, 1:32 PM Database Programming - Assignment: Creating & Manipulating a Database | Study.com https://study.com/academy/lesson/database-programming-assignment-creating-manipulating-a-database.html 4/21 18 David Wright Canada 19 Thomas Scott Canada 20 Helena Adams Canada 21 So�a Carter USA 22 Liam Parker Canada 23 Emily Murphy USA BookID BookTitle BookAuthor Genre 1 Build your database system 1 Science 2 The red wall 2 Fiction 3 The perfect match 3 Fiction 4 Digital Logic 4 Science 5 How to be a great lawyer 5 Law 6 Manage successful negotiations 6 Society 7 Pollution today 7 Science 8 A gray park 2 Fiction 9 How to be rich in one year 8 Humor 10 Their bright fate 9 Fiction 11 Black lines 10 Fiction 12 History of theater 11 Literature 13 Electrical transformers 12 Science 14 Build your big data system 1 Science 15 Right and left 13 Children 16 Programming using Python 1 Science 17 Computer networks 14 Science 18 Performance evaluation 15 Science 19 Daily exercise 16 Well being 20 The silver uniform 17 Fiction 21 Industrial revolution 18 History Book table: 4/13/23, 1:32 PM Database Programming - Assignment: Creating & Manipulating a Database | Study.com https://study.com/academy/lesson/database-programming-assignment-creating-manipulating-a-database.html 5/21 22 Green nature 19 Well being 23 Perfect football 20 Well being 24 The chocolate love 21 Humor 25 Director and leader 22 Society 26 Play football every week 20 well being 27 Maya the bee 13 Children 28 Perfect rugby 20 Well being 29 The end 23 Fiction 30 Computer security 1 Science 31 Participate 22 Society 32 Positive �gures 3 Fiction ClientID ClientFirstName ClientLastName ClientDoB Occupation 1 Kaiden Hill 2006 Student 2 Alina Morton 2010 Student 3 Fania Brooks 1983 Food Scientist 4 Courtney Jensen 2006 Student 5 Brittany Hill 1983 Fire�ghter 6 Max Rogers 2005 Student 7 Margaret McCarthy 1981 School Psychologist 8 Julie McCarthy 1973 Professor 9 Ken McCarthy 1974 Securities Clerk 10 Britany O'Quinn 1984 Violinist 11 Conner Gardner 1998 Licensed Massage Therapist 12 Mya Austin 1960 Parquet Floor Layer 13 Thierry Rogers 2004 Student 14 Eloise Rogers 1984 Computer Security Manager 15 Gerard Jackson 1979 Oil Exploration Engineer 16 Randy Day 1986 Aircraft Electrician Client table: 4/13/23, 1:32 PM Database Programming - Assignment: Creating & Manipulating a Database | Study.com https://study.com/academy/lesson/database-programming-assignment-creating-manipulating-a-database.html 6/21 17 Jodie Page 1990 Manufacturing Director 18 Coral Rice 1996 Window Washer 19 Ayman Austin 2002 Student 20 Jaxson Austin 1999 Repair Worker 21 Joel Austin 1973 Police O�cer 22 Alina Austin 2010 Student 23 Elin Austin 1962 Payroll Clerk 24 Ophelia Wolf 2004 Student 25 Eliot McGuire 1967 Dentist 26 Peter McKinney 1968 Professor 27 Annabella Henry 1974 Nurse 28 Anastasia Baker 2001 Student 29 Tyler Baker 1984 Police O�cer 30 Lilian Ross 1983 Insurance Agent 31 Thierry Arnold 1975 Bus Driver 32 Angelina Rowe 1979 Fire�ghter 33 Marcia Rowe 1974 Health Educator 34 Martin Rowe 1976 Ship Engineer 35 Adeline Rowe 2005 Student 36 Colette Rowe 1963 Professor 37 Diane Clark 1975 Payroll Clerk 38 Caroline Clark 1960 Dentist 39 Dalton Clayton 1982 Police O�cer 40 Steve Clayton 1990 Bus Driver 41 Melanie Clayton 1987 Computer Engineer 42 Alana Wilson 2007 Student 43 Carson Byrne 1995 Food Scientist 44 Conrad Byrne 2007 Student 45 Ryan Porter 2008 Student 46 Elin Porter 1978 Computer Programmer 47 Tyler Harvey 2007 Student 4/13/23, 1:32 PM Database Programming - Assignment: Creating & Manipulating a Database | Study.com https://study.com/academy/lesson/database-programming-assignment-creating-manipulating-a-database.html 7/21 48 Arya Harvey 2008 Student 49 Serena Harvey 1978 School Teacher 50 Lilly Franklin 1976 Doctor 51 Mai Franklin 1994 Dentist 52 John Franklin 1999 Fire�ghter 53 Judy Franklin 1995 Fire�ghter 54 Katy Lloyd 1992 School Teacher 55 Tamara Allen 1963 Ship Engineer 56 Maxim Lyons 1985 Police O�cer 57 Allan Lyons 1983 Computer Engineer 58 Marc Harris 1980 School Teacher 59 Elin Young 2009 Student 60 Diana Young 2008 Student 61 Diane Young 2006 Student 62 Alana Bird 2003 Student 63 Anna Becker 1979 Security Agent 64 Katie Grant 1977 Manager 65 Joan Grant 2010 Student 66 Bryan Bell 2001 Student 67 Belle Miller 1970 Professor 68 Peggy Stevens 1990 Bus Driver 69 Steve Williamson 1975 HR Clerk 70 Tyler Williamson 1999 Doctor 71 Izabelle Williamson 1990 Systems Analyst 72 Annabel Williamson 1960 Cashier 73 Mohamed Waters 1966 Insurance Agent 74 Marion Newman 1970 Computer Programmer 75 Ada Williams 1986 Computer Programmer 76 Sean Scott 1983 Bus Driver 77 Farrah Scott 1974 Ship Engineer 78 Christine Lambert 1973 School Teacher 4/13/23, 1:32 PM Database Programming - Assignment: Creating & Manipulating a Database | Study.com https://study.com/academy/lesson/database-programming-assignment-creating-manipulating-a-database.html 8/21 79 Alysha Lambert 2007 Student 80 Maia Grant 1984 School Teacher BorrowID ClientID BookID BorrowDate 1 35 17 2016-07-20 2 1 3 2017-04-19 3 42 8 2016-10-03 4 62 16 2016-04-05 5 53 13 2017-01-17 6 33 15 2015-11-26 7 40 14 2015-01-21 8 64 2 2017-09-10 9 56 30 2017-08-02 10 23 2 2018-06-28 11 46 19 2015-11-18 12 61 20 2015-11-24 13 58 7 2017-06-17 14 46 16 2017-02-12 15 80 21 2018-03-18 16 51 23 2015-09-01 17 49 18 2015-07-28 18 43 18 2015-11-04 19 30 2 2018-08-10 20 48 24 2015-05-13 21 71 5 2016-09-05 22 35 3 2016-07-03 23 57 1 2015-03-17 24 23 25 2017-08-16 25 20 12 2018-07-24 Borrower table: 4/13/23, 1:32 PM Database Programming - Assignment: Creating & Manipulating a Database | Study.com https://study.com/academy/lesson/database-programming-assignment-creating-manipulating-a-database.html 9/21 26 25 7 2015-01-31 27 72 29 2016-04-10 28 74 20 2017-07-31 29 53 14 2016-02-20 30 32 10 2017-07-24 31 12 15 2018-04-25 32 77 13 2017-06-09 33 30 4 2017-10-24 34 37 24 2016-01-14 35 27 26 2017-06-05 36 1 16 2018-05-06 37 21 9 2016-03-19 38 69 28 2017-03-29 39 17 19 2017-03-14 40 8 9 2016-04-22 41 63 18 2015-01-25 42 65 20 2016-10-10 43 51 19 2015-07-28 44 23 12 2017-01-25 45 17 4 2017-04-18 46 68 5 2016-09-06 47 46 13 2017-09-30 48 15 13 2017-07-05 49 11 19 2017-12-14 50 78 15 2017-01-26 51 47 9 2015-03-03 52 68 7 2016-05-26 53 37 26 2017-02-06 54 48 27 2015-12-30 55 9 21 2017-10-21 56 29 8 2018-04-01 4/13/23, 1:32 PM Database Programming - Assignment: Creating & Manipulating a Database | Study.com https://study.com/academy/lesson/database-programming-assignment-creating-manipulating-a-database.html 10/21 57 64 18 2017-08-29 58 61 26 2018-02-21 59 39 28 2016-07-26 60 73 18 2018-08-22 61 11 13 2018-01-17 62 45 6 2016-07-20 63 33 13 2018-03-18 64 10 17 2016-06-06 65 28 18 2017-02-17 66 51 3 2016-12-09 67 29 2 2015-09-18 68 28 30 2017-09-14 69 74 20 2015-12-12 70 15 22 2015-01-14 71 57 8 2017-08-20 72 2 5 2015-01-18 73 74 12 2018-04-14 74 51 10 2016-02-25 75 25 17 2015-02-24 76 45 21 2017-02-10 77 27 25 2016-08-03 78 32 28 2016-06-15 79 71 21 2017-05-21 80 75 26 2016-05-03 81 56 32 2015-12-23 82 26 32 2015-05-16 83 66 32 2015-05-30 84 57 18 2017-09-15 85 40 15 2016-09-02 86 65 4 2017-08-17 87 54 7 2015-12-19 4/13/23, 1:32 PM Database Programming - Assignment: Creating & Manipulating a Database | Study.com https://study.com/academy/lesson/database-programming-assignment-creating-manipulating-a-database.html 11/21 88 29 4 2017-07-22 89 44 9 2017-12-31 90 56 31 2015-06-13 91 17 4 2015-04-01 92 35 16 2018-07-19 93 22 18 2017-06-22 94 39 24 2015-05-29 95 63 14 2018-01-20 96 53 21 2016-07-31 97 40 9 2016-07-10 98 52 4 2017-04-05 99 27 20 2016-09-04 100 72 29 2015-12-06 101 49 16 2017-12-19 102 6 12 2016-12-04 103 74 31 2016-07-27 104 48 32 2016-06-29 105 69 2 2016-12-27 106 60 32 2017-10-29 107 45 22 2017-06-12 108 42 15 2017-05-14 109 79 8 2016-10-13 110 70 18 2016-12-04 111 34 8 2016-03-06 112 43 8 2015-12-19 113 42 32 2016-04-20 114 67 5 2017-03-06 115 80 25 2015-06-23 116 54 11 2017-05-03 117 34 28 2017-08-30 118 65 20 2017-08-26 4/13/23, 1:32 PM Database Programming - Assignment: Creating & Manipulating a Database | Study.com https://study.com/academy/lesson/database-programming-assignment-creating-manipulating-a-database.html 12/21 119 61 19 2018-01-05 120 38 12 2018-01-17 121 51 4 2016-05-13 122 7 16 2016-03-17 123 46 16 2016-11-25 124 75 30 2018-08-12 125 72 32 2015-03-12 126 44 17 2015-06-15 127 68 15 2016-02-21 128 21 1 2016-06-19 129 14 25 2016-10-10 130 68 21 2016-05-27 131 35 20 2015-03-19 132 16 27 2016-08-08 133 79 31 2018-03-07 134 14 17 2018-04-28 135 29 28 2018-03-11 136 41 4 2018-08-08 137 42 3 2016-02-23 138 45 3 2017-07-10 139 36 16 2018-07-19 140 36 30 2015-08-07 141 54 32 2018-03-14 142 61 15 2017-03-28 143 1 13 2018-05-17 144 43 1 2015-05-14 145 37 14 2015-07-30 146 62 17 2015-09-19 147 50 22 2016-12-02 148 45 1 2016-07-24 149 32 17 2018-03-10 4/13/23, 1:32 PM Database Programming - Assignment: Creating & Manipulating a Database | Study.com https://study.com/academy/lesson/database-programming-assignment-creating-manipulating-a-database.html 13/21 150 13 28 2016-02-14 151 15 9 2018-08-11 152 10 19 2018-08-29 153 66 3 2016-11-27 154 68 29 2017-07-12 155 21 14 2018-06-27 156 35 9 2016-01-22 157 17 24 2016-08-25 158 40 21 2015-07-09 159 1 24 2016-03-28 160 70 27 2015-07-10 161 80 26 2016-04-24 162 29 5 2015-10-18 163 76 12 2018-04-25 164 22 4 2016-12-24 165 2 2 2017-10-26 166 35 13 2016-02-28 167 40 8 2017-10-02 168 68 9 2016-01-03 169 32 5 2016-11-13 170 34 17 2016-09-15 171 34 16 2018-04-13 172 80 30 2016-10-13 173 20 32 2015-11-17 174 36 10 2017-09-01 175 78 12 2018-06-27 176 57 8 2016-03-22 177 75 11 2017-06-27 178 71 10 2015-08-01 179 48 22 2015-09-29 180 19 16 2016-02-21 4/13/23, 1:32 PM Database Programming - Assignment: Creating & Manipulating a Database | Study.com https://study.com/academy/lesson/database-programming-assignment-creating-manipulating-a-database.html 14/21 181 79 30 2018-08-20 182 70 13 2016-09-16 183 30 6 2017-02-10 184 45 12 2017-10-12 185 30 27 2016-11-23 186 26 3 2016-08-13 187 66 6 2017-01-14 188 47 15 2016-02-10 189 53 30 2018-08-08 190 80 16 2016-03-31 191 70 13 2018-02-03 192 14 25 2016-03-27 193 46 22 2016-01-13 194 30 32 2015-08-06 195 60 14 2016-11-27 196 14 13 2018-05-23 197 71 15 2016-06-22 198 38 21 2015-12-27 199 69