Back to Subject

Unit 2: Relational query language - Practice Quiz

INT306 — Database Management Systems 60 Questions
0 Correct 0 Wrong 60 Left
0/60

1 What does DDL stand for in the context of database management?

introduction to data definition language Easy
A. Data Definition Language
B. Detailed Data Language
C. Data Derivation Language
D. Dynamic Data Language

2 Which SQL command is used to build a new table in a database?

introduction to data definition language Easy
A. CREATE TABLE
B. GENERATE TABLE
C. BUILD TABLE
D. MAKE TABLE

3 Which DDL command is used to completely remove a table and its structure from the database?

introduction to data definition language Easy
A. TRUNCATE TABLE
B. REMOVE TABLE
C. DROP TABLE
D. DELETE TABLE

4 Which SQL command is used to add new rows of data into a table?

data manipulation Easy
A. ADD
B. APPEND
C. UPDATE
D. INSERT

5 Which SQL command is used to retrieve data from a database?

data manipulation Easy
A. SELECT
B. EXTRACT
C. FETCH
D. PULL

6 Which command is used to modify existing records in a table?

data manipulation Easy
A. UPDATE
B. CHANGE
C. ALTER
D. MODIFY

7 Which command deletes rows from a table but keeps the table structure intact?

data manipulation Easy
A. DELETE
B. ERASE
C. DROP
D. REMOVE

8 Which Data Control Language (DCL) command is used to give users access privileges to a database?

data control and transaction control language Easy
A. PERMIT
B. ALLOW
C. GRANT
D. ASSIGN

9 Which command is used to permanently save any transaction into the database?

data control and transaction control language Easy
A. COMMIT
B. STORE
C. ROLLBACK
D. SAVE

10 Which TCL command is used to undo transactions that have not yet been saved?

data control and transaction control language Easy
A. CANCEL
B. REVERT
C. UNDO
D. ROLLBACK

11 Which constraint ensures that all values in a column are different from one another?

integrity constraints Easy
A. UNIQUE
B. NOT NULL
C. CHECK
D. DEFAULT

12 Which constraint prevents a column from accepting a NULL value?

integrity constraints Easy
A. NO NULL
B. MANDATORY
C. REQUIRED
D. NOT NULL

13 Which constraint is used to limit the value range that can be placed in a column?

integrity constraints Easy
A. CHECK
B. RANGE
C. CONDITION
D. LIMIT

14 Which key uniquely identifies each record in a database table?

database keys Easy
A. Composite Key
B. Foreign Key
C. Primary Key
D. Alternate Key

15 Which key is used to establish and enforce a link between the data in two tables?

database keys Easy
A. Candidate Key
B. Super Key
C. Foreign Key
D. Primary Key

16 How many primary keys can a single standard relational table have?

database keys Easy
A. Unlimited
B. Two
C. Zero
D. Exactly one

17 What is a candidate key that is NOT selected as the primary key called?

database keys Easy
A. Super Key
B. Alternate Key
C. Foreign Key
D. Composite Key

18 Which SQL clause is used to filter records and extract only those that fulfill a specified condition?

SQL basic operations Easy
A. WHERE
B. FILTER
C. HAVING
D. CONDITION

19 Which SQL keyword is used to sort the result set in ascending or descending order?

SQL basic operations Easy
A. SORT BY
B. ARRANGE BY
C. ORDER BY
D. GROUP BY

20 In a SQL LIKE clause, what does the % (percent sign) wildcard represent?

SQL basic operations Easy
A. A space character
B. Exactly one character
C. Only numerical digits
D. Zero, one, or multiple characters

21 If a database administrator needs to change the data type of an existing column emp_salary in the Employees table from INTEGER to DECIMAL(10,2), which SQL command should be used?

introduction to data definition language Medium
A. CHANGE TABLE Employees COLUMN emp_salary DECIMAL(10,2);
B. UPDATE Employees SET emp_salary = DECIMAL(10,2);
C. ALTER TABLE Employees MODIFY emp_salary DECIMAL(10,2);
D. MODIFY TABLE Employees ALTER COLUMN emp_salary DECIMAL(10,2);

