常用的SQL语句查询练习
来源：互联网 发布：玩绝地求生网络延迟 编辑：程序博客网 时间：2024/05/22 00:22
练习1
Course表 student表
teacher表 score表
-- 1.查询“java””课程比“ php课程成绩高的所有学生的学号；
SELECT DISTINCT a.sno FROM (
-- 1.1查询php课程的成绩的学生
SELECT sc.`score`, sc.`sno` FROMcourse c, score sc WHERE c.`cid` = sc.`cno` AND cname = 'php'
) a, (
-- 1.2查询java课程的成绩
SELECT sc.`score`, sc.`sno` FROMcourse c, score sc WHERE c.`cid` = sc.`cno` AND cname = 'java'
) b
WHERE a.sno = b.sno AND a.score <b.score
-- 2.查询平均成绩大于60分的同学的学号和平均成绩；
-- 2.1 得到每个学生的平均成绩
SELECT sno ,AVG(score) FROM scoresc,student s WHERE sc.sno = s.sid GROUP BY sno
SELECT sno ,AVG(score) FROM scoresc GROUP BY sno
-- 2.2 得到平均成绩大于75 的同学
SELECT sno ,AVG(score) avgScoreFROM score sc GROUP BY sno HAVINGAVG(score) >75
-- 3.查询平均成绩大于75分的同学的学号和平均成绩 的平均成绩；
SELECT AVG(avgScore) FROM (SELECTsno,AVG(score) avgScore FROM score sc GROUP BY sno HAVING AVG(score) >75) b
-- 4.查询所有同学的学号、姓名、选课数、总成绩；
-- 采用左外连接是因为，可能有的学生没有选择课程，
SELECT s.sid, s.sname, COUNT(sc.cno) 课程数 ,SUM(score) 总分 FROM student s LEFT OUTER JOIN score sc ON s.sid = sc.sno GROUP BYs.sid,s.sname
-- 5.查询没学过“欧阳”老师课的同学的学号、姓名；
SELECT s.sid , s.sname FROM student s WHERE s.sid NOT IN (
SELECT DISTINCT( sc.sno)
FROM score sc,course c,teacher t
WHERE sc.`cno`=c.`cid` AND t.`tid` = c.`tno` AND t.`tname`='欧阳');
-- 7 查询学过“慕容”老师所教的所有课的同学的学号、姓名；
SELECT s.sid , s.sname FROM student s WHERE s.sid IN (
-- 查询学过“慕容”老师所教的课的同学的学号、姓名；这里存在有学生只学了慕容老师只学了他某一部分课的学生
SELECT DISTINCT( s.sid)
FROM score sc,course c,teacher t
WHERE sc.`cno`=c.`cid` AND t.`tid` = c.`tno` AND s.sid = sc.sno AND t.`tname`='慕容'
GROUP BY s.sid -- 通过学学号进行分组查询
-- 查找到慕容老师教所有课的总数
HAVING COUNT(sc.cno) = (SELECTCOUNT(c.cname) FROM teacher t,course cWHERE c.tno = t.tid AND t.tname = '慕容'));
-- 8.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
SELECT a.sid ,a.sname FROM
-- 查询学过编号为2学生的成绩，学号，姓名
( SELECT sc.score ,s.sid ,s.snameFROM score sc, student s WHERE s.sid =sc.sno AND sc.cno = 2) a,
-- 查询学过编号为1学生的成绩，学号，姓名
( SELECT sc.score ,s.sid ,s.sname FROM score sc,student s WHERE s.sid = sc.sno AND sc.cno= 1)b
WHERE a.score < b.score AND a.sid = b.sid
编号为2学生的成绩 编号为1学生的成绩 编号“2”的成绩<编号“1”课程低
-- 9 查询所有课程成绩小于60分的同学的学号、姓名；
SELECT s.sid, s.sname,sc.score,sc.cno FROM score sc ,student s WHERE sc.sno = s.sid HAVING sc.score<60
--10、查询没有学全所有课的同学的学号、姓名；
SELECT sid,sname FROM student s,score sc WHERE s.sid = sc.sno
GROUP BY s.sid,s.sname
HAVING COUNT(sc.cno) <(SELECT COUNT(c.cid) FROM course c);
-- 11.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
-- 查询学号为1的同学所学的课程
SELECT sid,sname ,sc.`cno` FROM student s ,score sc WHERE s.sid = sc.sno AND sc.cno IN (
SELECT sc.`cno` FROM scoresc WHERE sc.sno = 9
)
-- 13.把“score”表中“西门”老师教的课的成绩都更改为此课程的平均成绩；
-- 找出西门老师教的课程的id
SELECT c.cid FROM teacher t, course c WHERE t.tid = c.tno AND t.tname ='西门'
-- “西门”老师教的课的成绩
SELECT a.courseNo , sc.`score` FROM score sc, (SELECT c.cid courseNo FROM teacher t, coursec WHERE t.tid = c.tno AND t.tname = '西门') a WHEREa.courseNo = sc.`cno`
-- 把“score”表中“西门”老师教的课的成绩都更改为此课程的平均成绩；
SELECT a.cno, AVG(score) FROM ( SELECT a.courseNo cno, sc.`score`score FROM score sc, (SELECT c.cidcourseNo FROM teacher t, course c WHERE t.tid = c.tno AND t.tname = '西门') a WHEREa.courseNo = sc.`cno`
)a GROUP BY a.cno
练习2
student表
CREATE TABLE student (
id INT(10) NOT NULL UNIQUE PRIMARY KEY ,
name VARCHAR(20) NOT NULL ,
sex VARCHAR(4) ,
birth YEAR,
department VARCHAR(20) ,
address VARCHAR(50)
);
插入数据：
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
id | name | sex | birth| department | address |
+----------------------+----------+----------+------------------+-------------------------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
CREATE TABLE score (
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,
stu_id INT(10) NOT NULL ,
c_name VARCHAR(20) ,
grade INT(10)
);
插入数据：
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);
**从student表中查询年龄18~22岁的学生信息
SELECT id,name,sex,2013-birth ASage,department,address FROM studentWHERE 2013-birth BETWEEN 18 AND 22;
**8.从student表中查询每个院系有多少人
SELECT department, COUNT(id) FROM studentGROUP BY department;
**从score表中查询每个科目的最高分
SELECT c_name,MAX(grade) FROM score GROUPBY c_name;
**查询李四的考试科目（c_name）和考试成绩（grade）
SELECT c_name, grade FROM score WHERE stu_id=(SELECT id FROMstudent WHERE name= '李四' );
**用连接的方式查询所有学生的信息和考试信息
SELECT student.id,name,sex,birth,department,address,c_name,gradeFROM student,score WHEREstudent.id=score.stu_id;
**计算每个学生的总成绩
mysql> SELECT student.id,name,SUM(grade)FROM student,score
-> WHERE student.id=score.stu_id
-> GROUP BY id;
**计算每个考试科目的平均成绩
mysql> SELECT c_name,AVG(grade) FROMscore GROUP BY c_name;
**查询计算机成绩低于95的学生信息
mysql> SELECT * FROM student
-> WHERE id IN
-> (SELECT stu_id FROM score
-> WHERE c_name="计算机" and grade<95);
**查询同时参加计算机和英语考试的学生的信息
mysql> SELECT * FROM student
-> WHERE id =ANY
-> ( SELECT stu_id FROM score
-> WHERE stu_id IN (
-> SELECT stu_id FROM
-> score WHEREc_name= '计算机')
-> AND c_name= '英语' );
方式二：
mysql>SELECT a.* FROM student a ,score b ,score c
-> WHERE a.id=b.stu_id
-> AND b.c_name='计算机'
-> AND a.id=c.stu_id
-> AND c.c_name='英语';
**将计算机考试成绩按从高到低进行排序
mysql> SELECT stu_id, grade
-> FROM score WHERE c_name= '计算机'
-> ORDER BY grade DESC;
**查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
mysql> SELECT student.id,name,sex,birth,department, address, c_name,grade
-> FROM student, score
-> WHERE
-> (name LIKE '张%' OR name LIKE '王%')
-> AND
-> student.id=score.stu_id ;
/*
查询学生生日在91年之后的班级的信息。
*/
SELECT * FROM student WHEREbirth>'1991-01-01';
SELECT * FROM classes WHERE cid IN (SELECT cno FROM studentWHERE birth>'1991-01-01');
/*查询学生生日大于91年1月1日，如果记录存在，前面的SQL语句就会执行
*/
/*
这种方式错误，在where和exists之间不能有任何属性
SELECT * FROM classes WHERE cid exists (SELECT cno FROM student WHERE birth>'1991-01-01');*/
SELECT * FROM classes WHERE EXISTS (SELECTcno FROM student WHERE birthday > '1991-01-01');
/*
/*查询班级名称，和班级总人数
这种方式错误，count后面不能有空格，必须是这种：COUNT(*)
SELECT cname, COUNT (*) FROM classesc,student s WHERE c.cid = s.cno GROUP BY c.cname;
*/
SELECT cname, COUNT(*) FROM classesc,student s WHERE c.cid = s.cno GROUP BY c.cname;
/*
查询学生的姓名和学生所选的总课程平均成绩。
姓名:student
课程:course
成绩：score
*/
SELECT s.sname, AVG(sc.score) FROM students, score sc WHERE s.cno = sc.cno GROUPBY s.sname;
/*
/*查询学生的姓名和学生的选课总数，显示选课超过2门学生姓名。
*/
SELECT s.sname ,COUNT(*) FROM student s,score sc WHERE s.sid = sc.sno GROUP BY s.sname HAVING COUNT(*)>2;
/*
查询学生和平均成绩，但是平均成绩大于01班的任何一个学生的平均成绩。
得到1班的学生平均成绩。
*/
SELECT s.sname, AVG(sc.score) FROM scoresc, classes c,student s WHERE s.cno = c.cid AND s.sid = sc.sno AND c.cname LIKE '%1班%' GROUP BY s.sname;
SELECT s.sname,AVG(sc.score) FROM students,score sc WHERE s.sid = sc.sno GROUP BY s.sname HAVING AVG(sc.score) > ANY
(SELECT AVG(sc.score) FROM student s,scoresc,classes c WHERE s.sid = sc.sno AND s.cno = c.cid AND c.cname LIKE '%1班%' GROUP BYs.sname);
练习3
1.查询每个月倒数第 2 天入职的员工的信息.
2.查询出last_name为 ‘Chen’ 的 manager 的信息.
3.查询平均工资高于 8000 的部门 id 和它的平均工资.
4. 查询工资最低的员工信息: last_name,salary
5. 查询平均工资最低的部门信息
6. 查询平均工资最低的部门信息和该部门的平均工资
7. 查询平均工资最高的 job 信息
8. 查询平均工资高于公司平均工资的部门有哪些?
9. 查询出公司中所有 manager 的详细信息.
10. 各个部门中最高工资中最低的那个部门的最低工资是多少
11. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
12. 查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.
13.返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
************************answers*********************
1.查询每个月倒数第 2 天入职的员工的信息.
selectlast_name,hire_date
fromemployees
wherehire_date= last_day(hire_date) – 1
2.查询出last_name为 ‘Chen’ 的 manager 的信息.
1). 通过两条sql查询:
selectmanager_id
fromemployees
wherelower(last_name) = ‘chen’ –返回的结果为 108
select*
fromemployees
whereemployee_id= 108
2). 通过一条sql查询(自连接):
selectm.*
fromemployees e, employees m
wheree.manager_id= m.employee_id and e.last_name = ‘Chen’
3). 通过一条sql查询(子查询):
select*
fromemployees
whereemployee_id= (
selectmanager_id
fromemployees
wherelast_name= ‘Chen’
)
3.查询平均工资高于 8000 的部门 id 和它的平均工资.
SELECTdepartment_id, avg(salary)
FROM employees e
GROUP BYdepartment_id
HAVINGavg(salary) > 8000
4. 查询工资最低的员工信息: last_name,salary
SELECTlast_name, salary
FROMemployees
WHEREsalary = (
SELECTmin(salary)
FROMemployees
)
5. 查询平均工资最低的部门信息
SELECT*
FROMdepartments
WHEREdepartment_id = (
SELECTdepartment_id
FROMemployees
GROUPBY department_id
HAVINGavg(salary) = (
SELECTmin(avg(salary))
FROMemployees
GROUPBY department_id
)
)
6. 查询平均工资最低的部门信息和该部门的平均工资
select d.*,(select avg(salary) from employees where department_id = d.department_id)
from departmentsd
whered.department_id= (
SELECTdepartment_id
FROM employees
GROUP BYdepartment_id
HAVINGavg(salary) = (
SELECT min(avg(salary))
FROM employees
GROUP BY department_id
)
)
7. 查询平均工资最高的 job 信息
1). 按job_id分组, 查询最高的平均工资
SELECTmax(avg(salary))
FROMemployees
GROUPBY job_id
2). 查询出平均工资等于 1) 的job_id
SELECTjob_id
FROMemployees
GROUPBY job_id
HAVINGavg(salary) = (
SELECTmax(avg(salary))
FROMemployees
GROUPBY job_id
)
3). 查询出 2) 对应的 job 信息
SELECT*
FROMjobs
WHEREjob_id = (
SELECTjob_id
FROMemployees
GROUPBY job_id
HAVINGavg(salary) = (
SELECTmax(avg(salary))
FROMemployees
GROUPBY job_id
)
)
8. 查询平均工资高于公司平均工资的部门有哪些?
1). 查询出公司的平均工资
SELECTavg(salary)
FROMemployees
2). 查询平均工资高于 1) 的部门 ID
SELECTdepartment_id
FROMemployees
GROUPBY department_id
HAVINGavg(salary) > (
SELECTavg(salary)
FROMemployees
)
9. 查询出公司中所有 manager 的详细信息.
1). 查询出所有的manager_id
SELECTdistinct manager_id
FROMemployeess
2). 查询出employee_id为 1) 查询结果的那些员工的信息
SELECTemployee_id, last_name
FROMemployees
WHEREemployee_id in (
SELECTdistinct manager_id
FROMemployees
)
10. 各个部门中最高工资中最低的那个部门的最低工资是多少
1). 查询出各个部门的最高工资
SELECTmax(salary)
FROMemployees
GROUPBY department_id
2). 查询出 1) 对应的查询结果的最低值: 各个部门中最低的最高工资(无法查询对应的department_id)
SELECTmin(max(salary))
FROMemployees
GROUPBY department_id
3). 查询出 2) 所对应的部门 id 是多少: 各个部门中最高工资等于 2) 的那个部门的id
SELECTdepartment_id
FROMemployees
GROUPBY department_id
HAVINGmax(salary) = (
SELECTmin(max(salary))
FROMemployees
GROUPBY department_id
)
4). 查询出 3) 所在部门的最低工资
SELECTmin(salary)
FROMemployees
WHEREdepartment_id = (
SELECTdepartment_id
FROMemployees
GROUPBY department_id
HAVINGmax(salary) = (
SELECTmin(max(salary))
FROMemployees
GROUPBY department_id
)
)
11. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
1). 各个部门中, 查询平均工资最高的平均工资是多少
SELECTmax(avg(salary))
FROMemployees
GROUPBY department_id
2). 各个部门中, 平均工资等于 1) 的那个部门的部门号是多少
SELECTdepartment_id
FROMemployees
GROUPBY department_id
HAVINGavg(salary) = (
SELECTmax(avg(salary))
FROMemployees
GROUPBY department_id
)
3). 查询出 2) 对应的部门的manager_id
SELECTmanager_id
FROMdepartments
WHEREdepartment_id = (
SELECTdepartment_id
FROMemployees
GROUPBY department_id
HAVINGavg(salary) = (
SELECTmax(avg(salary))
FROMemployees
GROUPBY department_id
)
)
4). 查询出employee_id为 3) 查询的manager_id的员工的last_name,department_id, email, salary
SELECTlast_name, department_id, email, salary
FROMemployees
WHEREemployee_id = (
SELECTmanager_id
FROMdepartments
WHEREdepartment_id = (
SELECTdepartment_id
FROMemployees
GROUPBY department_id
HAVINGavg(salary) = (
SELECTmax(avg(salary))
FROMemployees
GROUPBY department_id
)
)
)
12. 查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.
1). 查询出 1999 年来公司的所有的员工的 salary
SELECTsalary
FROMemployees
WHEREto_char(hire_date, ‘yyyy’) = ’1999′
2). 查询出 1) 对应的结果的最大值
SELECTmax(salary)
FROMemployees
WHEREto_char(hire_date, ‘yyyy’) = ’1999′
3). 查询工资等于 2) 对应的结果且 1999 年入职的员工信息
SELECT*
FROMemployees
WHEREto_char(hire_date, ‘yyyy’) = ’1999′ AND salary = (
SELECTmax(salary)
FROMemployees
WHEREto_char(hire_date, ‘yyyy’) = ’1999′
)
13.返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary
SELECTemployee_id, last_name, job_id, salary
FROM employees
WHERE salary<ALL
(SELECT salary
FROM employees
WHERE job_id =‘IT_PROG’)
ANDjob_id<> ‘IT_PROG’;
******************高级子查询******************
· 书写多列子查询
· 在FROM 子句中使用子查询
· 在SQL中使用单列子查询
· 书写相关子查询
· 使用EXISTS 和NOT EXISTS 操作符
· 使用子查询更新和删除数据
· 使用WITH 子句
–多列子查询（不成对比较&成对比较）
1.查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id
[方式一]
SELECTemployee_id, manager_id, department_id
FROM employees
WHERE manager_idIN
(SELECT manager_id
FROM employees
WHEREemployee_id IN (174,141))
ANDdepartment_id IN
(SELECT department_id
FROM employees
WHEREemployee_id IN (174,141))
AND employee_idNOT IN(174,141);
[方式二]
SELECT employee_id,manager_id, department_id
FROM employees
WHERE(manager_id, department_id) IN
(SELECTmanager_id, department_id
FROM employees
WHEREemployee_id IN (141,174))
AND employee_idNOT IN (141,174);
–在 FROM 子句中使用子查询
2.返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
[方式一]
selectlast_name,department_id,salary,
(selectavg(salary)fromemployees e3
wheree1.department_id = e3.department_id
group bydepartment_id) avg_salary
from employeese1
where salary>
(selectavg(salary)
from employeese2
wheree1.department_id = e2.department_id
–group bydepartment_id
)
[方式二]
SELECTa.last_name, a.salary,
a.department_id,b.salavg
FROM employeesa, (SELECT department_id,
AVG(salary)salavg
FROM employees
GROUP BYdepartment_id) b
WHEREa.department_id = b.department_id
AND a.salary>b.salavg;
–单列子查询表达式
· Oracle8i只在下列情况下可以使用, 例如:
o SELECT 语句(FROM 和 WHERE 子句)
o INSERT 语句中的VALUES列表中
· Oracle9i中单列子查询表达式可在下列情况下使用:
o DECODE 和CASE
o SELECT 中除GROUP BY 子句以外的所有子句中
3.显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为‘Canada’,其余则为‘USA’。
SELECTemployee_id, last_name,
(CASEdepartment_id
WHEN(SELECTdepartment_id FROM departments
WHERElocation_id = 1800)
THEN ‘Canada’ELSE ‘USA’ END) location
FROM employees;
4.查询员工的employee_id,last_name,要求按照员工的department_name排序
SELECTemployee_id, last_name
FROM employees e
ORDER BY (SELECTdepartment_name
FROMdepartments d
WHEREe.department_id = d.department_id);
5.查询员工中工资大于本部门平均工资的员工的last_name,
salary和其department_id
SELECTlast_name, salary, department_id
FROM employeesouter
WHERE salary>
(SELECTAVG(salary)
FROM employees
WHEREdepartment_id =
outer.department_id);
6.若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id
SELECT e.employee_id,last_name,e.job_id
FROM employees e
WHERE 2<=(SELECT COUNT(*)
FROM job_history
WHEREemployee_id = e.employee_id);
–EXISTS 操作符
· EXISTS 操作符检查在子查询中是否存在满足条件的行
· 如果在子查询中存在满足条件的行:
o 不在子查询中继续查找
o 条件返回TRUE
7.查询公司管理者的employee_id,last_name,job_id,
department_id信息
SELECTemployee_id, last_name, job_id, department_id
FROM employeesouter
WHERE EXISTS (SELECT ‘X’
FROM employees
WHERE manager_id=
outer.employee_id);
8.查询departments表中，不存在于employees表中的部门的department_id和department_name
SELECTdepartment_id, department_name
FROM departmentsd
WHERE NOT EXISTS(SELECT ‘X’
FROM employees
WHEREdepartment_id
=d.department_id);
–关于数据更新
9.修改表employees,添加department_name列，赋予department_id相应的部门名称。
ALTER TABLEemployees
ADD(department_nameVARCHAR2(14));
UPDATE employeese
SETdepartment_name =
(SELECTdepartment_name
FROM departments d
WHEREe.department_id = d.department_id);
–关于数据删除
10.删除表employees中，其与emp_history表皆有的数据
DELETE FROMemployees E
WHEREemployee_id in
(SELECTemployee_id
FROM emp_history
WHEREemployee_id = E.employee_id);
–WITH 子句
11.查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
WITH
dept_costs AS (
SELECTd.department_name, SUM(e.salary) AS dept_total
FROM employeese, departments d
WHEREe.department_id = d.department_id
GROUP BYd.department_name),
avg_cost AS (
SELECTSUM(dept_total)/COUNT(*) AS dept_avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHEREdept_total>
(SELECT dept_avg
FROM avg_cost)
ORDER BYdepartment_name;
附加题目：
12.查询员工的last_name, department_id, salary.其中员工的salary,department_id与有奖金的任何一个员工的salary,
department_id相同即可
selectlast_name,department_id,salary
fromemployees
where(salary,department_id)in(
selectsalary,department_id
fromemployees
wherecommission_pctisnotnull
)
13.选择工资大于所有JOB_ID = ‘SA_MAN’的员工的工资的员工的last_name, job_id, salary
selectlast_name,job_id,salary
fromemployees
wheresalary>all(
selectsalary
fromemployees
wherejob_id=’SA_MAN’
)
14.选择所有没有管理者的员工的last_name
selectlast_name
fromemployeese1
wherenotexists(
select’A’
fromemployeese2
wheree1.manager_id=e2.employee_id
)
15.查询10，50，20号部门的job_id，department_id并且department_id按10，50，20的顺序排列
Column dummynoprint;
selectjob_id ,department_id ,1 dummy
from employees
wheredepartment_id= 10
union
selectjob_id ,department_id , 2
from employees
wheredepartment_id= 50
union
selectjob_id ,department_id , 3
from employees
wheredepartment_id=20
order by 3
- 常用的SQL语句查询练习
- 常用的SQL查询语句
- 常用的SQL查询语句
- 常用的sql查询语句
- 常用的sql查询语句
- SQL查询语句练习
- SQL查询语句练习
- SQL常用查询语句
- 常用SQl 查询语句
- SQL常用查询语句
- 常用SQl 查询语句
- 常用SQL查询语句
- SQL常用查询语句
- sql常用查询语句
- SQL常用查询语句
- sql常用查询语句
- SQL常用语句查询
- 常用sql语句-查询
- leetcode--7. Reverse Integer
- 机器学习—使用k-近邻算法改进约会网站的配对效果
- BZOJ 2597: [Wc2007]剪刀石头布 费用流
- 行内元素与块级元素的总结
- Ubuntu14.04 spyder安装 Python2和3切换
- 常用的SQL语句查询练习
- querySelector()和querySelectorAll()
- 产生随机数的方法
- [luogu2723]丑数 Humble Numbers（乱搞）
- 修改fork库名称，不能拉取代码
- Linux常见的压缩命令
- linux多核多进程访问文件示例程序(每个核上运行一个程序)
- Android中判断app何时启动和关闭的技术研究
- 非常优美的弹出提示框SweetAlert
