Conditional problems in SQL revolve around retrieving, filtering, and manipulating data based on specific conditions or criteria. These problems leverage SQL’s conditional statements and clauses to dynamically filter rows, calculate values, or make decisions within queries.

Example Table:

1. SQL Query to Find Employees Earning More than 15000 (One Condition)

 Select * FROM employees
 WHERE salary > 15000  

2. SQL Query to Find Employees with a Specific Name

  SELECT * FROM employee
  WHERE name = ‘Aman’ 

3. SQL Query to Find Employees Earning More than 15000 and less than 30000 (Multimple Conditions)

 Select * FROM employee
 WHERE salary > 15000
 AND salary < 30000

Aggregate Functions are SQL functions that perform calculations on multiple rows of data and return a single summarized value.

• SUM: Calculates the total sum of a numeric column. Usage: SUM(column_name)
• MAX: Finds the maximum value in a column. Usage: MAX(column_name)
• MIN: Finds the minimum value in a column. Usage: MIN(column_name)
• AVG: Calculates the average value of a numeric column. Usage: AVG(column_name)
• COUNT: Counts the number of rows or non-null values in a column. Usage: COUNT(column_name) or COUNT(*)

Example Table:

SQL Query to Find the Average Salary

  SELECT AVG(salary)
  FROM employees 

SQL Query to Find the Total Salary Paid to Employees

  SELECT SUM(salary)
  FROM employees

SQL Query to Find the Number of Employees

 SELECT COUNT(*)
 FROM employees 

SQL Query to Find the Maximum Salary

 SELECT MAX(salary) 
 FROM employees

SQL Query to Find the Minimum Salary

 SELECT MIN(salary)
 FROM employees

Sorting in SQL is used to arrange the result set of a query in a specific order.

• Keyword: ORDER BY
• Ascending Order: ASC (default)
• Descending Order: DESC
• Applicable for any data type, such as Number, String, Date, etc.

Syntax

    SELECT * FROM table_name
    ORDER BY column_name ASC;

    SELECT * FROM table_name
    ORDER BY column_name DESC;

Sorting with Multiple Columns

To sort data based on multiple columns, you can use the ORDER BY clause with multiple columns.

    SELECT * FROM table_name
    ORDER BY column1 ASC, column2 DESC;

Note: This sorts the data in ascending order of column1. If two cells in column1 are equal, it sorts the data in descending order of column2.

Example Table: employees

Questions

    SELECT * FROM employees 
    ORDER BY salary ASC;

    SELECT * FROM employees 
    ORDER BY salary DESC;

    SELECT * FROM employees 
    ORDER BY dept ASC, salary DESC;

To find the Nth highest salary from a table, you can use several approaches in SQL, including subqueries, MAX(), COUNT(), and DISTINCT keyword.

Example Table: employees

Solutions

    SELECT MAX(salary) FROM employees 
    WHERE salary != (SELECT MAX(salary) 
    FROM employees);

    SELECT e1.salary FROM 
    employees e1 
    WHERE n-1 = (SELECT COUNT(DISTINCT e2.salary) 
    FROM employees e2
    WHERE e2.salary > e1.salary);

Find the Nth Lowest Salary (IMP)

To find the Nth lowest salary, you can use a similar approach but adjust the query to count how many salaries are less than the current salary.

    SELECT e1.salary 
    FROM employees e1
    WHERE n-1 = (SELECT COUNT(DISTINCT e2.salary)
    FROM employees e2
    WHERE e2.salary < e1.salary);

This query works by counting how many salaries are less than the current salary and when the count matches n-1, it returns the Nth lowest salary.

GROUP BY in SQL is a clause used to group rows that have the same values in specified columns into summary rows. It is commonly used with aggregate functions like SUM, COUNT, AVG, MAX, and MIN to perform operations on each group of data.

Purpose

• Grouping Rows: The GROUP BY clause in SQL is used to arrange identical data into groups.
• Aggregation: It allows performing aggregate functions like SUM(), COUNT(), AVG(), MAX(), and MIN() on each group of data.

Key Points

• Columns in SELECT: Columns in the SELECT statement must be either in the GROUP BY clause or used with an aggregate function.
• Order of Execution: GROUP BY is applied after the WHERE clause but before the ORDER BY clause.

Example Syntax

  SELECT column1, column2, aggregate_function(column3)
  FROM table_name
  GROUP BY column1, column2;

Example Table

SQL Queries

• Find the Highest Salary of Each Department:

    SELECT dept, MAX(salary)
    FROM employees
    GROUP BY dept;

• Find the Average Salary of Each Department:

    SELECT dept, AVG(salary)
    FROM employees
    GROUP BY dept;

• Find the Count of Employees in Each Department:

    SELECT dept, COUNT(*)
    FROM employees
    GROUP BY dept;

Key Points:

• NULL values represent missing or unknown data.
• Regular comparison operators (= and !=) do not work with NULL.

How to Handle NULL Values:

• Use IS NULL to check if a value is NULL.
• Use IS NOT NULL to check if a value is not NULL.

Example Table: employees

Write the query to find the record which department is NULL

    SELECT * FROM employees 
    WHERE dept IS NULL;

