Queries

What is the purpose of query optimization in SQL?

• To increase execution time

• To reduce execution time and resource usage

• To add more indexes to the database

• To increase database storage

CREATE INDEX idx_city ON Students(City);

• Creates a new table named idx_city

• Creates an index on the City column of the Students table

• Deletes duplicate records from the Students table

• Adds a foreign key constraint on the City column

Which of the following is a benefit of using indexes in SQL?

• Reduces database storage

• Speeds up queries that filter data using indexed columns

• Makes the database smaller in size

• Makes the database smaller in size

Which SQL statement can be used to check if an index exists on a table?

• DESCRIBE INDEX

• SHOW INDEX FROM Students;

• SELECT * FROM INDEXES;

• EXPLAIN INDEX Students;

What does the EXPLAIN statement do in SQL?

• Drops an index from a table

• Displays the execution plan of a query

• Creates an optimized query automatically

• Removes duplicate rows from a query

If a table has no indexes, how does SQL search for a record?

• Binary search

• Full table scan

• Hash function lookup

• Using a stored procedure

What type of index is created by default in MySQL when using CREATE INDEX?

• HASH

• BTREE

• CLUSTERED

• FULLTEXT

Which of the following queries will run faster after creating an index on the City column?

• SELECT * FROM Students WHERE City = 'New York';

• SELECT DISTINCT City FROM Students;

• SELECT COUNT(*) FROM Students;

• INSERT INTO Students VALUES (6, 'John', 23, 'CS', 3.7, 'Boston');

Which SQL clause is used to limit the number of rows returned in a query?

• LIMIT

• GROUP BY

• HAVING

• INDEX

Which query optimization technique is used to improve performance in large datasets?

• Using SELECT * in queries

• Applying indexing on frequently used columns

• Running queries without WHERE conditions

• Avoiding indexes to reduce storage