22 What is the primary operational difference between the DDL command TRUNCATE and the DML command DELETE without a WHERE clause?

introduction to data definition language Medium
A. There is no difference; both operate exactly the same way under the hood.
B. TRUNCATE removes the table schema, while DELETE only removes the data.
C. TRUNCATE deallocates the data pages and is typically not individually logged per row, making it faster and generally non-rollbackable in standard SQL.
D. TRUNCATE logs each row deletion in the transaction log, making it slower than DELETE.

23 Consider a relational schema . Which of the following DDL statements correctly creates a view that only exposes columns A and B where C is greater than 100?

introduction to data definition language Medium
A. CREATE VIEW V_R AS SELECT A, B FROM R WHERE C > 100;
B. ALTER VIEW V_R ADD SELECT A, B FROM R WHERE C > 100;
C. CREATE TABLE V_R AS SELECT A, B FROM R WHERE C > 100;
D. GENERATE VIEW V_R (A, B) FROM R WHERE C > 100;

24 An HR manager wants to give a 10% salary increase to all employees in the 'Sales' department. Which DML statement correctly achieves this?

data manipulation Medium
A. MODIFY Employees SET salary = salary * 0.10 WHERE department = 'Sales';
B. UPDATE Employees SET salary = salary * 1.10 WHERE department = 'Sales';
C. INSERT INTO Employees (salary) VALUES (salary * 1.10) WHERE department = 'Sales';
D. UPDATE Employees SET salary = salary + 1.10 WHERE department = 'Sales';

25 Which of the following describes the correct behavior of the INSERT INTO ... SELECT statement?

data manipulation Medium
A. It creates a new table and inserts the selected rows into it.
B. It copies data from the result of a SELECT query and inserts it into an existing target table.
C. It generates a virtual table (view) based on the SELECT query.
D. It updates existing rows in the target table based on the SELECT query.

26 If you execute a DELETE statement on a parent table, but there are child records in another table referencing the parent records via a foreign key without any cascading rules defined, what happens?

data manipulation Medium
A. The database blocks the deletion and throws a referential integrity constraint violation error.
B. The database deletes the parent records and leaves the child records as orphans.
C. The database automatically deletes the referencing child records.
D. The database sets the foreign key values in the child table to NULL.

27 User A grants SELECT privilege on table to User B WITH GRANT OPTION. User B then grants the same privilege to User C. What happens if User A revokes the privilege from User B using CASCADE?

data control and transaction control language Medium
A. User B loses the privilege, but User C retains it.
B. The revoke command fails because User C is currently using the privilege.
C. Both User B and User C lose the privilege.
D. User B retains the privilege, but User C loses it.

28 Within a single transaction, a user executes: INSERT (Row 1), SAVEPOINT SP1, INSERT (Row 2), ROLLBACK TO SP1, INSERT (Row 3), COMMIT. Which rows will be permanently stored in the database?

data control and transaction control language Medium
A. Only Row 3
B. Row 1, Row 2, and Row 3
C. Only Row 1
D. Row 1 and Row 3

29 Which of the following scenarios implicitly commits an active transaction in most standard SQL relational database management systems?

data control and transaction control language Medium
A. Executing a DDL command like CREATE TABLE or ALTER TABLE.
B. Executing a DML command like UPDATE or DELETE.
C. Encountering a minor syntax error in a query.
D. Executing a SELECT statement.

30 A table Orders has a foreign key referencing the CustomerID in the Customers table, defined with ON DELETE CASCADE. If a customer with 5 orders is deleted from the Customers table, what is the outcome?

integrity constraints Medium
A. The customer is deleted, and the 5 associated orders in the Orders table are also automatically deleted.
B. The customer is deleted, and the CustomerID in the 5 orders is set to NULL.
C. The database throws an error preventing the customer from being deleted.
D. The 5 orders are deleted, but the customer record remains.

31 Which of the following best describes the difference between a PRIMARY KEY constraint and a UNIQUE constraint in SQL?

