Query Language SQL

Definition

SQL (Structured Query Language) is a standard declarative language used to define, manipulate, control, and query data stored in relational databases. It is used to create database structures, insert records, retrieve information, modify data, and manage access permissions.

A relational database organizes data into tables made of rows and columns. SQL works with these tables through commands such as SELECT, INSERT, UPDATE, DELETE, CREATE, and ALTER.

Example:

SELECT name, age
FROM students
WHERE age > 18;

This SQL statement retrieves the names and ages of students whose age is greater than 18.

---

Main Content

1. First Concept: SQL Basics and Database Tables

• SQL is designed to work with relational databases, where data is stored in tables.
• A table consists of rows and columns:
• Rows represent individual records.
• Columns represent attributes or fields.
• SQL commands are used to interact with these tables in a structured way.

A simple table called Students may look like this:

student_id	name	age	class
1	Anil	17	12A
2	Sita	18	12B
3	Ramesh	17	12A

Here:

• student_id, name, age, and class are columns
• Each record in the table is a row

SQL allows operations on such data using statements like:

SELECT * FROM Students;

This means “retrieve all rows and all columns from the Students table.”

Important SQL basics include:

Table

• : A structured collection of related data

Record/Row

• : One complete entry in a table

Field/Column

• : One category of information

Database

• : A collection of related tables

2. Second Concept: SQL Command Categories

• SQL commands are grouped into different categories based on their purpose.
• The major categories are:
• DDL (Data Definition Language): Defines database structure
• DML (Data Manipulation Language): Changes the data inside tables
• DQL (Data Query Language): Retrieves data from tables
• DCL (Data Control Language): Controls access to data
• TCL (Transaction Control Language): Manages transactions

DDL

DDL is used to create and modify database structures.

Examples:

CREATE TABLE Employees (
    emp_id INT,
    emp_name VARCHAR(50),
    salary DECIMAL(10,2)
);

Other DDL commands:

• ALTER — modify table structure
• DROP — delete a table permanently
• TRUNCATE — remove all records from a table

DML

DML is used to work with the data inside tables.

Examples:

INSERT INTO Employees VALUES (1, 'Rahul', 45000);
UPDATE Employees SET salary = 50000 WHERE emp_id = 1;
DELETE FROM Employees WHERE emp_id = 1;

DQL

DQL is mainly used to retrieve data.

Example:

SELECT * FROM Employees;

DCL

DCL controls permissions and security.

Examples:

• GRANT — give access
• REVOKE — remove access

TCL

TCL manages database transactions, ensuring data consistency.

Examples:

• COMMIT — save changes permanently
• ROLLBACK — undo changes
• SAVEPOINT — create a checkpoint within a transaction

3. Third Concept: SQL Queries, Clauses, and Filtering

• A query is a request to the database for specific information.
• SQL queries often use clauses such as WHERE, ORDER BY, GROUP BY, and HAVING to refine results.
• These clauses help in filtering, sorting, grouping, and summarizing data.

WHERE Clause

Used to filter rows based on a condition.

SELECT * FROM Students
WHERE class = '12A';

This retrieves only students from class 12A.

ORDER BY Clause

Used to sort results.

SELECT name, age FROM Students
ORDER BY age DESC;

This sorts students by age in descending order.

GROUP BY Clause

Used to group rows with similar values.

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

This counts how many students are in each class.

HAVING Clause

Used to filter grouped results.

SELECT class, COUNT(*)
FROM Students
GROUP BY class
HAVING COUNT(*) > 10;

This displays only classes with more than 10 students.

Common Query Features

DISTINCT

• : removes duplicate values

LIMIT / TOP

• : restricts number of rows returned

Aliases

• : rename columns or tables temporarily

Example:

SELECT DISTINCT class FROM Students;

This returns only unique class names.

---

Working / Process

1. Define the database structure

• First, tables are created using SQL DDL commands such as CREATE TABLE.
• Data types are chosen for each column, such as INT, VARCHAR, DATE, and DECIMAL.
• Primary keys and constraints are added to maintain data integrity.

2. Insert and manage data

• Once the structure is ready, records are inserted using INSERT.
• Existing records can be changed with UPDATE.
• Unwanted records can be removed using DELETE.
• Transactions may be used to ensure changes are completed safely.

3. Retrieve and analyze information

• SQL queries are written using SELECT.
• Conditions are applied using WHERE, sorting with ORDER BY, grouping with GROUP BY, and filtering groups with HAVING.
• The database engine processes the query and returns the required result set.

A simple flow of SQL operation:

User Query
    |
    v
SQL Parser
    |
    v
Database Engine
    |
    v
Table Access
    |
    v
Result Output

This shows how a query moves from the user to the database system and returns results.

---

Advantages / Applications

• SQL is easy to understand and widely standardized, making it useful across many database systems such as MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.
• SQL is powerful for managing large volumes of data, allowing quick searching, sorting, filtering, and reporting.
• SQL is used in many real-world applications such as banking, e-commerce, healthcare, education, inventory systems, payroll, and data analytics.

More practical advantages include:

• Faster data retrieval compared to manual searching
• Better data integrity through constraints and transactions
• Support for complex operations like joins, grouping, and aggregation
• Strong security and access control through permissions
• Useful for both technical users and data professionals

Typical applications of SQL:

• Creating and managing student databases
• Maintaining employee records in organizations
• Generating sales and financial reports
• Building backend systems for websites and apps
• Analyzing business data for decision-making

---

Summary

• SQL is the standard language used to work with relational databases.
• It helps in creating tables, storing records, retrieving data, and managing database operations.
• SQL is essential for handling structured data efficiently in real-world systems.
• Important terms to remember: table, row, column, query, SELECT, INSERT, UPDATE, DELETE, WHERE, GROUP BY, ORDER BY, DDL, DML, DQL, DCL, TCL