1. Delete Table Row

Explanation: The DELETE statement is used to delete existing records in a table. Be cautious when using this command as it removes data permanently.

Example:


DELETE FROM table_name WHERE condition;
Use Case: To delete a record with the id of 5 from the students table:

DELETE FROM students WHERE id = 5;

2. Drop Table

Explanation: The DROP TABLE statement is used to delete an existing table from the database. This will remove the table structure and all the data, so use with caution.

Example:

DROP TABLE table_name;
Use Case: To delete the students table:
DROP TABLE students;

3. Order By

Explanation: The ORDER BY keyword is used to sort the result-set in ascending or descending order based on one or more columns.

Example:



SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;
Use Case: To select all records from the students table and sort the result by the name column in ascending order:
SELECT * FROM students ORDER BY name ASC;

4. Placing Conditions on Groups – HAVING Clause

Explanation: The HAVING clause is used instead of WHERE with the GROUP BY clause to filter the results of a grouping. It allows you to filter the results after they have been grouped.

Example:



SELECT column1, column2, …
FROM table_name
GROUP BY column_name
HAVING condition;
Use Case: Imagine we have a sales table and we want to find the total sales for each product, but only display products that have total sales greater than 100 units:

SELECT product, SUM(sale_amount) as TotalSales
FROM sales
GROUP BY product
HAVING TotalSales > 100;

5.SUM()

Function: Adds up the values in a numeric column.
Syntax: SELECT SUM(column_name) FROM table_name WHERE condition;
Example: SELECT SUM(salary) FROM employees WHERE department=’HR’;

6 .AVG()

Function: Calculates the average value of a numeric column.
Syntax: SELECT AVG(column_name) FROM table_name WHERE condition;
Example: SELECT AVG(salary) FROM employees WHERE department=’HR’;

7. MAX()

Function: Returns the highest value in a numeric or date column.
Syntax: SELECT MAX(column_name) FROM table_name WHERE condition;
Example: SELECT MAX(salary) FROM employees;

8. MIN()

Function: Returns the lowest value in a numeric or date column.
Syntax: SELECT MIN(column_name) FROM table_name WHERE condition;
Example: SELECT MIN(salary) FROM employees;

9. COUNT()

Function: Counts the number of rows that match a specified condition.
Syntax: SELECT COUNT(column_name) FROM table_name WHERE condition;
Example: SELECT COUNT(employee_id) FROM employees WHERE department=’HR’;

10. COUNT(*)

Function: Counts all rows in a table.
Syntax: SELECT COUNT(*) FROM table_name;
Example: SELECT COUNT(*) FROM employees;

Joins

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

Types of Joins

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN (Not supported in MySQL)

INNER JOIN

    SELECT columns FROM table1
    INNER JOIN table2
    ON table1.column = table2.column;
    

Table Aliases

Table aliases are used to give a table a temporary name for the duration of a query.

    SELECT column FROM table AS alias;
    

Equi-Join

An equi-join is a type of join that combines rows based on the equality between specified columns from within the participating tables.

    SELECT columns FROM table1, table2
    WHERE table1.column = table2.column;
    

Example Tables

employees Table

id	name	department_id
1	Alice	1
2	Bob	2
3	Charlie	1
4	David	3

departments Table

id	name
1	HR
2	Marketing
3	Finance

MySQL Constraint Types

In MySQL, constraints are used to specify rules for the data in a table.

Types of Constraints

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK

NOT NULL Constraint

Ensures that a column cannot contain a NULL value.

    CREATE TABLE employees (
        id INT NOT NULL,
        name VARCHAR(50) NOT NULL,
        department_id INT NOT NULL
    );
    

UNIQUE Constraint

Ensures that all values in a column are unique.

    CREATE TABLE employees (
        id INT UNIQUE,
        name VARCHAR(50),
        department_id INT
    );
    

PRIMARY KEY Constraint

Uniquely identifies each record in a table.

    CREATE TABLE employees (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        department_id INT
    );
    

FOREIGN KEY Constraint

Uniquely identifies a row/record in another table.

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

CHECK Constraint

Ensures that all values in a column satisfy a specific condition.

    CREATE TABLE employees (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        department_id INT CHECK (department_id > 0)
    );
    

Example Tables

employees Table

id	name	department_id
1	Alice	1
2	Bob	2
3	Charlie	1
4	David	3

departments Table

id	name
1	HR
2	Marketing
3	Finance