20190411课堂作业<MYSQL语句练习十题>

/*1===========================*/
SELECT
    s.StuId
FROM
    tblstudent AS s
WHERE
    (
        SELECT
            c.Score
        FROM
            tblscore AS c
        WHERE
            c.CourseId = '001'
        AND s.StuId = c.StuId
    ) > (
        SELECT
            c1.Score
        FROM
            tblscore AS c1
        WHERE
            c1.CourseId = '002'
        AND s.StuId = c1.StuId
    )

/*2===========================*/
SELECT 
    StuId,AVG(score) AS avg
FROM
    tblscore
GROUP BY
    StuId
HAVING
    avg>60

/*3========难===================*/
SELECT 
s.StuSex,COUNT(1)
FROM
tblstudent AS s ,
tblscore AS c WHERE c.CourseId='001' AND s.StuId=c.StuId
GROUP BY
StuSex

/*4===========================*/
SELECT 
    COUNT(1)
FROM
    tblteacher AS tch
GROUP BY
    tch.TeaName
HAVING
    tch.TeaName LIKE '李%'
/*4_2*/
SELECT 
    COUNT(1)
FROM
    tblteacher AS tch
WHERE
    tch.TeaName LIKE '李%'


/*5==========不够简化=================*/
SELECT
    c.StuId,r.CourseName,t.TeaName,s1.StuName
FROM
    tblcourse AS r
JOIN 
    tblscore AS c
ON 
    r.CourseId = c.CourseId
JOIN
    tblteacher AS t
ON
  t.TeaId = r.TeaId
JOIN
    tblstudent AS s1
ON
  s1.StuId = c.StuId
WHERE 
    c.StuId in (SELECT s.StuId FROM tblstudent AS s WHERE s.StuName='张无忌')

/*6===========================*/
/*连表查询*/
SELECT 
    r.CourseName,t.TeaName,AVG(c.score)
FROM
    tblcourse AS r
JOIN
    tblteacher AS t
ON
    r.TeaId = t.TeaId
JOIN 
    tblscore AS c
ON
    c.CourseId = r.CourseId
GROUP BY
    c.CourseId


/*7===========================*/
/*连表查询*/
SELECT
    s.StuId,s.StuName
FROM
    tblstudent AS s
LEFT JOIN
    tblscore AS c
ON
    c.StuId = s.StuId
WHERE
    CourseId in 
('001','002')
GROUP BY
    s.StuName
HAVING
    COUNT(1)=2

/*8===========================*/
/*连表查询*/
SELECT s.StuId,StuName FROM tblstudent AS s
WHERE
    (SELECT c1.score FROM tblscore AS c1 WHERE c1.courseId in ('002') AND c1.stuid = s.StuId)<
(SELECT c2.score FROM tblscore as c2 WHERE c2.courseId in ('001') AND c2.stuid = s.StuId)

/*9===========================*/
/*这题没有答案,假设学满5科以上的*/
select s.StuId,StuName FROM tblstudent s
WHERE
s.StuId in
(select stuid FROM tblscore cor
GROUP BY cor.stuid 
HAVING
COUNT(1)>5)

/*10===========================*/
select s.StuId,StuName,cor1.courseid,coursename FROM tblstudent s
join tblscore as cor1
on cor1.stuid = s.stuid
join tblcourse as c
on c.courseid = cor1.courseid
WHERE
s.stuid in (select cor.stuid from tblscore as cor GROUP BY cor.stuid,cor.courseid  HAVING COUNT(1)>1)
and 
cor1.courseid in (cor2.courseid from tblscore as cor2 GROUP BY cor2.stuid,cor2.courseid  HAVING COUNT(1)>1)
GROUP BY 
s.StuId,c.courseid

/*11===========================*/
SELECT s.stuid,s.stuname,sum(cor.score)
FROM tblstudent as s
LEFT JOIN tblscore as cor
ON s.stuid = cor.stuid
GROUP BY 
s.stuid

/*12===========================*/
create view tianmin_view as
select s.stuid,s.stuname,tea.teaid,tea.teaname,
cour.courseid,cour.coursename,cor.score
from tblstudent as s 
join tblscore as cor
on s.stuid = cor.stuid
join tblcourse as cour 
on cour.courseid = cor.courseid
join tblteacher as tea
on tea.teaid = cour.teaid


select * from tianmin_view;


/*13===========================*/
select cour1.courseid,cour1.coursename FROM tblcourse as cour1
where 
cour1.courseid not in (

select cour.courseid FROM tblcourse as cour
join tblscore as cor
ON cour.courseid = cor.courseid
WHERE
cor.stuid in (select s.stuid from tblstudent as s where s.StuName in ('周芷若'))
)

/*14===========================*/
select * from tblstudent as s 
join tblscore as cor
on s.stuid = cor.stuid
where 
cor.courseid in (
select cor1.courseid from tblscore as cor1 where cor1.stuid in (
select s1.stuid from tblstudent as s1 where s1.stuname in ('周芷若'))
)
group by
s.stuid