Notes

create database College2

CREATE TABLE Department (
department_id INT PRIMARY KEY,
department_name VARCHAR(50),
location VARCHAR(50)
);

INSERT INTO Department (department_id, department_name, location) VALUES
(1, 'Sales', 'New York'),
(2, 'Marketing', 'Los Angeles'),
(3, 'Engineering', 'San Francisco'),
(4, 'Human Resources', 'Chicago');

CREATE TABLE Infrastructure (
infrastructure_id INT PRIMARY KEY,
infrastructure_type VARCHAR(50),
location VARCHAR(50),
purchase_date DATE,
price DECIMAL(10,2)
);

INSERT INTO Infrastructure (infrastructure_id, infrastructure_type, location, purchase_date, price) VALUES
(1, 'Server', 'New York', '2022-01-15', 5000.00),
(2, 'Networking Equipment', 'Los Angeles', '2021-07-01', 2500.00),
(3, 'Storage', 'San Francisco', '2022-02-20', 8000.00),


(4, 'Workstations', 'Chicago', '2021-12-05', 3000.00);

CREATE TABLE Course (
course_id INT PRIMARY KEY,
course_name VARCHAR(50),
instructor_name VARCHAR(50),
start_date DATE,
end_date DATE
);

INSERT INTO Course (course_id, course_name, instructor_name, start_date, end_date) VALUES
(1, 'Introduction to Programming', 'John Smith', '2022-01-10', '2022-03-05'),


(2, 'Database Management', 'Sarah Johnson', '2022-02-15', '2022-04-20'),
(3, 'Web Development', 'Michael Davis', '2022-03-01', '2022-05-10'),
(4, 'Data Structures and Algorithms', 'Emily Thompson', '2022-03-15', '2022-05-20');


CREATE TABLE Teachers (
teacher_id INT PRIMARY KEY,
teacher_name VARCHAR(50),
department_id INT,
hire_date DATE,
salary DECIMAL(10,2)
);

INSERT INTO Teachers (teacher_id, teacher_name, department_id, hire_date, salary) VALUES
(1, 'John Smith', 1, '2018-05-01', 60000.00),
(2, 'Sarah Johnson', 2, '2019-01-15', 70000.00),
(3, 'Michael Davis', 3, '2020-03-01', 80000.00),
(4, 'Emily Thompson', 1, '2021-06-01', 65000.00);


CREATE TABLE Students (
student_id INT PRIMARY KEY,
student_name VARCHAR(50),
department_id INT,
enroll_date DATE,
email VARCHAR(50)
);

INSERT INTO Students (student_id, student_name, department_id, enroll_date, email) VALUES
(1, 'Alice Smith', 1, '2021-08-01', '[email protected]'),


(2, 'Bob Johnson', 2, '2021-08-01', '[email protected]'),
(3, 'Charlie Davis', 3, '2021-08-01', '[email protected]'),
(4, 'Diana Thompson', 1, '2021-08-01', '[email protected]');


CREATE TABLE Labs (
lab_id INT PRIMARY KEY,
lab_name VARCHAR(50),
department_id INT,
capacity INT,
building VARCHAR(50)
);

INSERT INTO Labs (lab_id, lab_name, department_id, capacity, building) VALUES
(1, 'Physics Lab', 1, 30, 'Science Building'),
(2, 'Computer Lab', 2, 40, 'Engineering Building'),
(3, 'Chemistry Lab', 1, 25, 'Science Building'),
(4, 'Biology Lab', 3, 35, 'Life Sciences Building');

CREATE TABLE Theory (
theory_id INT PRIMARY KEY,
theory_name VARCHAR(50),
course_id INT,
teacher_id INT,
semester VARCHAR(10),
credit_hours INT
);

INSERT INTO Theory (theory_id, theory_name, course_id, teacher_id, semester, credit_hours) VALUES
(1, 'Introduction to Computer Science', 1, 2, 'Fall 2021', 3),
(2, 'Calculus I', 2, 3, 'Fall 2021', 4),
(3, 'General Physics I', 3, 1, 'Fall 2021', 4),
(4, 'Organic Chemistry', 4, 3, 'Spring 2022', 3),
(5, 'Introduction to Psychology', 5, 4, 'Spring 2022', 3);