SQLite Projects |Projects on SQLite|SQLite Projects with Source code and Output

STUDENT MANAGEMENT SYSTEM

1. Create Student Table

create table student(Student_ID int primary key, Student_Name char, Gender char, age int, Phone_No char, Course char);

Insert Student Records:

Display All Students:

Search for a Student:

Update Student Information and Check the updated record:

Delete a student and View the records:

2. Create Marks Table

create table Marks(Student_ID int, Basic_Marks int, HTML_Marks int, SQL_Marks int, FOREIGN KEY(Student_ID) REFERENCES Student(Student_ID));

Insert Marks:

Display Student Marks:

select Student.Student_ID, Student.Student_Name, Marks.Basic_Marks,Marks.HTML_Marks,Marks.SQL_Marks from Student JOIN Marks ON Student.Student_ID=Marks.Student_ID;

Calculate Total and Average:

select Student.Student_ID, Student.Student_Name, (Basic_Marks+HTML_Marks+SQL_Marks) as Total_Marks, (Basic_Marks+HTML_Marks+SQL_Marks)/3 as Average_Marks from Student JOIN Marks ON Student.Student_ID=Marks.Student_ID;

Find Students with Average Above 80:

select Student.Student_Name, (Basic_Marks+HTML_Marks+SQL_Marks)/3 as Average_Marks from Student JOIN Marks ON Student.Student_ID=Marks.Student_ID where (Basic_Marks+HTML_Marks+SQL_Marks)/3>=80;

EMPLOYEE DATABASE SYSTEM

1. Create Department Table

CREATE TABLE Department (Department_ID INT PRIMARY KEY, Department_Name CHAR, Location CHAR);

Insert Department Records:

2. Create Employee Table

CREATE TABLE Employee (Employee_ID INT PRIMARY KEY, Employee_Name CHAR, Gender CHAR, Age INT, Phone CHAR, Email CHAR, Job_Title CHAR, Salary INT, Department_ID INT, FOREIGN KEY (Department_ID) REFERENCES Department(Department_ID));

Insert Employee Records:

Display All Employees:

Search an Employee:

Display Employee and Department Details:

SELECT Employee.Employee_ID, Employee.Employee_Name, Employee.Job_Title, Employee.Salary, Department.Department_Name, Department.Location FROM Employee JOIN Department ON Employee.Department_ID = Department.Department_ID;

Find Employees with Salary Greater Than 45,000:

SELECT Employee_Name, Job_Title, Salary FROM Employee WHERE Salary > 45000;

Find Average Employee Salary:

Find Highest Salary

SELECT MAX(Salary) AS Highest_Salary FROM Employee;

Find Lowest Salary:

SELECT MIN(Salary) AS Lowest_Salary FROM Employee;

Calculate Total Salary:

SELECT SUM(Salary) AS Total_Salary FROM Employee;

Count Employees:

SELECT COUNT(*) AS Total_Employees FROM Employee;

Count Employees in Each Department:

SELECT Department.Department_Name, COUNT(Employee.Employee_ID) AS Number_of_Employees FROM Department LEFT JOIN Employee ON Department.Department_ID = Employee.Department_ID GROUP BY Department.Department_Name;

Update Employee Information:

UPDATE Employee SET Salary = 50000 WHERE Employee_ID = 101;

Delete an Employee

DELETE FROM Employee WHERE Employee_ID = 104;

HOSPITAL MANAGEMENT SYSTEM

1. Create Patient Table

create table Patient(Patient_ID int primary key, Patient_Name char, Gender char, Age int, Address char, Phone char, Disease char);

Insert Patient Records:

2. Create Doctor Table

create table Doctor(Doctor_ID int Primary Key,Doctor_Name char,Specialization char, Phone char,Salary int);

Insert Doctor Records:

3. Create Appointment Table:

create table Appointment (Appointment_ID int Primary Key, Patient_ID int, Doctor_ID int, Appointment_Date date, Appointment_Time time, Status char, Foreign Key(Patient_ID) References Patient(Patient_ID), Foreign Key(Doctor_ID) References Doctor(Doctor_ID));

Insert Appointments:

4. Create Room Table

CREATE TABLE Room (Room_ID INT PRIMARY KEY, Room_Type CHAR, Room_Charge INT, Availability CHAR);

Insert Room Records:

5. Create Medicine Table

CREATE TABLE Medicine (Medicine_ID INT PRIMARY KEY, Medicine_Name CHAR, Price int, Quantity INT);

Insert Medicine Records:

6. Create Bill Table

CREATE TABLE Bill(Bill_ID INT PRIMARY KEY, Patient_ID INT, Room_Charge int, Doctor_Charge int, Medicine_Charge int, Total_Amount int, FOREIGN KEY (Patient_ID) REFERENCES Patient(Patient_ID));

Insert Bill Records:

Display All Patients:

Search a Patient:

Display All Doctors

Display Appointments

Display Patient and Doctor Details

SELECT Patient.Patient_ID, Patient.Patient_Name, Doctor.Doctor_Name, Doctor.Specialization, Appointment.Appointment_Date,Appointment.Appointment_Time,Appointment.Status FROM Appointment JOIN Patient ON Appointment.Patient_ID = Patient.Patient_ID JOIN Doctor ON Appointment.Doctor_ID = Doctor.Doctor_ID;

Display Patient Bills

SELECT Patient.Patient_Name, Bill.Room_Charge, Bill.Doctor_Charge, Bill.Medicine_Charge, Bill.Total_Amount FROM Bill JOIN Patient ON Bill.Patient_ID = Patient.Patient_ID;