SQL

1. SQL Introduction

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases.

2. SQL Syntax

SQL follows a declarative syntax, and it is used to query and modify data.

Example:

SELECT * FROM employees;

3. SQL SELECT

The SELECT statement retrieves data from a database.

Example:

SELECT first_name, last_name FROM employees;

4. SQL SELECT DISTINCT

Retrieves unique records.

Example:

SELECT DISTINCT department FROM employees;

5. UNIQUE

The UNIQUE constraint ensures all values in a column are different.

Example:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

6. SQL WHERE

Filters records based on specified conditions.

Example:

SELECT * FROM employees WHERE department = 'HR';

7. Comments

SQL allows comments in queries for clarity.

Example:

-- This is a single-line comment
SELECT * FROM employees;  -- Inline comment

8. Column Aliasing

Renaming a column temporarily in the result set.

Example:

SELECT first_name AS "First Name", last_name AS "Last Name" FROM employees;

9. SQL ORDER BY

Sorts the result set by one or more columns.

Example:

SELECT * FROM employees ORDER BY last_name ASC;

10. Relational Operators

These are used to compare values.

Example:

SELECT * FROM employees WHERE salary > 50000;

11. SQL AND

Combines multiple conditions in a WHERE clause.

Example:

SELECT * FROM employees WHERE department = 'HR' AND salary > 40000;

12. SQL OR

Used to combine multiple conditions where at least one must be true.

Example:

SELECT * FROM employees WHERE department = 'HR' OR department = 'Finance';

13. SQL NOT

Reverses a condition.

Example:

SELECT * FROM employees WHERE NOT department = 'HR';

14. Dual Table

A special table in Oracle used for queries that don’t require a real table.

Example:

SELECT SYSDATE FROM dual;

15. Single and Multiple Column Sorting

Sorting results by one or more columns.

Example:

SELECT * FROM employees ORDER BY department, last_name;

16. SQL INSERT INTO

Adds a new row to a table.

Example:

INSERT INTO employees (employee_id, first_name, last_name) 
VALUES (101, 'John', 'Doe');

17. SQL NULL Values

Represents missing or unknown values.

Example:

SELECT * FROM employees WHERE email IS NULL;

18. SQL UPDATE

Modifies existing records in a table.

Example:

UPDATE employees SET salary = 60000 WHERE employee_id = 101;

19. SQL DELETE

Removes rows from a table.

Example:

DELETE FROM employees WHERE employee_id = 101;

20. SQL LIKE Operator

Searches for a specified pattern.

Example:

SELECT * FROM employees WHERE email LIKE '%gmail.com';

21. SQL Wildcards

Used with the LIKE operator for pattern matching.

Example:

SELECT * FROM employees WHERE first_name LIKE 'J_n';

22. SQL IN

Specifies multiple values in a WHERE clause.

Example:

SELECT * FROM employees WHERE department IN ('HR', 'Finance');

23. SQL BETWEEN

Selects values within a range.

Example:

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;

24. SQL Aliases

Temporary names given to tables or columns.

Example:

SELECT e.first_name, e.last_name FROM employees AS e;

25. SQL Aggregate Functions

Functions that perform a calculation on a set of values.

Example:

SELECT COUNT(*), AVG(salary) FROM employees;

26. SQL Min and Max

Returns the smallest or largest value in a set.

Example:

SELECT MIN(salary), MAX(salary) FROM employees;

27. SQL COUNT

Returns the number of rows.

Example:

SELECT COUNT(*) FROM employees;

28. SQL SUM

Adds up values in a column.

Example:

SELECT SUM(salary) FROM employees;

29. SQL AVG

Returns the average value.

Example:

SELECT AVG(salary) FROM employees;

30. SQL Variance

Measures how much values in a dataset vary.

Example:

SELECT VARIANCE(salary) FROM employees;

31. SQL Standard Deviation

Returns the standard deviation of a set of values.

Example:

SELECT STDDEV(salary) FROM employees;

32. SQL GROUP BY Single and Multiple Column

Groups records that have the same values in specified columns.

Example:

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

33. SQL HAVING

Filters groups after applying GROUP BY.

Example:

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;

34. Scalar Functions

Operate on a single value and return a single value.

Example:

SELECT UPPER(first_name) FROM employees;

35. Numeric Functions

Functions that operate on numeric values.

Example:

SELECT ROUND(salary, 2) FROM employees;

36. String Functions

Functions that operate on strings.

Example:

SELECT CONCAT(first_name, ' ', last_name) FROM employees;

37. Date Time Functions

Functions for manipulating dates and times.

Example:

SELECT SYSDATE FROM dual;

38. Conversion Functions

Convert one data type to another.

Example:

SELECT TO_CHAR(salary) FROM employees;

39. Tables

A collection of data organized into rows and columns.

Example:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

40. SQL CREATE TABLE

Creates a new table in the database.

Example:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

41. SQL DROP TABLE

Deletes a table and its data.

Example:

DROP TABLE employees;

42. SQL ALTER TABLE

Modifies an existing table.

Example:

ALTER TABLE employees ADD email VARCHAR(255);

43. Primary Key

Uniquely identifies each record in a table.

Example:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50)
);

44. Foreign Key

A key used to link two tables together.

Example:

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50)
);
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

45. SQL Constraints

