Database Principles Xidian University (third edition) Tingting the third chapter after the lesson the answer

CREATE TABLE student_t (

Sno Char (7) PRIMARY KEY,--study number

Sname Varchar () not NULL,--name

Ssex CHAR (2) Not NULL,--gender

Sage Smallint,--age

Clon CHAR (5)--Number of students ' classes

);

CREATE TABLE course_t (

CNO CHAR (1) PRIMARY KEY,--Course number

CNAME Varchar (not NULL)--course Name

Credit SMALLINT--credits

);

CREATE TABLE class_t (

Clno CHAR (5) PRIMARY KEY,--Class number

Speciality VARCHAR () not NULL,--editor's professional

Inyear CHAR (4) Not NULL,--in-school year

CNUM INTEGER,--Class size

Monitor_no CHAR (7)--Class leader study number

);

CREATE TABLE grade_t (

Sno Char (7),--School number

CNO CHAR (1) Not NULL,--Course number

Gmark NUMERIC (4,1)--Results

);

INSERT into student_t (Sno,sname,ssex,sage, Clon) VALUES (' 2000101 ', ' Li Yong ', ' Male ', 20, ' 00311 ');

INSERT into student_t (Sno,sname,ssex,sage, Clon) VALUES (' 2000102 ', ' Liu Shihen ', ' female ', 19, ' 00311 ');

INSERT into student_t (Sno,sname,ssex,sage, Clon) VALUES (' 2000103 ', ' Wang Yiming ', ' Male ', 20, ' 00312 ');

INSERT into student_t (Sno,sname,ssex,sage, Clon) VALUES (' 2000104 ', ' tingting Zhang ', ' female ', 21, ' 00312 ');

INSERT into student_t (Sno,sname,ssex,sage, Clon) VALUES (' 2001101 ', ' Li Yongmin ', ' female ', 19, ' 01311 ');

INSERT into student_t (Sno,sname,ssex,sage, Clon) VALUES (' 2001102 ', ' Jia Xiangdong ', ' Male ', 22, ' 01311 ');

INSERT into student_t (Sno,sname,ssex,sage, Clon) VALUES (' 2001103 ', ' Chen Baoyu ', ' Male ', 20, ' 01311 ');

INSERT into student_t (Sno,sname,ssex,sage, Clon) VALUES (' 2001104 ', ' Zhang Yifan ', ' Male ', 21, ' 01311 ');

--The following data is added for the purpose of 12.9 queries to the data

INSERT into student_t (Sno,sname,ssex,sage, Clon) VALUES (' 2000105 ', ' folding ', ' male ', 20, ' 00311 ');

INSERT into course_t (cno,cname,credit) VALUES (' 1 ', ' database ', 4);

INSERT into course_t (cno,cname,credit) VALUES (' 2 ', ' Discrete Math ', 3);

INSERT into course_t (cno,cname,credit) VALUES (' 3 ', ' Management Information System ', 2);

INSERT into course_t (cno,cname,credit) VALUES (' 4 ', ' Operating system ', 4);

INSERT into course_t (cno,cname,credit) VALUES (' 5 ', ' Data structure ', 4);

INSERT into course_t (cno,cname,credit) VALUES (' 6 ', ' Data processing ', 2);

INSERT into course_t (cno,cname,credit) VALUES (' 7 ', ' C language ', 4);

INSERT into class_t (Clno, Speciality,inyear,cnum,monitor_no) VALUES (' 00311 ', ' computer Software ', ' 2000 ', 120, ' 2000101 ');

INSERT into class_t (Clno, Speciality,inyear,cnum,monitor_no) VALUES (' 00312 ', ' Computer Application ', ' 2000 ', 140, ' 2000103 ');

INSERT into class_t (Clno, Speciality,inyear,cnum,monitor_no) VALUES (' 01311 ', ' computer Software ', ' 2001 ', 220, ' 2001103 ');

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2000101 ', ' 1 ', 92);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2000101 ', ' 3 ', 88);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2000101 ', ' 5 ', 86);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2000102 ', ' 1 ', 78);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2000102 ', ' 6 ', 55);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2000103 ', ' 3 ', 65);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2000103 ', ' 6 ', 78);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2000103 ', ' 5 ', 66);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2000104 ', ' 1 ', 54);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2000104 ', ' 6 ', 83);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2001101 ', ' 2 ', 70);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2001101 ', ' 4 ', 65);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2001102 ', ' 2 ', 80);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2001102 ', ' 4 ', 90);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2000103 ', ' 1 ', 83);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2000103 ', ' 2 ', 76);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2000103 ', ' 4 ', 56);

INSERT into grade_t (Sno, Cno,gmark) VALUES (' 2000103 ', ' 7 ', 88);

--12.1 Find out all the course numbers taken by the students

SELECT DISTINCT CNO from grade_t gt ORDER by GT.CNO;

