PTA-SQL-答案

发布时间：2023-11-07 13:55
修改时间：2023-11-15 19:29
【作者：無同 | 未经许可，请勿转载！】

目录

1.

select product_id,product_name,sale_price from Product where product_id not in
(select product_id from ShopProduct where shop_id='000C')

2.

select product_name,quantity,shop_name
from Product a join ShopProduct b
on a.product_id=b.product_id and a.product_id='0003' and b.product_id='0003'

3.

select product_name,quantity
from Product full join ShopProduct
on Product.product_id = ShopProduct.product_id
where quantity is NULL

4.

select * from dept

5.

select DId,DName from dept

6.

select TId,TName,TSexy from teacher where TSexy='女'

7.

select * from teacher where DId='CS'

8.

select * from teacher where DId='CS' or DId='IT'

9.

select TName,TSexy,TProf,DId from teacher where TSexy='女' and TProf='教授'

10.

select TName,TField,TTele,DId from teacher where TField='数据库'

11.

select TName,TBdate from teacher
where year(TBdate)>1983

12.

select TName,TSexy,TBdate,TTele from teacher
where month(TBdate)=5

13.

select SId from sc
where CId=1

14.

select SId from sc
where CId=1 or CId=2
group by SId
having count(CId)>1

15.

select CId from sc
where SId=012005002

16.

select * from sc
where SId=012005002

17.

select count(*)'Num' from sc
where CId=1

18.

select round(avg(SCScore3),2)'Avg' from sc
where CId=1

19.

select min(SCScore3)'Mask' from sc
where SId=012005002

20.

select CId,round(avg(SCScore3),2)'Avg' from sc
where SCTerm like '2008%'
group by CId
order by avg(SCScore3) desc

21.

select IRoom from information
where IWeek=4

22.

select TId from information
where IWeek=4

23.

select * from information
where IRoom='NB222'

24.

select IRoom,TId,IWeek,ITimeseg from information
where CId=1

25.

select IRoom,IWeek,ITimeseg from information
where TId='02004'

26.

select IRoom,CId,TId from information
where ITimeseg like '%4%'

27.

select TId,count(*)'Num',(45*sum(length(ITimeseg)))'Len' from information
group by TId

28.

select GId,sum(length(ITimeseg))'Hours' from information
group by GId
order by sum(length(ITimeseg)) desc

29.

select CId,CName from course
where DId='CS'

30.

select CName,CHour from course
where CCredit>3

31.

select DId,sum(CCredit)'Credit' from course
group by DId
order by sum(CCredit) desc

32.

select * from student
where SSexy='男'

33.

select * from student
where GId='01'

34.

select * from student
where GId='03' and SSexy='女'

35.

select STele from student
where SName='刘山'

36.

select SId,SName,GId from student
where SSexy='女'

37.

select GId,count(*)'Num' from student
group by GId
order by count(*) desc

38.

select * from student
where (2023-year(SBdate))<18

39.

select count(*)'Num' from student
where (2023-year(SBdate))<18

40.

select SName,SSexy,GName,STele
from student inner join grade
on student.GId=grade.GId
where SName like '白%'

41.

select GName,GYear from grade
where DId='CS'

42.

select * from grade
where GYear='2008'

43.

select SId,SName,SSexy,TIMESTAMPDIFF(year,SBdate,'2023-09-01')'Age',GName,GYear
from student inner join grade
on student.GId=grade.GId
where grade.GName like '计算机科学与技术%'

44.

select student.SId,SName
from (sc inner join student
on sc.SId=student.SId)
inner join course
on sc.CId=course.CId
where CName='数据库'

45.

select TName,TTele
from (grade inner join student
on grade.GId=student.GId)
inner join teacher
on grade.TId=teacher.TId
where SName='白云'

46.

select DName,DAddr,DTele
from (grade inner join student
on grade.GId=student.GId)
inner join dept
on grade.DId=dept.DId
where SName='白云'

47.

SELECT student.SId, SName, SUM(CCredit)'Credit'
FROM student,
     grade,
     dept,
     course,
     sc
