📘 Chapter 8 – Structured Query
Language (SQL)
🧠 Terminologies
Term Description Example
CHAR(n) Fixed-length string; reserves n CHAR(10) → 'Delhi' stored as
characters 'Delhi '
VARCHAR(n Variable-length string; uses only VARCHAR(10) → 'Delhi' uses 5
) needed space characters
INT Stores integer values INT → 123 or -5
FLOAT Stores real/decimal numbers FLOAT → 3.14, 99.99
DATE Stores date in YYYY-MM-DD format '2024-11-01'
NOT NULL Prevents column from having NULL SName VARCHAR(20) NOT NULL
values
UNIQUE Ensures all values in a column are GPhone CHAR(10) UNIQUE
unique
DEFAULT Sets a default value if no input is SDateofBirth DATE DEFAULT
given '2000-05-15'
PRIMARY Uniquely identifies rows, must be RollNumber INT PRIMARY KEY
KEY unique & not null
FOREIGN Links a field to primary key of FOREIGN KEY(GUID) REFERENCES
KEY another table GUARDIAN(GUID)
🧱 SQL Data Definition Language (DDL)
🔹 CREATE DATABASE and USE
CREATE DATABASE `Student Attendance`;
USE `Student Attendance`;
🔹 SHOW TABLES
SHOW TABLES;
🔹 DROP DATABASE / TABLE
DROP DATABASE `Student Attendance`;
DROP TABLE GUARDIAN;
🔹 CREATE TABLE
CREATE TABLE STUDENT (
RollNumber INT,
SName VARCHAR(20),
SDateofBirth DATE,
GUID CHAR(12),
PRIMARY KEY(RollNumber)
);
🔹 DESC (Describe Table Structure)
DESC STUDENT;
🔹 ALTER TABLE – Add/Modify Constraints
Add Primary Key
ALTER TABLE GUARDIAN ADD PRIMARY KEY(GUID);
Add Composite Primary 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 Attribute
ALTER TABLE GUARDIAN MODIFY GAddress VARCHAR(40);
Set NOT NULL
ALTER TABLE STUDENT MODIFY SName VARCHAR(20) NOT NULL;
Set DEFAULT
ALTER TABLE STUDENT MODIFY SDateofBirth DATE DEFAULT '2000-05-15';
🔹 DROP Attribute or Constraint
Drop Column
ALTER TABLE GUARDIAN DROP Income;
Drop Primary Key
ALTER TABLE GUARDIAN DROP PRIMARY KEY;
✍️ SQL Data Manipulation Language (DML)
🔹 Insert Record (All Columns)
INSERT INTO GUARDIAN VALUES ('444444444444', 'Amit Ahuja', '5711492685', 'G-35, Ashok
Vihar, Delhi');
🔹 Insert Record (Selected Columns)
INSERT INTO GUARDIAN (GUID, GName, GAddress) VALUES ('333333333333', 'Danny
Dsouza', 'S-13, Ashok Village, Daman');
🔍 SQL Queries Using SELECT
🔹 Basic Select
SELECT * FROM STUDENT;
🔹 Specific Columns
SELECT SName FROM STUDENT WHERE RollNumber = 1;
🔹 Alias (Renaming Columns)
SELECT EName AS Name FROM EMPLOYEE;
🔹 Calculated Fields
SELECT EName, Salary * 12 AS 'Annual Salary' FROM EMPLOYEE;
🔹 DISTINCT Keyword
SELECT DISTINCT DeptId FROM EMPLOYEE;
🎯 WHERE Clause Filters
🔹 Comparison Operators
SELECT * FROM EMPLOYEE WHERE Salary > 50000;
🔹 BETWEEN
SELECT * FROM EMPLOYEE WHERE Salary BETWEEN 20000 AND 50000;
🔹 IN / NOT IN
SELECT * FROM EMPLOYEE WHERE DeptId IN ('D01', 'D02');
SELECT * FROM EMPLOYEE WHERE DeptId NOT IN ('D01', 'D02');
🔹 IS NULL / IS NOT NULL
SELECT * FROM EMPLOYEE WHERE Bonus IS NULL;
SELECT EName FROM EMPLOYEE WHERE Bonus IS NOT NULL;
-- Note: 5 + NULL results in NULL
🔹 ORDER BY
SELECT * FROM EMPLOYEE ORDER BY Salary DESC;
🔤 Pattern Matching with LIKE
Starts with 'K'
SELECT * FROM EMPLOYEE WHERE EName LIKE 'K%';
Ends with 'a'
SELECT * FROM EMPLOYEE WHERE EName LIKE '%a';
5-letter ending with 'ANYA'
SELECT * FROM EMPLOYEE WHERE EName LIKE '_ANYA';
Contains 'se'
SELECT EName FROM EMPLOYEE WHERE EName LIKE '%se%';
Second letter is 'a'
SELECT EName FROM EMPLOYEE WHERE EName LIKE '_a%';
🔁 UPDATE Records
Single Column
UPDATE STUDENT SET GUID = '101010101010' WHERE RollNumber = 3;
Multiple Columns
UPDATE GUARDIAN
SET GAddress = 'MP', GPhone = '9010810547'
WHERE GUID = '466444444666';
❌ DELETE Records
DELETE FROM STUDENT WHERE RollNumber = 2;
⚠️ If WHERE is omitted, all records will be deleted from the table.