SQL Interview Questions Commonly Asked for QA

Basic SQL Queries:

  Fetch all columns from a table:

   SELECT * FROM table_name;

  Get distinct values from a column:

   SELECT DISTINCT column_name FROM table_name;

  Retrieve top N records from a table:

   SELECT * FROM table_name LIMIT N;

Filtering and Sorting:

  Filter rows where a column equals a value:

   SELECT * FROM table_name WHERE column_name = value;

  Filter rows within a range:

   SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

  Retrieve rows with NULL values in a column:

   SELECT * FROM table_name WHERE column_name IS NULL;

  Sort result set in ascending/descending order:

   SELECT * FROM table_name ORDER BY column_name ASC/DESC;

Aggregate Functions:

  Count total rows:

   SELECT COUNT(*) FROM table_name;

  Calculate average, sum, min, max:

   SELECT AVG(column_name), SUM(column_name), MIN(column_name), MAX(column_name) FROM table_name;

  Group and calculate aggregates:

   SELECT column_name, AVG(salary) FROM table_name GROUP BY column_name;

Joins:

  INNER, LEFT, RIGHT, FULL joins explained:

   INNER: Retrieve common rows from both tables.

   LEFT: All rows from the left table and matching rows from the right.

   RIGHT: All rows from the right table and matching from the left.

   FULL: All rows from both tables.

  Retrieve data from multiple tables:

   SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name;

Subqueries:

  Using a subquery to retrieve data:

   SELECT * FROM table_name WHERE column_name IN (SELECT column_name FROM another_table);

  Comparing values between tables:

   SELECT * FROM table1 WHERE column_name = (SELECT column_name FROM table2 WHERE condition);

Data Modification:

  Insert new record:

   INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

  Update records:

   UPDATE table_name SET column_name = new_value WHERE condition;

  Delete records:

   DELETE FROM table_name WHERE condition;

Table Design and Constraints:

  Primary key vs. Foreign key differences:

   Primary key uniquely identifies a record, while a foreign key links to another table's primary key.

  Design a table schema:

   Create table with appropriate columns, primary keys, and foreign keys.

Advanced Queries:

  Retrieve the nth highest (or lowest) value:

   SELECT column_name FROM table_name ORDER BY column_name DESC LIMIT n-1, 1;