Scribd
Upload
25 views7 pages

Query

The document outlines SQL commands for modifying and creating database tables related to staff, faculty, students, subjects, and classrooms. It includes alterations to existing tables, creation of new tables with specified attributes, and insertion of sample data into these tables. Additionally, it provides various SELECT queries for retrieving information based on specific conditions.

Uploaded by

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

Query

The document outlines SQL commands for modifying and creating database tables related to staff, faculty, students, subjects, and classrooms. It includes alterations to existing tables, creation of new tables with specified attributes, and insertion of sample data into these tables. Additionally, it provides various SELECT queries for retrieving information based on specific conditions.

Uploaded by

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

ALTER TABLE STAFF

RENAME COLUMN DATEHIRED TO


"joining date";

ALTER TABLE SUBJECTS


DROP COLUMN SUBJECTDESCRIPTION;

-- 1. FACULTY
CREATE TABLE Faculty (
StaffID INT PRIMARY KEY,
Title VARCHAR(50),
Status VARCHAR(20),
Tenured BOOLEAN
);

-- 2. STAFF
CREATE TABLE Staff (
StaffID INT PRIMARY KEY,
StFirstName VARCHAR(50),
StLastName VARCHAR(50),
StStreetAddress VARCHAR(100),
StCity VARCHAR(50),
StState VARCHAR(50),
StZipCode VARCHAR(10),
StAreaCode VARCHAR(10),
StPhoneNumber VARCHAR(15),
DateHired DATE,
Salary DECIMAL(10, 2),
Position VARCHAR(50)
);

