Скачать или смотреть Student Database Management System 4

CREATE DATABASE student_db;



USE student_db;

CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR (100),
email VARCHAR (100),
phone VARCHAR (100),
department VARCHAR(100)
);


USE student_db;

CREATE TABLE courses (
id INT PRIMARY KEY,
course_name VARCHAR (100),
credits INT
);

USE student_db;

CREATE TABLE enrollments (
id INT,
students_id INT,
courses_id INT,
grade VARCHAR (10),
FOREIGN KEY (students_id) REFERENCES students (id),
FOREIGN KEY (courses_id) REFERENCES courses (id)
);





INSERT INTO students (id, name, email, phone, department)
VALUES (1, 'Alice Smith', '[email protected]', '(555) 555-1212', 'Computer Science'),
(2, 'Bob Johnson', '[email address removed]', '(555) 555-3434', 'Mathematics'),
(3, 'Charlie Lee', '[email address removed]', '(555) 555-5656', 'History'),
(4, 'David Garcia', '[email address removed]', '(555) 555-7878', 'Engineering'),
(5, 'Emily Walker', '[email address removed]', '(555) 555-0101', 'English Literature'),
(6, 'Sophia Williams', '[email address removed]', '(555) 555-2323', 'Biology'),
(7, 'Michael Brown', '[email address removed]', '(555) 555-4545', 'Physics'),
(8, 'Olivia Jones', '[email address removed]', '(555) 555-6767', 'Chemistry'),
(9, 'William Davis', '[email address removed]', '(555) 555-8989', 'Economics'),
(10, 'Isabella Garcia', '[email address removed]', '(555) 555-1111', 'Psychology'),
(11, 'Noah Miller', '[email address removed]', '(555) 555-3333', 'Philosophy'),
(12, 'Mia Hernandez', '[email address removed]', '(555) 555-5555', 'English'),
(13, 'Ethan Moore', '[email address removed]', '(555) 555-7777', 'Mathematics'),
(14, 'Ava Lopez', '[email address removed]', '(555) 555-9999', 'Computer Science'),
(15, 'Matthew Taylor', '[email address removed]', '(555) 555-2222', 'History'),
(16, 'Evelyn Wilson', '[email address removed]', '(555) 555-1414', 'Nursing'),
(17, 'Lucas Anderson', '[email address removed]', '(555) 555-3636', 'Accounting'),
(18, 'Charlotte Young', '[email address removed]', '(555) 555-5858', 'Marketing'),
(19, 'Daniel Thomas', '[email address removed]', '(555) 555-8080', 'Political Science'),
(20, 'Grace Lee', '[email address removed]', '(555) 555-0303', 'Art History');


INSERT INTO courses (id, course_name, credits)
VALUES (1, 'Introduction to CS', 3),
(2, 'Calculus I', 4),
(3, 'World History', 3),
(4, 'Introduction to ME', 3),
(5, 'Shakespeare', 3),
(6, 'Data Structures & Algorithms', 4),
(7, 'Organic Chemistry', 4),
(8, 'Introduction to Psychology', 3),
(9, 'Microeconomics', 3),
(10, 'Western Civilization', 3),
(11, 'Advanced Programming', 4),
(12, 'Biochemistry', 4),
(13, 'Social Psychology', 3);



-- Enrollments with random grades (10)
INSERT INTO enrollments (id, students_id, courses_id, grade)
VALUES (26, 1, 8, 'C'),
(27, 3, 11, 'B'),
(28, 5, 2, 'A'),
(29, 6, 9, 'D'),
(30, 7, 4, 'B-'),
(31, 8, 6, 'F'),
(32, 9, 1, 'A'),
(33, 12, 7, 'C+'),
(34, 14, 3, 'B+'),
(35, 16, 10, 'A-');

-- Enrollments with specific grade probabilities (20)
INSERT INTO enrollments (id, students_id, courses_id, grade)
VALUES (36, 2, 5, 'B'),
(37, 4, 12, 'C'),
(38, 6, 13, 'A'),
(39, 7, 1, 'B'),
(40, 8, 3, 'C'),
(41, 9, 4, 'B'),
(42, 10, 6, 'A'),
(43, 11, 7, 'C'),
(44, 12, 8, 'B'),
(45, 13, 9, 'A'),
(46, 14, 10, 'B'),
(47, 15, 1, 'C'),
(48, 16, 2, 'A'),
(49, 17, 3, 'B'),
(50, 18, 4, 'C'),
(51, 19, 5, 'B'),
(52, 20, 6, 'A'),
(53, 1, 7, 'B'),
(54, 3, 10, 'C'),
(55, 5, 12, 'A');




SELECT * FROM students WHERE department = 'Computer Science';

SELECT DISTINCT course_name FROM courses;


SELECT e.students_id, c.course_name, e.grade
FROM enrollments e
JOIN courses c ON e.courses_id = c.id
WHERE e.students_id = 7;



INSERT INTO students (id, name, email, phone, department) VALUES (21, 'AbdulKareem', '[email protected]', '08035353522', 'Physics');


UPDATE students SET email = '[email protected]' WHERE id = 14;



DELETE FROM enrollments WHERE courses_id = 5;
DELETE FROM courses WHERE id = 5;