Images​

docker images                   # List all images
docker pull <image_name>         # Pull an image from Docker Hub
docker build -t <image_name> .   # Build an image from the current directory
docker rmi <image_id>            # Remove an image

Containers​

docker ps                       # List running containers
docker ps -a                    # List all containers
docker run <image_name>         # Create and start a container
docker exec -it <container_id> bash   # Access a running container's shell
docker stop <container_id>      # Stop a running container
docker rm <container_id>        # Remove a container

Volumes​

docker volume ls                # List all volumes
docker volume create <volume_name>  # Create a volume
docker run -v <volume_name>:/path/in/container <image_name>   # Mount a volume to a container

Networks​

docker network ls               # List all networks
docker network create <network_name>  # Create a network
docker run --network=<network_name> <image_name>   # Connect a container to a network

Compose​

docker-compose up               # Start services defined in a docker-compose.yml
docker-compose down             # Stop and remove services defined in a docker-compose.yml

Registry​

docker login                    # Log in to a Docker registry
docker push <image_name>         # Push an image to a registry
docker pull <registry>/<image_name>  # Pull an image from a registry

System​

docker info                     # Display system-wide information
docker version                  # Show the Docker version
docker system prune             # Remove all stopped containers, unused networks, and dangling images

Dockerize Applications​

docker build -t <image_name> .         # Build a Docker image
docker run -p <host_port>:<container_port> <image_name>   # Run a Docker container

Git Basics​

git init
git clone <repository_url>
git status

Staging and Commits​

git add <file(s)>
git commit -m "Commit message"
git reset --soft HEAD^  // Undo Last Commit (Keep Changes)
git reset --hard HEAD^  // Undo Last Commit (Discard Changes)

Branching​

git branch <branch_name>
git checkout <branch_name>
git checkout -b <branch_name>
git merge <branch_name>
git branch -d <branch_name>

Remote Repositories​

git remote add <remote_name> <repository_url>
git push <remote_name> <branch_name>
git pull <remote_name> <branch_name>

Logging and History​

git log
git show <commit_hash>
git diff

Miscellaneous​

touch .gitignore  // Ignore Files (Create .gitignore)
git checkout -- <file(s)>  // Undo Changes in Working Directory
git reset HEAD <file(s)>  // Undo Staged Changes

Cherry-pick​

git cherry-pick <commit_hash>

Rebase​

git rebase <base_branch>
git rebase -i <base_branch>  // Interactive rebase

Squash Commits during Rebase​

// Change "pick" to "squash" for the commits you want to squash
// Follow on-screen instructions to edit the commit messages
git rebase -i <base_branch>

Amend the Last Commit​

git commit --amend

Stash Changes​

git stash
git stash save "Stash message"

Apply Stashed Changes​

git stash apply
git stash pop  // Apply and remove from stash

View Stash List​

git stash list

Show Differences with Stash​

git stash show -p <stash_id>

Discard Stashed Changes​

git stash drop <stash_id>
git stash clear  // Remove all stashes

Tagging​

git tag <tag_name>  // Create lightweight tag
git tag -a <tag_name> -m "Tag message"  // Create annotated tag
git push origin <tag_name>  // Push tag to remote

Submodules​

git submodule add <repository_url> <path>  // Add submodule
git submodule update --init --recursive  // Initialize submodules
git submodule foreach git pull origin master  // Update submodules

Git Configurations​

git config --global user.name "Your Name"
git config --global user.email "your@email.com"

Show Configurations​

git config --list

1. c# basic(datatype, exception, ...) -> important
2. Algorithm (bubble sort, selected sort, ...) -> quick view
3. OOP (4 ) -> important
4. SOLID (5) -> quick view
5. C# advance (delegate, generic, async ) -> optional
6. SQL(join(8), understand func vs store procedure) -> important
7. Linq -> quick view
8. ef core -> quick view
9. webapi -> optional

Create and Build Projects​

