What is SQL?

SQL (Structured Query Language) is the standard language for interacting with relational databases. It allows users and applications to create database structures, insert and update data, delete data, and retrieve data using queries. SQL is a declarative language — you specify WHAT data you want, not HOW to get it.

SELECT — Retrieving Data

The SELECT statement retrieves data from one or more tables.

SELECT column1, column2
FROM tableName
WHERE condition
ORDER BY column1 ASC;

• SELECT * — retrieve all columns
• FROM — specifies the table
• WHERE — filters rows that meet a condition
• ORDER BY — sorts results (ASC = ascending, DESC = descending)

Example — retrieve all students with grade A:

SELECT StudentName, Grade
FROM Student
WHERE Grade = 'A'
ORDER BY StudentName ASC;

Comparison Operators in WHERE

JOIN — Querying Multiple Tables

A JOIN combines rows from two or more tables based on a related column (usually a primary key / foreign key pair).

SELECT Student.StudentName, Course.CourseName
FROM Student
INNER JOIN Enrolment ON Student.StudentID = Enrolment.StudentID
INNER JOIN Course ON Enrolment.CourseCode = Course.CourseCode
WHERE Course.CourseName = 'Computer Science';

• INNER JOIN — returns only rows where there is a match in BOTH tables (most commonly used).
• LEFT JOIN — returns ALL rows from the left table, plus matching rows from the right (unmatched right rows show NULL).
• RIGHT JOIN — returns ALL rows from the right table, plus matching rows from the left.

INSERT — Adding Data

INSERT INTO Student (StudentID, StudentName, DOB, Grade)
VALUES ('S003', 'Fatima Ali', '2006-05-10', 'B');

Always list the column names in the same order as the values.

UPDATE — Modifying Data

UPDATE Student
SET Grade = 'A'
WHERE StudentID = 'S003';

Always include a WHERE clause with UPDATE — without it, every row in the table is updated!

DELETE — Removing Data

DELETE FROM Student
WHERE StudentID = 'S003';

Again, always include WHERE — without it, all rows are deleted. Referential integrity may prevent deleting a row that is referenced by a foreign key in another table.

CREATE TABLE — Defining Structure

CREATE TABLE Student (
  StudentID VARCHAR(5) PRIMARY KEY,
  StudentName VARCHAR(50) NOT NULL,
  DOB DATE,
  Grade CHAR(1)
);

Common data types:

Aggregate Functions

SQL includes built-in functions to perform calculations on groups of rows:

SELECT COUNT(*) AS TotalStudents, AVG(Score) AS AverageScore
FROM Student
WHERE Grade = 'A';

Use GROUP BY to group results and HAVING to filter groups (like WHERE but for groups):

SELECT Grade, COUNT(*) AS NumberOfStudents
FROM Student
GROUP BY Grade
HAVING COUNT(*) > 5;