SQL Quiz - MCQ - Multiple Choice Questions

Q. Which SQL keyword is used to extract data from a database table?

A. SELECT
B. INSERT
C. UPDATE
D. DELETE

Q. What is the purpose of the WHERE clause in an SQL query?

A. To specify the table from which to retrieve data
B. To sort the result set in ascending order
C. To filter the rows based on a specified condition
D. To calculate the average of a numeric column

Q. How do you insert data into a table using SQL?

A. SELECT INTO table_name values (value1, value2, ...);
B. INSERT INTO table_name (column1, column2, ...) values (value1, value2, ...);
C. UPDATE table_name SET column1 = value1, column2 = value2, ...;
D. DELETE FROM table_name WHERE condition;

Q. What is a primary key in SQL?

A. A column that uniquely identifies each row in a table
B. A column that stores the date and time when a record was created
C. A column that is automatically incremented for each new row
D. A column that cannot contain null values

Q. How do you update data in a table using SQL?

A. SELECT * FROM table_name WHERE condition;
B. INSERT INTO table_name (column1, column2, ...) values (new_value1, new_value2, ...);
C. UPDATE table_name SET column1 = new_value1, column2 = new_value2, ... WHERE condition;
D. DELETE FROM table_name WHERE condition;

Q. What is the purpose of the GROUP BY clause in SQL?

A. To sort the result set in ascending order
B. To filter the rows based on a specified condition
C. To group rows based on the values in one or more columns
D. To calculate the sum of a numeric column

Q. What is a foreign key in SQL?

A. A column that uniquely identifies each row in a table
B. A column that references the primary key of another table
C. A column that stores the date and time when a record was updated
D. A column that cannot contain duplicate values

Q. How do you delete data from a table using SQL?

A. SELECT * FROM table_name WHERE condition;
B. INSERT INTO table_name (column1, column2, ...) values (value1, value2, ...);
C. UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
D. DELETE FROM table_name WHERE condition;

Q. What is a join in SQL?

A. A method to combine rows from two or more tables based on a related column
B. A way to filter data based on multiple conditions
C. A technique to sort data in ascending or descending order
D. A function to calculate the sum of values in a column

Q. What is the purpose of the HAVING clause in SQL?

A. To filter groups based on a specified condition
B. To sort the result set in ascending order
C. To calculate the average of a numeric column
D. To group rows based on the values in one or more columns

Q. How do you create a new table in SQL?

A. SELECT INTO table_name FROM source_table;
B. INSERT INTO table_name (column1, column2, ...) values (value1, value2, ...);
C. CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
D. UPDATE table_name SET column1 = value1, column2 = value2, ...;

Q. What is normalization in SQL?

A. The process of organizing data to minimize redundancy and improve data integrity
B. The technique of sorting data in ascending or descending order
C. The method of filtering data based on multiple conditions
D. The function of calculating the sum of values in a column

Q. How do you select distinct values from a column in SQL?

A. SELECT DISTINCT column_name FROM table_name;
B. SELECT ALL column_name FROM table_name;
C. SELECT UNIQUE column_name FROM table_name;
D. SELECT DIFFERENT column_name FROM table_name;

Q. What is a subquery in SQL?

A. A query within another query
B. A query that returns multiple result sets
C. A query that uses aggregate functions
D. A query that combines data from multiple tables

Q. How do you retrieve data from multiple tables using SQL?

A. By using a subquery for each table
B. By using the UNION operator to combine queries
C. By using the JOIN clause to combine tables
D. By using the GROUP BY clause to group tables

Which of the following SQL queries would you use to retrieve all the distinct cities from the "customers" table?

SELECT City
FROM customers;

SELECT DISTINCT City
FROM customers;

SELECT UNIQUE City
FROM customers;

SELECT City
FROM customers
GROUP BY City;

You have a table named "orders" with columns "order_id," "customer_id," and "order_date." How can you retrieve orders placed by a specific customer (e.g., customer_id = 123) after a certain date (e.g., '2023-01-01')?

SELECT *
FROM orders
WHERE customer_id = 123 AND order_date > '2023-01-01';

SELECT *
FROM orders
WHERE customer = 123 AND date(order_date) > '2023-01-01';

SELECT *
FROM orders
WHERE customer_id = '123' AND order_date >= '2023-01-01';

SELECT *
FROM orders
WHERE customer_id = 123
AND order_date BETWEEN '2023-01-01' AND '2023-12-31';