dotnet new console -n MyConsoleApp    // Create a new console application
dotnet new webapi -n MyWebApi         // Create a new Web API project
dotnet build                          // Build the project

Run Applications​

dotnet run                            // Run the application
dotnet run --project <project_path>   // Run a specific project
dotnet watch run                      // Run with file watching

Add Dependencies​

dotnet add package <package_name>     // Add a NuGet package
dotnet restore                        // Restore dependencies

Generate Code​

dotnet new classlib -n MyLibrary      // Create a class library
dotnet add reference <project_path>   // Add a project reference
dotnet publish -c Release             // Publish the application

Unit Testing​

dotnet new xunit -n MyTests           // Create xUnit test project
dotnet test                           // Run tests

Entity Framework Core​

dotnet ef migrations add <migration_name>    // Add a migration
dotnet ef database update                  // Apply migrations

Publish and Deploy​

dotnet publish -c Release --self-contained // Publish a self-contained application

Package Management​

dotnet nuget push -k <api_key> -s <source> <package.nupkg>    // Publish a NuGet package

ASP.NET Core Identity​

dotnet new identity -n MyIdentityApp   // Create an ASP.NET Core Identity project

Azure Functions​

dotnet new func -n MyFunction          // Create an Azure Functions project

Clean Up​

dotnet clean                          // Clean the build output

SQL Clauses​

• WHERE, ORDER BY, GROUP BY, and HAVING: Crafting strategic queries.
• Today's exploration: INNER JOIN, OUTER JOIN, EXCLUDING JOIN, SELF JOIN, CROSS JOIN, UNION, and UNION ALL.

Why Do We Need JOIN?​

• Combining data from multiple tables based on matching conditions for comprehensive analysis.

INNER JOIN: A Deeper Dive​

-- Example of INNER JOIN
SELECT c.CustomerID, c.FullName, o.FoodName
FROM Customer c
INNER JOIN CustomerOrder o ON c.CustomerID = o.CustomerID;

In this practice, we retrieve customer information along with their corresponding orders using INNER JOIN.

JOIN More Than 2 Tables​

-- Example of INNER JOIN with 3 tables
SELECT c.CustomerID, c.FullName, o.FoodName, d.FullAddress
FROM Customer c
INNER JOIN CustomerOrder o ON c.CustomerID = o.CustomerID
INNER JOIN DeliveryAddress d ON d.ID = o.DeliveryAddressID;

Extending the concept of INNER JOIN to involve three tables for a more comprehensive result set.

LEFT JOIN: Embracing Incompleteness​

-- Example of LEFT JOIN
SELECT c.CustomerID, c.FullName, o.FoodName
FROM Customer c
LEFT JOIN CustomerOrder o ON c.CustomerID = o.CustomerID;

Incorporating LEFT JOIN to include all customers, even those without orders.

RIGHT JOIN: Balancing the Equation​

-- Example of RIGHT JOIN
SELECT c.CustomerID, c.FullName, o.FoodName
FROM Customer c
RIGHT JOIN CustomerOrder o ON c.CustomerID = o.CustomerID;

Implementing RIGHT JOIN to include all orders, even those without customers.

FULL JOIN: Embracing Completeness​

-- Example of FULL JOIN
SELECT c.CustomerID, c.FullName, o.FoodName
FROM Customer c
FULL JOIN CustomerOrder o ON c.CustomerID = o.CustomerID;

Utilizing FULL JOIN to encompass all customers and orders, regardless of matches.

LEFT EXCLUDING JOIN: Seeking the Unique​

-- Example of LEFT EXCLUDING JOIN
SELECT c.CustomerID, c.FullName, o.FoodName
FROM Customer c
LEFT JOIN CustomerOrder o ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL;

Applying LEFT EXCLUDING JOIN to identify customers without orders.

RIGHT EXCLUDING JOIN: Excluding to the Right​