WHERE student.GId = grade.GId
  AND grade.DId = dept.DId
  AND dept.DName = '计算机科学与技术系'
  AND sc.SId = student.SId
  AND SCScore >= 60
  AND sc.CId = course.CId
GROUP BY student.SId,SName

48.

SELECT course.CId,course.CName,sc.SCScore3
FROM student,sc,course
WHERE student.SName = '张飞'
AND student.SId = sc.SId
AND sc.CId = course.CId

49.

SELECT GName, GYear, TName, TTele
FROM grade,
     dept,
     teacher
WHERE (dept.DName = '计算机科学与技术系' OR dept.DName = '信息科学与技术系')
  AND dept.DId = grade.DId
  AND grade.TId = teacher.TId

50.

SELECT student.SId, SName, grade.GName
FROM student,
     grade
WHERE grade.GYear = 2008
  AND student.GId = grade.GId

51.

select student.SId,SName,SCScore3
from sc,student,course
where sc.SId=student.SId
and sc.CId=course.CId
and CName='数据库'

52.

select CName,round(avg(SCScore3),2)'Avg'
from sc,course
where sc.CId=course.CId
group by CName
having avg(SCScore3)>75

53.

select student.SId,SName,round(avg(SCScore3),2)'Avg'
from sc,student
where sc.SId=student.SId
group by sc.SId,SName
having avg(SCScore3)>80

54.

select TName,TField,sum(length(ITimeseg))'Hours'
from teacher,information
where teacher.TId=information.TId
group by teacher.TId,TName,TField
having sum(length(ITimeseg))>5

55.

select GName,round(avg(SCScore3),2)'Avg'
from student,grade,sc
where student.GId=grade.GId
and student.SId=sc.SId
group by GName

56.

select SCTerm,round(avg(SCScore3),2)'Avg'
from sc,student,grade
where sc.SId=student.SId
and student.GId=grade.GId
and DId='CS'
group by SCTerm

57.

select DName,sum(length(ITimeseg))'Hours'
from information,dept,course
where course.DId=dept.DId
and course.CId=information.CId
group by DName

58.

select SId,SName,GName
from grade,student
where grade.GId=student.GId
and SId not in (select distinct SId from sc)

59.

select student.SId,SName,STele
from student,sc,teacher,information
where student.SId=sc.SId
and sc.CId=information.CId
and information.TId=teacher.TId
and TName='李飞'
group by student.SId,SName,STele

60.

select CName,CCredit
from information,course
where information.CId=course.CId
group by CName,CCredit
having sum(length(ITimeseg))>=6

61.

select GName
from information,grade
where information.GId=grade.GId
group by GName
having sum(length(ITimeseg))>=6

62.

select SId,SName,GName
from information,student,grade
where information.GId=student.GId
and information.GId=grade.GId
and IWeek='4'
and ITimeseg like '%3%'
group by SId,SName,GName

63.

select GName
from grade
where GName not in
(select GName
from sc,student,grade
where sc.SId=student.SId
and student.GId=grade.GId
and SCScore3<60
group by GName)

64.

select student.SId,SName,GName
from student,course,sc,grade
where grade.GId=student.GId
and student.SId=sc.SId
and sc.CId=course.CId
and CName='数据库'

65.

select student.SId,SName,count(*)'Num'
from student,course,sc
where student.SId=sc.SId
and sc.CId=course.CId
and SCScore2<60
group by student.SId,SName
having count(*)>1

66.

select GName,max(SCScore3)'Max'
from student,sc,grade
where grade.GId=student.GId
and student.SId=sc.SId
group by GName

67.

select student.SId,SName,GName
from student,grade,information
where information.GId=student.GId
and information.GId=grade.GId
group by student.SId,SName,GName
having sum(length(ITimeseg))>=8

68.

select IWeek,ITimeseg,IRoom,CName
from course,grade,information
where information.GId=grade.GId
and information.CId=course.CId
and GName='计算机科学与技术1班'

