🍕SQL

Basics

Keep in mind that the order of execution follows FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

-- Selecting columns to display in the result
SELECT Product, SUM(Amount) AS TotalSales

-- Specifying the table to retrieve data from
FROM Sales

-- Filtering data based on conditions
WHERE Region = 'East' AND YEAR(Date) = 2023

-- Grouping the result by the 'Product' column
GROUP BY Product

-- Filtering the grouped results based on aggregate conditions
HAVING SUM(Amount) > 10000

-- Ordering the final result by total sales in descending order
ORDER BY TotalSales DESC;

SQL Case

SELECT
    Amount,
    CASE
        WHEN Amount < 1000 THEN 'Low'
        WHEN Amount >= 1000 AND Amount < 5000 THEN 'Medium'
        ELSE 'High'
    END AS SalesCategory
FROM
    Sales;

Create table

CREATE TABLE SalesData (
    SaleID INT PRIMARY KEY,
    Product VARCHAR(50),
    Amount DECIMAL(10, 2),
    SaleDate DATE
);

Insert data into a table

INSERT INTO SalesData (SaleID, Product, Amount, SaleDate)
VALUES
    (1, 'ProductA', 1200.50, '2023-01-15'),
    (2, 'ProductB', 5500.75, '2023-02-20'),
    (3, 'ProductC', 800.00, '2023-03-10');

Delete table

DROP TABLE SalesData;

Alter table

• Add a new column

-- Adding a new column 'ProductName' to the 'Product' table
ALTER TABLE Product
ADD COLUMN ProductName VARCHAR(255);

• Rename a column

-- Renaming the 'ProductName' column to 'Name' in the 'Product' table
ALTER TABLE Product
RENAME COLUMN ProductName TO Name;

• Change the data type of a column

-- Changing the data type of 'Price' to a new data type (e.g., DECIMAL(10,2))
ALTER TABLE Product
ALTER COLUMN Price SET DATA TYPE DECIMAL(10, 2);

• Delete a column

-- Deleting the 'ProductID' column from the 'Product' table
ALTER TABLE Product
DROP COLUMN ProductID;