INTRODUCTION TO SQL
BSc Physical Sciences - Semester IV
UNIT-II: Comprehensive Study Notes
1. Introduction to SQL & Data Types
What is SQL?
SQL (Structured Query Language) is the standard domain-specific programming language
explicitly designed for managing, querying, and manipulating data stored within relational
database management systems. It functions as the bridge enabling developers and
administrators to construct database architectures, populate records, adjust existing rows,
and construct precise lookups to fetch business analytics.
SQL Data Types
When organizing tables, individual columns must be assigned an explicit data type to
define what category of structural input is permitted. The fundamental variants encompass:
Data Type Description Structural Example
INT Stores solid numeric integers without decimal fractions. Age INT
Variable-length character strings. Dynamically adjusts Name
VARCHAR(size)
memory space up to the designated boundary size limit. VARCHAR(50)
Fixed-length character strings. Automatically pads
CHAR(size) remaining space if string is shorter than designated Gender CHAR(1)
length.
Fixed-point exact numeric records where p marks total Salary
DECIMAL(p, s)
numerical digits and s controls trailing decimal points. DECIMAL(10, 2)
Represents standard calendar records utilizing the
DATE DOB DATE
systemic format YYYY-MM-DD.
Creation of a Database
Initializing work on an environment requires reserving separate allocation zones within
the server instance:
CREATE DATABASE CollegeDB;
Page 1 of 5
BSc Physical Sciences - Semester IV
2. Types of SQL Commands
Database tasks are categorized into distinct structural syntax subsets based on their
underlying direct function: DDL, DML, and DCL.
A. Data Definition Language (DDL)
DDL queries fundamentally dictate or alter the structural schema configuration (the
physical metadata layout) rather than touching internal individual rows. These queries
structurally auto-commit changes instantly to disk storage.
1. Creation of Tables (With & Without Constraints)
Constraints serve as schema rules mapped directly to attributes to police incoming data
accuracy and logical validation metrics.
• Without Constraints:
CREATE TABLE Student (
RollNo INT,
Name VARCHAR(50),
Age INT
);
• With Structural Integrity Constraints:
CREATE TABLE Student (
RollNo INT PRIMARY KEY, -- Forces uniqueness and non-NULL evaluation
Name VARCHAR(50) NOT NULL, -- Eliminates blank field entry anomalies
Email VARCHAR(100) UNIQUE, -- Guards column against duplicate inputs
Age INT CHECK (Age >= 17), -- Ensures conditional domain requirements
CourseID INT,
FOREIGN KEY (CourseID) REFERENCES Course(CourseID) -- Linkage mapping constraint
);
2. ALTER Commands
Executes structural modifications on tables without dropping them outright from active
memory.
• Adding Columns:
Page 2 of 5
BSc Physical Sciences - Semester IV
ALTER TABLE Student ADD Phone VARCHAR(15);
• Dropping Columns:
ALTER TABLE Student DROP COLUMN Age;
3. DROP TABLE
Purges both the structural metadata framework and all matching underlying index rows
permanently from disk storage.
DROP TABLE Student;
B. Data Manipulation Language (DML)
DML expressions focus directly on accessing, shifting, or cleaning the inner structural data
rows hosted inside the target schemas.
1. Insertion into Tables
Drives new tuple insertion blocks straight inside a targeted layout scheme.
INSERT INTO Student (RollNo, Name, Email, Age, CourseID)
VALUES (101, 'Amit Sharma', 'amit@[Link]', 20, 2);
2. Updating a Record
Edits active elements within target tuples. Must be explicitly tied to a WHERE qualifier to
ensure accidental multi-row execution overrides are safely avoided.
UPDATE Student
SET Age = 21
WHERE RollNo = 101;
3. Deleting Records from a Table
Trims target row instances out of metadata scopes matching strict conditional bounds.
Page 3 of 5
BSc Physical Sciences - Semester IV
DELETE FROM Student
WHERE RollNo = 101;
C. Data Control Language (DCL)
DCL statements govern systemic security configurations, enabling granular control over
database privileges and accessibility rights.
• GRANT: Authorizes structural system users to execute targeted commands.
GRANT SELECT, INSERT ON Student TO User_A;
• REVOKE: Strip access privileges back off designated data clients.
REVOKE INSERT ON Student FROM User_A;
3. Retrieving Records & Data Filtering
Basic Retrieval
Fetches targeted data properties across schema configurations using projection statements.
SELECT RollNo, Name FROM Student;
ORDER BY Clause
Sorts the returned projection rows into clear sorted alignments utilizing Ascending (ASC) or
Descending (DESC) configurations. Defaults to ascending layout patterns.
SELECT * FROM Student
ORDER BY Name ASC;
GROUP BY Clause
Collapses row blocks that display overlapping data values inside requested field segments
into clean summarization arrays. Typically combined alongside aggregate evaluation
functions such as COUNT(), MAX(), MIN(), SUM(), or AVG().
Page 4 of 5
BSc Physical Sciences - Semester IV
SELECT CourseID, COUNT(RollNo) AS TotalStudents
FROM Student
GROUP BY CourseID;
Systemic Query Evaluation Order: Relational systems parse multiple processing
clauses sequentially. Filtering happens via WHERE first, followed by structural grouping
via GROUP BY. Aggregate limits are evaluated under HAVING before projection arrays
settle via SELECT. Sorting conditions are resolved last by the ORDER BY clause.
Page 5 of 5