Easy SQL Tutorial

1. What is SQL?

SQL stands for Structured Query Language. It is used to work with databases.

With SQL we can:

• Create databases
• Create tables
• Add data
• Read data
• Update data
• Delete data

2. What is a Database?

A database is a place where we store information.

For example, a school database can contain:

3. What is a Table?

A table stores data in rows and columns.

Example:

Here id, name and age are columns. Each student record is a row.

4. CREATE DATABASE

We can create a database using the CREATE DATABASE command.

CREATE DATABASE school;

This creates a database named school.

5. CREATE TABLE

After creating a database, we can create a table.

CREATE TABLE students (
    id INT,
    name VARCHAR(50),
    age INT
);

Simple Explanation

id stores numbers.
name stores text.
age stores numbers.

6. INSERT Data

The INSERT command adds data to a table.

INSERT INTO students
(id, name, age)
VALUES
(1, 'Ali', 20);

We can add another student:

INSERT INTO students
(id, name, age)
VALUES
(2, 'Ahmed', 21);

7. SELECT Data

SELECT is used to display data.

SELECT * FROM students;

The * means all columns.

8. SELECT Specific Columns

We don't always need every column.

SELECT name, age
FROM students;

This displays only the name and age.

9. WHERE Condition

WHERE is used to find specific records.

SELECT * FROM students
WHERE age = 20;

This finds students whose age is 20.

10. AND / OR

AND

AND means both conditions must be true.

SELECT * FROM students
WHERE age = 20 AND name = 'Ali';

OR

OR means one of the conditions can be true.

SELECT * FROM students
WHERE age = 20 OR age = 21;

11. ORDER BY

ORDER BY is used to sort data.

Small to Large

SELECT * FROM students
ORDER BY age ASC;

Large to Small

SELECT * FROM students
ORDER BY age DESC;

12. LIMIT

LIMIT controls how many records we want to display.

SELECT * FROM students
LIMIT 2;

This displays only two records.

13. UPDATE Data

UPDATE changes existing data.

UPDATE students
SET age = 22
WHERE id = 2;

This changes the age of student number 2 to 22.

14. DELETE Data

DELETE removes data from a table.

DELETE FROM students
WHERE id = 2;

This deletes student number 2.

15. LIKE

LIKE is used when we want to search for matching text.

SELECT * FROM students
WHERE name LIKE 'A%';

A% means the name starts with A.

For example:

16. COUNT()

COUNT tells us how many records exist.

SELECT COUNT(*) FROM students;

This means there are 2 students.

17. SUM() and AVG()

SUM()

SUM adds numbers.

SELECT SUM(age)
FROM students;

AVG()

AVG calculates the average.

SELECT AVG(age)
FROM students;

18. Primary Key

A primary key uniquely identifies each record.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

Here id is the primary key.

19. Foreign Key

A foreign key connects two tables.

Example:

CREATE TABLE courses (
    id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

Another table can use the course ID to connect with this table.

20. JOIN

JOIN is used to get data from more than one table.

Suppose we have:

id | name | course_id

id | course_name

We can join them:

SELECT students.name, courses.course_name
FROM students
JOIN courses
ON students.course_id = courses.id;

JOIN is useful when information is stored in separate tables.

21. CRUD

CRUD is a very important concept for web developers.

Almost every database-based website uses CRUD.

22. SQL Practice Project

Now let's create a simple student table.

Step 1: Create Database

CREATE DATABASE school;

Step 2: Select Database

USE school;

Step 3: Create Table

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

Step 4: Add Students

INSERT INTO students
(id, name, age, city)
VALUES
(1, 'Ali', 20, 'Bahawalpur');

INSERT INTO students
(id, name, age, city)
VALUES
(2, 'Ahmed', 21, 'Lahore');

INSERT INTO students
(id, name, age, city)
VALUES
(3, 'Usman', 19, 'Multan');

Step 5: Display Students

SELECT * FROM students;

Step 6: Find One Student

SELECT * FROM students
WHERE id = 1;

Step 7: Update Student

UPDATE students
SET city = 'Islamabad'
WHERE id = 1;

Step 8: Delete Student

DELETE FROM students
WHERE id = 3;

SQL Quick Revision