SQL Queries Practical

What is the basic syntax for a SELECT query in SQL?

• SELECT column1, column2 WHERE table_name;

• SELECT column1, column2 FROM table_name;

• SELECT column1 column2 FROM table_name;

• SELECT * FROM table_name;

Explain the difference between INNER JOIN and LEFT JOIN.

• INNER JOIN returns all rows from the left table

• LEFT JOIN returns only matching rows

• INNER JOIN compares and returns only matching rows, LEFT JOIN returns all rows from the left table and matched rows from the right table

• INNER JOIN combines all rows from both tables

What are some common aggregate functions in SQL?

• TOTAL

• COUNT, SUM, AVG, MIN, MAX

• AVERAGE

• SMALLEST

• BIGGEST

How can you use a subquery in a WHERE clause?

• SELECT * FROM table_name WHERE column_name = (SELECT column_name FROM another_table WHERE condition)

• SELECT * FROM table_name WHERE column_name = (SELECT column_name FROM another_table WHERE condition) ORDER BY column_name

• SELECT * FROM table_name WHERE column_name = (SELECT column_name FROM another_table WHERE condition) OR column_name = value

• SELECT * FROM table_name WHERE column_name = (SELECT column_name FROM another_table WHERE condition) AND column_name = value

Write a query to retrieve the average salary of employees in a table.

• SELECT MAX(salary) FROM employees;

• SELECT AVG(salary) FROM employees;

• SELECT SUM(salary) FROM employees;

• SELECT MIN(salary) FROM employees;

When would you use a self-join in SQL?

• When you want to insert new rows into a table

• When you need to delete rows from a table

• When you need to perform aggregate functions on a table

• When you need to compare rows within the same table.

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

• To sort rows in ascending order

• To update values in a table

• To filter rows based on a condition

• To group rows with the same values together for aggregate function calculations.

Explain the difference between DELETE and TRUNCATE commands in SQL.

• DELETE removes rows based on a condition, TRUNCATE removes all rows without any condition.

• DELETE is faster than TRUNCATE.

• TRUNCATE can be rolled back, DELETE cannot.

• DELETE removes all rows, TRUNCATE removes rows based on a condition.

Write a query to find the second highest salary in an employee table.

• SELECT salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees) + 1;

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

• SELECT MAX(salary) AS second_highest_salary FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

• SELECT salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees) - 1;

How can you update multiple rows in a table using a single SQL query?

• UPDATE table_name SET column_name = value WHERE condition;

• UPDATE table_name SET column_name = CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE default_value END WHERE condition;

• UPDATE table_name SET column_name = value1, column_name = value2 WHERE condition;

• UPDATE table_name SET column_name = value1 AND column_name = value2 WHERE condition;