SQL INTRO
SQL (Structured Query Language) is used to store, retrieve, and manipulate data in relational
databases.
It works with tables, rows, and columns to perform database operations.
SELECT * FROM students;
Sample Table Used for Examples
CREATE TABLE students (id INT, name VARCHAR(20), age INT, city
VARCHAR(20), marks INT);
id name age city marks
1 Anu 20 Delhi 85
2 Ravi 22 Mumbai 78
3 Asha 21 Delhi 90
SQL SELECT
SELECT is used to retrieve data from a table.
You can select all columns or specific columns.
SELECT name, city FROM students;
name city
Anu Delhi
Ravi Mumbai
Asha Delhi
SQL WHERE
WHERE clause filters records based on a condition.
It reduces the number of rows returned.
SELECT * FROM students WHERE city='Delhi';
id name age city marks
1 Anu 20 Delhi 85
3 Asha 21 Delhi 90
SQL ORDER BY
ORDER BY sorts the result set.
Sorting can be ascending or descending.
SELECT * FROM students ORDER BY marks DESC;
name marks
Asha 90
Anu 85
Ravi 78
SQL INSERT INTO
INSERT INTO adds new records to a table.
Values must match column order.
INSERT INTO students VALUES (4,'Kiran',23,'Bangalore',88);
SQL UPDATE
UPDATE modifies existing records.
Always use WHERE to avoid updating all rows.
UPDATE students SET marks=95 WHERE id=3;
SQL DELETE
DELETE removes records from a table.
Without WHERE, all records will be deleted.
DELETE FROM students WHERE id=2;
SQL AGGREGATE FUNCTIONS
Aggregate functions perform calculations on multiple rows.
They return a single value.
SELECT COUNT(*) FROM students;
Result: 3
SQL MIN & MAX
MIN finds smallest value.
MAX finds largest value.
SELECT MAX(marks) FROM students;
Result: 95
SQL SUM & AVG
SUM calculates total.
AVG calculates average.
SELECT AVG(marks) FROM students;
Result: 89.33
SQL LIKE
LIKE is used for pattern matching.
% represents zero or more characters.
SELECT * FROM students WHERE name LIKE 'A%';
SQL IN
IN matches multiple values.
It reduces multiple OR conditions.
SELECT * FROM students WHERE city IN ('Delhi','Mumbai');
SQL BETWEEN
BETWEEN selects values in a range.
Range is inclusive.
SELECT * FROM students WHERE age BETWEEN 20 AND 22;
SQL GROUP BY
GROUP BY groups rows with same values.
Used with aggregate functions.
SELECT city, COUNT(*) FROM students GROUP BY city;
SQL HAVING
HAVING filters grouped data.
Used with GROUP BY.
SELECT city, COUNT(*) FROM students GROUP BY city HAVING COUNT(*)>1;
SQL INNER JOIN
JOIN combines data from multiple tables.
INNER JOIN returns matching records.
SELECT [Link], [Link] FROM students s INNER JOIN dept d ON [Link]=[Link];
SQL PRIMARY KEY
Primary key uniquely identifies a record.
It cannot contain NULL values.
id INT PRIMARY KEY
SQL FOREIGN KEY
Foreign key links two tables.
It maintains referential integrity.
FOREIGN KEY (sid) REFERENCES students(id)
SQL VIEW
View is a virtual table.
It does not store data physically.
CREATE VIEW student_view AS SELECT name, city FROM students;
SQL INDEX
Index improves query performance.
It speeds up searching.
CREATE INDEX idx_city ON students(city);
SQL AUTO INCREMENT
Auto increment automatically increases numeric values.
Used for primary keys.
id INT AUTO_INCREMENT