55 Essential SQL Functions with Sample Codes & Output

Here's the comprehensive list of function names and sample code & output mentioned in this blog

SELECT
INSERT
UPDATE
DELETE
TRUNCATE
COUNT
SUM
AVG
MIN
MAX
GROUP BY
HAVING
ORDER BY
LIKE
IN
AND
OR
NOT
UNION
JOIN (including INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN)
EXCEPT
EXPLAIN
VACUUM
TO_CHAR
TO_DATE
TO_NUMBER
STRING_AGG
LENGTH
UPPER
LOWER
REPLACE
TRIM
LTRIM
RTRIM
CONCAT
SUBSTRING
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
AGE
EXTRACT
SETSEED
RANDOM
CEIL
FLOOR
ROUND
POWER
CREATE INDEX
DROP INDEX
ALTER INDEX
CREATE VIEW
DROP VIEW
ALTER USER
CREATE USER
DROP USER

Basic SELECT with LIMIT

SQL Code Block:
```
  SELECT “column_names”
  FROM “table_name”
  [WHERE conditions]
  [ORDER BY expression [ ASC | DESC ]]
  LIMIT row_count;
```
- Explanation: This SQL statement retrieves data from specified columns of a table. It can include filtering with WHERE, sorting with ORDER BY, and limiting the number of records with LIMIT.
- Output Demo: The results depend on the column_names, table_name, and conditions specified.
- Details:
  - SELECT specifies the columns to retrieve.
  - WHERE filters rows based on conditions.
  - ORDER BY sorts the output in ascending or descending order.
  - LIMIT restricts the number of returned rows.

SELECT with WHERE, ORDER BY, and LIMIT

SQL Code Block 1:
```
  SELECT * FROM customer
  WHERE age >= 25
  ORDER BY age DESC
  LIMIT 8;
```
- Explanation: Retrieves all columns from the customer table where age is 25 or older, orders the results by age in descending order, and limits the output to 8 rows.
- Output Demo: The output will be the top 8 customers aged 25 or older, sorted from oldest to youngest.
- Details:
  - * retrieves all columns.
  - ORDER BY age DESC arranges the output in descending order of age.
  - LIMIT 8 restricts the result to 8 rows.
SQL Code Block 2:
```
  SELECT * FROM customer
  WHERE age >= 25
  ORDER BY age ASC
  LIMIT 10;
```
- Explanation: Similar to the first code block, but orders the results by age in ascending order and limits the output to 10 rows.
- Output Demo: Displays the youngest 10 customers aged 25 or older.
- Details:
  - ORDER BY age ASC arranges the output in ascending order.

LEFT JOIN

SQL Code Block:
```
  SELECT table1.column1, table2.column2...
  FROM table1
  LEFT JOIN table2
  ON table1.common_field = table2.common_field;
```
- Explanation: This LEFT JOIN retrieves all rows from table1, with matching rows from table2. If no match is found, columns from table2 are returned as NULL.
- Output Demo: The output displays all rows from table1 with corresponding data from table2, filling unmatched rows with NULL.
- Details:
  - LEFT JOIN ensures all records from the left table are included, even if there are no matches in the right table.

LEFT JOIN Example

SQL Code Block:
```
  SELECT
  a.order_line,
  a.product_id,
  a.customer_id,
  a.sales,
  b.customer_name,
  b.age
  FROM sales_2015 AS a 
  LEFT JOIN customer_20_60 AS b 
  ON a.customer_id = b.customer_id
  ORDER BY customer_id;
```
- Explanation: This LEFT JOIN query retrieves all rows from the sales_2015 table (a), and matches them with the customer_20_60 table (b). Rows in sales_2015 without corresponding matches in customer_20_60 will still appear, with NULL for columns from b.
- Output Demo: The output includes all order details from sales_2015, supplemented with customer information where available.
- Details:
  - LEFT JOIN ensures all records from the sales_2015 table are included.
  - Matching customer data is included from the customer_20_60 table, or NULL if not found.

Types of SQL JOINS

Explanation:
- INNER JOIN: Returns rows with matching values in both tables.
- LEFT OUTER JOIN (or LEFT JOIN): Returns all rows from the left table, with matching rows from the right table or NULL if no match.
- RIGHT OUTER JOIN (or RIGHT JOIN): Returns all rows from the right table, with matching rows from the left table or NULL if no match.
- FULL OUTER JOIN: Combines the results of LEFT JOIN and RIGHT JOIN, showing all records from both tables.
- CROSS JOIN: Produces a Cartesian product of both tables.

INNER JOIN Syntax

SQL Code Block:
```
  SELECT columns
  FROM table1
  INNER JOIN table2
  ON table1.column = table2.column;
```
- Explanation: This INNER JOIN fetches rows only where the specified columns in both tables match. It combines data from both tables into the result set when the join condition is met.
- Output Demo: Displays data from both tables for rows where there is a match.
- Details:
  - Omits rows that do not meet the join condition.

INNER JOIN Example with Specific Columns

SQL Code Block:
```
  SELECT
  a.order_line,
  a.product_id,
  a.customer_id,
  a.sales,
  b.customer_name,
  b.age
  FROM sales_2015 AS a 
  INNER JOIN customer_20_60 AS b 
  ON a.customer_id = b.customer_id
  ORDER BY customer_id;
```
- Explanation: This INNER JOIN retrieves data where customer_id matches between sales_2015 (a) and customer_20_60 (b).
- Output Demo: Lists sales data from sales_2015 with corresponding customer details from customer_20_60.
- Details:
  - Ensures only rows with matching customer_id appear in the result.
  - ORDER BY customer_id sorts the results by customer_id.

TRUNCATE Syntax

SQL Code Block:
```
  TRUNCATE [ONLY] table_name [ CASCADE | RESTRICT ];
```
- Explanation: The TRUNCATE statement removes all rows from a table, similar to DELETE without a WHERE clause, but more efficient as it does not log individual row deletions. CASCADE truncates related tables, while RESTRICT prevents truncation if related tables are present.
- Output Demo: Clears all data from the specified table.
- Details:
  - Faster than DELETE but not reversible unless within a transaction.

TRUNCATE TABLE Example

SQL Code Block:
```
  TRUNCATE TABLE Customer_20_60;
```
- Explanation: This command empties the Customer_20_60 table while keeping the table structure intact.
- Output Demo: The Customer_20_60 table is cleared of all records.
- Details:
  - Equivalent to DELETE FROM Customer_20_60; but performs better for large tables.

Using AS for Column and Table Aliases

SQL Code Block:
```
  SELECT column_name AS "column_alias"
  FROM "table_name";
```
- Explanation: The AS keyword assigns an alias to a column or table, making the output easier to read or simplifying references in the query.
- Output Demo: The result set displays columns with their assigned aliases.
- Details:
  - Commonly used to rename columns for clarity in the result set.

AS Example for Column Aliases

SQL Code Block:
```
  SELECT Cust_id AS “Serial number”, Customer_name AS name, Age AS Customer_age
  FROM Customer;
```
- Explanation: Renames the Cust_id, Customer_name, and Age columns to Serial number, name, and Customer_age, respectively, in the output.
- Output Demo: Displays customer data with custom column headers.
- Details:
  - Improves readability of query results.

TRUNCATE TABLE Example

SQL Code Block:
```
  TRUNCATE TABLE Customer_20_60;
```
- Explanation: The TRUNCATE statement empties all rows from the Customer_20_60 table without logging individual row deletions. It's faster than DELETE without a WHERE clause but can't be rolled back unless in a transaction.
- Output Demo: Clears all data from the Customer_20_60 table.
- Details:
  - Efficient for large tables as it resets them quickly.

COUNT Syntax

SQL Code Block:
```
  SELECT "column_name1", COUNT("column_name2")
  FROM "table_name";
```
- Explanation: Counts the number of non-NULL values in column_name2 for each value in column_name1.
- Output Demo: Shows the count alongside values from column_name1.
- Details:
  - Commonly used for aggregation and counting rows or distinct values.

COUNT Function Example

SQL Code Block:
```
  SELECT COUNT(*) FROM sales;
  SELECT COUNT(order_line) AS "Number of Products Ordered",
  COUNT(DISTINCT order_id) AS "Number of Orders"
  FROM sales WHERE customer_id = 'CG-12520';
```
- Explanation: The first query counts all rows in the sales table. The second query counts the total order_line and unique order_id entries for a specific customer.
- Output Demo: Provides the total number of products ordered and unique orders for customer_id = 'CG-12520'.
- Details:
  - COUNT(*) counts all rows, COUNT(column) counts non-NULL values.

LIKE Pattern Matching Syntax

SQL Code Block:
```
  SELECT "column_name"
  FROM "table_name"
  WHERE "column_name" LIKE {PATTERN};
```
- Explanation: The LIKE condition allows pattern matching within a column using wildcards.
- Output Demo: Retrieves rows matching the specified pattern.
- Details:
  - Commonly used wildcards: % for any string and _ for a single character.

