Learn SQL - Structured Query Language Tutorial

What is SQL?

SQL (pronounced "ess-cue-ell" or sometimes "sequel") stands for Structured Query Language. It is the standard language used to communicate with relational databases — databases that store data in tables with rows and columns.

With SQL, you can:

Retrieve specific data from large datasets
Insert, update, or delete data
Create and modify database structures
Perform calculations and aggregations
Create complex reports and analytics

SQL is used in systems like MySQL, PostgreSQL, Oracle, SQLite, and Microsoft SQL Server.

Basic SQL Commands

SQL consists of various commands that fall into categories:

DDL (Data Definition Language): CREATE, ALTER, DROP
DML (Data Manipulation Language): INSERT, UPDATE, DELETE
DQL (Data Query Language): SELECT
DCL (Data Control Language): GRANT, REVOKE
TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

These commands form the foundation of interacting with any relational database.

Creating Tables and Databases

To store data, you must first create a database and then tables within it.


CREATE DATABASE School;
USE School;

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    Grade CHAR(1)
);

In SQL, before storing any data, you need to create a container called a database. The statement CREATE DATABASE School; creates a new database named "School". The next command, USE School;, tells the system to start working inside that database so any further actions will apply to it.

After setting the database, we create a table named Students using the CREATE TABLE command. This table includes four columns: StudentID, which is an integer and also the primary key (meaning each value must be unique and identifies each student); Name, which stores the student's name as a text string up to 100 characters; Age, which stores the student's age as a number; and Grade, which stores a single character to represent the student's grade like 'A', 'B', or 'C'.

This structure helps organize student information clearly, ensuring each record is unique, properly typed, and easy to retrieve or manipulate later using SQL queries.

Data Types in SQL

Choosing the right data type is crucial for storage and performance.

INT - Whole numbers
VARCHAR(n) - Variable-length string (e.g., names)
CHAR(n) - Fixed-length string
FLOAT, DOUBLE - Decimal numbers
DATE, TIME, DATETIME - Date and time values
BOOLEAN - True or false (some systems use TINYINT instead)

Example:


CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(50),
    Price FLOAT,
    Available BOOLEAN
);

In SQL, selecting the appropriate data type for each column is very important because it affects how your data is stored, processed, and retrieved. Using the right data types improves performance, saves space, and ensures data accuracy.

INT is used to store whole numbers, like IDs or counts. VARCHAR(n) is used for storing variable-length text such as names or addresses, where "n" defines the maximum number of characters. CHAR(n) is for fixed-length text, which is useful when all entries in the column have the same length, like country codes.

FLOAT and DOUBLE are used to store decimal or floating-point numbers, which are important when dealing with prices, measurements, or percentages. DATE, TIME, and DATETIME are used for storing date and time information. Finally, BOOLEAN is used to store true or false values — although in some databases, it is stored as a small integer like 0 (false) or 1 (true).

In the example shown, the Products table contains a product ID as an integer, a product name as a text string of up to 50 characters, a price as a floating-point number, and a boolean field to indicate whether the product is available or not.

Retrieving Data with SELECT

The SELECT statement is the most widely used SQL command.

Select All Columns


SELECT * FROM Students;

Select Specific Columns


SELECT Name, Grade FROM Students;

Use of WHERE Clause


SELECT * FROM Students WHERE Age > 18;

The SELECT statement is one of the most important and commonly used SQL commands. It is used to retrieve data from a database and allows you to view the information stored in one or more tables.

If you want to see all the data in a table, you can use SELECT * FROM Students;. The asterisk (*) means "select all columns," so this will return every piece of data for every student in the Students table.

However, if you only want to see certain pieces of information, like names and grades, you can specify the column names directly. For example, SELECT Name, Grade FROM Students; will return just the names and grades of all students, nothing else.

You can also add conditions to your query using the WHERE clause. This helps filter results based on specific criteria. For example, SELECT * FROM Students WHERE Age > 18; will only return data for students who are older than 18.

Filtering with AND/OR


SELECT * FROM Students
WHERE Age > 18 AND Grade = 'A';

The AND and OR operators in SQL allow you to filter query results based on multiple conditions. In the example SELECT * FROM Students WHERE Age > 18 AND Grade = 'A';, the query will only return students who are both older than 18 and have a grade of 'A'. If either condition is not met, that record will not be included in the results. You can also use OR to retrieve results that meet at least one of the specified conditions.

Sorting and Limiting Data

