Advance SQL

In the process of data analysis, understanding the data is the most crutial part. It's important to know the tables, views, and their relationships. That’s why we create ERD(Entity Relationship Diagram) for the better understanding of the data.

Creating ERD in MySQL workbench

1st, select the data → the go to ‘Database’ tab → click in ‘Reverse Engineer’ → a dialogue box will be open, click next and next and you will get the ERD of that data.

(shortcut Ctrl+R)

Here is the ERD of ‘sakila’ database

ER diagram helps visualize and understand the data structure and relationships in the database.

Primary key & Foreign key

Primary Key: A primary key uniquely identifies each record in a table. It must contain unique values and It cannot contain NULL values.

For Example: In this ‘Sakila’ data’s customer table, the customer_id column is the primary key because it uniquely identifies each customer.

Foreign Key: A foreign key establishes a relationship between two tables. It refers to the primary key in another table, creating a link between records in different tables.

For Example: city_id in the address table is a foreign key that references city_id in the city table, linking each address to a specific city.

CTE or Common Table Expressions

• CTE or Common Table Expressions allows us to define a query as a building block and create a variable to hold this query

• The variable acts like a table and we can refer to that table elsewhere in the query

example -

WITH TotalSales AS (
    SELECT 
        e.EmployeeID,
        e.EmployeeName,
        SUM(s.SaleAmount) AS TotalSalesAmount
    FROM 
        Employees e
    JOIN 
        Sales s ON e.EmployeeID = s.EmployeeID
    GROUP BY 
        e.EmployeeID, e.EmployeeName
)

Now using the CTE

SELECT 
    EmployeeName, 
    TotalSalesAmount
FROM 
    TotalSales
ORDER BY 
    TotalSalesAmount DESC;