SQLize Online / PHPize Online  /  SQLtest Online

-- # students table creation CREATE TABLE students( id INT PRIMARY KEY, first_name VARCHAR(15) NOT NULL, last_name VARCHAR(15) NOT NULL, standard INT CHECK (standard BETWEEN 1 AND 12), percentage DECIMAL(5,2) CHECK(percentage BETWEEN 0.00 AND 100.00), interest VARCHAR(20) DEFAULT 'None' ); -- # data insertion in students table INSERT INTO students (id, first_name, last_name, standard, percentage, interest) VALUES (1, 'Hasti', 'Patel', 10, 90.00, 'Science'), (2, 'Aakash', 'Shah', 11, 82.08, 'Music'), (3, 'Ankita', 'Ramna', 9, 98.23, 'History'), (4, 'Yashraj', 'Gohil', 12, 75.48, 'Football'), (5, 'Nirbhay', 'Tejani', 10, 88.50, 'Robotics'), (6, 'Drashti', 'Chawda', 9, 50.00, 'Drawing'), (7, 'Harsh', 'Tarsariya', 5, 62.08, 'Sports'); -- # student_attendance table craetion CREATE TABLE student_attendances ( id INT PRIMARY KEY, student_id INT NOT NULL, created_at TIMESTAMP, attendance_status BIT DEFAULT 0, FOREIGN KEY (student_id) REFERENCES students(id) ); -- # insertion of data into student_attendance table INSERT INTO student_attendances (id, student_id, created_at, attendance_status) VALUES (1, 1, '2024-01-01 08:00:00', 1), (2, 1, '2024-02-02 08:00:00', 0), (3, 2, '2024-01-01 08:00:00', 0), (4, 3, '2024-01-01 08:00:00', 0), (5, 4, '2024-01-03 08:00:00', 1), (6, 3, '2024-03-01 08:00:00', 1), (7, 2, '2024-01-05 08:00:00', 1), (8, 4, '2024-01-01 08:00:00', 0), (9, 5, '2024-01-21 08:00:00', 0), (10, 5, '2024-01-01 08:00:00', 1), (11, 6, '2024-01-01 08:00:00', 0), (12, 6, '2024-01-05 08:00:00', 1), (13, 7, '2024-01-01 08:00:00', 1); -- # creation of teachers table CREATE TABLE teachers ( id INT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, subject VARCHAR(100) NOT NULL, interests VARCHAR(200), email VARCHAR(100) UNIQUE NOT NULL CHECK(email LIKE '%@%.%'), phone_number VARCHAR(20) CHECK (LENGTH(phone_number) = 10) ); -- # insertion of data into teachers table INSERT INTO teachers (id, first_name, last_name, subject, interests, email, phone_number) VALUES (1, 'Vishal', 'Andodariya', 'DBMS', 'Hiking, Reading', 'vishal2@gec.com', '9878987898'), (2, 'Kamlesh', 'Makwana', 'Constitution', 'Cooking, Traveling', 'mkamlesh@gec.com', '7485965847'), (3, 'Hardi', 'Sanghvi', 'English', 'Writing, Music', 'hardis@spei.com', '7885569874'), (4, 'Urvashi', 'Solanki', 'History', 'Gardening, Painting', 'uls23@spei.com', '7890123456'), (5, 'Himanshu', 'Shrivastava', 'ETC', 'Coding, Gaming', 'hsofficial@gec.com', '9012345678'); -- # table creation of teacher_attendance CREATE TABLE teachers_attendances ( id INT PRIMARY KEY, teacher_id INT NOT NULL, created_at TIMESTAMP, attendance_status BIT DEFAULT 0, FOREIGN KEY (teacher_id) REFERENCES teachers(id) ); -- # data insertion in teacher_attendance table INSERT INTO teachers_attendances (id, teacher_id, created_at, attendance_status) VALUES (1, 1, '2024-01-01 08:00:00', 1), (2, 3, '2024-01-22 08:00:00', 1), (3, 2, '2024-02-14 08:00:00', 0), (4, 1, '2024-04-01 08:00:00', 0), (5, 5, '2024-01-21 08:00:00', 1), (6, 2, '2024-01-01 08:00:00', 0), (7, 4, '2024-01-01 08:00:00', 0), (8, 3, '2024-06-03 08:00:00', 0); -- # Find the student's presence/absence on a particular day -- # All students attendance on particular day SELECT s.first_name, s.last_name, s.standard, sa.attendance_status AS Attendance, sa.created_at AS Day FROM students s JOIN student_attendances sa ON s.id = sa.student_id WHERE sa.created_at LIKE '2024-01-05%'; -- # Particular student’s attendance on particular day SELECT s.first_name, s.last_name, s.standard, sa.attendance_status AS Attendance, sa.created_at AS Day FROM students s JOIN student_attendances sa ON s.id = sa.student_id WHERE sa.created_at LIKE '2024-01-01%'AND s.id=3; -- # Find the total absence/presence of every student SELECT s.first_name, s.last_name, SUM(CASE WHEN sa.attendance_status = 1 THEN 1 ELSE 0 END) AS total_presents, SUM(CASE WHEN sa.attendance_status = 0 THEN 1 ELSE 0 END) AS total_absents FROM students s JOIN student_attendances sa ON s.id = sa.student_id GROUP BY s.id; -- # Find absent students with a percentage lower than 70. SELECT s.first_name, s.last_name, s.percentage, sa.attendance_status AS attendance, sa.created_at AS day FROM students s JOIN student_attendances sa ON s.id = sa.student_id WHERE sa.attendance_status = 0 AND s.percentage < 70; -- # Find a student who has the highest presence SELECT s.first_name, s.last_name, s.standard, COUNT(sa.id) AS total_presents FROM students s INNER JOIN student_attendances sa ON s.id = sa.student_id WHERE sa.attendance_status = 1 GROUP BY s.id ORDER BY total_presents DESC LIMIT 1; -- # Get all students and teachers first_name, last_name, full_name, interest, standard, subject and total absence. -- # Student(first name,last name,full name,interest,standard,total absence): SELECT s.first_name as s_first_name, s.last_name as s_last_name, (s.first_name||" "||s.last_name )AS s_full_name, s.standard as s_standard, s.interest as s_interest, count(sa.id) as total_absance FROM students s JOIN student_attendances sa ON s.id = sa.student_id WHERE sa.attendance_status = 0 GROUP BY s.id; -- # Teachers(first name,last name,full name,interests,total absence): SELECT t.first_name as t_first_name, t.last_name as t_last_name, (t.first_name||" "||t.last_name )AS t_full_name, t.subject as t_subject, t.interests as t_interests, count(ta.id) as t_total_absance FROM teachers t JOIN teachers_attendances ta ON t.id = ta.teacher_id WHERE ta.attendance_status = 0 GROUP BY t.id; -- # Union of both students and teachers on common fields SELECT t.first_name, t.last_name, (t.first_name||" "||t.last_name )AS full_name, t.interests, count(ta.id) as total_absance FROM teachers t JOIN teachers_attendances ta ON t.id = ta.teacher_id WHERE ta.attendance_status = 0 GROUP BY t.id union SELECT s.first_name, s.last_name, concat(s.first_name,s.last_name )AS full_name, s.interest, count(sa.id) as total_absance FROM students s JOIN student_attendances sa ON s.id = sa.student_id WHERE sa.attendance_status = 0 GROUP BY s.id;