📘 Chapter 8 - Introduction to Structured

Query Language (SQL)

🧠 Key Terminologies
Term Description

SQL Structured Query Language, a standard language for managing and

querying relational databases.

RDBMS Relational Database Management System, software like MySQL to

manage databases.

Data Type Specifies the type of data an attribute can hold (e.g., INT, VARCHAR,
DATE).

Constraint Rule to enforce data integrity (e.g., PRIMARY KEY, FOREIGN KEY, NOT
NULL).

Query SQL statement to retrieve or manipulate data.

Database Collection of related tables managed by an RDBMS.

Relation A table with rows (records) and columns (attributes).

NULL Represents missing or unknown data, distinct from zero.

Primary Key Uniquely identifies each record in a table.

Foreign Key Links a column to a primary key in another table.

Composite Primary key made of multiple attributes.

Key

📄 Introduction
Definition: SQL is a case-insensitive query language used to create, manage, and query
relational databases in RDBMS like MySQL, Oracle, etc.​
Purpose: Simplifies data management compared to file systems by defining structure,
manipulating data, and retrieving data without specifying how to access it.​
Focus: Creating, populating, and querying the Student Attendance database using MySQL.​
Applications: Efficient data storage, retrieval, and manipulation in systems like school records
or business databases.

✅ 1. SQL Overview
Role: Used for defining database structure (DDL), manipulating data (DML), and querying data
(DQL).​
Features:

●​ Case-insensitive (e.g., SELECT = select).

●​ Statements end with a semicolon (;).
●​ Multiline statements use -> prompt; end with ;.

MySQL Installation: Download from [Link] start MySQL service,

use mysql> prompt to enter SQL commands.​
Activity 8.1: Compare MySQL with LibreOffice Base for database management features.

⚒️ 2. Data Types and Constraints in MySQL

Data Types (Table 8.1):

●​ CHAR(n): Fixed-length string (0-255 chars), pads with spaces (e.g., CHAR(10) for 'city'
adds 6 spaces).
●​ VARCHAR(n): Variable-length string (0-65535 chars), stores only actual characters
(e.g., VARCHAR(30) for 'city' uses 4 chars).
●​ INT: Integer for numeric values (e.g., RollNumber).
●​ DATE: Stores dates (e.g., SDateofBirth).

Constraints:

●​ PRIMARY KEY: Ensures unique, non-NULL values.

●​ FOREIGN KEY: Links to a primary key in another table.
●​ NOT NULL: Requires a value.
●​ UNIQUE: Ensures no duplicate values.

Activity 8.2: Explore additional MySQL data types (e.g., FLOAT, TEXT).

⚖️ 3. SQL for Data Definition (DDL)

CREATE DATABASE:

CREATE DATABASE Student_Attendance;

 ●​ Case-sensitive in Linux, not in Windows. Use consistent naming.

USE DATABASE:

USE Student_Attendance;

●​ Selects the active database.

SHOW TABLES:

SHOW TABLES;

●​ Lists all tables in the database (empty initially).

CREATE TABLE:

CREATE TABLE tablename (

attribute1 datatype constraint,
attribute2 datatype constraint
);

●​ Defines table structure with attributes, data types, and constraints.

●​ Example (Student_Attendance tables):

CREATE TABLE STUDENT (

RollNumber INT,
SName VARCHAR(20),
SDateofBirth DATE,
GUID CHAR(12)
);
CREATE TABLE GUARDIAN (
GUID CHAR(12),
GName VARCHAR(20),
GPhone CHAR(10),
GAddress VARCHAR(30)
);
CREATE TABLE ATTENDANCE (
AttendanceDate DATE,
RollNumber INT,
AttendanceStatus CHAR(1)
);
ALTER TABLE:

ALTER TABLE tablename ADD/MODIFY/DROP attribute datatype constraint;

●​ Modifies table structure:

○​ Add primary key: ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);
○​ Add composite key: ALTER TABLE ATTENDANCE ADD PRIMARY KEY
(AttendanceDate, RollNumber);
○​ Add foreign key: ALTER TABLE STUDENT ADD FOREIGN KEY (GUID)
REFERENCES GUARDIAN(GUID);
○​ Add UNIQUE: ALTER TABLE GUARDIAN ADD UNIQUE (GPhone);
○​ Add attribute: ALTER TABLE GUARDIAN ADD income INT;
○​ Modify datatype: ALTER TABLE GUARDIAN MODIFY GAddress
VARCHAR(40);
○​ Modify constraint: ALTER TABLE STUDENT MODIFY SName VARCHAR(20)
NOT NULL;
○​ Set default: ALTER TABLE STUDENT MODIFY SDateofBirth DATE
DEFAULT '2000-05-15';
○​ Drop attribute: ALTER TABLE GUARDIAN DROP income;
○​ Drop primary key: ALTER TABLE GUARDIAN DROP PRIMARY KEY;

