SQLRevision Notes
Uploaded by
saistakhatun776SQLRevision Notes
Uploaded by
saistakhatun776SQL Detailed Revision Notes
SQL Detailed Revision Notes
Database: Collection of data stored in an organized form.
DBMS: Software used to manage databases.
SQL: Structured Query Language used with relational databases.
CRUD: Create, Read, Update, Delete.
Database Commands
CREATE DATABASE db_name;
CREATE DATABASE IF NOT EXISTS db_name;
DROP DATABASE db_name;
DROP DATABASE IF EXISTS db_name;
SHOW DATABASES;
USE db_name;
Purpose: Create, delete, view and select databases.
Create Table
CREATE TABLE table_name(
col1 datatype constraint,
col2 datatype constraint
);
Purpose: Create a new table.
Datatypes
CHAR, VARCHAR, INT, TINYINT, BIGINT, FLOAT, DOUBLE,
BOOLEAN, DATE, YEAR, BLOB.
Purpose: Define what type of data a column can store.
Keys
Primary Key:
id INT PRIMARY KEY
Foreign Key:
FOREIGN KEY(col_name)
REFERENCES parent_table(id)
PK = Unique + Not Null.
FK = Creates relationship between tables.
Constraints
NOT NULL:
name VARCHAR(50) NOT NULL
UNIQUE:
email VARCHAR(100) UNIQUE
DEFAULT:
salary INT DEFAULT 25000
CHECK:
age INT CHECK(age >= 18)
Purpose: Apply rules on data.
SELECT and WHERE
SELECT * FROM table_name;
SELECT col1,col2
FROM table_name
WHERE condition;
Purpose: Retrieve and filter data.
Operators
Comparison: =, !=, >, <, >=, <=
Logical: AND, OR, NOT, IN, BETWEEN
Arithmetic: +, -, *, /, %
Examples:
WHERE marks > 80
WHERE city IN ('Delhi','Mumbai')
WHERE marks BETWEEN 80 AND 90
LIMIT and ORDER BY
SELECT * FROM student LIMIT 5;
SELECT * FROM student
ORDER BY marks ASC;
SELECT * FROM student
ORDER BY marks DESC;
Aggregate Functions
COUNT():
SELECT COUNT(*) FROM student;
SUM():
SELECT SUM(marks) FROM student;
AVG():
SELECT AVG(marks) FROM student;
MAX():
SELECT MAX(marks) FROM student;
MIN():
SELECT MIN(marks) FROM student;
GROUP BY and HAVING
SELECT city,COUNT(*)
FROM student
GROUP BY city;
SELECT city,COUNT(*)
FROM student
GROUP BY city
HAVING MAX(marks) > 90;
GROUP BY = grouping
HAVING = condition after grouping
INSERT UPDATE DELETE
INSERT:
INSERT INTO table_name(col1,col2)
VALUES(val1,val2);
UPDATE:
UPDATE table_name
SET col1=value
WHERE condition;
DELETE:
DELETE FROM table_name
WHERE condition;
Cascade
ON DELETE CASCADE
ON UPDATE CASCADE
Used with Foreign Keys to automatically delete/update child records.
ALTER TABLE
ADD COLUMN:
ALTER TABLE table_name
ADD COLUMN col_name datatype;
DROP COLUMN:
ALTER TABLE table_name
DROP COLUMN col_name;
MODIFY COLUMN:
ALTER TABLE table_name
MODIFY col_name datatype;
CHANGE COLUMN:
ALTER TABLE table_name
CHANGE COLUMN old_name new_name datatype;
RENAME TABLE:
ALTER TABLE table_name
RENAME TO new_table_name;
TRUNCATE vs DELETE vs DROP
DELETE = Remove selected rows.
TRUNCATE = Remove all rows, keep structure.
DROP = Remove entire table and structure.
JOINS
INNER JOIN:
SELECT * FROM A
INNER JOIN B ON [Link]=[Link];
LEFT JOIN:
SELECT * FROM A
LEFT JOIN B ON [Link]=[Link];
RIGHT JOIN:
SELECT * FROM A
RIGHT JOIN B ON [Link]=[Link];
FULL JOIN (MySQL):
LEFT JOIN + UNION + RIGHT JOIN
SELF JOIN:
SELECT *
FROM employee a
JOIN employee b
ON [Link]=b.manager_id;
UNION
SELECT col FROM tableA
UNION
SELECT col FROM tableB;
Rules:
Same number of columns
Similar datatypes
Same order
Subqueries and Views
Subquery:
SELECT *
FROM student
WHERE marks >
(SELECT AVG(marks) FROM student);
View:
A virtual table based on an SQL query.
Quick Interview Revision
Execution Order:
SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY
Most Important:
PK, FK, Constraints, WHERE, GROUP BY, HAVING,
Aggregate Functions, ALTER TABLE, JOINS,
UNION, Subqueries.