Cloud

SQL Tutorial

What is SQL?

SQL (Structured Query Language) is the standard programming language used to manage and manipulate relational databases. It is designed for performing tasks such as retrieving data, updating records, inserting new rows, and deleting data from a database.

SQL is both declarative and domain-specific, which means you describe what data you want to work with, not how to retrieve it. It’s supported by all major relational database management systems (RDBMS) such as:

  • MySQL

  • PostgreSQL

  • Oracle Database

  • Microsoft SQL Server

  • SQLite

  • IBM Db2

A simple example:

sql
SELECT name, age FROM employees WHERE age > 30;

This query fetches names and ages of employees who are older than 30.

Why is SQL Important?

SQL plays a critical role in the world of data and application development. Here’s why it’s so important:

1. Universal Language for Databases

SQL is used across almost every RDBMS, which makes it a universal skill for developers, data analysts, and DBAs.

2. Backbone of Data-Driven Applications

From banking systems to e-commerce websites, data is stored and retrieved using SQL. It enables real-time data processing, analysis, and reporting.

3. Enables Powerful Data Analysis

With SQL, users can filter, group, and aggregate data using simple yet powerful commands. It’s essential for business intelligence, reporting, and dashboards.

4. Foundational for Advanced Technologies

SQL is foundational for learning advanced technologies like:

  • Data Warehousing

  • Big Data tools (Hive, Presto)

  • ETL pipelines

  • Business analytics platforms (Tableau, Power BI)

5. Widely Used in Jobs

SQL is among the top 3 most required technical skills for roles like:

  • Data Analyst

  • Backend Developer

  • Business Intelligence Analyst

  • Database Administrator

  • Data Engineer

Real-World Applications of SQL

1. Web Applications

Every time you log into a website, post a comment, or make a purchase, SQL is running in the background—storing user sessions, logging transactions, and querying user data.

Example:

  • Login credentials stored in a database

  • Product listings pulled using SELECT queries

2. Data Analysis and Reporting

Organizations use SQL to analyze sales trends, generate performance reports, and track KPIs.

Example:

sql
SELECT region, SUM(sales) FROM orders GROUP BY region;

This gives the total sales by region for business dashboards.

3. Financial Services

Banks and fintech platforms use SQL to:

  • Validate transactions

  • Generate statements

  • Detect fraud patterns

  • Manage customer data securely

4. Healthcare and Insurance

SQL is used in:

  • Patient record management

  • Insurance claims processing

  • Risk modeling

  • Appointment scheduling

5. E-commerce and Retail

Online retailers use SQL to:

  • Track orders and inventory

  • Personalize product recommendations

  • Analyze shopping behavior

  • Power search and filter systems

6. Human Resources and Payroll

HR departments rely on SQL to:

  • Manage employee databases

  • Calculate payroll

  • Track leaves, attendance, and performance reviews

7. Marketing and CRM

Marketers use SQL to:

  • Segment customer data

  • Measure campaign performance

  • Predict churn and lifetime value (CLTV)

8. Education and LMS Platforms

Learning platforms (like your eLearning site) use SQL to:

  • Track student progress

  • Manage course enrollments

Generate certificates and reports

Understanding Databases

To work effectively with SQL, it’s essential to understand how relational databases are structured and how they store data. This section covers the core building blocks: relational models, tables, rows, columns, primary keys, and foreign keys.

What is a Relational Database?

A relational database is a type of database that stores data in a structured format using tables. Each table represents a specific type of entity (e.g., customers, products, orders), and the data is organized in rows and columns.

Relational databases use relationships to link data across different tables. These relationships are maintained through keys (like primary and foreign keys).

Popular relational database management systems (RDBMS) include:

  • MySQL

  • PostgreSQL

  • Oracle Database

  • Microsoft SQL Server

  • SQLite

Relational databases follow the ACID principles:

  • Atomicity – Transactions are all or nothing

  • Consistency – Data remains valid before and after transactions

  • Isolation – Transactions do not interfere with each other

  • Durability – Committed data is permanently saved

Tables, Rows, and Columns

Table:

A table is a collection of related data entries. It is composed of columns (fields) and rows (records).

Example – Students Table:

