MYSQL測試題

1、查詢工資大于12000的員工的姓名和工資

SELECT
	last_name,salary
FROM
	employees
WHERE
	salary > 12000;
           

2、查詢員工号為176的員工名,部門編号和年薪

SELECT
	last_name,
	department_id,
	salary*12*(1+IFNULL(commission_pct,0))AS 年薪
FROM
	employees
WHERE
	employee_id ='176';

           

3、選擇工資不在5000到12000的員工的姓名和工資

SELECT 
	last_name,salary
FROM 
	employees
WHERE
	salary < 5000 OR salary >12000;
           

4、選擇在20或50 号部門工作的員工姓名和部門号

SELECT
	last_name,department_id
FROM
	employees
WHERE
	department_id IN (20,50);
           

5、選擇公司中沒有管理者的員工姓名及job_id

SELECT
	last_name,job_id
FROM 
	employees
WHERE
	manager_id IS NULL;
           

6、選擇公司中有獎金的員工姓名，工資和獎金級别

SELECT
	last_name,salary,commission_pct
FROM 
	employees
WHERE
	commission_pct IS NOT NULL;
           

7、選擇員工姓名第三個字母是a的員工姓名

SELECT
	last_name
FROM 
	employees
WHERE
	last_name LIKE '__a%';
           

8、選擇姓名中有字母a和e的員工姓名

SELECT
	last_name
FROM 
	employees
WHERE
	last_name LIKE '%a%' AND last_name LIKE '%e%';
           

9、顯示出表employees表彙總first_name以e結尾的員工資訊

SELECT
	*
FROM 
	employees
WHERE
	first_name LIKE '%e';
           

10、顯示出表employees部門編号在80-100之間的姓名、職位

SELECT
	last_name,department_id
FROM 
	employees
WHERE
	department_id BETWEEN 80 AND 100;
           

11、顯示出表employees的manager_id是100,101,110的員工姓名、職位

SELECT
	last_name,department_id
FROM 
	employees
WHERE
	department_id IN (100,101,110);
           

12、查詢沒有獎金，且工資小于18000的salary，last_name

SELECT
	last_name,salary
FROM
	employees
WHERE
	commission_pct IS NULL AND salary < 18000;
           

13、查詢employees表中，job_id不為‘IT’或者工資為12000的員工資訊

SELECT 
	*
FROM
	employees
WHERE
	job_id <> 'IT' OR salary = 12000;
           

14、檢視部門departments表的結構

15、查詢部門departments表中涉及到了哪些位置編号

SELECT
	location_id
FROM 
	departments;
           

16、經典面試題：

不一樣，commission_pct中有null值，and：其中一個為null值，其他的都為null