Write the query to find the record where salary is not NULL

    SELECT * FROM employees 
    WHERE salary IS NOT NULL;

Summary:

• NULL cannot be compared using = or !=.
• Use IS NULL and IS NOT NULL to handle NULL values in conditions.
• Remember, NULL indicates absence of value, so special handling is required in SQL queries.

Basic rule for pattern matching:

• ‘%’: Represents zero or more characters.
• ‘_’: Represents exactly one character.

Example Table: employees

1. Find all employees whose names start with the letter 'A'

  SELECT * FROM employees
  WHERE name LIKE 'A%';

2. Find all employees whose names contain the letter 'a'

  SELECT * FROM employees 
  WHERE name LIKE '%a%';

3. Find all employees whose names are exactly 5 characters long

  SELECT * FROM employees 
  WHERE name LIKE '_____';

4. Find all employees whose names start with 'A' and end with 't'

  SELECT * FROM employees
  WHERE name LIKE 'A%t';

Questions and Queries

  SELECT * 
  FROM employees 
  WHERE name REGEXP '^[^a]*a[^a]*$';

  SELECT * 
  FROM employees 
  WHERE name REGEXP '^(A|N)';

  SELECT * 
  FROM employees 
  WHERE mobile_number REGEXP '[6-9][0-9]{9}';

What is ROW_NUMBER()?

ROW_NUMBER() is a window function in SQL that assigns a unique, sequential integer to rows within a partition of a result set, starting from 1.

Example Table: employees

Query to Fetch Nth Row

  SELECT *
  FROM (
  SELECT
  id, name, salary,
  ROW_NUMBER() OVER (ORDER BY id) AS row_num
  FROM employees
  ) AS numbered_rows
  WHERE row_num = n;

Explanation

• Subquery: The inner query (SELECT ... AS numbered_rows) creates a temporary result set with row numbers assigned to each row.
• ROW_NUMBER() OVER (ORDER BY id): Generates row numbers sequentially based on the id column.
• Filter: The WHERE row_num = n retrieves only the nth row from the result set.

UNION Operator in SQL is used to combine the set of one or more SELECT statements as the resulting output. The UNION operator removes duplicates from the combined result of the set of SELECT statements.

Example:

  SELECT city FROM table1
  UNION
  SELECT city FROM table2

The conditions for a UNION statement are:

• Same number of columns
• Similar and compatible data types
• Same logical order

You can combine multiple columns as well:

  SELECT city, country FROM table1
  UNION
  SELECT city, country FROM table2

Result:

Note: When we use UNION, it removes duplicate records, while UNION ALL allows duplicate values when combining.

Example:

  SELECT city, country FROM table1
  UNION ALL
  SELECT city, country FROM table2

Result:

Joins in SQL are operations used to combine rows from two or more tables based on a related column between them.

Key Points:

• Purpose: To retrieve data from multiple tables and present it as a single result set.
• Common Column: Joins use a common column (key) that exists in both tables to match rows.
• Types: There are several types of joins, each serving different purposes (e.g., INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN, SELF JOIN).

TheINNER JOIN combine records from two tables whenever there are matching values in a field common to both tables.

• Based on the equality condition, data is retrieved from multiple tables.
• We must have the common column in both tables with the same data type.

Example: Display the emp_id, emp_name, and department_name using INNER JOIN.

  SELECT emp_id, emp_name, dept.dept_name
  FROM emp
  INNER JOIN dept 
  ON emp.dept_id = dept.dept_id;

Resulting Table:

The LEFT JOIN in SQL returns all records from the left table (emp), and the matched records from the right table (dept). If there is no match, the result is NULL on the right side.

Example

emp Table

dept Table

SQL Query

  SELECT 
  emp.emp_id,
  emp.emp_name,
  dept.dept_name
  FROM 
  emp
  LEFT JOIN 
  dept
  ON 
  emp.dept_id = dept.dept_id; 

Resulting Table

The RIGHT JOIN returns all records from the right table (dept), and the matched records from the left table (emp). If there is no match, the result is NULL on the left side.

Example

emp Table

dept Table

SQL Query

  SELECT 
  emp.emp_id,
  emp.emp_name,
  dept.dept_name
  FROM 
  emp
  RIGHT JOIN 
  dept
  ON 
  emp.dept_id = dept.dept_id; 

Resulting Table

The FULL OUTER JOIN returns all records when there is a match in either left (emp) or right (dept) table records. It returns NULL for non-matching rows on either side.

Example

emp Table

dept Table

SQL Query

  SELECT 
  emp.emp_id,
  emp.emp_name,
  dept.dept_name
  FROM 
  emp
  FULL OUTER JOIN 
  dept
  ON 
  emp.dept_id = dept.dept_id; 

Resulting Table

SELF JOIN is a regular join, but the table is joined with itself. This is useful for hierarchical data.

Example

emp Table

SQL Query

SELECT 
e1.emp_name AS Employee,
e2.emp_name AS Manager
FROM 
emp e1
LEFT JOIN 
emp e2
ON 
e1.manager_id = e2.emp_id;

Resulting Table