Elective database SQL statement exercises--Create a table

There is a database comprising four tables: Student table (Student), Course table (Course), score table (score) and teacher Information sheet (Teacher). The structure of the four tables, as shown in table 1-1 (i) to table (iv), is shown in table 1-2 table (i) ~ table (iv). Create four tables with SQL statements and complete related topics.

Table 1-1 table Structure of the database

Table (i) Student (student table)

Property name	Data type	Can be empty	Meaning
Sno	varchar (20)	Whether	Study number (main code)
Sname	varchar (20)	Whether	Student Name
Ssex	varchar (20)	Whether	Student Sex
Sbirthday	Datetime	Can	Student's date of birth
Class	varchar (20)	Can	Students in the same class

Table (ii) Course (curriculum)

Property name	Data type	Can be empty	Meaning
Cno	varchar (20)	Whether	Course Number (main code)
Cname	varchar (20)	Whether	Course Name
Tno	varchar (20)	Whether	Faculty Number (external code)

Table (iii) score (score table)

Property name	Data type	Can be empty	Meaning
Sno	varchar (20)	Whether	School Number (external code)
Cno	varchar (20)	Whether	Course number (outside code)
Degree	Decimal (4,1)	Can	Results
Main code: sno+ Cno

Table (iv) Teacher (Teacher's table)

Property name	Data type	Can be empty	Meaning
Tno	varchar (20)	Whether	Faculty Number (main code)
Tname	varchar (20)	Whether	Faculty Name
Tsex	varchar (20)	Whether	Faculty Sex
Tbirthday	Datetime	Can	Faculty Birth date
Prof	varchar (20)	Can	Title
Depart	varchar (20)	Whether	Faculty Department

Table 1-2 data in the database

Table (i) Student

Sno	Sname	Ssex	Sbirthday	Class
108	Zenghua	Man	1977-09-01	95033
105	Kuanming	Man	1975-10-02	95031
107	Wang Li	Woman	1976-01-23	95033
101	Li June	Man	1976-02-20	95033
109	Wang fang	Woman	1975-02-10	95031
103	Contacts	Man	1974-06-03	95031

Table (ii) Course

Cno	Cname	Tno
3-105	Introduction to Computers	825
3-245	Operating system	804
6-166	Digital circuit	856
9-888	Advanced mathematics	831

Table (iii) score

Sno	Cno	Degree
103	3-245	86
105	3-245	75
109	3-245	68
103	3-105	92
105	3-105	88
109	3-105	76
101	3-105	64
107	3-105	91
108	3-105	78
101	6-166	85
107	6-166	79
108	6-166	81

Table (iv) Teacher

Tno	Tname	Tsex	Tbirthday	Prof	Depart
804	Sung	Man	1958-12-02	Associate professor	Computer Department
856	Zhang Xu	Man	1969-03-12	Lecturer	Department of Electronic Engineering
825	Wang ping	Woman	1972-05-05	Ta	Computer Department
831	Liu Bing	Woman	1977-08-14	Ta	Department of Electronic Engineering

Create TableStudent (Snovarchar( -) not NULL Primary Key, #学号 Snamevarchar( -) not NULL, #学生姓名 Ssexvarchar( -) not NULL, #学生性别 sbirthdaydatetime, #学生出生年月 Classvarchar( -) #学生所在班级);Create TableCourse (Cnovarchar( -) not NULL Primary Key, #学号 Cnamevarchar( -) not NULL, #课程号 TnoDecimal(4,1) #成绩);Create TableScore (Snovarchar( -) not NULL, #学号 Cnovarchar( -) not NULL, #课程号 degreeDecimal(4,1), #成绩Primary Key(SNO,CNO));Create TableTeacher (Tnovarchar( -) not NULL Primary Key, #教工编号 tnamevarchar( -) not NULL, #教工姓名 tsexvarchar( -) not NULL, #教工性别 tbirthdaydatetime, #教工出生年月 Profvarchar( -), #职称 departvarchar( -) not NULL#教工所在部门)

Elective database SQL statement exercises--Create a table