DROP Statement:

DROP TABLE tablename;

DROP DATABASE database_name;

●​ Permanently deletes tables or databases (use cautiously).

Activity 8.5: Add foreign key to ATTENDANCE table referencing STUDENT(RollNumber).

🔎 4. SQL for Data Manipulation (DML)

INSERT INTO:

INSERT INTO tablename VALUES (value1, value2, ...);

●​ Inserts records; values must match attribute order or specify columns.

●​ Example:
INSERT INTO GUARDIAN VALUES ('444444444444', 'Amit Ahuja', '5711492685', 'G-35, Ashok
Vihar, Delhi');
INSERT INTO STUDENT (RollNumber, SName, SDateofBirth) VALUES (3, 'Taleem Shah',
'2002-02-28');

●​ Caution: Populate referenced tables (e.g., GUARDIAN) before tables with foreign keys
(e.g., STUDENT).

UPDATE:

UPDATE tablename SET attribute = value WHERE condition;

●​ Modifies records.
●​ Example:

UPDATE STUDENT SET GUID = '101010101010' WHERE RollNumber = 3;

DELETE:

DELETE FROM tablename WHERE condition;

●​ Deletes records.
●​ Example:

DELETE FROM STUDENT WHERE RollNumber = 2;

●​ Caution: Use WHERE to avoid deleting all records.

Activity 8.7: Write SQL to insert remaining rows from Table 8.7 into STUDENT.

📊 5. SQL for Data Query (DQL)

SELECT:

SELECT attribute1, attribute2 FROM tablename WHERE condition;

●​ Retrieves data in tabular form.

●​ Example:

SELECT SName, SDateofBirth FROM STUDENT WHERE RollNumber = 1;

Clauses:

●​ DISTINCT: Removes duplicates (e.g., SELECT DISTINCT Salary FROM

EMPLOYEE;).
●​ WHERE: Filters records with conditions (e.g., =, <, >, <=, >=, !=).
●​ Logical Operators: AND, OR, NOT (e.g., WHERE Salary > 20000 AND Salary <=
50000).
●​ BETWEEN: Specifies range (e.g., WHERE Salary BETWEEN 20000 AND 50000).
●​ IN: Matches values in a list (e.g., WHERE DeptId IN ('D01', 'D02')).
●​ ORDER BY: Sorts results (e.g., ORDER BY Salary DESC).
●​ LIKE: Pattern matching with % (zero or more chars) and _ (single char).
○​ Example: WHERE EName LIKE 'K%' (names starting with 'K').
●​ IS NULL/IS NOT NULL: Checks for NULL values (e.g., WHERE Bonus IS NULL).
●​ AS: Renames columns in output (e.g., SELECT EName AS Name, Salary*12 AS
'Annual Salary' FROM EMPLOYEE).

Examples:

●​ Display unique salaries in D01: SELECT DISTINCT Salary FROM EMPLOYEE WHERE
DeptId = 'D01';
●​ Employees with salary 20000-50000: SELECT EName, DeptId FROM EMPLOYEE
WHERE Salary BETWEEN 20000 AND 50000;
●​ Employees with names ending in 'a': SELECT * FROM EMPLOYEE WHERE EName
LIKE '%a';

📝 Summary Quick Review

SQL Command Function Example

CREATE Creates a new CREATE DATABASE Student_Attendance;

DATABASE database

CREATE TABLE Defines a table CREATE TABLE STUDENT (RollNumber INT,

SName VARCHAR(20));

ALTER TABLE Modifies table ALTER TABLE GUARDIAN ADD income INT;
structure

DROP Deletes DROP TABLE GUARDIAN;

table/database
INSERT INTO Adds records INSERT INTO STUDENT VALUES (1, 'Atharv
Ahuja', '2003-05-15', '444444444444');

UPDATE Modifies records UPDATE STUDENT SET GUID =

'101010101010' WHERE RollNumber = 3;

DELETE Removes records DELETE FROM STUDENT WHERE RollNumber =

2;

SELECT Retrieves data SELECT SName FROM STUDENT WHERE

RollNumber = 1;

🔹 Exercises
Match Clauses:

●​ ALTER: Modify table structure

●​ UPDATE: Modify records
●​ DELETE: Remove records
●​ INSERT INTO: Add records
●​ CONSTRAINTS: Enforce data integrity

MOVIE Database Queries:

●​ a) SELECT * FROM MOVIE;

●​ b) SELECT MovieID, MovieName, BusinessCost FROM MOVIE;
●​ c) SELECT DISTINCT Category FROM MOVIE;
●​ d) SELECT MovieID, MovieName, (BusinessCost - ProductionCost) AS
NetProfit FROM MOVIE;
○​ Note: Not a table column, called a derived column; unreleased movies show
NULL profit, not zero.
●​ e) SELECT MovieID, MovieName, ProductionCost FROM MOVIE WHERE
ProductionCost BETWEEN 80000 AND 125000;
●​ f) SELECT * FROM MOVIE WHERE Category IN ('Comedy', 'Action');
●​ g) SELECT * FROM MOVIE WHERE ReleaseDate IS NULL;