integrity constraints Medium
A. There is no difference; they are interchangeable terms in SQL.
B. A PRIMARY KEY constraint is used only for numeric data types, while UNIQUE can be used for any data type.
C. A table can have multiple PRIMARY KEY constraints, but only one UNIQUE constraint.
D. A PRIMARY KEY column cannot contain NULL values, whereas a UNIQUE constraint column can typically contain one or more NULL values.

32 A table Employees has a constraint: CHECK (Age >= 18). If an application attempts to execute INSERT INTO Employees (Name, Age) VALUES ('John', NULL);, what will generally happen according to standard SQL?

integrity constraints Medium
A. The insertion will succeed because a CHECK constraint evaluates to UNKNOWN for NULL, which does not violate the constraint.
B. The insertion will fail because NULL is less than 18.
C. The database will automatically replace NULL with the default value of 18.
D. The insertion will fail due to a strict boolean FALSE evaluation.

33 Which integrity rule states that no part of a primary key can be NULL?

integrity constraints Medium
A. Domain Integrity
B. Referential Integrity
C. Key Integrity
D. Entity Integrity

34 Given a relation where the only candidate keys are and . Which of the following is considered a Super Key but NOT a Candidate Key?

database keys Medium
A.
B.
C.
D.

35 In a relational database, what is a composite key?

database keys Medium
A. A key that consists of two or more attributes that together uniquely identify an entity occurrence.
B. A primary key that is automatically generated by the database system (like an auto-increment integer).
C. A key that uniquely identifies a record in another table.
D. A key used for indexing and fast retrieval but cannot ensure uniqueness.

36 An Employee table contains columns EmpID (Primary Key), Name, and ManagerID. ManagerID contains the EmpID of the employee's manager. What type of key is ManagerID?

database keys Medium
A. A Composite Key
B. A Candidate Key
C. A Recursive/Self-referencing Foreign Key
D. A Surrogate Key

37 Why might a database designer choose to implement a surrogate key instead of a natural key as the primary key?

database keys Medium
A. Surrogate keys are required by SQL standards to enforce referential integrity.
B. Natural keys cannot be used as foreign keys in child tables.
C. Surrogate keys have intrinsic business meaning and help in data validation.
D. Natural keys often consist of multiple columns or string values that can change over time, whereas surrogate keys are stable, single-column numeric identifiers.

38 Consider the query: SELECT * FROM Students WHERE Name LIKE '_a%';. Which of the following names will be returned by this query?

SQL basic operations Medium
A. Sarah
B. Aaron
C. James
D. Adam

39 What is the expected output order when executing SELECT * FROM Products ORDER BY Category ASC, Price DESC;?

SQL basic operations Medium
A. Products are sorted randomly if both Category and Price are provided.
B. Products are sorted by Price in descending order, and then tied prices are sorted by Category in ascending order.
C. Products are sorted by Category in ascending order; within the same category, products are sorted by Price in descending order.
D. The query will fail because ASC and DESC cannot be used in the same ORDER BY clause.

40 A table Scores has 5 rows. The score_value column contains the values: 10, 20, NULL, 40, NULL. What will the query SELECT COUNT(*), COUNT(score_value) FROM Scores; return?

SQL basic operations Medium
A. 5 and 3
B. 5 and 5
C. 3 and 5
D. 3 and 3

41 Suppose you execute ALTER TABLE Employees ADD COLUMN department_id INT NOT NULL; on a table that already contains 10,000 rows. According to standard SQL behavior, what will happen if no DEFAULT clause is specified?

introduction to data definition language Hard
A. The statement will succeed, but the table will be locked until an UPDATE statement populates the missing values.
B. The statement will succeed, and all existing rows will have the department_id set to 0 automatically.
C. The statement will fail and return an error because the existing rows cannot be populated with a NULL value to satisfy the NOT NULL constraint.
D. The statement will succeed, and existing rows will contain NULL values because the constraint only applies to future inserts.

42 Table Orders has a foreign key referencing the primary key of table Customers. Both tables are currently empty. What is the behavior of executing TRUNCATE TABLE Customers; versus DELETE FROM Customers; in a standard relational database?