You have a table named "employees" with columns "employee_id," "first_name," "last_name," and "salary." How can you calculate the average salary of employees with a specific job title (e.g., 'Manager')?

SELECT AVG(salary)
FROM employees
WHERE job_title = 'Manager';

SELECT job_title, AVG(salary)
FROM employees
GROUP BY job_title
HAVING job_title = 'Manager';

SELECT job_title, AVG(salary) AS average_salary
FROM employees
GROUP BY job_title
ORDER BY average_salary DESC;

SELECT job_title, average_salary
FROM (
    SELECT job_title, AVG(salary) AS average_salary
    FROM employees
    GROUP BY job_title
) AS salary_avg
WHERE job_title = 'Manager';

You have a table named "products" with columns "product_id," "product_name," and "price." How can you find the most expensive product in each category, assuming there is a "categories" table with "category_id" and "category_name" columns?

SELECT p.product_name, p.price
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
WHERE p.price = (
    SELECT MAX(price)
    FROM products
    WHERE category_id = c.category_id
);

SELECT p.product_name, p.price
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
WHERE p.price = (
    SELECT MAX(price)
    FROM products
    GROUP BY category_id
);

SELECT p.product_name, p.price
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
WHERE (SELECT MAX(price) FROM products WHERE category_id = c.category_id) = p.price;

SELECT p.product_name, p.price
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
WHERE p.price = (
    SELECT MAX(p2.price)
    FROM products p2
    WHERE p2.category_id = c.category_id
);

You have a table named "students" with columns "student_id," "first_name," "last_name," and "grade." How can you retrieve the names of students who have a higher grade than a specific student (e.g., student_id = 101) in the same class?

SELECT first_name, last_name
FROM students
WHERE grade > (
    SELECT grade
    FROM students
    WHERE student_id = 101
);

SELECT first_name, last_name
FROM students s1
WHERE grade > (
    SELECT grade
    FROM students s2
    WHERE s2.student_id = 101 AND s1.class_id = s2.class_id
);

SELECT s1.first_name, s1.last_name
FROM students s1, students s2
WHERE s1.grade > s2.grade AND s2.student_id = 101;

SELECT first_name, last_name
FROM students
WHERE grade > (
    SELECT MAX(grade)
    FROM students
    WHERE student_id <> 101
);

You have a table named "books" with columns "book_id," "title," "author," and "publication_year." How can you retrieve books published in the last 5 years?

SELECT *
FROM books
WHERE publication_year >= YEAR(CURDATE()) - 5;

SELECT *
FROM books
WHERE publication_year >= DATE_SUB(CURDATE(), INTERVAL 5 YEAR);

SELECT *
FROM books
WHERE publication_year >= DATEADD(YEAR, -5, GETDATE());

SELECT *
FROM books
WHERE publication_year >= CURRENT_DATE - INTERVAL '5 years';

You have a table named "employees" with columns "employee_id," "first_name," "last_name," "manager_id," and "salary." How can you retrieve the names of employees who earn more than their managers?

SELECT e.first_name, e.last_name
FROM employees e
WHERE e.salary > (
    SELECT m.salary
    FROM employees m
    WHERE e.manager_id = m.employee_id
);

SELECT e.first_name, e.last_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

SELECT e.first_name, e.last_name
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > ANY (SELECT salary FROM employees WHERE employee_id = e.manager_id);

SELECT e.first_name, e.last_name
FROM employees e
WHERE e.salary > ALL (SELECT salary FROM employees WHERE employee_id = e.manager_id);

You have a table named "sales" with columns "sale_id," "product_id," "quantity," and "sale_date." How can you calculate the total quantity of products sold for each month in the year 2023?

SELECT MONTH(sale_date) AS month, SUM(quantity) AS total_quantity
FROM sales
WHERE YEAR(sale_date) = 2023
GROUP BY MONTH(sale_date);

SELECT EXTRACT(MONTH FROM sale_date) AS month, SUM(quantity) AS total_quantity
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY EXTRACT(MONTH FROM sale_date);

SELECT TO_CHAR(sale_date, 'MM') AS month, SUM(quantity) AS total_quantity
FROM sales
WHERE EXTRACT(YEAR FROM sale_date) = 2023
GROUP BY TO_CHAR(sale_date, 'MM');

SELECT MONTH(sale_date) AS month, SUM(quantity)
FROM sales
WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01'
GROUP BY MONTH;

Search Tutorials