- A database language enables the user to perform complex queries designed to transform the raw data into useful information.
True
2. A(n) asterisk character is a symbol that can be used as a general substitute for other characters or commands.
False
3. The COUNT function is designed to tally the number of non-null “values” of an attribute and is often used in conjunction with the DISTINCT clause.
True
4. The SQL COUNT function gives the number of rows containing non-null values for a given column.
True
5. Rows can be grouped into smaller collections quickly and easily using the COUNT clause within the SELECT statement.
False
6. All SQL commands must be issued on a single line.
False
7. Although SQL commands can be grouped together on a single line, complex command sequences are best shown on separate lines, with space between the SQL command and the command’s components.
True
8. You can select partial table contents by naming the desired fields and by placing restrictions on the rows to be included in the output.
True
9. Oracle users can use the Access QBE (query by example) query generator.
False
10. Comparison operators cannot be used to place restrictions on character-based attributes.
False
11. When building your clauses which of the following is recommended to address first:
FROM
12. In practice which of the following is more common to find?
Cryptic, confusing and nonstandardized attribute names.
13. Which of the following is a valid reason to not include the DISTINCT clause if it is not necessary?
Improve the performance
14. What command should be used to save permanently all changes made to a database.
COMMIT
15. Which of the following queries correctly uses an alias for the MRENT field?
SELECT ID, MRENT AS “monthly rent” FROM TENANTS
16. Which of the following is true about the result of the following query?
It has three columns of the CLIENT table.
17. Consider the following table
Table name: TENANT
| ID | NAME | RENT |
| 18 | Alex | 219 |
| 31 | Rose | 250 |
| 51 | Alex | 300 |
| 77 | Mary | 280 |
| 81 | Rose | 240 |
How many rows are in the following query?
SELECT DISTINCT NAME FROM TENANT.
3
18. Consider the following table
Table name: TENANT
| ID | NAME | RENT |
| 18 | Alex | 219 |
| 31 | Rose | 250 |
| 77 | Alex | 300 |
| 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
19.After requesting a service, a client must pay 30 days after the service is required. In the SERVICE table, there exists the attribute DATE that holds the day a service was required, but there is no attribute for the due date. Which of the following queries shows the date a service was required and when the payment is due as DUE_DATE.
SELECT SID, DATE, DUE+30 AS DUE DATE FROM SERVICE
20. The table STUDENT has the attributes STUDENT_ID, NAME, SCHOOL_ID, and ADDR. The table SCHOOL has the attributes SCHOOL_ID, NAME, and STATE_CODE. Assume that there is a 1:N relation between SCHOOL and STUDENT. Which of the following creates a table that shows the STUDENT attributes and the corresponding SCHOOL attributes?
SELECT * SCHOOL_ID FROM STUDENT JOIN SCHOOL USING(SCHOOL_ID)
21. The table STUDENT has the attributes STUDENT_ID, NAME, S_ID, and ADDR. The table SCHOOL has the attributes SCHOOL_ID, NAME, and STATE_CODE. Assume that there is a 1:N relation between SCHOOL and STUDENT where the common attributes are S_ID, and SCHOOL_ID. Which of the following creates a table that shows the STUDENT attributes and the corresponding SCHOOL attributes?
SELECT * FROM STUDENT JOIN SCHOOL ON STUDENT.S_ID = SCHOOL.SCHOOL_ID
22. Consider the following tables
Name: CLIENTS
Primary Key: CLIENT_ID
| 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?
SELECT *
FROM CLIENTS RIGHT JOIN SALES ON CLIENTS.CLIENT_ID = SALES.CLIENT_ID
3
23. Which of the following better describes the result of SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id2?
Rows match the join condition and rows in table1 with unmatched id on table2.
24. When using a cross join between a table with 8 rows and another with 5 rows how many rows are in the result?
40
25. Which of the following is equivalent to WHERE attribute BETWEEN 10 AND 1000?
WHERE attribute >= 10 AND attribute <= 1000
26. Which comparison operator indicates a value is not equal?
<>
27. Which of the following is a match for “%c%s” when using the LIKE operator?
“acts”
28. Which of the following are equivalent to WHERE attribute IN (123, 200)?
WHERE attribute = 123 OR attribute = 200
29. Which of the following is equivalent to WHERE attribute NOT BETWEEN 100 AND 1000?
WHERE attribute < 100 OR attribute > 1000
30. Which of the following is equivalent to WHERE NOT(attribute1 > 100 AND attribute2 = 100)?
WHERE attribute1 <= 100 AND attribute2 <> 100
31. The special operator used to check whether a subquery returns any rows is _____.
EXISTS
32. Consider the following tables:
table1
| vcode | name |
| 7 | Alex |
| 8 | Tony |
| 9 | Charles |
| 11 | Mary |
table2
| scode | vcode | total |
| 341 | 7 | 102 |
| 213 | 9 | 59 |
| 312 | 7 | 89 |
| 712 | 3 | 301 |
How many rows has the result of the following query?
SELECT * FROM table1 NATURAL JOIN table2
3
33. Consider the following tables:
| vcode | name |
| 7 | Alex |
| 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
1
34. Consider the following tables:
| vcode | name |
| 7 | Alex |
| 8 | Tony |
| 9 | Charles |
| 11 | Mary |
| 13 | Jhon |
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 LEFT JOIN table2 ON table1.vcode = table2.vcode
5
35. Suppose that a mattress store has a TABLE1 with sales representatives with an attribute vcode as primary key. It also has a TABLE2 with every mattress sold, the table has an attribute vcode to indicate which sales representative made the sale, and a mt_code to indicate which mattress was sold. Which of the following returns all the sales representatives that haven’t closed a sale?
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.vcode = TABLE2.vcode WHERE mt_code IS NULL.
36. Some rows of a User table are shown below:
User
| ucode | name | ranking | rate |
| 7 | Alex | 3 | 0.2 |
| 8 | Tony | 2 | 0.3 |
| 9 | Charles | 1 | 0.8 |
| 11 | Mary | 3 | 0.7 |
Which of the following queries retrieves the rows where the ranking is less than 3 and the rate is greater than 0.4.
SELECT * FROM User WHERE ranking < 3 AND rate > 0.4
37. Some rows of a User table are shown below:
User
| ucode | name | phone | scode |
| 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 IS NULL
38. 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?
SELECT BIN, YEAR FROM VEHICLES WHERE YEAR IN (2010, 2020)
All BINs, and YEARS of vehicles that were bought in 2010, or 2020
39. Below are some rows of the PROVIDERS table for a factory:
| PCODE | NAME | PHONE |
| 10192 | Hans, Inc. | 229 – 3092 |
| 98411 | Tools and More | 293 – 1029 |
| 0489035 | Motors Inc. | 993 – 1821 |
You desire to get all providers that are legal corporations. Legal corporations must include the Inc in their name. Which of the following queries provides the desired result.
SELECT PCODE, NAME FROM PROVIDERS WHERE NAME LIKE ‘%Inc%’
40. Below are some rows of the PROVIDERS table for a factory:
| PCODE | NAME | PHONE |
| 10192 | Hans, Inc. | 231 – 3092 |
| 98411 | Tools and More | 231 – 1029 |
| 0489035 | Motors Inc. | 993 – 1821 |
All providers from the factory zone of the city start with 231. You want to get the providers that are in the factory zone of the city. Which of the following is more appropriate?
SELECT * FROM PROVIDERS WHERE PHONE LIKE ‘231%’
41. Consider the following table:
TABLE1
| val1 | val2 | val3 |
| 3 | 129 | 10 |
| 2 | 391 | 30 |
| 12 | 89 | 30 |
| 5 | 129 | 20 |
| 90 | 12 | 10 |
| 12 | 10 | 20 |
What is the result of the query below?
SELECT * FROM TABLE1 WHERE val2 BETWEEN (100, 400) AND val3 IN (10, 20)
| val1 | val2 | val3 |
| 3 | 129 | 10 |
| 5 | 129 | 20 |
42. 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 COUNT(GPA) FROM STUDENT WHERE GPA > 3.0
43. Consider the STUDENT table given below:
| CODE | NAME | GPA | YEAR |
| 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
44. Consider the STUDENT table given below:
| CODE | NAME | GPA | YEAR |
| 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 |
How many rows are returned from the query below?
SELECT MAX(GPA) FROM STUDENTS GROUP BY YEAR
Assume that if an error occurs 0 rows are returned.
3
45. Consider the STUDENT table given below:
| CODE | NAME | GPA | YEAR |
| 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 |
Which of the following queries retrieve a table with the max GPA of each year sorted by the max GPA value.
SELECT YEAR, MAX(GPA) FROM STUDENT GROUP BY YEAR ORDER BY MAX(GPA)
46. Some rows of the STUDENT table are shown below:
| CODE | NAME | GPA | YEAR |
| 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 |
Which best describes the result of the query below?
SELECT YEAR, AVG(GPA) FROM STUDENT WHERE GPA > 2.0 GROUP BY YEAR
The average GPA of students with a GPA higher than 2.0 each year.
47. Some rows of the STUGRADE table of a school are shown below:
| STU_CODE | CLS_CODE | GRADE | YEAR |
| 291 | 111 | 3.1 | 2010 |
| 938 | 101B | 2.3 | 2011 |
| 931 | M101 | 3.3 | 2040 |
| 291 | M101 | 3.4 | 2018 |
| 190 | 111 | 2.0 | 2021 |
| 931 | 321 | 3.9 | 2003 |
Suppose that STU_CODE is the student code (foreign key) and CLS_CODE is the class code (foreign key). Which of the following computes the average for each year of the student with code 931?
SELECT AVG(GRADE) FROM STUGRADE WHERE STU_CODE = 931 GROUP BY YEAR
48. Some rows of the STUGRADE table of a school are shown below:
| STU_CODE | CLS_CODE | GRADE | YEAR |
| 291 | 111 | 3.1 | 2010 |
| 938 | 101B | 2.3 | 2011 |
| 931 | M101 | 3.3 | 2040 |
| 291 | M101 | 3.4 | 2018 |
| 190 | 111 | 2.0 | 2021 |
| 931 | 321 | 3.9 | 2003 |
You want to show all STU_CODE with average grades higher than 3.0. Which of the following gets the required data?
SELECT STU_CODE, AVG(GRADE) FROM STUGRADE GROUP BY STU_CODE HAVING AVG(GRADE) > 3.0
49. Which of the following differentiates the HAVING clause from the WHERE clause?
WHERE is executed prior to GROUP BY and HAVING after.
50. Some rows of the STUDENT table are given below:
| CODE | NAME | DEPARTMENT | GPA | YEAR |
| 291 | ALEX | PSI | 3.1 | 1992 |
| 938 | MICHELE | PHY | 2.3 | 1992 |
| 931 | JHON | MD | 3.3 | 2001 |
| 182 | JOE | MD | 3.4 | 2002 |
| 190 | REY | PHY | 2.0 | 2001 |
| 330 | RON | PSI | 3.9 | 2001 |
You want to get a list of the maximum GPA of each department with an average GPA of 2.4, but only for students after 2004. Which of the following gets the desired result?
SELECT MAX(GPA) FROM STUDENT WHERE YEAR > 2004 GROUP BY DEPARTMENT HAVING AVG(GPA) > 2.4
51. Some of the rows the GRADES table are below:
| CODE | NAME | GPA | YEAR |
| 938 | ALEX | 3.1 | 2 |
| 931 | MICHELLE | 2.3 | 1 |
| 182 | JOE | 3.1 | 3 |
| 918 | ADELE | 2.9 | 2 |
Which of the following queries returns the name of all the students that achieved the maximum GPA among all the students?
SELECT NAME FROM STUDENT
WHERE GPA = (SELECT MAX(GPA) FROM STUDENT)
52. Some rows of the tables SALESPERSON and SALES are shown below:
SALESPERSON
| VCODE | NAME |
| 7 | Alex |
| 8 | Tony |
| 9 | Charles |
| 11 | Mary |
SALES
| SCODE | VCODE | TOTAL |
| 341 | 9 | 19.50 |
| 213 | 11 | 89.95 |
| 312 | 7 | 31.10 |
Which of the following queries return the salespersons codes with at least one sale greater than the average?
SELECT VCODE FROM SALES
WHERE TOTAL > (SELECT AVG(TOTAL) FROM SALES);
53. Some rows of the tables SALESPERSON and SALES are shown below:
SALESPERSON
| VCODE | NAME |
| 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?
SELECT NAME FROM SALESPERSON
WHERE VCODE IN (SELECT VCODE FROM SALES WHERE TOTAL > 100)
54. Some rows of the table STUDENT are shown below:
| CODE | NAME | DEPARTMENT | GPA | YEAR |
| 291 | ALEX | PSI | 3.1 | 1992 |
| 938 | MICHELE | PHY | 2.3 | 1992 |
| 931 | JHON | MD | 3.3 | 2001 |
| 182 | JOE | MD | 3.4 | 2002 |
| 190 | REY | PHY | 2.0 | 2001 |
| 330 | RON | PSI | 3.9 | 2001 |
You want the name all departments that have a department GPA average of 2004 greater than the global GPA average. Which query gives you the desired result?
SELECT DEPARTMENT
WHERE YEAR = 2004
FROM STUDENT
GROUP BY DEPARTMENT
HAVING AVG(GPA) > (SELECT AVG(GPA) FROM STUDENT);
55. Some rows of the table STUDENT are shown below:
| CODE | NAME | DEPARTMENT | GPA | YEAR |
| 291 | ALEX | PSI | 3.1 | 1992 |
| 938 | MICHELE | PHY | 2.3 | 1992 |
| 931 | JHON | MD | 3.3 | 2001 |
| 182 | JOE | MD | 3.4 | 2002 |
| 190 | REY | PHY | 2.0 | 2001 |
| 330 | RON | PSI | 3.9 | 2001 |
Which of the following queries return the students from the MD department with a GPA greater than the global average in 2004?
SELECT NAME, GPA
FROM STUDENT
GROUP BY DEPARTMENT
HAVING GPA > (SELECT AVG(GPA) FROM STUDENT WHERE YEAR = 2004);
56. Some rows of the table STUDENT are shown below:
| CODE | NAME | DEPARTMENT | GPA | YEAR |
| 291 | ALEX | PSI | 3.1 | 1992 |
| 938 | MICHELE | PHY | 2.3 | 1992 |
| 931 | JHON | MD | 3.3 | 2001 |
| 182 | JOE | MD | 3.4 | 2002 |
| 190 | REY | PHY | 2.0 | 2001 |
| 330 | RON | PSI | 3.9 | 2001 |
You want to get the name of the students that are in a department with a department GPA average larger than 2.5. Which query produces the desired result?
SELECT NAME
FROM STUDENT
WHERE DEPARTMENT IN
(SELECT DEPARTMENT
FROM STUDENT
GROUP BY DEPARTMENT
HAVING AVG(GPA) > 2.5);
57. In subquery terminology, the first query in the SQL statement is known as the _____ query.
outer
58. Consider the following table:
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 |
What is the result of the query below?
SELECT *
FROM table1
WHERE val2 > all(
SELECT val2
FROM table1
WHERE val1 = 10);
| cod1 | val1 | val2 |
| 21 | 11 | 15 |
| 41 | 9 | 11 |
59. The _____ function returns the current system date in MS Access.
DATE()
60. When using the Oracle TO_DATE function, the code _____ represents a three-letter month name.
MON
61. Consider the following table:
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 |
What is the result of the query below?
SELECT *
FROM table1
WHERE val1 IN (SELECT val1
FROM table1
WHERE val2 = (SELECT MAX(val2)
FROM table1));
| cod1 | val1 | val2 |
| 21 | 11 | 15 |
| 11 | 11 | 4 |
62. 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 |
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;
10
63. Consider the following table:
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 |
What is the result of the query below?
SELECT MAX(val2)
FROM table1
WHERE val1 in (SELECT val1
FROM table1
WHERE val2 = (SELECT MIN(val2)
FROM table1))
8
64. Some rows of the tables SALESPERSON and SALES are shown below.
SALESPERSON
| VCODE | NAME |
| 7 | Alex |
| 8 | Tony |
| 9 | Charles |
| 11 | Mary |
SALES
| 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.
65. Consider the following table:
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 |
What is the result of the query below?
SELECT cod1, (SELECT MAX(val2) FROM table1) AS MAX2
FROM table1
WHERE val2 = MAX2 AND val1 = 10
| cod1 | DIFF |
| 10 | 21 |
| 12 | 9 |
| 33 | 13 |
| 8 | 9 |
66. Consider a table EMPLOYEES with an attribute HIRING_DATE that holds the date where an employee was hired as a date data type. Which of the following queries display the employees hired before 2019?
SELECT *
FROM EMPLOYEE
WHERE YEAR(HIRING_DATE) < 2019
67. The table STUDENTS has an attribute BIRTH_DATE that holds the birth date of a student in a date format. Which of the following gives the birthday of a student?
TO_DATE(BIRTH_DATE, ‘%M-%D’)
68. What is the result of SUBSTR(‘19425127’, 2, 4)?
‘9425’
69. Below are some rows of the table INVOICE
| COD | PROV_COD | DATE | TYPE | LOC | TOTAL |
| 2910 | 192 | 2022-03-11 | 90 | TX | 1928 |
| 9301 | 384 | 2022-05-03 | 90 | NY | 2800 |
Overdue invoices are those whose date plus TYPE days have passed. Which of the following shows all invoices with overdue dates?
SELECT *
FROM INVOICE
WHERE DATE+TYPE > CURDATE()
70. While designing a query you determine that the data needs to be preprocessed. Which of the following is most appropriate?
Use a subquery to preprocess.
Other Links:
See other websites for quiz:
Check on QUIZLET