Database Design & Development SQL 2

Given the tables "students" with columns "student_id", "name", and "grade", and "courses" with columns "course_id", "student_id", and "course_name", which SQL statement retrieves the names of students who are enrolled in at least one course?

• SELECT name FROM students WHERE student_id IN (SELECT student_id FROM courses);

• SELECT name FROM students JOIN courses ON students.student_id = courses.student_id;

• SELECT name FROM students, courses WHERE students.student_id = courses.student_id;

• SELECT name FROM students WHERE EXISTS (SELECT student_id FROM courses WHERE students.student_id = courses.student_id);

How would you write a query to find the second highest salary from a table named "employees" with a column "salary"?

• SELECT MAX(salary) FROM employees WHERE salary (SELECT AVG(price) FROM products);

• SELECT product_name FROM products WHERE price > ALL (SELECT AVG(price) FROM products);

• SELECT product_name FROM products WHERE price > MAX(price);

• SELECT product_name FROM products WHERE price > (SELECT TOTAL(price) FROM products);

In a table "employees" with columns "employee_id", "name", and "manager_id", which SQL query retrieves the names of employees who do not have a manager?

• SELECT name FROM employees WHERE manager_id IS NULL;

• SELECT name FROM employees WHERE manager_id = 0;

• SELECT name FROM employees WHERE manager_id = '';

• SELECT name FROM employees WHERE manager_id IS NOT NULL;

Which SQL statement retrieves the top 3 highest salaries from a table "salaries" with a column "amount"?

• SELECT amount FROM salaries ORDER BY amount DESC LIMIT 3;

• SELECT TOP 3 amount FROM salaries ORDER BY amount DESC;

• SELECT amount FROM salaries WHERE amount IN (SELECT amount FROM salaries ORDER BY amount DESC LIMIT 3);

• SELECT amount FROM salaries ORDER BY amount DESC FETCH FIRST 3 ROWS ONLY;

In a table "sales" with columns "sale_id", "product_id", and "sale_date", which SQL statement finds the most recent sale date?

• SELECT MAX(sale_date) FROM sales;

• SELECT sale_date FROM sales ORDER BY sale_date DESC LIMIT 1;

• SELECT TOP 1 sale_date FROM sales ORDER BY sale_date DESC;

• SELECT sale_date FROM sales WHERE sale_date = (SELECT MAX(sale_date) FROM sales);

How would you write a query to find all customers who have placed more than 5 orders from a table "orders" with columns "order_id" and "customer_id"?

• SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(order_id) > 5;

• SELECT customer_id FROM orders WHERE COUNT(order_id) > 5;

• SELECT customer_id FROM orders WHERE order_id > 5;

• SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(DISTINCT order_id) > 5;

Given a table "inventory" with columns "item_id", "item_name", and "quantity", which SQL statement updates the quantity of all items to zero?

• UPDATE inventory SET quantity = 0;

• UPDATE inventory SET quantity = '0';

• UPDATE inventory SET quantity TO 0;

• UPDATE inventory SET quantity = NULL;

Which SQL query lists the names of employees who have the same salary as the employee with the name 'John' from a table "employees" with columns "name" and "salary"?

• SELECT name FROM employees WHERE salary = (SELECT salary FROM employees WHERE name = 'John');

• SELECT name FROM employees WHERE salary IN (SELECT salary FROM employees WHERE name = 'John');

• SELECT name FROM employees WHERE salary = (SELECT salary FROM employees WHERE name = 'John') AND name != 'John';

• SELECT name FROM employees WHERE salary = ALL (SELECT salary FROM employees WHERE name = 'John');

How would you write a query to find the longest name from a table "customers" with a column "customer_name"?

• SELECT customer_name FROM customers ORDER BY LENGTH(customer_name) DESC LIMIT 1;

• SELECT customer_name FROM customers WHERE LENGTH(customer_name) = (SELECT MAX(LENGTH(customer_name)) FROM customers);

• SELECT customer_name FROM customers WHERE LENGTH(customer_name) = (SELECT MAX(LENGTH(customer_name)) FROM customers) LIMIT 1;

• SELECT MAX(LENGTH(customer_name)), customer_name FROM customers;

In a table "projects" with columns "project_id", "project_name", and "start_date", which SQL statement finds projects that started in the year 2022?

• SELECT project_name FROM projects WHERE start_date BETWEEN '2022-01-01' AND '2022-12-31';

• SELECT project_name FROM projects WHERE start_date LIKE '2022%';

• SELECT project_name FROM projects WHERE YEAR(start_date) = 2022;

• SELECT project_name FROM projects WHERE start_date >= '2022-01-01' AND start_date <= '2022-12-31';