Course Selection data table Design

Create Database Test

Create Table s
(
S # int primary key not null,
SN varchar (20 ),
Sex varchar (2 ),
Age int,
Dept varchar (50)
)

Insert into S values (1, 'guoyong ',' B ', 28, 'shandong ')
Insert into S values (2, 'hangzhou', 'B', 28, 'json ')
Insert into S values (3, 'nizhong ',' B ', 28, 'beijing ')
Insert into S values (4, 'xiaoguang ',' B ', 28, 'beijing ')

Create Table C
(
C # int primary key not null,
CN varchar (50)
)

Insert into C values (1, 'Math ')
Insert into C values (2, 'English ')
Insert into C values (3, 'China ')
Insert into C values (4, 'java ')
Create Table SC
(
S # int not null,
C # int not null,
Grade int
)

Insert into SC values (1, 1, 80)
Insert into SC values (1, 2, 50)
Insert into SC values (1, 3, 67)
Insert into SC values (1, 4, 77)
Insert into SC values (2, 1, 75)
Insert into SC values (2, 2, 98)
Insert into SC values (2, 3, 97)
Insert into SC values (2, 4, 57)

Insert into SC values (3,1, 77)
Insert into SC values (3,2, 88)
Insert into SC values (3, 3, 99)
Insert into SC values (3, 4, 90)

Insert into SC values (4,1, 77)
Insert into SC values (4,2, 88)
Insert into SC values (4,3, 99)
Insert into SC values (4, 4, 90)

Delete from SC where s # = 4

Select count (*) from S

Select S. Sn, SC. C #, SC. grade from s inner join SC on S. S # = SC. s #

Select s # from SC group by S # Having count (*)> 1

Select *
From C
Where exists
(Select C #
From SC
Where C. C # = SC. C #
Group by C #
Having count (*) between 2 and 4
)