AIM: To create and query a database using SQL 80- THOMAS ELLIOT OLIVIA LUCY SQL - Who is it? DANIEL PHOEBE KATIE EMILY 1 INTO University Module INU0121 - Principles

of Computing B RYAN 8 HARRY REBECCA EMMA BEN Majin MATTHEW SARAH THOMAS Helen's Groups University INTO Module INU0121 - Principles of Computing This is created from a game called "Who is it?". There are 2 players and they each select a person in secret. They take it in turns to ask a question to the other player to guess the identity of their selected person. The answer must only be a yes or no. For example, a player may ask - Does the person have blonde hair? Or, Does the person have big eyes? In the end the first player to guess the identity of the other player's selected person is the winner. You may play this game with your neighbour to help you identify columns to add to your database, if you wish... YOU MAY NEED TO REFER TO THE LECTURE SLIDES TO HELP WITH THIS WORKSHEET WRITE THE SQL STATEMENTS FOR THE FOLLOWING QUERIES 1) Create a database and populate it with data from all of these people. You should include name, hair colour, eye size and whatever else you think you may need. Also include IDs starting with 1 from the top left of the pictures (Thomas = 1, Daniel = 2 etc). Finally include the persons age (look at the picture and estimate it) and include the number of times they exercise each week (Estimate this. Do they look healthy and fit?). 2) Create a view of the database (use SELECT *). 3) Create list of names of all people with blonde hair. 4) Delete Thomas with blonde hair from the database (you will need his ID number). 5) Update Ryan to have big eyes (You can't see behind his glasses - but he does have big eyes). 6) Create a list of names and show this in order of eye size (small to large), also show hair colour in your list. Reduce this list to include only people that wear glasses. 7) 8) What is the average age of people who have brown hair? 9) Write a query that will select Katie (You can not use her name or ID) 10) What is the sum of the ages for people with each hair colour? 11) Create a list of people names who exercise more than 3 times a week and are over 21 years old. 12) Create a list of people names who exercise more than 3 times a week or are over 21 years old. 13) Alter the above two queries and tell me whether AND has precedence over OR or is it the other way round? The film industry is recruiting people for new movies and is looking for a particular type of person. Create another table which has ID, hair colour and years of acting experience (give people a range of experience from 1 year to 30 years). 14) Write a query to show all the information from the people table who have over 10 years of acting experience. 15) Show the average age of people with each hair colour only if it is greater than 85. 2 Helen's Groups