-- Example of RIGHT EXCLUDING JOIN
SELECT c.CustomerID, c.FullName, o.FoodName
FROM Customer c
RIGHT JOIN CustomerOrder o ON c.CustomerID = o.CustomerID
WHERE c.CustomerID IS NULL;

Implementing RIGHT EXCLUDING JOIN to identify orders without customers.

OUTER EXCLUDING JOIN: Excluding in Unison​

-- Example of OUTER EXCLUDING JOIN
SELECT c.CustomerID, c.FullName, o.FoodName
FROM Customer c
FULL JOIN CustomerOrder o ON c.CustomerID = o.CustomerID
WHERE c.CustomerID IS NULL OR o.CustomerID IS NULL;

Integrating OUTER EXCLUDING JOIN to identify unmatched records from both tables.

SELF JOIN: Connecting Within​

-- Example of SELF JOIN
SELECT emp.ID, emp.FullName, manager.FullName AS Manager 
FROM Employee emp
INNER JOIN Employee manager ON emp.ManagerID = manager.ID;

Demonstrating the concept of SELF JOIN to connect records within the same table.

CROSS JOIN: Expanding Horizons​

-- Example of CROSS JOIN
SELECT *
FROM A
CROSS JOIN B;

Expanding horizons with CROSS JOIN to combine each row from one table with each row from another.

UNION: Merging Similarities​

-- Example of UNION
SELECT FromColumnTableA FROM A
UNION
SELECT FromColumnTableB FROM B;

Merging similarities with UNION to combine result-sets from two tables.

UNION ALL: Embracing All​

-- Example of UNION ALL
SELECT FromColumnTableA FROM A
UNION ALL
SELECT FromColumnTableB FROM B;

Embracing all with UNION ALL, including duplicates in the result set.

Sub Queries and Advanced Operators​

• Exploring the power of subqueries.
• Advanced operators: EXISTS, ALL, IN, ANY.

What We Will Explore Today?​

• Sub queries, advanced operators, rules of subqueries, and practical exercises.

Subquery Basics​

• A sub-query, or inner query, nested inside a larger query.
• Works independently within the outer query.
• Execution sequence: Inner query executes first, results stored, and outer query runs on stored results.
• Exception: Correlated subqueries reference outer query columns.

Example 1: Subquery for Average​

-- Example of Subquery
USE LECTURE4_FUNCTION
SELECT LastName, Physic, (SELECT AVG(Physic * 1.0) FROM Student) AS 'AVG OF Physic'
FROM Student;

Example 2: Subquery in WHERE Clause​

-- Example of Subquery in WHERE
USE LECTURE5_JOIN_DEMO
SELECT CustomerID, FullName
FROM Customer
WHERE CustomerID IN (SELECT CustomerID FROM CustomerOrder);

Types of Subqueries​

• Single-row subquery.
• Multiple-row subquery.
• Multiple-column subquery.
• Correlated subquery.
• Nested subquery.

Single-Row Subquery​

-- Example of Single-Row Subquery
USE LECTURE5_JOIN_DEMO;
SELECT CustomerID, FullName
FROM Customer
WHERE CustomerID = (SELECT CustomerID
FROM CustomerOrder
WHERE FoodName = 'Heo Quay');

Exercise: Single-Row Subquery​

Query CustomerID, OrderID, FoodName from CustomerOrder with Delivery FullAddress = 'TP. HCM' using a single-row subquery.

Multiple-Row Subquery​

-- Example of Multiple-Row Subquery
USE LECTURE5_JOIN_DEMO
SELECT CustomerID, FullName
FROM Customer
WHERE CustomerID IN (SELECT CustomerID FROM CustomerOrder);

Exercise: Multiple-Row Subquery​

Query CustomerID, OrderID, FoodName from CustomerOrder with Delivery FullAddress = 'TP. HCM' or 'TP. HA NOI' using a multiple-row subquery.

Multiple-Column Subquery​

