Section 1: Key SQL Concepts

1. Distinguishing Between HAVING and WHERE Clauses

The HAVING clause is utilized alongside the GROUP BY clause to filter records based on aggregate functions, applied post-grouping. Conversely, the WHERE clause filters records before any grouping or aggregation. To summarize, WHERE is for filtering individual rows, while HAVING pertains to filtering grouped data.

SELECT department, COUNT(*) AS total_employees

FROM employees

GROUP BY department

HAVING total_employees > 10;

2. Understanding UNION vs. UNION ALL

The UNION operator merges the results of two or more SELECT statements, eliminating duplicates. In contrast, UNION ALL combines results without filtering out duplicates, making it generally faster since it skips the duplicate removal step. Use UNION if deduplication is necessary.

SELECT employee_id FROM employees

UNION

SELECT employee_id FROM former_employees;

SELECT employee_id FROM employees

UNION ALL

SELECT employee_id FROM former_employees;

3. Finding the nth Highest Salary in an Employee Table

To determine the nth highest salary, employ the LIMIT clause in descending order. For example, to find the 3rd highest salary:

SELECT salary

FROM employees

ORDER BY salary DESC

LIMIT 2, 1;

This skips the top 2 salaries and retrieves the next row, which reflects the 3rd highest salary.

4. Exploring Subqueries in SQL

A subquery, or nested query, is a query embedded within another. It can reside in the WHERE, FROM, or HAVING clauses, providing a result set that the outer query utilizes for conditions or data. Subqueries are instrumental for complex queries or when merging data from various tables.

SELECT employee_name

FROM employees

WHERE department_id IN (

SELECT department_id

FROM departments

WHERE region = 'North America'

);

5. Determining the Second Highest Value in a Column

To locate the second highest value, combine ORDER BY and LIMIT:

SELECT MAX(salary) AS second_highest_salary

FROM employees

WHERE salary < (

SELECT MAX(salary) FROM employees

);

6. Utilizing the COALESCE Function in SQL

The COALESCE function returns the first non-null value from a list of expressions. If all values are null, it will yield null. This function is beneficial for handling nullable columns or substituting nulls with default values.

SELECT COALESCE(salary, 0) AS final_salary

FROM employees;

7. Identifying the Most Common Value in a Column

To find the most frequent value, employ GROUP BY, COUNT, and ORDER BY:

SELECT column_name

FROM table_name

GROUP BY column_name

ORDER BY COUNT(*) DESC

LIMIT 1;

8. Understanding the LIKE Operator in SQL

The LIKE operator, used in the WHERE clause, enables pattern searching within a column. It supports wildcard characters: % matches any sequence of characters, while _ matches a single character.

SELECT column_name

FROM table_name

WHERE column_name LIKE 'A%';

9. The CASE Statement in SQL

The CASE statement facilitates conditional logic within SQL queries, yielding different outputs based on established conditions. It can be presented in simple or searched forms.

SELECT column_name,

CASE column_name

WHEN value1 THEN 'Result1'

WHEN value2 THEN 'Result2'

ELSE 'Result3'

END

FROM table_name;

10. Different Types of SQL Joins

SQL supports multiple join types for merging rows from various tables based on shared columns:

• INNER JOIN: Returns only matching rows from both tables.

  • LEFT JOIN: Returns all rows from the left table and matching rows from the right.
  • RIGHT JOIN: Returns all rows from the right table and matching rows from the left.
  • FULL OUTER JOIN: Combines results from both left and right joins.

SELECT *

FROM employees

INNER JOIN departments ON employees.department_id = departments.department_id;

11. Finding Duplicate Rows in a Table

To locate duplicates, use GROUP BY and HAVING with COUNT:

SELECT column1, column2, COUNT(*)

FROM table_name

GROUP BY column1, column2

HAVING COUNT(*) > 1;

12. The Purpose of INDEX in SQL

An INDEX enhances data retrieval speed on database tables by maintaining a sorted copy of data and pointers to the original rows. While beneficial for query performance, indexes require additional disk space and can slow down modifications (inserts, updates, deletes).

CREATE INDEX index_name

ON table_name (column1, column2);

13. Primary Key vs. Foreign Key

A primary key uniquely identifies each row in a table, ensuring data integrity. In contrast, a foreign key refers to a primary key in another table, establishing a relationship between two tables.

14. Calculating the Average of a Column in SQL

To compute the average, utilize the AVG function:

SELECT AVG(salary) AS average_salary

FROM employees;

15. The GROUP BY Clause

The GROUP BY clause groups rows based on specified columns, commonly used with aggregate functions.

SELECT department, COUNT(*) AS total_employees

FROM employees

GROUP BY department;

16. Retrieving Current Date and Time in SQL

Various database systems offer functions for fetching the current date and time. Examples include:

• MySQL: SELECT CURDATE(), CURTIME();

  • PostgreSQL: SELECT CURRENT_DATE, CURRENT_TIME;
  • SQL Server: SELECT GETDATE();
  • Oracle: SELECT SYSDATE FROM DUAL;

17. Views vs. Tables

A table is a fundamental database structure that stores data in rows and columns, while a view is a virtual table created from one or more tables. Views do not store data but are defined by queries, offering a way to simplify complex queries and protect sensitive information.

18. Deleting Duplicate Rows from a Table

To remove duplicates, use DELETE with a subquery that identifies duplicates:

DELETE FROM table_name

WHERE (column1, column2) IN (

SELECT column1, column2

FROM table_name

GROUP BY column1, column2

HAVING COUNT(*) > 1

);

19. Purpose of the TRUNCATE Statement

The TRUNCATE statement removes all rows from a table quickly, differing from DELETE, which processes rows individually. TRUNCATE cannot be rolled back and resets auto-increment values.

TRUNCATE TABLE table_name;

20. Finding the Length of a String in SQL

The method for string length varies by database system:

• MySQL: SELECT LENGTH(string);

  • PostgreSQL: SELECT LENGTH(string);
  • SQL Server: SELECT LEN(string);
  • Oracle: SELECT LENGTH(string) FROM DUAL;

21. ACID Properties in Database Transactions

ACID ensures database transactions' reliability:

• Atomicity: Transactions are all-or-nothing.
• Consistency: Transactions maintain data integrity.
• Isolation: Transactions operate independently.
• Durability: Committed changes are permanent.

22. Concatenating Strings in SQL

To concatenate strings, use the specific operator for your database system:

• MySQL: SELECT CONCAT(string1, string2);

  • PostgreSQL: SELECT string1 || string2;
  • SQL Server: SELECT string1 + string2;
  • Oracle: SELECT string1 || string2 FROM DUAL;

23. Using the ROLLBACK Statement

The ROLLBACK statement undoes changes made in a transaction:

BEGIN TRANSACTION;

-- Perform operations

IF condition THEN

ROLLBACK;

ELSE

COMMIT;

END IF;

24. Calculating Date Differences in SQL

To find the difference between two dates, use date difference functions:

SELECT DATEDIFF(day, start_date, end_date) AS date_difference

FROM table_name;

25. Performing Pagination in SQL

Pagination limits the number of rows returned by a query. Syntax varies by database:

SELECT column1, column2

FROM table_name

LIMIT 10 OFFSET 20;