-- ========================================
-- PART I: DML (INSERT, UPDATE, DELETE)
-- ========================================
-- 1. Insert Professor Charles Xavier Data
INSERT INTO Professor (emp_num, emp_fname, emp_lname, emp_dob, dept_code)
VALUES (101, "Charles", "Xavier", "1975-05-04", "CIS");
-- 2. Update Professor Xavier's phone extension to 0077
UPDATE Professor
SET prof_extension = "0077"
WHERE emp_num = 101;
-- 3. Remove Professor Xavier
DELETE FROM Professor
WHERE emp_num = 101;
-- ========================================
-- PART II: DQL (SELECT Queries)
-- ========================================
-- 4. Show all students’ ID, fullname, birthdate, and age
SELECT
stu_num,
CONCAT(stu_fname, " ", stu_lname) AS fullname,
stu_dob,
YEAR(CURDATE()) - YEAR(stu_dob) AS age
FROM Student;
-- 5. List all unique course codes from the Class table
SELECT DISTINCT course_code FROM Class;
-- 6. List all CIS professors' names and department codes
SELECT emp_fname, emp_lname, dept_code
FROM Professor
WHERE dept_code = "CIS";
-- 7. List professors with "M" in their first or last name
SELECT emp_fname, emp_lname
FROM Professor
WHERE (emp_fname LIKE "%M%" OR emp_fname LIKE "%m%")
AND (emp_lname LIKE "%M%" OR emp_lname LIKE "%m%");
-- 8. Show students in ENG, ART, SOC, or HIST departments
-- Sorted alphabetically by department code
SELECT stu_fname, stu_lname, dept_code
FROM Student
WHERE dept_code IN ("ENG", "ART", "SOC", "HIST")
ORDER BY dept_code ASC;
-- 9. Tuition fee of all "Intro." courses (cost per credit: $250)
SELECT
course_code,
course_name,
(crs_credit * 250) AS tuition_fee,
dept_code
FROM Course
WHERE course_name LIKE "Intro.%"
ORDER BY dept_code ASC;
-- 10. Select courses in classrooms starting with "KLR" and class codes between 20000 and 30000
SELECT DISTINCT course_code, class_room
FROM Class
WHERE class_room LIKE "KLR%"
AND class_code BETWEEN 20000 AND 30000;
-- ========================================
-- PART III: DDL (Data Definition Language)
-- ========================================
-- 11. Create a new table: Project
CREATE TABLE Project (
pnumber INT PRIMARY KEY,
pname VARCHAR(50) NOT NULL,
dept_no INT NOT NULL,
CONSTRAINT FK_DeptProj FOREIGN KEY (dept_no) REFERENCES Department(dnumber)
);
-- 12. Alter the Employee table to add a new column for job title
ALTER TABLE Employee
ADD COLUMN job_title VARCHAR(50);
-- 13. Drop the old Training table
DROP TABLE Training;
-- ========================================
-- Additional Examples
-- ========================================
-- Insert a batch of departments
INSERT INTO Department (dnumber, dname, location)
VALUES
(1, "Accounting", "2A101 Fl.1"),
(2, "Human Resources", "2A104 Fl.1"),
(3, "Research and Development", "2B401 Fl.4"),
(4, "Information Technology", "2A404 Fl.4"),
(5, "Public Relations", "2B201 Fl.2");
-- Update employee salary and department
UPDATE Employee
SET salary = 2000, dept_no = 3
WHERE emp_num = 123;
-- Delete records with missing department
DELETE FROM Employee
WHERE dept_no IS NULL;
