Catalogue
2. SQL statement [create data table (6 tables), insert data]
2.1. Chinese field representation
2.2. English field representation
3.2. Total credits (student number, credits)
1. Textbook [P281-P287]
2. SQL statement [create data table (6 tables), insert data]
All the SQL statements used are here!!!
2.1. Chinese field representation
--Table 1 - student table
CREATE TABLE student table(
Student No. char(8) PRIMARY KEY,
Name char(8),
Sex char (2) check (sex IN ('male ',' female '),
System char(20),
Professional char(20),
Shift number char(6)
)
--Table 2 - Curriculum
CREATE TABLE Curriculum(
Course number char(8) PRIMARY KEY,
Course number varchar(30) NOT NULL,
Tiny check (BETWEEN 1 AND 8),
Tinyint check (BETWEEN 1 AND 12),
Course nature char (4) check (course nature IN ('compulsory ',' elective '),
Char (4) check (IN ('examination ',' examination '),
Tinyint check (teaching hours < = 68),
Practice hours tinyint
)
--Table 3 - teacher list
CREATE TABLE teacher table(
char(10) PRIMARY KEY,
Teacher name char(8) NOT NULL,
Sex char (2) check (sex IN ('male ',' female '),
Title char (6) check (title IN ('ta ',' lecturer ',' associate professor ',' Professor '),
Degree char (6) check (degree IN ('undergraduate ',' master ',' doctor ',' postdoctoral '),
Date of birth smalldatetime,
Department char (20)
)
--Table 4 - course selection table
CREATE TABLE(
Student number char(8) NOT NULL,
Course number char(8) NOT NULL,
Course selection year char(8) NOT NULL,
Course selection term char(8) NOT NULL,
Primary key (student number, course number),
Foreign key REFERENCES student form,
Foreign key REFERENCES Curriculum
)
--Table 5 - score sheet
CREATE TABLE(
Student number char(8) NOT NULL,
Course number char(8) NOT NULL,
Tinyint check (number of tests BETWEEN 1 AND 3),
Test result tiny check (test result BETWEEN 0 AND 100),
Primary key (student number, course number, number of examinations),
Foreign key REFERENCES student form,
Foreign key REFERENCES Curriculum
)
--Table 6 - lecture schedule
CREATE TABLE(
Course number char(8) NOT NULL,
Teacher number char(10) NOT NULL,
Teaching year char(4),
Teaching term tinyint,
Class: char (6) check,
Teaching hours tinyint,
Primary key (course number, teacher number, teaching year, teaching term),
Foreign key REFERENCES curriculum,
Teacher key REFERENCES teacher form (teacher number)
)
------------------Insert data------------------
--Table 1 - student table
INSERT INTO student table VALUES('20180901 ',' Li motong ',' female ',' computer department ',' mobile software ',' class 1801 ');
INSERT INTO student table VALUES('20180902 ',' Su Xiaoyan ',' female ',' computer department ',' mobile software ',' class 1801 ');
INSERT INTO student table VALUES('20180903 ',' feinger ',' male ',' computer department ',' mobile software ',' class 1801 ');
INSERT INTO student table VALUES('20180904 ',' Lulin city ',' man ',' computer department ',' mobile software ',' class 1801 ');
INSERT INTO student table VALUES('20180905 ',' Lu Mingze ',' male ',' computer department ',' mobile software ',' class 1801 ');
INSERT INTO student table VALUES('20180906 ',' Lu Mingfei ',' male ',' computer department ',' mobile software ',' class 1801 ');
INSERT INTO student table VALUES('20180907 ',' Giovanni ',' female ',' computer department ',' mobile software ',' class 1801 ');
INSERT INTO student table VALUES('20110901 ',' Wang Minmin ',' female ',' computer department ',' network engineering ',' class 1101 ');
INSERT INTO student table VALUES('20110902 ',' Wang min'er ',' female ',' computer department ',' network engineering ',' class 1101 ');
INSERT INTO student table VALUES('20010901 ',' Li Xiaolin ',' female ',' computer department ',' communication engineering ',' class 0101 ');
INSERT INTO student table VALUES('20010902 ',' Li Xiaohua ',' female ',' computer department ',' communication engineering ',' class 0101 ');
INSERT INTO student form VALUES('20010903 ',' Li Xiaoling ',' female ',' computer department ',' communication engineering ',' class 0101 ');
INSERT INTO student form VALUES('20010904 ',' Wang Nini ',' male ',' computer department ',' communication engineering ',' class 0102 ');
INSERT INTO student form values ('199901 ',' song Shuyu ',' female ',' computer department ',' network operation and maintenance ',' 9901 class');
INSERT INTO student form values ('199902 ',' song Shuhang ',' male ',' computer department ',' network operation and maintenance ',' 9901 class');
INSERT INTO student table VALUES('19980901 ',' Zhang Xiaohai ',' male ',' computer department ',' intelligent IOT ',' class 9801 ');
INSERT INTO student table VALUES('19980902 ',' Qian Xiaoping ',' male ',' computer department ',' intelligent IOT ',' class 9801 ');
INSERT INTO student table VALUES('19210901 ',' Wang Dali ',' male ',' computer department ',' network development ',' class 2101 ');
INSERT INTO student table VALUES('19210902 ',' Wang Xiaoli ',' male ',' computer department ',' network development ',' class 2101 ');
INSERT INTO student table VALUES('19210903 ',' Wang Zhongli ',' male ',' computer department ',' network development ',' class 2102 ');
INSERT INTO student table VALUES('19210904 ',' Wang niuli ',' male ',' computer department ',' network development ',' class 2102 ');
--Table 2 - Curriculum
INSERT INTO curriculum VALUES('a001 ',' Advanced Mathematics', '2', '1', 'compulsory', 'examination', '46', '8');
INSERT INTO curriculum VALUES('a002 ',' discrete mathematics', '2', '2', 'elective', 'exam', '52', '6');
INSERT INTO curriculum VALUES('a003 ',' mathematical modeling ',' 4 ',' 3 ',' elective ',' examination ',' 46 ',' 8 ');
INSERT INTO curriculum VALUES('a004 ',' linear algebra ',' 2 ',' 4 ',' compulsory ',' exam ',' 42 ',' 8 ');
INSERT INTO curriculum VALUES('b001 ',' College Chinese ',' 2 ',' 1 ',' compulsory ',' exam ',' 46 ',' 8 ');
INSERT INTO curriculum VALUES('b002 ',' College Mathematics', '2', '2', 'elective', 'exam', '52', '6');
INSERT INTO curriculum VALUES('b003 ',' College English ',' 4 ',' 3 ',' elective ',' examination ',' 46 ',' 8 ');
INSERT INTO curriculum VALUES('b004 ',' College Physics', '2', '4', 'compulsory', 'exam', '33', '8');
INSERT INTO curriculum VALUES('b005 ',' college chemistry ',' 2 ',' 8 ',' compulsory ',' exam ',' 33 ',' 8 ');
INSERT INTO curriculum VALUES('b006 ',' college biology ',' 2 ',' 4 ',' compulsory ',' exam ',' 33 ',' 8 ');
INSERT INTO curriculum VALUES('b007 ',' university politics', '2', '5', 'compulsory', 'examination', '42', '8');
INSERT INTO curriculum VALUES('b008 ',' university history ',' 2 ',' 4 ',' required ',' exam ',' 42 ',' 8 ');
INSERT INTO curriculum VALUES('b009 ',' University Geography ',' 2 ',' 6 ',' compulsory ',' examination ',' 42 ',' 8 ');
INSERT INTO curriculum VALUES('b010 ',' College Physical Education ',' 3 ',' 4 ',' elective ',' examination ',' 42 ',' 8 ');
--Table 3 - teacher list
INSERT INTO teacher table VALUES('t001 ',' Wang Nini ',' male ',' Professor ',' Undergraduate ',' 2000-01-01 ',' computer ');
INSERT INTO teacher table VALUES('t002 ',' Lu Yiting ',' male ',' ta ',' doctor ',' 1998-01-01 ',' computer ');
INSERT INTO teacher table VALUES('t003 ',' Louis', 'man', 'lecturer', 'master', '1997-01-01', 'computer');
INSERT INTO teacher table VALUES('t004 ',' Da Vinci ',' man ',' ta ',' doctor ',' 2001-01-01 ',' computer ');
INSERT INTO teacher table VALUES('t005 ',' Arthas', 'man', 'lecturer', 'doctor', '2013-01-01', 'computer');
INSERT INTO teacher table VALUES('t006 ',' Nicol ',' man ',' Professor ',' doctor ',' 2000-01-01 ',' computer ');
INSERT INTO teacher table VALUES('t007 ',' tongrenjun ',' male ',' ta ',' Undergraduate ',' 2012-01-01 ',' computer ');
INSERT INTO teacher table VALUES('t008 ',' Ming Er ',' male ',' Professor ',' master ',' 1871-01-01 ',' computer ');
INSERT INTO teacher table VALUES('t009 ',' Li Yihai ',' male ',' lecturer ',' doctor ',' 1970-01-01 ',' computer ');
INSERT INTO teacher table VALUES('t010 ',' Lu Junyi ',' male ',' Professor ',' doctor ',' 2010-01-01 ',' computer ');
--Table 4 - course selection table
Values ('20180907 ',' A001 ',' 2018 ',' 1 ');
Values ('20180907 ',' a002 ',' 2018 ',' 1 ') of INSERT INTO course selection table;
Values ('20180907 ',' a003 ',' 2018 ',' 2 ');
Values ('20180907 ',' A004 ',' 2018 ',' 2 ');
Values ('20180906 ',' B001 ',' 2019 ',' 1 ') of INSERT INTO course selection table;
Values ('20180906 ',' B002 ',' 2019 ',' 1 ');
Values ('20180906 ',' B003 ',' 2019 ',' 2 ');
Values ('20180906 ',' B004 ',' 2019 ',' 2 ');
Values ('20180906 ',' B005 ',' 2019 ',' 2 ');
--delete from course selection table;
--Table 5 - score sheet
INSERT INTO score sheet values ('20180907 ',' A001 ',' 1 ',' 99 ');
INSERT INTO score sheet values ('20180907 ',' a002 ',' 2 ',' 87 ');
INSERT INTO score sheet values ('20180907 ',' a003 ',' 3 ',' 88 ');
INSERT INTO score sheet values ('20180907 ',' A004 ',' 1 ',' 99 ');
INSERT INTO score sheet values ('20180906 ',' B001 ',' 2 ',' 33 ');
INSERT INTO score sheet values ('20180906 ',' B002 ',' 1 ',' 99 ');
INSERT INTO score sheet values ('20180906 ',' B003 ',' 2 ',' 91 ');
INSERT INTO score sheet values ('20180906 ',' B004 ',' 1 ',' 92 ');
INSERT INTO score table values ('20180906 ','b005','3 ','93');
--Table 6 - lecture schedule
Values ('a001 ',' T001 ',' 2018 ', 1,' keynote ', 33);
Value ('a002 ',' T002 ',' 2010 ', 2,' keynote ', 24);
INSERT INTO teaching table values ('a003 ',' t003 ',' 2012 ', 1,' keynote ', 25);
INSERT INTO teaching table values ('a004 ',' T004 ',' 2011 ', 2,' main speaker ', 25);
INSERT INTO teaching table values ('b001 ','t005','2018 ', 1,' speakers', 33);
Value ('b002 ',' T006 ',' 2010 ', 2,' keynote ', 24);
Value ('b003 ',' T007 ',' 2012 ', 1,' keynote ', 25) of INSERT INTO;
INSERT INTO values ('b005 ',' T009 ',' 2018 ', 1,' keynote ', 33);
--delete from lecture form;
------------------Query operation------------------
--1. Student course selection Report
SELECT class number, student table. Student number, name, course name
FROM student table JOIN course selection table ON student table. Student number = course selection table. Student number
JOIN course schedule ON course schedule. Course number = course selection schedule. Course number
WHERE class No. = '1801 class' AND course year =' 2018 'AND course semester = 2
--2. Student test results report
SELECT class number, student table. Student number, name, course name, test result
FROM student table JOIN grade table ON student table. Student number = grade table. Student number
JOIN course schedule ON course schedule. Course number = score sheet. Course number
JOIN course selection table ON course table. Course number = course selection table. Course number
WHERE class No. = '1801 class' AND course year =' 2018 'AND course semester = 1
--3. Total credits statement of students
--SELECT student form. Student number, name, class number, total credit = sum (credit)
--FROM student table JOIN course selection table ON student table. Student number = course selection table. Student number
--JOIN course schedule ON course schedule. Course number = course selection schedule. Course number
--WHERE test score > = 60
--GROUP BY student table. Student number
--4. New query required
--CREATE VIEW v_ Total credits (student number, total credits)
--AS
--SELECT student form. Student number, sum (credit)
--FROM student table JOIN grade table ON student table. Student number = grade table. Student number
--JOIN course schedule ON course schedule. Course number = score sheet. Course number
--WHERE test score > = 60
--GROUP BY student table. Student number
--5,
CREATE VIEW v_ Total credits (student number, total credits)
AS
SELECT student form. Student number, sum (credit)
FROM student table JOIN grade table ON student table. Student number = grade table. Student number
JOIN course schedule ON course schedule. Course number = score sheet. Course number
WHERE test score > = 60
GROUP BY student table. Student number
CREATE VIEW v_ Total credit statement
AS
SELECT student form. Student number, name, class number, total credits
FROM student table JOIN v_ Total credits ON student form. Student number = v_ Total credits. Student ID2.2. English field representation
--Table 1-Student table CREATE TABLE Student ( --Student table Sno char(8) PRIMARY KEY, --Student number Sname char(8), --full name Sgender char(2) CHECK( Sgender IN ('male', 'female') ), --Gender Sdepart char(20), --Department Smajor char(20), --major SclassNo char(6) --Shift No ) --Table 2-Class Schedule Card CREATE TABLE Course ( Cno char(8) PRIMARY KEY, --Course number Cname varchar(30) NOT NULL, --Course name Credit tinyint CHECK( Credit BETWEEN 1 AND 8 ), --credit Semester tinyint CHECK( Semester BETWEEN 1 AND 12 ) , --Opening semester Cnature char(4) CHECK( Cnature IN ('Compulsory', 'Elective') ), --Course nature ExamNature char(4) CHECK( ExamNature IN ('examination', 'examine') ), --Examination nature LectureHours tinyint CHECK( LectureHours <= 68 ), --Lecture hours PracticalHours tinyint --Practical hours ) --Table 3-Teacher list CREATE TABLE Teacher ( Tno char(10) PRIMARY KEY, --Teacher number Tname char(8) NOT NULL, --Teacher name Tgender char(2) CHECK( Tgender IN ('male', 'female') ), --Gender Ttitle char(6) CHECK( Ttitle IN ('assistant','lecturer','associate professor','professor') ), --title Teducation char(6) CHECK( Teducation IN ('undergraduate','master','doctor','post-doctoral') ), --education TdateOfBirth smalldatetime, --date of birth Tdepartment char (20) --Department ) --Table 4-StudentCourse CREATE TABLE StudentCourse ( Sno char(8) NOT NULL, --Student number Cno char(8) NOT NULL, --Course number AcademicYear char(8) NOT NULL, --Academic year of course selection ElectiveSemester char(8) NOT NULL, --Elective semester PRIMARY KEY(Sno, Cno), FOREIGN KEY(Sno) REFERENCES Student(Sno), FOREIGN KEY(Cno) REFERENCES Course(Cno) ) --Table 5-Score sheet CREATE TABLE Score ( Sno char(8) NOT NULL, --Student number Cno char(8) NOT NULL, --Course number NumOfExam tinyint CHECK( NumOfExam BETWEEN 1 AND 3), --Number of examinations ExamRes tinyint CHECK( ExamRes BETWEEN 0 AND 100), --Examination results PRIMARY KEY(Sno, Cno, NumOfExam), FOREIGN KEY(Sno) REFERENCES Student(Sno), FOREIGN KEY(Cno) REFERENCES Course(Cno) ) --Table 6-Teach CREATE TABLE Teach ( Cno char(8) NOT NULL, --Course number Tno char(10) NOT NULL, --Teacher number TeachingYear char(4), --Teaching year TeachingTerm tinyint, --Teaching term TeachingType char(6) CHECK( TeachingType IN ('be the speaker', 'coach', 'Band experiment') ), --Class TeachingHours tinyint, --Lecture hours PRIMARY KEY(Cno, Tno, TeachingYear, TeachingTerm), FOREIGN KEY(Cno) REFERENCES Course(Cno), FOREIGN KEY(Tno) REFERENCES Teacher(Tno) ) ------------------insert data------------------ --Table 1-Student table-Student INSERT INTO Student VALUES('20180901', 'Li Mo Tong', 'female', 'Computer Department', 'Mobile software', '1801 class'); INSERT INTO Student VALUES('20180902', 'Su Xiaoyan', 'female', 'Computer Department', 'Mobile software', '1801 class'); INSERT INTO Student VALUES('20180903', 'Finger ', 'male', 'Computer Department', 'Mobile software', '1801 class'); INSERT INTO Student VALUES('20180904', 'Lulin City', 'male', 'Computer Department', 'Mobile software', '1801 class'); INSERT INTO Student VALUES('20180905', 'Lu Mingze', 'male', 'Computer Department', 'Mobile software', '1801 class'); INSERT INTO Student VALUES('20180906', 'Lu Mingfei', 'male', 'Computer Department', 'Mobile software', '1801 class'); INSERT INTO Student VALUES('20180907', 'Giovanni', 'female', 'Computer Department', 'Mobile software', '1801 class'); INSERT INTO Student VALUES('20110901', 'Wang Minmin', 'female', 'Computer Department', 'Network engineering', '1101 class'); INSERT INTO Student VALUES('20110902', 'Wang min'er', 'female', 'Computer Department', 'Network engineering', '1101 class'); INSERT INTO Student VALUES('20010901', 'Li Xiaolin', 'female', 'Computer Department', 'communication engineering', '0101 class'); INSERT INTO Student VALUES('20010902', 'Li Xiaohua', 'female', 'Computer Department', 'communication engineering', '0101 class'); INSERT INTO Student VALUES('20010903', 'Li Xiaoling', 'female', 'Computer Department', 'communication engineering', '0101 class'); INSERT INTO Student VALUES('20010904', 'Wangnini', 'male', 'Computer Department', 'communication engineering', '0102 class'); INSERT INTO Student VALUES('19990901', 'Song Shuyu', 'female', 'Computer Department', 'Network operation and maintenance', '9901 class'); INSERT INTO Student VALUES('19990902', 'Song Shuhang', 'male', 'Computer Department', 'Network operation and maintenance', '9901 class'); INSERT INTO Student VALUES('19980901', 'Zhang Xiaohai', 'male', 'Computer Department', 'Intelligent IOT', '9801 class'); INSERT INTO Student VALUES('19980902', 'Qian Xiaoping', 'male', 'Computer Department', 'Intelligent IOT', '9801 class'); INSERT INTO Student VALUES('19210901', 'Wang Dali', 'male', 'Computer Department', 'Network development', '2101 class'); INSERT INTO Student VALUES('19210902', 'Wang Xiaoli', 'male', 'Computer Department', 'Network development', '2101 class'); INSERT INTO Student VALUES('19210903', 'Wang Zhongli', 'male', 'Computer Department', 'Network development', '2102 class'); INSERT INTO Student VALUES('19210904', 'Wang niuli', 'male', 'Computer Department', 'Network development', '2102 class'); --Table 2-Class Schedule Card-Course INSERT INTO Course VALUES('a001', '<Advanced Mathematics', '2', '1', 'Compulsory', 'examination', '46', '8'); INSERT INTO Course VALUES('a002', '<Discrete mathematics', '2', '2', 'Elective', 'examination', '52', '6'); INSERT INTO Course VALUES('a003', '<Mathematical modeling', '4', '3', 'Elective', 'examine', '46', '8'); INSERT INTO Course VALUES('a004', '<Linear algebra', '2', '4', 'Compulsory', 'examination', '42', '8'); INSERT INTO Course VALUES('b001', '<College Chinese', '2', '1', 'Compulsory', 'examination', '46', '8'); INSERT INTO Course VALUES('b002', '<College Mathematics', '2', '2', 'Elective', 'examination', '52', '6'); INSERT INTO Course VALUES('b003', '<College English', '4', '3', 'Elective', 'examine', '46', '8'); INSERT INTO Course VALUES('b004', '<College Physics', '2', '4', 'Compulsory', 'examination', '33', '8'); INSERT INTO Course VALUES('b005', '<University Chemistry', '2', '8', 'Compulsory', 'examination', '33', '8'); INSERT INTO Course VALUES('b006', '<University Biology', '2', '4', 'Compulsory', 'examination', '33', '8'); INSERT INTO Course VALUES('b007', '<University politics', '2', '5', 'Compulsory', 'examination', '42', '8'); INSERT INTO Course VALUES('b008', '<University History', '2', '4', 'Compulsory', 'examination', '42', '8'); INSERT INTO Course VALUES('b009', '<University Geography', '2', '6', 'Compulsory', 'examination', '42', '8'); INSERT INTO Course VALUES('b010', '<College Physical Education', '3', '4', 'Elective', 'examination', '42', '8'); --Table 3-Teacher list-Teacher INSERT INTO Teacher VALUES('t001', 'Wangnini', 'male', 'professor', 'undergraduate', '2000-01-01', 'computer'); INSERT INTO Teacher VALUES('t002', 'Lu Yiting', 'male', 'assistant', 'doctor', '1998-01-01', 'computer'); INSERT INTO Teacher VALUES('t003', 'Louis', 'male', 'lecturer', 'master', '1997-01-01', 'computer'); INSERT INTO Teacher VALUES('t004', 'Vinci', 'male', 'assistant', 'doctor', '2001-01-01', 'computer'); INSERT INTO Teacher VALUES('t005', 'Arthas ', 'male', 'lecturer', 'doctor', '2013-01-01', 'computer'); INSERT INTO Teacher VALUES('t006', 'Nicol', 'male', 'professor', 'doctor', '2000-01-01', 'computer'); INSERT INTO Teacher VALUES('t007', 'Tongrenjun', 'male', 'assistant', 'undergraduate', '2012-01-01', 'computer'); INSERT INTO Teacher VALUES('t008', 'Mindale', 'male', 'professor', 'master', '1991-01-01', 'computer'); INSERT INTO Teacher VALUES('t009', 'Li Yihai', 'male', 'lecturer', 'doctor', '1970-01-01', 'computer'); INSERT INTO Teacher VALUES('t010', 'Lu Junyi', 'male', 'professor', 'doctor', '2010-01-01', 'computer'); --Table 4-StudentCourse -StudentCourse INSERT INTO StudentCourse VALUES('20180907', 'a001', '2018', '1'); INSERT INTO StudentCourse VALUES('20180907', 'a002', '2018', '1'); INSERT INTO StudentCourse VALUES('20180907', 'a003', '2018', '2'); INSERT INTO StudentCourse VALUES('20180907', 'a004', '2018', '2'); INSERT INTO StudentCourse VALUES('20180906', 'b001', '2019', '1'); INSERT INTO StudentCourse VALUES('20180906', 'b002', '2019', '1'); INSERT INTO StudentCourse VALUES('20180906', 'b003', '2019', '2'); INSERT INTO StudentCourse VALUES('20180906', 'b004', '2019', '2'); INSERT INTO StudentCourse VALUES('20180906', 'b005', '2019', '2'); --delete from StudentCourse; --Table 5-Score sheet-Score INSERT INTO Score VALUES('20180907', 'a001', '1', '99'); INSERT INTO Score VALUES('20180907', 'a002', '2', '87'); INSERT INTO Score VALUES('20180907', 'a003', '3', '88'); INSERT INTO Score VALUES('20180907', 'a004', '1', '99'); INSERT INTO Score VALUES('20180906', 'b001', '2', '33'); INSERT INTO Score VALUES('20180906', 'b002', '1', '99'); INSERT INTO Score VALUES('20180906', 'b003', '2', '91'); INSERT INTO Score VALUES('20180906', 'b004', '1', '92'); INSERT INTO Score VALUES('20180906', 'b005', '3', '93'); --Table 6-Teach -Teach INSERT INTO Teach VALUES('a001', 't001', '2018', 1, 'be the speaker', 33); INSERT INTO Teach VALUES('a002', 't002', '2010', 2, 'be the speaker', 24); INSERT INTO Teach VALUES('a003', 't003', '2012', 1, 'be the speaker', 25); INSERT INTO Teach VALUES('a004', 't004', '2011', 2, 'be the speaker', 25); INSERT INTO Teach VALUES('b001', 't005', '2018', 1, 'be the speaker', 33); INSERT INTO Teach VALUES('b002', 't006', '2010', 2, 'be the speaker', 24); INSERT INTO Teach VALUES('b003', 't007', '2012', 1, 'be the speaker', 25); INSERT INTO Teach VALUES('b005', 't009', '2018', 1, 'be the speaker', 33); --delete from Teach; ------------------Query operation------------------ --1,Student course selection Report SELECT SclassNo, Student.Sno, Sname, Cname FROM Student JOIN StudentCourse ON Student.Sno = StudentCourse.Sno JOIN Course ON Course.Cno = StudentCourse.Cno WHERE SclassNo = '1801 class' AND AcademicYear = '2018' AND ElectiveSemester = 2 --2,Student test results report SELECT SclassNo, Student.Sno, Sname, Cname, ExamRes FROM Student JOIN Score ON Student.Sno = Score.Sno JOIN Course ON Course.Cno = Score.Cno JOIN StudentCourse ON Course.Cno = StudentCourse.Cno WHERE SclassNo = '1801 class' AND AcademicYear = '2018' AND ElectiveSemester = 1 --3,Student's cumulative total credits report [error! ] --SELECT Student table.Student number, full name, Shift No, Total credits = SUM(credit) -- FROM Student table JOIN StudentCourse ON Student table.Student number = StudentCourse .Student number -- JOIN Class Schedule Card ON Class Schedule Card.Course number = StudentCourse .Course number -- WHERE Examination results >= 60 -- GROUP BY Student table.Student number --4,[New query required] --CREATE VIEW v_Total credits(Sno, Total credits) --AS --SELECT Student table.Student number, SUM(credit) -- FROM Student table JOIN Score sheet ON Student table.Student number = Score sheet.Student number -- JOIN Class Schedule Card ON Class Schedule Card.Course number = Score sheet.Course number -- WHERE Examination results >= 60 -- GROUP BY Student table.Student number --5, --CREATE VIEW v_Total credits(Student number, Total credits) --AS --SELECT Student table.Student number, SUM(credit) -- FROM Student table JOIN Score sheet ON Student table.Student number = Score sheet.Student number -- JOIN Class Schedule Card ON Class Schedule Card.Course number = Score sheet.Course number -- WHERE Examination results >= 60 -- GROUP BY Student table.Student number --CREATE VIEW v_Total credit statement --AS -- SELECT Student table.Student number, full name, Shift No, Total credits -- FROM Student table JOIN v_Total credits ON Student table.Student number = v_Total credits.Student number --4,[New query required] CREATE VIEW v_Total credits(Sno, SUM(Credit)) AS SELECT Student.Sno, SUM(Credit) FROM Student JOIN Score ON Student.Sno = Score.Sno JOIN Course ON Course.Cno = Score.Cno WHERE ExamRes >= 60 GROUP BY Student.Sno --5, CREATE VIEW v_Total credits(Sno, SUM(Credit)) AS SELECT Student.Credit, SUM(Credit) FROM Student JOIN Score ON Student.Sno = Score.Sno JOIN Course ON Course.Cno = Score.Cno WHERE ExamRes >= 60 GROUP BY Student.Sno CREATE VIEW v_Total credit statement AS SELECT Student.Sno, Sname, SclassNo, SUM(Credit) FROM Student JOIN v_Total credits ON Student.Sno = v_Total credits.Sno /* 1,Inquire the basic information of students according to the information of department, major and class. 2,Query the basic information of the course according to the semester. 3,Inquire the basic information of teachers according to the Department. 4,According to the class number, query the students' course selection in the current semester and academic year. 5,According to the class number, inquire about the examination of students in the current semester and academic year. 6,According to the course inquiry current semester and academic year student's choice of course and examination situation. 7,Inquire the teaching situation of teachers according to departments and professional titles. 8,Count the number of teachers with various titles in each department. 9,Count the number of courses selected for each course in the current semester and academic year. 10,The average scores of each student in the current semester and academic year are calculated by class. 11,The top three students with the highest average scores in each class are counted by class. */ --1,According to the Department, major, class and other information to query the basic information of students. --SELECT Sdepart FROM Student GROUP BY Sdepart; SELECT Sdepart, Smajor, SclassNo FROM Student GROUP BY Sdepart, Smajor, SclassNo;
3. New view
3.1, 6 table general view
3.2. Total credits (student number, credits)
3.3. Total credit statement
4. Query exercise
Hope to help you!!!