ORDER BY


SELECT * FROM Students
ORDER BY Age DESC;

LIMIT (MySQL/PostgreSQL)


SELECT * FROM Students
LIMIT 3;

In SQL, the ORDER BY clause is used to sort the results of a query based on one or more columns. For example, ORDER BY Age DESC will sort the students in descending order of age, meaning the oldest students appear first. By default, SQL sorts in ascending order if ASC or DESC is not specified. The LIMIT clause is used in MySQL and PostgreSQL to restrict the number of rows returned by a query. For example, LIMIT 3 will return only the first three records from the result set, which is useful when you only want to preview a small portion of data.

Inserting Data

To add records:


INSERT INTO Students (StudentID, Name, Age, Grade)
VALUES (1, 'Ali', 20, 'B');

Multiple records:


INSERT INTO Students (StudentID, Name, Age, Grade)
VALUES 
(2, 'Sara', 19, 'A'),
(3, 'John', 21, 'C');

The INSERT INTO statement is used to add new records into a table. You specify the table name, followed by the column names in parentheses, and then provide the corresponding values. For example, INSERT INTO Students (StudentID, Name, Age, Grade) VALUES (1, 'Ali', 20, 'B'); adds one new student record to the Students table. To add multiple records at once, you can list several sets of values separated by commas, as shown in the second example. This method is more efficient when you need to insert many rows at the same time.

Updating Records

To change existing values:


UPDATE Students
SET Grade = 'A'
WHERE Name = 'Ali';

The UPDATE statement in SQL is used to modify existing records in a table. You use the SET clause to specify which column(s) you want to update and assign new values. In the example UPDATE Students SET Grade = 'A' WHERE Name = 'Ali';, the command changes the grade of the student named Ali to 'A'. The WHERE clause is important because it ensures that only the intended record(s) are updated — without it, all rows in the table would be changed.

Deleting Records


DELETE FROM Students
WHERE StudentID = 3;

The DELETE statement is used in SQL to remove one or more records from a table. In the example DELETE FROM Students WHERE StudentID = 3;, the command deletes the student whose ID is 3. The WHERE clause is crucial, as it specifies which record to delete. If you omit the WHERE clause, all rows in the table will be deleted, which can result in accidental data loss.

Aggregate Functions

SQL supports built-in functions for summarizing data.


SELECT COUNT(*) FROM Students;
SELECT AVG(Age) FROM Students;
SELECT MAX(Age) FROM Students;
SELECT MIN(Age) FROM Students;

You can group results too:


SELECT Grade, COUNT(*) FROM Students
GROUP BY Grade;

SQL provides built-in aggregate functions to help you summarize and analyze data. For example, COUNT(*) returns the total number of records, AVG(Age) calculates the average age, while MAX(Age) and MIN(Age) return the highest and lowest age values, respectively. You can also group data using GROUP BY to see summaries by category — such as how many students received each grade with SELECT Grade, COUNT(*) FROM Students GROUP BY Grade;. These functions are especially useful for reports and data insights.

SQL Joins (Combining Tables)

INNER JOIN


SELECT Students.Name, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.StudentID = Courses.StudentID;

LEFT JOIN


SELECT Students.Name, Courses.CourseName
FROM Students
LEFT JOIN Courses ON Students.StudentID = Courses.StudentID;

SQL joins are used to combine rows from two or more tables based on a related column. An INNER JOIN returns only the records that have matching values in both tables. For example, it will show students and their course names only if there is a match in both the Students and Courses tables. A LEFT JOIN, on the other hand, returns all records from the left table (Students) and the matching records from the right table (Courses). If there's no match, it still shows the student, but the course information will be shown as NULL.

Subqueries

A subquery is a query inside another query.


SELECT Name FROM Students
WHERE Age > (
    SELECT AVG(Age) FROM Students
);

A subquery is a query nested inside another SQL query, typically used to perform an intermediate calculation or condition. In the example SELECT Name FROM Students WHERE Age > (SELECT AVG(Age) FROM Students);, the inner query calculates the average age of all students, and the outer query then retrieves the names of students who are older than that average. Subqueries help you make more dynamic and data-driven decisions within your SQL statements.

Constraints

Constraints ensure data integrity:

PRIMARY KEY: Uniquely identifies each row
FOREIGN KEY: Links to another table
NOT NULL: Field cannot be empty
UNIQUE: Prevents duplicate values
CHECK: Validates data

Example:


