It is a type of database that stores data in tables — just like an Excel sheet. Each table has rows (data records) and columns (data fields).
- Example: A table called Students might have columns like ID, Name, and Age, and rows with actual student details.
Relation: Different tables can be linked using a key (like a student’s ID can link to their marks in another table).
A DBMS is a software that helps us store, manage, and use data easily. It allows users to add, update, delete, and fetch data from databases.
Examples:
- MySQL
- PostgreSQL
- SQL Server
These tools help us write and run SQL queries.
SQL (Structured Query Language) is used to talk to the database. We use it to:
Get data: SELECT * FROM students;
Add data: INSERT INTO students (name, age) VALUES ('Arjun', 22);
Update data: UPDATE students SET age = 23 WHERE name = 'Arjun';
Delete data: DELETE FROM students WHERE name = 'Arjun';
DDL commands help manage the structure of the database (not the data itself). Main commands: CREATE, ALTER, DROP, and TRUNCATE.
Used to create new database objects such as tables or indexes.
- PRIMARY KEY → unique identifier for each row.
- FOREIGN KEY → link between two tables.
- CHECK → ensure data follows certain rules.
Used to change the structure of an existing table.
Used to delete database objects permanently.
Used to delete all rows from a table, but keep the table structure.
DML commands are used to work with the data stored in tables — inserting, updating, deleting, or reading it.
Used to add new records to a table.
Used to change existing records.
Used to remove specific rows from a table.
Used to fetch data from tables.
Used to control access or permissions to the data in a database. It helps in granting and revoking privileges to users on database objects (like tables, views, etc.).
Used to give permissions to a user.
( SYNTAX : GRANT privilege_name ON object_name TO user_name; )
Example for student database
- (GRANT SELECT, INSERT ON student TO teacher1;)
- This allows teacher1 to view (SELECT) and add (INSERT) records in the student table.
Used to take back permissions that were previously granted.
( Syntax: REVOKE privilege_name ON object_name FROM user_name;)
Example for student Database
- (REVOKE INSERT ON student FROM teacher1;)
- This removes teacher1’s permission to insert data into the student table.
Used to fetch or retrieve data from the database.
Used to group rows that have the same value in one or more columns.
Usually used with aggregate functions like COUNT, SUM, AVG, etc.
is like WHERE, but it is used after GROUP BY to filter groups.
used to sort the result — either ascending (ASC) or descending (DESC) order
TCL commands are used to manage transactions in a database. A transaction is a group of SQL operations that are treated as a single unit of work — either all succeed or all fail.
- COMMIT-- permanently saves changes,
- ROLLBACK -- undoes uncommitted changes,
- SAVEPOINT -- lets you set a point to roll back to within a transaction.\
- rules you set on your table to keep your data correct and safe.
- They make sure no wrong or duplicate data goes inside your table.
- Uniquely identifies each record in a table.
- It cannot be NULL and must be unique. Example: studentID INT PRIMARY KEY
- Creates a link between two tables.
- It ensures the value in one table matches a value in another table. Example: FOREIGN KEY (deptID) REFERENCES department(deptID)
Makes sure all values in a column are different (no duplicates). Example: email VARCHAR(30) UNIQUE
Ensures that the values in a column meet a specific condition. Example: CHECK (age >= 18)
Ensures a column cannot have a NULL (empty) value. Example: name VARCHAR(20) NOT NULL
- INNER JOIN: rows that match in both tables.
- LEFT JOIN: all rows from the left table, plus matching rows from the right (or NULL when no match).
- RIGHT JOIN: all rows from the right table, plus matching rows from the left (or NULL when no match).
- FULL JOIN: all rows from both tables; where no match exists, columns show NULL.
- CROSS JOIN: every row from left paired with every row from right (Cartesian product).
- COUNT(*) — count rows
- SUM(column) — add values in a column
- AVG(column) — average value
- MIN(column) and MAX(column) — smallest / largest
-
SELECT — choose columns / expressions
-
FROM — table(s)
-
WHERE — filter rows before grouping
- GROUP BY — form groups of rows
- HAVING — filter groups after grouping
- ORDER BY — sort the final result
Procedure in SQL is like a saved program or a set of SQL commands that you can reuse anytime.
A Subquery is a query inside another query. It helps to get data from one query and use it inside another.
Find the oldest student
Find youngest student name
CTE means Common Table Expression. It is like a temporary table that you create inside a query — only for that query.
A view is a virtual table. It’s used to see specific data easily without writing long queries again and again. We use CREATE VIEW to make it and DROP VIEW to remove it.”
