apd9NrAE

1-- move to default database
2USE master;
3GO
4-- create new database
5DROP DATABASE IF EXISTS StudentDemo;
6CREATE DATABASE StudentDemo;
7GO
8-- switch to new database
9USE StudentDemo;
10GO
11-- create student table
12CREATE TABLE Student(
13    StudentID INT IDENTITY PRIMARY KEY,
14    FirstName NVARCHAR(32) NOT NULL,
15    LastName NVARCHAR(32) NOT NULL,
16    Age INT NOT NULL,
17    Photo VARBINARY(MAX)
18);
19GO
20INSERT INTO Student(FirstName, LastName,Age,Photo)
21VALUES ( 'John', 'Smith', 32, NULL);
22GO
23
24SELECT * FROM Student;
25GO
26
27DROP TABLE IF EXISTS Course;
28CREATE TABLE Course (
29    CourseID INT IDENTITY PRIMARY KEY,
30    Code NCHAR(8) NOT NULL,
31    Name NVARCHAR(32) NOT NULL,
32    StudentID INT NOT NULL
33);
34GO
35ALTER TABLE Course
36ADD CONSTRAINT FK_Student_StudentID
37FOREIGN KEY (StudentID)
38REFERENCES Student (StudentID) ON DELETE CASCADE  ON UPDATE CASCADE;
39GO
40
41INSERT INTO Course (Code, Name, StudentID)
42VALUES ('DBAS4002', 'Transactional SQL Programming', 1);
43GO