CREATE TABLE Teachers (
    TeacherID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Age INT CHECK (Age > 25)
);

SQL constraints are rules applied to table columns to maintain data accuracy and consistency. For example, PRIMARY KEY uniquely identifies each row, ensuring no duplicates. FOREIGN KEY creates a relationship between tables. NOT NULL ensures that a column cannot have empty values, and UNIQUE prevents duplicate entries in a column. The CHECK constraint validates that data meets specific conditions, like CHECK (Age > 25) to ensure teachers are older than 25. These constraints help enforce rules on your data to prevent errors.

Views

Views are virtual tables based on a query.


CREATE VIEW AdultStudents AS
SELECT * FROM Students
WHERE Age >= 18;

You can query it like a table:


SELECT * FROM AdultStudents;

A view in SQL is a virtual table created by saving a query, allowing you to simplify complex queries and improve readability. For example, the view AdultStudents is defined using CREATE VIEW to include only students aged 18 or older. Once created, you can use it just like a regular table with queries like SELECT * FROM AdultStudents;. Views don’t store data themselves—they reflect the data in the original table and update automatically when the source data changes.

Indexes

Indexes make queries faster.


CREATE INDEX idx_name ON Students(Name);

Indexes in SQL are used to speed up data retrieval by creating a quick lookup reference for specific columns. When you create an index, like CREATE INDEX idx_name ON Students(Name);, the database builds a structure that allows it to find rows faster when searching or filtering by the Name column. Although indexes improve read performance, they can slightly slow down insert and update operations because the index must also be updated.

Transactions in SQL

Transactions help manage multiple operations as a single unit.


BEGIN;

UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 2;

COMMIT;

Transactions in SQL ensure that a group of operations either all succeed or all fail, maintaining data consistency. A transaction starts with BEGIN, includes one or more SQL statements (like transferring money between accounts), and ends with COMMIT to save the changes. If something goes wrong during the process, you can use ROLLBACK (not shown here) to cancel all changes made during the transaction. This is especially useful in banking systems or any critical multi-step operations.

Stored Procedures

Stored procedures are reusable blocks of code stored in the database.


DELIMITER //
CREATE PROCEDURE GetStudents()
BEGIN
    SELECT * FROM Students;
END //
DELIMITER ;

Call with:


CALL GetStudents();

Stored procedures are pre-written SQL code saved in the database that you can execute whenever needed. They help reduce repetition and improve performance by bundling logic into a single callable unit. In the example, the procedure GetStudents() retrieves all records from the Students table. The DELIMITER is used to define custom statement boundaries for the procedure. Once created, you can run the procedure using CALL GetStudents();, making your code cleaner and more organized.

User Management and Permissions


CREATE USER 'waleed'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON School.* TO 'waleed'@'localhost';

SQL allows you to manage database access by creating users and assigning specific permissions. For example, CREATE USER creates a new user named waleed with a password. The GRANT statement then gives that user permission to perform SELECT and INSERT operations on all tables within the School database. This helps control who can access or modify data, improving security and organization in multi-user environments.

SQL Best Practices

Use WHERE to avoid updating all records accidentally
Avoid SELECT * in production
Normalize your tables to avoid redundancy
Back up your data regularly
Use comments (--) to document complex queries

Following SQL best practices helps ensure your databases are efficient, secure, and maintainable. Always use a WHERE clause to prevent accidental changes to all records. Avoid using SELECT * in production to reduce unnecessary data load. Normalize your tables to eliminate data duplication and ensure consistency. Regularly back up your data to prevent loss, and use comments (--) to explain complex queries, making your code easier to understand and maintain.

Click here for online SQL Code reader.

Click here to download Oracle.

Click to go back Home.

Codcups

Complete SQL Course

What is SQL?

With SQL, you can:

Basic SQL Commands

Creating Tables and Databases

Data Types in SQL

Example:

Retrieving Data with SELECT

Select All Columns

Select Specific Columns

Use of WHERE Clause

Filtering with AND/OR

Sorting and Limiting Data

ORDER BY

LIMIT (MySQL/PostgreSQL)

Inserting Data

To add records:

Multiple records:

Updating Records

Deleting Records

Aggregate Functions

SQL supports built-in functions for summarizing data.

SQL Joins (Combining Tables)

INNER JOIN

LEFT JOIN

Subqueries

Constraints

Example:

Views

Indexes

Transactions in SQL

Stored Procedures

User Management and Permissions

SQL Best Practices