Chapter 7 – Introduction to Structured Query
Language (SQL)
7.1 Introduction to SQL
Structured Query Language (SQL) is a standardized programming language used for managing
and manipulating data in relational databases such as Microsoft Access, MySQL, Oracle, and SQL
Server. SQL allows users to perform tasks like defining structures, inserting, updating, retrieving,
and deleting data efficiently.
Key Characteristics:
1 SQL is a non-procedural language — users specify what they want, not how to get it.
2 It follows ANSI and ISO standards (SQL-92, SQL-99, SQL:2003, etc.).
3 Used in almost all relational database management systems (RDBMS).
7.2 Data Definition Language (DDL)
DDL commands define the structure of the database objects such as tables, views, and indexes.
Common DDL Commands:
1 CREATE TABLE – Creates a new table in the database.
2 ALTER TABLE – Modifies existing table structures.
3 DROP TABLE – Deletes an existing table.
4 CREATE INDEX – Creates an index to speed up data retrieval.
5 CREATE VIEW – Defines a virtual table based on SQL queries.
Example (MS Access):
CREATE TABLE Students ( StudentID AUTOINCREMENT PRIMARY KEY, StudentName
TEXT(50) NOT NULL, Course TEXT(50), Age INTEGER, City TEXT(30) );
7.3 Data Manipulation Language (DML)
DML commands are used to manage the data stored within tables. These commands add, modify,
delete, or retrieve records from the database.
1 INSERT – Add new records.
2 UPDATE – Modify existing records.
3 DELETE – Remove existing records.
4 SELECT – Retrieve records from one or more tables.
Examples (MS Access):
INSERT INTO Students (StudentName, Course, Age, City) VALUES ('Aryan',
'BSc IT', 20, 'Delhi');
UPDATE Students SET City = 'Mumbai' WHERE StudentID = 1;
DELETE FROM Students WHERE City = 'Delhi';
SELECT * FROM Students;
7.4 SELECT Queries
The SELECT statement is the most commonly used SQL command. It retrieves data from tables
according to specified conditions.
Syntax:
SELECT column1, column2 FROM table_name WHERE condition;
Example:
SELECT StudentName, City FROM Students WHERE Course = 'BSc IT';
7.5 Advanced SELECT Operations
SQL provides several advanced features for filtering, grouping, and aggregating data.
1 ORDER BY – Sorts results in ascending or descending order.
2 GROUP BY – Groups records based on column values.
3 HAVING – Filters grouped data (used with GROUP BY).
4 Aggregate Functions – COUNT(), SUM(), AVG(), MIN(), MAX()
Example:
SELECT Course, COUNT(StudentID) AS TotalStudents FROM Students GROUP BY
Course HAVING COUNT(StudentID) > 10;
7.6 Joining Tables
A JOIN is used to combine records from two or more tables based on related columns.
1 INNER JOIN – Returns only matching rows.
2 LEFT JOIN – Returns all rows from the left table even if there is no match.
3 RIGHT JOIN – Returns all rows from the right table even if there is no match.
Example (MS Access):
SELECT Students.StudentName, Courses.CourseName FROM Students INNER JOIN
Courses ON Students.Course = Courses.CourseID;
7.7 Creating Views (Virtual Tables)
A view is a virtual table that displays data from one or more tables using a SELECT query. In MS
Access, views are similar to saved queries.
Example:
CREATE VIEW SeniorStudents AS SELECT StudentName, Course, Age FROM
Students WHERE Age > 21;
Summary
SQL is a powerful and essential language for managing data in relational databases. It allows users
to create, modify, and retrieve data efficiently. In MS Access, SQL is used behind forms, queries,
and reports, making it vital for database development and data analysis.
