English
Menu

Database Basics

Learn databases with multiple-choice questions: tables and keys in relational databases, SQL searches, aggregation, joins and subqueries, normalization and E-R diagrams, transactions and the ACID properties, concurrency problems and isolation levels, locks and deadlocks, recovery from failures, indexes and NoSQL.

Practice 10 questionsRandom questions from this topic · about 5 minutes Practice this topicAll questions in this topic, in random order Weak questionsRetry only the questions you last got wrong

SQL practice →

Database Basics: key points

From an online shop's inventory to bank accounts, most information systems entrust their data to a database. A relational database holds data as tables, links tables with primary keys and foreign keys, and retrieves and updates the data it needs with a language called SQL.

This topic covers searching with SQL SELECT statements, aggregation with GROUP BY and HAVING, inner and outer joins, and subqueries, counting the results on small example tables. It also covers normalization and E-R diagrams for splitting tables correctly, the ACID properties of transactions, concurrency problems such as lost updates and dirty reads and the isolation levels that prevent them, locks and deadlocks, recovery from failures using logs, how indexes work, and the ideas behind NoSQL, which helps with the database section of IT certification exams such as Japan's Fundamental and Applied Information Technology Engineer Examinations.

All Database Basics questions (40)
  1. In relational database terminology, which term corresponds to one row of a table (relation)?
  2. In a relational database table, which condition must the column (or set of columns) designated as the primary key satisfy?
  3. In an employee table (emp_no, email, name, dept), both emp_no and email are different for every employee and never NULL. Name and dept may be the same as those of other employees. Which lists all the candidate keys of this table?
  4. An enrollment table (student_no, course_no, grade) records which student took which course and with what grade. One student takes several courses, and one course is taken by several students. If the same student never registers for the same course twice, which is an appropriate primary key for this table?
  5. The primary key of a customer table (cust_id, name) is cust_id, and cust_id in an orders table (order_id, cust_id) is defined as a foreign key referring to cust_id in the customer table. The customer table contains only the rows with cust_id 'C1' and 'C2'. What happens when INSERT INTO orders VALUES ('O9', 'C5') is executed in this state?
  6. SQL statements are divided into the data definition language (DDL), data manipulation language (DML) and data control language (DCL). Which one is classified as DDL?
  7. A product table has five rows of (name, price): (pen, 120), (notebook, 250), (eraser, 80), (file, 300), (ruler, 150). Which shows, in order, the rows returned by SELECT name FROM product WHERE price >= 150 ORDER BY price DESC?
  8. The dept column of an employee table contains, from the top row down, Sales, Dev, Sales, Admin, Dev, Sales (six rows in total). How many rows does SELECT DISTINCT dept FROM employee return?
  9. What is the result of executing DELETE FROM employee in SQL without a WHERE clause?
  10. The score column of an exam table contains four values: 80, NULL, 70, 90 (NULL means the person did not take the exam). What is the result of SELECT AVG(score) FROM exam?
  11. The score column of a result table contains six values: 50, NULL, 70, NULL, 90, 30. Which shows, in order, the result of SELECT COUNT(*), COUNT(score), AVG(score) FROM result?
  12. From an employee table (emp_no, dept, salary), you want the departments whose average salary is 300 or more, together with that average. Which SQL statement is correct?
  13. A sales table (store, amount) has seven rows: (A, 500), (A, 700), (B, 300), (B, 400), (C, 1200), (D, 200), (D, 900). How many rows does SELECT store, SUM(amount) FROM sales GROUP BY store HAVING SUM(amount) >= 1000 return?
  14. An employee table (name, dept, salary) has eight rows, with salaries by department as follows: Sales 320, 280, 350; Dev 400, 310; Admin 260, 300; Planning 330. How many rows does SELECT dept, COUNT(*) FROM employee WHERE salary >= 300 GROUP BY dept HAVING COUNT(*) >= 2 return?
  15. An employee table (emp_no, name, dept_id) has four rows whose dept_id values are, in order, 10, 20, 10 and NULL. A department table (dept_id, dept_name) has three rows with dept_id 10, 20 and 30. How many rows does SELECT employee.name, department.dept_name FROM employee INNER JOIN department ON employee.dept_id = department.dept_id return?
  16. An employee table (emp_no, name, dept_id) has five rows whose dept_id values are, in order, 10, 20, 10, NULL and NULL. A department table (dept_id, dept_name) has three rows with dept_id 10, 20 and 30. How many rows does SELECT department.dept_name, employee.name FROM department LEFT OUTER JOIN employee ON department.dept_id = employee.dept_id return?
  17. Column x of table R has four rows with values 1, 1, 2, 3, and column x of table S has four rows with values 1, 1, 2, 4. How many rows does SELECT * FROM R INNER JOIN S ON R.x = S.x return?
  18. A customer table (cust_id, name) has four rows: (C1, Aoki), (C2, Inoue), (C3, Ueda), (C4, Eto). An orders table (order_id, cust_id, amount) has five rows: (O1, C1, 3000), (O2, C2, 8000), (O3, C1, 6000), (O4, C3, 2000), (O5, C2, 9000). How many rows does SELECT name FROM customer WHERE cust_id IN (SELECT cust_id FROM orders WHERE amount > 5000) return?
  19. From a customer table (cust_id, name) and an orders table (order_id, cust_id, amount), you want the names of customers who have never placed an order. Which SQL statement is correct?
  20. Which correctly describes a database view in a relational database?
  21. What is the main purpose of normalization of tables in a relational database?
  22. The primary key of an order table (order_no, item_no, qty, cust_no, cust_name) is {order_no, item_no}, and its functional dependencies are {order_no, item_no} → qty, order_no → cust_no and cust_no → cust_name. Which is the correct result of normalizing this table up to third normal form (3NF)?
  23. The primary key of an employee table (emp_no, name, dept_code, dept_name) is emp_no, and every column holds a single value. The functional dependencies are emp_no → name, emp_no → dept_code and dept_code → dept_name. Which correctly states the normal form of this table?
  24. In a database used mainly for reading and aggregation, normalized tables are sometimes deliberately merged into one so that the same data is held in duplicate (denormalization). Which correctly pairs an advantage and a disadvantage of this design?
  25. In an E-R diagram, there is a many-to-many relationship between students and courses: one student takes several courses, and one course is taken by several students. Which is an appropriate way to implement this relationship with relational database tables?
  26. In an account table, a transaction is started, and an UPDATE that subtracts 300 from account A's balance and an UPDATE that adds 300 to account B's balance are executed. Then ROLLBACK is executed instead of COMMIT. What happens to the account table?
  27. Among the ACID properties a transaction should have, which is the property that a transfer debiting account A and crediting account B is either carried out completely or not at all, even if a failure occurs partway through?
  28. The stock count of a product in a stock table is 10. Transaction T1 reads the stock count and writes back the value minus 3, and T2 reads the stock count and writes back the value minus 5. If they run without locks in the order T1 reads, T2 reads, T1 writes, T2 writes, what is the final stock count?
  29. Among the problems that occur when transactions run concurrently, which correctly describes a dirty read?
  30. Transaction T1 ran SELECT COUNT(*) FROM orders WHERE amount >= 1000 and got 8. Immediately afterwards, T2 inserted an order row with an amount of 2000 and committed. When T1 ran the same SELECT statement again, it got 9. What is this phenomenon?
  31. Among the isolation levels defined by the SQL standard, which correctly lists the phenomena that can occur under READ COMMITTED?
  32. Transaction T1 holds a shared lock on a row and is reading it. T2 requests a shared lock and T3 requests an exclusive lock on the same row. Which is correct for the period until T1 releases its lock?
  33. Which correctly describes two-phase locking (2PL), used for concurrency control of transactions?
  34. Transaction T1 took an exclusive lock on row A and then requested a lock on row B, while T2 took an exclusive lock on row B and then requested a lock on row A, so neither can proceed. What does a DBMS typically do when it detects this situation?
  35. When updating data, many DBMSs record the changes in a log (journal) and write that log to nonvolatile storage before the database itself (WAL: write-ahead logging). What is the main reason for this approach?
  36. There are transactions T1 to T5, and one checkpoint was taken before a failure occurred. T1 started and committed before the checkpoint. T2 and T3 started before the checkpoint; T2 committed after the checkpoint but before the failure, and T3 had not committed at the time of the failure. T4 and T5 started after the checkpoint; T4 committed before the failure, and T5 had not committed. In recovery after the failure, which transactions must be rolled forward using the log?
  37. A B+ tree database index was created on the member_no column of a member table with 1 million rows. Which correctly pairs the effect that can generally be expected with its cost?
  38. For a member table with 1 million rows, consider creating a B+ tree index on each of the columns member_no (all different), prefecture (47 values) and gender (2 values, split roughly in half). When searching by specifying a value for one column in the WHERE clause, on which column is an index likely to be least effective?
  39. Among the databases called NoSQL, which is generally a correct description of the characteristics of a key-value store?
  40. Which is an appropriate description of the CAP theorem for a distributed database that keeps copies of data on multiple servers?

Books on Database Basics Ad

As an Amazon Associate I earn from qualifying purchases.

Other topics in Computer systems