MySQL Query statement exercises

/*1. Check out all employees with department number 30*//*Analysis: 1). Column: No column is specified to query, so all column 2 is queried. Table: Only one table, Emp3). Condition: Department number 30, i.e. deptno=30*/SELECT *  fromEmpWHEREDeptno= -;/**********************************************//*2. Name, number and department number of all sales staff. *//*Analysis: Column: Name ename, number empno, Department number deptno table: EMP Condition: All salespeople, that is, job= ' sales staff '*/SELECTEname,empno,deptno fromEmpWHEREJob='Sales Clerk'/**********************************************//*3. Find the employee with the bonus above the salary. *//*Analysis: Columns: All lists: EMP condition: Bonus > salary, i.e. Comm>sal*/SELECT *  fromEmpWHEREComm>Sal;/**********************************************//*4. Find employees with bonuses above 60% of their salary. *//*Analysis: Columns: All lists: EMP condition: Bonus > Salary *0.6, i.e. comm>sal*0.6*/SELECT *  fromEmpWHEREComm>Sal*0.6;/**********************************************//*5. Identify all the managers in the department Number 10, and the department number 20 for all sales staff details. *//*Analysis: Columns: All lists: EMP Condition: Department number = 10 and job for manager, and department number = 20 and job for salesperson*/SELECT *  fromEmpWHERE(Deptno=Ten  andJob='Manager')OR(Deptno= -  andJob='Sales Clerk');/**********************************************//*6. Identify all the managers in the department Number 10, the department number 20 for all salespeople, and the details of all employees who are not managers and are not salespeople but whose wages are large or equal to 20000. *//*Analysis: Columns: All lists: EMP condition: deptno=10 and job= ' manager ', depnto=20 and job= ' salesperson ', Job not in (' Salesperson ', ' manager ') and sal>=20000*/SELECT *  fromEMPWHERE(Deptno=Ten  andJob='Manager')   OR(Deptno= -  andJob='Sales Clerk')   ORJob not inch('Manager','Sales Clerk') andSal>=20000;/**********************************************//*7. Jobs with bonuses. *//*columns: Work (cannot recur) Table: EMP Condition: Comm is not null*/SELECT DISTINCTJob fromEmpWHEREComm is  not NULL;/**********************************************//*8. No bonuses or bonuses less than 1000 of employees. *//*Analysis: Columns: All lists: EMP condition: Comm is null or comm <*/SELECT *  fromEmpWHEREComm is NULL ORComm<  +;/**********************************************//*9. Query employees whose names consist of three characters. *//*Analysis: Columns: All tables: EMP condition: ename like ' ___ '*/SELECT *  fromEmpWHEREEname like '___'/**********************************************//*10. Search for employees who have entered the workforce in 2000. *//*Analysis: Columns: All tables: EMP condition: hiredate like ' 2000% '*/SELECT *  fromEmpWHEREHireDate like '2000%';/**********************************************//*11. Query all employee details and sort by number in ascending order*//*analysis; columns: All tables: EMP Condition: No order: empno ASC*/SELECT *  fromEmpORDER  byEmpnoASC;/**********************************************//*12. Query all employee details, sorted in descending order of wages, if the same wage is used in ascending order of the date of entry*//*Analysis: Columns: All tables: EMP Condition: No order: Sal Desc, hiredate ASC*/SELECT *  fromEmpORDER  bySalDESC, HireDateASC/**********************************************//*13. Check the average salary for each department*//*Analysis: Column: Department number, average wage (average wage is group information) Table: EMP Condition: No grouping: Each department, i.e., using departmental groupings, average wages, using the AVG () function*/SELECTDeptnoAVG(SAL) fromEmpGROUP  byDeptno;/**********************************************//*14. Find out the number of employees in each department. *//*Analysis: Column: Department number, number of personnel (number of people is the number of records, this is the group information) Table: EMP Condition: No grouping: Each department is a group of information, the number of people, using the count () function*/SELECTDeptnoCOUNT(1) fromEmpGROUP  byDeptno;/**********************************************//*15. Query the maximum wage, minimum wage, number of persons for each job: department, maximum wage, minimum wage, number of persons (of which the highest wage, minimum wage, number of persons are grouped information) Table: EMP Condition: No grouping: Each salary is a group of information, the maximum wage is using Max (SAL), Minimum wage use min (sal), number of people using count (*)*/SELECTJobMAX(SAL),MIN(SAL),COUNT(1) fromEmpGROUP  byjob;/**********************************************//*16. Display the non-salesperson's job name and the sum of monthly wages for the same job employee, and to meet the total monthly salary of the employee who is engaged in the same job is greater than 50000, the output is listed in ascending order of monthly wages*//*columns: Job name, payroll, and (Group information) Table: EMP Condition: No grouping: Wage for the same job and, that is, use the job grouping condition: Payroll Total >50000, which is a grouping condition, not a Where condition sort: payroll total sort, that is, SUM (SAL) ASC */SELECTJobSUM(SAL) fromEmpGROUP  byJob having SUM(SAL)>50000 ORDER  by SUM(SAL)ASC;

MySQL Query statement exercises