What is an index in SQL used for?
A
To enforce data integrity B
To improve query performance C
To store backup copies of tables D
To create user permissions
Analysis & Theory
Indexes help queries run faster by allowing quick lookups.
Which SQL statement creates an index on the 'LastName' column of the 'Employees' table?
A
CREATE INDEX idx_lastname ON Employees (LastName); B
MAKE INDEX idx_lastname FROM Employees (LastName); C
BUILD INDEX idx_lastname ON Employees.LastName; D
ADD INDEX idx_lastname (Employees.LastName);
Analysis & Theory
The correct syntax is CREATE INDEX index_name ON table (column).
Which type of index ensures that values in a column are unique?
A
Clustered Index B
Composite Index C
Unique Index D
Filtered Index
Analysis & Theory
A unique index enforces uniqueness of the values.
How do you remove an index in SQL?
A
DROP INDEX idx_name; B
DELETE INDEX idx_name; C
REMOVE INDEX idx_name; D
ALTER TABLE DROP INDEX idx_name;
Analysis & Theory
DROP INDEX index_name is the correct syntax.
What is a composite index?
A
An index on computed columns B
An index that includes multiple columns C
A backup index D
A temporary index
Analysis & Theory
Composite indexes cover more than one column.
Which statement creates a UNIQUE index on 'Email' in the 'Users' table?
A
CREATE UNIQUE INDEX idx_email ON Users (Email); B
CREATE INDEX UNIQUE idx_email ON Users (Email); C
CREATE INDEX idx_email UNIQUE ON Users (Email); D
CREATE UNIQUE Users.Email INDEX;
Analysis & Theory
The correct syntax: CREATE UNIQUE INDEX index_name ON table (column).
Which of these statements about clustered indexes is TRUE?
A
A table can have multiple clustered indexes B
A clustered index stores the data rows in sorted order C
Clustered indexes cannot be created manually D
Clustered indexes are slower than non-clustered indexes
Analysis & Theory
Clustered indexes physically sort the data rows.
What does this statement do?
CREATE INDEX idx_composite ON Orders (CustomerID, OrderDate);
A
Creates an index only on OrderDate B
Creates a composite index on CustomerID and OrderDate C
Creates a unique index D
Deletes duplicate records
Analysis & Theory
This creates a composite index on both columns.
Which of these is a potential drawback of using indexes?
A
They can slow down data retrieval B
They can increase storage requirements C
They prevent table updates D
They disable constraints
Analysis & Theory
Indexes consume additional storage space.
What happens if you create an index on a column that already has many duplicate values?
A
It always improves performance significantly B
It may have limited performance benefits C
It will prevent inserts D
It will convert the duplicates to unique values
Analysis & Theory
Indexes on columns with low selectivity (many duplicates) may not help much.