Quiz 3 database

1. 1. SQL query to delete all rows in a table without deleting the table (structure, attributes, and indexes):

A. DELETE FROM table_name;

2. 2. Triggers have two parts: the event and the action.

B. False

3. 3. Find the SQL statement equivalent to: SELECT NAME FROM CUSTOMER WHERE STATE = 'VA';

D. SELECT NAME FROM CUSTOMER WHERE STATE IN ('VA');

4. 4. Which of the following statements is true about views in SQL?

D. We can insert and delete rows in a view

5. 5. Which is called a virtual table in SQL?

C. VIEW

6. 6. Which of the following is the original purpose of SQL?

D. All of the above

7. 7. The command to eliminate a table from a database is:

B. DROP TABLE CUSTOMER;

8. 8. In the given query, which keyword has to be inserted? INSERT INTO employee _____ (1002,Joey,2000);

B. Values

9. 9. What operator tests a column for absence of data?

C. IS NULL Operator

10. Here is query Q on Employees(name, age, salary), with name as the key. Which query or queries are equivalent to Q? | Q: SELECT e1.name FROM Employees e1 WHERE EXISTS ( SELECT e2.salary FROM Employees e2 WHERE e1.salary > e2.salary AND e2.age >= 65 ); Q1: SELECT e1.name FROM Employees e1 WHERE e1.salary > ( SELECT e2.salary FROM Employees e2 WHERE e2.age >= 65 ); Q2: SELECT e1.name FROM Employees e1 WHERE e1.salary > ANY ( SELECT e2.salary FROM Employees e2 WHERE e2.age >= 65 ); Q3: SELECT e1.name FROM Employees e1 WHERE e1.salary > ALL ( SELECT e2.salary FROM Employees e2 WHERE e2.age >= 65 ); | A. Just Q1 | B. Just Q2 | C. Just Q3 | D. Just Q1 and Q2 | E. Just Q1 and Q3

B. Just Q2

11. 11. In relational databases, the natural join of two tables is:

B. Combination of project and selection from Cartesian product

12. 12. The following query is an example of: | SELECT a.EmployeeID FROM Employee a WHERE a.ContactID IN ( SELECT b.ContactID FROM Contact b WHERE b.Title = 'Mrs.' );

B. Noncorrelated Subquery

13. 13. Null values are a problem because they are ambiguous.

A. True

14. 14. Identify the error, if any, in the following SQL statement: | SELECT Name FROM Employees WHERE NOT (Age < 30 AND Department = 'Sales');

D. No error

15. 15. A view always fetches up-to-date data from the table because it runs its query each time it receives a call. Is this statement correct?

A. True

16. 16. Which of the following sets represents table1 LEFT JOIN table2?

A. Table1

17. 17. What result will the following SQL statement produce? | SELECT AVG(standard_price) AS average FROM Product_V;

B. The average Standard_Price of all products in Product_V

18. Assume Employees(name, age, salary) is a table with name as the primary key. How could a tuple-based CHECK require that if someone's age is NULL, then their salary must be less than 5000? | A. CHECK (age = NULL OR salary < 5000) | B. CHECK (age IS NULL OR salary < 5000) | C. CHECK (age <> NULL OR salary < 5000) | D. CHECK (age IS NOT NULL OR salary < 5000) | E. CHECK (age IS NOT NULL AND salary < 5000)

D. CHECK (age IS NOT NULL OR salary < 5000)

19. 19. How do standard SQL aggregate functions (like SUM, AVG, MIN, MAX) handle NULL values in a column?

C. They silently skip NULL values.

20. 20. A user view is:

A. A logical description of some portion of the database.

21. 21. What is a common reason for using a database view?

C. To simplify complex queries and restrict exposed data

22. 22. If you SELECT attributes and use an aggregate function, you must GROUP BY the non-aggregate attributes in the SELECT phrase.

A. True

23. 23. What happens if you use a column in the SELECT statement that is not in the GROUP BY clause?

A. The query fails

24. 24. A view definition is stored in databases.

A. True

25. 25. If a subquery used in a WHERE x > ALL (…) condition returns zero rows, what does the condition evaluate to?

A. TRUE

26. 26. What is the purpose of the SQL GROUP BY clause?

C. To group rows with the same values into summary rows

27. 27. The HAVING clause does which of the following?

A. Acts like a WHERE clause but is used for groups rather than rows.

28. Among the following ORDER BY queries, which are correct? | A. SELECT * FROM Table ORDER BY Column; | B. SELECT * FROM Table ORDER BY Column ASC; | C. SELECT * FROM Table ORDER BY Column DESC; | D. All of the above | E. None of the above

D. All of the above

29. 29. Which SQL statement is used to change the data type of a column in an existing table?

A. ALTER TABLE

30. Which JOIN returns all records from the right table with matching records from the left table, and returns NULL for missing left-side matches? | A. Right Outer JOIN | B. CROSS JOIN | C. Left Outer Join | D. Full Outer JOIN

A. Right Outer JOIN

31. Which of the following operations requires the relations to be union compatible? | A. UNION | B. INTERSECTION | C. DIFFERENCE | D. ALL OF THESE

D. ALL OF THESE

32. 32. The relation movie(title, budget) contains titles and budgets of different movies. Assuming no two movies have the same budget, what does the following SQL query list? | SELECT title FROM movie m WHERE (SELECT COUNT(*) FROM movie n WHERE n.budget > m.budget) < 4;

D. Titles of the four most expensive movies

33. 33. In SQL, which command is used to change a table's storage characteristics?

A. ALTER TABLE

34. 34. The EXISTS construct returns true if the argument subquery is:

B. Not empty

35. 35. A subquery that references one or more columns from the outer query and executes once for each row processed by the outer query is called a:

C. Correlated subquery

36. 36. Arithmetic operations cannot be used inside the SUM function.

B. False

37. 37. A SQL query automatically eliminates duplicates.

B. False

38. 38. Unlike a standard table-level CHECK constraint, a SQL ASSERTION is distinct because:

C. It is a standalone schema element that can restrict data across multiple tables simultaneously.

39. 39. Which of the following is the correct order of a SQL statement?

B. SELECT, WHERE, GROUP BY, HAVING