Wildcards in LIKE

Explanation:
- %: Matches any string of any length (e.g., A% matches ABC, ABCDE).
- _: Matches a single character (e.g., AB_C matches ABXC).
- Examples:
  - A%: Matches values starting with A.
  - %A: Matches values ending with A.
  - A%B: Matches values starting with A and ending with B.
  - AB_C: Matches AB followed by one character, then C.

LIKE Pattern Examples

SQL Code Block:

  SELECT * FROM customer_table
  WHERE first_name LIKE 'Jo%';

  SELECT * FROM customer_table
  WHERE first_name LIKE '%od%';

  SELECT first_name, last_name FROM customer_table
  WHERE first_name LIKE 'Jas_n';

  SELECT first_name, last_name FROM customer_table
  WHERE last_name NOT LIKE 'J%';

  SELECT * FROM customer_table
  WHERE last_name LIKE 'G\%';

Explanation: Demonstrates various uses of LIKE for pattern matching, including using wildcards at different positions.
Output Demo: Retrieves rows where first_name or last_name matches the specified patterns.
Details:
- LIKE 'Jo%': Matches names starting with Jo.
- LIKE '%od%': Matches names containing od.
- NOT LIKE 'J%': Excludes names starting with J.
- LIKE 'G\%': Matches names starting with G%.

UPDATE Syntax

SQL Code Block:
```
  UPDATE "table_name"
  SET column_1 = [value1], column_2 = [value2], ...
  WHERE "condition";
```
- Explanation: Modifies existing records in a table. Only updates rows that meet the WHERE condition.
- Output Demo: Changes data in the specified columns of matching rows.
- Details:
  - Essential for updating specific records without altering the entire table.

UPDATE Examples

SQL Code Block 1 (Single Row Update):
```
  UPDATE Customer_table
  SET Age = 17, Last_name = 'Pe'
  WHERE Cust_id = 2;
```
- Explanation: Updates the Age and Last_name for the customer with Cust_id 2 in the Customer_table.
- Output Demo: The customer record with Cust_id 2 now has Age set to 17 and Last_name set to 'Pe'.
- Details:
  - The SET clause specifies columns to update.
  - The WHERE clause ensures only the matching row is updated.
SQL Code Block 2 (Multiple Row Update):
```
  UPDATE Customer_table
  SET email_id = 'gee@xyz.com'
  WHERE First_name = 'Gee' OR First_name = 'gee';
```
- Explanation: Updates the email_id for all rows where First_name is 'Gee' or 'gee'.
- Output Demo: All matching records have their email_id updated to 'gee@xyz.com'.
- Details:
  - The OR operator allows matching multiple conditions.

DELETE Syntax

SQL Code Block:
```
  DELETE FROM "table_name"
  WHERE "condition";
```
- Explanation: Deletes rows from the specified table that match the WHERE condition.
- Output Demo: Removes the specified records based on the condition.
- Details:
  - Without WHERE, the DELETE statement will remove all rows from the table.

DELETE Examples

SQL Code Block 1 (Single Row):
```
  DELETE FROM CUSTOMERS
  WHERE ID = 6;
```
- Explanation: Deletes the record with ID = 6 from the CUSTOMERS table.
- Output Demo: The record with ID = 6 is removed.
- Details:
  - Deletes only the matching record specified by the WHERE clause.
SQL Code Block 2 (Multiple Rows):
```
  DELETE FROM CUSTOMERS
  WHERE age > 25;
```
- Explanation: Deletes all rows where age is greater than 25.
- Output Demo: All records with age greater than 25 are removed.
- Details:
  - Useful for bulk deletions with a condition.
SQL Code Block 3 (All Rows):
```
  DELETE FROM CUSTOMERS;
```
- Explanation: Deletes all rows from the CUSTOMERS table, clearing its contents.
- Output Demo: The CUSTOMERS table is emptied.
- Details:
  - Similar to TRUNCATE but logs each deletion.

Types of SQL JOINS

Explanation:
- INNER JOIN: Retrieves rows with matching values in both tables.
- LEFT OUTER JOIN (or LEFT JOIN): Includes all rows from the left table, with NULLs for non-matching rows from the right table.
- RIGHT OUTER JOIN (or RIGHT JOIN): Includes all rows from the right table, with NULLs for non-matching rows from the left table.
- FULL OUTER JOIN: Combines results of LEFT and RIGHT JOIN, showing all records from both tables.
- CROSS JOIN: Produces a Cartesian product of the tables.

CROSS JOIN Syntax

SQL Code Block:
```
  SELECT table1.column1, table2.column2...
  FROM table1, table2 [, table3];
```
- Explanation: Creates a Cartesian product between two or more tables, combining each row from one table with every row from the other table.
- Output Demo: Displays all possible combinations of rows from the specified tables.
- Details:
  - Often used for generating test data or combinations.

CROSS JOIN Example

SQL Code Block:
```
  SELECT a.YYYY, b.MM
  FROM year_values AS a, month_values AS b
  ORDER BY a.YYYY, b.MM;
```
- Explanation: Returns every combination of years (YYYY) and months (MM) from the year_values and month_values tables.
- Output Demo: Lists all year-month combinations in ascending order.
- Details:
  - ORDER BY arranges the result set by YYYY and MM.

ORDER BY Clause

SQL Code Block:
```
  SELECT "column_name"
  FROM "table_name"
  [WHERE "condition"]
  ORDER BY "column_name" [ASC, DESC];
```
- Explanation: Sorts the result set by the specified column. By default, sorts in ascending order (ASC); DESC is used for descending order.
- Output Demo: Displays the result set ordered by the specified column(s).
- Details:
  - Can sort by multiple columns for multi-level ordering.

ORDER BY Examples

SQL Code Block 1:
```
  SELECT * FROM customer
  WHERE state = 'California'
  ORDER BY Customer_name;
```
- Explanation: Retrieves all columns from the customer table where state is 'California', and orders the results by Customer_name in ascending order.
- Output Demo: The result displays customers from California, sorted alphabetically by Customer_name.
- Details:
  - Equivalent to using ORDER BY Customer_name ASC.
SQL Code Block 2:
```
  SELECT * FROM customer
  ORDER BY 2 DESC;
```
- Explanation: Orders the result set based on the second column in descending order.
- Output Demo: The table is displayed with results sorted by the second column.
- Details:
  - ORDER BY 2 refers to the position of the column in the select list.
SQL Code Block 3:
```
  SELECT * FROM customer
  WHERE age > 25
  ORDER BY City ASC, Customer_name DESC;
```
- Explanation: Retrieves customers older than 25 and orders results first by City in ascending order, then by Customer_name in descending order.
- Output Demo: Displays customers above 25 years, grouped by city and sorted by customer names in reverse order.
- Details:
  - Multi-column ordering is possible for more complex sorting.
SQL Code Block 4:
```
  SELECT * FROM customer
  ORDER BY age;
```
- Explanation: Orders all customer data by age in ascending order.
- Output Demo: Lists all customers from youngest to oldest.
- Details:
  - Default order is ascending unless specified otherwise.

EXPLAIN Query Plan

SQL Code Block:
```
  EXPLAIN [ VERBOSE ] query;
```
- Explanation: Provides the execution plan for a query, helping you understand how PostgreSQL will run it without actually executing the query.
- Output Demo: Shows the steps and estimated costs for the query plan.
- Details:
  - VERBOSE provides a more detailed query plan, including full execution details.

SOFT DELETE vs HARD DELETE

Explanation:
- Soft Delete:
  - Marks records as deleted (e.g., setting an is_deleted flag) but does not remove them from the database.
  - Benefits include retaining data for audits or recovery.
- Hard Delete:
  - Physically removes data from the database.
  - Results in permanent data deletion and potential loss of recoverability.

UPDATE Best Practices

SQL Code Block:
```
  UPDATE customer
  SET customer_name = (TRIM(UPPER(customer_name)))
  WHERE (TRIM(UPPER(customer_name)) <> customer_name);
```
- Explanation: Updates customer_name by trimming whitespace and converting it to uppercase, but only if the transformation changes the value.
- Output Demo: Modifies customer names to be uniformly formatted.
- Details:
  - Reduces unnecessary updates by ensuring only changed rows are affected.
- Tip: Frequent updates behave like a soft delete followed by an insert, potentially increasing storage usage.

VACUUM Command

SQL Code Block:
```
  VACUUM [ table ];
```
- Explanation: Reclaims disk space from rows marked for deletion after UPDATE or DELETE operations. Helps compact the table and free up space.
- Output Demo: The command optimizes table storage and performance.
- Details:
  - Regular use is beneficial for tables with frequent updates or deletions.

TRUNCATE vs DELETE

Explanation:
- TRUNCATE:
  - More efficient for clearing all rows in a table compared to DELETE without WHERE.
  - Retains table structure and properties.
  - Less resource-intensive, with fewer logs and quicker execution.
