mysql simple single linked list query exercise

Keywords: Database MySQL

  • Query all employee information.
  • Query the number, name and position of each employee.
  • Query all department information.
  • Query all department numbers with employees.
  • Query departments and positions with employees.
  • Query the annual salary of all employees.
  • Query the annual salary of all employees (use alias).
  • Query the annual salary of all employees ((monthly salary + Bonus) * 12).
  • Query employee information with bonus.
  • Inquire about the boss of the company.
  • Query the information of all employees whose basic salary is higher than 1500.
  • Query the work of an employee named SCOTT.
  • Query the information of employees who joined in 1981.
  • Query employees whose annual salary is less than 3W.
  • Query the information of all employees who are not salespeople.
  • Query employee information with salary between 2000-3000.
  • Query employees who joined in 1981.
  • Query employees with salary of 800 or 1600 or 3000.
  • Query all employee information whose names start with A.
  • Find out all employee information whose second letter is M.
  • Query all employee information containing the letter A in any position of the employee name.
  • Query the employee name with e or a in the name.
  • Query all employee information with salary between 1500 and 3000.
  • Query employee information whose salary is not between 2000-3000.
  • Query employees whose salary is not 800 or 1600 or 3000.
  • Find out all the information that the position is clerk (CLERK) or salesperson (SALESMAN), and the salary is more than 1000.
  • Query all employee information and sort by salary.
  • Query all employee information and sort by annual salary in descending order.
  • Query all employee information, and sort by department and annual salary in descending order.
  • Query employee number, employee name, and the number and name of the Department to which the employee belongs.
  • Query the employee's name, salary, department name and salary grade.
  • Query the average salary and total salary of all employees every month.
  • Query the total number of employees with a monthly salary of more than 2000.
  • Query the maximum wage and minimum wage gap of employees.
  • According to the position grouping, the maximum and minimum wages of each position are calculated.
  • Query the average bonus of employees in each department.
  • Query the average salary of employees in each department.
  • Query the average salary of each department and position.
  • Query the Department whose average salary is higher than 2000 and its average salary.
  • Query how many people entered the company in 80, 81 and 82.
  • Query the average salary of employees under each manager. The minimum salary cannot be less than 1300, and the boss is not calculated.
  • Query all employee information whose salary is higher than MARTIN.
  • Query the Department information where the average salary is higher than the average salary of the company.
  • Query the employee information whose salary is equal to the Department MANAGER (position is MANAGER).
  • Query the number, name, department number and average salary of each department.

Table information

