ISDD+Searching+and+Sorting

media type="custom" key="28059969"


 * Questions**


 * Cars Database**

A car dealer creates a database to store all of his cars that are for sale. Answer the questions below on the cars database.

__Search Query Examples__ Example 1: find all the Ford cars Answer: MAKE = "Ford"

Example 2: find all the the Green Mondeos Answer: MODEL = "Mondeo" AND Colour = "Green"

1. What would be the query for: Find all of the red cars 2. What would be the query for: Find all of the green Fiestas 3. What would you expect the result of a search for MODEL = "Ford" AND PRICE = 10999 to be? 4. What would be the query for: Find all of the cars priced at more than 6000 5. Andy is buying a first car for his daughter Jade. His budget is 1500. What search query would you use? 6. Lee is looking for a car and has between 3000 and 4000 to spend. What search query would you use? 7. What would you expect the result of the query PRICE < 5000 AND COLOUR = "Red" to be?

8. Carry out the search queries from questions 1-7 using the Cars database

9. The cars database has been sorted on a single field. (a) What is the primary sort field? (b) Say whether the table has been sorted in ascending or descending order

10. Say how the cars database has been sorted in the table below (Hint: this is a complex sort)

11. Sort the actual cars database using the complex sort from question 9


 * Oscars Database**

Download the Best Picture Oscars database which stores details of the films which won an Oscar for the Best Picture from 1980 onwards. Use the database to answer the following questions:

1. Which film won the Oscar for best film in 2005? 2. Which film won the Oscar for best film in 1987? 3. Which film won the Oscar for best film in 1993? 4. Which film won the 68th best film Oscar? 5. Which film won the 81st best film Oscar?
 * Searching **

6. Which film won the 64th best film Oscar? 7. Which films in the database are of the Mystery & Suspense genre? 8. Which films in the database are of the Comedy genre? 9. Which films in the database are of the Musical genre? 10. How many films in the database are of the Drama genre?

11. Which Oscar winners were produced by DreamWorks? 12. Which Oscar winners were produced by Miramax? 13. Which film was directed by Kathryn Bigelow? 14. Which film was directed by Oliver Stone? 15. How many best film Oscar winners have been directed by Clint Eastwood?

16. Which best film Oscar winners has Russell Crowe appeard in? 17. Which best film Oscar winners has Willem Dafoe appeard in? 18. Which best film Oscar winners has Woody Harrelson appeard in? 19. Which best film Oscar winners has Leonardo DiCaprio appeard in? 20. Which film has a rating of 83?

21. Which films have a rating of 91? 22. Which films have a rating of more than 95? (Hint: Use >) 23. Which films have a rating of less than 75?

24. Sort the database by Genre. Describe what happens. 25. Sort the database by Rating (ascending). Describe what happens. 26. Sort the database by Rating (descending). Describe what happens. 27. Sort the database alphabetically by film name. 28. Sort the database by Rating (ascending), and then on a second level by year. Describe what happens when films have the same rating (Hint: look at films with a rating of 88
 * Sorting **

29. Research and add the Best Picture winners from 2013 onwards
 * Adding records **


 * Hotel Room Database**

A hotel keeps a database of all of their rooms in order to manage bookings 1. What would be the query for: Find all booked rooms 2. What would be the query for: Find the room booked by Singh 3. What would be the query for: Find all booked rooms that have a bath 4. What would be the query for: Find all of the double rooms with no TV 5. What would you expect the result of a search for TYPE = "Twin" AND OTHER = "Bath" to be? 6. What would you expect the result of a search for TYPE = "Single" AND SURNAME = "Little" to be?

7. Say how the hotel database has been sorted in the table below (Hint: Complex Sort):


 * Holiday Database**

A travel agent uses a database to record their different holidays. An extract of the database is shown below. Show how the extract of the database would look if it was sorted as follows: Primary sort on DESTINATION (Ascending) Secondary sort on PRICE (Descending)