- DELETE:
  - Logs each row deletion, allowing for more granular control but at the cost of performance.
  - Supports WHERE clauses for selective row removal.

Pattern Matching Best Practices

Guidelines:
- Use LIKE over complex REGEX where possible for performance.
- Prefer simple string functions like TRIM over more intensive operations (REPLACE, UPPER, LOWER).
- Avoid using string columns for date storage; use proper date formats instead.

JOIN Best Practices

SQL Code Block:
```
  SELECT a.order_line, a.product_id, b.customer_name, b.age
  FROM sales_2015 AS a 
  LEFT JOIN customer_20_60 AS b 
  ON a.customer_id = b.customer_id
  ORDER BY customer_id;
```
- Explanation: Demonstrates using a LEFT JOIN with an ORDER BY clause. Best practice includes limiting fields in SELECT and using GROUP BY to avoid unnecessary one-to-many joins.
- Details:
  - Improves performance by reducing data transfer and processing.

Schemas in Databases

Explanation:
- A schema organizes database objects (e.g., tables, views, procedures) within a database for better management.
- Allows multiple users or applications to use the same database without conflicts.
- Third-party applications can have separate schemas to avoid name collisions.
SQL Code Block:
```
  CREATE SCHEMA testschema;
```
- Explanation: Creates a new schema named testschema.
- Details:
  - Useful for grouping related database objects logically.

BETWEEN Condition Syntax

SQL Code Block:
```
  SELECT "column_name"
  FROM "table_name"
  WHERE "column_name" BETWEEN 'value1' AND 'value2';
```
- Explanation: The BETWEEN condition is used to filter records where the specified column's value lies within a range.
- Output Demo: Retrieves rows where column_name falls between value1 and value2.
- Details:
  - Equivalent to column_name >= 'value1' AND column_name <= 'value2'.

BETWEEN Condition Examples

SQL Code Block 1:
```
  SELECT * FROM customer
  WHERE age BETWEEN 20 AND 30;
```
- Explanation: Retrieves all customers whose age is between 20 and 30, inclusive.
- Output Demo: Lists all customers aged between 20 and 30.
- Details:
  - Can also be written as age >= 20 AND age <= 30.
SQL Code Block 2:
```
  SELECT * FROM customer
  WHERE age NOT BETWEEN 20 AND 30;
```
- Explanation: Retrieves all customers whose age is not between 20 and 30.
- Output Demo: Lists customers outside the age range of 20 to 30.
SQL Code Block 3:
```
  SELECT * FROM sales
  WHERE ship_date BETWEEN '2015-04-01' AND '2016-04-01';
```
- Explanation: Retrieves all sales records where ship_date is within the specified date range.
- Output Demo: Displays sales records shipped between April 1, 2015, and April 1, 2016.
- Details:
  - Useful for date filtering and range queries.

Data Types Overview

Numeric Data Types:
- smallint: Stores whole numbers in a small range (2 bytes, -32,768 to +32,767).
- integer: Common for storing integers (4 bytes, -2,147,483,648 to +2,147,483,647).
- bigint: Stores large range whole numbers (8 bytes, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807).
- decimal: User-defined precision for exact numeric values, useful for financial data.
Character Data Types:
- varchar(n): Variable-length string with a defined limit.
- char(n): Fixed-length, blank-padded string.
- text: Stores unlimited-length strings.
Date/Datetime Data Types:
- timestamp: Includes both date and time (8 bytes).
- date: Only date, no time (4 bytes).
- time: Only time, no date (8 bytes).
- interval: Represents a time interval (12 bytes).

CREATE TYPE Syntax

SQL Code Block:
```
  CREATE TYPE Dollar AS DECIMAL(9,2);
  CREATE TYPE days AS ENUM ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');
```
- Explanation: Allows users to create custom data types to suit specific needs, such as predefined value sets or specific formats.
- Output Demo: The Dollar type will store decimal numbers with 9 digits and 2 decimal places; days can store one of the enumerated day values.

EXCEPT Operator Syntax

SQL Code Block:
```
  SELECT expression1, expression2, ...
  FROM tables
  [WHERE conditions]
  EXCEPT
  SELECT expression1, expression2, ...
  FROM tables
  [WHERE conditions];
```
- Explanation: The EXCEPT operator returns all rows from the first SELECT statement that are not present in the second SELECT statement.
- Output Demo: Retrieves unique rows found in the first query that are absent in the second.
- Details:
  - Ensures only distinct rows are returned.

EXCEPT Example

SQL Code Block:
```
  SELECT customer_id
  FROM sales_2015
  EXCEPT
  SELECT customer_id
  FROM customer_20_60
  ORDER BY customer_id;
```
- Explanation: Selects customer_id values from sales_2015 that do not exist in customer_20_60, and orders the result by customer_id.
- Output Demo: Displays customer_id entries found in sales_2015 but not in customer_20_60.
- Details:
  - Useful for identifying discrepancies or exclusive records between tables.

SELECT Statement Syntax

SQL Code Block:
```
  SELECT "column_name1", "column_name2", "column_name3"
  FROM "table_name";

  SELECT * FROM "table_name";
```
- Explanation: The SELECT statement is used to fetch data from a database table and present it in a result set.
- Output Demo: Retrieves specified columns or all columns when SELECT * is used.
- Details:
  - Fetches data in a tabular format called a result set.
  - Used with various clauses to filter, sort, and format results.

SELECT Examples

SQL Code Block 1 (Select One Column):
```
  SELECT first_name FROM customer_table;
```
- Explanation: Fetches the first_name column from the customer_table.
- Output Demo: Displays a list of first names.
SQL Code Block 2 (Select Multiple Columns):
```
  SELECT first_name, last_name FROM customer_table;
```
- Explanation: Fetches first_name and last_name columns from the customer_table.
- Output Demo: Displays a list with first and last names.
SQL Code Block 3 (Select All Columns):
```
  SELECT * FROM customer_table;
```
- Explanation: Fetches all columns from the customer_table.
- Output Demo: Displays all columns and rows from the table.

SELECT DISTINCT Syntax

SQL Code Block:
```
  SELECT DISTINCT "column_name"
  FROM "table_name";
```
- Explanation: The DISTINCT keyword ensures that duplicate rows are removed from the result set.
- Output Demo: Displays unique values from the specified column.
- Details:
  - Useful for finding unique entries in a column.

SELECT DISTINCT Examples

SQL Code Block 1 (Select One Column):
```
  SELECT DISTINCT customer_name FROM customer_table;
```
- Explanation: Retrieves unique customer names from the customer_table.
- Output Demo: Displays a list of unique customer names.
SQL Code Block 2 (Select Multiple Columns):
```
  SELECT DISTINCT customer_name, age FROM customer_table;
```
- Explanation: Fetches unique combinations of customer_name and age.
- Output Demo: Displays rows with unique customer_name and age pairs.

CREATE TABLE Syntax

SQL Code Block:
```
  CREATE TABLE “table_name” (
    “column 1“ "data type for column 1" [column 1 constraint(s)],
    “column 2“ "data type for column 2" [column 2 constraint(s)],
    ...
    “column n“
    [table constraint(s)]
  );
```
- Explanation: The CREATE TABLE statement defines a new table, specifying column names, data types, and constraints.
- Output Demo: A new table structure is created in the database.
- Details:
  - Constraints include NOT NULL, DEFAULT, UNIQUE, CHECK, PRIMARY KEY, and FOREIGN KEY.

Key Constraints

Explanation:
- Primary Key: Uniquely identifies each row in the table and cannot be NULL.
- Foreign Key: Ensures referential integrity by referencing the primary key of another table.
- Composite Key: A primary key composed of multiple columns.
- Constraints:
  - NOT NULL: Prevents NULL values.
  - DEFAULT: Sets a default value for a column.
  - UNIQUE: Ensures all values in a column are unique.
  - CHECK: Validates column values against a condition.

WHERE Clause Syntax

SQL Code Block:
```
  SELECT "column_name"
  FROM "table_name"
  WHERE "condition";
```
- Explanation: The WHERE clause filters records based on a specified condition.
- Output Demo: Displays only rows meeting the condition.
- Details:
  - Can be combined with logical operators such as AND, OR, and comparison operators like =, >, <.

WHERE Clause Examples

SQL Code Block 1 (Equals Condition):
```
  SELECT first_name FROM customer_table WHERE age = 25;
```
- Explanation: Fetches first_name from customer_table where age equals 25.
- Output Demo: Displays first names of customers aged 25.
SQL Code Block 2 (Greater Than Condition):
```
  SELECT first_name, age FROM customer_table WHERE age > 25;
```
- Explanation: Fetches first_name and age where age is greater than 25.
- Output Demo: Lists names and ages of customers over 25.
SQL Code Block 3 (Matching Text Condition):
```
  SELECT * FROM customer_table WHERE first_name = "John";
```
- Explanation: Retrieves all columns for customers with first_name 'John'.
- Output Demo: Displays rows where first_name is 'John'.

ACID Properties