introduction to data definition language Hard
A. TRUNCATE will fail due to the existing foreign key constraint, while DELETE will succeed.
B. Both commands will fail unless CASCADE is explicitly specified.
C. DELETE will fail due to the existing foreign key constraint, while TRUNCATE will succeed.
D. Both commands will execute successfully since the tables are empty.

43 Consider a view created with CREATE VIEW HighEarners AS SELECT * FROM Employees WHERE salary > 50000 WITH CHECK OPTION;. What happens if an UPDATE statement via this view attempts to change a salary from 60000 to 40000?

introduction to data definition language Hard
A. The update succeeds, and the row remains visible in the view until the session is closed.
B. The update fails because views created with WITH CHECK OPTION are strictly read-only for DML operations.
C. The update fails with a constraint violation error because the new row does not satisfy the view's defining condition.
D. The update succeeds, but the row immediately disappears from the view.

44 A table T1(A, B) contains values (1, 10) and (2, 20). If you execute the statement UPDATE T1 SET A = B, B = A;, what will be the final values in T1?

data manipulation Hard
A. (10, 1) and (20, 2)
B. (10, 10) and (20, 20)
C. The statement will result in a syntax error because a column cannot be referenced multiple times in a SET clause.
D. (1, 1) and (2, 2)

45 Consider the query: DELETE FROM Employees WHERE department_id NOT IN (SELECT department_id FROM Departments);. If the subquery SELECT department_id FROM Departments returns the values (1, 2, NULL), how many rows will be deleted from the Employees table?

data manipulation Hard
A. Only rows where department_id is NULL.
B. Zero rows.
C. All rows in the Employees table.
D. All rows where department_id is neither 1 nor 2.

46 What is the consequence of executing a MERGE statement where multiple rows in the source table match a single row in the target table based on the ON condition?

data manipulation Hard
A. The target row is updated multiple times sequentially based on the order of the source rows.
B. The target row is updated, and a duplicate row is inserted for every subsequent match.
C. Only the first matching source row (based on internal ROWID) is used to update the target row; subsequent matches are ignored.
D. The statement throws a deterministic error indicating that the ON clause resulted in multiple matches for a target row.

47 When implementing a Correlated UPDATE, such as UPDATE T1 SET col1 = (SELECT col2 FROM T2 WHERE T1.id = T2.ref_id);, what happens if the subquery returns no rows for a specific T1.id?

data manipulation Hard
A. The col1 of the affected T1 row is set to NULL.
B. The row in T1 is skipped, leaving col1 unchanged.
C. The UPDATE statement fails with an error.
D. The col1 is set to the default value defined in the table schema.

48 User A grants SELECT on Table X to User B WITH GRANT OPTION. User B grants it to User C. Later, User A grants SELECT on Table X directly to User C without the grant option. If User A revokes the privilege from User B CASCADE, what is the privilege state for User C?

data control and transaction control language Hard
A. User C retains the SELECT privilege without the grant option because of the direct grant from User A.
B. User C loses the SELECT privilege entirely because the cascaded revoke overrides all existing grants.
C. User C retains the SELECT privilege and the grant option because A cannot revoke C's access indirectly.
D. User C loses the SELECT privilege but retains a suspended grant that reactivates if User B regains the privilege.

49 In a transaction, you issue a SAVEPOINT S1, execute an UPDATE, issue SAVEPOINT S2, execute a DELETE, and then issue ROLLBACK TO SAVEPOINT S1. Finally, you issue a COMMIT. Which changes are made permanent?

data control and transaction control language Hard
A. Neither the UPDATE nor the DELETE.
B. Only the DELETE.
C. Both the UPDATE and the DELETE.
D. Only the UPDATE.

50 Which of the following transaction isolation levels in standard SQL is the lowest level required to explicitly prevent Phantom Reads?

data control and transaction control language Hard
A. REPEATABLE READ
B. READ UNCOMMITTED
C. SERIALIZABLE
D. READ COMMITTED

