Student Management Project
Database Name: student_management
Tables Overview
| S.No | Table Name | No. of Fields | Demo Data | |
|---|---|---|---|---|
| Create | Insert | |||
| 1 | attendance | 8 | Create | Insert |
| 2 | class | 5 | Create | Insert |
| 3 | fee | 9 | Create | Insert |
| 4 | proofs | 4 | Create | Insert |
| 5 | results | 8 | Create | Insert |
| 6 | scholarship | 5 | Create | Insert |
| 7 | sections | 4 | Create | Insert |
| 8 | students | 9 | Create | Insert |
| 9 | student_address | 8 | Create | Insert |
| 10 | student_parents | 9 | Create | Insert |
| 11 | subjects | 4 | Create | Insert |
Table Structure: attendance
| Field Name | Data Type |
|---|---|
| Id | int |
| class_id | varchar |
| section_id | varchar |
| student_id | varchar |
| date | date |
| attendance | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "attendance"
CREATE TABLE attendance (
id int,
class_id varchar(100),
section_id varchar(100),
student_id date,
date date,
attendance varchar(100),
created_at datetime ,
updated_at datetime );
id int,
class_id varchar(100),
section_id varchar(100),
student_id date,
date date,
attendance varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE attendance
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 8 records in "authors"
INSERT INTO attendance (id, class_id, section_id, student_id, date, attendance, created_at, updated_at) VALUES
(1, '1', '1', 's1', '2020-12-05', '1', '2020-12-12 13:24:39', '2020-12-12 13:24:39'),
( 2, '2', '2', 's2', '2020-12-05', '1', '2020-12-12 13:24:39', '2020-12-12 13:24:39' ),
( 3, '3', '3', 's3', '2020-12-05', '1', '2020-12-12 13:24:40', '2020-12-12 13:24:40' );
(1, '1', '1', 's1', '2020-12-05', '1', '2020-12-12 13:24:39', '2020-12-12 13:24:39'),
( 2, '2', '2', 's2', '2020-12-05', '1', '2020-12-12 13:24:39', '2020-12-12 13:24:39' ),
( 3, '3', '3', 's3', '2020-12-05', '1', '2020-12-12 13:24:40', '2020-12-12 13:24:40' );
Table Structure: class
| Field Name | Data Type |
|---|---|
| Id | int |
| class | varchar |
| section_id | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "class"
CREATE TABLE class (
id int,
class varchar(100),
section_id varchar(100),
created_at datetime ,
updated_at datetime );
id int,
class varchar(100),
section_id varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE class
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 5 records in "class"
INSERT INTO class (id, class, section_id, created_at, updated_at) VALUES
(1, '1st-class', '1', '2020-12-12 13:25:40', '2020-12-12 13:25:40'),
(2, '2nd-class', '2', '2020-12-12 13:25:40', '2020-12-12 13:25:40'),
(3, '3rd-class', '3', '2020-12-12 13:25:40', '2020-12-12 13:25:40');
(1, '1st-class', '1', '2020-12-12 13:25:40', '2020-12-12 13:25:40'),
(2, '2nd-class', '2', '2020-12-12 13:25:40', '2020-12-12 13:25:40'),
(3, '3rd-class', '3', '2020-12-12 13:25:40', '2020-12-12 13:25:40');
Table Structure: fee
| Field Name | Data Type |
|---|---|
| Id | int |
| class_id | varchar |
| section_id | varchar |
| student_id | varchar |
| date | date |
| fee | varchar |
| due | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "fee"
CREATE TABLE fee (
id int,
class_id varchar(100),
section_id varchar(100),
student_id varchar(100),
date date,
fee varchar(100),
due varchar(100),
created_at datetime ,
updated_at datetime );
id int,
class_id varchar(100),
section_id varchar(100),
student_id varchar(100),
date date,
fee varchar(100),
due varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE fee
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 9 records in "fee"
INSERT INTO fee (id, class_id, section_id, student_id, date, fee, due, created_at, updated_at) VALUES
(1, '1', '1', 's1', '2020-10-05', '500', '', '2020-12-12 13:27:00', '2020-12-12 13:27:00'),
(2, '2', '2', 's2', '2020-10-05', '500', '', '2020-12-12 13:27:00', '2020-12-12 13:27:00'),
(3, '3', '3', 's3', '2020-10-05', '500', '', '2020-12-12 13:27:00', '2020-12-12 13:27:00');
(1, '1', '1', 's1', '2020-10-05', '500', '', '2020-12-12 13:27:00', '2020-12-12 13:27:00'),
(2, '2', '2', 's2', '2020-10-05', '500', '', '2020-12-12 13:27:00', '2020-12-12 13:27:00'),
(3, '3', '3', 's3', '2020-10-05', '500', '', '2020-12-12 13:27:00', '2020-12-12 13:27:00');
Table Structure: proofs
| Field name | Data type |
|---|---|
| Id | int |
| proofs | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "proofs"
CREATE TABLE proofs (
id int,
proofs varchar(100),
created_at datetime ,
updated_at datetime );
id int,
proofs varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE proofs
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 4 records in "proofs"
INSERT INTO proofs (id, proofs, created_at, updated_at) VALUES
(1, 'Aadhar', '2020-12-12 13:28:08', '2020-12-12 13:28:08'),
(2, 'caste', '2020-12-12 13:28:08', '2020-12-12 13:28:08'),
(3, 'income', '2020-12-12 13:28:08', '2020-12-12 13:28:08');
(1, 'Aadhar', '2020-12-12 13:28:08', '2020-12-12 13:28:08'),
(2, 'caste', '2020-12-12 13:28:08', '2020-12-12 13:28:08'),
(3, 'income', '2020-12-12 13:28:08', '2020-12-12 13:28:08');
Table Structure: results
| Field name | Data type |
|---|---|
| Id | int |
| class_id | varchar |
| section_id | varchar |
| student_id | varchar |
| subject_id | varchar |
| marks | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "results"
CREATE TABLE results (
id int,
class_id varchar(100),
section_id date,
student_id varchar(100),
subject_id varchar(100),
marks varchar(100),
created_at datetime ,
updated_at datetime );
id int,
class_id varchar(100),
section_id date,
student_id varchar(100),
subject_id varchar(100),
marks varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE results
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 8 records in "results"
INSERT INTO results (id, class_id, section_id, student_id, subject_id, marks, created_at, updated_at) VALUES
(1, '1', '1', 's1', '1', '52', '2020-12-12 14:32:16', '2020-12-12 14:32:16'),
(2, '2', '2', 's2', '2', '63', '2020-12-12 14:32:16', '2020-12-12 14:32:16'),
(3, '3', '3', 's3', '3', '72', '2020-12-12 14:32:16', '2020-12-12 14:32:16');
(1, '1', '1', 's1', '1', '52', '2020-12-12 14:32:16', '2020-12-12 14:32:16'),
(2, '2', '2', 's2', '2', '63', '2020-12-12 14:32:16', '2020-12-12 14:32:16'),
(3, '3', '3', 's3', '3', '72', '2020-12-12 14:32:16', '2020-12-12 14:32:16');
Table Structure: scholarship
| Field name | Data type |
|---|---|
| Id | int |
| scholarship_name | varchar |
| student_id | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "scholarship"
CREATE TABLE scholarship (
id int,
scholarship_name varchar(100),
student_id varchar(100),
created_at datetime ,
updated_at datetime );
id int,
scholarship_name varchar(100),
student_id varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE scholarship
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 5 records in "scholarship"
INSERT INTO scholarship (id, scholarship_name, student_id, created_at, updated_at) VALUES
(1, 'G. P. Birla Scholarship', 's1', '2020-12-12 14:35:11', '2020-12-12 14:35:11'),
(2, 'CLP India Scholarship Scheme', 's2', '2020-12-12 14:35:11', '2020-12-12 14:35:11'),
(3, 'Sitaram Jindal Foundation Scholarship Scheme', 's3', '2020-12-12 14:35:11', '2020-12-12 14:35:11');
(1, 'G. P. Birla Scholarship', 's1', '2020-12-12 14:35:11', '2020-12-12 14:35:11'),
(2, 'CLP India Scholarship Scheme', 's2', '2020-12-12 14:35:11', '2020-12-12 14:35:11'),
(3, 'Sitaram Jindal Foundation Scholarship Scheme', 's3', '2020-12-12 14:35:11', '2020-12-12 14:35:11');
Table Structure: sections
| Field name | Data type |
|---|---|
| Id | int |
| section | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "sections"
CREATE TABLE sections (
id int,
section varchar(100),
created_at datetime ,
updated_at datetime );
id int,
section varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE sections
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 4 records in "sections"
INSERT INTO INTO sections (id, section, created_at, updated_at) VALUES
(1, 'A', '2020-12-12 14:35:48', '2020-12-12 14:35:48'),
(2, 'B', '2020-12-12 14:35:48', '2020-12-12 14:35:48'),
(3, 'C', '2020-12-12 14:35:48', '2020-12-12 14:35:48');
(1, 'A', '2020-12-12 14:35:48', '2020-12-12 14:35:48'),
(2, 'B', '2020-12-12 14:35:48', '2020-12-12 14:35:48'),
(3, 'C', '2020-12-12 14:35:48', '2020-12-12 14:35:48');
Table Structure: students
| Field name | Data type |
|---|---|
| Id | int |
| class_id | varchar |
| section_id | varchar |
| student_id | varchar |
| name | varchar |
| admission_year | date |
| admission_fee | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "students"
CREATE TABLE students (
id int,
class varchar(100),
section_id varchar(100),
student_id varchar(100),
name varchar(100),
admission_year date,
admission_fee varchar(100),
created_at datetime ,
updated_at datetime );
id int,
class varchar(100),
section_id varchar(100),
student_id varchar(100),
name varchar(100),
admission_year date,
admission_fee varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE students
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 9 records in "students"
INSERT INTO students (id, class, section_id, student_id, name, admission_year, admission_fee, created_at, updated_at) VALUES
(1, '1', '1', 's1', 'Akhilesh', '0000-00-00', '5000', '2020-12-12 14:39:44', '2020-12-12 14:39:44'),
(2, '2', '2', 's2', 'Akshay', '0000-00-00', '6000', '2020-12-12 14:39:44', '2020-12-12 14:39:44'),
(3, '3', '3', 's3', 'Aman', '0000-00-00', '5500', '2020-12-12 14:39:44', '2020-12-12 14:39:44');
(1, '1', '1', 's1', 'Akhilesh', '0000-00-00', '5000', '2020-12-12 14:39:44', '2020-12-12 14:39:44'),
(2, '2', '2', 's2', 'Akshay', '0000-00-00', '6000', '2020-12-12 14:39:44', '2020-12-12 14:39:44'),
(3, '3', '3', 's3', 'Aman', '0000-00-00', '5500', '2020-12-12 14:39:44', '2020-12-12 14:39:44');
Table Structure: student_address
| Field name | Data type |
|---|---|
| Id | int |
| student_id | Varchar |
| address | Varchar |
| mobile | Varchar |
| alternate_mobile | Varchar |
| proof | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "student_address"
CREATE TABLE student_address (
id int,
student_id varchar(100),
address varchar(100),
mobile varchar(100),
alternate_mobile varchar(100),
proof varchar(100),
created_at datetime ,
updated_at datetime );
id int,
student_id varchar(100),
address varchar(100),
mobile varchar(100),
alternate_mobile varchar(100),
proof varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE student_address
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 8 records in "student_address"
INSERT INTO student_address (id, student_id, address, mobile, alternate_mobile, proof, created_at, updated_at) VALUES
(1, 's1', 'delhi', '2587413690', '5874213695', '1', '2020-12-12 14:42:27', '2020-12-12 14:42:27'),
(2, 's2', 'gujarat', '8965412874', '8563475125', '2', '2020-12-12 14:42:27', '2020-12-12 14:42:27'),
(3, 's3', 'tamilnadu', '3571596421', '9654127835', '3', '2020-12-12 14:42:27', '2020-12-12 14:42:27');
(1, 's1', 'delhi', '2587413690', '5874213695', '1', '2020-12-12 14:42:27', '2020-12-12 14:42:27'),
(2, 's2', 'gujarat', '8965412874', '8563475125', '2', '2020-12-12 14:42:27', '2020-12-12 14:42:27'),
(3, 's3', 'tamilnadu', '3571596421', '9654127835', '3', '2020-12-12 14:42:27', '2020-12-12 14:42:27');
Table Structure: student_parents
| Field name | Data type |
|---|---|
| Id | int |
| student_id | varchar |
| father_name | date |
| mother_name | varchar |
| gaurdian_name | varchar |
| father_designation | varchar |
| mother_designation | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "student_parents"
CREATE TABLE student_parents (
id int,
student_id varchar(100),
father_name varchar(100),
mother_name varchar(100),
gaurdian_name varchar(100),
father_designation varchar(100),
mother_designation varchar(100),
created_at datetime ,
updated_at datetime );
id int,
student_id varchar(100),
father_name varchar(100),
mother_name varchar(100),
gaurdian_name varchar(100),
father_designation varchar(100),
mother_designation varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE student_parents
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 9 records in "student_parents"
INSERT INTO student_parents (id, student_id, father_name, mother_name, gaurdian_name, father_designation, mother_designation, created_at, updated_at) VALUES
(1, 's1', 'shafi', 'sheela', 'shafi', 'engineer', 'housewife', '2020-12-12 14:48:08', '2020-12-12 14:48:08'),
(2, 's2', 'mustafa', 'khaira', 'mustafa', 'bussiness', 'housewife', '2020-12-12 14:48:08', '2020-12-12 14:48:08'),
(3, 's3', 'chapal', 'nandini', 'chapal', 'teacher', 'housewife', '2020-12-12 14:48:08', '2020-12-12 14:48:08');
(1, 's1', 'shafi', 'sheela', 'shafi', 'engineer', 'housewife', '2020-12-12 14:48:08', '2020-12-12 14:48:08'),
(2, 's2', 'mustafa', 'khaira', 'mustafa', 'bussiness', 'housewife', '2020-12-12 14:48:08', '2020-12-12 14:48:08'),
(3, 's3', 'chapal', 'nandini', 'chapal', 'teacher', 'housewife', '2020-12-12 14:48:08', '2020-12-12 14:48:08');
Table Structure: subjects
| Field name | Data type |
|---|---|
| Id | int |
| subject | varchar |
| created_at | datetime |
| updated_at | datetime |
Following SQL creates a new table called "subjects"
CREATE TABLE subjects (
id int,
subject varchar(100),
created_at datetime ,
updated_at datetime );
id int,
subject varchar(100),
created_at datetime ,
updated_at datetime );
Following syntax altering the privileges
ALTER TABLE subjects
ADD PRIMARY KEY (`id`);
ADD PRIMARY KEY (`id`);
Following SQL inserts 4 records in "subjects"
INSERT INTO subjects (id, subject, created_at, updated_at) VALUES
(1, 'English', '2020-12-12 14:48:47', '2020-12-12 14:48:47'),
(2, 'Mathematics', '2020-12-12 14:48:48', '2020-12-12 14:48:48'),
(3, 'Social', '2020-12-12 14:48:48', '2020-12-12 14:48:48');
(1, 'English', '2020-12-12 14:48:47', '2020-12-12 14:48:47'),
(2, 'Mathematics', '2020-12-12 14:48:48', '2020-12-12 14:48:48'),
(3, 'Social', '2020-12-12 14:48:48', '2020-12-12 14:48:48');
Questions
1. Display the attendance based on date?
A. SELECT ad.id,s.name,ad.date from attendance ad left join students s on s.student_id=ad.student_id
2. Display the fee based on student id?
A. SELECT* FROM `fee`
3. Display the fee due based on student id?
A. SELECT f.due from fee f where student_id="s1"
4. Display the subjects in our system?
A. SELECT * FROM `subjects`
5. Dsiplay the date and time of the student records?
A. SELECT * FROM `students` where DATE(created_at) = DATE('2020-12-12')
6. What are the proofs the student submitted?
A. SELECTsd.id,sd.student_id,sd.address,sd.mobile,sd.alternate_mobile,pf.proofs from student_address sd left join proofs pf on sd.proof=pf.id
7. In which class the student is in?
A. SELECT s.id,c.class,s.student_id,s.name FROM students s left join class c on s.class=c.id
8. In which section the student is in?
A. SELECT s.id,c.class,sc.section,s.student_id,s.name FROM students s left join class c on s.class=c.id left join sections sc on s.section_id=sc.id
9. Display the result of a particular subject based on student id?
A. SELECT r.id,c.class,r.section_id,r.student_id,r.marks from results r left join class c on r.class_id=c.id where student_id="s1"
10. Display the admission fee?
A. SELECT s.name,s.admission_fee from students s
11. Display the student details completely?
A. SELECT s.id,s.class,s.section_id,s.student_id,s.name,s.admission_year,s.admission_fee,sd.address,sd.mobile,sd.
alternate_mobile,sd.proof,sp.father_name,sp.mother_name,sp.gaurdian_name from students s left join student_address sd on s.student_id=sd.student_id left join student_parents sp on s.student_id=sp.student_id
alternate_mobile,sd.proof,sp.father_name,sp.mother_name,sp.gaurdian_name from students s left join student_address sd on s.student_id=sd.student_id left join student_parents sp on s.student_id=sp.student_id
12. Display the parent details of the student?
A. SELECT s.id,s.name,sp.father_name,sp.mother_name,sp.gaurdian_name,sp.father_designation,sp.mother_designation from students s LEFT join student_parents sp on s.student_id=sp.student_id
13. Display the scholarship name?
A. SELECT* FROM `scholarship`
14. Display the father designation based on student id?
A. SELECT s.id,s.name,sp.father_name,sp.mother_name,sp.gaurdian_name,sp.father_designation,sp.mother_designation from students s LEFT join student_parents sp on s.student_id=sp.student_id
15. If a student submitted proofs, display the submitted proof name?
A. SELECT sa.id,s.name,sa.address,sa.mobile,sa.alternate_mobile,p.proofs from student_address sa LEFT join proofs p on sa.proof=p.id left join students s on s.student_id=sa.student_id
16. Display the results based on student id?
A. SELECT r.id,c.class,sc.section,s.name,sb.subject,r.marks from results r left join class c on r.class_id=c.id left join sections sc on r.section_id=sc.id left join students s on r.student_id=s.student_id left join subjects sb on r.subject_id=sb.id
17. Display the guardian name of the student?
A. SELECT s.id,s.name,sp.gaurdian_name FROM students s left join student_parents sp on s.student_id=sp.student_id