Explanation:
- Atomicity: Ensures that all operations within a transaction are completed; if not, the transaction is aborted and no changes are made.
- Consistency: Guarantees that a transaction transitions the database from one valid state to another.
- Isolation: Ensures that concurrent transactions do not interfere with each other and that each transaction is independent.
- Durability: Ensures that once a transaction has been committed, it remains in the system even if there is a system failure.

AVG Function Syntax

SQL Code Block:
```
  SELECT AVG(aggregate_expression)
  FROM tables
  [WHERE conditions];
```
- Explanation: The AVG function calculates the average value of a numeric column in a result set.
- Output Demo: Displays the average value of the specified column.
- Details:
  - Commonly used for calculating the mean of data points.

AVG Function Examples

SQL Code Block 1:
```
  SELECT AVG(age) AS "Average Customer Age"
  FROM customer;
```
- Explanation: Calculates the average age of all customers.
- Output Demo: Displays the average age, e.g., "Average Customer Age: 35.2".
SQL Code Block 2:
```
  SELECT AVG(sales * 0.10) AS "Average Commission Value"
  FROM sales;
```
- Explanation: Computes the average value of 10% commission from the sales table.
- Output Demo: Displays the average commission value.

Subquery Syntax

SQL Code Block:
```
  SELECT "column_name1"
  FROM "table_name1"
  WHERE "column_name2" [Comparison Operator] 
  (SELECT "column_name3"
   FROM "table_name2"
   WHERE "condition");
```
- Explanation: A subquery is a query nested within another SQL query. It can reside in WHERE, FROM, or SELECT clauses.
- Output Demo: The output depends on the results returned by the subquery.
- Details:
  - Subqueries are enclosed in parentheses and can only return one column unless used in the FROM clause.

Subquery Examples

SQL Code Block 1 (Subquery in WHERE):
```
  SELECT * FROM sales
  WHERE customer_ID IN 
  (SELECT DISTINCT customer_id
   FROM customer 
   WHERE age > 60);
```
- Explanation: Retrieves sales records where the customer_ID matches those of customers older than 60.
- Output Demo: Displays sales records of customers over 60 years old.

SQL Code Block 2 (Subquery in FROM):

  SELECT 
  a.product_id,
  a.product_name,
  a.category,
  b.quantity
  FROM product AS a 
  LEFT JOIN (SELECT product_id, SUM(quantity) AS quantity 
             FROM sales 
             GROUP BY product_id) AS b
  ON a.product_id = b.product_id
  ORDER BY b.quantity DESC;

Explanation: Joins the product table with a subquery that aggregates total quantity from sales.
Output Demo: Displays products with their total quantity sold, sorted by quantity in descending order.

SQL Code Block 3 (Subquery in SELECT):

  SELECT customer_id, 
         order_line, 
         (SELECT customer_name
          FROM customer 
          WHERE sales.customer_id = customer.customer_id)
  FROM sales 
  ORDER BY customer_id;

Explanation: Includes a subquery within the SELECT clause to fetch customer_name for each customer_id in sales.
Output Demo: Displays customer details along with order lines.

Subquery Rules

Guidelines:
- Subqueries must be enclosed in parentheses.
- They can return only one column unless used in the FROM clause.
- ORDER BY cannot be used in a subquery but can be used in the main query.
- Use GROUP BY in subqueries to achieve similar functionality to ORDER BY.
- Subqueries that return more than one row must use multiple value operators like IN.
- Subqueries cannot be directly enclosed in set functions or use the BETWEEN operator.

FULL OUTER JOIN Syntax

SQL Code Block:
```
  SELECT table1.column1, table2.column2...
  FROM table1
  FULL JOIN table2
  ON table1.common_field = table2.common_field;
```
- Explanation: Combines the results of LEFT JOIN and RIGHT JOIN, returning all rows from both tables. If a match is not found, NULL is returned for columns from the table without a match.
- Output Demo: Displays all records from both tables, matching where possible and filling NULL for non-matches.
- Details:
  - Useful for comprehensive data comparisons across two tables.

FULL OUTER JOIN Example

SQL Code Block:
```
  SELECT
    a.order_line,
    a.product_id,
    a.customer_id,
    a.sales,
    b.customer_name,
    b.age,
    b.customer_id
  FROM sales_2015 AS a 
  FULL JOIN customer_20_60 AS b 
  ON a.customer_id = b.customer_id
  ORDER BY a.customer_id, b.customer_id;
```
- Explanation: This FULL JOIN combines all rows from sales_2015 (a) and customer_20_60 (b). Where records match in customer_id, the rows are merged; if not, unmatched rows will display NULL for non-existent data in the other table.
- Output Demo: Shows all records from both tables, with NULL in columns where no match exists.
- Details:
  - Combines LEFT and RIGHT JOIN outputs for comprehensive comparison.
  - Helps in finding mismatched data across tables.

ALTER TABLE Syntax

SQL Code Block:
```
  ALTER TABLE "table_name"
  [Specify Actions];
```
- Explanation: Used to modify the structure or definition of an existing table. Possible actions include adding, deleting, modifying, or renaming columns and constraints.
- Output Demo: Adjusts the table structure to reflect the specified changes.
- Details:
  - Supports altering columns and adding or dropping constraints and indexes.

ADD & DROP Columns Syntax

SQL Code Block:
```
  ALTER TABLE "table_name"
  ADD "column_name" "Data Type";

  ALTER TABLE "table_name"
  DROP "column_name";
```
- Explanation: The first command adds a new column with the specified data type; the second removes an existing column.
- Output Demo: Adjusts the table to include or exclude columns as specified.
- Details:
  - Adding columns allows extending table structure; dropping columns permanently removes them.

MODIFY & RENAME Columns Syntax

SQL Code Block:
```
  ALTER TABLE "table_name"
  ALTER COLUMN "column_name" TYPE "New Data Type";

  ALTER TABLE "table_name"
  RENAME COLUMN "column 1" TO "column 2";
```
- Explanation: Changes the data type of a column or renames a column in an existing table.
- Output Demo: Reflects modified column types or renamed columns in the table.
- Details:
  - Useful for refining table design without data loss.

ADD & DROP Constraints Syntax

SQL Code Block:

  ALTER TABLE "table_name" ALTER COLUMN "column_name" SET NOT NULL;
  ALTER TABLE "table_name" ALTER COLUMN "column_name" DROP NOT NULL;
  ALTER TABLE "table_name" ADD CONSTRAINT "constraint_name" CHECK ("column_name" >= 100);
  ALTER TABLE "table_name" ADD PRIMARY KEY ("column_name");
  ALTER TABLE "child_table" ADD CONSTRAINT "fk_name" FOREIGN KEY ("child_column") REFERENCES "parent_table"("parent_column");

Explanation: These commands add or remove constraints on columns. Constraints ensure data validity and integrity.
Output Demo: The table enforces the specified constraints or removes them.
Details:
- CHECK, PRIMARY KEY, and FOREIGN KEY constraints support data integrity.

Data Management Basics

Key Topics:
- SQL Basics, Database Fundamentals, SQL Queries.
- Includes core concepts that form the foundation of database management.

Fundamental SQL Commands

Key Commands:
- CREATE, SELECT, INSERT, COPY, UPDATE, ALTER.
- Essential for building, modifying, and querying database tables.

Data Filtering and Sorting

Key Clauses:
- WHERE, ORDER BY, logical operators (OR, AND, NOT).
- Pattern matching and filtering using IN, BETWEEN, and LIKE.

Aggregate Functions and GROUP BY

Functions:
- SUM, AVG, COUNT, MIN, MAX.
- Data aggregation using GROUP BY and conditional filtering with HAVING.

Joins Overview

Join Types:
- INNER JOIN, OUTER JOIN (left and right), CROSS JOIN, EXCEPT, and UNION.
- Used for combining data from multiple tables based on relationships.

Advanced SQL Concepts

Key Topics:
- Subqueries, Views, Indexes.
- Advanced strategies for optimizing database interactions and creating efficient queries.

String Functions

Key Functions:
- UPPER / LOWER: Converts string to uppercase or lowercase.
- TRIM, LTRIM, RTRIM: Removes whitespace from strings (TRIM from both sides, LTRIM from the left, RTRIM from the right).
- REPLACE: Substitutes all occurrences of a substring within a string with another substring.
- SUBSTRING: Extracts a portion of a string.
- CONCAT: Combines multiple strings into one.
- STRING_AGG: Concatenates strings from a group, separated by a specified delimiter.

Mathematical Functions

Key Functions:
- CEIL: Rounds a number up to the nearest integer.
- FLOOR: Rounds a number down to the nearest integer.
- RANDOM: Generates a random number between 0 and 1.
- SETSEED: Sets the seed for the RANDOM function for repeatable results.
- ROUND: Rounds a number to the nearest integer or to a specified number of decimal places.
- POWER: Raises a number to the power of another.

Date Time Functions

