Crafting SQL Databases (DDL) – MCQs
1. Navigating Database Relationships
1. Which SQL command creates a relationship between two tables?
a) JOIN
b) FOREIGN KEY
c) PRIMARY KEY
d) RELATE
Answer: b) FOREIGN KEY
2. A relationship where one record in a table relates to many in
another is:
a) One-to-One
b) One-to-Many
c) Many-to-Many
d) Self-Join
Answer: b) One-to-Many
3. Many-to-Many relationships are implemented in SQL using:
a) Direct foreign keys
b) A junction/bridge table
c) Primary key duplication
d) Views
Answer: b) A junction/bridge table
4. A self-referencing relationship is handled with:
a) Foreign key referencing another table
b) Foreign key referencing the same table
c) Composite key
d) Trigger
Answer: b) Foreign key referencing the same table
5. Referential Integrity ensures:
a) Unique values in every column
b) No orphan records in related tables
c) Fast queries
d) Automatic backup of relations
Answer: b) No orphan records in related tables
6. Which constraint ensures a column in child table refers to a valid
value in parent table?
a) CHECK
b) FOREIGN KEY
c) UNIQUE
d) NOT NULL
Answer: b) FOREIGN KEY
7. When deleting a parent row, related child rows can be auto-deleted
using:
a) CASCADE DELETE
b) AUTO DELETE
c) TRIGGER DELETE
d) REMOVE ALL
Answer: a) CASCADE DELETE
8. Which SQL command creates a table with a relationship?
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
This establishes which relationship?
Answer: One-to-Many
9. Which type of join returns only matching rows from both tables?
a) LEFT JOIN
b) RIGHT JOIN
c) INNER JOIN
d) FULL OUTER JOIN
Answer: c) INNER JOIN
10. Which type of join returns all rows from both tables, matching
where possible?
a) INNER JOIN
b) FULL OUTER JOIN
c) CROSS JOIN
d) LEFT JOIN
Answer: b) FULL OUTER JOIN
2. Data Quality and Speed: Keys and Indexing
11. Which key uniquely identifies each record in a table?
a) Foreign Key
b) Candidate Key
c) Primary Key
d) Super Key
Answer: c) Primary Key
12. Which key can act as a primary key but is not chosen?
a) Candidate Key
b) Alternate Key
c) Foreign Key
d) Composite Key
Answer: b) Alternate Key
13. A combination of two or more columns used to uniquely
identify records is called:
a) Super Key
b) Composite Key
c) Primary Key
d) Foreign Key
Answer: b) Composite Key
14. Which type of key allows duplicate values but ensures fast
access?
a) Primary Key
b) Unique Key
c) Index
d) Foreign Key
Answer: c) Index
15. Which constraint ensures no duplicate values in a column but
allows NULLs?
a) PRIMARY KEY
b) UNIQUE
c) NOT NULL
d) CHECK
Answer: b) UNIQUE
16. Which constraint prevents NULL values in a column?
a) NOT NULL
b) UNIQUE
c) CHECK
d) DEFAULT
Answer: a) NOT NULL
17. Which constraint provides a default value if none is given?
a) UNIQUE
b) DEFAULT
c) PRIMARY KEY
d) CHECK
Answer: b) DEFAULT
18. Which constraint enforces conditions on column values?
a) CHECK
b) UNIQUE
c) NOT NULL
d) FOREIGN KEY
Answer: a) CHECK
19. Which indexing method maintains a balanced tree structure?
a) B-Tree Index
b) Hash Index
c) Bitmap Index
d) Clustered Index
Answer: a) B-Tree Index
20. Which index stores data physically in sorted order of the key?
a) Non-clustered Index
b) Clustered Index
c) Bitmap Index
d) Hash Index
Answer: b) Clustered Index
21. Which index is more efficient for equality lookups?
a) B-Tree Index
b) Hash Index
c) Clustered Index
d) Composite Index
Answer: b) Hash Index
22. A composite index is built on:
a) A single column
b) Multiple columns
c) Primary Key only
d) Foreign Key only
Answer: b) Multiple columns
23. Which type of index is best for low-cardinality data like gender
(M/F)?
a) B-Tree Index
b) Hash Index
c) Bitmap Index
d) Clustered Index
Answer: c) Bitmap Index
24. Which command creates an index?
a) NEW INDEX
b) CREATE INDEX
c) ADD INDEX
d) BUILD INDEX
Answer: b) CREATE INDEX
25. Which command removes an index?
a) DELETE INDEX
b) DROP INDEX
c) REMOVE INDEX
d) ALTER INDEX
Answer: b) DROP INDEX
3. Normalization and Denormalization Strategies
26. Normalization is done to:
a) Increase redundancy
b) Eliminate redundancy and anomalies
c) Improve backup speed
d) Reduce security risks
Answer: b) Eliminate redundancy and anomalies
27. The process of combining normalized tables into fewer tables
is called:
a) Normalization
b) Denormalization
c) Decomposition
d) Structuring
Answer: b) Denormalization
28. 1st Normal Form (1NF) eliminates:
a) Partial Dependency
b) Multi-valued attributes
c) Transitive Dependency
d) Duplicate Tuples
Answer: b) Multi-valued attributes
29. 2nd Normal Form (2NF) eliminates:
a) Partial Dependency
b) Transitive Dependency
c) Redundant tuples
d) Null values
Answer: a) Partial Dependency
30. 3rd Normal Form (3NF) eliminates:
a) Partial Dependency
b) Multi-valued attributes
c) Transitive Dependency
d) Duplicate Rows
Answer: c) Transitive Dependency
31. Boyce-Codd Normal Form (BCNF) handles cases not covered
by:
a) 1NF
b) 2NF
c) 3NF
d) 4NF
Answer: c) 3NF
32. Which normal form removes multivalued dependencies?
a) 2NF
b) 3NF
c) 4NF
d) 5NF
Answer: c) 4NF
33. Which normal form deals with join dependency?
a) 3NF
b) 4NF
c) 5NF
d) BCNF
Answer: c) 5NF
34. Denormalization is mainly done for:
a) Speed of query performance
b) Removing anomalies
c) Data compression
d) Data hiding
Answer: a) Speed of query performance
35. A denormalized table usually has:
a) More redundancy
b) Less redundancy
c) No redundancy
d) Only foreign keys
Answer: a) More redundancy
4. Blueprinting Database Structures
36. DDL stands for:
a) Data Description Language
b) Data Definition Language
c) Database Design Language
d) Data Display Language
Answer: b) Data Definition Language
37. Which DDL command is used to create a new table?
a) MAKE TABLE
b) CREATE TABLE
c) NEW TABLE
d) INIT TABLE
Answer: b) CREATE TABLE
38. Which command modifies an existing table structure?
a) UPDATE TABLE
b) ALTER TABLE
c) CHANGE TABLE
d) MODIFY TABLE
Answer: b) ALTER TABLE
39. Which command permanently deletes a table and its
structure?
a) DROP TABLE
b) DELETE TABLE
c) REMOVE TABLE
d) CLEAR TABLE
Answer: a) DROP TABLE
40. Which command removes all rows from a table but keeps the
structure?
a) DELETE
b) TRUNCATE
c) DROP
d) REMOVE
Answer: b) TRUNCATE
41. Which constraint ensures column values are not empty?
a) DEFAULT
b) CHECK
c) NOT NULL
d) UNIQUE
Answer: c) NOT NULL
42. Which constraint restricts column values within a condition?
a) CHECK
b) DEFAULT
c) UNIQUE
d) FOREIGN KEY
Answer: a) CHECK
43. Which clause is used to rename an existing table?
a) ALTER TABLE RENAME
b) CHANGE TABLE NAME
c) UPDATE TABLE NAME
d) MODIFY NAME
Answer: a) ALTER TABLE RENAME
44. Which command creates a copy of an existing table structure
without data?
a) CREATE TABLE new AS old
b) CREATE TABLE new LIKE old
c) DUPLICATE TABLE
d) COPY STRUCTURE
Answer: b) CREATE TABLE new LIKE old
45. Which command creates a table with data from another table?
a) CREATE TABLE new AS SELECT …
b) COPY TABLE old INTO new
c) INSERT INTO new SELECT …
d) CLONE TABLE old
Answer: a) CREATE TABLE new AS SELECT …
46. Which SQL object is a virtual table based on SELECT query?
a) View
b) Index
c) Procedure
d) Trigger
Answer: a) View
47. Which command creates a new view?
a) CREATE VIEW
b) NEW VIEW
c) DEFINE VIEW
d) INIT VIEW
Answer: a) CREATE VIEW
48. Which command deletes a view?
a) DELETE VIEW
b) DROP VIEW
c) REMOVE VIEW
d) CLEAR VIEW
Answer: b) DROP VIEW
49. Which SQL object automatically performs actions when events
occur?
a) Index
b) Trigger
c) Procedure
d) Cursor
Answer: b) Trigger
50. Which command creates a trigger?
a) DEFINE TRIGGER
b) CREATE TRIGGER
c) NEW TRIGGER
d) MAKE TRIGGER
Answer: b) CREATE TRIGGER
51. Which SQL object stores a set of reusable SQL statements?
a) View
b) Index
c) Stored Procedure
d) Cursor
Answer: c) Stored Procedure
52. Which command is used to create a stored procedure?
a) NEW PROCEDURE
b) CREATE PROCEDURE
c) DEFINE PROCEDURE
d) MAKE PROCEDURE
Answer: b) CREATE PROCEDURE
53. Which command deletes a stored procedure?
a) DELETE PROCEDURE
b) DROP PROCEDURE
c) REMOVE PROCEDURE
d) CLEAR PROCEDURE
Answer: b) DROP PROCEDURE
54. Which DDL command is used to create a schema?
a) NEW SCHEMA
b) CREATE SCHEMA
c) MAKE SCHEMA
d) INIT SCHEMA
Answer: b) CREATE SCHEMA
55. Which command removes a schema completely?
a) DELETE SCHEMA
b) DROP SCHEMA
c) REMOVE SCHEMA
d) CLEAR SCHEMA
Answer: b) DROP SCHEMA
56. Which constraint enforces entity integrity?
a) UNIQUE
b) PRIMARY KEY
c) FOREIGN KEY
d) DEFAULT
Answer: b) PRIMARY KEY
57. Which constraint enforces referential integrity?
a) UNIQUE
b) CHECK
c) FOREIGN KEY
d) DEFAULT
Answer: c) FOREIGN KEY
58. Which SQL object improves query speed but doesn’t store
data itself?
a) Index
b) View
c) Trigger
d) Procedure
Answer: a) Index
59. Which DDL command is used to change column data type?
a) CHANGE COLUMN TYPE
b) ALTER TABLE MODIFY
c) UPDATE COLUMN TYPE
d) RENAME COLUMN TYPE
Answer: b) ALTER TABLE MODIFY
60. Which DDL command is used to delete an entire database?
a) REMOVE DATABASE
b) DROP DATABASE
c) CLEAR DATABASE
d) DELETE DATABASE
Answer: b) DROP DATABASE