Mysql-3 Exercises (filter again after grouping queries, sort in reverse order)

Insert the following table

/* Insert Table */create table Sanguo (empno INT, ename varchar, Job VARCHAR (), Mgr INT, Hiredte DATE, SA  L Double, comm Double, Deptno INT);    SELECT * from Sanguo;  ALTER TABLE Sanguo Change COLUMN hiredte hiredate DATE;  /* Fill in the data */INSERT INTO Sanguo VALUES (1001, ' ganning ', ' clerk ', 1013, ' 20001217 ', 8000.00,null,20);  INSERT into Sanguo VALUES (1002, ' Day ', ' Salesperson ', 1006, ' 2001-02-20 ', 16000.00,3000.00,30);  INSERT into Sanguo VALUES (1003, ' Yintian positive ', ' salesperson ', 1006, ' 2001-02-22 ', 12500.00,5000.00,30);  INSERT into Sanguo VALUES (1004, ' Liu Bei ', ' manager ', 1009, ' 2001-04-02 ', 29750.00,null,20);  INSERT into Sanguo VALUES (1005, ' Sheson ', ' Salesperson ', 1006, ' 2001-09-28 ', 12500.00,14000.00,30);  INSERT into Sanguo VALUES (1006, ' Guan Yu ', ' manager ', 1009, ' 2001-05-01 ', 28500.00,null,30);  INSERT into Sanguo VALUES (1007, ' Zhang Fei ', ' manager ', 1009, ' 2001-09-01 ', 24500.00,null,10);  INSERT into Sanguo VALUES (1008, ' Zhuge Liang ', ' analyst ', 1004, ' 2007-04-19 ', 30000.00,null,20);  INSERT into Sanguo VALUES (1009, ' Zeng-ox ', ' chairman ', NULL, ' 2001-11-17 ', 50000.00,null,10); InchSERT into Sanguo VALUES (1010, ' Wei-A-smile ', ' salesperson ', 1006, ' 2001-09-08 ', 15000.00,0.00,30);  INSERT into Sanguo VALUES (1011, ' Jacky Chow ', ' clerk ', 1008, ' 2007-05-23 ', 11000.00,null,20);  INSERT into Sanguo VALUES (1012, ' Cheng ', ' clerk ', 1006, ' 2001-12-03 ', 9500.00,null,30);  INSERT into Sanguo VALUES (1013, ' Pang Tong ', ' analyst ', 1004, ' 2001-12-03 ', 30000.00,null,20);  INSERT into Sanguo VALUES (1014, ' Huang Gai ', ' Clerk ', 1007, ' 2002-01-23 ', 13000.00,null,10);    INSERT into Sanguo VALUES (1015, ' Zhang San ', ' cleaners ', 1001, ' 2013-05-01 ', 80000.00,50000.00,50);  /* Query for all employees with department number 30 */SELECT * from Sanguo WHERE deptno = 30;  /* Query the number, name, department number */SELECT Empno,ename,deptno from Sanguo WHERE job = ' Salesperson ' for all positions for salesperson's employee;  /* Query all employees with bonuses greater than salary */SELECT * from Sanguo WHERE comm > sal;  /* Query for all employees with bonuses greater than 60% of the salary */SELECT * from Sanguo WHERE comm > (0.6*sal);  /* Query All department Number 10 is also the manager or department number 20 is also the salesperson's employee */SELECT * from Sanguo WHERE (Deptno = ten and job = ' manager ') or (Deptno = and job =  ' Sales staff '); /* Query All department Number 10 is also a manager or department number 20 and is a salesperson or a bonus greater than 20000 employees */SELECT * from Sanguo WHERE (Deptno = ten and job = ' warpor (Deptno = "job = ' salesperson ') or (Comm >= 20000);   /* Query for employees with no bonuses or bonuses less than 1000 */SELECT ename from Sanguo WHERE comm is NULL or comm < 1000;   /* Query All employees whose names are three words */SELECT ename from Sanguo WHERE ename like ' ___ ';    /* Query all employees who joined the company in 2000 */SELECT * from Sanguo WHERE hiredate like ' 2000% ';   /* Query all employee details, sort ascending by number */SELECT * from Sanguo order by Empno ASC;   /* Query all employee details, sorted in descending order if wages are the same using the entry date ascending order */SELECT * from Sanguo ORDER by Sal Desc,hiredate ASC; /* Search for employees with a two-name week.  */SELECT * from Sanguo WHERE ename like ' Week _ '; /* Query all employees surnamed Zhang.  */SELECT * from Sanguo WHERE ename like ' Zhang% '; /* Query How many positions there are in the department, and how many people are in each position.  */SELECT Job,count (*) from the Sanguo GROUP by job; /* Query which post is more than 3 in number. */SELECT job from Sanguo GROUP by Job have COUNT (*) >3;

Mysql-3 Exercises (filter again after grouping queries, sort in reverse order)