SQL Server Exercises 2

Server

Topic 2

Problem Description:
Known relationship pattern:
S (sno,sname) student relationship. SNO is the school number, sname is the name
C (cno,cname,cteacher) course relationship. CNO is the course number, CNAME is the course name, Cteacher is the instructor
SC (Sno,cno,scgrade) elective relationship. Scgrade for grades

The following 5 processing requirements are achieved:
1. Find out the names of all the students who have not been enrolled in the course by Li Ming Teacher
2. List the names of students with two or more (including two) failing courses and their average grades
3. List the names of all students who have studied "1" and have studied the "2" course
4. List the number of students who scored "1" in the course of "2".
5. List the student numbers of all students with "1" score higher than the "2" course and their "1" and "2" course scores.

1. Find out the names of all the students who have not been enrolled in the course by Li Ming Teacher
--Implementation code:
SELECT sname from S
WHERE not EXISTS (
SELECT * from Sc,c
WHERE SC. Cno=c.cno
And C.cteacher= ' Li Ming '
and SC. SNO=S.SNO)

2. List the names of students with two or more (including two) failing courses and their average grades
--Implementation code:
SELECT S.sno,s.sname,avg_scgrade=avg (SC. Scgrade)
From S,SC, (
SELECT SNO
From SC
WHERE scgrade<60
GROUP by SNO
Having COUNT (DISTINCT CNO) >=2
A WHERE S.sno=a.sno and SC. Sno=a.sno
GROUP by S.sno,s.sname

3. List the names of all students who have studied the "1" course and have studied "2"
--Implementation code:
SELECT S.sno,s.sname
From S, (
SELECT SC. SNO
From Sc,c
WHERE SC. Cno=c.cno
and C.cname in (' 1 ', ' 2 ')
GROUP by SNO
Having COUNT (DISTINCT CNO) =2
) SC WHERE S.SNO=SC. SNO

4. List the student numbers of all students who have scored higher in the course of "1" than "2"
--Implementation code:
SELECT S.sno,s.sname
From S,SC SC1,SC SC2
WHERE SC1. cno= ' 1 '
and SC2. sno= ' 2 '
and SC1. Cno=s.cno
and SC1. SCGRADE>SC2. Scgrade

5. List the number of all students with a "1" course score higher than the "2" course and their "1" and "2" course results
--Implementation code:
SELECT SC1. Sno,[1 the result of the class]=SC1. Scgrade,[2 the result of the class]=SC2. Scgrade
From SC SC1,SC SC2
WHERE SC1. cno= ' 1 '
and SC2. cno= ' 2 '
and SC1. SNO=SC2. SNO
and SC1. SCGRADE>SC2. Scgrade

trackback:http://tb.blog.csdn.net/trackback.aspx?postid=384993

[Click here to collect this article] posted on May 31, 2005 17:31:00

Ten years waiting to be published in 2005-06-06 12:04 PM ip:61.186.252.*
How are you doing:

1. Find out the names of all the students who have not been enrolled in the course by Li Ming Teacher

SELECT sname from S
WHERE not EXISTS (
SELECT * from Sc,c <= do you want to add s here?
WHERE SC. Cno=c.cno
And Cname= ' Li Ming ' <= should be cteacher = ' Li Ming '.
and SC. SNO=S.SNO)

Ten years waiting to be published in 2005-06-06 12:47 PM ip:61.186.252.*
List the number of students who scored "1" in the course of "2".
SELECT S.sno,s.sname
From S, (
SELECT SC1. SNO
From SC sc1,c c1,sc sc2,c C2
WHERE SC1. Cno=c1. CNO and c1.name= ' 1 '
and SC2. Cno=c2. CNO and C2.name= ' 2 '
<= It looks like irrelevant here??
<= is and SC2. Cno=c2. CNO and SC2. sno= ' 2 ', right?
and SC1. SCGRADE>SC2. Scgrade
) SC WHERE S.SNO=SC. SNO

and "1" class, my understanding is course ID, that is, CNO, how can be cname, and here you also wrote a c1.name, this attitude is not to AH

I am a novice, the general is the Internet to check the information, but fortunately before a little bit, or for those who will not be the landlord killed, or do not say, or must have a serious attitude, frankly, please landlord understanding

GHB published in 2005-11-14 12:04 PM ip:61.236.10.*
/*
Problem Description:
Known relationship pattern:
S (sno,sname) student relationship. SNO is the school number, sname is the name
C (cno,cname,cteacher) course relationship. CNO is the course number, CNAME is the course name, Cteacher is the instructor
SC (Sno,cno,scgrade) elective relationship. Scgrade for grades

The following 5 processing requirements are achieved:
1. Find out the names of all the students who have not been enrolled in the course by Li Ming Teacher
2. List the names of students with two or more (including two) failing courses and their average grades
3. List the names of all students who have studied "1" and have studied the "2" course
4. List the number of students who scored "1" in the course of "2".
5. List the student numbers of all students with "1" score higher than the "2" course and their "1" and "2" course scores.
*/

--create table S (Sno varchar), sname varchar (20))
--create Table C (cno varchar (a), CNAME varchar (a), Cteacher varchar (20))
--create Table SC (sno varchar (), CNO varchar (), scgrade Integer)

INSERT INTO S
Select ' 1 ', ' GHB '
UNION ALL SELECT ' 2 ', ' TW '
UNION ALL SELECT ' 3 ', ' WKP '

INSERT INTO C
Select ' 1 ', ' Chinese ', ' Li Ming '
UNION ALL SELECT ' 2 ', ' mathematics ', ' King's '
UNION ALL SELECT ' 3 ', ' English ', ' other '

