数据库原理及应用

Group 1: 用命令“Select”查询相关数据

• 1、检索名字没有字母’R’的员工姓名；

Select ename from emp where ename not like ‘%r’;

• 2、 检索在任何位置有字母“A”的员工姓名；

Select ename from emp where ename like ‘%A%’;

• 3、 检索所有员工姓名的前三个字母；

Select left(ename,3) from emp;

• 4、 检索工作是sales的员工姓名；

Select ename from emp where job=’salesman’;

• 5、 检索最低工资在1500元的工作岗位；

select job,min(sal)min_sal from emp where sal=1500 group by job;

• 6、 查询部门2所有的经理、部门3所有clerks以及所有其他工资超过2000元的员工姓名；

select ename from emp where (deptno='3' and job='clerk') or (deptno='2' a
nd job='manager') and sal>2000;

• 7、 检索员工津贴comm为null或comm小于300的员工姓名和津贴值；

select ename,comm from emp where comm is null or comm<300;

• 8、 检索所有员工的姓名、职位和工资，按职位降序排列；

select ename,job,sal from emp order by job desc;

• 9、 检索所有员工的年收入，按升序显示；

mysql> select ename,sal*12 as income_year from emp order by income_year;

• 10、 检索与SCOTT的工作岗位相同的所有员工的姓名；

select ename,deptno from emp where job=(select job from emp where ename='SCOTT') and ename<>'SCOTT';（发现满足条件中有两位同名于是加上了部门编号来加以区分）

• 11、 检索所有职员的姓名和所在部门的名称；

select e.ename,d.dname from emp as e,dept as d where e.job="clerk" and e.deptno=d.deptno;

• 12、 检索部门“Research”或“Manager”的所有员工姓名；

select e.ename,d.dname from emp as e,dept as d where d.dname="manager" or (d.dname="research") and e.deptno=d.deptno;

• 13、 通过子查询检索与Smith所在部门相同的所有员工的姓名

select ename from emp where deptno=(select deptno from emp where ename='smith')and ename<>"smith";

• 14、 检索所有工资高于Smith工资的员工姓名和所在部门；

select e.ename,d.dname as dept from emp as e,dept as d where sal>(select sal from emp where ename="smith")and e.deptno=d.deptno;

• 15、 检索所有工资高于整个公司平均工资的员工的姓名和工资；

select e.ename,e.sal from emp as e,(select avg(sal)as avgsal from emp)as d where e.sal>d.avgsal;

• 16、 检索所有员工加入公司的天数；

select ename,timestampdiff(day,hiredate,now())as days from emp;

• 17、 检索所有员工的姓名、所在部门名称和年薪；

select e.ename,d.dname,e.sal*12 as year_sal from emp as e,dept as d where e.deptno=d.deptno;

• 18、查询与3号部门某个员工工资相同的员工姓名和工资；

select ename,sal from emp where sal=(select sal from emp where deptono=3 and ename=’ward’)and ename<>’ward’;

• 19、检索每个部门的名称和人数;

Select d.dname,count(e.deptno) from dept as d,emp as e where e.deptno=d.deptno group by e.deptno;

• 20、检索每一种工作的最低工资和工作;

Select d.name,min(e.sal) as min_sal,max(e.sal) as max_sal from emp as e,dept as d where e.deptno=d.deptno group by e.deptno;

• 21、查询各个部门经理职位的员工最低工资;

select e.ename,min(sal) as min_sal,d.dname from emp as e,dept as d where
e.job='manager' and e.deptno = d.deptno group by dname;

• 22、检索那些员工的姓名和工资，他们的工资高于3号部门员工的最高工资。(>any或max)

select ename,sal from emp where sal >any(select sal from emp where deptno=3);

• 23、查询各部门员工人数、平均工资、平均参加工作的天数；

select deptno,count(ename)as emp_num,avg(sal)as avg_sal,avg(timestampdiff(day,hiredate,now()))as avg_days from emp group by deptno;

• 24、检索所有被雇佣日期是某个月的倒数第三天的员工名字；

Select ename from emp where hiredate=date_format(last_day(hiredate)-2,'%y-%m-%d');

• 25、检索至少有两个人的部门名称和人数；

select dept.dname,count(emp.ename) sum from dept left join emp on emp.deptno=dept.deptno group by dept.deptno having sum>=2;

• 26、检索所有部门名称和所有员工，包括那些没有任何员工的部门;

select dept.dname,emp.ename from dept left outer join emp on dept.deptno=emp.deptno;

• 27、检索所有员工及其相关领导的姓名;

select a.ename ,b.ename as mgr from emp a inner join emp b on a.mgr=b.empno;

• 28、查询聘用日期早于他们的领导的雇员姓名。

select a.ename from emp a inner join emp b on a.mgr=b.empno where timestampdiff(day,a.hiredate,now()) > timestampdiff(day,b.hiredate,now());

---

Group 2:请使用你的账户创建两个表，包括属性、数据类型、主键和外键

ddept (deptno, dname loc)
deptno:integer;dname: varchar (20); loc: varchar (30);
primary key:deptno;

create table ddept(deptno integer, dname varchar(20), loc varchar(30), primary key(deptno));

eemp (empno, ename,job,hiredate,salary,mgr,comm,deptno)
empno: integer;ename: varchar(20);job:varchar(20),hiredate:datetime, salary:double; comm:double;deptno: integer;
Primary key:empno;forign keys:deptno reference ddept(deptno)，mgr reference EEmp(empno)。

create table eemp(empno integer, ename varchar(20), job varchar(20), hiredate datetime, salary double, mgr int, comm double, deptno integer
not null, primary key(empno), foreign key(deptno) references
ddept(deptno), foreign key(mgr) references EEmp(empno));