Key Functions:
- CURRENT_DATE: Returns the current date.
- CURRENT_TIME: Returns the current time.
- AGE: Calculates the interval between two dates.
- EXTRACT: Extracts specific parts of a date (e.g., YEAR, MONTH, DAY).

Data Type Conversion Functions

Key Conversions:
- Conversion to String: Using CAST or TO_CHAR for converting other data types to a string.
- Conversion to Date: Using CAST or TO_DATE to convert strings or numbers to date types.
- Conversion to Number: Using CAST or TO_NUMBER to convert strings to numeric types.

Performance Tuning

Key Techniques:
- EXPLAIN: Analyzes the execution plan of a query without running it.
- Tips for String: Optimize queries by avoiding unnecessary string operations.
- Tips for Joins: Use indexed columns and appropriate join strategies for better performance.
- Query Comparison: Evaluate different query approaches to select the most efficient one.

Pattern Matching

Key Patterns:
- LIKE: Matches patterns in strings using % for multiple characters and _ for a single character.
- SIMILAR TO: Combines the simplicity of LIKE with regular expression capabilities.
- ~ (Regular Expressions): Matches patterns using full regular expression syntax for complex searches.

Bonus Lectures

Topics:
- Interview Tips: Guidance on SQL-related interview questions.
- Keys: Detailed discussion on primary and foreign keys.
- Access Control: Techniques for managing database access permissions.
- Tablespace: Managing physical storage of database data.

SUM Function Syntax

SQL Code Block:
```
  SELECT SUM(aggregate_expression)
  FROM tables
  [WHERE conditions];
```
- Explanation: The SUM function calculates the total of a numeric column.
- Output Demo: Returns the sum of the specified column values.
- Details:
  - Often used with GROUP BY for aggregated totals.

SUM Function Examples

SQL Code Block 1:
```
  SELECT SUM(Profit) AS "Total Profit"
  FROM sales;
```
- Explanation: Calculates the total profit from the sales table.
- Output Demo: Displays the sum of the Profit column, e.g., "Total Profit: $500,000".
SQL Code Block 2:
```
  SELECT SUM(quantity) AS "Total Quantity"
  FROM orders
  WHERE product_id = 'FUR-TA-10000577';
```
- Explanation: Calculates the total quantity of a specific product from the orders table.
- Output Demo: Displays the sum of quantity for the specified product_id.

CEIL & FLOOR Functions

Explanation:
- CEIL: Returns the smallest integer greater than or equal to a specified number.
- FLOOR: Returns the largest integer less than or equal to a specified number.
Syntax:
```
  CEIL(number)
  FLOOR(number)
```
Example SQL Code:
```
  SELECT order_line, 
         sales, 
         CEIL(sales), 
         FLOOR(sales)
  FROM sales
  WHERE discount > 0;
```
- Explanation: Fetches order_line and sales from the sales table and returns the CEIL and FLOOR of the sales values where discount is greater than 0.
- Output Demo: Displays the original sales, its rounded-up value using CEIL, and its rounded-down value using FLOOR.

RANDOM Function

Explanation: The RANDOM function returns a random decimal number between 0 (inclusive) and 1 (exclusive).
Syntax:
```
  RANDOM();
```
Example SQL Code:
```
  SELECT RANDOM() * (b - a) + a;
```
- Explanation: Generates a random decimal number between a and b (with a included and b excluded).
- Output Demo: Displays a random number within the specified range.
Random Integer Example:
```
  SELECT FLOOR(RANDOM() * (b - a + 1)) + a;
```
- Explanation: Generates a random integer between a and b (both inclusive).
- Output Demo: Shows a whole number between the specified boundaries.

SETSEED Function

Explanation: The SETSEED function sets the seed for the RANDOM function, ensuring that the generated sequence of random numbers is repeatable.
Syntax:
```
  SETSEED(seed);
```
Example SQL Code:
```
  SELECT SETSEED(0.5);
  SELECT RANDOM();
  SELECT RANDOM();
```
- Explanation: Initializes the random number generator with a seed value of 0.5, ensuring subsequent calls to RANDOM produce a repeatable sequence.
- Output Demo: Generates consistent random numbers when the seed is set.

ROUND Function

Explanation: The ROUND function rounds a number to the nearest integer or to a specified number of decimal places.
Syntax:
```
  ROUND(number);
```
Example SQL Code:
```
  SELECT order_line, 
         sales, 
         ROUND(sales)
  FROM sales;
```
- Explanation: Fetches order_line and sales, and rounds the sales value to the nearest whole number.
- Output Demo: Displays the rounded sales values for each record.

POWER Function

Explanation: The POWER function calculates the result of a number (m) raised to the power of another number (n).
Syntax:
```
  POWER(m, n);
```
Example SQL Code:
```
  SELECT POWER(6, 2);
  SELECT age, POWER(age, 2)
  FROM customer
  ORDER BY age;
```
- Explanation: The first query returns 6 raised to the power of 2. The second query fetches age and computes age squared for each record in the customer table, sorting the results by age.
- Output Demo: Displays ages and their squared values, helping to analyze data transformations.

SQL Query Types

Categories of SQL Commands:
- DDL (Data Definition Language): Commands like CREATE, ALTER, and DROP used for defining database structures.
- DML (Data Manipulation Language): Commands like INSERT, UPDATE, and DELETE for manipulating data.
- DQL (Data Query Language): Commands like SELECT, ORDER BY, and GROUP BY for querying data.
- DCL (Data Control Language): Commands like GRANT and REVOKE for controlling access to data.
- TCC (Transactional Control Commands): Commands like COMMIT and ROLLBACK for managing transactions.

CURRENT DATE & TIME Functions

Key Functions:
- CURRENT_DATE: Returns the current date in 'YYYY-MM-DD' format.
- CURRENT_TIME: Returns the current time in 'HH:MM:SS.GMT+TZ' format.
- CURRENT_TIMESTAMP: Returns both the current date and time in 'YYYY-MM-DD HH:MM:SS.GMT+TZ' format.

Syntax:

  SELECT CURRENT_DATE;
  SELECT CURRENT_TIME;
  SELECT CURRENT_TIME(1);
  SELECT CURRENT_TIMESTAMP;

AGE Function

Explanation: The AGE function calculates the interval between two dates, returning the result in years, months, and days.
Syntax:
```
  AGE([date1,] date2);
```
- If date1 is not provided, the current date is used.

Example SQL Code:

  SELECT AGE('2014-04-25', '2014-01-01');
  SELECT order_line, order_date, ship_date, AGE(ship_date, order_date) AS time_taken
  FROM sales
  ORDER BY time_taken DESC;

Explanation: The first query calculates the age between the two dates. The second query calculates the time taken between order_date and ship_date for each order line.

EXTRACT Function

Explanation: The EXTRACT function retrieves specific parts of a date or time value.
Syntax:
```
  EXTRACT('unit' FROM 'date');
```
Unit Examples:
- day: Day of the month (1-31).
- epoch: Number of seconds since '1970-01-01 00:00:00 UTC'.
- year: Year as a 4-digit number.
- month: Number representing the month (1-12).
- hour, minute, second: Parts of the time.

Example SQL Code:

  SELECT EXTRACT(day FROM '2014-04-25');
  SELECT EXTRACT(minute FROM '08:44:21');
  SELECT order_line, EXTRACT(EPOCH FROM (ship_date - order_date)) FROM sales;

Explanation: Extracts the day from a date, the minute from a time, and calculates the epoch difference between ship_date and order_date.

CASE Expression

Explanation: The CASE expression allows conditional logic within a SQL query, similar to if/else statements in programming.

Syntax:

  CASE WHEN condition THEN result
  [WHEN ...]
  [ELSE result]
  END;

  CASE expression
  WHEN value THEN result
  [WHEN ...]
  [ELSE result]
  END;

Example SQL Code:
```
  SELECT *,
  CASE WHEN age < 30 THEN 'Young'
       WHEN age > 60 THEN 'Senior Citizen'
       ELSE 'Middle aged'
  END AS Age_category
  FROM customer;
```
- Explanation: This query adds an Age_category column based on the age value of each customer.
- Output Demo: Categorizes customers as 'Young', 'Senior Citizen', or 'Middle aged' based on their age.

HAVING Clause

Explanation: The HAVING clause is used with the GROUP BY clause to filter groups of rows that meet a certain condition.

Syntax:

  SELECT ColumnNames, aggregate_function(expression)
  FROM tables
  [WHERE conditions]
  GROUP BY column1
  HAVING condition;

Example SQL Code:
```
  SELECT region, COUNT(customer_id) AS customer_count
  FROM customer
  GROUP BY region
  HAVING COUNT(customer_id) > 200;
```
- Explanation: Counts the number of customers in each region and only returns regions where the customer count is greater than 200.
- Output Demo: Displays a list of regions and their respective customer counts if the count exceeds 200.

CREATE USER Statement

Explanation: The CREATE USER statement is used to create a new database account.

