Lecturer: Song Hongkang
Microblog: Shang Silicon Valley - Song Hongkang
Section 1 Selection and filtering
[Title] # 1. Query the total salary of employees for 12 months and alias it ANNUAL SALARY # 2. Query the employees table to remove duplicate jobs_ Data after ID # 3. Query the name and salary of employees whose salary is greater than 12000 # 4. Query the name and department number of the employee with employee number 176 # 5. Select the name and salary of employees whose salary is not between 5000 and 12000 # 6. Select the name and department number of the employee working in department 20 or 50 # 7. Select the names and jobs of employees without managers in the company_ id # 8. Select the name, salary and bonus level of employees with bonus in the company # 9. Select the employee whose third letter is a # 10. Select the employee's name with letters a and e in the name
1. Query the total salary of employees for 12 months and alias it ANNUAL SALARY
SELECT employee_id , last_name, salary * 12 "ANNUAL SALARY" FROM employees;
2. Query the employees table to remove duplicate jobs_ Data after ID
SELECT DISTINCT job_id FROM employees;
3. Query the name and salary of employees whose salary is greater than 12000
SELECT last_name, salary FROM employees WHERE salary > 12000;
4. Query the name and department number of the employee with employee number 176
SELECT last_name, department_id FROM employees WHERE employee_id = 176;
5. Select the name and salary of employees whose salary is not between 5000 and 12000
SELECT last_name, salary FROM employees WHERE salary < 5000 OR salary > 12000;
SELECT last_name, salary FROM employees WHERE salary NOT BETWEEN 5000 AND 12000;
6. Select the name and department number of the employee working in department 20 or 50
SELECT last_name, department_id FROM employees WHERE department_id = 20 OR department_id = 50;
SELECT last_name, department_id FROM employees WHERE department_id IN(20, 50);
7. Select the names and jobs of employees without managers in the company_ id
SELECT last_name, job_id FROM employees WHERE manager_id IS NULL;
8. Select the name, salary and bonus level of employees with bonus in the company
SELECT last_name, salary, commission_pct FROM employees WHERE commission_pct IS NOT NULL;
9. The third letter of the selected employee's name is the employee's name of a
SELECT last_name FROM employees WHERE last_name LIKE '__a%';
10. Select the employee's name with letters a and e in the name
SELECT last_name FROM employees WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';
Section 2 multi table query-1
[Title] # 1. Display the names, Department numbers and department names of all employees. # 2. Query the job of the employee in department 90_ ID and location of department 90_ id # 3. Select the last of all employees with bonus_ name , department_ name , location_ id , city # 4. Select the last name of the city's employees working in Toronto_ name , job_ id , department_ id , department_ name # 5. Select the name and employee number of the designated employee, as well as the name and employee number of his manager. The result is similar to the following format employees Emp# manager Mgr# kochhar 101 king 100
1. Display the names, Department numbers and department names of all employees.
SELECT last_name, e.department_id, department_name FROM employees e LEFT OUTER JOIN departments d ON e.`department_id` = d.`department_id`;
2. Query the job of the employee in department 90_ ID and location of department 90_ id
SELECT job_id, location_id FROM employees e, departments d WHERE e.`department_id` = d.`department_id` AND e.`department_id` = 90;
or
SELECT job_id, location_id FROM employees e JOIN departments d ON e.`department_id` = d.`department_id` WHERE e.`department_id` = 90;
3. Select the last of all employees with bonus_ name , department_ name , location_ id , city
SELECT last_name , department_name , d.location_id , city FROM employees e LEFT OUTER JOIN departments d ON e.`department_id` = d.`department_id` LEFT OUTER JOIN locations l ON d.`location_id` = l.`location_id` WHERE commission_pct IS NOT NULL;
4. Select the last name of the city's employees working in Toronto_ name , job_ id , department_ id , department_ name
SELECT last_name , job_id , e.department_id , department_name FROM employees e, departments d, locations l WHERE e.`department_id` = d.`department_id` AND d.`location_id` = l.`location_id` AND city = 'Toronto';
or
SELECT last_name , job_id , e.department_id , department_name FROM employees e JOIN departments d ON e.`department_id` = d.`department_id` JOIN locations l ON l.`location_id` = d.`location_id` WHERE l.`city` = 'Toronto';
5. Select the name and employee number of the designated employee, as well as the name and employee number of his manager. The result is similar to the following format
employees Emp# manager Mgr#
kochhar 101 king 100
SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager, mgr.employee_id "Mgr#" FROM employees emp LEFT OUTER JOIN employees mgr ON emp.manager_id = mgr.employee_id;
Section 2 multi table query-2
Reserve: table creation: CREATE TABLE `t_dept` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `address` VARCHAR(40) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `t_emp` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) DEFAULT NULL, `age` INT(3) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL, empno int not null, PRIMARY KEY (`id`), KEY `idx_dept_id` (`deptId`) #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO t_dept(deptName,address) VALUES('Huashan Mountain','Huashan Mountain'); INSERT INTO t_dept(deptName,address) VALUES('a group of beggars','Luoyang'); INSERT INTO t_dept(deptName,address) VALUES('Mount Emei','Mount Emei'); INSERT INTO t_dept(deptName,address) VALUES('Wudang','Wudang Mountain'); INSERT INTO t_dept(deptName,address) VALUES('Ming religion','Bright Summit'); INSERT INTO t_dept(deptName,address) VALUES('Shaolin Temple','Shaolin Temple'); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Breezy',90,1,100001); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('yue buqun',50,1,100002); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('linghu chong',24,1,100003); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('master hongqi',70,2,100004); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Qiao Feng',35,2,100005); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Abbess extinction',70,3,100006); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Zhou Zhiruo',20,3,100007); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Zhang Sanfeng',100,4,100008); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('zhang wuji',25,5,100009); INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('Wei Xiaobao',18,null,100010); [Title] #1. Information of all sect members ( A,B (shared by two tables) #2. List all users and display their organization information (A (complete set of) #3. List all sects (B (complete set of) #4. All non sect members (A (unique to) #5. All sects that no one enters (B (unique to) #6. List the cross reference relationship of all personnel and institutions (AB All have) #MySQL Full Join is implemented because MySQL does not support FULL JOIN. The following is an alternative method #Left join + union + right join #7. List all the people and sects who are not assigned (A Unique+B (unique to)
1. Information of all sect members
(shared by tables A and B)
select * from t_emp a inner join t_dept b on a.deptId = b.id;
2. List all users and display their organization information
(complete works of A)
select * from t_emp a left join t_dept b on a.deptId = b.id;
3. List all sects
(complete works of B)
select * from t_dept b;
4. All non sect members
(unique to A)
select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null;
5. All sects that no one enters
(unique to B)
select * from t_dept b left join t_emp a on a.deptId = b.id where a.deptId is null;
6. List the cross reference relationship of all personnel and institutions
(all AB)
#MySQL Full Join is implemented because MySQL does not support FULL JOIN. The following is an alternative method #Left join + union + right join SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id UNION SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id
7. List all the people and sects who are not assigned
(unique to A + unique to B)
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL UNION SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;
Section 3 single line function
[Title] # 1. Display system time (Note: date + time) # 2. Query the employee number, name, salary, and the result after the salary is increased by 20% (new salary) # 3. Sort the names of employees by initials and write the length of the names # 4. Query employee id,last_name,salary, and output as a column, alias OUT_PUT # 5. Make a query and produce the following results <last_name> earns <salary> monthly but wants <salary*3> Dream Salary King earns 24000 monthly but wants 72000 # 6. Use case when according to the following conditions: job grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E The following results are produced Last_name Job_id Grade king AD_PRES A
1. Display system time (Note: date + time)
SELECT NOW() FROM DUAL;
2. Query the employee number, name, salary, and the result after the salary is increased by 20% (new salary)
SELECT employee_id, last_name, salary, salary * 1.2 "new salary" FROM employees;
3. Sort the names of employees by initials and write the length of the names
SELECT last_name, LENGTH(last_name) FROM employees ORDER BY last_name DESC;
4. Query employee id,last_name,salary, and output as a column, alias OUT_PUT
SELECT CONCAT(employee_id, ',' , last_name , ',', salary) OUT_PUT FROM employees;
5. Make a query and produce the following results
– <last_name> earns <salary> monthly but wants <salary*3>
– Dream Salary
– King earns 24000 monthly but wants 72000
SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary, 0) , ' monthly but wants ', TRUNCATE(salary * 3, 0)) "Dream Salary" FROM employees;
6. Use case when according to the following conditions:
– job grade
– AD_PRES A
– ST_MAN B
– IT_PROG C
– SA_REP D
– ST_CLERK E
– produce the following results
– Last_name Job_id Grade
– king AD_PRES A
SELECT last_name Last_name, job_id Job_id, CASE job_id WHEN 'AD_PRES' THEN 'A' WHEN 'ST_MAN' THEN 'B' WHEN 'IT_PROG' THEN 'C' WHEN 'SA_REP' THEN 'D' WHEN 'ST_CLERK' THEN 'E' ELSE 'F' END "grade" FROM employees;
Section 4 grouping function
[Title] #1. Can the where clause use group functions for filtering? #2. Query the maximum value, minimum value, average value and total of employee salary #3. Query each job_ The maximum value, minimum value, average value, and sum of employee wages of ID #4. Select each job_ Number of employees with ID # 5. Query the DIFFERENCE between the maximum wage and the minimum wage # 6. Query the minimum wage of employees under each manager. The minimum wage cannot be less than 6000, and employees without managers are not included # 7. Query the names and locations of all departments_ ID, number of employees and average salary
#1. Can the where clause use group functions for filtering?
No!
#2. Query the maximum value, minimum value, average value and total of employee salary
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary) FROM employees;
#3. Query each job_ The maximum value, minimum value, average value, and sum of employee wages of ID
SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary) FROM employees GROUP BY job_id;
#4. Select each job_ Number of employees with ID
SELECT job_id, COUNT(*) FROM employees GROUP BY job_id;
5. Query the DIFFERENCE between the maximum wage and the minimum wage
SELECT MAX(salary), MIN(salary), MAX(salary) - MIN(salary) DIFFERENCE FROM employees;
6. Query the minimum wage of employees under each manager. The minimum wage cannot be less than 6000, and employees without managers are not included
#select min(salary) #from employees #group by manager_id #having min(salary) > 6000 #and manager_id is not null; SELECT manager_id, MIN(salary) FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary) > 6000;
7. Query the names and locations of all departments_ ID, number of employees and average salary
SELECT department_name, location_id, COUNT(employee_id), AVG(salary) FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id` GROUP BY department_name, location_id;
Section 5 sub query
[Title] #1. Query the name and salary of employees in the same department as Zlotkey #2. Query the employee number, name and salary of employees whose salary is higher than the average salary of the company. #3. Query the employee number, name and salary of employees in each department whose salary is higher than the average salary of the Department (difficult) #4. Query the employee number and name of the employee whose name contains the letter u in the same department #5. Query the location of the Department_ Employee number of the employee working in the Department with ID 1700 #6. Query the name and salary of employees whose manager is King #7. Query the minimum wage employee information: last_name, salary #8. Query the information of the Department with the lowest average wage #9. Query the information of the Department with the lowest average wage and the average wage of the Department (difficult) #10. Query the job information with the highest average salary #11. Query the departments whose average salary is higher than the average salary of the company? #12. Query the details of all manager s in the company #13. What is the minimum wage of the lowest of the highest wages in each department? #14. Query the manager details of the Department with the highest average salary: last_name, department_id, email, salary
1. Query the name and salary of employees in the same department as Zlotkey
SELECT last_name, salary FROM employees WHERE department_id = ( SELECT department_id FROM employees WHERE last_name = 'Zlotkey' )
2. Query the employee number, name and salary of employees whose salary is higher than the average salary of the company
SELECT employee_id, last_name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees )
3. Query the employee number, name and salary of employees in each department whose salary is higher than the average salary of the Department (difficult)
Method 1: related sub query
SELECT employee_id,last_name,salary FROM employees e1 WHERE salary > ( # Query the average of an employee's Department SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.`department_id` );
Mode 2:
SELECT employee_id,last_name,salary FROM employees e1, (SELECT department_id,AVG(salary) avg_sal FROM employees e2 GROUP BY department_id ) dept_avg_sal WHERE e1.`department_id` = dept_avg_sal.department_id AND e1.`salary` > dept_avg_sal.avg_sal;
4. Query the employee number and name of the employee whose name contains the letter u in the same department
SELECT employee_id, last_name FROM employees WHERE department_id = ANY( SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%' )
5. Query the location of the Department_ Employee number of the employee working in the Department with ID 1700
SELECT employee_id FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE location_id = 1700 )
6. Query the name and salary of employees whose manager is King
SELECT last_name, salary FROM employees WHERE manager_id IN ( SELECT employee_id FROM employees WHERE last_name = 'King' )
7. Query the minimum wage employee information: last_name, salary
SELECT last_name,salary FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees );
8. Query the information of the Department with the lowest average wage
#Mode 1:
SELECT * FROM departments WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) = ( SELECT MIN(dept_avgsal) FROM ( SELECT AVG(salary) dept_avgsal FROM employees GROUP BY department_id ) avg_sal ) );
#Mode 2:
SELECT * FROM departments WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) <= ALL( SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id ) );
#Mode 3:
SELECT * FROM departments WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) = ( SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id ORDER BY avg_sal LIMIT 0,1 ) )
#Mode 4:
SELECT d.* FROM departments d,( SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id ORDER BY avg_sal LIMIT 0,1) dept_avg_sal WHERE d.department_id = dept_avg_sal.department_id
9. Query the information of the Department with the lowest average wage and the average wage of the Department (difficult)
#Mode 1:
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal FROM departments d WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) = ( SELECT MIN(dept_avgsal) FROM ( SELECT AVG(salary) dept_avgsal FROM employees GROUP BY department_id ) avg_sal ) );
#Mode 2:
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal FROM departments d WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) <= ALL( SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id ) );
#Mode 3:
SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal FROM departments d WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) = ( SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id ORDER BY avg_sal LIMIT 0,1 ) )
#Mode 4:
SELECT d.*,dept_avg_sal.avg_sal FROM departments d,( SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id ORDER BY avg_sal LIMIT 0,1) dept_avg_sal WHERE d.department_id = dept_avg_sal.department_id
10. Query the job information with the highest average salary
#Mode 1:
SELECT * FROM jobs WHERE job_id = ( SELECT job_id FROM employees GROUP BY job_id HAVING AVG(salary) = ( SELECT MAX(avg_sal) FROM( SELECT AVG(salary) avg_sal FROM employees GROUP BY job_id ) job_avgsal ) );
#Mode 2:
SELECT * FROM jobs WHERE job_id = ( SELECT job_id FROM employees GROUP BY job_id HAVING AVG(salary) >= ALL( SELECT AVG(salary) FROM employees GROUP BY job_id ) );
#Mode 3:
SELECT * FROM jobs WHERE job_id = ( SELECT job_id FROM employees GROUP BY job_id HAVING AVG(salary) = ( SELECT AVG(salary) avg_sal FROM employees GROUP BY job_id ORDER BY avg_sal DESC LIMIT 0,1 ) );
#Mode 4:
SELECT j.* FROM jobs j,( SELECT job_id,AVG(salary) avg_sal FROM employees GROUP BY job_id ORDER BY avg_sal DESC LIMIT 0,1 ) job_avg_sal WHERE j.job_id = job_avg_sal.job_id
11. Query the departments whose average salary is higher than the average salary of the company?
SELECT department_id FROM employees WHERE department_id IS NOT NULL GROUP BY department_id HAVING AVG(salary) > ( SELECT AVG(salary) FROM employees );
12. Query the details of all manager s in the company
#Mode 1:
SELECT employee_id,last_name,salary FROM employees WHERE employee_id IN ( SELECT DISTINCT manager_id FROM employees );
#Mode 2:
SELECT DISTINCT e1.employee_id, e1.last_name, e1.salary FROM employees e1 JOIN employees e2 WHERE e1.employee_id = e2.manager_id;
#Mode 3:
SELECT employee_id, last_name, salary FROM employees e1 WHERE EXISTS ( SELECT 'X' FROM employees e2 WHERE e2.manager_id = e1.employee_id);
13. What is the minimum wage of the lowest of the highest wages in each department?
#Mode 1
SELECT MIN(salary) FROM employees WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING MAX(salary) = ( SELECT MIN(max_sal) FROM ( SELECT MAX(salary) max_sal FROM employees GROUP BY department_id) dept_max_sal ) ); SELECT * FROM employees WHERE department_id = 10;
#Mode II
SELECT MIN(salary) FROM employees WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING MAX(salary) <= ALL( SELECT MAX(salary) max_sal FROM employees GROUP BY department_id ) );
#Mode 3:
SELECT MIN(salary) FROM employees WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING MAX(salary) = ( SELECT MAX(salary) max_sal FROM employees GROUP BY department_id ORDER BY max_sal LIMIT 0,1 ) )
#Mode 4:
SELECT employee_id,MIN(salary) FROM employees e, (SELECT department_id,MAX(salary) max_sal FROM employees GROUP BY department_id ORDER BY max_sal LIMIT 0,1) dept_max_sal WHERE e.department_id = dept_max_sal.department_id
14. Query the manager details of the Department with the highest average salary: last_name, department_id, email, salary
Mode 1:
SELECT employee_id,last_name, department_id, email, salary FROM employees WHERE employee_id IN ( SELECT DISTINCT manager_id FROM employees WHERE department_id = ( SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) = ( SELECT MAX(avg_sal) FROM( SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id ) dept_sal ) ) );
Mode 2:
SELECT employee_id,last_name, department_id, email, salary FROM employees WHERE employee_id IN ( SELECT DISTINCT manager_id FROM employees WHERE department_id = ( SELECT department_id FROM employees e GROUP BY department_id HAVING AVG(salary)>=ALL( SELECT AVG(salary) FROM employees GROUP BY department_id ) ) );
#Mode 3:
SELECT * FROM employees WHERE employee_id IN ( SELECT DISTINCT manager_id FROM employees e,( SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id ORDER BY avg_sal DESC LIMIT 0,1) dept_avg_sal WHERE e.department_id = dept_avg_sal.department_id )
#Summary:
Classification: related sub query (question 3) vs Unrelated subquery Format: sub query is flexible. Can appear in where,from,select,order by ... Writing skills: ①Write from the outside in ②Write from the inside out
Section 6 creating and managing tables
1. Operations related to books table
Case:
1. Create database test01_library
2. Create table books
Field nameField descriptiondata typeNull allowedonlyb_idBook numberint(11)noyesb_nametitlevarchar(50)nonoauthorsauthorvarchar(100)nonopricePricefloatnonopubdatePublication dateyearnononoteexplainvarchar(100)yesnonumstockint(11)nono3. Insert a record into the books table
1) Specify all field names to insert the first record
2) Insert the second record without specifying the field name
3) Insert multiple records at the same time (all remaining records)
b_idb_nameauthorspricepubdatenotenum1Tal of AAADickes231995novel112EmmaTJane lura351993joke223Story of JaneJane Tim402001novel04Lovey DayGeorge Byron202005novel305Old landHonore Blade302010law06The BattleUpton Sara301999medicine407Rose HoodRichard haggard282008cartoon284. Increase the price of novel books by 5.
5. Change the price of the book named EmmaT to 40.
6. Delete record with inventory 0
#Create database test01_library CREATE DATABASE test01_library; #Specify which database to use USE test01_library; #Create table books CREATE TABLE books( b_id INT, b_name VARCHAR(50), `authors` VARCHAR(100), price FLOAT, pubdate YEAR, note VARCHAR(100), num INT ); #Specify all field names to insert the first record INSERT INTO books (b_id,b_name,`authors`,price,pubdate,note,num) VALUES(1,'Tal of AAA','Dickes',23,1995,'novel',11); #Insert the second record without specifying the field name INSERT INTO books VALUE(2,'EmmaT','Jane lura',35,1993,'Joke',22); #Insert multiple records at the same time (all remaining records). INSERT INTO books VALUES (3,'Story of Jane','Jane Tim',40,2001,'novel',0), (4,'Lovey Day','George Byron',20,2005,'novel',30), (5,'Old land','Honore Blade',30,2010,'Law',0), (6,'The Battle','Upton Sara',30,1999,'medicine',40), (7,'Rose Hood','Richard haggard',28,2008,'cartoon',28); #Increase the price of novel books by 5. UPDATE books SET price=price+5 WHERE note = 'novel'; #Change the price of the book named EmmaT to 40. UPDATE books SET price=40 WHERE b_name='EmmaT'; #Delete record with inventory 0 DELETE FROM books WHERE num=0;
2. Operations related to customers table
1. Create database test02_market
2. Create table customers
Field namedata typec_numint(11)c_namevarchar(50)c_contactvarchar(50)c_cityvarchar(50)c_birthdate**Requirement 3: * * C_ Move the contact field to C_ After the birth field
**Requirement 4: * * C_ Change the data type of name field to varchar(70)
**Requirement 5: * * C_ The contact field is renamed c_phone
**Requirement 6: * * add c_gender field to C_ After name, the data type is char(1)
**Requirement 7: * * change the table name to customers_info
**Requirement 8: * * delete field c_city
#1. Create a database Market and enter to operate CREATE DATABASE test02_market; #Specify which database to operate on USE test02_market; #2. Create the data table customers, CREATE TABLE customers( c_num INT , c_name VARCHAR(50), c_contact VARCHAR(50), c_city VARCHAR(50), c_birth DATE ); #3. Will c_ The contact field is inserted into C_ After the birth field ALTER TABLE customers MODIFY c_contact VARCHAR(50) AFTER c_birth; #4. Will c_ Change the data type of name field to varchar(70) ALTER TABLE customers MODIFY c_name VARCHAR(70); #5. Will c_ The contact field is renamed c_phone. ALTER TABLE customers CHANGE c_contact c_phone VARCHAR(50); #6. Add c_gender field, data type char(1) ALTER TABLE customers ADD c_gender CHAR(1) AFTER c_name; #The default is in the last column #Add first to the first column #If you want to specify which column to follow, add the name of the after column #7. Change the table name to customers_info ALTER TABLE customers RENAME customers_info; #8. Delete field c_city ALTER TABLE customers_info DROP c_city ;
3. Operations related to the offices table
1. Create database company
2. Create form offices
Field namedata typeofficeCodeintcityvarchar(30)addressvarchar(50)countryvarchar(50)postalCodevarchar(25)3. Create table employees
Field namedata typeempNumint(11)lastNamevarchar(50)firstNamevarchar(50)mobilevarchar(25)codeintjobTitlevarchar(50)birthdateNotevarchar(255)Sexvarchar(5)**Requirement 4: * * modify the mobile field of the employees table to the code field.
**Requirement 5: * * change the birth field of the employees table to birthday;
**Requirement 6: * * modify the sex field and the data type is char (1).
**Requirement 7: * * delete the field note;
**Requirement 8: * * add field name favorite_ Activity, the data type is varchar (100);
**Requirement 9: * * change the name of the table employees to employees_info
#Create database test03_company CREATE DATABASE test03_company; #Specify which database to use, that is, which database is targeted by the following sql statements USE test03_company; CREATE TABLE offices( officeCode INT, city VARCHAR(30), address VARCHAR(50), country VARCHAR(50) , postalcode VARCHAR(25) ); CREATE TABLE employees( empNum INT(11), lastName VARCHAR(50), firstName VARCHAR(50), mobile VARCHAR(25), `code` INT , jobtitle VARCHAR(50), birth DATE, note VARCHAR(255), sex VARCHAR(5) ); #4. Modify the mobile field of the table employees to the code field. ALTER TABLE employees MODIFY mobile VARCHAR(25)AFTER CODE; #5. Change the birth field of the employees table to birthday; ALTER TABLE employees CHANGE birth birthday DATE NOT NULL; #6. Modify the sex field, and the data type is char (1) ALTER TABLE employees MODIFY sex CHAR(1) ; #7. Delete the field note; ALTER TABLE employees DROP note; #8. Add field name favor_ Activity, the data type is varchar (100); ALTER TABLE employees ADD COLUMN favoriate_activity VARCHAR(100); #9. Change the name of the table employees to employees_info ALTER TABLE employees RENAME employees_info;
Section 7 addition, deletion and modification of data processing
1. Operations related to employee table
1. Create database test01db
2. Create the form employee and add records
idnamesexteladdrsalary10001Zhang Yiyimale13456789000Shaoguan, Guangdong1001.5810002Liu Xiaohongfemale13454319000Jiangmen, Guangdong1201.2110003Li Simale0751-1234567Foshan, Guangdong1004.1110004Liu Xiaoqiangmale0755-5555555Shenzhen, Guangdong1501.2310005Wang Yanfemale020-1232133Guangzhou, Guangdong1405.16**Requirement 3: * * query the employee information whose salary is between 1200 and 1300.
**Requirement 4: * * find out the job number, name and home address of the employee surnamed "Liu".
**Requirement 5: * * change the home address of "Li Si" to "Shaoguan, Guangdong"
**Requirement 6: * * query the employees with "small" in their names
-- Create database test01db CREATE DATABASE test01db; -- use test01db database USE test01db; #Create employee table CREATE TABLE employee( id INT, `name` VARCHAR(20), sex VARCHAR(20), tel VARCHAR(20), addr VARCHAR(50), salary FLOAT ); #Add information INSERT INTO employee(id,`name`,sex,tel,addr,salary)VALUES (10001,'Zhang Yiyi','male','13456789000','Shaoguan, Guangdong',1001.58), (10002,'Liu Xiaohong','female','13454319000','Jiangmen, Guangdong',1201.21), (10003,'Li Si','male','0751-1234567','Foshan, Guangdong',1004.11), (10004,'Liu Xiaoqiang','male','0755-5555555','Shenzhen, Guangdong',1501.23), (10005,'Wang Yan','male','020-1232133','Guangzhou, Guangdong',1405.16); #Requirement 3: query the salary information between 1200 and 1300. SELECT * FROM employee WHERE salary BETWEEN 1200 AND 1300; #Requirement 4: find out the job number, name and home address of the employee surnamed "Liu". SELECT id,addr FROM employee WHERE `name` LIKE 'Liu%'; #Requirement 5: change the home address of "Li Si" to "Shaoguan, Guangdong" UPDATE employee SET addr='Shaoguan, Guangdong' WHERE `name`='Li Si'; #Requirement 6: query the employees with "small" in their names. SELECT * FROM employee WHERE `name` LIKE '%Small%';
2. Related operations of pet table
1. Create database test02db
2. Create table pet
Field nameField descriptiondata typenamePet namevarchar(20)ownerPet ownervarchar(20)speciestypevarchar(20)sexGenderchar(1)birthdate of birthyeardeathDate of deathyear3. Add record
nameownerspeciessexbirthdeathFluffyharoldCatf20032010ClawsgwenCatm2004BuffyDogf2009FangbennyDogm2000bowserdianeDogm20032009ChirpyBirdf20084. Add field owner's birthday owner_birth.
5. Change the owner of the cat named jaws to kevin
6. Change the owner of the undead dog to duck
7. Check the names of pets without owners;
8. Query the name, owner and time of death of the dead cat;
9. Delete dead dogs
10. Query all pet information
-- Create database test02db CREATE DATABASE test02db; -- Specify which database to use USE test02db; -- stay market Create data table in customers， CREATE TABLE pet( `name` VARCHAR(20), `owner` VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth YEAR, death YEAR ); -- 3,Add data INSERT INTO pet VALUES('Fluffy','harold','Cat','f','2013','2010'); INSERT INTO pet(`name`,`owner`,species,sex,Birth) VALUES('Claws','gwen','Cat','m','2014'); INSERT INTO pet(`name`,species,sex,Birth) VALUES('Buffy','Dog','f','2009'); INSERT INTO pet(`name`,`owner`,species,sex,Birth) VALUES('Fang','benny','Dog','m','2000'); INSERT INTO pet VALUES('bowser','diane','Dog','m','2003','2009'); INSERT INTO pet(`name`,species,sex,birth) VALUES('Chirpy','Bird','f','2008'); #4. Add field owner's birthday owner_birth. ALTER TABLE pet ADD COLUMN owner_birth DATE; #5. Change the owner of the cat named jaws to kevin UPDATE pet SET `owner`='kevin' WHERE `name`='Claws' AND species='Cat'; #6. Change the owner of the undead dog to duck UPDATE pet SET `owner`='duck' WHERE species='Dog' AND death IS NULL; #7. Check the names of pets without owners; SELECT `name` FROM pet WHERE `owner` IS NULL; #8. Query the name, owner and time of death of the dead cat; SELECT `name`,`owner`,death FROM pet WHERE death IS NOT NULL; #9. Delete dead dogs DELETE FROM pet WHERE death IS NOT NULL; #10. Query all pet information SELECT * FROM pet;
Section 8 constraints
1. Operations related to books table
Case:
1. Create database test_library
2. Create table books
Field nameField descriptiondata typeb_idBook numberint(11)b_nametitlevarchar(50)authorsauthorvarchar(100)pricePricefloatpubdatePublication dateyearnoteexplainvarchar(100)numstockint(11)3. Use the alter statement to add corresponding constraints to books as follows
Field nameField descriptiondata typePrimary keyForeign keyNon emptyonlySelf increasingb_idBook numberint(11)yesnoyesyesyesb_nametitlevarchar(50)nonoyesnonoauthorsauthorvarchar(100)nonoyesnonopricePricefloatnonoyesnonopubdatePublication dateyearnonoyesnononoteexplainvarchar(100)nonononononumstockint(11)nonoyesnono4. Insert a record into the books table
1) Specify all field names to insert the first record
2) Insert the second record without specifying the field name
3) Insert multiple records at the same time (all remaining records)
b_idb_nameauthorspricepubdatenotenum1Tal of AAADickes231995novel112EmmaTJane lura351993joke223Story of JaneJane Tim402001novel04Lovey DayGeorge Byron202005novel305Old landHonore Blade302010law06The BattleUpton Sara301999medicine407Rose HoodRichard haggard282008cartoon285. Books with a letter in the title of statistical books
6. Count the number of books with a letter in the title and the total inventory
7. Find the "novel" books and arrange them in descending order of price
8. Query the book information and arrange it in descending order according to the inventory. If the inventory is the same, arrange it in ascending order according to the note
9. Count the number of books by note
10. According to the inventory of the note classification statistics book, it displays the inventory of more than 30 books
11. Query all books, display 5 books per page, and display the second page
12. According to the inventory of the note classification statistics book, the company with the largest inventory now
13. Query the book with a title of 10 characters, excluding the spaces inside
14. Query the title and type of the book, where
note value is novel, law shows law, medicine shows medicine, cartoon shows cartoon and joke shows joke
15. Query book title and inventory, where
If the num value exceeds 30, it shows unsalable; if it is greater than 0 and less than 10, it shows best-selling; if it is 0, it needs to be out of stock
16. Count the inventory of each note and total the total
17. Count the number of each note and sum up the total
18. Statistics of the top three books in stock
19. Find the first book published
20. Find the highest book in novel
21. Find the book with the largest number of words in the title, without spaces
#Create database test_library CREATE DATABASE test_library; #Use test_library USE test_library; #Create table books CREATE TABLE books( b_id INT, b_name VARCHAR(50), `authors` VARCHAR(100), price FLOAT, pubdate YEAR, note VARCHAR(100), num INT ); #Give b_id add primary key constraint ALTER TABLE books ADD PRIMARY KEY(b_id); #Give B_ Add auto increment constraint to ID field ALTER TABLE books MODIFY b_id INT AUTO_INCREMENT; #Give B_ Add non empty constraints to fields such as name ALTER TABLE books b_name VARCHAR(50) NOT NULL; ALTER TABLE books `authors` VARCHAR(100) NOT NULL; ALTER TABLE books price FLOAT NOT NULL; ALTER TABLE books pubdate DATE NOT NULL; ALTER TABLE books num INT NOT NULL; #Specify all field names to insert the first record INSERT INTO books (b_id,b_name,`authors`,price,pubdate,note,num) VALUES(1,'Tal of AAA','Dickes',23,1995,'novel',11); #Insert the second record without specifying the field name INSERT INTO books VALUE(2,'EmmaT','Jane lura',35,1993,'Joke',22); #Insert multiple records at the same time (all remaining records). INSERT INTO books VALUES (3,'Story of Jane','Jane Tim',40,2001,'novel',0), (4,'Lovey Day','George Byron',20,2005,'novel',30), (5,'Old land','Honore Blade',30,2010,'Law',0), (6,'The Battle','Upton Sara',30,1999,'medicine',40), (7,'Rose Hood','Richard haggard',28,2008,'cartoon',28); #Look up books whose titles contain the letter a SELECT * FROM books WHERE b_name LIKE '%a%'; #Count the number of books with a letter in the title and the total inventory SELECT COUNT(*),SUM(num) FROM books WHERE b_name LIKE '%a%'; #Find the "novel" books and arrange them in descending order of price SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC; #Query the book information and arrange it in descending order according to the inventory. If the inventory is the same, arrange it in ascending order according to the note SELECT * FROM books ORDER BY num DESC,note ASC; #Count the number of books by note SELECT note,COUNT(*) FROM books GROUP BY note; #According to the inventory of the note classification statistics book, it displays the inventory of more than 30 books SELECT note,SUM(num) FROM books GROUP BY note HAVING SUM(num)>30; #Query all books, display 5 books per page, and display the second page SELECT * FROM books LIMIT 5,5; #According to the inventory of the note classification statistics book, the company with the largest inventory now SELECT note,SUM(num) FROM books GROUP BY note ORDER BY SUM(num) DESC LIMIT 0,1; #Query the book with a title of 10 characters, excluding the spaces inside SELECT * FROM books WHERE CHAR_LENGTH(REPLACE(b_name,' ',''))>=10; /* Query the title and type of the book, where note A value of novel shows a novel, law shows a law, medicine shows medicine, cartoon shows a cartoon, and joke shows a joke */ SELECT b_name AS "title" ,note, CASE note WHEN 'novel' THEN 'novel' WHEN 'law' THEN 'law' WHEN 'medicine' THEN 'medicine' WHEN 'cartoon' THEN 'Cartoon' WHEN 'joke' THEN 'joke' END AS "type" FROM books; /* Query book title and inventory, where num If the value exceeds 30, it shows unsalable; if it is greater than 0 and less than 10, it shows best-selling; if it is 0, it needs to be out of stock, and other displays are normal */ SELECT b_name,num,CASE WHEN num>30 THEN 'Unsalable' WHEN num>0 AND num<10 THEN 'Best seller' WHEN num=0 THEN 'No goods' ELSE 'normal' END AS "Inventory status" FROM books; #Count the inventory of each note and total the total SELECT IFNULL(note,'Total inventory') AS note,SUM(num) FROM books GROUP BY note WITH ROLLUP; #Count the number of each note and sum up the total SELECT IFNULL(note,'Total') AS note,COUNT(*) FROM books GROUP BY note WITH ROLLUP; #Statistics of the top three books in stock SELECT * FROM books ORDER BY num DESC LIMIT 0,3; #Find the first book published SELECT * FROM books ORDER BY pubdate ASC LIMIT 0,1; #Find the highest book in novel SELECT * FROM books WHERE note = 'novel' ORDER BY price DESC LIMIT 0,1; #Find the book with the largest number of words in the title SELECT * FROM books ORDER BY CHAR_LENGTH(REPLACE(b_name,' ','')) DESC LIMIT 0,1;
2. Related operations of department and other tables
1. Create database: test_company
2. Create the following 3 tables under this database. The data type, width and whether it is empty are defined according to the actual situation.
A. Department table: department number (depid), department name (depname), Department profile (deinfo); The department number is the primary key.
B. Employee form: employee number (empid), name (name), sex (sex), title (title), birth date (birthday), department number (depid); among
- Employee number is the primary key;
- The department number is a foreign key, and the foreign key constraint level is (on update cascade and on delete set null);
- Gender is male by default;
C. Salary table: employee number (empid), base salary, title salary, deduction. The employee number is the primary key.
3. Add a foreign key constraint to the employee number (empid) of the salary table. The foreign key constraint levels are (on update cascade and on delete cascade)
4. Add data as follows:
Department table:
Department numberDepartment nameDepartment profile111Production departmentNull222Sales DepartmentNull333Ministry of PersonnelHuman resource managementEmployee table:
Employee numberfull nameGendertitledate of birthDepartment No1001Zhang Sanmalesenior engineer1975-1-11111002Li SifemaleAssistant Engineer1985-1-11111003Wang Wumaleengineer1978-11-112221004Zhang Liumaleengineer1999-1-1222payroll:
Employee numberbase payJob salarydeduction10012200110020010021200200NULL10032900700200100419507001505. Query the employee number, name, professional title, department name, salary payable (basic salary + job salary) and paid salary (basic salary + job salary - deduction) of each employee.
6. Query the name and basic salary of employees in the sales department
7. Query all information and age of employees with surname "Zhang" and age less than 40
8. Query the basic salary and job salary of all male employees
9. Query the name, title and department name of employees whose basic salary is less than 2000
10. Query total number of employees
11. Total number of query departments
12. Query the average wage, maximum wage and minimum wage of wages payable
13. Calculate the average wage of wages payable according to the Department
14. Find out if the average salary of the Department's basic salary is lower than 2000
15. According to the employee number, name, basic salary, job salary and deduction, and in ascending order of job. If the job salary is the same, then in ascending order of basic salary
16. Query employee number, name, date of birth, and age group, where
If born 80 years ago, it is defined as "old age"; the post-80s are defined as "middle age" and the post-90s are defined as "young adults"“
17. Query all employee information and the name of his department
18. Query all department information and employee information of the Department
19. Query the number of male employees with "engineer" in all positions
20. Query the number and average basic salary of boys and girls in each department
#Create database: test_company CREATE DATABASE test_company; #Using the database test_company USE test_company; #Create department table CREATE TABLE department( depid INT PRIMARY KEY, depname VARCHAR(20) NOT NULL, deinfo VARCHAR(50) ); #Create employee table (emoloyee) CREATE TABLE employee( empid INT PRIMARY KEY, `name` VARCHAR(20) NOT NULL, sex CHAR NOT NULL DEFAULT 'male', title VARCHAR(20) NOT NULL, birthday DATE, depid INT, FOREIGN KEY(depid) REFERENCES department(depid) ON UPDATE CASCADE ON DELETE SET NULL ); #Create payroll CREATE TABLE salary( empid INT PRIMARY KEY, basesalary DOUBLE, titlesalary DOUBLE, deduction DOUBLE ); #Add a foreign key constraint to the employee number (empid) of the salary table. The foreign key constraint levels are (on update cascade and on delete cascade) ALTER TABLE salary ADD FOREIGN KEY empid REFERENCES employee(empid) ON UPDATE CASCADE ON DELETE CASCADE; #Add department table data INSERT INTO department VALUES (111,'Production department',NULL), (222,'Sales Department',NULL), (333,'Ministry of Personnel','Human resource management'); #Add employee table INSERT INTO employee VALUES (1001,'Zhang San',DEFAULT,'senior engineer','1975-1-1',111), (1002,'Li Si','female','Assistant Engineer','1985-1-1',111), (1003,'Wang Wu','male','engineer','1978-11-11',222), (1004,'Zhang Liu',DEFAULT,'engineer','1999-1-1',222); #Add payroll INSERT INTO salary VALUES (1001,2200,1100,200), (1002,1200,200,NULL), (1003,2900,700,200), (1004,1950,700,150); /* Find out the employee number, name, professional title and department name of each employee, Salary payable (basic salary + job salary), Paid salary (basic salary + job salary - deduction). */ SELECT employee.empid,`name`,title,depname, basesalary+titlesalary AS "Wages payable", basesalary+titlesalary-IFNULL(deduction,0) AS "net salary" FROM department INNER JOIN employee INNER JOIN salary ON department.depid = employee.depid AND employee.empid = salary.empid; #Query the name and basic salary of employees in the sales department SELECT `name`,basesalary FROM department INNER JOIN employee INNER JOIN salary ON department.depid = employee.depid AND employee.empid = salary.empid WHERE department.depname = 'Sales Department'; #Query all information and age of employees with surname "Zhang" and age less than 40 SELECT *,YEAR(CURRENT_DATE())-YEAR(birthday) AS "Age" FROM employee WHERE `name` LIKE 'Zhang%' AND YEAR(CURRENT_DATE())-YEAR(birthday)<40; #Query the basic salary and job salary of all male employees SELECT basesalary,titlesalary FROM employee INNER JOIN salary ON employee.empid = salary.empid WHERE employee.sex = 'male'; #Query the name, title and department name of employees whose basic salary is less than 2000 SELECT `name`,title,depname FROM department INNER JOIN employee INNER JOIN salary ON department.depid = employee.depid AND employee.empid = salary.empid WHERE basesalary < 2000; #Query total number of employees SELECT COUNT(*) FROM employee; #Total number of query departments SELECT COUNT(*) FROM department; #Query the average wage, maximum wage and minimum wage payable SELECT AVG(basesalary+titlesalary) AS "Average salary payable", MAX(basesalary+titlesalary) AS "Maximum salary payable", MIN(basesalary+titlesalary) AS "Minimum wage payable" FROM salary; #Calculate the average wage of wages payable according to the Department SELECT depid,AVG(basesalary+titlesalary) FROM employee INNER JOIN salary ON employee.`empid` = salary.`empid` GROUP BY employee.`depid`; #Find out if the average salary of the Department's basic salary is lower than 2000 SELECT depid,AVG(basesalary) FROM employee INNER JOIN salary ON employee.`empid` = salary.`empid` GROUP BY employee.`depid` HAVING AVG(basesalary)<2000; #According to employee number, name, basic salary, job salary and deduction, #They are arranged in ascending order by job. If the job salary is the same, they are arranged in ascending order by basic salary SELECT emp.empid,`name`,basesalary,titlesalary,deduction FROM employee emp INNER JOIN salary ON emp.`empid` = salary.`empid` ORDER BY emp.`title` ASC , basesalary ASC; #Query employee number, name, date of birth, and age group, where #If born 80 years ago, it is defined as "old age"; the post-80s are defined as "middle age" and the post-90s are defined as "young adults"“ SELECT empid,`name`,birthday, CASE WHEN YEAR(birthday)<1980 THEN 'old age' WHEN YEAR(birthday)<1990 THEN 'middle age' ELSE 'Young adults' END "age group" FROM employee; #Query all employee information and the name of his department SELECT emp.*,depname FROM employee emp LEFT JOIN department dep ON emp.`depid` = dep.`depid`; #Query all department information and employee information of the Department SELECT dep.*,emp.* FROM employee emp RIGHT JOIN department dep ON emp.`depid` = dep.`depid`; #Query the number of male employees with "engineer" in all positions SELECT COUNT(*) FROM employee WHERE sex='male' AND title LIKE '%engineer%'; #Query the number and average basic salary of boys and girls in each department SELECT dep.depid,sex,COUNT(*),AVG(basesalary) FROM department dep INNER JOIN employee INNER JOIN salary ON dep.depid = employee.depid AND employee.empid = salary.empid GROUP BY dep.depid,sex;
3. Related operations of department table
1. Create a database: test_school
2. Create the following table
Table 1 definition of department table
Field nameField descriptiondata typePrimary keyForeign keyNon emptyonlyDepNoDepartment numberint(10)yesnoyesyesDepNameDepartment namevarchar(20)nonoyesnoDepNoteDepartment remarksVarchar(50)nonononoTable 2 definition of teacher table
Field nameField descriptiondata typePrimary keyForeign keyNon emptyonlyNumberFaculty numberintyesnoyesyesNamefull namevarchar(30)nonoyesnoSexGendervarchar(4)nonononoBirthdate of birthdatenonononoDepNoDepartment numberintnoyesnonoSalarywagesfloatnonononoAddressHome addressvarchar(100)nononono3. Add record
DepNoDepNameDepNote601Department of software technologySoftware technology and other majors602Department of network technologyMultimedia technology and other majors603Department of art and designAdvertising art design and other majors604Department of Management EngineeringMajor in chain operation and management NumberNameSexBirthDepNoSalaryAddress2001Tomfemale1970-01-106024500Mianyang City, Sichuan Province2002Lucymale1983-12-186012500Changping District, Beijing2003Mikemale1990-06-016041500Yuzhong District, Chongqing2004Jamesfemale1980-10-206023500Chengdu City, Sichuan Province2005Jackmale1975-05-306031200Nan'an District, Chongqing4. Query all records of the Teacher table with the SELECT statement.
5. Find out the teaching staff number and department name of all teachers whose home address contains "Beijing", and require that the column titles in the display results be represented by Chinese aliases.
6. Get the highest paid faculty number and name in the Teacher table.
7. Find all faculty numbers with income between 2500 and 4000.
8. Find the name, gender and salary of teachers working in the Department of network technology.
#Create a database: test_school CREATE DATABASE test_school; #Use database USE test_school; #Create table -- Department information sheet Department CREATE TABLE Department( DepNo INT(10) PRIMARY KEY, DepName VARCHAR(20) NOT NULL, DepNote VARCHAR(50) ); -- Create data table Teacher CREATE TABLE Teacher( Number INT PRIMARY KEY, `Name` VARCHAR(30) UNIQUE, Sex VARCHAR(4), Birth DATE, DepNo INT, Salary FLOAT, Address VARCHAR(100), FOREIGN KEY (DepNo) REFERENCES Department(DepNo) ); -- Insert the contents of Table 4 Department In the table INSERT INTO Department VALUES (601,'Department of software technology','Software technology and other majors'); INSERT INTO Department VALUES (602,'Department of network technology','Multimedia technology and other majors'); INSERT INTO Department VALUES (603,'Department of art and design','Advertising art design and other majors'); INSERT INTO Department VALUES (604,'Department of Management Engineering','Major in chain operation and management'); -- Insert the contents of Table 3 Teacher In the table. INSERT INTO Teacher VALUES(2001,'Tom','female','1970-01-10',602,4500,'Mianyang City, Sichuan Province'); INSERT INTO Teacher VALUES(2002,'Lucy','male','1983-12-18',601,2500,'Changping District, Beijing'); INSERT INTO Teacher VALUES(2003,'Mike','male','1990-06-01',604,1500,'Yuzhong District, Chongqing'); INSERT INTO Teacher VALUES(2004,'James','female','1980-10-20',602,3500,'Chengdu City, Sichuan Province'); INSERT INTO Teacher VALUES(2005,'Jack','male','1975-05-30',603,1200,'Nan'an District, Chongqing'); #Query all records of the Teacher table with the SELECT statement. SELECT * FROM teacher; #Find out the teaching staff number and department name of all teachers with "Beijing" in their home address, and require the titles of each column in the display results to be expressed in Chinese. SELECT number AS Faculty number,Teacher.depno AS Department name FROM Teacher INNER JOIN Department ON Teacher.DepNo = Department.DepNo WHERE address LIKE '%Beijing%'; #Get the highest paid faculty number and name in the Teacher table. SELECT number,`name` FROM teacher WHERE salary = (SELECT MAX(salary) FROM teacher); SELECT number,`name` FROM teacher ORDER BY salary DESC LIMIT 0,1; #Find all faculty numbers with income between 2500 and 4000. SELECT number FROM teacher WHERE salary BETWEEN 2500 AND 4000; #Find the name, gender and salary of teachers working in the Department of network technology. SELECT `name`,sex,salary FROM teacher WHERE depno=(SELECT depno FROM department WHERE depname='Department of network technology'); SELECT `name`,sex,salary FROM teacher INNER JOIN department ON teacher.depno = department.depno WHERE depname ='Department of network technology';
4. student table related operations
Case:
1. Establish database test_student
2. Create the following three tables and insert records
Table:Classes
majorclassfull nameSex seatcomputer networkClass 1Zhang Sanmale8software engineeringClass 2Li Simale12Computer maintenanceClass 1Wang Wumale9computer networkClass 2LILYfemale15software engineeringClass 1cockroachmale20Computer maintenanceClass 1CoCofemale18Table:Score
full nameEnglishmathematicschineseZhang San657598Li Si874586Wang Wu988565LILY758687cockroach856058CoCo968770Table: Records
full namerecordcockroachlatecockroachcompassionate leaveLi SiAbsenteeismLi SiAbsenteeismLi SilateCoCosick leaveLILYcompassionate leaveRequirement 3: write the SQL statement that modifies Zhang San's Chinese score to 88.
Requirement 4: search the average score of each course in class 1 of computer maintenance.
Requirement 5: search the list of people who have failed in the subject.
Requirement 6: query and record the names and grades of students who have recorded more than 2 times.
#Establish database test_student CREATE DATABASE test_student; #Use database USE test_student; #Create tables and add records CREATE TABLE Classes( Pro_name VARCHAR(20) NOT NULL, Grade VARCHAR(10) NOT NULL, `name` VARCHAR(10) NOT NULL, sex VARCHAR(4) NOT NULL, seat INT(10) NOT NULL UNIQUE ); CREATE TABLE Score( `name` VARCHAR(10) NOT NULL, En_score INT(10) NOT NULL, Ma_score INT(10) NOT NULL, Ch_score INT(10) NOT NULL ); CREATE TABLE Records( `name` VARCHAR(10) NOT NULL, record VARCHAR(10) ); -- towards classes Add data to INSERT INTO classes VALUES('computer network','1 class','Zhang San','male',8); INSERT INTO classes VALUES('software engineering','2 class','Li Si','male',12); INSERT INTO classes VALUES('Computer maintenance','1 class','Wang Wu','male',9); INSERT INTO classes VALUES('computer network','2 class','LILY','female',15); INSERT INTO classes VALUES('software engineering','1 class','cockroach','male',20); INSERT INTO classes VALUES('Computer maintenance','1 class','CoCo','female',18); -- towards score Add data to INSERT INTO Score VALUES('Zhang San',65,75,98); INSERT INTO Score VALUES('Li Si',87,45,86); INSERT INTO Score VALUES('Wang Wu',98,85,65); INSERT INTO Score VALUES('LILY',75,86,87); INSERT INTO Score VALUES('cockroach',85,60,58); INSERT INTO Score VALUES('CoCo',96,87,70); -- towards records Add data to INSERT INTO records VALUES('cockroach','late'); INSERT INTO records VALUES('cockroach','compassionate leave'); INSERT INTO records VALUES('Li Si','Absenteeism'); INSERT INTO records VALUES('Li Si','Absenteeism'); INSERT INTO records VALUES('Li Si','late'); INSERT INTO records VALUES('CoCo','sick leave'); INSERT INTO records VALUES('LILY','compassionate leave'); #Requirement 3: write the SQL statement that modifies Zhang San's Chinese score to 88. UPDATE score SET ch_score=88 WHERE `name`='Zhang San'; #Requirement 4: search the average score of each course in class 1 of computer maintenance. SELECT AVG(en_score),AVG(ma_score),AVG(ch_score) FROM score WHERE `name` IN (SELECT `name` FROM classes WHERE Pro_name='Computer maintenance' AND grade='1 class'); #Requirement 5: search the list of people who have failed in the subject. SELECT `name` FROM score WHERE en_score<60 OR ma_score<60 OR ch_score<60; #Requirement 6: query and record the names and grades of students who have recorded more than 2 times. SELECT * FROM score INNER JOIN (SELECT `name`,COUNT(*) FROM Records GROUP BY `name` HAVING COUNT(*)>2) temp ON score.name = temp.name;
5. Related operations of student and other tables
1. Establish database: test_xuankedb
2. Create the following three tables:
Student form consists of five fields: student number (Sno), name (Sname), gender (Ssex), age (Sage) and department (Sdept). Sno is the keyword.
Curriculum consists of four fields: Course number (Cno), Course name (Cname), elective Course number (Cpno) and credit (Ccredit). Cno is the keyword.
The grade sheet SG consists of three fields: student number (Sno), course number (Cno) and grade (SNO, CNO) as keywords.
3. Add the "enrollment time (Scome)" column to the Student table, and its data type is date type.
4. Query the student number and grades of students who have taken No. 3 course, and the query results are arranged in descending order of scores.
5. Query the highest score and average score of students studying course 1.
6. Inquire about students studying in the same department as "Li Yang".
7. Set the grades of all students in the computer department to zero.
8. Delete the student record No. 05019 in the student form.
9. Delete the grade records of all students in the computer department.
-- Create a database: test_xuankedb CREATE DATABASE test_xuankedb; -- Use database USE test_xuankedb; -- Create student table CREATE TABLE student( sno INT(10) PRIMARY KEY, sname VARCHAR(10), ssex VARCHAR(10), sage INT(10), sdept VARCHAR(40) ); -- Create Curriculum CREATE TABLE course( cno INT(10) PRIMARY KEY, cname VARCHAR(20), cpno VARCHAR(40), ccredit INT(20) ); -- Create grade sheet CREATE TABLE sg( sno INT(10), cno INT(10), grade INT(3), PRIMARY KEY(sno,cno), CONSTRAINT stu_s_sno_fk FOREIGN KEY (sno) REFERENCES student(sno), CONSTRAINT cou_s_sno_fk FOREIGN KEY (cno) REFERENCES course(cno) ); #3. Add the column "enrollment time (Scome)" to the Student table, and its data type is date type. ALTER TABLE student ADD COLUMN scome DATE; #4. Query the student number and grades of students who have taken No. 3 course, and the query results are arranged in descending order of scores. SELECT sno,grade FROM sg WHERE cno=3 ORDER BY grade DESC; #5. Query the highest score and average score of students studying course 1. SELECT MAX(grade),AVG(grade) FROM sg WHERE cno=1; #6. Inquire about students studying in the same department as "Li Yang". SELECT * FROM student WHERE sdept=(SELECT sdept FROM student WHERE sname='Li Yang'); #7. Set the scores of all students in the computer department to zero. UPDATE sg SET grade=0 WHERE sno IN (SELECT sno FROM student WHERE sdept='Computer Department') #8. Delete the student record No. 05019 in the student form. DELETE FROM student WHERE sno=05019; #9. Delete the grade records of all students in the computer department. DELETE FROM sg WHERE sno IN (SELECT sno FROM student WHERE sdept='Computer Department');
6. Related operations of press and other tables
1. Establish database: test_library
2. Create the following three tables:
Table 1: press press
Attributes: number Presid (int), name presname (varchar), address(varchar)
Table 2: sort types
Attribute: number sortno(int), quantity Scout (int)
Table 2: book
Attributes: No. bid(int), name bname(varchar), type bsortno(int), press No. pressid(int)
3. Add a column of attributes to the sort table: descriptions (varchar)
4. Insert several pieces of data into each of the three tables
[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-2WuNn3x2-1631281610414)(MySQL database chapter exercise. assets/1566490682634.png)]
[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-hr5JvOYq-1631281610417)(MySQL database chapter exercise. assets/1566490720312.png)]
[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-352TBoHI-1631281610418)(MySQL database chapter exercise. assets/1566490751669.png)]
5. Query all information of books with Publisher id 100
6. Query all the information about the books published by FLTRP
7. Query the category with the number of books (scount) greater than 100
8. Query the publisher information with the largest variety of books
-- Establish database: test_library CREATE DATABASE test_library; -- Use database USE test_library; -- Create publisher table CREATE TABLE press( pressid INT(10) PRIMARY KEY, pressname VARCHAR(30), address VARCHAR(50) ); -- Create a category table CREATE TABLE sort( sortno INT(10) PRIMARY KEY, scount INT(10) ); -- Create book table CREATE TABLE book( bid INT(10) PRIMARY KEY, bname VARCHAR(40), bsortno INT(10), pressid INT(10), CONSTRAINT p_b_pid_fk FOREIGN KEY (pressid) REFERENCES press(pressid), CONSTRAINT s_b_sno_fk FOREIGN KEY (bsortno) REFERENCES sort(sortno) ); -- Add a list of attributes ALTER TABLE sort ADD COLUMN describes VARCHAR(30); -- Add data INSERT INTO press VALUES(100,'FLTRP','Shanghai'); INSERT INTO press VALUES(101,'Peking University Press','Beijing'); INSERT INTO press VALUES(102,'Education Press','Beijing'); -- Add data INSERT INTO sort(sortno,scount,describes)VALUES(11,50,'novel'); INSERT INTO sort(sortno,scount,describes)VALUES(12,300,'science fiction'); INSERT INTO sort(sortno,scount,describes)VALUES(13,100,'mythology'); -- Add data INSERT INTO book VALUES(1,'Red and black',11,100); INSERT INTO book VALUES(2,'Ice Fantasy',12,102); INSERT INTO book VALUES(3,'MYTHOS',13,102); INSERT INTO book VALUES(4,'The one thousand one night',13,102); #5. Query all information of books with Publisher id 100 SELECT * FROM book WHERE pressid=100; #6. Inquire all the information about the books published by FLTRP SELECT * FROM book WHERE pressid=(SELECT pressid FROM press WHERE pressname='FLTRP'); #7. Query the category with the number of books (scount) greater than 100 SELECT * FROM sort WHERE scount>100; #8. Query the information of the publishing house with the largest variety of books SELECT * FROM press WHERE pressid=( SELECT temp.pressid FROM (SELECT pressid,MAX(t.c) FROM (SELECT pressid,COUNT(*) AS c FROM book GROUP BY pressid ORDER BY c DESC ) AS t) AS temp); SELECT * FROM press WHERE pressid=( SELECT pressid FROM (SELECT pressid,bsortno FROM book GROUP BY pressid,bsortno) temp GROUP BY pressid ORDER BY COUNT(*) DESC LIMIT 0,1)
7. Related operations of agency and other tables
1. Establish database: test_tour
2. Create the following two tables:
Agency travel agency form:
Column name (English name)Column name (Chinese name)data typeNull value allowedexplainIdTravel agency NointnoPrimary keyNameTravel agency namevarcharnoAddressTravel agency addressvarcharnoAreaidRegion IdIntyestravel route table:
Column name (English name)Column name (Chinese name)data typeNull value allowedexplainTidTravel route numberintnoPrimary keyTimeTime requiredvarcharnoPositiondestinationvarcharnoMoneyspendFloatyesAidTravel agency idIntnoForeign keyCountNumber of applicantsIntyes3. Add record
[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-umK3Yasq-1631281610420)(MySQL database chapter exercise. assets/1566490822909.png)]
[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-xE2BlWfk-1631281610421)(MySQL database chapter exercise. assets/1566490863309.png)]
4. Find out the Hotels with the most travel routes
5. Find out the most popular travel routes (that is, find out the routes with the largest number of applicants)
6. Check travel routes that cost less than 5000
7. Find the most expensive travel agency name for a trip
8. Find out how long it takes to play all the travel routes of the youth hostel.
#Establish database: test_tour CREATE DATABASE test_tour; #Use database USE test_tour; CREATE TABLE agency( id INT PRIMARY KEY NOT NULL, NAME VARCHAR(20) NOT NULL, address VARCHAR(100) NOT NULL, areaid INT ); CREATE TABLE trval( tid INT PRIMARY KEY NOT NULL, TIME VARCHAR(50) NOT NULL, POSITION VARCHAR(100) NOT NULL, money FLOAT, aid INT NOT NULL, rcount INT, CONSTRAINT bk_aid FOREIGN KEY trval(aid) REFERENCES agency(id) ); INSERT INTO agency(id,NAME,address) VALUES (101,'Youth Travel Agency','Haidian, Beijing'); INSERT INTO agency(id,NAME,address) VALUES (102,'Tiantian travel agency','Tianjin Haiyuan'); INSERT INTO trval(tid,TIME,POSITION,money,aid,rcount) VALUES (1,'5 day','badaling section of the great wall',3000,101,10); INSERT INTO trval(tid,TIME,POSITION,money,aid,rcount) VALUES (2,'7 day','Water Great Wall',5000,101,14); INSERT INTO trval(tid,TIME,POSITION,money,aid,rcount) VALUES (3,'8 day','Water Great Wall',6000,102,11); SELECT * FROM agency; SELECT * FROM trval; #4. Find out the Hotels with the most travel routes SELECT * FROM agency INNER JOIN (SELECT t.aid,MAX(t.c) FROM (SELECT aid,COUNT(*) AS c FROM trval GROUP BY aid) AS t)temp ON agency.id = temp.aid #5. Find out the most popular travel routes (that is, find out the routes with the largest number of applicants) SELECT * FROM trval WHERE rcount=(SELECT MAX(rcount) FROM trval); #6. Check travel routes that cost less than 5000 SELECT * FROM trval WHERE money<5000; #7. Find the most expensive travel agency name for a trip SELECT NAME FROM agency WHERE id = (SELECT aid FROM trval WHERE money =(SELECT MAX(money) FROM trval )); #8. Find out how long it takes to play all the travel routes of the youth hostel. SELECT SUM(TIME) FROM trval WHERE aid=(SELECT id FROM agency WHERE NAME='Youth Travel Agency');