Quiz 7 - Introduction to SQL

12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024) Quiz: Introduction to SQL Due Nov 1 at 11:59pm Points 15 Questions 15 Time Limit 30 Minutes Allowed Attempts 2 Take the Quiz Again Attempt History LATEST Attempt Time Score Attempt 1 16 minutes 14 out of 15  Correct answers are hidden. Score for this attempt: 14 out of 15 Submitted Oct 31 at 6:39pm This attempt took 16 minutes.  IncorrectQuestion 1 0 / 1 pts Which of the following better describes the result of SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id2? Only the values of table1 with id matching id2 from table2. Rows in table1 with unmatched id on table2. Incorrect. The join condition returns the rows with matching values on both tables. The left outer join also returns the rows in the left table, table1 in this case, with unmatched values in the right table. Rows match the join condition and rows in table1 with unmatched id on table2. Rows match the join condition and rows in table2 with unmatched id2 on table1. https://canvas.csun.edu/courses/154575/quizzes/571404 1/15 12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024)  Question 2 1 / 1 pts In practice which of the following is more common to find? Cryptic, confusing and nonstandardized attribute names. Correct. Real-world databases can be messy, the attribute names of the tables might not follow the best practices and as a result be cryptic, and confusing. Well designed databases. Patients' names and ids. Best practice application.  Question 3 1 / 1 pts Which of the following are equivalent to WHERE attribute IN (123, 200)? WHERE attribute = 123 OR attribute = 200 Correct. The IN operator means that the attribute must be in the list of values given. Thus, the attribute is either equal to 123 or to 200. WHERE attribute = 123 AND attribute = 200 WHERE attribute BETWEEN 123 AND attribute = 200 WHERE attribute <= 123 AND attribute >= 200  Question 4 1 / 1 pts Consider the STUDENT table given below: CODE NAME https://canvas.csun.edu/courses/154575/quizzes/571404 GPA YEAR 2/15 12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024) 291 ALEX 3.1 2 938 MICHELE 2.3 1 931 JHON 3.3 1 182 JOE 3.4 2 190 REY 2.0 2 330 RON 3.9 3 What is the result from the query below? SELECT MAX(GPA) FROM STUDENT WHERE STUDENT.YEAR = 2 3.4 Correct. The MAX(GPA) computes the maximum of the GPA, with the WHERE clause the max is only calculated among the students with YEAR = 2. 3.9 3 3.3  https://canvas.csun.edu/courses/154575/quizzes/571404 3/15 12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024) Question 5 1 / 1 pts Consider the following tables: Name: table1 cod1 val1 val2 10 10 8 12 10 6 21 11 15 33 10 2 41 9 11 8 10 6 14 9 5 11 11 4 https://canvas.csun.edu/courses/154575/quizzes/571404 4/15 12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024) What is the result of the query below? SELECT MIN(cod1) FROM (SELECT val1, MAX(val2) max_val2 FROM table1 GROUP BY val1) max_table JOIN table1 ON max_table.val1 = table1.val1 WHERE val2 = max_val2; 8 33 11 10 Correct. The inner subquery used as part of the join returns a table with the max val2 for each value of val1. From the join the minimum cod1 number from the rows with maximum val2 for each val1 is chosen.  Question 6 1 / 1 pts Some rows of a User table are shown below: User ucode name https://canvas.csun.edu/courses/154575/quizzes/571404 phone scode 5/15 12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024) 7 Alex 847 - 3902 UX 8 Tony 203 - 3902 PX 9 Charles ​ BD 11 Mary 877 - 3333 BD Which of the following queries retrieves the rows where there is no phone? SELECT * FROM User WHERE phone NOT IS NULL SELECT * FROM User WHERE phone IS NULL Correct. When an attribute value is missing its value is NULL so the desired result comes from “phone IS NULL”. SELECT * FROM User WHERE phone = “ ” SELECT * FROM User WHERE phone > 0  Question 7 1 / 1 pts Some rows of the tables SALESPERSON and SALES are shown below: SALESPERSON VCODE https://canvas.csun.edu/courses/154575/quizzes/571404 NAME 6/15 12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024) 7 Alex 8 Tony 9 Charles 11 Mary SALES SCODE VCODE TOTAL 341 9 19.50 213 11 89.95 312 7 31.10 You want the name of all the salespersons that have made at least one sale with a total higher than 100. Which of the following queries gives the desired result? https://canvas.csun.edu/courses/154575/quizzes/571404 7/15 12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024) SELECT NAME FROM SALESPERSON WHERE VCODE IN (SELECT VCODE FROM SALES WHERE TOTAL > 100) Correct. The name can be extracted from the salespersons table by checking which vcodes in the sales table have a total > 100. For that a subquery that returns a list of vcodes can be used. SELECT NAME FROM SALESPERSON WHERE VCODE IN (SELECT * FROM SALES WHERE TOTAL > 100) SELECT NAME FROM SALESPERSON WHERE VCODE = (SELECT VCODE FROM SALES WHERE TOTAL > 100) SELECT NAME FROM SALESPERSON WHERE TOTAL > 100.  Question 8 1 / 1 pts Comparison operators cannot be used to place restrictions on character-based attributes. True False Correct. Comparison operators can be placed on character-based attributes; string base comparison is made from left to right.  Question 9 1 / 1 pts Consider the following tables Name: CLIENTS Primary Key: CLIENT_ID https://canvas.csun.edu/courses/154575/quizzes/571404 8/15 12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024) CLIENT_ID NAME 19283 Jhon 19281 Mary 19272 Elizabeth Name: SALES Primary Key: (SALE_ID, PROD_ID) Foreign Key: PROD_ID, CLIENT_ID SALE_ID PROD_ID CLIENT_ID DATE FGS12301 12930182 19281 2022-07-10 DEF28358 19283104 19272 2022-07-11 GHM3920 19283123 17913 2021-06-22 How many rows produces the following query? https://canvas.csun.edu/courses/154575/quizzes/571404 9/15 12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024) SELECT * FROM CLIENTS RIGHT JOIN SALES ON CLIENTS.CLIENT_ID = SALES.CLIENT_ID 0 3 Correct. The right join includes the rows on the right table, SALES table in this case, that don’t have a match with the left table. 6 2  Question 10 1 / 1 pts If you want to count how many students got a gpa higher than 3.0 from the STUDENT table. Which of the following is more appropriate? Assume that the GPA attribute of the table stores the gpa of each student. SELECT GPA FROM STUDENT WHERE GPA > 3.0 COUNT SELECT * FROM STUDENT WHERE GPA > 3.0 AND COUNT SELECT COUNT(*) FROM STUDENT WHERE GPA > 3.0 SELECT COUNT(GPA) FROM STUDENT WHERE GPA > 3.0 Correct. The count should be done over all attribute thus the wildcard * is used inside the COUNT function. It is not a GPA count since there might be more than one student with the same GPA.  Question 11 1 / 1 pts In a car rental table VEHICLES, the YEAR attribute indicates the YEAR a car was bought, and the BIN attribute a unique identification number for a car. What describes better the result of the query below? https://canvas.csun.edu/courses/154575/quizzes/571404 10/15 12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024) SELECT BIN, YEAR FROM VEHICLES WHERE YEAR IN (2010, 2020) All vehicles' BIN . All YEARS from 2010, or 220 where a vehicle was bought. All vehicles bought from 2010 to 220. All BINs, and YEARS of vehicles that were bought in 2010, or 2020 Correct. The clause YEAR IN (2010, 2020) indicates that the YEAR attribute should be 2010 or 2020. Thus, the query retrieves the BIN, and YEAR of all vehicles bought in 2010, or 2020.  Question 12 1 / 1 pts Which of the following is a valid reason to not include the DISTINCT clause if it is not necessary? Improve the performance. Correct. For performance reasons do not include DISTINCT, if it is not needed. DISTINCT should be used when the query might return duplicate rows that should be suppressed in order to get the desired result. The number of rows is too small. Aggregate functions are used. There are no subqueries used.  Question 13 1 / 1 pts Consider the following table Table name: TENANT | ID | NAME | RENT | | 18 | Alex | 219 | | 31 | Rose | 250 | | 77 | Alex | 300 | https://canvas.csun.edu/courses/154575/quizzes/571404 11/15 12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024) | 41 | Mary | 280 | | 81 | Rose | 240 | Which query returns the following? | ID | NAME | RENT | | 18 | Alex | 219 | | 81 | Rose | 240 | | 31 | Rose | 250 | | 41 | Mary | 280 | | 77 | Alex | 300 | SELECT * FROM TENANT ORDER BY RENT Correct. The * character can be used as a wildcard to choose all columns, and the order by command should be used at the end of the query. SELECT * ORDER BY RENT FROM TENANT SELECT ORDER BY RENT FROM TENANT * SELECT RENT FROM TENANT ORDER BY RENT  Question 14 1 / 1 pts Consider the following tables: vcode name 7 Alex https://canvas.csun.edu/courses/154575/quizzes/571404 12/15 12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024) 8 Tony 9 Charles 11 Mary table2 scode vcode rcode 341 9 7 213 11 4 312 7 8 How many rows has the result of the following query? SELECT * FROM table1 JOIN table2 ON table1.vcode = table2.rcode 3 https://canvas.csun.edu/courses/154575/quizzes/571404 13/15 12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024) 0 2 1 Correct. The join compares the value of vcode on table1 with those of rcode in table2. So the rows with scode 321, and 312 are returned.  Question 15 1 / 1 pts Some rows of the tables SALESPERSON and SALES are shown below. SALESPERSON VCODE NAME 7 Alex 8 Tony 9 Charles 11 Mary SALES https://canvas.csun.edu/courses/154575/quizzes/571404 14/15 12/14/24, 2:00 AM Quiz: Introduction to SQL: COMP 440 DATABASE DESIGN - (17042-FA2024) SCODE VCODE TOTAL 341 9 19.50 213 11 89.95 312 7 31.10 Which of the following best describes the result of the following query? SELECT NAME FROM SALESPERSON WHERE VCODE IN (SELECT VCODE FROM SALES WHERE TOTAL = (SELECT MAX(TOTAL) FROM SALES)) The name of the salespersons with a sale equal to the maximum TOTAL. Correct. The inner subquery returns the VCODEs that have a sale made with a TOTAL equal to the largest total in the table SALES. The outer query returns the name of the salespersons that have at least one sale with the highest value for TOTAL. The name of the salespersons with more sales made. The name of the salesperson with the largest TOTAL accumulated. The name of the salesperson with the highest VCODE Quiz Score: 14 out of 15 https://canvas.csun.edu/courses/154575/quizzes/571404 15/15