<>1. task 1
<>2. task 2
-- 2. Write see DEPT Table and EMP Table structure of sql sentence homework02.sql 10min Practice yourself first -- DESC dept DESC
emp-- 3. Complete with simple query statement : -- (1) Show all department names . SELECT dname FROM dept; -- (2)
Show all employee names and their annual income 13 month ( wages + subsidy ), And specify the column alias " annual income " SELECT ename, (sal + IFNULL(comm,0)) * 13 AS
" annual income " FROM emp SELECT * FROM emp; -- -- 4. Restrict query data . -- (1) Show salary over 2850 Name and salary of employee .
SELECT ename, sal FROM emp WHERE sal > 2850 -- (2) Show salary not in 1500 reach 2850 Name and salary of all employees between .
SELECT ename, sal FROM emp WHERE sal < 1500 OR sal > 2850 SELECT ename, sal
FROM empWHERE NOT (sal >= 1500 AND sal <= 2850) -- Reverse operation -- (3) Display number is 7566
Employee's name and department number .SELECT ename, deptno FROM emp WHERE empno = 7566 -- (4) Display department 10 and 30
Medium salary exceeds 1500 Employee name and salary . SELECT ename, sal FROM emp WHERE (deptno = 10 OR deptno = 30)
AND sal> 1500 -- (5) Display employee name and position without Manager . SELECT ename, job FROM emp WHERE mgr IS NULL
; -- -- 5. Sort data . -- (1) Show in 1991 year 2 month 1 Day arrival 1991 year 5 month 1 Employees employed between days , Position and date of employment , -- And sorted by employment date [ default ]
.-- thinking 1. Query the corresponding results first 2. Consider sorting SELECT ename, job, hiredate FROM emp WHERE hiredate
>= '1991-02-01' AND hiredate <= '1991-05-01' ORDER BY hiredate -- (2)
Display the names of all employees who received the subsidy , Wages and subsidies , In descending order of salary SELECT ename, sal, comm FROM emp ORDER BY sal DESC
<>3. task 3
-- homework03 -- ------1. Select Department 30 All employees in . SELECT * FROM emp WHERE deptno = 30 --
------2. List all clerks (CLERK) Name of , Number and department number . SELECT ename, empno, deptno, job FROM emp
WHERE job = 'CLERK' -- ------3. Identify employees whose commissions are higher than their salaries . SELECT * FROM emp WHERE IFNULL(comm,
0) > sal -- ------4. Find out that commission is higher than salary 60% Employees of . SELECT * FROM emp WHERE IFNULL(comm, 0) >
sal* 0.6 -- ------5. Find the Department 10 All managers in (MANAGER) And department 20 All clerks in (CLERK) Details of . -- SELECT *
FROM emp WHERE(deptno = 10 AND job = 'MANAGER') OR (deptno = 20 AND job =
'CLERK') -- ------6. Find the Department 10 All managers in (MANAGER), department 20 All clerks in (CLERK), --
There are also those who are neither managers nor clerks but whose salaries are greater than or equal to 2000 Details of all employees of . SELECT * FROM emp WHERE (deptno = 10 AND
job= 'MANAGER') OR (deptno = 20 AND job = 'CLERK') OR (job != 'MANAGER' AND job
!= 'CLERK' AND sal >= 2000 ) -- ------7. Identify the different jobs of employees who receive commissions . SELECT DISTINCT job
FROM emp WHERE comm IS NOT NULL-- ------8. Find out if no commission is charged or if the commission charged is less than 100 Employees of . SELECT * FROM
emp WHERE comm IS NULLOR IFNULL(comm, 0) < 100 -- ------9. Find the penultimate of each month 3 All employees employed for days . --
Tips : last_day( date ), You can return to the last day of the month in which the date is located -- last_day( date ) - 2 Get the penultimate of all months of the date 3 day SELECT *
FROM empWHERE LAST_DAY(hiredate) - 2 = hiredate -- ------10. Find earlier than 12 Employees employed before .（ Namely ：
More than 12 year ） SELECT * FROM emp WHERE DATE_ADD(hiredate, INTERVAL 12 YEAR) < NOW()
-- -- ------11. Display the names of all employees in lowercase . SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),
SUBSTRING(ename,2)) FROM emp; -- ------12. Display exactly 5 Employee's name of characters . SELECT * FROM emp
WHERE LENGTH(ename) = 5 -- ------13. Display without "R" Name of employee . SELECT * FROM emp WHERE
ename NOT LIKE'%R%' -- ------14. Display the first three characters of all employee names . SELECT LEFT(ename,3) FROM emp --
------15. Show names of all employees , use a Replace all "A" SELECT REPLACE(ename, 'A', 'a') FROM emp -- ------
16. Display full 10 Name and date of employment of employees with years of service . SELECT ename, hiredate FROM emp WHERE DATE_ADD(
hiredate, INTERVAL 10 YEAR) <= NOW() -- ------17. Show employee details , Sort by name . SELECT * FROM
emp ORDER BY ename-- ------18. Displays the employee's name and date of employment , According to their service life , Put the oldest employees at the top . -- SELECT ename,
hiredate FROM emp ORDER BY hiredate-- ------19. Show names of all employees , Work and salary , Sort by work descending ,
Sort by salary if jobs are the same . SELECT ename, job, sal FROM emp ORDER BY job DESC, sal -- ------20.
Show names of all employees , Year and month of joining the company , Sort by month of employment date , -- If the months are the same, the employees of the earliest year will be ranked first . SELECT ename, CONCAT(
YEAR(hiredate),'-', MONTH(hiredate)) FROM emp ORDER BY MONTH(hiredate), YEAR(
hiredate) -- ------21. Displayed in one month as 30 Daily salary of all employees , Ignore remainder . SELECT ename, FLOOR(sal / 30),
sal/ 30 FROM emp; -- ------22. Find out where ( In any year )2 All employees employed in the month . SELECT * FROM emp WHERE
MONTH(hiredate) = 2 -- ------23. For each employee , Show the number of days they joined the company . -- SELECT ename, DATEDIFF(NOW
(), hiredate) FROM emp -- ------24. Show name fields anywhere containing "A" Names of all employees of . SELECT * FROM emp
WHERE ename LIKE'%A%' -- ------25. Display the service years of all employees in the form of month, year and day . ( Probably ) -- thinking 1. Find out first How many days have you worked
SELECT ename, FLOOR(DATEDIFF(NOW(), hiredate) / 365) AS " Working year ", FLOOR((DATEDIFF
(NOW(), hiredate) % 365) / 31) AS " Work month ", DATEDIFF(NOW(), hiredate) % 31 AS "
Working days " FROM emp;
<>4. task 4
-- -- (1)． List all departments with at least one employee /* First find out how many people there are in each department use having Clause filtering */ SELECT COUNT(*) AS c,
deptno FROM emp GROUP BY deptno HAVING c>= 1 -- (2)． List salary ratio “SMITH” All employees . /* Find out first
smith of sal => As subquery Then other employees sal greater than smith that will do */ SELECT * FROM emp WHERE sal > (
SELECT sal FROM empWHERE ename = 'SMITH' ) -- (3)． List all employees whose employment date is later than their immediate supervisor . /* First emp
surface As two tables worker , leader condition 1. worker.hiredate > leader.hiredate 2. worker.mgr =
leader.empno */ SELECT worker.ename AS ' Employee name ', worker.hiredate AS ' Employee induction time ',
leader.ename AS ' Superior name ', leader.hiredate AS ' Superior employment time ' FROM emp worker , emp leader
WHERE worker.hiredate > leader.hiredate AND worker.mgr = leader.empno; -- (4)
． List Department names and employee information for these departments , Also list those departments that do not have employees ./* All departments need to be displayed here , So consider using external connections ,( Left outer connection )
If there is no impression , Go and have a look at the teacher's external connection . */ SELECT dname, emp.* FROM dept LEFT JOIN emp ON dept.
deptno= emp.deptno -- (5)． List all “CLERK”（ clerk ） Name and department name of . SELECT ename, dname , job
FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno -- (6)． List minimum salary greater than
1500 All kinds of work . /* Query the minimum wage for each job use having Clause to filter */ SELECT MIN(sal) AS min_sal , job
FROM emp GROUP BY job HAVING min_sal> 1500 -- (7)． List in Department “SALES”（ Sales Department ） Name of employee working .
SELECT ename, dname FROM emp , dept WHERE emp.deptno = dept.deptno AND dname =
'SALES' -- (8)． List all employees whose salary is higher than the average salary of the company . SELECT * FROM emp WHERE sal > ( SELECT AVG(sal
) FROM emp ) -- (9)． List and “SCOTT” All employees engaged in the same work . SELECT * FROM emp WHERE job = (
SELECT job FROM empWHERE ename = 'SCOTT' ) AND ename != 'SCOTT' -- (10)
． List departments with higher salary 30 Salary of all employees working for employee name and salary . -- Query first 30 Maximum wage of the Department SELECT ename, sal FROM emp
WHERE sal> ( SELECT MAX(sal) FROM emp WHERE deptno = 30 ) -- (11)
． List the number of employees working in each department , Average salary and average service period ( Time unit ). -- Teacher's suggestion , write sql It was done step by step SELECT COUNT(*) AS
" Number of department employees ", deptno , AVG(sal) AS " Department average wage " , FORMAT(AVG(DATEDIFF(NOW(), hiredate) /
365 ),2) AS " Average service period ( year )" FROM emp GROUP BY deptno -- (12)． List the names of all employees , Department name and salary . --
namely emp and dept Joint query , The connection condition is emp.deptno = dept.deptno -- (13)． List details and number of people in all departments . --
1. Get the number of people in each department first , Consider the following results as a temporary table and dept Table union query SELECT COUNT(*) AS c , deptno FROM emp
GROUP BY deptno-- 2. SELECT dept.*, tmp.c AS " Number of departments " FROM dept, ( SELECT COUNT(*)
AS c , deptno FROM emp GROUP BY deptno ) tmp WHERE dept.deptno = tmp.deptno -- (
14)． List minimum wages for various jobs . SELECT MIN(sal), job FROM emp GROUP BY job -- (15)
． list MANAGER（ manager ） Minimum salary .SELECT MIN(sal), job FROM emp WHERE job = 'MANAGER' -- (16)
． List the annual salary of all employees , Sort by annual salary from low to high . -- 1. Get the employee's annual salary first SELECT ename, (sal + IFNULL(comm, 0)) *
12 year_sal FROM emp ORDER BY year_sal -- Technical window paper
<>5. task 5
-- Complete the last comprehensive exercise -- 8. The school environment is as follows : A department has several Majors , Each major can only recruit one class a year , How many students are there in each class . --
Now we want to establish a system about , student , Class database , The relationship mode is ：-- class CLASS
（ Class number classid, Major name subject, Family name deptname, Enrollment Year enrolltime, Number of persons num）-- student STUDENT
（ Student ID studentid, full name name, Age age, Class number classid）-- system DEPARTMENT （ Serial number departmentid, Family name deptname）
-- on trial SQL Language completes the following functions ： homework05.sql 10min -- -- (1) Build table , Declaration required in definition ： -- （1） Primary foreign code of each table . --
（2）deptname Is a unique constraint . -- （3） Student name cannot be blank . -- Create table system DEPARTMENT
（ Serial number departmentid, Family name deptname） CREATETABLE DEPARTMENT ( departmentid VARCHAR(32)
PRIMARY KEY, deptname VARCHAR(32) UNIQUE NOT NULL); -- class CLASS
（ Class number classid, Major name subject, Family name deptname, Enrollment Year enrolltime, Number of persons num） CREATE TABLE `class` (
classid INTPRIMARY KEY, `subject` VARCHAR(32) NOT NULL DEFAULT '', deptname
VARCHAR(32) , -- Foreign key field , Specify after table definition enrolltime INT NOT NULL DEFAULT 2000, num INT NOT
NULLDEFAULT 0, FOREIGN KEY (deptname) REFERENCES DEPARTMENT(deptname)); --
student STUDENT （ Student ID studentid, full name name, Age age, Class number classid） CREATETABLE hsp_student (
studentid INTPRIMARY KEY, `name` VARCHAR(32) NOT NULL DEFAULT '', age INT NOT
NULLDEFAULT 0, classid INT, -- Foreign key FOREIGN KEY (classid) REFERENCES `class`(
classid)); -- Add test data INSERT INTO department VALUES('001',' mathematics '); INSERT INTO
departmentVALUES('002',' computer '); INSERT INTO department VALUES('003',' Chemistry ');
INSERT INTO departmentVALUES('004',' chinese '); INSERT INTO department VALUES('005',
' Economics '); INSERT INTO class VALUES(101,' Software ',' computer ',1995,20); INSERT INTO class
VALUES(102,' microelectronics ',' computer ',1996,30); INSERT INTO class VALUES(111,' inorganic chemistry ',' Chemistry ',1995,
29); INSERT INTO class VALUES(112,' Polymer chemistry ',' Chemistry ',1996,25); INSERT INTO class
VALUES(121,' Statistical Mathematics ',' mathematics ',1995,20); INSERT INTO class VALUES(131,' modern languages ',' chinese ',1996,
20); INSERT INTO class VALUES(141,' international trade ',' Economics ',1997,30); INSERT INTO class VALUES
(142,' international finance ',' Economics ',1996,14); INSERT INTO hsp_student VALUES(8101,' Zhang San ',18,101);
INSERT INTO hsp_studentVALUES(8102,' Qian Si ',16,121); INSERT INTO hsp_student VALUES(
8103,' Wang Ling ',17,131); INSERT INTO hsp_student VALUES(8105,' Li Fei ',19,102); INSERT
INTO hsp_studentVALUES(8109,' Zhaosi ',18,141); INSERT INTO hsp_student VALUES(8110,
' Li Ke ',20,142); INSERT INTO hsp_student VALUES(8201,' Zhang Fei ',18,111); INSERT INTO
hsp_studentVALUES(8302,' Zhou Yu ',16,112); INSERT INTO hsp_student VALUES(8203,' Wang Liang ',17
,111); INSERT INTO hsp_student VALUES(8305,' Dongqing ',19,102); INSERT INTO hsp_student
VALUES(8409,' Zhaolong ',18,101); SELECT * FROM department SELECT * FROM class SELECT *
FROM hsp_student-- (3) Complete the following query functions -- 3.1 Find out all the students surnamed Li . -- Look up table hsp_student , like
SELECT* FROM hsp_student WHERE `name` LIKE ' Lee %' -- 3.2 List all open more than 1 The names of major departments . -- 1.
First, check the number of majors in each department SELECT COUNT(*) AS nums, deptname FROM class GROUP BY deptname
HAVING nums> 1 -- 3.3 List the number of people greater than or equal to 30 Number and name of the Department of . -- 1. First find out how many people there are in each department , And get >= 30 System of SELECT
SUM(num) AS nums, deptname FROM class GROUP BY deptname HAVING nums >= 30 -- 2.
Consider the above results as a temporary table and department Joint query SELECT tmp.*, department.departmentid FROM
department, ( SELECT SUM(num) AS nums, deptname FROM class GROUP BY deptname
HAVING nums>= 30 ) tmp WHERE department.deptname = tmp.deptname; -- (4)
A new physics department has been added to the school , No 006 -- Add a piece of data INSERT INTO department VALUES('006',' physics department '); -- (5)
Student Zhang San dropped out , Please update the related table -- analysis ：1. Number of Zhang San's class -1 2. Delete Zhang San from the student list 3. Transaction control is required -- Open transaction START
TRANSACTION; -- Number of Zhang San's class -1 UPDATE class SET num = num - 1 WHERE classid = (
SELECT classid FROM hsp_studentWHERE NAME = ' Zhang San ' ); DELETE FROM hsp_student
WHERE NAME = ' Zhang San '; -- Commit transaction COMMIT; SELECT * FROM hsp_student; SELECT * FROM
class
Technology