69.

select TName,sum(length(ITimeseg))'Hours'
from information,teacher
where information.TId=teacher.TId
and TProf='教授'
group by TName

70.

select GName,DName
from grade,dept
where grade.DId=dept.DId
and TId is NULL

71.

select TName,GName
from teacher,grade
where teacher.TId=grade.TId
and TName in
(select TName
from teacher,grade
where teacher.TId=grade.TId
and TProf='讲师'
group by TName
having count(*)>2)

72.

INSERT INTO sc
    (SCId, SId, CId, SCTerm)
SELECT (@row_number:=@row_number+1) as SCId, student.SId, '4' as CId, '2009-2010-1' as SCTerm
FROM student,
     grade,
     (SELECT @row_number:=MAX(SCId) FROM sc) as a
WHERE student.GId = grade.GId
  AND GName = '计算机科学与技术3班'

73.

update student
set STele = '661010'
where SName = '李飞'

74.

update sc
set SCScore='70'
where SCScore1>90
and SCScore<70

75.

update grade
set TId=
  (select TId
  from teacher
  where TName='李丽青')
where GName='电子信息1班'

76.

delete from sc
where CId=
  (select CId
   from course
   where CName='数据库')

77.

delete from sc
where CId in
  (select CId from
    (select CId from sc
    group by CId
    having count(SId)<6)
    as sc1
  )

78.

delete from teacher
where TId not in
  (select TId from grade
  where TId is not NULL)
and TId not in
  (select TId from information
  where TId is not NULL)

79.

select cname from course

80.

select * from students
where ssex='男'

81.

select tname,tdept from teachers

82.

select cname from course
where cname like 'DB\_%'

83.

select sname,bplace from students
where (sname like '_小%'
or sname like '_晓%')
and ssex='男'

84.

select cname'课程名称',avg(score)'平均成绩'
from (sc join students
on sc.sno=students.sno)
join course
on sc.cno=course.cno
where class='14计科1'
group by cname

85.

select cname
from teachers,teaching,course
where teachers.tno=teaching.tno
and teaching.cno=course.cno
and tname='李琳'

86.

select sname,cno,score
from students,sc
where students.sno=sc.sno
and score>60
and ssex='男'

87.

select students.sno,sname,score
from students,sc,course
where students.sno=sc.sno
and course.cno=sc.cno
and cname='高等数学'

88.

select students.sno
from students,sc
where students.sno=sc.sno
and (cno='0000008'
or cno='0000007')
group by students.sno
having count(*)>1

89.

select cname
from students,sc,course
where students.sno=sc.sno
and sc.cno=course.cno
and sname='陈红'

90.

select cno,cname
from course
where cno in
(select teaching.cno
from teachers,teaching
where teachers.tno=teaching.tno
and tsex='男')

91.

select cname
from course
where cno in
(select cno
from students,sc
where students.sno=sc.sno
and class='17计应1')

92.

select sno,score
from course,sc
where course.cno=sc.cno
and cname like '%电子%'
and score<60

93.

select class'班级',count(*)'人数'
from students,sc
where students.sno=sc.sno
and cno='0000011'
group by class

94.

select students.sno,sname
from students,sc,course
where students.sno=sc.sno
and sc.cno=course.cno
and cname='数据库OCP考证'

95.

select sc.cno,sno,score
from teachers,teaching,sc
where teachers.tno=teaching.tno
and teaching.cno=sc.cno
and tname='严蔚敏'
group by sc.cno,sno,score

96.

select sname
from students
where sdept in
(select sdept
from students
where sname='陈红')

97.

select sno,sname,class
from students
where sno not in
(select sno
from sc)

98.

select sname
from students,sc
where students.sno=sc.sno
and (cno='0000008'
or cno='0000007')
group by students.sno
having count(*)>1

99.

select sno,score
from sc
where score>
(select avg(score)
from sc)

100.

select sname,sdept
from Student
where sdept in
(select sdept
from Student
where sname='刘晨')
and sname!='刘晨'