SQL Queries Quiz

Consider a table named 'students' with columns 'id', 'name', and 'age'. Write a query to retrieve all the students whose age is greater than 18.

• SELECT * FROM students WHERE age < 18;

• SELECT * FROM students WHERE age >= 18;

• SELECT * FROM students WHERE age > 18;

• SELECT * FROM students WHERE age = 18;

Given two tables 'orders' and 'customers' with a common column 'customer_id', write a query to retrieve all the orders along with the customer details (name, email) for each order.

• SELECT orders.*, customers.name, customers.email FROM orders JOIN customers ON orders.customer_id = customers.customer_id

• SELECT orders.*, customers.name, customers.email FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id

• SELECT orders.*, customers.name, customers.email FROM orders RIGHT JOIN customers ON orders.customer_id = customers.customer_id

• SELECT * FROM orders, customers WHERE orders.customer_id = customers.customer_id

Consider a table named 'employees' with columns 'id', 'name', 'department_id', and a table named 'departments' with columns 'id' and 'name'. Write a query to retrieve the names of all employees along with their corresponding department names.

• SELECT employees.name, departments.name FROM employees LEFT JOIN departments ON employees.department_id = departments.id

• SELECT employees.name, departments.name FROM employees JOIN departments ON employees.department_id = departments.id

• SELECT employees.name, departments.name FROM employees WHERE employees.department_id = departments.id

• SELECT employees.name, departments.name FROM employees INNER JOIN departments ON employees.department_id = departments.id WHERE employees.department_id IS NOT NULL

Given a table named 'products' with columns 'id', 'name', and 'price', write a query to retrieve the product with the highest price.

• SELECT * FROM products WHERE price = (SELECT AVG(price) FROM products)

• SELECT * FROM products WHERE price = (SELECT MIN(price) FROM products)

• SELECT * FROM products WHERE price = (SELECT SUM(price) FROM products)

• SELECT * FROM products WHERE price = (SELECT MAX(price) FROM products)

Consider a table named 'students' with columns 'id', 'name', and 'age'. Write a query to retrieve the names of all students whose name starts with the letter 'A'.

• SELECT name FROM students WHERE name LIKE '_A%'

• SELECT name FROM students WHERE name LIKE 'A%'

• SELECT name FROM students WHERE name LIKE 'A_'

• SELECT name FROM students WHERE name LIKE '%A'

Given two tables 'orders' and 'customers' with a common column 'customer_id', write a query to retrieve the total number of orders for each customer.

• SELECT customers.customer_id, COUNT(orders.order_id) AS total_orders FROM customers JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customers.customer_id

• SELECT customers.customer_id, COUNT(orders.order_id) AS total_orders FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customers.customer_id

• SELECT customers.customer_id, COUNT(orders.order_id) AS total_orders FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customers.customer_id

• SELECT customers.customer_id, COUNT(orders.order_id) AS total_orders FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customers.customer_id

Consider a table named 'employees' with columns 'id', 'name', 'department_id', and a table named 'departments' with columns 'id' and 'name'. Write a query to retrieve the names of all departments along with the count of employees in each department.

• SELECT departments.name, COUNT(*) FROM departments JOIN employees ON departments.id = employees.department_id GROUP BY departments.name

• SELECT departments.name, COUNT(employees.id) FROM departments JOIN employees ON departments.id = employees.department_id

• SELECT departments.name, COUNT(employees.id) FROM departments LEFT JOIN employees ON departments.id = employees.department_id GROUP BY departments.name

• SELECT departments.name, COUNT(employees.id) FROM departments JOIN employees ON departments.id = employees.department_id GROUP BY departments.name

Given a table named 'products' with columns 'id', 'name', and 'price', write a query to retrieve the average price of all products.

• SELECT SUM(price) FROM products;

• SELECT AVG(price) FROM products;

• SELECT MAX(price) FROM products;

• SELECT MIN(price) FROM products;

Consider a table named 'students' with columns 'id', 'name', and 'age'. Write a query to retrieve the names of all students whose age is between 20 and 25 (inclusive).

• SELECT name FROM students WHERE age = 20 OR age = 25

• SELECT name FROM students WHERE age > 20 AND age < 25

• SELECT name FROM students WHERE age BETWEEN 20 AND 25

• SELECT name FROM students WHERE age < 20 OR age > 25

Given two tables 'orders' and 'customers' with a common column 'customer_id', write a query to retrieve the customer details (name, email) for all the customers who have placed at least one order.

• SELECT customers.name, customers.email FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id

• SELECT customers.name, customers.email FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id

• SELECT customers.name, customers.email FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id

• SELECT customers.name, customers.email FROM customers JOIN orders ON customers.customer_id = orders.customer_id