Scribd
Upload
22 views6 pages

RDBMS PR 08 SQL 229311136

The document describes the creation of a database called AttendanceManagementSystem with tables for Users, Classes, and Attendance. Users include administrators, teachers, and students. Attendance records include the user, class, date, and status. The tables are joined to create a view called AttendanceDetails that displays attendance records. Sample data is inserted and various queries are demonstrated to select, update, delete and manage the attendance data.

Uploaded by

Shreya Singh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
22 views6 pages

RDBMS PR 08 SQL 229311136

The document describes the creation of a database called AttendanceManagementSystem with tables for Users, Classes, and Attendance. Users include administrators, teachers, and students. Attendance records include the user, class, date, and status. The tables are joined to create a view called AttendanceDetails that displays attendance records. Sample data is inserted and various queries are demonstrated to select, update, delete and manage the attendance data.

Uploaded by

Shreya Singh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 6

CREATE DATABASE AttendanceManagementSystem;

USE AttendanceManagementSystem;

CREATE TABLE Users (


UserID INT PRIMARY KEY,
UserName VARCHAR(255) NOT NULL,
UserType VARCHAR(50) NOT NULL --
'Administrator', 'Teacher', 'Student', etc.
);

CREATE TABLE Classes (


ClassID INT PRIMARY KEY,
ClassName VARCHAR(50) NOT NULL
);

CREATE TABLE Attendance (


AttendanceID INT PRIMARY KEY,
UserID INT,
ClassID INT,
AttendanceDate DATE,
Status VARCHAR(10));

CREATE VIEW AttendanceDetails AS


SELECT A.AttendanceID, U.UserName,
C.ClassName, A.AttendanceDate, A.Status
FROM Attendance A
JOIN Users U ON A.UserID = U.UserID
JOIN Classes C ON A.ClassID = C.ClassID;
INSERT INTO Users (UserID, UserName,
UserType) VALUES
(1, 'Admin', 'Administrator'),
(2, 'Teacher1', 'Teacher'),
(3, 'Student1', 'Student');

INSERT INTO Classes (ClassID, ClassName)


VALUES
(1, 'Math101'),
(2, 'English101');

INSERT INTO Attendance (AttendanceID,


UserID, ClassID, AttendanceDate, Status)
VALUES
(1, 3, 1, '2023-01-01', 'Present'),
(2, 3, 1, '2023-01-02', 'Absent'),
(3, 3, 2, '2023-01-01', 'Present'),
(4, 3, 2, '2023-01-02', 'Absent');

SELECT * FROM AttendanceDetails WHERE


UserName = 'Student1';
UPDATE Attendance SET Status = 'Present'
WHERE UserID = 3 AND AttendanceDate = '2023-
01-02';

SELECT * FROM AttendanceDetails WHERE


ClassName = 'Math101';

SELECT * FROM AttendanceDetails WHERE


AttendanceDate = '2023-01-01';
-- DROP TABLE Attendance;

-- DROP TABLE Classes;

-- DROP TABLE Users;

-- DROP VIEW AttendanceDetails;

INSERT INTO Users (UserID, UserName,


UserType) VALUES
(4, 'Student2', 'Student');
-- Insert a new class
INSERT INTO Classes (ClassID, ClassName)
VALUES
(3, 'Science101');

INSERT INTO Attendance (AttendanceID,


UserID, ClassID, AttendanceDate, Status)
VALUES
(5, 4, 3, '2023-01-03', 'Present');

INSERT INTO Attendance (AttendanceID,


UserID, ClassID, AttendanceDate, Status)
VALUES
(5, 4, 3, '2023-01-03', 'Present');

INSERT INTO Attendance (AttendanceID,


UserID, ClassID, AttendanceDate, Status)
VALUES
(5, 4, 3, '2023-01-03', 'Present');
SELECT * FROM AttendanceDetails;

-- View attendance details for a specific


user (e.g., Student1)

SELECT * FROM AttendanceDetails WHERE


UserName = 'Student1';

DELETE FROM Classes WHERE ClassID = 3;

SELECT * FROM Classes;

SELECT * FROM AttendanceDetails WHERE


AttendanceDate = '2023-01-01';

SELECT DISTINCT U.UserID, U.UserName


FROM Users U
LEFT JOIN Attendance A ON U.UserID =
A.UserID
WHERE A.Status IS NULL OR A.Status =
'Absent';

DELETE FROM Users WHERE UserID = 4;

DELETE FROM Attendance WHERE UserID = 4;

UPDATE Users SET UserName = 'Admin1' WHERE


UserID = 1;
UPDATE Users SET UserName =
'Teacher1_updated' WHERE UserID = 2;
INSERT INTO Attendance (AttendanceID,
UserID, ClassID, AttendanceDate, Status)
VALUES
(6, 3, 1, '2023-01-04', 'Present'),
(7, 4, 1, '2023-01-04', 'Absent');

UPDATE Users SET UserType = 'Admin' WHERE


UserID = 1;

UPDATE Users SET UserType = 'VIP Teacher'


WHERE UserID = 2;

SELECT * FROM AttendanceDetails WHERE


UserType = 'Student';

DELETE FROM Attendance WHERE UserID = 3;

UPDATE Users SET UserType = 'Super Teacher'


WHERE UserType = 'Teacher';

UPDATE Users SET UserType = 'Super Student'


WHERE UserType = 'Student';

UPDATE Classes SET ClassName =


'Mathematics101' WHERE ClassID = 2;
UPDATE Users SET UserType = 'Mega Admin'
WHERE UserType = 'Power Admin';

DELETE FROM Attendance WHERE AttendanceDate


= '2023-01-01';

DELETE FROM Attendance WHERE UserID = 2 AND


ClassID = 1;

You might also like