Unit 4: Database Joins - Practice Quiz

1 What is the main purpose of a database join?

2 Which part of a join usually specifies how two tables are related?

3 Which columns commonly connect two related tables?

4 Why are joins useful in a normalized database?

5 Which join returns only rows that have matching values in both tables?

6 Which join returns every row from the left table and matching rows from the right table?

7 Which join returns every row from the right table and matching rows from the left table?

8 What is a self join?

9 Why are aliases commonly used in a self join?

10 What happens to an unmatched row when an INNER JOIN is used?

11 In a LEFT JOIN, what usually appears for right-table columns when no match exists?

12 In A RIGHT JOIN B, which table's rows are all preserved?

13 What is a database view?

14 A standard database view is often described as which type of table?

15 Which SQL statement is used to define a new view?

16 How can a view help improve database security?

17 What is a database transaction?

18 Which command permanently saves the changes made by a transaction?

19 Which command cancels uncommitted changes in a transaction?

20 Which transaction property means that all operations succeed together or fail together?

21 A report must display each order together with the name of the customer who placed it. Orders and customers are stored in separate tables. Why is a join needed?

22 A query lists Employees and Departments in the FROM clause but provides no join condition. If there are 12 employees and 5 departments, how many rows will the query return?

23 The Customers table contains customer IDs 1, 2, and 3. The Orders table contains four rows with customer IDs 1, 1, 3, and 4. How many rows result from an inner join on customer ID?

24 Using the same data—customers 1, 2, and 3, and orders belonging to customers 1, 1, 3, and 4—how many rows result from Customers LEFT JOIN Orders on customer ID?

25 Which query form is logically equivalent to A RIGHT JOIN B ON A.id = B.a_id?

26 An Employees table has columns employee_id, employee_name, and manager_id, where manager_id references another employee. Which technique should be used to display each employee with the employee's manager?

27 Consider the query: SELECT c.id, o.id FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id WHERE o.status = 'PAID';. What happens to customers who have no orders?

28 Both Students and Enrollments identify a student using the composite key (student_id, campus_id). What is the main risk of joining the tables only on student_id?

29 A report uses Departments LEFT JOIN Employees and must show zero for departments with no employees. Which aggregate expression should count employees correctly?

30 A company wants analysts to see employee names and departments but not salaries. Which database design best supports this requirement?

31 Which view is most likely to be directly updatable in a typical relational database system?

32 A view is defined as CREATE VIEW ActiveAccounts AS SELECT * FROM Accounts WHERE status = 'ACTIVE' WITH CHECK OPTION;. What does WITH CHECK OPTION enforce?

33 An ordinary view displays product prices from a base table. A price is updated and committed in the base table. What will a later query of the view normally display?

34 A view joins Orders with Customers to present customer names beside order totals. What is a key maintenance benefit of this view?

35 A bank transfer subtracts money from one account and adds it to another. Which transaction structure best protects data if the second update fails?

36 A transaction inserts a customer, creates savepoint S1, inserts two orders, and then executes ROLLBACK TO S1. Which changes remain pending in the transaction?

37 Transaction T1 updates a row but has not committed. Transaction T2 reads that updated value. Which concurrency anomaly has occurred?

38 After a transaction successfully executes COMMIT, what is the expected effect of a later ROLLBACK issued outside that completed transaction?

39 Two transactions read the same inventory value, calculate different new values, and then update the row. The later update overwrites the earlier one. Which technique most directly prevents this lost-update problem?

40 Transaction T1 locks row A and waits for row B, while T2 locks row B and waits for row A. What should a database system typically do to resolve this situation?

41 A relation Orders(order_id, customer_id, order_date) contains 10 million rows, while Customers(customer_id, region) contains 500,000 rows. A query returns orders placed by customers in the EU region. Which condition most directly determines whether an equijoin can produce a correct result without duplicating an order?

42 Consider Employee(emp_id, dept_id) and Department(dept_id, manager_id). A developer joins them using Employee.emp_id = Department.dept_id. The query returns an empty result even though both tables contain rows. What is the primary relational error?

43 Relations A and B each contain duplicate values for the join attribute k. If one value of k appears 3 times in A and 4 times in B, how many rows does an equijoin contribute for that value, assuming no additional predicates?

44 A query joins Sales to Products and then applies WHERE Products.discontinued = false. The query is intended to retain all sales, including sales whose product record is missing. Which rewrite preserves that requirement?

45 Given A(id) containing {1, 2} and B(id) containing {2, 3}, which set of identifiers is returned by A LEFT JOIN B ON A.id = B.id when selecting A.id?

46 A query uses LEFT JOIN Payments p ON o.order_id = p.order_id and then adds WHERE p.amount > 0. What is the effective behavior for orders without payments?

47 In a self-join of Employee e to Employee m, which condition correctly identifies employees whose manager earns more than the employee?

48 A table Employee(emp_id, salary) is self-joined using e1.salary < e2.salary to find salary pairs. If salaries are not unique, what issue occurs?

49 A right join is written as A RIGHT JOIN B ON A.key = B.key. Which equivalent formulation preserves the result, including unmatched rows?

50 A left join between Customers and Orders is intended to find customers with no orders. Which predicate is correct when Orders.order_id is declared NOT NULL?

51 Suppose Orders has one row per order and OrderItems has multiple rows per order. Which query correctly returns orders whose total item quantity exceeds 10 without unintentionally multiplying order-level output rows?

52 A view is defined as CREATE VIEW ActiveUsers AS SELECT * FROM Users WHERE status = 'A'. A new column is later added to Users. Which statement is generally safest when relying on a stable view interface?

53 A view joins Customers to Orders, where one customer may have many orders. An update through the view attempts to change only Customers.email. Why might the view be non-updatable even though the target column belongs to one base table?

54 A security view exposes rows from Employee where department_id = 10. An update through the view changes department_id to 20. Which view property is needed to prevent the row from escaping the view after the update?

55 A reporting view computes SUM(amount) grouped by customer_id. Why is direct deletion of one displayed row generally ambiguous?

56 A transaction transfers $100 from account A to account B. The debit succeeds, but the system fails before the credit is committed. Which ACID property requires the debit to be undone after recovery?

57 Transaction T1 reads a balance of 500. T2 commits an update changing it to 700. T1 reads the same row again and sees 700 within its still-open transaction. Which anomaly occurred?

58 Transaction T1 executes SELECT COUNT(*) FROM Orders WHERE total > 100 twice. T2 inserts and commits a qualifying order between those statements, so T1's count increases. What anomaly is this?

59 A transaction executes SAVEPOINT s1, performs updates, executes ROLLBACK TO s1, and then commits. What is the expected effect?

60 Two concurrent transactions each read that fewer than 10 doctors are on call, then each inserts a row making one additional doctor on call. The final state violates the maximum of 10, despite both transactions reading consistent data. Which control is most directly required?