Very good SQL statement learning manual instance version
Very good SQL statement learning manual instance version
Table operations
Example 1 Table STUDENTS in the table teaching management database can be defined as follows:
The Code is as follows:
Create table students (sno numeric (6, 0) NOT NULL
Sname char (8) NOT NULL
Age numeric (3, 0)
Sex char (2)
Bplace char (20)
Primary key (SNO ))
For example 2, the table ENROLLS in the table teaching management database can be defined as follows:
The Code is as follows:
Create table enrolls (sno numeric (6, 0) NOT NULL
Cno char (4) NOT NULL
GRADE INT
Primary key (SNO, CNO)
Foreign key (SNO) references students (SNO)
Foreign key (CNO) references courses (CNO)
CHECK (grade is null) or (grade between 0 AND 100 )))
Example 3 create a table of STUDENTS that only contain student IDs, names, and ages based on the STUDENTS table in the table.
The Code is as follows:
Create table girl as Select SNO, SNAME, AGE
From students Where SEX = 'femal ';
Example 4 Delete the instructor table TEACHER.
Drop TABLE TEACHER
Example 5 add the address column in the instructor table.
Alter table teachers add (addr char (50 ))
Example 6 Delete the BPLACE column in the STUDENTS table and delete all views and constraints that reference the BPLACE column.
Alter table students Drop BPLACE CASCADE
Example 7: add the primary keyword of the ENROLLS table.
Alter table enrolls add primary key (SNO, CNO );
View operations (Virtual tables)
Example 9 create a view FACULTY that only includes the instructor ID, name, and age. (The view definition cannot contain the orDER BY clause)
Create view faculty as Select TNO, TNAME, AGE FROM TEACHERS
Example 10 a view GRADE_TABLE is generated from the student table, course list, and course selection table, which includes the Student name, course name, and score.
Create VIEW GRADE_TABLE AS Select SNAME, CNAME, GRADE
From students, COURSES, ENROLLS
Where STUDENTS. SNO = ENROLLS. SNO AND
COURSES. CNO = ENROLLS. CNO
Example 11 Delete view GRADE_TABLE
Drop VIEW GRADE_TABLE RESTRICT
Index operations
Example 12 create an index by student ID in the student table.
Create unique index st on students (SNO, ASC)
Example 13 delete the index created by student ID.
Drop INDEX ST
Database mode operations
Example 14 create a database mode of the simple teaching database, TEACHING_DB, with the owner being ZHANG.
Create SCHEMA TEACHING_DB AUTHRIZATION ZHANG
Example 15 Delete the simple teaching database mode TEACHING_DB.
(1) CASCADE is selected, that is, when the database mode is deleted, the database mode and its basic tables, views, indexes, and so on are all deleted.
(2) Select RESTRICT, that is, the basic tables, views, and indexes of the current database mode have been cleared in advance before the database mode can be deleted. Otherwise, the database mode cannot be deleted.
Drop SCHEMA TEACHING_DB CASCADE