Syntax:

  CREATE USER user_name
  [WITH PASSWORD 'password_value' | VALID UNTIL 'expiration'];

Example SQL Code:
```
  CREATE USER starttech WITH PASSWORD 'academy';
  CREATE USER starttech WITH PASSWORD 'academy' VALID UNTIL 'Jan 1, 2020';
  CREATE USER starttech WITH PASSWORD 'academy' VALID UNTIL 'infinity';
```
- Explanation: Creates a user account named starttech with a password and optionally specifies an expiration date for the password.
- Output Demo: The user starttech is created and can log into the database with the specified credentials.

GRANT & REVOKE Permissions

Explanation: The GRANT statement assigns specific privileges to users, while the REVOKE statement removes them.

Syntax:

  GRANT privileges ON object TO user;
  REVOKE privileges ON object FROM user;

Privileges:
- SELECT: Allows performing SELECT queries.
- INSERT: Allows inserting records.
- UPDATE: Allows updating existing records.
- DELETE: Allows deleting records.
- TRUNCATE: Allows truncating tables.
- REFERENCES: Allows creating foreign keys.
- TRIGGER: Allows creating triggers.
- CREATE: Allows creating tables.
- ALL: Grants all permissions.
Example SQL Code:
```
  GRANT SELECT, INSERT, UPDATE, DELETE ON products TO starttech;
  GRANT ALL ON products TO starttech;
  GRANT SELECT ON products TO PUBLIC;
  REVOKE ALL ON products FROM starttech;
```
- Explanation: Grants various levels of access to the products table for starttech and the public and revokes all permissions from starttech.
- Output Demo: Users gain or lose the specified privileges for the products table.

DROP USER Statement

Explanation: The DROP USER statement removes a user from the database. Ensure that the user does not own any databases before dropping.
Syntax:
```
  DROP USER user_name;
```
Example SQL Code:
```
  DROP USER techonthenet;
```
- Explanation: Deletes the user techonthenet from the database.
- Output Demo: The specified user is removed from the database.

RENAME USER with ALTER USER Statement

Explanation: The ALTER USER statement is used to rename a user in the database.

Syntax:

  ALTER USER user_name RENAME TO new_name;

Example SQL Code:
```
  ALTER USER starttech RENAME TO newtech;
```
- Explanation: Renames the user starttech to newtech.
- Output Demo: The user now logs in with the new name newtech.

RENAME USER with ALTER USER Statement

Explanation: The ALTER USER statement is used to rename an existing user in the database.
Example SQL Code:
```
  ALTER USER hashnode RENAME TO HN;
```
- Explanation: Changes the username hashnode to HN.
- Output Demo: The user now logs in using the new username HN.

FIND ALL USERS

Explanation: To list all database users, you can query the pg_user table in PostgreSQL.
Syntax:
```
  SELECT usename
  FROM pg_user;
```
Output Demo: Displays a list of all users in the database.

FIND LOGGED-IN USERS

Explanation: To find currently logged-in users, query the pg_stat_activity table.

Syntax:

  SELECT DISTINCT usename
  FROM pg_stat_activity;

Output Demo: Shows unique usernames of currently active database connections.

Pattern Matching Techniques

Methods:
- LIKE: Uses % and _ for pattern matching.
- SIMILAR TO: Offers more complex matching than LIKE.
- ~ (Regular Expressions): Provides robust pattern matching capabilities using regular expressions.

LIKE Wildcards

Wildcard Descriptions:
- %: Matches any string of any length (including zero length).
- _: Matches exactly one character.
Examples:
- A%: Matches strings starting with A (e.g., ABC, ABCDE).
- %A: Matches strings ending with A (e.g., CBA).
- A%B: Matches strings starting with A and ending with B.
- AB_C: Matches strings starting with AB, followed by one character, then C.

LIKE Examples

SQL Code Block:

  SELECT * FROM customer_table
  WHERE first_name LIKE 'Jo%';

  SELECT * FROM customer_table
  WHERE first_name LIKE '%od%';

  SELECT first_name, last_name FROM customer_table
  WHERE first_name LIKE 'Jas_n';

  SELECT first_name, last_name FROM customer_table
  WHERE last_name NOT LIKE 'J%';

  SELECT * FROM customer_table
  WHERE last_name LIKE 'G\%';

Explanation: Demonstrates pattern matching using the LIKE clause, including cases for matching prefixes, infixes, and suffixes.
Output Demo: Returns rows based on the matching patterns.

REGEX Wildcards and Symbols

Symbols Explained:
- |: Alternation (either condition).
- *: Zero or more repetitions of the preceding item.
- +: One or more repetitions of the preceding item.
- ?: Zero or one repetition of the preceding item.
- {m}: Exactly m repetitions.
- {m,}: m or more repetitions.
- {m,n}: Between m and n repetitions.
- ^: Start of the string.
- $: End of the string.
- [chars]: Matches any character in the set.
- ~: Case-sensitive regex match.
- ~*: Case-insensitive regex match.

~ Operator Examples

SQL Code Block:

  SELECT * FROM customer
  WHERE customer_name ~* '^a+[a-z\s]+$';

  SELECT * FROM customer
  WHERE customer_name ~* '^(a|b|c|d)+[a-z\s]+$';

  SELECT * FROM customer
  WHERE customer_name ~* '^(a|b|c|d)[a-z]{3}\s[a-z]{4}$';

  SELECT * FROM users
  WHERE name ~* '[a-z0-9\.\-\_]+@[a-z0-9\-]+\.[a-z]{2,5}';

Explanation: Uses the ~* operator for case-insensitive pattern matching, including complex matches like email validation.
Output Demo: Matches customers or users based on specific patterns, such as starting with certain letters or matching an email format.

RIGHT JOIN Syntax

Explanation: The RIGHT JOIN returns all rows from the right table and matching rows from the left table. If there is no match, NULL values are returned for columns from the left table.

Syntax:

  SELECT table1.column1, table2.column2...
  FROM table1
  RIGHT JOIN table2
  ON table1.common_field = table2.common_field;

Output Demo: Displays all rows from table2, with matching rows from table1 or NULL if unmatched.

RIGHT JOIN Example

SQL Code Block:
```
  SELECT
    a.order_line,
    a.product_id,
    a.customer_id,
    a.sales,
    b.customer_name,
    b.age
  FROM sales_2015 AS a 
  RIGHT JOIN customer_20_60 AS b 
  ON a.customer_id = b.customer_id
  ORDER BY customer_id;
```
- Explanation: Retrieves all rows from customer_20_60 (right table) and matching rows from sales_2015 (left table). Unmatched rows in the left table are returned as NULL.
- Output Demo: Displays all customers, with corresponding sales information where available.

CREATE TABLE Syntax

SQL Code Block:

  CREATE TABLE "table_name" (
    "column 1" "data type for column 1" [column 1 constraint(s)],
    "column 2" "data type for column 2" [column 2 constraint(s)],
    ...
    "column n" "data type for column n"
    [table constraint(s)]
  );

Explanation: Defines a new table, specifying column names, data types, and optional constraints.
Output Demo: Creates a new table with the defined structure.

Constraints Overview

Common Constraints:
- NOT NULL: Ensures a column cannot contain NULL values.
- DEFAULT: Assigns a default value to a column.
- UNIQUE: Guarantees all values in a column are distinct.
- CHECK: Validates that column values meet a specific condition.
- PRIMARY KEY: Uniquely identifies each row in a table.
- FOREIGN KEY: Ensures referential integrity by linking to the primary key of another table.

Keys in Table Creation

Primary Key:
- Uniquely identifies each record.
- Can be a single or composite (multi-column) key.
Foreign Key:
- A column referencing the primary key of another table.
- Ensures data integrity across related tables.

Example of Table Structure

Customer and Order Table Key Characteristics:
- Customer Table:
  - Cust_ID (Primary Key)
  - Last_Name, First_Name
- Order Table:
  - Order_ID (Primary Key)
  - Customer_SID (Foreign Key referencing Cust_ID)
  - Order_Date, Amount

TABLESPACES

Explanation: Tablespaces designate specific file system locations for database object storage, aiding in storage management and performance optimization.

Syntax:

  CREATE TABLESPACE <tablespace_name> LOCATION '<location_on_drive>';

Example SQL Code:

  CREATE TABLESPACE newspace LOCATION '/mnt/sda1/postgresql/data';
  CREATE TABLE first_table (test_column INT) TABLESPACE newspace;

Explanation: The newspace tablespace is used to store first_table on a specific disk location, enabling flexible storage solutions.

Uses of TABLESPACES

Advantages:
- Helps manage storage across different partitions or disks, especially when existing space runs out.
- Optimizes performance by placing frequently used data on faster drives and archival data on slower, cost-effective storage.

VIEW Creation Syntax

SQL Code Block:

  CREATE [OR REPLACE] VIEW view_name AS
  SELECT columns
  FROM tables
  [WHERE conditions];

