fork download
copy 
  1. ======================== Database Lab 2 Question ========================
  2. 1. Design a Schema consisting of at least 4 relations, Implement it and perform and entry
  3. 2. Write 5 queries based on your database design
  4. =========================================================================
  5.  
  6.  
  7. ==================== Database BUBT Management System ====================
  8.  
  9. -- Step 1: (create databse)
  10. 	CREATE DATABASE bubt_management
  11.  
  12. -- Step 2: (create students databse)	
  13. 	CREATE TABLE Students (
  14. 	    student_id INT PRIMARY KEY,
  15. 	    first_name VARCHAR(50),
  16. 	    last_name VARCHAR(50),
  17. 	    email VARCHAR(100),
  18. 	    date_of_birth DATE,
  19. 	    enrollment_date DATE
  20. 	);
  21.  
  22. -- Step 3: (create professors databse)
  23. 	CREATE TABLE Professors (
  24. 	    professor_id INT PRIMARY KEY,
  25. 	    first_name VARCHAR(50),
  26. 	    last_name VARCHAR(50),
  27. 	    email VARCHAR(100),
  28. 	    department VARCHAR(50)
  29. 	);
  30.  
  31. -- Step 4: (create courses databse)
  32. 	CREATE TABLE Courses (
  33. 	    course_id INT PRIMARY KEY,
  34. 	    course_name VARCHAR(100),
  35. 	    credits INT,
  36. 	    semester VARCHAR(20),
  37. 	    professor_id INT,
  38. 	    FOREIGN KEY (professor_id) REFERENCES Professors(professor_id)
  39. 	);
  40.  
  41. -- Step 4: (create courses databse)
  42. 	CREATE TABLE Enrollments (
  43. 	    enrollment_id INT PRIMARY KEY,
  44. 	    student_id INT,
  45. 	    course_id INT,
  46. 	    grade CHAR(2),
  47. 	    FOREIGN KEY (student_id) REFERENCES Students(student_id),
  48. 	    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
  49. 	);
  50. =========================================================================
  51.  
  52.  
  53. ============================== Insert Data ==============================
  54. -- 1. Insert into Students
  55. 	INSERT INTO Students (student_id, first_name, last_name, email, date_of_birth, 		enrollment_date) VALUES
  56. 	(1, 'John', 'Doe', 'john.doe@example.com', '2001-05-15', '2020-08-25'),
  57. 	(2, 'Jane', 'Smith', 'jane.smith@example.com', '2000-03-22', '2019-09-12'),
  58. 	(3, 'Alice', 'Johnson', 'alice.johnson@example.com', '2002-11-30', '2021-01-17'),
  59. 	(4, 'Bob', 'Brown', 'bob.brown@example.com', '2001-07-04', '2020-02-14'),
  60. 	(5, 'Charlie', 'Davis', 'charlie.davis@example.com', '2000-09-08', '2018-09-10');
  61.  
  62.  
  63.  
  64. -- 2. Insert into Professors
  65. 	INSERT INTO Professors (professor_id, first_name, last_name, email, department) VALUES
  66. 	(1, 'Dr. William', 'Taylor', 'william.taylor@bubt.edu', 'Computer Science'),
  67. 	(2, 'Dr. Emily', 'Adams', 'emily.adams@bubt.edu', 'Mathematics'),
  68. 	(3, 'Dr. Sarah', 'White', 'sarah.white@bubt.edu', 'Physics'),
  69. 	(4, 'Dr. Michael', 'Clark', 'michael.clark@bubt.edu', 'Chemistry'),
  70. 	(5, 'Dr. Robert', 'Lee', 'robert.lee@bubt.edu', 'Biology');
  71.  
  72.  
  73.  
  74. -- 3. Insert into Courses
  75. 	INSERT INTO Courses (course_id, course_name, credits, semester, professor_id) VALUES
  76. 	(101, 'Introduction to Programming', 3, 'Fall 2025', 1),
  77. 	(102, 'Calculus I', 4, 'Spring 2025', 2),
  78. 	(103, 'Physics Fundamentals', 3, 'Fall 2025', 3),
  79. 	(104, 'Organic Chemistry', 3, 'Spring 2025', 4),
  80. 	(105, 'Cell Biology', 3, 'Fall 2025', 5);
  81.  
  82.  
  83.  
  84. -- 4. Insert into Enrollments
  85. 	INSERT INTO Enrollments (enrollment_id, student_id, course_id, grade) VALUES
  86. 	(1, 1, 101, 'A'),
  87. 	(2, 2, 102, 'B'),
  88. 	(3, 3, 103, 'A'),
  89. 	(4, 4, 104, 'C'),
  90. 	(5, 5, 105, 'B');
  91.  
  92. =========================================================================
  93.  
  94. ================================= Query =================================
  95. -- 1. Get all students with their enrollment dates
  96. 	SELECT student_id, first_name, last_name, enrollment_date
  97. 	FROM Students;
  98.  
  99.  
  100. -- 2.Get all courses taught by a specific professor
  101. 	SELECT course_id, course_name, credits, semester
  102. 	FROM Courses
  103. 	WHERE professor_id = 1;
  104.  
  105.  
  106. -- 3. Get the list of students enrolled in a specific course
  107. 	SELECT s.student_id, s.first_name, s.last_name, e.grade
  108. 	FROM Students s
  109. 	JOIN Enrollments e ON s.student_id = e.student_id
  110. 	WHERE e.course_id = 101;
  111.  
  112.  
  113. -- 4. Get all professors in the 'Computer Science' department
  114. 	SELECT professor_id, first_name, last_name, email
  115. 	FROM Professors
  116. 	WHERE department = 'Computer Science';
  117.  
  118.  
  119. -- 5. Get all students with their grades and the courses they are enrolled in
  120. 	SELECT s.first_name, s.last_name, c.course_name, e.grade
  121. 	FROM Students s
  122. 	JOIN Enrollments e ON s.student_id = e.student_id
  123. 	JOIN Courses c ON e.course_id = c.course_id;
  124.  
  125. =========================================================================
Success #stdin #stdout #stderr 0.01s 5288KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
101|Introduction to Programming|3|Fall 2025
1|Dr. William|Taylor|william.taylor@bubt.edu
stderr
copy 
Error: near line 1: near "==": syntax error
Error: near line 50: near "==": syntax error
Error: near line 92: near "==": syntax error
Error: near line 107: no such table: Students
Error: near line 120: no such table: Students
Error: near line 125: near "==": syntax error
https://ideone.com/uuzujr
language:
SQLite (sqlite 3.27.2)
created:
12 days ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!