SQL Fundamentals (Detailed Explanation with Examples)

We'll use the following tables throughout the examples.

Student Table

StudentID Name Age DepartmentID Marks

101 Alice 20 1 85

102 Bob 21 2 75

103 Charlie 19 1 90

104 David 22 NULL 65

105 Emma 20 3 95

Department Table

DepartmentID DepartmentName

1 Computer Science

2 Electronics

3 Mechanical

4 Civil

1. Primary Key

Definition

A Primary Key is a column (or combination of columns) that uniquely identifies each
row in a table.

Properties

 Unique values

 Cannot contain NULL

 Only one Primary Key per table

 Automatically creates a unique index

Example
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);

Here,

StudentID

is the Primary Key.

Why?

Suppose we have:

StudentID Name

101 Alice

101 Bob

This creates confusion because two students have the same ID.

Primary Key prevents this.

Real-Life Example

 Aadhaar Number

 Passport Number

 Employee ID

 Roll Number

2. Foreign Key

Definition

A Foreign Key is a column that refers to the Primary Key of another table.

It creates a relationship between tables.

Example

CREATE TABLE Department(

DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50)
);

CREATE TABLE Student(

StudentID INT PRIMARY KEY,
Name VARCHAR(50),
DepartmentID INT,
FOREIGN KEY (DepartmentID)
REFERENCES Department(DepartmentID)
);

Relationship

Department Table

DepartmentID
1
2
3

Student Table

DepartmentID
1
2
1
3

Students belong to departments.

Why Foreign Keys?

Without Foreign Key:

DepartmentID = 100

may exist although no department 100 exists.

Foreign Key prevents invalid references.

3. Candidate Key

Definition
A Candidate Key is any column (or set of columns) that can uniquely identify each
row.

One Candidate Key becomes the Primary Key.

Example

StudentID Email Phone

101 alice@[Link] 9876543210

102 bob@[Link] 9876543211

Unique columns:

 StudentID

 Email

 Phone

All three are Candidate Keys.

Choose StudentID as Primary Key.

Comparison

Primary Key Candidate Key

Chosen unique key Possible unique key

Only one Many possible

Cannot be NULL Cannot be NULL

4. SELECT

Used to retrieve data.

Syntax

SELECT column_name
FROM table_name;

Example
SELECT Name
FROM Student;

Output

Name

Alice

Bob

Charlie

David

Emma

Retrieve all columns

SELECT *
FROM Student;

Multiple columns

SELECT Name, Marks

FROM Student;

5. ORDER BY

Used to sort results.

Default:

Ascending.

Ascending

SELECT *
FROM Student
ORDER BY Marks;

Output

65

75
85

90

95

Descending

SELECT *
FROM Student
ORDER BY Marks DESC;

Output

95

90

85

75

65

Sort by multiple columns

SELECT *
FROM Student
ORDER BY DepartmentID, Marks DESC;

6. Aliases (AS)

Aliases give temporary names to columns or tables.

Column Alias

SELECT Name AS Student_Name,

Marks AS Score
FROM Student;

Output
Student_Name Score

Alice 85

Table Alias

SELECT [Link]
FROM Student s;

Instead of writing

[Link]

write

[Link]

Useful in joins.

7. UPDATE

Used to modify existing data.

Syntax

UPDATE table_name
SET column=value
WHERE condition;

Example

UPDATE Student
SET Marks=88
WHERE StudentID=101;

Before

StudentID Marks

101 85

After

StudentID Marks

101 88
Update multiple columns

UPDATE Student
SET Marks=90,
Age=21
WHERE StudentID=102;

⚠ Without WHERE

UPDATE Student
SET Marks=100;

Every student's marks become 100.

8. NULL

NULL means unknown or missing value.

NULL is NOT:

 0

 Empty string

 False

Example

StudentID DepartmentID

104 NULL

Meaning:

Department not assigned.

Finding NULL values

SELECT *
FROM Student
WHERE DepartmentID IS NULL;

Finding NOT NULL

SELECT *
FROM Student
WHERE DepartmentID IS NOT NULL;

Why not

DepartmentID = NULL

Because NULL cannot be compared using =.

Correct

IS NULL

9. Constraints

Constraints enforce rules on table data.

PRIMARY KEY

Unique + Not NULL.

FOREIGN KEY

Maintains relationships.

UNIQUE

No duplicate values.

