Database Operations (i)

drop table Zmt_s_c;

drop table zmt_student;

drop table Zmt_course;

CREATE TABLE Zmt_student (

Sno Char (4) primary key NOT NULL,

Sname nchar (TEN) NOT NULL,

Sex char (1) Check (sex= ' M ' or sex= ' F '),//Condition constraint

Age smallint Check (age<=25 and >=18)

);

INSERT into zmt_student values (' 1 ', ' Zhang Mengting ', ' F ', 22);

INSERT into zmt_student values (' 2 ', ' Yang Mingzhu ', ' F ', 22);

INSERT into zmt_student values (' 3 ', ' Trichon ', ' M ', 22);

INSERT into zmt_student values (' 4 ', ' Mason ', ' M ', 22);

INSERT into zmt_student values (' 5 ', ' fenbing ', ' M ', 22);

INSERT into zmt_student values (' 6 ', ' Mu an Feng ', ' M ', 22);

CREATE TABLE Zmt_course (

CNO Char (4) primary key NOT NULL,

CNAME char (10),

Credit smallint NOT NULL check (credit>=0 and credit<=100)

);

INSERT into zmt_course values (' 1 ', ' math ', 22);

INSERT into zmt_course values (' 2 ', ' English ', 22);

INSERT into Zmt_course values (' 3 ', ' language ', 22);

CREATE TABLE Zmt_student (

Sno Char (4) identity (primary key not null,//identity): Identity column, Identity (A, b), AB is a positive integer, A is the start number, and B represents an increase, just like the identity ( means that the column automatically grows, starting from 1 each time the increment is 1,

Sname nchar (TEN) NOT NULL,

Sex char (1) Check (sex= ' M ' or sex= ' F '),

Age smallint Check (age<=25 and >=18)

);

CREATE TABLE Zmt_course (

CNO Char (4) identity (primary key) is not NULL,

CNAME char (10),

Credit smallint NOT NULL check (credit>=0 and credit<=100)

);

CREATE TABLE Zmt_s_c (

Sno Char (4),

CNO Char (4),

Grade smallint check (grade>=0 and grade <=100),

Constraint ZMT_S_C_SNO_FK foreign KEY (SNO) references Zmt_student (SNO),//FOREIGN KEY constraint

Constraint ZMT_S_C_CNO_FK foreign KEY (CNO) references Zmt_course (CNO)

);

INSERT into Zmt_s_c values (' 1 ', ' 2 ', 80);

INSERT into Zmt_s_c values (' 1 ', ' 1 ', 77);

INSERT into Zmt_s_c values (' 1 ', ' 3 ', 90);

--1. Query gender is not a male student's name

Select Sname from zmt_student where sex!= ' M ';

--2. Query gender is the name of the student whose age is greater than 20

Select Sname from zmt_student where sex= ' M ' and age>20;

--3. Arranging course names in descending order by course number Ascending sort query out result set

SELECT * from Zmt_course ORDER by CNO Desc,cname ASC;

--4. In the S_c table, the student number is 1 students have completed several courses

Select COUNT (CNO) from Zmt_s_c where sno= ' 1 ';

Database Operations (i)