SQL DATABASE PROJECT
Name: ___________________________
Class: ___________________________
Roll No: _________________________
School: __________________________
SQL DATABASE PROJECT
Index
1. Introduction
2. Table Creation
3. Data Insertion
4. Queries with Functions
5. SQL Data Types
6. CHAR vs VARCHAR Comparison
7. Conclusion
8. Bibliography
9. Program Screenshots
SQL DATABASE PROJECT
Introduction
Structured Query Language (SQL) is used to store, manipulate and retrieve data in
databases. In this project, three tables (STUDENT, EMPLOYEE, and ITEMS) are created.
Different SQL functions are applied to demonstrate Maths, Text, Date/Time, and
Aggregate operations. The project also explains SQL data types and presents a
comparison between CHAR and VARCHAR.
Q1 create at least 3 tables: 1. STUDENT, [Link], [Link] Choose
the appropriate columns and data types
Table Creation
1
CREATE TABLE STUDENT (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Class VARCHAR(20),
DateOfBirth DATE,
AdmissionDate DATE);
2
CREATE TABLE EMPLOYEE (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Position VARCHAR(50),
Department VARCHAR(50),
DateOfJoining DATE,
Salary DECIMAL(10,2),
ContactNumber VARCHAR(15));
3
CREATE TABLE ITEMS (
ItemID INT PRIMARY KEY,
ItemName VARCHAR(50),
Price DECIMAL(10,2),
Quantity INT,
MfgDate DATE);
SQL DATABASE PROJECT
➢ Program1
➢ Program2
➢ Program 3
SQL DATABASE PROJECT
Q2 Insert records (minimum 5 in each table)
Data Insertion
1
INSERT INTO STUDENT VALUES
(1,'Amit','Sharma','CSE','2002-05-10','2022-08-15'),
(2,'Neha','Singh','ECE','2001-07-22','2021-07-10'),
(3,'Rahul','Mehta','ME','2003-01-05','2023-01-12'),
(4,'Priya','Verma','CSE','2000-09-20','2020-09-05'),
(5,'Arjun','Das','EEE','2002-11-10','2022-11-25');
INSERT INTO EMPLOYEE VALUES
(101,'Ravi','Kumar','Manager','Admin','2020-06-01',45000.00,'9876543210'),
(102,'Sneha','Roy','Clerk','Accounts','2021-03-15',30000.00,'9123456789'),
(103,'Vikas','Gupta','Developer','IT','2019-09-10',55000.00,'9988776655'),
(104,'Meera','Nair','HR','HR','2022-02-01',40000.00,'9911223344'),
(105,'Anil','Yadav','Team Lead','IT','2018-12-20',60000.00,'8899776655');
INSERT INTO ITEMS VALUES
(201,'Laptop',55000.00,10,'2022-05-01'),
(202,'Mobile',15000.00,25,'2023-01-10'),
(203,'Headphones',2000.00,50,'2023-03-22'),
SQL DATABASE PROJECT
(204,'Keyboard',1200.00,40,'2022-11-18'),
(205,'Mouse',800.00,60,'2022-12-05');
SQL DATABASE PROJECT
➢ Program 1
➢ Program 2
➢ Program 3
SQL DATABASE PROJECT
Q3
Write the SQL queries using the following functions
a. Maths function
b. Text functions
c. Date and Time functions
d. Aggregate functions
Queries with Functions
Maths Functions
[Link] AVG(Salary) AS AverageSalary FROM EMPLOYEE;
2. SELECT ROUND(Salary, -3) AS RoundedSalary FROM EMPLOYEE;
3. SELECT MAX(Price) AS HighestPrice FROM ITEMS;;
4. SELECT MIN(Price) AS LowestPrice FROM ITEMS;
5. SELECT ItemID, Quantity, MOD(Quantity, 2) AS EvenOdd FROM ITEMS;
Text Functions
1. SELECT UPPER(FirstName) FROM STUDENT;
2. SELECT LOWER(LastName) FROM EMPLOYEE;
3. SELECT ItemName, LENGTH(ItemName) AS NameLength FROM ITEMS;
4. SELECT SUBSTRING(FirstName,1,3) FROM STUDENT;
5. SELECT CONCAT(FirstName,' ',LastName,' - ',Position) AS EmployeeDetails FROM
EMPLOYEE;
SQL DATABASE PROJECT
Date and Time Functions
1. SELECT DATE(NOW()) AS TodayDate;
2 SELECT MONTH(NOW()) AS CurrentMonth;
3. SELECT YEAR(NOW()) AS CurrentYear;
4. SELECT DAYNAME(NOW()) AS TodayDay;
5. SELECT DAYOFMONTH(NOW()) AS TodayDateNo;
Aggregate Functions
1. SELECT COUNT(*) AS TotalStudents FROM STUDENT;
2. SELECT AVG(Salary) AS AvgSalary FROM EMPLOYEE;
3. SELECT SUM(Quantity) AS TotalStock FROM ITEMS;
4. SELECT MAX(Salary) AS MaxSalary FROM EMPLOYEE;
5. SELECT MIN(Price) AS MinPrice FROM ITEMS;
Q4
SQL uses different data types to store the data. Write down in detail
about each data type with examples. (A comparison table to
represent the difference between CHAR & VARCHAR
SQL Data Types
1. Numeric Data Types: INT, DECIMAL, FLOAT, DOUBLE
Example: Salary DECIMAL(10,2)
2. Character/String Data Types: CHAR, VARCHAR, TEXT
Example: FirstName VARCHAR(50)
3. Date/Time Data Types: DATE, TIME, DATETIME, TIMESTAMP
Example: AdmissionDate DATE
4. Large Object Data Types: BLOB (Binary Large Object), CLOB (Character Large Object)
Example: Images stored in BLOB
5. Boolean Data Types: TRUE/FALSE
SQL DATABASE PROJECT
CHAR vs VARCHAR Comparison
Feature CHAR(n) VARCHAR(n)
Storage Fixed length Variable length
Speed Faster for fixed size Slightly slower
Padding Pads spaces to full length Stores only actual length
Example CHAR(10) → 'A ' VARCHAR(10) → 'A'
Conclusion
This project demonstrates the creation of tables, insertion of records, and use of SQL
functions to manipulate and analyze data. It also explains SQL data types and compares
CHAR with VARCHAR. Thus, it provides a complete understanding of basic SQL
operations.
Bibliography
1. NCERT Informatics Practices
Textbook
2. [Link]/sql/
3. [Link]/sql-
tutorial/