gaurav
My SQL Notes

My SQL Notes

Gaurav Vala

Gaurav Vala

Monday, Mar 30, 2026

These are my notes from the SQL Module of Udemy course of Hitesh Choudhary. The Notes are very RAW and they are just there for the syntax reference.

CREATE TABLE chai_store(
id 			SERIAL PRIMARY KEY,
chai_name 	VARCHAR(50),
price 		DECIMAL(5,2),
chai_type 	VARCHAR(100),
available 	BOOLEAN
);

-- INSERT RECORDS IN THE chai_store TABLE
INSERT INTO chai_store(
	chai_name, price, chai_type, available
) VALUES
('Masala Chai', 30.00, 'Spiced', 	 TRUE),
('Green Chai', 	25.00, 'Herbal', 	 TRUE),
('Black Chai',  20.00, 'Classic',	 TRUE),
('Iced Chai',  	35.00, 'Cold', 		 FALSE),
('Oolong Chai', 40.00, 'Speciality', TRUE);

-- SELECT chai_name and price FROM THE TABLE AND THOSE COLUMNS WILL HAVE CUSTOM NAME
SELECT chai_name AS "CHAI NAME", price as "COST IN INR" FROM chai_store;

-- SELECT ALL RECORDS WHERE chai_name INCLUDES Iced WORD
SELECT * FROM chai_store WHERE chai_name LIKE '%Iced%';

-- SELECT ALL THE RECORDS FOR PRICE LESS THAN 30
SELECT * FROM chai_store WHERE price < 30;

-- SELECT ALL THE RECORDS BUT SHOW THEM IN PRICE DESCENDING MANNER
SELECT * FROM chai_store ORDER BY price DESC;

-- UPDATE THE price AND available VALUE IN DB FOR Iced Chai
UPDATE chai_store SET price = 38.00, available = TRUE WHERE chai_name = 'Iced Chai';

-- SELECT ALL RECORDS
SELECT * FROM chai_store;

-- DELETE RECORD WITH CONDITION
DELETE FROM chai_store WHERE chai_name = 'Black Chai';

-- CHANGE THE SCHEMA OF THE TABLE, LIKE ADDING A NEW COLUMN IN THE TABLE
ALTER TABLE chai_store ADD stock INT DEFAULT 0;

-- CHANGE THE EXISTING COLUMN IN THE EXISTING TABLE
ALTER TABLE chai_store ALTER COLUMN price TYPE DECIMAL(12, 2);

-- DELETES WHOLE COLUMN NAMED category
ALTER TABLE chai_store DROP category

-- THIS KEEPS THE STRUCTURE OF THE TABLE BUT DELETES ALL THE RECORDS(ROWS)
TRUNCATE TABLE chai_table;

-- THIS WILL RENAME THE TABLE NAME
ALTER TABLE chai_store RENAME TO bakery_story;

-- THIS WILL CHANGE THE COLUMN NAME 
ALTER TABLE chai_store RENAME COLUMN chai_type TO chai_varients;

DDL - Data Definition Language

  • Used to define and manage database objects like tables, indexes, views, etc..

  • DDL Commands deal with structure and schema of the database

  • CREATE, ALTER , DROP, TRUNCATE, RENAME

DML - Data Manipulation Language

  • Deals with manipulation of the data inside the table

  • compared to the DDL, these commands only work with data not structure

  • INSERT, UPDATE, DELETE, SELECT

DCL - Data Control Language

  • Deals with granting and revoking privileges on the database

  • GRANT, REVOKE

-- THIS WILL GIVE SELECT PERMISSION TO USER NAMED gaurav
GRANT SELECT ON chai_store TO gaurav;

GRANT INSERT, DELETE ON chai_store TO sales_team;

-- THIS WILL REMOVE SELECT PERMISSION FROM USER NAMED gaurav
REVOKE SELECT ON chai_store FROM gaurav;

REVOKE INSERT, DELETE ON chai_store FROM sales_team;

TCL - Transaction Control Language

  • Deals with transaction management in the database.

  • Transaction make sure series of DML commands are executed successfully or not at all.

  • BEGINE, COMMIT, ROLLBACK, SAVEPOINT

