create table DEPT
(
deptno int not null primary key,
dname varchar(14) null,
loc varchar(13) null
)
create table EMP
(
empno int not null primary key,
ename varchar(10) null,
job varchar(9) null,
mgr int null,
hiredate date null,
sal float null,
comm float null,
deptno int null foreign key references DEPT(deptno),
)
create table SALGRADE
(
grade int null,
losal int null,
hisal int null,
)
--查询部分:
--1.查询雇员姓名,所在部门编号和名称
SELECT emp.ename,dbo.DEPT.deptno,dept.dname FROM emp,dbo.DEPT WHERE emp.deptno=dbo.DEPT.deptno
--2.查询雇员姓名,工作,领导的姓名,自链接
SELECT e1.ename,e1.job,e2.ename FROM dbo.EMP e1 INNER JOIN dbo.EMP e2 ON e1.mgr=e2.empno
--3.查询雇员姓名,工作,领导姓名及部门名称
SELECT e1.ename,e1.job,e2.ename,(SELECT dname FROM dbo.DEPT WHERE e1.deptno=deptno)
FROM dbo.EMP e1 INNER JOIN dbo.EMP e2 ON e1.mgr=e2.empno
--4.查询雇员姓名,工作,工资
SQL server混合查询,经典查询练习-----查询出部门名称,部门的员工数,部门的平均工资,部门的最低收入雇员姓名:SELECT d.dname'部门',ed.c'员工数',ed.a'平均工资',e.ename'最低收入雇员'FROM dept d,emp e,( SELECT deptno,COUNT(empno) c,AVG(sal) a,MIN(sal) m FROM emp GROUP BY deptno) edWHERE d.deptno=ed.deptno A