Back to Subject

Unit 2 - Practice Quiz

INT306 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. Data Definition Language
C. Dynamic Data 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. MAKE TABLE
B. GENERATE TABLE
C. CREATE TABLE
D. BUILD 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. DELETE TABLE
B. TRUNCATE TABLE
C. REMOVE TABLE
D. DROP TABLE

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

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

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

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

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

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

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

data manipulation Easy
A. DROP
B. DELETE
C. ERASE
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. 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. COMMIT
B. SAVE
C. STORE
D. ROLLBACK

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. UNDO
C. REVERT
D. ROLLBACK

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

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

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

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

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

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

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

database keys Easy
A. Primary Key
B. Alternate Key
C. Foreign Key
D. Composite 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. Primary Key
C. Foreign Key
D. Super Key

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

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

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

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

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

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

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

SQL basic operations Easy
A. GROUP BY
B. ARRANGE BY
C. ORDER BY
D. SORT 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. ALTER TABLE Employees MODIFY emp_salary DECIMAL(10,2);
B. MODIFY TABLE Employees ALTER COLUMN emp_salary DECIMAL(10,2);
C. UPDATE Employees SET emp_salary = DECIMAL(10,2);
D. CHANGE TABLE Employees 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 logs each row deletion in the transaction log, making it slower than DELETE.
B. TRUNCATE removes the table schema, while DELETE only removes the data.
C. There is no difference; both operate exactly the same way under the hood.
D. TRUNCATE deallocates the data pages and is typically not individually logged per row, making it faster and generally non-rollbackable in standard SQL.

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 TABLE V_R AS SELECT A, B FROM R WHERE C > 100;
B. GENERATE VIEW V_R (A, B) FROM R WHERE C > 100;
C. CREATE VIEW V_R AS SELECT A, B FROM R WHERE C > 100;
D. ALTER VIEW V_R ADD SELECT 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. INSERT INTO Employees (salary) VALUES (salary * 1.10) WHERE department = 'Sales';
B. UPDATE Employees SET salary = salary + 1.10 WHERE department = 'Sales';
C. MODIFY Employees SET salary = salary * 0.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 updates existing rows in the target table based on the SELECT query.
B. It copies data from the result of a SELECT query and inserts it into an existing target table.
C. It creates a new table and inserts the selected rows into it.
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 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 sets the foreign key values in the child table to NULL.
D. The database automatically deletes the referencing child records.

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. Both User B and User C lose the privilege.
B. User B loses the privilege, but User C retains it.
C. The revoke command fails because User C is currently using 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 1
B. Only Row 3
C. Row 1 and Row 3
D. Row 1, Row 2, 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 SELECT statement.
C. Executing a DML command like UPDATE or DELETE.
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 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. A PRIMARY KEY constraint is used only for numeric data types, while UNIQUE can be used for any data type.
B. There is no difference; they are interchangeable terms in SQL.
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 fail because NULL is less than 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 due to a strict boolean FALSE evaluation.
D. The database will automatically replace NULL with the default value of 18.

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

integrity constraints Medium
A. Referential Integrity
B. Domain 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 uniquely identifies a record in another table.
B. A primary key that is automatically generated by the database system (like an auto-increment integer).
C. A key used for indexing and fast retrieval but cannot ensure uniqueness.
D. A key that consists of two or more attributes that together uniquely identify an entity occurrence.

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 Recursive/Self-referencing Foreign Key
C. A Composite 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. Natural keys cannot be used as foreign keys in child tables.
B. Natural keys often consist of multiple columns or string values that can change over time, whereas surrogate keys are stable, single-column numeric identifiers.
C. Surrogate keys are required by SQL standards to enforce referential integrity.
D. Surrogate keys have intrinsic business meaning and help in data validation.

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. Aaron
B. Sarah
C. Adam
D. James

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

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

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. 3 and 3
B. 5 and 3
C. 5 and 5
D. 3 and 5

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 existing rows will contain NULL values because the constraint only applies to future inserts.
C. The statement will succeed, and all existing rows will have the department_id set to 0 automatically.
D. 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.

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 execute successfully since the tables are empty.
C. TRUNCATE will fail due to the existing foreign key constraint, while DELETE will succeed.
D. Both commands will fail unless CASCADE is explicitly specified.

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, but the row immediately disappears from the view.
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, and the row remains visible in the view until the session is closed.

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. Zero rows.
B. All rows in the Employees table.
C. All rows where department_id is neither 1 nor 2.
D. Only rows where department_id is NULL.

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. Only the first matching source row (based on internal ROWID) is used to update the target row; subsequent matches are ignored.
C. The statement throws a deterministic error indicating that the ON clause resulted in multiple matches for a target row.
D. The target row is updated multiple times sequentially based on the order of the source rows.

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 is set to the default value defined in the table schema.
B. The col1 of the affected T1 row is set to NULL.
C. The UPDATE statement fails with an error.
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 loses the SELECT privilege but retains a suspended grant that reactivates if User B regains the privilege.
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 retains the SELECT privilege without the grant option because of the direct grant from User A.

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. Both the UPDATE and the DELETE.
C. Only the UPDATE.
D. Only 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. REPEATABLE READ
B. SERIALIZABLE
C. READ UNCOMMITTED
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 only revokes privileges from users currently logged in, while CASCADE revokes from all users.
B. RESTRICT limits the revocation to DML commands, while CASCADE applies to both DML and DDL commands.
C. 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.
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 DML statement fails immediately and rolls back the transaction.
B. The database automatically alters the constraint to INITIALLY IMMEDIATE.
C. The constraint violation is ignored completely.
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 delete operation fails and raises a constraint violation error due to the NOT NULL constraint.
B. The referencing row is automatically deleted instead of being set to NULL.
C. The foreign key constraint is automatically dropped to allow the deletion.
D. The delete operation successfully sets the referencing column to NULL, bypassing 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 fails because mathematical operations with NULL trigger a data type error in CHECK constraints.
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 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. 32
B. 12
C. 14
D. 16

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

database keys Hard
A. There is only one candidate key, which is .
B. , , and are all candidate keys of .
C. is a candidate key of .
D. , , and are all candidate keys 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 is defined over multiple columns.
B. When the Unique Key allows multiple NULL values.
C. When the Unique Key allows exactly one NULL value.
D. When the Unique Key is combined with a NOT NULL constraint on all its constituent columns.

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

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

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

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