Explanation: A VIEW is a virtual table that presents data from one or more tables. It doesn't store data itself but dynamically retrieves it when queried.

CREATE VIEW Example

SQL Code Block:

  CREATE VIEW logistics AS 
  SELECT a.order_line, 
         a.order_id,
         b.customer_name,
         b.city, 
         b.state, 
         b.country 
  FROM sales AS a
  LEFT JOIN customer AS b
  ON a.customer_id = b.customer_id
  ORDER BY a.order_line;

Explanation: Creates a VIEW named logistics to simplify queries involving sales and customer data.
Output Demo: The logistics view displays order and customer details for streamlined data access.

DROP or UPDATE VIEW

Explanation: A VIEW can be dropped or updated, but it is not a physical table—it exists as a stored query joining one or more tables.
Example SQL Code:
```
  DROP VIEW logistics;

  UPDATE logistics
  SET Country = 'US'
  WHERE Country = 'United States';
```
- Explanation: The DROP VIEW statement deletes the logistics view from the database. The UPDATE statement modifies data in the logistics view, assuming the view meets conditions for updates.

VIEW Overview

Key Notes:
- Definition: A VIEW acts as a virtual table that consists of rows and columns like a real table but is based on a SQL query over one or more base tables or views.
- Characteristics:
  - Reflects data changes in underlying tables.
  - Offers abstraction and simplification of complex queries.
  - Requires minimal storage as it does not store actual data.
- Advantages:
  - Simplifies query execution for end users.
  - Saves space as it does not duplicate data.
  - Enhances data security by exposing only specific columns or data to users.

Conditions for Updatable Views

Notes:
- The SELECT clause must not contain:
  - DISTINCT
  - Aggregate functions
  - Set functions or operators
  - ORDER BY
- The FROM clause must involve a single table.
- WHERE clauses must not contain subqueries.
- GROUP BY or HAVING clauses should be avoided.
- Calculated columns cannot be updated.
- All NOT NULL columns from the base table must be present for INSERT operations.

IN Condition

Explanation: The IN condition helps reduce the use of multiple OR conditions in a query.

Syntax:

  SELECT "column_name"
  FROM "table_name"
  WHERE "column_name" IN ('value1', 'value2', ...);

Example SQL Code:
```
  SELECT *
  FROM customer
  WHERE city IN ('Philadelphia', 'Seattle');
```
- Explanation: This query retrieves customers from either Philadelphia or Seattle.
- Output Demo: Equivalent to using WHERE city = 'Philadelphia' OR city = 'Seattle'.

AND & OR Operators

Explanation: The AND and OR operators combine multiple conditions to filter data in a query.

Syntax:

  SELECT "column_name"
  FROM "table_name"
  WHERE "simple condition"
  { [AND|OR] "simple condition"}+;

Example SQL Code:

  SELECT first_name, last_name, age
  FROM customer_table
  WHERE age > 20 AND age < 30;

  SELECT first_name, last_name, age
  FROM customer_table
  WHERE age < 20 OR age > 30 OR first_name = 'John';

Explanation: The first query retrieves customers aged between 21 and 29. The second query retrieves customers aged under 20, over 30, or with the first name 'John'.

NOT Condition

Explanation: The NOT condition negates a specified condition in a SQL query.

Syntax:

  SELECT "column_name"
  FROM "table_name"
  WHERE NOT "simple condition";

Example SQL Code:

  SELECT first_name, last_name, age
  FROM employee
  WHERE NOT age = 25;

  SELECT first_name, last_name, age
  FROM employee
  WHERE NOT age = 25 AND NOT first_name = 'JAY';

Explanation: The first query excludes employees aged 25. The second excludes employees aged 25 and those with the first name 'JAY'.

UNION Operator

Explanation: The UNION operator combines result sets of two or more SELECT statements and removes duplicates.

Syntax:

  SELECT expression1, expression2, ... expression_n
  FROM tables
  [WHERE conditions]
  UNION
  SELECT expression1, expression2, ... expression_n
  FROM tables
  [WHERE conditions];

Example SQL Code:
```
  SELECT first_name, last_name
  FROM customers_2021
  UNION
  SELECT first_name, last_name
  FROM customers_2022;
```
- Explanation: Combines the results from customers_2021 and customers_2022 while eliminating duplicate rows.
- Output Demo: Displays unique first and last names from both tables.

UNION Example

SQL Code Block:
```
  SELECT customer_id
  FROM sales_2015
  UNION
  SELECT customer_id
  FROM customer_20_60
  ORDER BY customer_id;
```
- Explanation: Combines the customer_id from both sales_2015 and customer_20_60 tables, removing duplicates and ordering the results by customer_id.
- Output Demo: Returns a unified list of customer_id values across both tables without duplicates.

INDEX Creation

Explanation: An index enhances the performance of data retrieval by creating a quick lookup table for one or more columns.

Syntax:

  CREATE [UNIQUE] INDEX index_name
  ON table_name (index_col1 [ASC | DESC], index_col2 [ASC | DESC], ...);

Example SQL Code:
```
  CREATE INDEX mon_idx
  ON month_values(MM);
```
- Explanation: Creates an index named mon_idx on the MM column of the month_values table.
- Output Demo: Improves search performance on the MM column.

DROP or RENAME INDEX Syntax

Explanation: You can remove or rename an index to modify the database structure or for performance tuning.

Syntax:

  DROP INDEX [IF EXISTS] index_name [CASCADE | RESTRICT];

  ALTER INDEX [IF EXISTS] index_name
  RENAME TO new_index_name;

Example SQL Code:
```
  DROP INDEX mon_idx;
```
- Explanation: Deletes the mon_idx index from the database.
- Output Demo: The mon_idx index no longer exists in the database structure.

Good Practices for Using INDEX

Guidelines:
- Build indexes on columns of integer types.
- Keep indexes as narrow as possible.
- Pay attention to the column order in composite indexes.
- Ensure indexed columns are declared NOT NULL.
- Create indexes only when necessary.
When to Reconsider INDEX Usage:
- Avoid using indexes on small tables.
- Refrain from indexing tables with frequent, large batch updates or insert operations.
- Do not index columns with a high number of NULL values.
- Avoid indexing columns that are frequently updated or manipulated.

LENGTH Function

Explanation: The LENGTH function returns the number of characters in a specified string.
Syntax:
```
  LENGTH(string);
```
Example SQL Code:
```
  SELECT Customer_name, LENGTH(Customer_name) AS characters
  FROM customer
  WHERE age > 30;
```
- Explanation: Retrieves the Customer_name and its character count for customers older than 30.
- Output Demo: Displays the customer name along with the length of each name.

UPPER & LOWER Functions

Explanation: The UPPER function converts a string to uppercase, while the LOWER function converts it to lowercase.
Syntax:
```
  UPPER(string);
  LOWER(string);
```
Example SQL Code:
```
  SELECT UPPER(Customer_name), LOWER(Customer_name)
  FROM customer;
```
- Explanation: Converts Customer_name to both uppercase and lowercase for each record.
- Output Demo: Displays customer names in both uppercase and lowercase formats.

UPPER & LOWER Functions

Explanation: The UPPER function converts all characters in a string to uppercase, while the LOWER function converts them to lowercase.
Example SQL Code:
```
  SELECT UPPER('example text');
  SELECT LOWER('EXAMPLE TEXT');
```
- Explanation: Converts the provided string to uppercase or lowercase.
- Output Demo: Displays the string in the specified case format.

REPLACE Function

Explanation: The REPLACE function substitutes all occurrences of a specified substring within a string with another substring. It is case-sensitive.

Syntax:

  REPLACE(string, from_substring, to_substring);

Example SQL Code:
```
  SELECT 
    Customer_name,
    country,
    REPLACE(country, 'United States', 'US') AS country_new
  FROM customer;
```
- Explanation: Replaces 'United States' with 'US' in the country column for all rows.
- Output Demo: Returns customer names along with updated country names.

TRIM, LTRIM & RTRIM Functions

Explanation:
- TRIM: Removes specified characters from the beginning or end of a string.
- LTRIM: Removes characters from the left side.
- RTRIM: Removes characters from the right side.

Syntax:

  TRIM([LEADING | TRAILING | BOTH] [trim_character] FROM string);
  LTRIM(string, trim_character);
  RTRIM(string, trim_character);

Example SQL Code:
```
  SELECT TRIM(LEADING ' ' FROM '    example    ');
  SELECT LTRIM('    example', ' ');
  SELECT RTRIM('example    ', ' ');
```
- Explanation: Demonstrates removing leading and trailing spaces from strings.
- Output Demo: Shows strings with specified characters removed.

CONCAT Operator (`||`)

Explanation: The || operator concatenates two or more strings.
Syntax:
```
  string1 || string2 || string_n;
```
Example SQL Code:
```
  SELECT 
    Customer_name,
    city || ', ' || state || ', ' || country AS address
  FROM customer;
```
- Explanation: Concatenates city, state, and country into a formatted address.
- Output Demo: Displays customer names with their full address.