Email VARCHAR(50) UNIQUE

NOT NULL

Value must be provided.

Name VARCHAR(30) NOT NULL

CHECK

Restricts values.

Age INT CHECK(Age>=18)

Age below 18 is rejected.

DEFAULT

Provides default value.

Status VARCHAR(20)
DEFAULT 'Active'

If not specified,

Status becomes

Active

10. Joins

Joins combine rows from multiple tables.

Why?

Student table

StudentID DepartmentID

101 1

Department table

DepartmentID DepartmentName

1 Computer Science

Need

Alice → Computer Science

Use JOIN.

INNER JOIN

Returns matching rows.

SELECT [Link],
[Link]
FROM Student s
INNER JOIN Department d
ON [Link]=[Link];

Output
Name Department

Alice Computer Science

Bob Electronics

Charlie Computer Science

Emma Mechanical

David not shown because DepartmentID is NULL.

LEFT JOIN

Returns all rows from left table.

SELECT [Link],
[Link]
FROM Student s
LEFT JOIN Department d
ON [Link]=[Link];

Output

Name Department

Alice Computer Science

Bob Electronics

Charlie Computer Science

David NULL

Emma Mechanical

RIGHT JOIN

Returns all rows from right table.

SELECT [Link],
[Link]
FROM Student s
RIGHT JOIN Department d
ON [Link]=[Link];

Output
Department Student

Computer Science Alice

Computer Science Charlie

Electronics Bob

Mechanical Emma

Civil NULL

Civil department has no students.

FULL OUTER JOIN

Returns every row from both tables.

SELECT [Link],
[Link]
FROM Student s
FULL OUTER JOIN Department d
ON [Link]=[Link];

Includes

 David (no department)

 Civil (no students)

Join Comparison

Join Returns

INNER JOIN Only matching records

LEFT JOIN All rows from left table + matching rows from right

RIGHT JOIN All rows from right table + matching rows from left

FULL OUTER JOIN All rows from both tables

11. GROUP BY

Used to group rows having the same value.

Usually used with aggregate functions:

 COUNT()
  SUM()

 AVG()

 MAX()

 MIN()

Example

Count students in each department.

SELECT DepartmentID,
COUNT(*) AS TotalStudents
FROM Student
GROUP BY DepartmentID;

Output

DepartmentID TotalStudents

1 2

2 1

3 1

Average marks

SELECT DepartmentID,
AVG(Marks)
FROM Student
GROUP BY DepartmentID;

Output

DepartmentID Average

1 87.5

2 75

3 95

12. HAVING

HAVING filters grouped data.

WHERE filters individual rows before grouping.

HAVING filters groups after grouping.

Example

Departments having more than one student.

SELECT DepartmentID,
COUNT(*) AS Students
FROM Student
GROUP BY DepartmentID
HAVING COUNT(*) > 1;

Output

DepartmentID Students

1 2

Average marks greater than 80

SELECT DepartmentID,
AVG(Marks)
FROM Student
GROUP BY DepartmentID
HAVING AVG(Marks) > 80;

Output

DepartmentID Average

1 87.5

3 95

WHERE vs HAVING

WHERE (before grouping)

SELECT *
FROM Student
WHERE Marks > 80;

Output

Name Marks

Alice 85
Name Marks

Charlie 90

Emma 95

HAVING (after grouping)

SELECT DepartmentID,
AVG(Marks)
FROM Student
GROUP BY DepartmentID
HAVING AVG(Marks) > 80;

Output

DepartmentID Average Marks

1 87.5

3 95

WHERE vs HAVING Comparison

WHERE HAVING

Filters rows before grouping Filters groups after grouping

Cannot use aggregate functions directly Primarily used with aggregate functions

Executed before GROUP BY Executed after GROUP BY

Aggregate Functions

These functions are commonly used with GROUP BY and HAVING.

Function Description Example

COUNT() Counts rows COUNT(*)

SUM() Adds values SUM(Marks)

AVG() Calculates average AVG(Marks)

MAX() Finds maximum value MAX(Marks)

MIN() Finds minimum value MIN(Marks)

Example:

SELECT
COUNT(*) AS TotalStudents,
AVG(Marks) AS AverageMarks,
MAX(Marks) AS HighestMarks,
MIN(Marks) AS LowestMarks,
SUM(Marks) AS TotalMarks
FROM Student;