Dive in SQL

Which SQL keyword is used to retrieve only unique values from a column?

• DISTINCT

• UNIQUE

• LIMIT

• GROUP BY

What does the INNER JOIN operation return?

• a) All rows from the left table

• All rows from the right table

• Rows that have matching values in both tables

• Non-matching rows from both tables

Given the following SQL query, what will be the result of the subquery?

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

• All employees with salaries less than the average

• All employees with salaries greater than the average

• An error

Which command is used to remove all rows from a table but keep the structure intact?

• DELETE

• DROP

• TRUNCATE

• REMOVE

Which of the following SQL clauses is used to specify the conditions under which a group is included in the result set of a GROUP BY statement?

(a)  

What is the effect of using RANK() in a SQL query as opposed to ROW_NUMBER()?

• RANK() skips ranks when there are ties, while ROW_NUMBER() does not.

• ROW_NUMBER() skips ranks when there are ties, while RANK() does not.

• Both RANK() and ROW_NUMBER() produce identical results.

• RANK() and ROW_NUMBER() can be used interchangeably without any difference.

Which of the following SQL queries would return duplicate rows in its result set?

• SELECT * FROM products;

• SELECT DISTINCT * FROM products;

• SELECT product_id, COUNT(*)

  FROM products

  GROUP BY product_id;

• SELECT * FROM products GROUP BY product_id;

Which query returns the total sales amount for each product but includes only products where the total sales exceed $1000?

• SELECT product_id, COUNT(amount)

  FROM sales

  GROUP BY product_id

  HAVING COUNT(amount) > 1000;

• SELECT product_id, SUM(amount)

  FROM sales

  WHERE SUM(amount) > 1000

  GROUP BY product_id;

• SELECT product_id, SUM(amount)

  FROM sales

  GROUP BY product_id

  HAVING SUM(amount) > 1000;

• SELECT product_id, SUM(amount)

  FROM sales

  GROUP BY product_id

  WHERE SUM(amount) > 1000;

What is the purpose of a window function in SQL?

• To perform row-level operations.

• To apply aggregate functions over a specified range of rows.

• To calculate the number of rows in a table.

• To delete rows in a partitioned query.

Which SQL query will return the second-highest salary from the employees table?

• SELECT MAX(salary)

  FROM employees

  WHERE salary < (SELECT MAX(salary) FROM employees);

• SELECT MAX(salary)

  FROM employees

  ORDER BY salary DESC

  LIMIT 1, 1;

• SELECT salary

  FROM employees

  ORDER BY salary DESC

  LIMIT 1 OFFSET 1;

• SELECT salary

  FROM employees

  WHERE salary < MAX(salary);