测试派 Give testers a home.

不是很熟练，有关联之间的表如果不去熟悉会搞混，有些雷同，毕竟是最后提交的

#6 查询出语文最高分
SELECT c.Cno,c.Cname,MAX(sc.score) FROM sc 
	INNER JOIN course c ON c.Cno = sc.Cno 
	WHERE c.Cname = '语文';
#8 查询语数外三门成绩的平均分 
SELECT c.Cname,AVG(sc.score) FROM sc 
	INNER JOIN course c ON c.Cno = sc.Cno 
	GROUP BY c.Cname;
#9 查询报名孤独求败老师课程的学生信息
SELECT s.* FROM teacher t
	INNER JOIN course c ON c.Tno = t.Tno AND t.Tname = '孤独求败'
	INNER JOIN sc ON sc.Cno = c.Cno
	INNER JOIN student s ON s.Sno = sc.Sno

#6 查询出语文最高分
SELECT MAX(sc.score),course.Cname from lemon_sc as sc INNER JOIN lemon_course as course on course.Cno=sc.Cno and course.Cname like '%语文'

#8 查询语数外三门成绩的平均分
SELECT AVG(sc.score),course.Cname from lemon_sc as sc INNER JOIN lemon_course as course on course.Cno=sc.Cno GROUP BY course.Cno

#9 查询报名孤独求败老师课程的学生信息
SELECT stu.* from lemon_student as stu INNER JOIN lemon_sc as sc on stu.Sno=sc.Sno INNER JOIN lemon_course as cou on sc.Cno=cou.Cno INNER JOIN lemon_teacher as tea on tea.Tno=cou.Tno where tea.Tname ='孤独求败'

1、	查询出语文最高分 
select max(score) as '语文最高分' from sc inner join course as c on c.Cno=sc.Cno where c.Cname='语文' 
2、	查询语数外三门成绩的平均分
select c.Cname,avg(sc.score)as '平均分' from course as c inner join sc on c.Cno=sc.Cno group by c.Cno,c.Cname 
3、	查询报名孤独求败老师课程的学生信息 
select * from student as s inner join sc on s.Sno=sc.Sno inner join course as c on sc.Cno=c.Cno right join teacher as t on t.Tno=c.Tno where t.Tname='孤独求败'

1、 查询出语文最高分
select max(score) as '语文最高分' from sc inner join course as c on c.Cno=sc.Cno
where c.Cname='语文'
2、查询语数外三门成绩的平均分
select c.Cname,avg(sc.score)as '平均分' from course as c inner join sc on c.Cno=sc.Cno
group by c.Cno,c.Cname
3、查询报名孤独求败老师课程的学生信息
select * from student as s inner join sc on s.Sno=sc.Sno
inner join course as c on sc.Cno=c.Cno
right join teacher as t on t.Tno=c.Tno
where t.Tname='孤独求败'

没用，太奇怪了，就那行加了分号就当行报错，不加就下一行报错？不着急吧，老师慢慢看

CREATE DEFINER=root@localhost PROCEDURE CrateTestData()
BEGIN
declare i int default 1;
while i < 10000 do
insert into member values
(null,CONCAT('test_',i),MD5(123456),13517793136+i,1,RAND()*10000,NOW());
set i = i +1;
end while;
END
CALL crateTestData();

第二题条件不全

很棒~满分

第二题不能直接用leader=1

不容易，加油~

第一第二题能不能不用子查询解题呢？

满分~~~~

第一题有瑕疵~

我发现我一个也没写对，全错了😤

第三题有瑕疵~

第二题有瑕疵~

1、2、3题都有一些小瑕疵，可以看看其他同学怎么做的

第二题不能直接用leader=1哦~

满分~~~666

第二题需要用到分组和统计函数
第二第三题都可以参考一下其他同学的答案

图文结合，非常好，满分

满分~~满分

因为你是内连接，两个条件是可以并列的，所以where 和 and没有区别

第三题能用连接查询做出来吗？

666，满分

第三题再想想~

满分~

兄弟，怎么只提交了一题的答案？

第一题的解法有点6

满分~

第二、三题可以看看其他同学的答案

第三题有点小瑕疵

6 查询出语文最高分

select * from course a
inner join sc b on a.Cno=b.Cno
where a.Cname="语文"
order by b.score limit 0, 1 desc;

8 查询语数外三门成绩的平均分

select b.Cname, avg(a.score), count(*) from sc a
inner join course b on a.Cno=b.Cno
inner join student c on c.Sno=a.Sno
group by b.Cname, a.score;

9 查询报名独孤求败老师课程的学生信息

select * from teacher a
inner join course b on a.Tno=b.Tno and a.Tname="独孤求败"
inner join sc d on d.Sno=b.Sno
right join student c on c.Sno=d.Sno
where b.Cno is not null;

666，多种方式开拓思维~

你把 #号 还有/**/ 这些注释都去掉。

老铁，没毛病

1：
select MAX(score) from sc s inner join course c on s.Cno = c.Cno and c.Cname = '语文'

2：
select AVG(s.score),c.Cname from sc s inner join course c on s.Cno = c.Cno
GROUP BY c.Cname;

3：
SELECT * from student s inner join sc c on s.Sno = c.Sno
inner join course r on r.Cno = c.Cno
inner join teacher t on t.Tno = r.Tno where t.Tname = '孤独求败'

好的

1、查询张姓员工的员工信息和所在部门信息。

SELECT * FROM emp a LEFT JOIN dept b ON a.dept_id = b.id WHERE a.name LIKE '张%'

2、查询张三丰管理了几个员工

SELECT count(b.id) FROM emp a INNER JOIN emp b ON a.id = b.leader WHERE a.name = '张三丰'

3、查询出所有实习员工（实习员工无部门信息）

SELECT * FROM emp a LEFT JOIN dept b ON a.dept_id = b.id where b.id IS NULL

1：select * from emp inner join dept on emp.id=dept.id where emp.name like '张%'

2：
select count() from emp as a inner join emp as b on a.leader = b.id where b.name ='张三丰'
select count() from emp as a inner join emp as b on a.leader = b.id and b.name ='张三丰'
发现第二题用where 和and 的结果是一样的

3：select * from emp where dept_id = 0

1、查询张姓员工的员工信息和所在部门信息。

很抱歉，试过了，还是不行，不知道是不是那个很细的细节问题，后来我试着用最懒的方法复制老师的代码直接粘贴过去，居然还是不行，

第一题：select * from emp as a inner join dept as
b on a.dept_id=b.id where a.name like '张%';

第二题：select * from emp as a where dept_id=1 and leader=1；

不知道怎么计数

第三题：select * from emp as a where dept_id=1 and leader=1

根据课上的emp和dept表完成下列作业：
1、查询张姓员工的员工信息和所在部门信息。
select * from emp as a inner join dept as b on a.dept_id=b.id
where a.name like "张%";
2、查询张三丰管理了几个员工
select count(*) from emp as a inner join emp as b on a.id=b.leader where a.name="张三丰" and b.is_enable=1;
3、查询出所有实习员工（实习员工无部门信息）
select * from emp as a left join dept as b on a.dept_id=b.id
where b.id is null and is_enable=1;

declare i int default 1; 你缺少了分号