Here’s a detailed breakdown of your CT-1 syllabus with more explanations,
examples, and key points for each topic.
CT-1 Syllabus (SQL & Database Concepts)
1. DDL (Data Definition Language)
DDL statements are used to define, modify, and delete database structures. These
commands do not manipulate data but affect the schema of the database.
DDL Commands
Command Description
CREATE Creates a new database, table, or other objects.
ALTER Modifies an existing database object (e.g., adding/removing columns).
DROP Deletes a database or table permanently.
TRUNCATE Removes all records from a table but keeps its structure.
Examples of DDL Commands
Creating a Table
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Department VARCHAR(50)
);
Altering a Table (Adding a New Column)
ALTER TABLE Students ADD Email VARCHAR(100);
Dropping a Table
DROP TABLE Students;
Truncating a Table (Deleting all records but keeping the structure)
TRUNCATE TABLE Students;
2. DML (Data Manipulation Language)
DML statements modify the data inside tables without altering the table structure.
DML Commands
Command Description
INSERT Adds new records to a table.
UPDATE Modifies existing records in a table.
DELETE Removes records from a table.
Examples of DML Commands
Inserting Data
INSERT INTO Students (StudentID, Name, Age, Department)
VALUES (1, 'John Doe', 20, 'Computer Science');
Updating Data
UPDATE Students
SET Age = 21
WHERE StudentID = 1;
Deleting Data
DELETE FROM Students WHERE StudentID = 1;
3. Integrity Constraints
Integrity constraints are rules applied to table columns to enforce data consistency
and accuracy.
Types of Integrity Constraints
Constraint Description
PRIMARY KEY Ensures each row in a table is unique.
FOREIGN KEY Maintains referential integrity between tables.
NOT NULL Ensures a column cannot have NULL values.
Constraint Description
UNIQUE Ensures all values in a column are different.
CHECK Restricts column values based on a condition.
Examples of Constraints
Primary Key (Unique Identifier)
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(50),
Salary DECIMAL(10,2)
);
Foreign Key (Establishing Relationships)
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
Not Null (Ensuring Column Cannot be Empty)
ALTER TABLE Employees
MODIFY Name VARCHAR(50) NOT NULL;
Unique (Ensuring No Duplicate Values)
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE
);
Check (Restricting Values Based on a Condition)
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Price DECIMAL(10,2) CHECK (Price > 0)
);
4. SQL Commands and Concepts
a) CREATE Statement (Creating Database & Table)
CREATE DATABASE CollegeDB;
USE CollegeDB;
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Department VARCHAR(50)
);
b) SELECT Statement (Retrieving Data)
SELECT * FROM Students;
SELECT Name, Age FROM Students;
c) WHERE Clause (Filtering Records)
SELECT * FROM Students WHERE Age > 18;
SELECT * FROM Students WHERE Department = 'Computer Science';
d) String Matching (LIKE, Wildcards %, _)
Wildcard Description
% Represents zero, one, or multiple characters.
_ Represents a single character.
Examples:
SELECT * FROM Students WHERE Name LIKE 'J%'; -- Names starting with 'J'
SELECT * FROM Students WHERE Name LIKE '_a%'; -- Names where second letter is
'a'
e) ORDER BY (Sorting Results)
SELECT * FROM Students ORDER BY Name ASC; -- Ascending order
SELECT * FROM Students ORDER BY Age DESC; -- Descending order
f) JOIN (Combining Data from Multiple Tables)
Inner Join
SELECT Students.Name, Orders.OrderID
FROM Students
JOIN Orders ON Students.StudentID = Orders.CustomerID;
Left Join
SELECT Students.Name, Orders.OrderID
FROM Students
LEFT JOIN Orders ON Students.StudentID = Orders.CustomerID;
Right Join
SELECT Students.Name, Orders.OrderID
FROM Students
RIGHT JOIN Orders ON Students.StudentID = Orders.CustomerID;
5. Aggregate Functions
Aggregate functions perform calculations on multiple rows and return a single value.
Function Description
COUNT() Counts the number of rows.
Function Description
SUM() Calculates total sum of a column.
AVG() Finds the average value.
MAX() Finds the highest value.
MIN() Finds the lowest value.
Examples of Aggregate Functions
SELECT COUNT(*) FROM Students;
SELECT AVG(Age) FROM Students;
SELECT MAX(Age), MIN(Age) FROM Students;
6. GROUP BY Clause (Grouping Data)
Groups rows with the same values and applies aggregate functions.
Example: Counting Students by Department
SELECT Department, COUNT(StudentID)
FROM Students
GROUP BY Department;
7. HAVING Clause (Filtering Grouped Data)
The HAVING clause is used after GROUP BY to filter grouped results.
Example: Finding Departments with More than 5 Students
SELECT Department, COUNT(StudentID)
FROM Students
GROUP BY Department
HAVING COUNT(StudentID) > 5;
Summary of Key Topics
DDL (CREATE, ALTER, DROP, TRUNCATE) – Defines database structure.
DML (INSERT, UPDATE, DELETE) – Manipulates data inside tables.
Integrity Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK)
– Ensures data accuracy.
SQL Queries (SELECT, WHERE, LIKE, ORDER BY, JOIN) – Retrieves and
manipulates data.
Aggregate Functions (COUNT, SUM, AVG, MAX, MIN) – Performs calculations on
data.
GROUP BY & HAVING – Groups data and filters grouped results.