SUBSTRING Function

Explanation: The SUBSTRING function extracts a part of a string.

Syntax:

  SUBSTRING(string [FROM start_position] [FOR length]);

Example SQL Code:

  SELECT 
    Customer_id,
    Customer_name, 
    SUBSTRING(Customer_id FOR 2) AS cust_group
  FROM customer 
  WHERE SUBSTRING(Customer_id FOR 2) = 'AB';

  SELECT 
    Customer_id,
    Customer_name, 
    SUBSTRING(Customer_id FROM 4 FOR 5) AS cust_number
  FROM customer 
  WHERE SUBSTRING(Customer_id FOR 2) = 'AB';

Explanation: Extracts parts of Customer_id based on defined positions.
Output Demo: Displays customer data with extracted substrings.

STRING_AGG Function

Explanation: The STRING_AGG function concatenates input values into a single string, separated by a specified delimiter.
Syntax:
```
  STRING_AGG(expression, delimiter);
```
Use Case: Useful for creating a list of items in a single field or aggregating data within groups.

STRING_AGG Function Example

SQL Code Block:
```
  SELECT 
    order_id,
    STRING_AGG(product_id, ', ')
  FROM sales
  GROUP BY order_id;
```
- Explanation: Concatenates product_id values for each order_id into a single string, separated by commas.
- Output Demo: Displays order_id and a string of associated product_id values for each order.

INSERT Statement

Explanation: The INSERT INTO statement adds new records to a table.

Syntax:

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

Examples:

Single Row without Specified Columns:

  INSERT INTO customer_table
  VALUES (1, 'bee', 'cee', 32, 'bc@xyz.com');

Single Row with Specified Columns:

  INSERT INTO customer_table (cust_id, first_name, age, email_id)
  VALUES (2, 'dee', 22, 'd@xyz.com');

Multiple Rows:

  INSERT INTO customer_table
  VALUES 
  (1, 'ee', 'ef', 35, 'ef@xyz.com'),
  (2, 'gee', 'eh', 42, 'gh@xyz.com'),
  (3, 'eye', 'jay', 62, 'ij@xyz.com');

COPY Statement for CSV Import

Explanation: The COPY statement imports data from a CSV file into a table.

Syntax:

  COPY "table_name" ("column1", "column2", ...) 
  FROM 'C:\path\to\file.csv' DELIMITER ',' CSV HEADER;

Example SQL Code:
```
  COPY customer_table (cust_id, first_name, age, email_id)
  FROM 'C:\tmp\customers.csv' DELIMITER ',' CSV HEADER;
```
- Explanation: Imports data from a CSV file into the customer_table, using a comma as the delimiter and treating the first line as the header.

Conversion to String with TO_CHAR Function

Explanation: The TO_CHAR function converts numbers or dates to formatted strings.
Syntax:
```
  TO_CHAR(value, format_mask);
```

Examples:

  SELECT sales, TO_CHAR(sales, '9999.99') 
  FROM sales;

  SELECT order_date, TO_CHAR(order_date, 'MMDDYY')
  FROM sales;

  SELECT order_date, TO_CHAR(order_date, 'Month DD, YYYY')
  FROM sales;

Explanation: Formats sales as a string with a specific number format, and order_date as custom date strings.
Output Demo: Displays sales and order_date with formatted string representations.

Conversion to Date with TO_DATE Function

Explanation: The TO_DATE function converts a string to a date type.
Syntax:
```
  TO_DATE(string1, format_mask);
```
Example Parameters:
- YYYY: 4-digit year
- MM: Month (01-12)
- DD: Day of the month (01-31)
- HH24: Hour of the day (00-23)
Example SQL Code:
```
  SELECT TO_DATE('2024-03-25', 'YYYY-MM-DD');
```
- Explanation: Converts the string 2024-03-25 to a date type in YYYY-MM-DD format.
- Output Demo: Displays the date value in the database's standard date format.

TO_DATE Function Examples

Explanation: The TO_DATE function converts a string to a date type based on the provided format mask.
Example SQL Code:
```
  SELECT TO_DATE('2014/04/25', 'YYYY/MM/DD');
  SELECT TO_DATE('033114', 'MMDDYY');
```
- Explanation: Converts strings like '2014/04/25' and '033114' into date formats using specified format masks.
- Output Demo: Displays the converted date values.

TO_NUMBER Function

Explanation: The TO_NUMBER function converts a string into a numerical value, which is useful when working with numeric data stored as text.
Syntax:
```
  TO_NUMBER(string1, format_mask);
```
Example SQL Code:
```
  SELECT TO_NUMBER('1210.73', '9999.99');
  SELECT TO_NUMBER('$1,210.73', 'L9,999.99');
```
- Explanation: Converts formatted strings into numeric values. The format mask defines how the string should be interpreted (e.g., with or without currency symbols, decimal points).
- Output Demo: Returns numerical representations of the input strings.

GROUP BY Clause

Explanation: The GROUP BY clause groups results by one or more columns and is often used with aggregate functions like COUNT, SUM, MIN, MAX, and AVG.

Syntax:

  SELECT "column_name1", "function_type"("column_name2")
  FROM "table_name"
  GROUP BY "column_name1";

Examples:

  SELECT region, COUNT(customer_id) AS customer_count
  FROM customer
  GROUP BY region;

  SELECT product_id, SUM(quantity) AS quantity_sold
  FROM sales
  GROUP BY product_id
  ORDER BY quantity_sold DESC;

  SELECT customer_id,
         MIN(sales) AS min_sales,
         MAX(sales) AS max_sales,
         AVG(sales) AS average_sales,
         SUM(sales) AS total_sales
  FROM sales
  GROUP BY customer_id
  ORDER BY total_sales DESC
  LIMIT 5;

Explanation: The first query counts customers by region, the second sums product quantities by product_id, and the third aggregates sales data per customer_id.
Output Demo: Groups data and applies aggregation functions to the grouped results.

MIN/MAX Functions

Explanation: The MIN function returns the minimum value, while the MAX function returns the maximum value of an expression.

Syntax:

  SELECT MIN(aggregate_expression)
  FROM tables
  [WHERE conditions];

  SELECT MAX(aggregate_expression)
  FROM tables
  [WHERE conditions];

Example SQL Code:

  SELECT MIN(sales) AS min_sales_june15
  FROM sales
  WHERE order_date BETWEEN '2015-06-01' AND '2015-06-30';

  SELECT MAX(sales) AS max_sales_june15
  FROM sales
  WHERE order_date BETWEEN '2015-06-01' AND '2015-06-30';

Explanation: Finds the minimum and maximum sales within June 2015.
Output Demo: Displays the minimum and maximum sales figures for the given date range.

55 Essential SQL Functions with Sample Codes & Output

Table of contents

SELECT with WHERE, ORDER BY, and LIMIT

LEFT JOIN

LEFT JOIN Example

Types of SQL JOINS

INNER JOIN Syntax

INNER JOIN Example with Specific Columns

TRUNCATE Syntax

TRUNCATE TABLE Example

Using AS for Column and Table Aliases

AS Example for Column Aliases

TRUNCATE TABLE Example

COUNT Syntax

COUNT Function Example

LIKE Pattern Matching Syntax

Wildcards in LIKE

LIKE Pattern Examples

UPDATE Syntax

UPDATE Examples

DELETE Syntax

DELETE Examples

Types of SQL JOINS

CROSS JOIN Syntax

CROSS JOIN Example

ORDER BY Clause

ORDER BY Examples

EXPLAIN Query Plan

SOFT DELETE vs HARD DELETE

UPDATE Best Practices

VACUUM Command

TRUNCATE vs DELETE

Pattern Matching Best Practices

JOIN Best Practices

Schemas in Databases

BETWEEN Condition Syntax

BETWEEN Condition Examples

Data Types Overview

CREATE TYPE Syntax

EXCEPT Operator Syntax

EXCEPT Example

SELECT Statement Syntax

SELECT Examples

SELECT DISTINCT Syntax

SELECT DISTINCT Examples

CREATE TABLE Syntax

Key Constraints

WHERE Clause Syntax

WHERE Clause Examples

ACID Properties

AVG Function Syntax

AVG Function Examples

Subquery Syntax

Subquery Examples

Subquery Rules

FULL OUTER JOIN Syntax

FULL OUTER JOIN Example

ALTER TABLE Syntax

ADD & DROP Columns Syntax

MODIFY & RENAME Columns Syntax

ADD & DROP Constraints Syntax

Data Management Basics

Fundamental SQL Commands

Data Filtering and Sorting

Aggregate Functions and GROUP BY

Joins Overview

Advanced SQL Concepts

String Functions

Mathematical Functions

Date Time Functions

Data Type Conversion Functions

Performance Tuning

Pattern Matching

Bonus Lectures

SUM Function Syntax

SUM Function Examples

CEIL & FLOOR Functions

RANDOM Function

SETSEED Function

ROUND Function

CONCAT Operator (`||`)