Intermediate level sql quiz

Which SQL keyword is used to create an index on a table?

CREATE INDEX

ADD INDEX

MAKE INDEX

INSERT INDEX

What is the purpose of the `UNION ALL` operator in SQL?

It combines the result sets of two queries and includes duplicates

It combines the result sets of two queries and removes duplicates

It joins two tables

It filters the result set by removing NULL values

Which of the following is a correct SQL statement to find the second highest salary from an `employees` table?

SELECT MAX(salary) FROM employees WHERE salary NOT IN (SELECT MAX(salary) FROM employees)

SELECT MAX(salary) FROM employees WHERE salary > (SELECT MAX(salary) FROM employees)

SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1

SELECT salary FROM employees ORDER BY salary ASC LIMIT 1 OFFSET 1

Which SQL command is used to remove all rows from a table without deleting the table itself?

TRUNCATE

DROP

DELETE

REMOVE

What is the function of the `RANK()` window function in SQL?

It assigns a rank to each row in a partition of a result set

It sums the values in a column

It calculates the average of a column

It returns the count of rows in a partition

Which SQL clause is used to combine rows from two or more tables based on a related column?

JOIN

GROUP BY

UNION

INTERSECT

What is the default join type in SQL if no join type is specified?

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

Which SQL function is used to convert a string to lowercase?

LOWER()

UPPER()

CONVERT()

LCASE()

What does the `DISTINCT` keyword do in SQL?

It returns only unique values in the result set

It sorts the result set

It limits the number of rows returned

It combines the result sets of multiple queries

Which of the following is the correct way to join three tables in SQL?

SELECT * FROM table1, table2, table3

SELECT * FROM table1 JOIN table2 JOIN table3

SELECT * FROM table1 JOIN table2 ON table1.id = table2.id JOIN table3 ON table2.id = table3.id

SELECT * FROM table1, table2 WHERE table1.id = table2.id