QDpZmZx2

1DROP TABLE IF EXISTS Student;
2
3CREATE TABLE Student(
4    studentId varchar(9),
5    name varchar(25),
6    bdate date,
7    age int,
8    weight decimal(5, 2),
9    nid varchar(16)
10);
11
12SELECT * FROM Student;
13
14
15# Add column to the table
16ALTER TABLE Student ADD (
17    cgpa decimal(3, 2),
18    hobby varchar(30)
19);
20
21
22# Delete column 
23ALTER TABLE Student DROP COLUMN cgpa; 
24SELECT * FROM Student;
25
26
27ALTER TABLE Student MODIFY hobby varchar(50);
28SELECT * FROM Student;
29
30
31# table rename
32RENAME TABLE Student to StudentInfo;
33
34
35
36
37# Apply Constrains
38DROP TABLE IF EXISTS Student;
39CREATE TABLE Student(
40    studentId varchar(9) NOT NULL,
41    name varchar(25) ,
42    #date_of_admission date DEFAULT '2021-07-01',
43    date_of_admission date DEFAULT sysdate(),
44    age int CHECK(age > 0),
45    weight decimal(5, 2),
46    nid varchar(16) UNIQUE,
47    PRIMARY KEY(studentId)
48);
49
50INSERT INTO Student(studentId, name, age) VALUES ('123', 'xyz', 1);
51INSERT INTO Student(studentId, name, age) VALUES ('456', 'ABC', 10);
52
53SELECT * FROM Student;
54
55
56# New Tabel
57DROP TABLE IF EXISTS Result;
58CREATE TABLE Result(
59    studentId varchar(9) UNIQUE,
60    cgpa decimal(3, 2),
61    FOREIGN KEY (studentId) REFERENCES Student(studentId)
62);
63
64#INSERT INTO Result(studentId, cgpa) VALUES('789', 8.5);
65INSERT INTO Result(studentId, cgpa) VALUES('456', 8.5);
66SELECT * FROM Result;
67
68
69
70
71DROP TABLE IF EXISTS Result;
72DROP TABLE IF EXISTS Student;
73CREATE TABLE Student(
74    studentId varchar(9) NOT NULL,
75    name varchar(25) NOT NULL,
76    date_of_admission date DEFAULT sysdate(),
77    age int CHECK(age > 0),
78    weight decimal(5, 2),
79    nid varchar(16) UNIQUE,
80    PRIMARY KEY(studentId, name)
81);
82
83INSERT INTO Student(studentId, name, age) VALUES ('123', 'xyz', 1);
84INSERT INTO Student(studentId, name, age) VALUES ('456', 'ABC', 10);
85
86CREATE TABLE Result(
87    studentId varchar(9),
88    name varchar(25),
89    cgpa decimal(3, 2),
90    FOREIGN KEY (studentId, name) REFERENCES Student(studentId, name) ON DELETE CASCADE
91);
92
93INSERT INTO Result VALUES('456', 'ABC', 8.5);
94
95SELECT * FROM Result;