-- Example of Multiple-Column Subquery
USE LECTURE5_JOIN_DEMO;
SELECT CustomerID, FullName
FROM Customer 
WHERE EXISTS
(SELECT CustomerID, OrderID, FoodName
FROM CustomerOrder
WHERE CustomerOrder.CustomerID = Customer.CustomerID);

Exercise: Multiple-Column Subquery​

Create a database, perform a self-join on the Employee table, and query managers with at least 1 employee.

Correlated Subquery​

• Normal subquery executes first and provides a value to the outer query.
• Correlated subquery references a column in the outer query and executes the subquery once for each row in the outer query.

-- Example of Correlated Subquery
USE LECTURE5_JOIN_DEMO;
SELECT CustomerID, FullName
FROM Customer 
WHERE EXISTS
(SELECT CustomerID, OrderID, FoodName
FROM CustomerOrder
WHERE CustomerOrder.CustomerID = Customer.CustomerID);

Exercise: Correlated Subquery​

Create a database, perform a self-join on the Employee table, and query managers with at least 1 employee.

Nested Subquery​

-- Example of Nested Subquery
USE LECTURE5_JOIN_DEMO;
SELECT CustomerID, FullName
FROM Customer
WHERE CustomerID IN (SELECT CustomerID 
FROM CustomerOrder
WHERE DeliveryAddressID = (SELECT ID
FROM DeliveryAddress
WHERE FullAddress = 'TP. HA NOI'));

Rules of Subqueries​

• Enclose a subquery in parentheses.
• Must include a SELECT clause and a FROM clause.
• Subqueries that return more than one row can only be used with multiple-value operators.
• Can include WHERE, GROUP BY, and HAVING clauses.
• Can include an ORDER BY clause only with a TOP clause.
• Can nest subqueries up to 32 levels.

Advanced Operators: EXISTS​

• Used to test for the existence of any record in a subquery.

-- Example of EXISTS
USE LECTURE5_JOIN_DEMO;
SELECT CustomerID, FullName
FROM Customer 
WHERE EXISTS
(SELECT CustomerID, OrderID, FoodName
FROM CustomerOrder
WHERE CustomerOrder.CustomerID = Customer.CustomerID);

Advanced Operators: ALL​

• Returns TRUE if ALL of the subquery values meet the condition.

-- Example of ALL
USE LECTURE5_JOIN_DEMO;
SELECT CustomerID, FullName
FROM Customer
WHERE CustomerID = ALL (SELECT CustomerID FROM CustomerOrder);

Advanced Operators: IN​

• Allows specifying multiple values in a WHERE clause.

-- Example of IN
USE LECTURE5_JOIN_DEMO;
SELECT CustomerID, FullName
FROM Customer
WHERE CustomerID IN (SELECT CustomerID FROM CustomerOrder);

Advanced Operators: ANY​

• Allows performing a comparison between a single column value and a range of other values.

-- Example of ANY
USE LECTURE5_JOIN_DEMO;
SELECT CustomerID, FullName
FROM Customer
WHERE CustomerID = ANY (SELECT CustomerID FROM CustomerOrder);

Data Manipulation Language (DML) Operations:​

DML operations involve the manipulation of data within a database.

INSERT Data:​

-- Single Record
INSERT INTO Student(ID, FullName, Email, PhoneNumber, Math)
VALUES(5, 'HUY', 'HUY@gmail.com', '0123456789', 5);

-- Multiple Records
INSERT INTO Student(ID, FullName, Email, PhoneNumber, DateOfBirth, Math)
VALUES
    (4, 'LAN', 'LAN@gmail.com', '0123456789', '1/30/1999', 7),
    (3, 'HAO', 'HAO@gmail.com', '0123456789', '12/15/2000', 8);

The INSERT statement adds new records to a table.

UPDATE Data:​

-- Update Single Record
UPDATE Student
SET FullName = 'I AM A GOOD BOY'
WHERE ID = 3;

