-
20200325 数据库连接查询面试题作业
2020-07-24 10:576 查询出语文最高分
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; -
20200325 数据库连接查询面试题作业
2020-07-24 10:571:
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 = '孤独求败' -
20200324 连接查询作业
2020-07-24 10:571、查询张姓员工的员工信息和所在部门信息。
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
-
20200324 连接查询作业
2020-07-24 10:571: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
-
20200324 连接查询作业
2020-07-24 10:57第一题: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
-
20200324 连接查询作业
2020-07-24 10:57根据课上的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; -
使用存储过程构造测试数据
2020-07-24 10:57你好罗杰老师,请过目一下,看看是问题出在哪吧,我只不过改成插入了1000条数据而已,如果是固定10000也不行,前面的单条数据倒是插入成功,剩下的或许是我哪里理解错了吧,麻烦老师了
单条数据顺利插入
-
20200324 连接查询作业
2020-07-24 10:57
太不容易了~~没有工具只能去网上马不停蹄地找工具然后下载安装(mysql服务器&客户端、navicat工具);
下载并成功安装后才开始写SQL语句!
我太难了😂 😂 😂 -
20200324 连接查询作业
2020-07-24 10:57太不容易了,没有工具 去网上下载安装(mysql服务器&客户端、navicat工具);
下载并成功安装后才开始写SQL语句!
我太难了😂 -
20200324 连接查询作业
2020-07-24 10:571、查询张姓员工的员工信息和所在部门信息。
select * from emp left join dept on emp.dept_id = dept.id where emp.name like '张%'
2、查询张三丰管理了几个员工
select count(*) from emp e Inner join emp on e.id=emp.leader where emp.leader=1
3、查询出所有实习员工(实习员工无部门信息)
select * from emp left join dept on emp.dept_id = dept.id where dept.id is null -
20200324 连接查询作业
2020-07-24 10:57-- 1、查询张姓员工的员工信息和所在部门信息
SELECT e.
name
,e.leader,e.is_enable,dt.name
as 部门 fromemp
e
left JOIN dept dt on e.dept_id=dt.id
where e.name
like '张%';-- 查询张三丰管理了几个员工
#方法一:
SELECT count(*) from emp ep where ep.leader in(SELECT id from emp where
name
='张三丰');#方法二:
SELECT count(em1.
name
) as 所管理员工数,em2.name
as 领导 from emp em1
LEFT JOIN emp em2 on em1.leader=em2.id
GROUP BY em2.name
having em2.name
='张三丰';-- 查询出所有实习员工(实习员工无部门信息)
SELECT ep.*,dt.
name
as 部门名称 from emp ep
LEFT JOIN dept dt on ep.dept_id=dt.id
where dt.name
is NULL