BEGIN;
INSERT INTO chai_store (chai_name, chai_type, price, available) VALUES ('Masala Chai', 'Spiced', 30.00, TRUE);
UPDATE inventory SET stock = stock - 5 WHERE name = 'Jacket';
COMMIT;

  • In the above example, both queries will need to be executed or not executed at all, they will not be stopped in the middle, where the insert query will be executed and update will not, that will not happen since we are using the BEGIN command.

  • And with COMMIT command we finalize the whole transaction.

  • When using the ORMs, this part will be handled by the ORM by default so we dont have to deal with whole ATOMICITY part manually and we can focus on the actual query itself

BEGIN;
-- CREATE A SAVEPOINT
SAVEPOINT savepoint1;
-- PERFORM AN OPERATION
INSER INTO products (name, price, stock) VALUES ("Jeans", 3000.00, 6);
-- ROLLBACK TO SAVEPOINT IF NEEDED
ROLLBACK TO savepoint1;
  • This is the example for the SAVEPOINT and ROLLBACK commands, it will create a point in the query to comeback to and then we can decide when to comeback to that checkpoint using the ROLLBACK TO command.

Foreign Key

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    OrderNumber int NOT NULL,
    CustomerID int,
    -- Establishing the link to the Customers table
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- ADDING THE FOREIGN KEY TO THE EXISTING TABLE
ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerOrder
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

  • Foreign key is defined while creating a new table and we can also add it using ALTER TABLE method if the foreign key does not exist and we need to add it

  • in this we use the FOREIGN KEY command, define the column name that is the primary key of another table and then add REFERENCES keyword to give the reference to the table name and the column name.

JOINS

  • Inner join will only give data that has reference in both the table

  • LEFT JOIN will perform an INNER Join first and then return the rest of the data from LEFT JOIN

  • Same with the RIGHT JOIN but with this we will get the data from Right Table

  • OUTER JOIN combines both table and gives us combined data

  • In any query, the first table will be considered as the left table

  • We just define the type of the join and then the table name

SELECT customer_name, product_name, quantity FROM customers INNER JOIN orders;

  • When dealing with the joins we have to define which value will be coming from which table and we can define acronym for both of the table and then use those acronym on the select values

  • at the end we also define the joining condition for both table with INNER JOIN orders o ON c.customer_id = o.customer_id; (“Match rows where the customer_id is the same in both tables”)

  • here we are defining which is the left table

SELECT c.customer_name, o.product_name, o.quantity 
FROM customers c 
INNER JOIN orders o ON c.customer_id = o.customer_id;
  • now from this, if you want to change this into a LEFT JOIN or RIGHT JOIN, you can just change the keyword from INNER JOIN to LEFT JOIN
SELECT c.customer_name, o.product_name, o.quantity 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id;

  • In the above example, first we will get the intersection result of the Join and then all other record of the LEFT table (since this is the LEFT JOIN, if it would be RIGHT JOIN then we get the intersection and then all the data from right table)

  • for the outer join we just use the FULL OUTER JOIN keyword

SELECT c.customer_name, o.product_name, o.quantity 
FROM customers c 
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
  • We can also do multiple join, we can add them at the end of the query
SELECT customer_name, product_name, quantity 
FROM customers 
INNER JOIN orders LEFT JOIN products RIGHT JOIN tracking_table;

Inner Query

  • We define the inner query or sub query with the IN keyword

  • this inner query will run first then the outer query or the main query will run

Mental model

  • First, make a list: “Who bought T-Shirts?”

  • Then, go to customers table and say: “Give me their names.”

SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE product_name = 'T-Shirt'
)
  • Aggregator (or aggregate) functions in SQL are functions that take multiple rows of data and return a single value.
SELECT student.student_name,COUNT(*)
       FROM student,course
       WHERE student.student_id=course.student_id
       GROUP BY student_name;

  • here the COUNT is the aggregate function, that gives us the row count of the query

  • The aggregate function COUNT(*) is counting how many times each student appears after the join — which equals how many courses they have.