/*
Navicat MySQL Data Transfer
Date: 2015-11-23 23:22:16
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `dept`
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `DEPTNO` bigint(2) PRIMARY KEY AUTO_INCREMENT COMMENT 'Indicates the department number, which is composed of two digits',
  `DNAME` varchar(14) COMMENT 'Department name, consisting of up to 14 characters',
  `LOC` varchar(13) COMMENT 'Location of Department'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dept
-- ACCOUNTING:Finance Department
-- RESEARCH:Research Department
-- SALES:Sales Department
-- OPERATIONS:Operation Department
-- ----------------------------
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');

-- ----------------------------
-- Table structure for `emp`
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `EMPNO` bigint(4) PRIMARY KEY AUTO_INCREMENT COMMENT 'Employee number, consisting of four digits',
  `ENAME` varchar(10) COMMENT 'Employee's name, consisting of 10 characters',
  `JOB` varchar(9)  COMMENT 'Employee's position',
  `MGR` bigint(4)  COMMENT 'The leader number corresponding to the employee. The leader is also an employee',
  `HIREDATE` date COMMENT 'Employee's date of employment',
  `SAL` double(7,2) COMMENT 'Basic salary, including two decimal places and five integers, a total of seven',
  `COMM` double(7,2) COMMENT 'Bonus, commission(Sales only)',
  `DEPTNO` bigint(2) COMMENT 'Employee's department number'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of emp
-- CLERK:staff member
-- SALESMAN:salesperson
-- MANAGER:division manager
-- ANALYST:analyst
-- PRESIDENT:CEO
-- ----------------------------
INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975.00', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850.00', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450.00', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', '0.00', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100.00', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', null, '10');

-- ----------------------------
-- Table structure for `salgrade`
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `GRADE` bigint(11) PRIMARY KEY AUTO_INCREMENT COMMENT 'Wage scale',
  `LOSAL` int(11)  COMMENT 'Minimum wage at this level',
  `HISAL` int(11)  COMMENT 'Maximum wage at this level'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of 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');

answer

-- Query all employee information.
SELECT * FROM emp

-- Query the number, name and position of each employee
SELECT emp.`DEPTNO`,emp.`ENAME`,emp.`JOB` FROM emp

-- Query all department information.
SELECT * FROM dept

-- Query departments and positions with employees.
SELECT emp.`DEPTNO`,emp.`JOB` FROM emp

-- Query the annual salary of all employees.
SELECT (emp.`SAL`*12)+IFNULL(emp.`COMM`,0) FROM emp

-- Query the annual salary of all employees (use alias).
SELECT (emp.`SAL`*12)+IFNULL(emp.`COMM`,0) year_sal FROM emp

-- Query the annual salary of all employees((a monthly salary + bonus) * 12) . 
SELECT (emp.`SAL` + IFNULL(emp.`COMM`,0))*12 FROM emp

-- Query employee information with bonus.
SELECT * FROM emp WHERE emp.`COMM` IS NOT NULL

-- Inquire about the boss of the company.
SELECT * FROM emp WHERE emp.`MGR` IS NULL

-- Query the information of all employees whose basic salary is higher than 1500.
SELECT * FROM emp WHERE emp.`SAL` > 1500

-- Query name SCOTT The work of our employees.
SELECT * FROM emp WHERE emp.`ENAME` = 'scott'

-- Query the information of employees who joined in 1981.
SELECT * FROM emp WHERE YEAR(emp.`HIREDATE`) = 1981

-- Query annual salary less than 3 W Employees.
SELECT * FROM emp WHERE (emp.`SAL`*12)+IFNULL(emp.`COMM`,0) < 30000

-- Query the information of all employees who are not salespeople.
SELECT * FROM emp WHERE emp.`JOB` != 'SALESMAN'

-- Query salary in 2000-3000 Employee information between.
SELECT * FROM emp WHERE emp.`SAL` BETWEEN  2000 AND 3000

-- Query employees who joined before 1981.
SELECT * FROM emp WHERE YEAR(emp.`HIREDATE`) < 1981

-- Query employees with salary of 800 or 1600 or 3000.
SELECT * FROM emp WHERE emp.`sal` IN(800,1600,3000)

-- Find out the names of all employees by A All employee information at the beginning.
SELECT * FROM emp WHERE emp.ename LIKE 'A%'

-- The second letter of the employee's name is M All employee information.
SELECT * FROM emp WHERE emp.`Ename` LIKE '_M%'

-- Find out the letters in any position of the employee's name A All employee information.
SELECT * FROM emp WHERE emp.`ENAME` LIKE '%a%'

-- There are in the query name e perhaps a Name of the employee.
SELECT * FROM emp WHERE emp.`ENAME` LIKE '%e%' OR emp.`ENAME` LIKE '%a%'

-- Query salary at 1500~3000 All employee information between.
SELECT * FROM emp WHERE emp.`SAL` BETWEEN 1500 AND 3000

-- Query salary is not in 2000-3000 Employee information between.
SELECT * FROM emp WHERE emp.`SAL` NOT BETWEEN  2000 AND 3000

-- Query employees whose salary is not 800 or 1600 or 3000.
SELECT * FROM emp WHERE emp.`SAL` NOT IN(800,1600,3000)

-- The position found is clerk (CLERK) Or a salesperson (SALESMAN) And the salary is more than 1000
SELECT * FROM emp WHERE (emp.`JOB` = 'clerk' OR emp.`JOB` = 'SALESMAN') AND emp.`SAL` > 1000

-- Query all employee information and sort by salary.
SELECT * FROM emp ORDER BY emp.`SAL` DESC

-- Query all employee information and sort by annual salary in descending order.
SELECT * FROM emp ORDER BY ((emp.`SAL`*12)+IFNULL(emp.`COMM`,0)) DESC

-- Query all employee information, and sort by department and annual salary in descending order.
SELECT * FROM emp ORDER BY emp.`DEPTNO` DESC , ((emp.`SAL`*12)+IFNULL(emp.`COMM`,0)) DESC

-- Query employee number, employee name, and the number and name of the Department to which the employee belongs.
SELECT emp.`EMPNO` ,emp.`ENAME` ,emp.`DEPTNO` ,dept.`DNAME` 
	FROM emp , dept 
	WHERE emp.`DEPTNO` = dept.`DEPTNO`
	
-- Query the employee's name, salary, department name and salary grade.
SELECT emp.`ENAME`,emp.`SAL`,dept.`DNAME`,salgrade.`GRADE` 
	FROM emp , dept ,salgrade
	WHERE emp.`DEPTNO` = dept.`DEPTNO` AND emp.`SAL` BETWEEN salgrade.`LOSAL` AND salgrade.`HISAL`
	
-- Query the average salary and total salary of all employees every month.
SELECT AVG(emp.`SAL`),SUM(emp.`SAL`+IFNULL(emp.`COMM`,0)) FROM emp

-- Query the total number of employees with a monthly salary of more than 2000.
SELECT COUNT(*) FROM emp WHERE emp.`SAL`>2000

-- Query the maximum wage and minimum wage gap of employees.
SELECT MAX(emp.`SAL`) - MIN(emp.`SAL`) FROM emp 

-- According to the position grouping, the maximum and minimum wages of each position are calculated.
SELECT emp.`JOB` , MAX(emp.`SAL`) - MIN(emp.`SAL`) FROM emp GROUP BY emp.`JOB`

-- Query the average bonus of employees in each department.
SELECT SUM(IFNULL(emp.`COMM`,0))/COUNT(*) sal,emp.`DEPTNO` FROM emp GROUP BY emp.`DEPTNO`

-- Query the average salary of employees in each department.
SELECT SUM(IFNULL(emp.`SAL`,0))/COUNT(*) avgsal FROM emp GROUP BY emp.`DEPTNO`

-- Query the average salary of each department and position.
SELECT SUM(emp.`SAL`)/COUNT(*) sal,emp.`DEPTNO`,emp.`JOB` FROM emp GROUP BY emp.`DEPTNO` ,emp.`JOB`

-- Query the Department whose average salary is higher than 2000 and its average salary.
SELECT dept.`DEPTNO` , AVG(emp.`SAL`) avgsal FROM emp,dept
 WHERE emp.`DEPTNO` = dept.`DEPTNO`
  GROUP BY emp.`DEPTNO`
   HAVING avgsal>2000
   
-- Query how many people entered the company in 80, 81 and 82.
SELECT * FROM emp WHERE  RIGHT(YEAR(emp.`HIREDATE`),2) IN(80,81,82)

-- Query the average salary of employees under each manager. The minimum salary cannot be less than 1300, and the boss is not calculated
SELECT AVG(emp.`SAL`) FROM emp GROUP BY emp.`MGR` HAVING emp.`MGR` IS NOT NULL

-- Query salary ratio MARTIN Higher total employee information
SELECT * FROM emp WHERE emp.`SAL` > (SELECT emp.`SAL` FROM emp WHERE emp.`ENAME` = 'MARTIN')

-- Query the Department information where the average salary is higher than the average salary of the company.
SELECT * FROM emp WHERE AVG(emp.`SAL`*12 + IFNULL(emp.`COMM`,0))

-- Query salary equals to Department Manager (position is) MANAGER)Employee information.
SELECT * FROM emp WHERE emp.`SAL` IN (SELECT emp.`SAL` FROM emp WHERE emp.`JOB` = 'manager')

-- Query the number, name, department number and average salary of each department.
SELECT dept.`DEPTNO` ,dept.`DNAME` ,COUNT(*) ,AVG(emp.`SAL`) 
	FROM emp,dept
	WHERE emp.`DEPTNO` = dept.`DEPTNO`
	GROUP BY emp.`DEPTNO`

Posted by syed on Thu, 04 Nov 2021 08:24:50 -0700

Hot Keywords

©2024 Programmer Group