sql 题目一根据sql语句创建如下表完成相关sql语句的编写1 ##部门表2 CREATE TABLE dept3 deptno INT PRIMARY KEY##部门编号4 dname VARCHAR14 ##部门名称5 loc VARCHAR13 ##部门地址6 ;7 #部门表数据8 INSERT INTO dept VALUES 10财务部北京20市场部上海30销售部广州40运营部深圳;

单表：

SELECT * FROM emp WHERE deptno = 30;

SELECT empno, ename, deptno FROM emp WHERE job = 'clerk';

SELECT * FROM emp WHERE comm > sal;

SELECT * FROM emp WHERE comm > sal * 0.6;

SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;

SELECT * FROM emp WHERE comm < 100 OR comm IS NULL;

SELECT * FROM emp WHERE ename LIKE 'a%' OR ename LIKE 'b%' OR ename LIKE 's%';

SELECT * FROM emp WHERE LENGTH(ename) = 6;

SELECT * FROM emp WHERE ename NOT LIKE '%r%';

SELECT * FROM emp ORDER BY ename;

SELECT * FROM emp ORDER BY job DESC, sal ASC;

SELECT * FROM emp WHERE ename LIKE '%a%';

多表：

SELECT e.ename, m.ename AS manager_name FROM emp e LEFT JOIN emp m ON e.mgr = m.empno;

SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;

SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno WHERE e.job = 'clerk';

SELECT e.ename FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno WHERE d.dname = '销售部';

SELECT d.deptno, d.dname, d.loc, COUNT(e.empno) AS emp_count FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno GROUP BY d.deptno;

SELECT e.ename, d.dname, e.sal FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;

SELECT e.*, d.dname FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;

SELECT job, MIN(sal) AS min_sal FROM emp GROUP BY job;

SELECT ename, sal + COALESCE(comm, 0) AS annual_salary FROM emp ORDER BY annual_salary;

SELECT ename FROM emp WHERE sal BETWEEN (SELECT losal FROM salgrade WHERE grade = 4) AND (SELECT hisal FROM salgrade WHERE grade = 4)