CHAPTER 3
Create the DDL Commands – Table Creation, Altering the Table Structure, Truncating a Table and
Dropping a Table
CREATE TABLE:
CREATE DATABASE EEE;
USE EEE;
CREATE TABLE STUDENTS(
ID INT PRIMARY KEY,
NAME VARCHAR(25),
AGE TINYINT,
DEPARTMENT CHAR(3),
MARKS DECIMAL(3,1));
INSERT INTO STUDENTS
VALUES(101,'RAMU',19,'EEE',89.5),
(102,'SITA',18,'ECE',78.5),
(103,'KRISHNA',20,'CSE',91.0),
(104,'RADHA',19,'MEC',86.5);
ALTER TABLE
Add a column:
ALTER TABLE STUDENTS
ADD COURSE_ID INT;
UPDATE STUDENTS SET COURSE_ID=201 WHERE ID=101;
UPDATE STUDENTS SET COURSE_ID=202 WHERE ID=102;
UPDATE STUDENTS SET COURSE_ID = 203 WHERE ID = 103;
UPDATE STUDENTS SET COURSE_ID = 201 WHERE ID = 104;
TRUNCATE TABLE
Deletes all records but keeps table structure.
TRUNCATE TABLE STUDENT;
DROP TABLE
Deletes the table completely.
DROP TABLE STUDENT;
Develop the DML Commands – Insert, Select, Update and Delete Commands
INSERT Command:
Used to add new records into a table.
INSERT INTO STUDENT
VALUES (3, 'Ramu', 'CSE', 70);
SELECT Command:
Used to retrieve data from a table.
SELECT * FROM STUDENT;
Select specific columns:
SELECT Name, Marks
FROM STUDENT;
With condition:
SELECT * FROM STUDENT
WHERE Dept = 'CSE';
UPDATE Command:
Used to modify existing records.
UPDATE STUDENT
SET Marks = 85
WHERE SID = 1;
DELETE Command:
Used to remove records from a table.
DELETE FROM STUDENT
WHERE SID = 2;
Given a table STUDENTS (student_id, name, age, department, marks), develop SQL queries to:
i) Display the names and departments of all students.
ii) Display the details of students with marks greater than 75.
iii) Display students from the 'Computer Science' department.
CREATE DATABASE EEE;
USE EEE;
CREATE TABLE STUDENTS(
ID INT PRIMARY KEY,
NAME VARCHAR(25),
AGE TINYINT,
DEPARTMENT CHAR(3),
MARKS DECIMAL(3,1));
INSERT INTO STUDENTS
VALUES(101,'RAMU',19,'EEE',89.5),
(102,'SITA',18,'ECE',78.5),
(103,'KRISHNA',20,'CSE',91.0),
(104,'RADHA',19,'MEC',86.5);
#Display the names and departments of all students.
SELECT NAME,DEPARTMENT FROM STUDENTS;
#Display the details of students with marks greater than 80.
SELECT * FROM STUDENTS
WHERE MARKS >80;
#Display students from the 'Computer Science' department.
SELECT * FROM STUDENTS
WHERE DEPARTMENT='CSE';
SQL Arithmetic Operators
Arithmetic operators perform simple arithmetic operations such as addition,
subtraction, multiplication etc.
Operator Description
+ Addition
- Subtraction
* Multiplication
/ Divide
% Modulo (Remainder)
# AIRTHMETIC OPERATORS
SELECT 20+30;
SELECT 30-20;
SELECT 30*20;
SELECT 30%20;
SELECT ID, NAME, AGE, MARKS+5 AS NEW_MARKS FROM STUDENTS;
SELECT ID, NAME, AGE, MARKS-10 AS NEW_MARKS FROM STUDENTS;
LOGICAL OPERATORS( AND, OR, NOT):
#LOGICAL OPERATORS
SELECT * FROM STUDENTS
WHERE MARKS>80 AND AGE>=19;
SELECT * FROM STUDENTS
WHERE MARKS<90 OR AGE<>19;
SELECT * FROM STUDENTS
WHERE NOT(MARKS<90);
Using a table EMPLOYEES (emp_id, name, salary, department_id), develop SQL queries to:
i) Display employees who earn more than 5000 and belong to department 20.
ii) Display employees whose salary is not equal to 6000 and department is not 30.
1. Employees earning more than 5000 and belonging to department 20
SELECT *
FROM EMPLOYEES
WHERE salary > 5000 AND department_id = 20;
2. Employees whose salary is not equal to 6000 and department is not 30
SELECT *
FROM EMPLOYEES
WHERE salary <> 6000 AND department_id <> 30;
STUDENTS TABLE & COURSES TABLES:
#INNER JOIN
SELECT [Link],
[Link],[Link],STUDENTS.COURSE_ID,COURSES.COURSE_NAME
FROM STUDENTS INNER JOIN COURSES ON
STUDENTS.COURSE_ID=COURSES.COURSE_ID;
#LEFT JOIN
SELECT [Link],
[Link],[Link],STUDENTS.COURSE_ID,COURSES.COURSE_NAME
FROM STUDENTS LEFT JOIN COURSES
ON STUDENTS.COURSE_ID=COURSES.COURSE_ID;
#RIGHT JOIN
SELECT [Link],
[Link],[Link],STUDENTS.COURSE_ID,COURSES.COURSE_NAME
FROM STUDENTS RIGHT JOIN COURSES
ON STUDENTS.COURSE_ID=COURSES.COURSE_ID;
#FULL JOIN
SELECT [Link],
[Link],[Link],STUDENTS.COURSE_ID,COURSES.COURSE_NAME
FROM STUDENTS LEFT JOIN COURSES
ON STUDENTS.COURSE_ID=COURSES.COURSE_ID
UNION
SELECT [Link],
[Link],[Link],STUDENTS.COURSE_ID,COURSES.COURSE_NAME
FROM STUDENTS RIGHT JOIN COURSES
ON STUDENTS.COURSE_ID=COURSES.COURSE_ID;
Lossless Join Decomposition
Definition:
Lossless Join Decomposition means splitting a table into two or more tables in such a way that
we can join them again to obtain the original table without losing any information.
Simple Meaning
• A large table is divided into smaller tables
• When we JOIN those tables again, we must get exactly the same original table
• No data loss and no extra tuples
Example
Original Table: STUDENT
SID NAME COURSE
1 Ravi DBMS
2 Sita JAVA
Decomposition
Table 1 : STUDENT_INFO
SID NAME
1 Ravi
2 Sita
Table 2 : COURSE_INFO
SID COURSE
1 DBMS
2 JAVA
Joining Again:
SELECT *
FROM STUDENT_INFO
JOIN COURSE_INFO
ON STUDENT_INFO.SID = COURSE_INFO.SID;
SID NAME COURSE
1 Ravi DBMS
2 Sita JAVA
DATE AND TIME FUNCTIONS:
1. NOW(): Returns the current date and time (e.g., '2025-02-24 08:18:00').
2. CURDATE(): Returns only the current date (e.g., '2025-02-24')
3. CURTIME(): Returns only the current time (e.g., '08:18:00').
4. YEAR(date): Extracts the year.
5. MONTH(date) : Extracts the month as a number (1-12).
6. DAY(date) : Extracts the day of the month (1-31).
7. HOUR(time) : Extracts the hour.
8. MINUTE(time) : Extracts the minute.
9. SECOND(time) : Extracts the second.
10. DAYNAME(date) : Returns the full name of the weekday (e.g., 'Monday').
11. MONTHNAME(date) : Returns the full name of the month (e.g., 'February').
NUMERICAL FUNCTIONS:
1. ROUND(x, d): Rounds a number to a specified number of decimal places (d)
2. CEIL(x) / CEILING(x): Returns the smallest integer value that is greater than or
equal to x.
3. FLOOR(x): Returns the largest integer value that is less than or equal to x.
4. TRUNCATE(x, d): Truncates a number to decimal places without rounding
5. ABS(x): Returns the absolute (positive) value of a number.
6. MOD(x, y): Returns the remainder of x divided by y .
7. POW(x, y) / POWER(x, y): Returns x raised to the power of y .
8. SQRT(x): Returns the square root of a non-negative number.
9. SIGN(x): Returns the sign of a number as -1 (negative), 0 (zero), or 1 (positive).
Aggregate functions:
10. COUNT(): Counts number of rows.
SELECT COUNT(*) FROM students;
Counts total students.
Count only non-null values: Counts only students who have marks (ignores
NULL).
SELECT COUNT(marks) FROM students;
11. SUM(): SELECT SUM(marks) FROM students;
Gives total marks of all students.
12. AVG(): SELECT AVG(marks) FROM students;
Gives average marks.
13. MAX(): SELECT MAX(marks) FROM students;
Gives Highest marks.
14. MIN(): SELECT MIN(marks) FROM students;
Gives Lowest marks.
GROUP BY Clause:
The GROUP BY clause is used to arrange rows into groups based on one or more
columns. It is mainly used with aggregate functions.
HAVING Clause:
The HAVING clause is used to filter groups after the GROUP BY operation. It is
used to apply conditions on aggregate functions.
Example: Employee Salary Table
Table: employees
EmpID Dept Salary
1 CSE 50000
2 CSE 60000
3 EEE 40000
EmpID Dept Salary
4 EEE 45000
5 MECH 30000
Problem: Display departments where total salary is more than 90,000
SELECT Dept, SUM(Salary) AS Total_Salary
FROM employees
GROUP BY Dept
HAVING SUM(Salary) > 90000;
Step-by-Step Explanation
Step 1: GROUP BY Dept
CSE → 50000 + 60000 = 110000
EEE → 40000 + 45000 = 85000
MECH → 30000
Step 2: HAVING condition
Condition: SUM(Salary) > 90000
✔CSE→110000✔(included)
✘EEE→85000
✘ MECH → 30000
Final Output
Dept Total_Salary
CSE 110000
Example: 2 Student Marks Table
Table: students
RollNo Dept Marks
1 CSE 85
2 CSE 75
3 CSE 65
4 EEE 90
RollNo Dept Marks
5 EEE 80
6 MECH 60
7 MECH 50
Problem: Display departments where
Average marks > 75
Number of students ≥ 2
SELECT Dept, AVG(Marks) AS Avg_Marks, COUNT(*) AS Total_Students
FROM students
GROUP BY Dept
HAVING AVG(Marks) > 75 AND COUNT(*) >= 2;
Step-by-Step Solution
Step 1: GROUP BY Dept
CSE
Marks:85,75,65
Average=(85+75+65)/3=75
Count = 3
EEE
Marks:90,80
Average=(90+80)/2=85
Count = 2
MECH
Marks:60,50
Average=(60+50)/2=55
Count = 2
Step 2: Apply HAVING Condition
Condition:
• AVG(Marks) > 75
• COUNT(*) ≥ 2
Check:
✔EEE→Avg=85(>75 )and Count = 2 ✔
✘ CSE → Avg = 75 (not >75)
✘ MECH → Avg = 55
Final Output
Dept Avg_Marks Total_Students
EEE 85 2
ORDER BY Clause:
The ORDER BY clause is used to sort the result of a query. It can sort data:
• In Ascending order (ASC) – default
• In Descending order (DESC)
Example 1: Simple ORDER BY
Table: students
RollNo Name Marks
1 Arun 75
2 Bala 90
3 Charan 60
4 David 85
Problem: Display students in ascending order of marks
SELECT Name, Marks
FROM students
ORDER BY Marks ASC;
Output
Name Marks
Charan 60
Arun 75
David 85
Bala 90
Example 2: Descending Order
Display students in descending order of marks
SELECT Name, Marks
FROM students
ORDER BY Marks DESC;
String Conversion Functions
1. UPPER() Function
Purpose: Converts all characters of a string to uppercase.
SELECT UPPER('database systems');
2. LOWER() Function
Purpose: Converts all characters of a string to lowercase.
SELECT LOWER('DATABASE SYSTEMS');
3. INITCAP() Function
Purpose: Converts the first letter of each word to uppercase and the remaining
letters to lowercase.
SELECT INITCAP('database management system');
4. TO_CHAR() Function
Purpose: Converts numbers or dates into character (string) format.
SELECT TO_CHAR(1250) ; Ouput:’1250’
5. TO_NUMBER() Function
Purpose: Converts a string into numeric format.
SELECT TO_NUMBER('500') + 100; OUPUT:600
6. TO_DATE() Function
Purpose: Converts a string into date format.
SELECT TO_DATE('07-03-2026','DD-MM-YYYY') ; OUTPUT:07-MAR-2026
Difference Between Nested Queries and Subqueries:
Feature Subquery Nested Query
A query written inside A query that contains multiple levels of
Definition
another SQL query. queries (query inside query inside query).
Complexity Simple More complex
Number of inner
Usually one inner query More than one inner query
queries
To provide a value/result to To solve complex problems using multiple
Purpose
the main query query levels
Example Tables
STUDENT Table
ID NAME MARKS
1 Ravi 80
2 Sita 70
ID NAME MARKS
3 Ram 90
4 Anu 60
1. Subquery Example (Single Inner Query)
Problem: Find students whose marks are greater than average marks.
SELECT NAME
FROM STUDENT
WHERE MARKS > (SELECT AVG(MARKS) FROM STUDENT);
Step 1: Inner Query
SELECT AVG(MARKS) FROM STUDENT;
Average = 75
Step 2: Outer Query Result
NAME
Ravi
Ram
Because 80 and 90 > 75
2. Nested Query Example (Multiple Inner Queries)
Problem: Find the second highest marks student.
SELECT NAME
FROM STUDENT
WHERE MARKS =( SELECT MAX(MARKS)
FROM STUDENT
WHERE MARKS < ( SELECT MAX(MARKS)
FROM STUDENT) );
Step-by-step:
Inner Query: SELECT MAX(MARKS) FROM STUDENT;
Result = 90
Second Query:
SELECT MAX(MARKS) FROM STUDENT WHERE MARKS < 90;
Result = 80
Outer Query:
NAME
Ravi
1. Primary Key (Entity Integrity Constraints)
Definition
A Primary Key is a column that uniquely identifies each record in a table.
✔ No duplicate values
✔ No NULL values
✔ Only one primary key per table
Example Table: STUDENT
ID NAME MARKS
1 Ravi 80
2 Sita 65
3 Ram 90
Here ID uniquely identifies each student.
CREATE TABLE STUDENT(
ID INT PRIMARY KEY,
NAME VARCHAR(20),
MARKS INT);
2. Foreign Key (Referential Integrity Constraints)
Definition
A Foreign Key is a column that links one table to another table's Primary Key.
It creates a relationship between two tables.
Example Tables
STUDENT Table
SID NAME
1 Ravi
2 Sita
COURSE Table:
CID SID COURSE
101 1 DBMS
102 2 JAVA
Here:
• SID in STUDENT → Primary Key
• SID in COURSE → Foreign Key
Constraints in SQL
Definition
Constraints are rules applied on table columns to maintain data accuracy and integrity.
Types of Constraints:
Constraint Purpose
PRIMARY KEY Unique identification
FOREIGN KEY Link between tables
NOT NULL Column cannot be empty
UNIQUE No duplicate values
CHECK Restricts values
DEFAULT Assigns default value
CREATE TABLE STUDENT(
ID INT PRIMARY KEY,
NAME VARCHAR(20) NOT NULL,
EMAIL VARCHAR(30) UNIQUE,
AGE INT CHECK (AGE >=18),
CITY VARCHAR(20) DEFAULT 'VIJAYAWADA');