sql语句练习

1. 查询出所有学员的学员编号姓名和生日

SELECT StudentID,StudentName,Birth from tb_student;

1. 查询出所有master的学员

SELECT * from tb_student

where Degree='master';

1. 查询出所有学员的信息，并按班级编号倒序排序。

SELECT * FROM tb_student

order by ClassID DESC;

Asc为升序（默认），desc为倒序；

1. 查询出每个班级的人数（group  by）

SELECT COUNT(ClassID),ClassID FROM tb_student

 GROUP BY ClassID;

1. 查询出每种学位的人数(group by)

SELECT degree,COUNT(*) FROM tb_student

 GROUP BY Degree;

1. 查询出学员姓名的第二个字母是a的所有学员。

SELECT * from tb_student

where StudentName like '_a%';

1. 查询出住址不在houston，并且出生日期在1981-1-1后出生的学员

SELECT * FROM tb_student

WHERE Birth > '1981-1-1' AND NOT City='houston' ;

1. 查询出班级编号是1或者学历是bachelor的所有学员

SELECT * from tb_student

where ClassID=1 or Degree='bachelor';

1. 查询出每个班级的平均工资。

SELECT ClassID, AVG(Salary),count(ClassID),SUM(Salary)FROM tb_student

GROUP BY classid;

子查询：----------------------------------------------------------------------------------------------------------------------------------

1. 查询出比james的工资高的所有员工；

SELECT * from emp

WHERE sal>(SELECT sal from emp where ename ='james');

1. 查询出比平均工资高的所有员工；

select * from emp

where sal>(SELECT avg(sal) from emp);

1. 查询出james的同事

select * from emp

where deptno=(SELECT deptno from emp where ename='james');

1. 查询出比james工资高，并且与james职位相同的员工；

SELECT * FROM emp

where sal>(SELECT sal from emp where ename='james')

and job=(SELECT job FROM emp where ename='james')

-------------------------------------------------------------------------------------------------------------------

having:

5.查询出平均工资高于20部门的平均工资的职位；并从高到低进行排序；

select job,avg(sal) from emp

GROUP BY job

having avg(sal)>(select avg(sal) from emp where deptno=20)

ORDER BY avg(sal) desc

6.查询出没有员工的部门；

          SELECT * from dept d

where d.deptno  not In (SELECT deptno from emp e)

-- where d.deptno  not In (SELECT deptno from emp e where e.deptno is not null)

-- 当有员工部门为空时要有 is not null

---

学生成绩查询练习：（数据库：student）