-- Update Multiple Records
UPDATE Student
SET FullName = 'Updated Name'
WHERE Math > 5;

The UPDATE statement modifies existing records in a table.

DELETE Data:​

-- Delete Single Record
DELETE FROM Student
WHERE ID = 3;

-- Delete All Records with a Condition
DELETE FROM Student
WHERE Math > 5;

-- Truncate Table (Remove all records)
TRUNCATE TABLE Parent;

The DELETE statement removes records from a table.

Operators:​

Operators perform various operations in SQL.

Arithmetic Operators:​

SELECT 30 + 20 AS 'ADDITION';
SELECT 30 - 20 AS 'SUBTRACTION';
SELECT 30 * 20 AS 'MULTIPLICATION';
SELECT 30 / 20 AS 'DIVISION';
SELECT 30 % 20 AS 'MODULO';

Arithmetic operators perform mathematical operations.

Comparison Operators:​

-- Equal To
SELECT ID, FullName
FROM Student
WHERE FullName = 'Lan Anh';

-- Greater Than
SELECT ID, FullName, Math
FROM Student
WHERE Math > 5;

Comparison operators compare values for equality, inequality, etc.

Logical Operators:​

-- AND Operator
SELECT ID, FullName
FROM Student
WHERE FullName = 'Lan Anh' AND ID = 1010;

-- OR Operator
SELECT ID, FullName
FROM Student
WHERE FullName = 'Lan Anh' OR ID = 1005;

-- NOT Operator
SELECT ID, FullName, Math
FROM Student
WHERE NOT Math > 5;

Logical operators combine conditions using AND, OR, and NOT.

BETWEEN - AND:​

-- Values within a Range
SELECT ID, FullName
FROM Student
WHERE ID BETWEEN 1003 AND 1005;

The BETWEEN - AND operator selects values within a specified range.

LIKE Operator:​

-- Pattern Matching
SELECT FullName, Math
FROM Student
WHERE FullName LIKE '%U%';

The LIKE operator searches for a specified pattern in a column.

IS NULL:​

-- Check for NULL Values
SELECT ID, FullName, Math
FROM Student
WHERE Math IS NULL;

The IS NULL operator checks for NULL values in a column.

SELECT Statement:​

The SELECT statement retrieves data from one or more tables.

ORDER BY Clause:​

-- Ascending Order
SELECT *
FROM Student
ORDER BY FullName ASC;

-- Descending Order
SELECT *
FROM Student
ORDER BY FullName DESC;

-- Order by Multiple Columns
SELECT *
FROM Student
ORDER BY FullName ASC, Math ASC;

The ORDER BY clause sorts query results in ascending or descending order.

TOP, PERCENT, and WITH TIES:​

-- SELECT TOP N Rows
SELECT TOP 3 *
FROM Student;

-- SELECT TOP N Percent of Rows
SELECT TOP 50 PERCENT ID, FullName
FROM Student;

TOP limits the number of rows returned, and PERCENT filters by a percentage.

DISTINCT Keyword:​

-- Select Distinct Values
SELECT DISTINCT FullName
FROM Student;

-- Select Distinct Values for Multiple Columns
SELECT DISTINCT ID, FullName
FROM Student;

The DISTINCT keyword removes duplicate rows from query results.

SELECT INTO Statement:​

-- Create a New Table from SELECT Query
SELECT ID, FullName
INTO NewTable
FROM Student;

-- Select Data from Newly Created Table
SELECT *
FROM NewTable;

The SELECT INTO statement creates a new table from the result of a query.

VIEW:​

A view is a virtual table based on the result of a SELECT statement.

CREATE VIEW:​

-- Create a View
CREATE VIEW HelloView AS
SELECT FullName, Math
FROM Student
WHERE Math > 5;

-- Select Data from the View
SELECT *
FROM HelloView;

Use CREATE VIEW to define a view.

SQL Operators (Continued):​