Rules applied to data columns to ensure data integrity.

Example:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL
);

46. SQL NOT NULL

Ensures that a column cannot have NULL values.

Example:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL
);

47. SQL CHECK

Specifies a condition that must be met for the value in the column.

Example:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  salary INT CHECK (salary > 0)
);

48. SQL DEFAULT

Provides a default value for a column.

Example:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  department VARCHAR(50) DEFAULT 'HR'
);

49. SQL Joins

Combines rows from two or more tables based on a related column.

50. SQL INNER JOIN

Returns records with matching values in both tables.

Example:

SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;

51. SQL LEFT JOIN

Returns all records from the left table and matched records from the right table.

Example:

SELECT * FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;

52. SQL RIGHT JOIN

Returns all records from the right table and matched records from the left table.

Example:

SELECT * FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;

53. SQL FULL JOIN

Returns records when there is a match in either left or right table.

Example:

SELECT * FROM employees FULL JOIN departments ON employees.department_id = departments.department_id;

54. Cross Join

Returns the Cartesian product of two tables.

Example:

SELECT * FROM employees CROSS JOIN departments;

55. SQL Self Join

A join where a table is joined with itself.

Example:

SELECT e1.first_name AS Employee, e2.first_name AS Manager 
FROM employees e1, employees e2 
WHERE e1.manager_id = e2.employee_id;

56. Natural Join

Automatically joins tables based on columns with the same name.

Example:

SELECT * FROM employees NATURAL JOIN departments;

57. Set Operators

Combine results from two or more queries.

58. SQL UNION

Combines results of two queries and removes duplicates.

Example:

SELECT department FROM employees 
UNION
SELECT department FROM departments;

59. INTERSECT

Returns common records between two queries.

Example:

SELECT department FROM employees 
INTERSECT
SELECT department FROM departments;

60. MINUS

Returns records from the first query that aren't in the second query.

Example:

SELECT department FROM employees 
MINUS
SELECT department FROM departments;

61. SQL CASE

Performs conditional logic.

Example:

SELECT employee_id, 
  CASE 
    WHEN salary > 50000 THEN 'High'
    ELSE 'Low' 
  END AS salary_category
FROM employees;

62. Sub Queries

A query inside another query.

Example:

SELECT * FROM employees 
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'HR');

63. SQL Views

A virtual table representing a query.

Example:

CREATE VIEW employee_view AS
SELECT * FROM employees WHERE department = 'HR';

64. Synonyms

An alias for a table or view.

Example:

CREATE SYNONYM emp FOR employees;

65. Indexes

Improves the speed of retrieval operations.

Example:

CREATE INDEX idx_employee_name ON employees(last_name);

66. Sequences

Generates unique numbers.

Example:

CREATE SEQUENCE emp_seq START WITH 1;

67. Data Dictionary

Metadata about database objects.

68. Analytical Functions

Provides advanced statistical or ranking calculations.

69. Ranking Functions

Includes ROW_NUMBER, RANK, DENSE_RANK.

Example:

SELECT employee_id, salary, 
  RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Ranking functions in SQL are a type of window function that assign a rank to each row in a result set. They are often used to assign ranks, row numbers, or dense ranks to data, based on a specific order of rows. The ranking is done over a specified partition or over the entire dataset. These functions are useful for reporting and analysis, especially when working with large datasets and needing to rank or number rows dynamically.

Here are the main ranking functions in SQL:

70. ROW_NUMBER()

• This function assigns a unique sequential number to each row within a partition of a result set, starting from 1.
• The numbering restarts for each partition (if partitioned by a specific column).

Syntax:

ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name)

Example:

SELECT employee_id, first_name, salary,
   ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

• This query will assign a unique row number to each employee based on their salary in descending order.

71. RANK()

• The RANK() function also assigns a rank to each row, but if two rows have the same value (e.g., same salary), they will receive the same rank. However, the next row will be skipped (i.e., the rank is not consecutive).

Syntax:

RANK() OVER (PARTITION BY column_name ORDER BY column_name)

Example:

SELECT employee_id, first_name, salary,
   RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

• This query assigns ranks to employees based on their salary. If two employees have the same salary, they will receive the same rank, and the next rank will be skipped. For example, if two employees are ranked 1st, the next employee will be ranked 3rd.

72. DENSE_RANK()

• DENSE_RANK() works similarly to RANK(), but it does not leave gaps in the ranking. If two rows have the same value, they get the same rank, and the next row will have the very next rank (without skipping).

Syntax:

DENSE_RANK() OVER (PARTITION BY column_name ORDER BY column_name)

Example:

SELECT employee_id, first_name, salary,
   DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

• In this example, employees are ranked based on their salary. If two employees have the same salary, they will receive the same rank, and the next employee will receive the next consecutive rank without any gaps.

73. NTILE()

• The NTILE() function divides the result set into a specified number of roughly equal parts (or buckets). It assigns a bucket number to each row. The number of buckets must be provided as an argument to NTILE().

Syntax:

NTILE(n) OVER (PARTITION BY column_name ORDER BY column_name)

• Where n is the number of buckets you want to divide your result set into.

Example:

SELECT employee_id, first_name, salary,
   NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

• This query divides the employees into 4 groups (quartiles) based on their salary. The result will show which quartile each employee belongs to, with 1 being the highest salary group.