fork download
copy 
  1. CREATE DATABASE my_database
  2. GO 
  3. USE my_database
  4. -- ALTER DATABASE my_database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  5. -- USE master
  6. -- DROP DATABASE my_database
  7. -- 1. Tao bang Department truoc
  8. GO
  9. CREATE TABLE Department (
  10.     department_id VARCHAR(5) PRIMARY KEY,
  11.     department_name NVARCHAR(50) NOT NULL,
  12.     office VARCHAR(5),
  13.     department_head VARCHAR(9)   
  14. )
  15.  
  16. GO
  17. -- Tao bang Instructor sau
  18. CREATE TABLE Instructor (
  19.     instructor_id VARCHAR(9) PRIMARY KEY NOT NULL,
  20.     instructor_name NVARCHAR(50) NOT NULL,
  21.     phone NVARCHAR(9) NOT NULL,
  22.     department_id VARCHAR(5) REFERENCES Department(department_id),
  23.     salary INT CHECK (salary > 0)
  24. )
  25. -- Cap nhap lai bang Department de them khoa ngoai cho department_head
  26. GO
  27. ALTER TABLE Department
  28. ADD FOREIGN KEY (department_head) REFERENCES Instructor(instructor_id)
  29.  
  30. GO
  31. -- Tao bang Student
  32. CREATE TABLE Student (
  33.     student_id VARCHAR(9) PRIMARY KEY,
  34.     student_name NVARCHAR(50) NOT NULL,
  35.     gender CHAR(1) CHECK (gender IN ('M', 'F', 'O')),
  36.     birthday DATETIME,
  37.     student_class VARCHAR(5), 
  38.     department_id VARCHAR(5) REFERENCES Department(department_id)
  39. )
  40. GO
  41. -- Tao bang Course
  42. CREATE TABLE Course (
  43.     course_id VARCHAR(9) PRIMARY KEY,
  44.     course_name NVARCHAR(50) NOT NULL UNIQUE,
  45.     credit INT CHECK (credit > 0),
  46.     department_id VARCHAR(5) REFERENCES Department(department_id)
  47. )
  48.  
  49. GO
  50. -- Tao bang Section
  51. CREATE TABLE Section (
  52.     section_id INT PRIMARY KEY NOT NULL,
  53.     course_id VARCHAR(9) NOT NULL REFERENCES Course(course_id),
  54.     semester VARCHAR(9) NOT NULL,
  55.     school_year INT NOT NULL,
  56.     capacity INT CHECK (capacity > 0),
  57.     CONSTRAINT UQ_Section_Course_Semester_Year UNIQUE (course_id, semester, school_year)
  58. )
  59.  
  60. GO
  61. -- Tao bang Teaching 
  62. CREATE TABLE Teaching (
  63.     section_id INT NOT NULL REFERENCES Section(section_id),
  64.     instructor_id VARCHAR(9) NOT NULL REFERENCES Instructor(instructor_id),
  65.     teaching_role VARCHAR(9) CHECK (teaching_role IN ('Lecturer', 'TA')),
  66.     PRIMARY KEY (section_id, instructor_id)
  67. )
  68. GO
  69. -- Tao bang GradeReport
  70. CREATE TABLE GradeReport (
  71.     section_id INT NOT NULL REFERENCES Section(section_id),
  72.     student_id VARCHAR(9) NOT NULL REFERENCES Student(student_id),
  73.     grade_100 INT CHECK (grade_100 >= 0),
  74.     grade_ABC CHAR(1) CHECK (grade_ABC IN ('A', 'B', 'C', 'D', 'E', 'F')),
  75.     PRIMARY KEY (section_id, student_id)
  76. )
  77. GO
  78. -- Tao bang Prerequisite
  79. CREATE TABLE Prerequisite (
  80.     course_id VARCHAR(9) NOT NULL REFERENCES Course(course_id),
  81.     prerequisite_id VARCHAR(9) NOT NULL REFERENCES Course(course_id),
  82.     PRIMARY KEY (course_id, prerequisite_id)
  83. )
  84.  
  85. GO
  86.  
  87. INSERT INTO Department(department_id, department_name, office, department_head)
  88. VALUES
  89. ('AI', 'Artificial Intelligence', 'I86', NULL),
  90. ('CS', 'Computer Science', 'I81', NULL),
  91. ('IS', 'Information System', 'I84', NULL),
  92. ('NW', 'Network', 'I87', NULL),
  93. ('SE', 'Software Engineering', 'I82', NULL);
  94. GO
  95.  
  96. INSERT INTO Instructor (instructor_id, instructor_name, phone, department_id, salary)
  97. VALUES 
  98. ('I001', 'Dang Huynh Bao Khanh', '080913213', 'CS', 1000),
  99. ('I002', 'Alex Grant', '082412613', 'CS', 2000),
  100. ('I003', 'Tran Hoang Lan', '080921234', 'SE', 1500),
  101. ('I004', 'Nguyen Ngoc Khanh', '090245613', 'IS', 1500),
  102. ('I005', 'James Cobb', '092193213', 'SE', 2000),
  103. ('I006', 'Le Khanh', '090799131', 'IS', 2200),
  104. ('I007', 'Vu Ngoc Bao', '090511342', 'SE', 2100),
  105. ('I008', 'Tran Hong An', '099912353', 'NW', 1900),
  106. ('I009', 'Nguyen Hai Lam', '080911234', 'AI', 1500),
  107. ('I010', 'Dang Hoang Phong', '090233451', 'AI', 2300);
  108. GO
  109.  
  110. --3. Cap nhap (Update) lai department_head cho Department sau khi Instructor da co du lieu
  111. UPDATE Department SET department_head = 'I009' WHERE department_id = 'AI';
  112. UPDATE Department SET department_head = 'I001' WHERE department_id = 'CS';
  113. UPDATE Department SET department_head = 'I004' WHERE department_id = 'IS';
  114. UPDATE Department SET department_head = 'I003' WHERE department_id = 'SE';
  115.  
  116. GO
Success #stdin #stdout #stderr 0.01s 5320KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
Standard output is empty
stderr
copy 
Error: near line 1: near "DATABASE": syntax error
Error: near line 3: near "USE": syntax error
Error: near line 18: near "GO": syntax error
Error: near line 87: no such table: Department
Error: near line 96: no such table: Instructor
Error: near line 111: no such table: Department
Error: near line 112: no such table: Department
Error: near line 113: no such table: Department
Error: near line 114: no such table: Department
https://ideone.com/IAvfn4
language:
SQLite (sqlite 3.27.2)
created:
3 hours 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!