Additional SQL operators and their applications.

NOT NULL and IS NOT NULL:​

-- Records with NULL Values
SELECT ID, FullName, Math
FROM Student
WHERE Math IS NULL;

-- Records with Non-NULL Values
SELECT ID, FullName, Math
FROM Student
WHERE Math IS NOT NULL;

Check for NULL or non-NULL values using IS NULL and IS NOT NULL .

Wildcards in LIKE Operator:

Utilize % and _ for pattern matching in the LIKE operator.

Wildcards in LIKE Operator:​

-- Pattern Matching with Wildcards
SELECT FullName, Math
FROM Student
WHERE FullName LIKE '%U%';

% represents zero or more characters, and _ represents a single character in pattern matching.

JOIN Operation:​

The JOIN operation combines rows from two or more tables based on related columns.

JOIN Operation:​

-- Combine Rows from Two Tables
SELECT Student.ID, Student.FullName, Course.CourseName
FROM Student
JOIN Course ON Student.CourseID = Course.CourseID;

Use the JOIN keyword with specified conditions for combining rows.

GROUP BY and HAVING:​

The GROUP BY clause groups rows that have the same values in specified columns, and HAVING applies conditions to grouped data.

GROUP BY and HAVING:​

-- Group by CourseID and Calculate Average Math Score
SELECT CourseID, AVG(Math) AS AvgMathScore
FROM Student
GROUP BY CourseID
HAVING AVG(Math) > 7;

Use the GROUP BY clause for grouping and apply conditions with HAVING.

Authentic Operators:​

Arithmetic:​

-- Mathematical Wonders
SELECT 5 + 3 AS 'Addition', 10 - 4 AS 'Subtraction', 6 * 2 AS 'Multiplication', 16 / 4 AS 'Division', 17 % 5 AS 'Modulo';

In this example, we perform basic arithmetic operations on numerical values, showcasing the versatility of SQL in handling mathematical tasks.

Comparison:​

-- Unveiling Equality and More
SELECT ID, FullName
FROM Students
WHERE Age = 25 OR Age > 30;

Here, we retrieve student records based on age, utilizing comparison operators to filter results for specific age criteria.

Logical:​

-- Crafting Conditions with AND, OR, and NOT
SELECT ID, FullName
FROM Students
WHERE Age > 25 AND Department = 'Engineering';

Crafting a query that combines logical operators to filter students who are older than 25 and belong to the Engineering department.

Selecting Wisely:​

SELECT Syntax:​

-- Crafting the Perfect SELECT Statement
SELECT FirstName, LastName, Age
FROM Employees;

In this example, we select specific columns from the Employees table, demonstrating the fundamental syntax of the SELECT statement.

TOP & PERCENT:​

-- Limiting and Filtering Rows
SELECT TOP 5 ID, ProductName, Price
FROM Products
ORDER BY Price DESC;

Limiting query results to the top 5 rows, showcasing the use of TOP in conjunction with ORDER BY to filter and sort data effectively.

ALIAS & DISTINCT:​

-- Bringing Clarity to Your Results
SELECT AVG(Salary) AS 'Average Salary'
FROM Employees;

Calculating the average salary and assigning it an alias for clarity, highlighting the use of aliases in result sets.

FROM, WHERE, VIEW, SELECT INTO:​

-- Mastering the Essentials
SELECT EmployeeID, FirstName, LastName
INTO NewEmployeeTable
FROM Employees
WHERE Department = 'IT';

Creating a new table named NewEmployeeTable by selecting specific columns from the Employees table and filtering results based on the IT department.

SQL Built-in Functions: A Symphony of Capabilities​

String Functions Showcase:​

LOWER & UPPER:​

-- Transforming Case with Flair
SELECT LOWER('Hello') AS 'lower function', UPPER('Hi there') AS 'UPPER FUNCTION';

Applying the LOWER and UPPER functions to manipulate text case, showcasing the versatility of string functions.

