CREATE DATABASE gjp CHARSET "utf8";

CREATE TABLE gjp_sort(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(100),

parent VARCHAR(100),

sdesc VARCHAR(10000)

);

CREATE TABLE gjp_ledger(

lid INT PRIMARY KEY AUTO_INCREMENT,

parent VARCHAR(100),

money DOUBLE,

sid INT,

account VARCHAR(100),

createtime DATE,

ldesc VARCHAR(1000)

);

ALTER TABLE gjp_ledger CHANGE idesc ldesc VARCHAR(1000);

#需求4.1：统计gjp_ledger表有多少条数据

SELECT COUNT(*) FROM gjp_ledger;

#需求4.2：统计工商银行的内容有多少条

SELECT COUNT(account) FROM gjp_ledger WHERE account = "工商银行";

#需求4.3：统计账务表中金额大于3000的有多少条记录

SELECT COUNT(money) FROM gjp_ledger WHERE money > 3000;

#需求4.4：查询账务表，求出money字段的和

SELECT SUM(money) FROM gjp_ledger;

#需求4.5：查询账务表，统计所有支出的金额总和

SELECT SUM(money) FROM gjp_ledger WHERE parent = "支出";

#需求4.6：查询账务表，统计出 支出中的最大值 和 收入中的最小值

SELECT MAX(money) FROM gjp_ledger WHERE parent = "支出";

SELECT MIN(money) FROM gjp_ledger WHERE parent = "收入";

#需求4.7：查询账务表，计算出，所有支出的平均数

SELECT AVG(money) FROM gjp_ledger WHERE parent = "支出";

#需求4.8：查询账务表，使用money字段，进行升序排序 

SELECT * FROM gjp_ledger ORDER BY money ASC;

#需求4.9：查询账务表，使用createtime字段，进行降序排序 

SELECT * FROM gjp_ledger ORDER BY createtime DESC;

#需求5.1：查询出，支出的共计多少钱，收入的共计多少钱

SELECT parent,SUM(money) FROM gjp_ledger GROUP BY parent;

#需求5.2：查询出，所有的金额总和，先按照支出和收入分类，再按照银行的类型分。

SELECT parent,account,SUM(money) FROM gjp_ledger GROUP BY parent ASC,account ASC;

#需求5.3：查询出，支出的共计多少钱，收入的共计多少钱， 只显示大于20000元的

SELECT parent,SUM(money) s FROM gjp_ledger GROUP BY parent HAVING s >20000;

/*

作业2

*/

#1.返回员工信息以及员工所在的部门名称和部门地址。

SELECT e.* ,d.dname,d.loc FROM emp e INNER JOIN dept d ON e.deptno = d.DEPTNO;

#2.工资水平多于smith的员工信息。

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = "smith");

#3.返回员工和所属经理的姓名。

SELECT e.ename,f.ename FROM emp e LEFT JOIN emp f ON e.mgr = f.EMPNO ;

#4.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名

SELECT e.ename,e.HIREDATE,f.ename,f.HIREDATE FROM emp e INNER JOIN emp f ON e.MGR = f.EMPNO AND e.HIREDATE < f.HIREDATE;

#5.返回员工姓名及其所在的部门名称。

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

#6.返回从事clerk工作的员工姓名和所在部门名称。

SELECT e.ename,e.job,d.dname FROM emp e INNER JOIN dept d ON e.deptno = d.deptno AND e.job = "clerk";

#7.返回部门号及其本部门的最低工资。

SELECT deptno,MIN(sal) FROM emp GROUP BY deptno;

#8.返回销售部(sales)所有员工的姓名。

SELECT ename FROM emp e INNER JOIN dept d ON e.DEPTNO = d.DEPTNO AND d.dname = "sales";

#9.返回工资水平多于平均工资的员工。

SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp) ;

#10.返回与30部门员工工资水平相同的员工姓名与工资。

SELECT ename,sal FROM emp WHERE deptno != 30 AND sal = ANY(SELECT sal FROM emp WHERE deptno = 30);

#11.返回工资高于30部门所有员工工资水平的员工信息。

SELECT * FROM emp WHERE deptno != 30 AND sal > ALL(SELECT sal FROM emp WHERE deptno = 30);

#12.返回部门号、部门名、部门所在位置及其每个部门的员工总数。

SELECT d.DEPTNO,d.DNAME,d.LOC,COUNT(e.ename) 员工数 FROM dept d INNER JOIN emp e ON d.DEPTNO = e.DEPTNO GROUP BY e.DEPTNO;

#13.返回员工的姓名、所在部门名及其工资。

SELECT e.ename,d.dname,e.sal FROM emp e INNER JOIN dept d ON e.DEPTNO = d.DEPTNO;