StudentID Name Age Grade
1 John Smith 20 A
2 Emma Brown 21 B

  • The table name is Students.

  • Each column (StudentID, Name, Age, Grade) represents a field of the record.

  • Each row is a data record of a single student.

Column:

Defines the type of data stored. Each column has a name and data type (e.g., INT, VARCHAR).

Row:

Represents a single record in the table. A row contains values for each column.

Primary Keys

A Primary Key is a unique identifier for each record in a table. It ensures that no two rows can have the same primary key value.

Characteristics of a Primary Key:

  • Must contain unique values

  • Cannot be NULL

  • Each table can have only one primary key

Example: In the Students table:

sql
CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(100), Age INT );

Here, StudentID is the primary key. It uniquely identifies each student.

Foreign Keys

A Foreign Key is a field in one table that refers to the primary key in another table. It is used to create relationships between tables.

Example:

Let’s say you have a Courses table and an Enrollments table:

sql
CREATE TABLE Courses ( CourseID INT PRIMARY KEY, CourseName VARCHAR(100) ); CREATE TABLE Enrollments ( EnrollmentID INT PRIMARY KEY, StudentID INT, CourseID INT, FOREIGN KEY (StudentID) REFERENCES Students(StudentID), FOREIGN KEY (CourseID) REFERENCES Courses(CourseID) );

In this case:

  • StudentID in Enrollments is a foreign key referring to the Students table.

  • CourseID in Enrollments is a foreign key referring to the Courses table.

These keys ensure referential integrity, meaning:

    • You can’t enroll a student who doesn’t exist in the Students table.

You can’t reference a course that hasn’t been defined in the Courses table.

SQL Data Types

SQL provides a wide range of data types to define the kind of data each column can hold. Choosing the correct data type ensures data integrity, performance, and storage efficiency.

1. Numeric Data Types

Numeric types are used to store whole numbers and decimal values.

Data Type Description Example Values
INT Integer number (no decimal) 1, 100, -25
SMALLINT Smaller range of integers 100, -300
BIGINT Larger range of integers 9999999999
DECIMAL(p, s) Fixed precision number 123.45 (p=5, s=2)
NUMERIC(p, s) Same as DECIMAL 1000.00
FLOAT Approximate decimal value (floating point) 1.2345, 3.14e+05
REAL Lower precision floating point 3.14, -0.001
DOUBLE Double precision floating point 12345.6789

Use INT for whole numbers like age or quantity.
Use DECIMAL or NUMERIC for financial or precise values like prices or interest rates.
Avoid FLOAT for currency—it may lose precision.

2. Character/String Data Types

These types are used to store letters, numbers, and symbols as text.

Data Type Description Example
CHAR(n) Fixed-length string (n characters) ‘Y’ → stored as ‘Y ‘
VARCHAR(n) Variable-length string up to n characters ‘Hello’, ‘abc123’
TEXT Large string of text (size may exceed VARCHAR) Full paragraph, comments

  • CHAR is good for fixed values like country codes (e.g., ‘US’).

  • VARCHAR is ideal for variable data like names or email addresses.

  • TEXT is best for long entries like descriptions or messages.

sql
CREATE TABLE users ( username VARCHAR(50), bio TEXT );
3. Date and Time Data Types

Used to store calendar dates, times, and timestamps.

Data Type Description Example
DATE Stores year, month, and day ‘2025-07-16’
TIME Stores hour, minute, second ’14:30:00′
DATETIME Stores both date and time ‘2025-07-16 14:30:00’
TIMESTAMP Stores date/time with timezone support ‘2025-07-16T14:30:00Z’
YEAR Stores a year (MySQL specific) ‘2025’

Use these types for:

  • Birthdays → DATE

  • Order timestamps → TIMESTAMP or DATETIME

  • Log records → TIMESTAMP

sql
CREATE TABLE orders ( order_id INT, order_date DATETIME );
4. Boolean Data Type

Boolean stores TRUE or FALSE values.

Data Type Description Example
BOOLEAN or BOOL True/False flag TRUE, FALSE, 1, 0

In many databases:

  • TRUE is stored as 1

  • FALSE is stored as 0

Use Boolean values for:

  • IsActive flags

  • IsDeleted or IsVerified fields

sql

