[Sql练习题]

by 清嘉

章节四

1.
SELECT last_name
FROM employees
WHERE salary >= 5000 && salary <=12000;
2.
SELECT last_name, department_id
FROM employees
WHERE department_id = 20 || department_id = 50;
-- WHERE department_id IN (20,50);也可
3.
SELECT last_name, job_id
FROM employees
WHERE manager_id IS NULL;
4.
SELECT last_name, salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
5.
SELECT first_name
FROM employees
WHERE first_name LIKE '__a%'
6.SELECT first_name
FROM employees
WHERE first_name LIKE '%a%k%' OR  first_name LIKE '%k%a%'
7.
SELECT last_name
FROM employees
WHERE last_name REGEXP 'e$'
8.
SELECT first_name,job_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;
9.
SELECT first_name,salary,manager_id
FROM employees
WHERE manager_id IN (100,101,110)

章节五

1.
SELECT last_name,department_id,salary*12 annual_salary
FROM employees
ORDER BY annual_salary DESC, last_name ASC;
--像下面这样则不行：
-- SELECT last_name,department_id,salary*12 "annual salary"
-- FROM employees
-- ORDER BY "annual salary" DESC, last_name ASC;
2.
SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20
3.
SELECT *
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC, department_id ASC;

章节六

1.SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
2.SELECT e.job_id,d.location_id
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id = 90
3.SELECT e.last_name,d.department_id,d.location_id,l.city
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON l.location_id = d.location_id
WHERE e.commission_pct IS NOT NULL;
4.SELECT e.last_name,e.job_id,d.department_id,d.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 d.department_name,l.street_address,e.last_name,e.job_id,e.salary
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON l.location_id = d.location_id
WHERE d.department_name = 'Executive'
6.SELECT 
    e.last_name AS employees,
    e.job_id AS 'Emp#',
    b.last_name AS manager,
    b.job_id AS 'Mgr#'
FROM 
    employees e
JOIN 
    employees b ON e.manager_id = b.employee_id;
7.SELECT last_name,department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.last_name IS NULL;
8.SELECT city
FROM locations l
LEFT JOIN departments d ON l.location_id = d.location_id
WHERE d.location_id IS NULL;
9.SELECT *
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name in ('Sales', 'IT')

第七章

1.SELECT
CURDATE(), CURTIME()
FROM DUAL
2.SELECT employee_id,last_name,salary, salary*1.2 'new salary'
FROM employees
3.SELECT last_name, LENGTH(last_name) length
FROM employees
ORDER BY last_name
4.SELECT CONCAT(employee_id, ',',last_name,',', salary) 'OUT_PUT'
FROM employees
5.
SELECT 
    employee_id, 
    FLOOR(DATEDIFF(CURDATE(), hire_date) / 365) AS 工作年限,
    DATEDIFF(CURDATE(), hire_date) AS 工作天数
FROM 
    employees
ORDER BY 
    工作年限 DESC, 工作天数 DESC;

6.
SELECT 
    e.last_name, 
    e.hire_date, 
    e.department_id 
FROM 
    employees e
JOIN 
    departments d 
ON 
    d.department_id = e.department_id -- 连接条件应该放在ON子句中
WHERE 
    YEAR(e.hire_date) > 1997 -- 明确指定hire_date来自employees表
    AND d.department_id IN (80, 90, 100) 
    AND e.commission_pct IS NOT NULL; -- 明确指定commission_pct来自employees表
7.SELECT last_name,hire_date
FROM employees
WHERE DATEDIFF(CURDATE(),hire_date) > 10000
8.SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) 'Dream Salary'
FROM employees
9.SELECT 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

第八章

-- SELECT
-- 	MAX(salary),
-- 	MIN(salary),
-- 	AVG(salary),
-- 	SUM(salary)
-- FROM employees


-- SELECT
-- 	job_id,
-- 	MAX(salary),
-- 	MIN(salary),
-- 	AVG(salary),
-- 	SUM(salary)
-- FROM employees
-- GROUP BY job_id

-- SELECT
-- 	job_id, COUNT(*) totals
-- FROM employees
-- GROUP BY job_id

-- SELECT MAX(salary) - MIN(salary) difference
-- FROM employees

-- SELECT
-- MIN(salary)
-- FROM employees
-- WHERE salary >= 6000 and manager_id is not NULL
-- GROUP BY(manager_id)



-- SELECT department_name, location_id, COUNT(employee_id), AVG(salary) avg_sal
--  FROM employees e RIGHT JOIN departments d
--  ON e.`department_id` = d.`department_id`
--  GROUP BY department_name, location_id
--  ORDER BY avg_sal DESC;

SELECT job_id,department_name, MIN(salary)
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY job_id,department_name

第九章

-- SELECT last_name,salary
-- FROM employees
-- WHERE department_id =
-- 	(
-- 		SELECT department_id
-- 		FROM employees
-- 		WHERE last_name = 'Zlotkey'
-- 	)

-- SELECT employee_id,last_name,salary
-- FROM employees
-- WHERE salary > 
-- 	(
-- 		SELECT AVG(salary) FROM employees
-- 	)
-- 

-- SELECT last_name, job_id,salary
-- FROM employees
-- WHERE salary > ALL(
-- 	SELECT salary
-- 	FROM employees
-- 	WHERE job_id = 'SA_MAN'
-- )

-- SELECT job_id, last_name
-- FROM employees
-- WHERE department_id in (
-- 	SELECT department_id
-- 	FROM employees
-- 	WHERE last_name LIKE '%u%'
-- )