--12.2 Find out the personal information of 01311 classes of female students

SELECT * from student_t stu WHERE Stu.clon = ' 01311 ' and stu.ssex = ' female ';

--12.3 find out the names, gender, and year of birth of 01311 and 01312 class students

SELECT STU. SNAME,

STU. SSEX,

(To_date (To_char (sysdate, ' yyyy/mm/dd '), ' yyyy/mm/dd ')-STU. SAGE) as Year of birth

From student_t STU

WHERE STU. Clon in (' 01311 ', ' 01312 ');

--12.4 Find out the personal information of all the students surnamed Li

SELECT * from student_t stu WHERE stu.sname like ' li% ';

--12.5 find out the number of students in Li Yong's class

SELECT Ct.cnum from student_t Stu joins class_t ct on stu.clon = ct.clno WHERE stu.sname = ' Li Yong ';

-or sub-query (first query out the class number of Li Yong, pay attention to use distinct, to prevent multiple Li Yong error)

Select ct.cnum from class_t ct where ct.clno = (SELECT DISTINCT stu.clon from student_t stu where stu.sname = ' Li Yong ');

--12.6 find out the average score, highest score, and minimum score for the course named operating system

SELECT To_char (AVG (GT). Gmark), ' 99999999999999.99 ') as average score,

MAX (GT. Gmark) as the highest score,

MIN (GT. Gmark) as lowest score

From course_t COU joins grade_t GT on COU. CNO = GT. CNO

WHERE COU. CNAME = ' operating system ';

--12.7 find out the number of students who have elective courses

Select COUNT (1) from (select DISTINCT gt.sno from grade_t GT);

-OR

Select COUNT (1) from (select Gt.sno, Count (gt.sno) from grade_t GT GROUP by Gt.sno);

--12.8 find out the number of students who have elective courses for the operating system

SELECT COUNT (1) from course_t CT JOIN grade_t gt on ct.cno = gt.cno WHERE ct.cname = ' operating system ';

--12.9 find out the student's name of the grade 2000 computer software class is empty

SELECT STU. SNAME

From student_t STU

Left joins class_t CT on STU. Clon = CT. Clno

Left JOIN grade_t gt on gt.sno = Stu.sno

WHERE CT. Inyear = ' 2000 '

and CT. Speciality = ' computer software '

and Gt.gmark is NULL;

--13.1 find information about students in the same class as Li Yong

SELECT * from student_t stu where Stu.clon = (SELECT clon from student_t where sname = ' Li Yong ');

--13.2 Find out all students who have the same elective course as student Li Yong

SELECT DISTINCT stu.*

From student_t STU

JOIN grade_t GT

On STU. SNO = GT. SNO

WHERE GT. CNO in

(SELECT GT. CNO

From grade_t

WHERE SNO =

(SELECT SNO from student_t WHERE SNAME = ' Li Yong '));

--13.3 find information about students aged between Li Yong and 25 years old

SELECT * from student_t Stu where stu.sage between (select Sage from student_t where sname = ' Li Yong ') and 25;

--13.4 find the student's number and name for the course operating system.

SELECT Stu.sno,stu.sname

From student_t STU

JOIN grade_t GT

On STU. SNO = GT. SNO

JOIN course_t COU

On COU. CNO = GT. CNO

WHERE COU. CNAME = ' operating system ';

--13.5 find out the names of all the students who did not take the No. 1th course

Select Stu.sname from student_t Stu where Stu.sno isn't in (SELECT Gt.sno from grade_t gt WHERE gt.cno = 1);

--13.6 Find out the name of the student who took all the courses

SELECT Stu.sname

From student_t STU

JOIN grade_t GT

On STU. SNO = GT. SNO

GROUP by STU. Sno,stu. SNAME

Have count (1) = (SELECT COUNT (1) from course_t);

--14.1 Inquiry took the student number of course No. 3rd and its achievements, and ranked in descending order of grades

SELECT Stu.sno,gt.gmark from student_t Stu joins grade_t GT on Stu. SNO = GT. SNO WHERE gt.cno = 3 ORDER by Gt.gmark;

--14.2 query All student information, the query results by class number in ascending order, the same class students in descending order by age

SELECT * from student_t stu ORDER by Stu.clon,stu.sage DESC;

--14.3 for each course number and the corresponding number of elective courses

SELECT Cou.cno,count (1) from course_t cou left JOIN grade_t gt on cou.cno = Gt.cno GROUP by Cou.cno ORDER by Cou.cno;

--14.4 find out the number of students enrolled in more than 3 courses

SELECT Gt.sno from grade_t gt GROUP by Gt.sno have count (1) >3;

Xi ' an University of Science and Technology High School ITCSC 1001 class Folding

This article is from the "Mickle" blog, make sure to keep this source http://sheting.blog.51cto.com/4838048/1597021

Database Principles Xidian University (third edition) Tingting the third chapter after the lesson the answer