CREATE TABLE users ( user_id INT, is_active BOOLEAN );

SQL Statement Categories

SQL statements are grouped into four main categories based on their functionality: DDL, DML, DCL, and TCL. Understanding these categories helps you write more efficient and structured queries for database operations.

1. DDL – Data Definition Language

DDL commands are used to define and modify the structure of database objects like tables, schemas, indexes, etc. These changes affect the database schema.

Command Description
CREATE Creates a new table, database, or index
ALTER Modifies an existing object (add/remove column, etc.)
DROP Deletes tables, views, databases, etc.
TRUNCATE Removes all records from a table (faster than DELETE)
RENAME Renames a table or column
Example:
sql
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(100), Department VARCHAR(50) );
2. DML – Data Manipulation Language

DML commands allow you to manipulate (insert, update, delete) data in existing tables. These operations affect the records, not the schema.

Command Description
SELECT Retrieves data from one or more tables
INSERT Adds new rows to a table
UPDATE Modifies existing records
DELETE Removes rows from a table
MERGE Combines INSERT and UPDATE in one step (supported in some RDBMS)
Example:
sql
INSERT INTO Employees (EmployeeID, Name, Department) VALUES (1, 'John Smith', 'HR');
3. DCL – Data Control Language

DCL commands manage access rights, permissions, and other controls of the database.

Command Description
GRANT Gives privileges to users or roles
REVOKE Removes previously granted permissions
Example:
sql
GRANT SELECT, INSERT ON Employees TO user123;

This allows user123 to read and insert data into the Employees table.

4. TCL – Transaction Control Language

TCL commands handle transactions, ensuring consistency and reliability of database operations. Transactions are useful when a group of DML operations must be treated as a single unit.

Command Description
BEGIN Starts a transaction
COMMIT Saves all changes made in the transaction
ROLLBACK Undoes changes made in the current transaction
SAVEPOINT Sets a point to roll back to within a transaction
SET TRANSACTION Sets properties like isolation level
Example:
sql
BEGIN; UPDATE Employees SET Department = 'Finance' WHERE EmployeeID = 1; DELETE FROM Employees WHERE Department = 'Sales'; ROLLBACK;

This will undo both operations if something goes wrong.

Creating and Managing Tables in SQL

In SQL, tables are the core structures that store data in rows and columns. To effectively manage databases, you need to understand how to create, modify, and delete tables, along with defining constraints to ensure data integrity.

CREATE TABLE

The CREATE TABLE command is used to create a new table by specifying the column names, data types, and constraints.

Basic Syntax:
sql
CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, ... );
Example:
sql
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(100) NOT NULL, Email VARCHAR(100) UNIQUE, Department VARCHAR(50), Salary DECIMAL(10,2) DEFAULT 50000, Age INT CHECK (Age >= 18) );

This table includes:

  • A primary key on EmployeeID

  • A NOT NULL constraint on Name

  • A UNIQUE constraint on Email

  • A DEFAULT salary

  • A CHECK constraint to ensure age is 18 or older

ALTER TABLE

The ALTER TABLE command allows you to modify an existing table. You can:

  • Add or remove columns

  • Change column data types

  • Add or drop constraints

Add a Column:
sql
ALTER TABLE Employees ADD JoinDate DATE;

Modify a Column:

sql
ALTER TABLE Employees MODIFY Salary DECIMAL(12,2);

Drop a Column:

sql
ALTER TABLE Employees DROP COLUMN JoinDate;

Add a Constraint:

sql
ALTER TABLE Employees ADD CONSTRAINT chk_salary CHECK (Salary > 0);

DROP TABLE

The DROP TABLE command is used to permanently delete a table and all its data from the database.

Syntax:

sql
DROP TABLE table_name;
Example:
sql
DROP TABLE Employees;

Note: This action is irreversible—use with caution.

Common Table Constraints

SQL constraints are rules applied to columns to enforce data integrity.

Constraint Description
NOT NULL Prevents NULL values in a column
UNIQUE Ensures all values in a column are different
DEFAULT Sets a default value for a column if no value is given
CHECK Ensures values meet a condition
PRIMARY KEY Uniquely identifies each row in the table
FOREIGN KEY Enforces referential integrity between tables