INSERT INTO SC
Select ' 1 ', ' 2 ', 50
UNION ALL SELECT ' 1 ', ' 3 ', 52
UNION ALL SELECT ' 2 ', ' 1 ', 80
UNION ALL SELECT ' 2 ', ' 2 ', 90
UNION ALL SELECT ' 2 ', ' 3 ', 59
UNION ALL SELECT ' 3 ', ' 1 ', 100
UNION ALL SELECT ' 3 ', ' 2 ', 59
UNION ALL SELECT ' 3 ', ' 3 ', 70

--delete from SC where sno = ' 1 ' and CNO = ' 1 '
SELECT *
from S
SELECT *
From c
SELECT *
From SC
--1. Find out the names of all the students who have not been enrolled in the course by Li Ming Teacher
Select Sname
from S
where S.sno not in (select Sc.sno from sc,c where sc.cno = c.cno and C.cteacher = ' liming ')

SELECT sname from S
WHERE not EXISTS (
SELECT * from Sc,c
WHERE SC. Cno=c.cno
And C.cteacher= ' Li Ming '
and SC. SNO=S.SNO)

--2. List the names of students with two or more (including two) failing courses and their average grades
Select S.sname,avg (sc.scgrade) as AVGSC
From S,SC
where S.sno = Sc.sno and S.sno in (select Sno from SC where Scgrade < a group by Sno having Count (Sno) >= 2)
GROUP BY S.sname

Select S.sname,avg (SC. Scgrade) from S,SC where SC. Scgrade<60 and S.sno=sc.sno GROUP by S.sname has the having count (SC. Scgrade) >=2

SELECT S.sno,s.sname,avg_scgrade=avg (SC. Scgrade)
From S,SC, (
SELECT SNO
From SC
WHERE scgrade<60
GROUP by SNO
Having COUNT (DISTINCT CNO) >=2
A WHERE S.sno=a.sno and SC. Sno=a.sno
GROUP by S.sno,s.sname

--3. List the names of all students who have studied "1" and have studied the "2" course
Select Tem.sname
From
(
Select S.sname
from S
where S.sno in (select Sno from sc where cno = ' 1 ')
UNION ALL
Select S.sname
from S
where S.sno in (select Sno from sc where cno = ' 2 ')
) TEM
GROUP BY Tem.sname
Having count (tem.sname) > 1

Select S.sname from S,SC where S.sno=sc.sno and Sc.cno=1 and Sc.sno in (select B.sno from SC b where B.sno=sc.sno and b.cn o=2)

Select S.sname from S,SC where S.sno=sc.sno and sc.cno=1 and exists (select B.sno from SC b where B.sno=sc.sno and b.cno=2 )

SELECT S.sno,s.sname
From S, (
SELECT SC. SNO
From Sc,c
WHERE SC. Cno=c.cno
and C.cno in (' 1 ', ' 2 ')
GROUP by SNO
Having COUNT (DISTINCT c.cno) =2
) SC WHERE S.SNO=SC. SNO

--4. List the number of students who scored "1" in the course of "2".
Select Sc.sno
From SC
where cno = ' 1 ' and Scgrade > (select Scgrade from sc where sno = ' 2 ' and CNO = ' 1 ') and Sno <> ' 2 '

Select Sc.sno from sc where sc.cno = ' 1 ' and exists (SELECT * from SC b where B.sno = Sc.sno and Sc.scgrade > B.scgrade and B.sno = ' 2 ')

Select S.sno from S,SC where S.sno=sc.sno and Sc.cno=1 and SC. Scgrade> (select B.scgrade from SC b where B.sno=sc.sno and b.cno=2)

SELECT S.sno,s.sname
From S,SC SC1,SC SC2
WHERE SC1. cno= ' 1 '
and SC2. sno= ' 2 '
and SC1. Cno=s.cno
and SC1. SCGRADE>SC2. Scgrade

Select Sc1.sno
From SC sc1,sc SC2
where sc1.cno = ' 1 ' and Sc1.sno <> ' 2 ' and sc2.cno = ' 1 ' and Sc2.sno = ' 2 ' and Sc1.scgrade > Sc2.scgrade and SC1. CNO = Sc2.cno

--5. List the student numbers of all students with "1" score higher than the "2" course and their "1" and "2" course scores.
Select Sc1.sno,sc1.scgrade,sc2.scgrade
From SC sc1,sc SC2
where Sc1.sno = Sc2.sno and sc1.cno = ' 1 ' and sc2.cno = ' 2 ' and Sc1.scgrade > Sc2.scgrade

Select A.sno,a.scgrade from SC a WHERE (a.cno=1 or a.cno=2)
and A.sno in
(select S.sno from S,SC where S.sno=sc.sno and Sc.cno=1 and SC.) Scgrade> (select B.scgrade from SC b where B.sno=sc.sno and b.cno=2))

SELECT SC1. Sno,[1 the result of the class]=SC1. Scgrade,[2 the result of the class]=SC2. Scgrade
From SC SC1,SC SC2
WHERE SC1. cno= ' 1 '
and SC2. cno= ' 2 '
and SC1. SNO=SC2. SNO
and SC1. SCGRADE>SC2. Scgrade

drop TABLE S
drop TABLE C
DROP TABLE SC

Shenjane published in 2006-02-07 3:13 PM ip:210.22.152.*
There are some mistakes in question fifth.
SELECT S.SN,SC1. Scgrade as Course 1,sc2. Scgrade as course 2 from S,SC sc1,sc SC2 WHERE
S.sno = SC1. SNO and SC1. cno= ' 0001 ' and SC2. cno= ' 0002 '
and SC1. SNO=SC2. SNO and SC1. SCGRADE<SC2. Scgrade