Sports Database:

●​ a) CREATE DATABASE Sports;

●​ b) CREATE TABLE TEAM (TeamID INT, TeamName VARCHAR(10));
●​ c) ALTER TABLE TEAM ADD PRIMARY KEY (TeamID);
 ●​ d) DESC TEAM;
●​ e) INSERT INTO TEAM VALUES (1, 'Team Titan'), (2, 'Team Rockers'),
(3, 'Team Magnet'), (4, 'Team Hurricane');
●​ f) SELECT * FROM TEAM;
●​ g)

CREATE TABLE MATCH_DETAILS (

MatchID CHAR(2),
MatchDate DATE,
FirstTeamID INT,
SecondTeamID INT,
FirstTeamScore INT,
SecondTeamScore INT,
PRIMARY KEY (MatchID),
FOREIGN KEY (FirstTeamID) REFERENCES TEAM(TeamID),
FOREIGN KEY (SecondTeamID) REFERENCES TEAM(TeamID)
);

●​ h) Insert data:

INSERT INTO MATCH_DETAILS VALUES

('M1', '2018-07-17', 1, 2, 90, 86),
('M2', '2018-07-18', 3, 4, 45, 48),
('M3', '2018-07-19', 1, 3, 78, 56),
('M4', '2018-07-19', 2, 4, 56, 67),
('M5', '2018-07-20', 1, 4, 32, 87),
('M6', '2018-07-21', 2, 3, 67, 51);

Relational Algebra for Sports:

●​ a) Matches with both teams scoring >70:​

π_MatchID (σ_FirstTeamScore>70 ∧ SecondTeamScore>70
(MATCH_DETAILS))
●​ b) FirstTeam <70, SecondTeam >70:​
π_MatchID (σ_FirstTeamScore<70 ∧ SecondTeamScore>70
(MATCH_DETAILS))
●​ c) Team 1 wins:​
π_MatchID,MatchDate (σ_FirstTeamID=1 ∧
FirstTeamScore>SecondTeamScore (MATCH_DETAILS))
●​ d) Team 2 loses:​
π_MatchID (σ_FirstTeamID=2 ∧ FirstTeamScore<SecondTeamScore
 (MATCH_DETAILS)) ∪ π_MatchID (σ_SecondTeamID=2 ∧
SecondTeamScore<FirstTeamScore (MATCH_DETAILS))
●​ e) Rename TEAM: Rename to T_DATA, attributes to T_ID, T_NAME (use ALTER
TABLE in SQL).

Differentiate:

●​ ALTER vs. UPDATE: ALTER modifies table structure; UPDATE changes record values.
●​ DELETE vs. DROP: DELETE removes records; DROP removes tables/databases.

SCHOOL_UNIFORM Database:

●​ a) ALTER TABLE PRICE RENAME TO COST;

●​ b) INSERT INTO COST VALUES (7, 'M', 100); (Assumes UCode 7 for
handkerchief).
●​ c) ALTER TABLE COST ADD FOREIGN KEY (UCode) REFERENCES
UNIFORM(UCode);
●​ d) ALTER TABLE UNIFORM MODIFY UName VARCHAR(20) NOT NULL;
●​ e) ALTER TABLE COST ADD CHECK (Price > 0);

🔹 Pro Tips
●​ ✉ Constraints: Use PRIMARY KEY, FOREIGN KEY, NOT NULL to ensure data

🔎
integrity.
●​ WHERE Clause: Always include in UPDATE/DELETE to avoid affecting all records.

🔒
●​ ⚠ NULL Handling: Use IS NULL/IS NOT NULL for testing; avoid arithmetic with NULL.
●​ SELECT: Use DISTINCT, LIKE, and ORDER BY for precise data retrieval.