1. NOT NULL

Ensures the column must have a value.

sql
Name VARCHAR(100) NOT NULL

2. UNIQUE

Ensures all values in the column are different.

sql
Email VARCHAR(100) UNIQUE

3. DEFAULT

Automatically assigns a value if none is provided.

sql
Salary DECIMAL(10,2) DEFAULT 50000

4. CHECK

Validates values using a condition.

sql
Age INT CHECK (Age >= 18)

5. PRIMARY KEY

Identifies each row uniquely. Combines NOT NULL + UNIQUE.

sql
EmployeeID INT PRIMARY KEY

6. FOREIGN KEY

Links one table to another using a key.

sql

CREATE TABLE Departments ( DeptID INT PRIMARY KEY, DeptName VARCHAR(50) );

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(100), DeptID INT, FOREIGN KEY (DeptID) REFERENCES Departments(DeptID) );

Inserting and Manipulating Data in SQL

Once you’ve created your tables, the next step is to insert, update, and delete data. These are key operations in the Data Manipulation Language (DML) category of SQL.

1. INSERT INTO – Add New Records

The INSERT INTO statement adds new rows into a table.

Syntax:
sql
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Example:
sql
INSERT INTO Employees (EmployeeID, Name, Email, Department, Salary, Age) VALUES (101, 'Alice Johnson', 'alice@example.com', 'HR', 60000, 28);

You can also insert multiple rows at once:

sql
INSERT INTO Employees (EmployeeID, Name, Department) VALUES (102, 'Bob Smith', 'Finance'), (103, 'Clara Brown', 'IT');
Tip: Always list column names to avoid issues if table structure changes.

2. UPDATE – Modify Existing Records

The UPDATE statement modifies data that already exists in a table.

Syntax:
sql
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Example:
sql
UPDATE Employees SET Salary = 65000 WHERE EmployeeID = 101;

You can update multiple columns:

sql
UPDATE Employees SET Department = 'Operations', Age = 30 WHERE Name = 'Alice Johnson';
Important: Always use a WHERE clause to avoid updating all rows.

3. DELETE – Remove Records

The DELETE statement removes existing records from a table.

Syntax:
sql
DELETE FROM table_name WHERE condition;
Example:
sql
DELETE FROM Employees WHERE EmployeeID = 103;

If you omit the WHERE clause:

sql
DELETE FROM Employees;

This deletes all rows from the table—use with caution!

Comparison Table

Operation Purpose Requires WHERE? Risk Level
INSERT Add new row(s) No Low
UPDATE Modify existing row(s) Yes (recommended) Medium to High
DELETE Remove row(s) Yes (recommended) High

Real-World Use Cases

Use Case SQL Example
Add a new employee INSERT INTO Employees (...) VALUES (...);
Increase salaries by 10% UPDATE Employees SET Salary = Salary * 1.10;
Remove inactive users DELETE FROM Users WHERE is_active = FALSE;
Correct a user’s email UPDATE Users SET Email = 'new@mail.com' WHERE ID=1;
— Latest Posts

MuleSoft Interview Questions

July 19, 2025 Cloud

SQL Tutorial

July 19, 2025 Cloud

MuleSoft Tutorial

July 18, 2025 Cloud
— Upcoming
$ 75.00
Design
UI/UX Design Course
Mike Lewis
  • 9 Lessons

Join our community

ABOUT US

eLearnCourses is a trusted destination for high-quality, industry-relevant online IT training. We are committed to empowering learners and professionals with the practical skills and knowledge they need to succeed in the digital era.

Training Delivered In Cities/Countries: Hyderabad, Bangalore, Mumbai, Delhi, Chennai, Pune, Texas, California, New Jersey, Virginia, London, Dubai (UAE), Toronto, Melbourne, Sydney, Singapore, and many more.

TRENDING COURSES
Socials
Support
Contact Us
Suite 204, 1200 South Avenue, Staten Island, New York, NY – 10314
 
 Unit 210, 789 Don Mills Road, North York, Toronto, ON, M3C 1T5
 
 #301, Sai Sree Residency, Ayyappa Society, Madhapur, Hyderabad, Telangana – 500081

Powered by eLearnCourses. All rights reserved.