-- 3. FACULTY_CATEGORIES
CREATE TABLE Faculty_Categories (
StaffID INT,
CategoryID INT,
PRIMARY KEY (StaffID, CategoryID),
FOREIGN KEY (StaffID) REFERENCES Staff(StaffID),
FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

-- 4. FACULTY_CLASSES
CREATE TABLE Faculty_Classes (
StaffID INT,
ClassID INT,
PRIMARY KEY (StaffID, ClassID),
FOREIGN KEY (StaffID) REFERENCES Faculty(StaffID),
FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
);

-- 5. FACULTY_SUBJECTS
CREATE TABLE Faculty_Subjects (
StaffID INT,
SubjectID INT,
ProficiencyRating INT,
PRIMARY KEY (StaffID, SubjectID),
FOREIGN KEY (StaffID) REFERENCES Faculty(StaffID),
FOREIGN KEY (SubjectID) REFERENCES Subjects(SubjectID)
);

-- 6. CLASSES
CREATE TABLE Classes (
ClassID INT PRIMARY KEY,
SubjectID INT,
ClassRoomID INT,
StartTime TIME,
Duration INT,
FOREIGN KEY (SubjectID) REFERENCES Subjects(SubjectID),
FOREIGN KEY (ClassRoomID) REFERENCES Classrooms(ClassRoomID)
);

-- 7. STUDENT_SCHEDULES
CREATE TABLE Student_Schedules (
ClassID INT,
StudentID INT,
ClassStatus VARCHAR(10),
Grade VARCHAR(5),
PRIMARY KEY (ClassID, StudentID, ClassStatus),
FOREIGN KEY (ClassID) REFERENCES Classes(ClassID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (ClassStatus) REFERENCES Student_Class_Status(ClassStatus)
);

-- 8. STUDENTS
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudFirstName VARCHAR(50),
StudLastName VARCHAR(50),
StudStreetAddress VARCHAR(100),
StudCity VARCHAR(50),
StudState VARCHAR(50),
StudZipCode VARCHAR(10),
StudAreaCode VARCHAR(10),
StudPhoneNumber VARCHAR(15)
);
-- 9. SUBJECTS
CREATE TABLE Subjects (
SubjectID INT PRIMARY KEY,
CategoryID INT,
SubjectCode VARCHAR(20),
SubjectName VARCHAR(100),
SubjectDescription TEXT,
FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

-- 10. CATEGORIES
CREATE TABLE Categories (
CategoryID INT PRIMARY KEY,
CategoryDescription VARCHAR(100),
DepartmentID INT
);

-- 11. CLASSROOMS
CREATE TABLE Classrooms (
ClassRoomID INT PRIMARY KEY,
BuildingCode VARCHAR(10),
PhoneAvailable BOOLEAN,
FOREIGN KEY (BuildingCode) REFERENCES Buildings(BuildingCode)
);

-- 12. BUILDINGS
CREATE TABLE Buildings (
BuildingCode VARCHAR(10) PRIMARY KEY,
BuildingName VARCHAR(100),
NumberOfFloors INT
);

-- 13. STUDENT_CLASS_STATUS
CREATE TABLE Student_Class_Status (
ClassStatus VARCHAR(10) PRIMARY KEY,
ClassDescription VARCHAR(100)
);

UPDATE Staff
SET stareacode = '0484'
WHERE staffid = '3004';

UPDATE STUDENTS
SET STUDENTID='202'
WHERE studlastname='S;
UPDATE Staff
SET "joining date" = '2012-08-02'
WHERE staffid = 3001;

UPDATE FACULTY
SET tenured='2025-06-04'
WHERE staffid=3004;

SELECT staffid, title, status, tenured


FROM FACULTY
WHERE tenured <= CURRENT_DATE - INTERVAL '5 years';

SELECT
staffid,
title,
status,
tenured,
(tenured + INTERVAL '10 years' - CURRENT_DATE) AS remaining_tenure
FROM FACULTY
WHERE tenured <= CURRENT_DATE - INTERVAL '5 years';

2.1
[Link] INTO staff (staffid, stfirstname, stlastname, ststreetaddress, stcity, ststate, stzipcode,
stareacode, stphonenumber, joining_date, salary, position) VALUES
(3001, 'Arun', 'Menon', '123 MG Road', 'Trivandrum', 'Kerala', 695001, '0471', '9998877665', '2015-06-
01', 65000.00, 'Professor'),
(3002, 'Divya', 'Nair', '45 Lake View', 'Kochi', 'Kerala', 682001, '0484', '8887766554', '2021-07-10',
48000.00, 'Asst. Professor'),
(3003, 'Vivek', 'Krishnan', '78 Hilltop Ave', 'Kollam', 'Kerala', 691001, '0474', '7776655443', '2012-01-
20', 58000.00, 'Professor'),
(3004, 'Sneha', 'Raj', '34 Green Park', 'Calicut', 'Kerala', 673001, '0484', '6665544332', '2019-09-05',
35000.00, 'Lecturer'),
(3005, 'John', 'Antony', '56 River Side', 'Chennai', 'Tamil Nadu', 600001, '044', '9554433322', '2016-03-
12', 72000.00, 'Professor');

[Link] INTO faculty (staffid, title, status, tenured) VALUES


(3001, 'Dr.', 'Full-time', '2018-06-01'),
(3002, 'Ms.', 'Part-time', '2023-07-10'),
(3003, 'Mr.', 'Full-time', '2016-01-20'),
(3004, 'Dr.', 'Part-time', '2022-09-05'),
(3005, 'Dr.', 'Full-time', '2019-03-12');

[Link] INTO Buildings VALUES


('B001', 'Main Building', 5),
('B002', 'Science Block', 3);
[Link] INTO Categories VALUES
(1, 'Computer Science Core', 101),
(2, 'Mathematics', 102);

[Link] INTO Subjects (SubjectID, CategoryID, SubjectCode, SubjectName) VALUES


(501, 1, 'CS101', 'Data Structures'),
(502, 2, 'MA201', 'Mathematics');

[Link] INTO Classes (ClassID, SubjectID, ClassRoomID, StartTime, Duration) VALUES


(1001, 501, 101, '[Link]', 90),
(1002, 502, 102, '[Link]', 60);

[Link] INTO Classrooms VALUES


(101, 'B001', TRUE),
(102, 'B001', FALSE),
(201, 'B002', TRUE);

[Link] INTO Faculty_Categories VALUES


(3001, 1),
(3002, 2);

[Link] INTO Student_Schedules (ClassID, StudentID, ClassStatus, Grade) VALUES


(1001, 201, 'A', 'A+'),
(1002, 202, 'A', 'B+');

[Link] INTO Faculty_Classes (StaffID, ClassID) VALUES


(3001, 1001),
(3002, 1002);

[Link] INTO Faculty_Subjects (StaffID, SubjectID, ProficiencyRating) VALUES


(3001, 501, 9),
(3002, 502, 8);
[Link] INTO Student_Class_Status VALUES
('A', 'Attending'),
('C', 'Completed'),
('D', 'Dropped');
13
.INSERT INTO Students (
StudentID,
StudFirstName,
StudLastName,
StudStreetAddress,
StudCity,
StudState,
StudZipCode,
StudAreaCode,
StudPhoneNumber
)
VALUES
(201, 'Nikhil', 'R', '123 MG Road', 'Trivandrum', 'Kerala', '695001', '0471', '1234567890'),
(202, 'Anjali', 'S', '456 Main St', 'Kochi', 'Kerala', '682001', '0484', '9876543210');

2.2
SELECT
[Link],[Link],[Link],[Link],[Link]
FROM FACULTY F
JOIN STAFF S ON [Link]=[Link]
WHERE [Link]<=CURRENT_DATE-INTERVAL '5 YEARS';

2.3
SELECT
staffid,
tenured,
AGE(tenured, CURRENT_DATE) AS remaining_tenure
FROM Faculty
WHERE tenured > CURRENT_DATE;

2.4
SELECT STAFFID,STFIRSTNAME,STLASTNAME,SALARY
FROM STAFF
WHERE SALARY>10000 AND SALARY<50000;
2.5
SELECT COUNT(DISTINCT Position)AS Numberofuniquepositions
FROM Staff;

2.6
SELECT COUNT(*) AS NUMBEROFSTAFFINAREACODE
FROM STAFF
WHERE Stareacode='0484';

You might also like