LEN & REVERSE:​

-- Revealing Lengths and Reversing Strings
SELECT LEN('Database') AS 'Length', REVERSE('SQL') AS 'Reversed';

Using LEN to find the length of a string and REVERSE to reverse the characters, demonstrating string manipulation capabilities.

CONCAT & SUBSTRING:​

-- Crafting and Extracting Strings
SELECT CONCAT('Hello', ' ', 'World') AS 'CONCAT FUNCTION', SUBSTRING('123456789', 3, 4) AS 'SUBSTRING FUNCTION';

Combining strings with CONCAT and extracting a substring, exemplifying the power of string functions in SQL.

LTRIM & RTRIM:​

-- Trimming with Finesse
SELECT '|' + LTRIM('   Hi') + '|' AS 'LTRIM FUNCTION', '|' + RTRIM('Hi   ') + '|' AS 'RTRIM FUNCTION';

Trimming leading and trailing spaces using LTRIM and RTRIM, showcasing string manipulation for cleaner outputs.

Datetime Functions: Unraveling Time's Mysteries:​

MONTH, DAY, YEAR:​

-- Extracting Temporal Insights
SELECT MONTH('11/13/2022') AS 'MONTH FUNCTION', DAY('11/13/2022') AS 'DAY FUNCTION', YEAR('11/13/2022') AS 'YEAR FUNCTION';

Extracting month, day, and year components from a date, unraveling temporal insights using datetime functions.

GETDATE & ISDATE:​

-- Unleashing the Power of Time
SELECT GETDATE() AS 'GETDATE FUNCTION', ISDATE('11/13/2022') AS 'ISDATE FUNCTION';

Utilizing GETDATE to retrieve the current date and time, along with ISDATE to validate a date string.

DATEPART:​

-- Precision in Date and Time
SELECT DATEPART(HOUR, '11/13/2022 19:20') AS 'DATEPART HOUR', DATEPART(MINUTE, '11/13/2022 19:20') AS 'DATEPART MINUTE';

Extracting specific components like hour and minute using DATEPART, providing precision in date and time manipulation.

Date Manipulation with DATEDIFF:​

-- Exploring the Gap Between Dates
SELECT DATEDIFF(MONTH, '10/15/2020', '11/25/2022') AS 'DATEDIFF MONTHS';

Calculating the difference in months between two dates using DATEDIFF, demonstrating date manipulation capabilities.

Aggregate Functions: Bringing Data Together​

SUM & AVG:​

-- Summing Up and Finding Averages
SELECT SUM(Sales) AS 'Total Sales', AVG(Price) AS 'Average Price'
FROM Products;

Aggregating data with SUM and AVG, showcasing their roles in summarizing numeric values.

MIN & MAX:​

-- Uncovering Extremes
SELECT MIN(Age) AS 'Minimum Age', MAX(Age) AS 'Maximum Age'
FROM Employees;

Identifying the minimum and maximum values within a dataset using MIN and MAX aggregate functions.

COUNT:​

-- Counting Rows Strategically
SELECT COUNT(*) AS 'Total Records'
FROM Customers;

Counting the total number of records in the Customers table, emphasizing the strategic use of COUNT.

Counting the COUNTs: Strategies Unveiled​

COUNT(*) vs COUNT(1):​

-- Decoding the Mystery
SELECT COUNT(*) AS 'Total Records', COUNT(1) AS 'Another Count'
FROM Orders;

Comparing COUNT(*) and COUNT(1) to decode the mystery of counting rows effectively.

COUNT with DISTINCT:​

-- Navigating Unique Territories
SELECT COUNT(DISTINCT Department) AS 'Distinct Departments'
FROM Employees;

Counting the distinct departments within the Employees table, illustrating the usage of COUNT with DISTINCT.

Ceiling & Floor: Elevating and Lowering Numbers​

CEILING & FLOOR:​

-- Rounding Up and Down