SQL语句面试题解

[出个Sql面试题！]--总体情况 SELECT a.ID, COUNT(*) AS 总数， SUM(CASE b.STYLE WHEN 0 THEN 1 ELSE 0 END ) AS 过关数， SUM(CASE b.STYLE WHEN 1 THEN 1 ELSE 0 END ) AS 未过关数 from a, b...+阅读

现场助？我是不是来晚了？

1、SELECT `Sc_name` FROM `score` WHERE `Sc`=0 and `Sc_number` LIKE '98%';

2、SELECT `score.Sc_name`,`score.Sc_score`,`course.Co_name` FROM `score` inner join `course` ON `score.Sc_courseid` = `course.Co_id`;

3、SELECT `Sc_name`,AVG(Sc_score) as ＂ScAVG＂ FROM `score` WHERE `Sc_score` < 60 ORDER BY 2 DESC;

4、这题不太理解什么意思。

5、a）合理使用索引； b）尽量避免使用*作为查找字段； c）使用EXISTS代替IN,NOT EXISTS代替NOT IN; d）使用>；=代替>; e）不要在索引列使用IS NULL、IS NOT NULL;

数据库SQL查询语句面试题

5.1

select a.username,b.deptname from users a,dept b where a.dept_id=b.id;

5.2

update users set dept_id='9' where dept_id='2';

5.3

select a.deptname,b.count_id from dept a,(select dept_id,count(id) as count_id from users group by dept_id having count(id)>1) b where a.id=b.dept_id;

5.4

select a.deptname,b.count_man,c.count_woman from dept a,(select dept_id,count(sex) as count_man from users where group by dept_id) b,(select dept_id,count(sex) as count_woman from users where group by dept_id) c where a.id=b.dept_id and a.id=c.dept_id;

5.5

添加历史记录表

create table history(

id number(8)， -- 记录编号

dept_id varchar2(5)， -- 部门ID

user_id varchar2(5)， -- 用户ID

change_date date -- 变动日期

);