MySQL练习题（一）

1.建立数据表

1.CREATE TABLE DEPT( DEPTNO INT PRIMARY KEY,DNAME VARCHAR(14),LOC VARCHAR(13));

2.CREATE  TABLE EMP ( EMPNO INT PRIMARY KEY, ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT, HIREDATE DATE, SAL INT , COMM INT, DEPTNO INT , FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO) );

3. create table SALGRADE(GRADE INT ,LOSAL INT,HISAL INT);

4. create table BONUS(ENAME varchar(10), JOB varchar(9), SAL int, COMM  int);

2.输入数据

DEPT表

insert into DEPT values(10,'ACCOUNTING','NEW YORK')；

insert into DEPT values(20,'RESEARCH','DALLAS');

insert into DEPT values(30,'SALES','CHICAGO');

insert into DEPT values(40,'OPERATIONS','BOSTON');

EMP表

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20); 
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30); 
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30); 
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
 INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30); 
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30); 
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10); 
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20); 
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
 INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30); 
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20); 
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30); INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20); 
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLEARK', 7782, '1982-01-23', 1300, NULL, 10);

SALGRADE表

INSERT INTO SALGRADE VALUES( 1, 700, 1200 ); 
INSERT INTO SALGRADE VALUES( 2, 1201, 1400 ); 
INSERT INTO SALGRADE VALUES( 3, 1401, 2000 );
 INSERT INTO SALGRADE VALUES( 4, 2001, 3000 );
 INSERT INTO SALGRADE VALUES( 5, 3001, 9999 ); COMMIT;

 单表查询练习

SQL语句练习题一
1、选择部门30中的所有员工；

select ENAME,DEPTNO from EMP where DEPTNO = 30;

2、列出所有办事员(CLERK)的姓名，编号和部门编号；

select ENAME,EMPNO,DEPTNO from EMP where JOB='CLERK';

3、找出奖金高于工资的员工；

select ENAME,SAL,COMM from EMP where COMM>SAL;

4、找出奖金高于工资的60%的员工；

select ENAME,SAL,COMM from EMP where COMM>SAL*0.6;

5、找出部门10中的所有经理(MANAGER)和部门20中所有的办事员(CLERK)的详细资料；

select * from EMP where DEPTNO=10 and JOB='MANAGER' or DEPTNO =20 and JOB='CLERK';

6、找出部门10中所有的经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其工资大于或等于2000的所有员工的详细资料；

select * from EMP where (DEPTNO=10 and JOB='MANAGER') or (DEPTNO =20 and JOB='CLERK') or (JOB not in ('MANAGER','CLERK') and SAL>=2000);

7、找出收取奖金的员工的不同工作；

select * from EMP where  not  (COMM is NULL);

8、找出不收取奖金或者收取的奖金低于100的员工；

select * from EMP where COMM is NULL or COMM <100;

9、找出各月倒数第三天受雇的所有员工；

 select * from EMP where right(HIREDATE,2)>=28;

**SELECT * FROM EMP WHERE LAST_DAY(HIREDATE)-2=HIREDATE;**

10、找出早于12年前受雇的员工；

select * from EMP where 2023-left(HIREDATE,4)>12;

11、以首字母大写的方式显示所有员工的姓名；

update EMP set ENAME=concat(UPPER(left(ENAME,1)),substring(ENAME,2,(length(ENAME)-1))));

12、显示正好为5个字符的员工的姓名；

select ENAME from EMP where length(ENAME)=5;

13、显示不带有“R”的员工姓名；

select ENAME from EMP where ENAME not like '%R%';

14、显示所有员工姓名的前三个字符；

select substring( ENAME,1,3) from EMP ;

15、显示所有员工的姓名，用“a”替换所有的“A”；

select replace( ENAME,'A','a') from EMP ;

16、显示满10年服务年限的员工的姓名和受雇日期；

select ENAME,HIREDATE from EMP where 2023-year(HIREDATE)>=10;

17、显示员工的详细资料，按姓名排序；

select * from EMP order by ENAME;

18、显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面；

select ENAME,HIREDATE from EMP order by HIREDATE;

19、显示所有员工的姓名、工作和工资，按工作的降序排序，若工作相同则按工资排序；

select ENAME,JOB,SAL from EMP order by JOB,SAL desc;

20、显示所有员工姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面；

select ENAME,year(HIREDATE) as 入职年份, month(HIREDATE)as 入职月份 from EMP order by month(HIREDATE),year(HIREDATE);

21、显示在一个月为30天的情况所有员工的日工资，忽略余数；

select ENAME,truncate(SAL/30,0) as 日工资 from EMP ;

22、找出(任何年份的)2月受聘的所有员工；

select * from EMP where month(HIREDATE)=2;

23、对于每个员工，显示其加入公司的天数；

select ENAME,(2023-year(HIREDATE))*365 as 入职天数 from EMP;

24、显示姓名字段的任何位置包含“A”的所有员工的姓名；

select ENAME from EMP where ENAME like '%A%';

25、以年月日的方式显示所有员工的服务年限；

 SELECT ENAME,HIREDATE,
		TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)YEAR,
		TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,HIREDATE),12))MONTH,
		TRUNC(SYSDATE-ADD_MONTHS(HIREDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE))) DAY
		FROM EMP;