51 What is the primary difference between a REVOKE ... RESTRICT and a REVOKE ... CASCADE command in Data Control Language?

data control and transaction control language Hard
A. RESTRICT limits the revocation to DML commands, while CASCADE applies to both DML and DDL commands.
B. RESTRICT only revokes privileges from users currently logged in, while CASCADE revokes from all users.
C. RESTRICT removes the GRANT OPTION but keeps the basic privilege, while CASCADE removes the privilege entirely.
D. RESTRICT prevents the REVOKE command from executing if the privilege has been passed on to others, while CASCADE removes the privilege from the target and all subsequent grantees.

52 A table constraint is defined as INITIALLY DEFERRED. During a transaction, a DML statement violates this constraint. What happens immediately after the DML statement is executed?

integrity constraints Hard
A. The constraint violation is ignored completely.
B. The database automatically alters the constraint to INITIALLY IMMEDIATE.
C. The DML statement fails immediately and rolls back the transaction.
D. The statement succeeds temporarily; the violation is only checked when COMMIT is issued.

53 Consider a foreign key defined with ON DELETE SET NULL. However, the column in the referencing table is defined with a NOT NULL constraint. What occurs when a referenced row in the parent table is deleted?

integrity constraints Hard
A. The referencing row is automatically deleted instead of being set to NULL.
B. The foreign key constraint is automatically dropped to allow the deletion.
C. The delete operation successfully sets the referencing column to NULL, bypassing the NOT NULL constraint.
D. The delete operation fails and raises a constraint violation error due to the NOT NULL constraint.

54 A table has a constraint CHECK (Quantity * Price > 100). A user inserts a row with Quantity = 5 and Price = NULL. Does this insertion succeed or fail, and why?

integrity constraints Hard
A. It succeeds because the database automatically substitutes 0 for NULL, resulting in 0 > 100 which bypasses the check.
B. It succeeds because 5 * NULL evaluates to UNKNOWN, and CHECK constraints are only violated if the condition evaluates to FALSE.
C. It fails because mathematical operations with NULL trigger a data type error in CHECK constraints.
D. It fails because 5 * NULL evaluates to NULL, and a CHECK constraint requires the condition to evaluate to TRUE.

55 Let be a relation schema. If the only candidate keys are and , what is the total number of superkeys for relation ?

database keys Hard
A. 16
B. 14
C. 12
D. 32

56 Given functional dependencies , , and in a relation , which of the following statements about candidate keys is TRUE?

database keys Hard
A. , , and are all candidate keys of .
B. , , and are all candidate keys of .
C. There is only one candidate key, which is .
D. is a candidate key of .

57 Which of the following scenarios describes a situation where a Unique Key constraint acts functionally identical to a Primary Key constraint in relational theory?

database keys Hard
A. When the Unique Key allows exactly one NULL value.
B. When the Unique Key allows multiple NULL values.
C. When the Unique Key is combined with a NOT NULL constraint on all its constituent columns.
D. When the Unique Key is defined over multiple columns.

58 How is the relational algebra 'Division' operator () typically implemented in SQL?

SQL basic operations Hard
A. Using a standard INNER JOIN with a GROUP BY clause.
B. Using the INTERSECT operator between the two tables.
C. Using a LEFT OUTER JOIN where the right table's key is NULL.
D. Using a double NOT EXISTS nested subquery.

59 Consider the query: SELECT val FROM T1 WHERE val > ALL (SELECT val FROM T2);. What is the result if the table T2 contains zero rows?

SQL basic operations Hard
A. The query throws a runtime execution error.
B. The query returns all rows from T1.
C. The query returns zero rows because comparison with an empty set evaluates to UNKNOWN.
D. The query returns only the rows in T1 where val is NULL.

60 A query uses LEFT JOIN between Table_A and Table_B. It includes an aggregate function COUNT(Table_B.id). If Table_A has 5 rows and none of them match any row in Table_B, what does COUNT(Table_B.id) return if no GROUP BY is used?

SQL basic operations Hard
A. 5
B. NULL
C. 0
D. 1
Previous MCQ
Unit 1
Next MCQ
Unit 3