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 Derivation Language
B. Dynamic Data Language
C. Data Definition Language
D. Detailed Data Language

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

introduction to data definition language Easy
A. GENERATE TABLE
B. CREATE 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. DELETE TABLE
C. DROP TABLE
D. REMOVE TABLE

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

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

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

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

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

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

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

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

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. ASSIGN
B. PERMIT
C. ALLOW
D. GRANT

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

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

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

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

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

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

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

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

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

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

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

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

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

database keys Easy
A. Primary Key
B. Super Key
C. Foreign Key
D. Candidate 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. Foreign Key
C. Alternate 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. CONDITION
B. FILTER
C. HAVING
D. WHERE

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

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

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

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

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

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. ALTER VIEW V_R ADD SELECT A, B FROM R WHERE C > 100;
B. CREATE VIEW V_R AS 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. INSERT INTO Employees (salary) VALUES (salary * 1.10) WHERE department = 'Sales';
C. UPDATE Employees SET salary = 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 updates existing rows in the target table based on the SELECT query.
D. It generates a virtual table (view) 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 sets the foreign key values in the child table to NULL.
B. The database automatically deletes the referencing child records.
C. The database deletes the parent records and leaves the child records as orphans.
D. The database blocks the deletion and throws a referential integrity constraint violation error.

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. The revoke command fails because User C is currently using the privilege.
B. Both User B and User C lose the privilege.
C. User B retains the privilege, but User C loses it.
D. User B loses the privilege, but User C retains 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. Row 1 and Row 3
B. Only Row 3
C. Row 1, Row 2, and Row 3
D. Only Row 1

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 SELECT statement.
B. Executing a DML command like UPDATE or DELETE.
C. Executing a DDL command like CREATE TABLE or ALTER TABLE.
D. Encountering a minor syntax error in a query.

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 5 orders are deleted, but the customer record remains.
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 customer is deleted, and the 5 associated orders in the Orders table are also automatically deleted.

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

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

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 database will automatically replace NULL with the default value of 18.
B. The insertion will succeed because a CHECK constraint evaluates to UNKNOWN for NULL, which does not violate the constraint.
C. The insertion will fail because NULL is less than 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. Referential Integrity
B. Entity Integrity
C. Key Integrity
D. Domain 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 used for indexing and fast retrieval but cannot ensure uniqueness.
B. A primary key that is automatically generated by the database system (like an auto-increment integer).
C. A key that consists of two or more attributes that together uniquely identify an entity occurrence.
D. A key that uniquely identifies a record in another table.

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 Candidate Key
B. A Composite Key
C. A Surrogate Key
D. A Recursive/Self-referencing Foreign 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 have intrinsic business meaning and help in data validation.
B. Surrogate keys are required by SQL standards to enforce referential integrity.
C. Natural keys cannot be used as foreign keys in child tables.
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. Adam
C. James
D. Aaron

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 by Price in descending order, and then tied prices are sorted by Category in ascending order.
B. The query will fail because ASC and DESC cannot be used in the same ORDER BY clause.
C. Products are sorted by Category in ascending order; within the same category, products are sorted by Price in descending order.
D. Products are sorted randomly if both Category and Price are provided.

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 5
B. 3 and 5
C. 5 and 3
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, and existing rows will contain NULL values because the constraint only applies to future inserts.
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, but the table will be locked until an UPDATE statement populates the missing values.

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. DELETE will fail due to the existing foreign key constraint, while TRUNCATE will succeed.
B. Both commands will fail unless CASCADE is explicitly specified.
C. TRUNCATE will fail due to the existing foreign key constraint, while DELETE 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 with a constraint violation error because the new row does not satisfy the view's defining condition.
C. The update succeeds, but the row immediately disappears from the view.
D. The update fails because views created with WITH CHECK OPTION are strictly read-only for DML operations.

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. The statement will result in a syntax error because a column cannot be referenced multiple times in a SET clause.
B. (1, 1) and (2, 2)
C. (10, 10) and (20, 20)
D. (10, 1) and (20, 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. All rows in the Employees table.
B. Zero rows.
C. Only rows where department_id is NULL.
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, and a duplicate row is inserted for every subsequent match.
B. The statement throws a deterministic error indicating that the ON clause resulted in multiple matches for a target row.
C. The target row is updated multiple times sequentially based on the order of the source rows.
D. Only the first matching source row (based on internal ROWID) is used to update the target row; subsequent matches are ignored.

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 UPDATE statement fails with an error.
C. The col1 is set to the default value defined in the table schema.
D. The row in T1 is skipped, leaving col1 unchanged.

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 retains the SELECT privilege and the grant option because A cannot revoke C's access indirectly.
C. User C loses the SELECT privilege entirely because the cascaded revoke overrides all existing grants.
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. Only the DELETE.
B. Only the UPDATE.
C. Both the UPDATE and the DELETE.
D. Neither the UPDATE nor the DELETE.

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. READ UNCOMMITTED
B. SERIALIZABLE
C. REPEATABLE READ
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 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.
C. RESTRICT only revokes privileges from users currently logged in, while CASCADE revokes from all users.
D. RESTRICT removes the GRANT OPTION but keeps the basic privilege, while CASCADE removes the privilege entirely.

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 statement succeeds temporarily; the violation is only checked when COMMIT is issued.
C. The DML statement fails immediately and rolls back the transaction.
D. The database automatically alters the constraint to INITIALLY IMMEDIATE.

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 delete operation successfully sets the referencing column to NULL, bypassing the NOT NULL constraint.
B. The delete operation fails and raises a constraint violation error due to the NOT NULL constraint.
C. The foreign key constraint is automatically dropped to allow the deletion.
D. The referencing row is automatically deleted instead of being set to NULL.

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

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. 14
B. 32
C. 12
D. 16

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

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

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 is combined with a NOT NULL constraint on all its constituent columns.
B. When the Unique Key allows exactly one NULL value.
C. When the Unique Key is defined over multiple columns.
D. When the Unique Key allows multiple NULL values.

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

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

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 returns zero rows because comparison with an empty set evaluates to UNKNOWN.
B. The query returns only the rows in T1 where val is NULL.
C. The query returns all rows from T1.
D. The query throws a runtime execution error.

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. 0
B. 1
C. NULL
D. 5
Previous MCQ
Unit 1
Next MCQ
Unit 3