ITD 256 Quiz 2 Make-Up Name: Each question is worth 5 points. Total possible points is 100. 1. Given a relation called COURSE where each row in the table describes a course available for students, which SQL statement below will return a count of courses in the COURSE tables? 2. Which clause, or clauses, are always required when executing an SQL statement that will return either a result set or NULL? a. b. c. d. e. select where having from both a and d 3. Which keyword is used in SQL expressions to select partial values? HINT: It can be used with wildcard characters, which represent unknown characters in a pattern. 4. Write an SQL query that creates a database container for a database named itd_256. 5. Given a table named COURSE, write an SQL query that returns all of the data in the COURSE table (i.e., all of the fields and all records). 6. Given a table called COURSE that contains a numeric field called Fee, write an SQL query that returns all fields and all records in the table where Fee is greater than 100. Page 1 of 6 ITD 256 Quiz 2 Make-Up 7. Given a table called CUSTOMER that contains a varchar field called CustomerFirstName, write an SQL query that returns all of the data in the CUSTOMER table and sorts the result set by field CustomerFirstName in ascending order. 8. Given a table named COURSE with fields CourseName (varChar), CourseDate (Date), and Fee (numeric), write an SQL query that returns the fields CourseName, Fee, and CourseDate (in that order) for all courses with CourseName equal to Adv Pastels. 9. Given a table named COURSE with fields CourseName (varChar), CourseDate (Date), and Fee (numeric), write an SQL query that returns the fields CourseName, Fee, and CourseDate (in that order) for all courses that have a CourseName ending in Pastels. 10. Given a table named COURSE with fields CourseName (varChar), CourseDate (Date), and Fee (numeric), write an SQL query that returns the fields CourseName and Fee (in that order) for all courses that have a CourseName ending in Pastels and a Fee of more than $150. Page 2 of 6 ITD 256 Quiz 2 Make-Up 11. Given the tables below, write a query that returns the following fields CustomerNumber, CourseNumber, CourseName, Fee, and AmountPaid. Use the appropriate join to ensure that only courses that have at least one enrolled customer are returned in the result set. Remember to qualify any conflicting field names with the associated table name if necessary. (8.5 points) COURSE CourseNumber CourseName Fee ENROLLMEN T CourseNumber CustomerNumber AmountPaid 12. Given the tables below, write a query that returns fields CustomerNumber, CourseNumber, CourseName, Fee, and AmountPaid. Use the appropriate join to ensure that all courses are returned in the result set—including those that have no customers enrolled. Remember to qualify any conflicting field names with the associated table name if necessary. (9 points) 13. COURSE CourseNumber CourseName Fee ENROLLMENT CourseNumber CustomerNumber AmountPaid The ______________ keyword is used in conjunction with wildcard characters to match partial values. To match a single character, a ________________ symbol is used. To match zero, one, or more characters, a ___________________ symbol is used. Page 3 of 6 ITD 256 Quiz 2 Make-Up 14. When joining tables, when it is necessary to qualify the field name with the table name? Use the following tables to answer questions 15 through 20. SERVICE ServiceID Description PET_SERVICE ServiceID PET PetID PetID PetName CUSTOMER CustID LName Fee AmountPai d CustID FName Phone 15. Write a query that returns the total amount paid for pet services by all customers. 16. Write a query that returns the pet’s name and the associated customer’s last name, first name, and phone number. The query should return all customers regardless of whether they have a pet or not. Page 4 of 6 ITD 256 Quiz 2 Make-Up 17. You are writing a query that joins the SERVICE table to PET_SERVICE table and you want the query to return all of the records in the SERVICE table regardless of whether that service has ever been performed (i.e., there is a corresponding record in the PET_SERVICE table). What type of join should you use? 18. Write a query that returns all of the fields in the CUSTOMER table and sorts them by last name followed by first name in ascending order. 19. Write a query that returns the last name, first name, and phone number of all customers who live in the 703 area code. 20. Write a query that returns the description and fee of all services that have a fee in excess of $50. Page 5 of 6
Purchase answer to see full attachment