SQL Handbook
Uploaded by
Ashish Kumar SinghSQL Handbook
Uploaded by
Ashish Kumar SinghSQL Handbook
1. Creating Tables
Definition: Creating tables is the process of defining the structure of the database to store
data.
Syntax:
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);
Example:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(255),
LastName VARCHAR(255),
EnrollmentDate DATE,
Major VARCHAR(255),
Email VARCHAR(255)
);
Key Points:
Define primary keys (PRIMARY KEY) to uniquely identify each row.
Use appropriate data types (INT, VARCHAR, DATE, etc.).
Constraints like NOT NULL can enforce rules on the data.
2. Inserting Data
Definition: Inserting data adds new rows to a table.
Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
Example:
INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate,
Major, Email) VALUES
(1, 'Alice', 'Smith', '2023-09-01', 'Computer Science',
'[email protected]');
Key Points:
Match the order of columns and values.
You can insert multiple rows with a single INSERT statement by separating value sets
with commas.
3. Deleting Data
Definition: Deleting data removes rows from a table based on specified conditions.
Syntax:
DELETE FROM table_name
WHERE condition;
Example:
DELETE FROM Students
WHERE StudentID = 1;
Key Points:
Use WHERE clause to specify which rows to delete.
Omitting the WHERE clause will delete all rows in the table.
4. Dropping Tables
Definition: Dropping tables removes the table and all its data from the database.
Syntax:
DROP TABLE table_name;
Example:
DROP TABLE Students;
Key Points:
Dropping a table is irreversible and will delete all data in the table.
Ensure to backup data if needed before dropping a table.
5. Retrieving Data (SELECT) with WHERE
Definition: Retrieving data extracts data from tables based on specified conditions.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
SELECT * FROM Students
WHERE Major = 'Computer Science';
Key Points:
SELECT * retrieves all columns; specifying columns retrieves only those columns.
The WHERE clause filters rows based on conditions.
6. Aggregate Functions
Definition: Aggregate functions perform calculations on a set of values and return a single
value.
Functions and Syntax:
COUNT(column): Counts the number of rows.
AVG(column): Calculates the average value.
SUM(column): Calculates the sum of values.
MAX(column): Finds the maximum value.
MIN(column): Finds the minimum value.
Example:
sql
Copy code
SELECT COUNT(*) as TotalStudents FROM Students;
Key Points:
Often used with GROUP BY to group rows that share a property.
7. Grouping (GROUP BY)
Definition: Grouping combines rows that have the same values in specified columns into
aggregate data.
Syntax:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;
Example:
SELECT Major, COUNT(*) as NumberOfStudents FROM Students
GROUP BY Major;
Key Points:
Each group can be aggregated with functions like SUM, AVG, COUNT, etc.
Columns in the SELECT clause must be either in the GROUP BY clause or used with
aggregate functions.
8. Joins
Definition: Joins combine rows from two or more tables based on a related column.
Types and Syntax:
INNER JOIN: Selects records that have matching values in both tables.
SELECT columns FROM table1
INNER JOIN table2 ON table1.column = table2.column;
LEFT JOIN: Selects all records from the left table and matched records from the right
table.
SELECT columns FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
RIGHT JOIN: Selects all records from the right table and matched records from the left
table.
SELECT columns FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
FULL JOIN: Selects all records when there is a match in either left or right table.
SELECT columns FROM table1
FULL JOIN table2 ON table1.column = table2.column;
Example:
SELECT Students.FirstName, Courses.CourseName FROM Students
JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
JOIN Courses ON Enrollments.CourseID = Courses.CourseID;
Key Points:
Joins are crucial for relational database operations to combine data from different
tables.
Use ON to specify the condition for joining tables.
9. Having Clause
Definition: The HAVING clause filters records that work on aggregated data.
Syntax:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;
Example:
SELECT Major, COUNT(*) as NumberOfStudents FROM Students
GROUP BY Major
HAVING COUNT(*) > 1;
Key Points:
Used after GROUP BY to filter groups based on aggregate values.
Similar to WHERE, but for grouped data.
10. Key Points to Remember
Primary Key: Uniquely identifies each record in a table.
Foreign Key: Links two tables together.
Constraints: Rules enforced on data columns (NOT NULL, UNIQUE, CHECK).
Data Types: Choose appropriate data types for each column.
Backup: Always backup data before performing operations that modify or delete
data.
Prepared By:- Ashish Kumar Singh
Prepared With ❤️