-- SELECT employee_id
-- FROM employees
-- WHERE department_id in (
-- 	SELECT department_id
-- 	FROM departments
-- 	WHERE location_id = 1700
-- 	)

-- SELECT e.last_name,e.salary
-- FROM employees e JOIN  employees m 
-- ON e.manager_id = m.employee_id
-- WHERE m.last_name = 'King'

-- SELECT last_name,salary
-- FROM employees
-- WHERE salary <= (
-- 	SELECT MIN(salary)
-- 	FROM employees
-- )

-- 8,有难度
-- 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
-- 		)
-- 	)

-- 9.有难度

-- 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
-- 		)
-- 	)

-- SELECT * FROM jobs
-- WHERE job_id = (
-- 	SELECT job_id
-- 	FROM employees
-- 	GROUP BY job_id
-- 	HAVING AVG(salary) >= ALl(
-- 		SELECT AVG(salary) avg_sal
-- 		FROM employees
-- 		GROUP BY job_id
-- 		)
-- 	)

第十章

  • 练习1
-- CREATE TABLE dept01(
-- 	id INT(7),
-- 	name VARCHAR(25)
-- )

-- CREATE TABLE dept02
-- AS SELECT * FROM atguigudb.departments

-- CREATE TABLE emp01(
-- 	id INT(7),
-- 	first_name VARCHAR(25),
-- 	last_name VARCHAR(25),
-- 	dept_id INT(25)
-- )

DESC emp01

ALTER TABLE emp01
MODIFY last_name VARCHAR(50)n
-- 把信息过滤掉，创建后就不会包含数据，而仅负责表结构
CREATE TABLE emp02 AS
SELECT * FROM atguigudb.employees WHERE 1 = 2

DROP TABLE IF EXISTS emp01

ALTER TABLE emp02
RENAME TO emp01

ALTER TABLE dept02
ADD job_id VARCHAR(15)

show CREATE TABLE dept02
desc dept02

ALTER table emp01
DROP department_id
  • 练习2
-- CREATE DATABASE test02_market
-- 
-- USE test02_market
-- CREATE TABLE IF EXISTS customers(
-- 	c_num INT,
-- 	c_name VARCHAR(50),
-- 	c_contact VARCHAR(50),
-- 	c_city VARCHAR(50),
-- 	c_birth DATE
-- )

ALTER TABLE customers
MODIFY c_contact VARCHAR(50) AFTER c_birth

ALTER TABLE customers
MODIFY c_name VARCHAR(70) 

desc customers

ALTER TABLE customers
CHANGE c_contact c_phone VARCHAR(50)

ALTER TABLE customers
ADD c_gender VARCHAR(1) AFTER c_name

ALTER TABLE customers
RENAME TO customers_info

ALTER TABLE customers_info
DROP COLUMN c_city
  • 练习3
CREATE DATABASE test03_company
use test03_company
CREATE TABLE IF NOT EXISTS offices(
	officeCode int,
	city VARCHAR(30),
	address VARCHAR(50),
	country VARCHAR(50),
	postalCode VARCHAR(25)
)
CREATE TABLE IF NOT EXISTS employees(
	empNum int,
	lastName VARCHAR(50),
	firstName VARCHAR(50),
	mobile VARCHAR(25),
	code int,
	jobTitle VARCHAR(50),
	birth date,
	note VARCHAR(255),
	sex VARCHAR(5)
	
)

ALTER TABLE employees
MODIFY mobile VARCHAR(20) AFTER `code`;

ALTER TABLE employees
CHANGE birth birthday date

ALTER TABLE employees
MODIFY sex char(1)

ALTER TABLE employees
ADD favoriate_activity varchar(100)

ALTER TABLE employees
RENAME TO employees_info

第十一章

CREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8';

 USE dbtest11;
 CREATE TABLE my_employees(
 id INT(10),
 first_name VARCHAR(10),
 last_name VARCHAR(10),
 userid VARCHAR(10),
 salary DOUBLE(10,2)
 );
 CREATE TABLE users(
 id INT,
 userid VARCHAR(10),
 department_id INT
 );
 
 DESC my_employees
 
 INSERT INTO my_employees 
 VALUES(1,'patel','Ralph','Rpatel',895),
 (2,'Dancs','Betty','Bdancs',860),
 (3,'Biri','Ben','Bbiri',1100),
 (4,'Newman','Chad','Cnewman',750),
 (5,'Ropeburn','Audrey','Aropebur',1550)
 
  INSERT INTO users VALUES
 (1,'Rpatel',10),
 (2,'Bdancs',10),
 (3,'Bbiri',20),
 (4,'Cnewman',30),
 (5,'Aropebur',40)
 
 
 UPDATE my_employees
 SET last_name = 'drelxer'
 WHERE id = 3
 
 UPDATE my_employees
 SET salary = 1000
 WHERE salary < 900
 
 -- 8.有难度
 DELETE u,e
 FROM my_employees e
 JOIN users u 
 ON e.userid = u.userid
 WHERE u.userid = 'Bbiri'
 
 DELETE FROM users
 DELETE FROM my_employees
 
  TRUNCATE TABLE my_employees;

Related Posts

[数据结构与算法]基础知识回顾
by 清嘉
[自我介绍]
by 清嘉
永远回不去的梦
by 清嘉

Leave a Reply

Your email address will not be published. Required fields are marked *