Shulou(Shulou.com)06/01 Report--

Use the WHERE clause to filter out rows that do not meet the criteria:

Considerations for WHERE filtering:

The WHERE clause follows the FROM clause.

WHERE clause followed by conditional expression

Column names, expressions, constants

Comparison operator: =

Greater than > =

Greater than or equal to

< 小于=3000 orader by salary; 4.3 查找一下工资大于等于10000的员工姓名，并按照 xxx 's salary is 10000的格式输入 select first_name||q'['s salary is ]' || salary as "salary is 10000 of emp" from employees where salary=10000; 4.4 查找一下工资在8000-10000之间的员工姓名和员工id select employee_id,first_name,salary from employees where salary between 8000 and 10000 order by salary; 4.5 查找一下工资为7000、3100、8100、9000、10000的员工名字 select first_name,salary from employees where salary in(7000,3100,8000,9000,10000); 4.6 查找员工名称为S开头的job_id select first_name,job_id from employees where first_name like 'S%'; 4.7 查找员工名称中带有s的 job_id select first_name,job_id from employees where first_name like '%s'; 4.8 查找名字第二个字母是o的员工信息 select first_name,job_id from employees where first_name like '_o%'; 4.9 查找名字第三个字母为e和a的员工名字 select employee_id,last_name,salary,department_id from employees where manager_id= &mgr_num order by &order_col 4.9.1 查找manager_id 为空的名字 select first_name,manager_id from employees where manager_id is null; 4.9.2 查找到04年入职的员工姓名 select last_name,hire_date from employees where hire_date like ''; 5、逻辑运算符 操作符含义AND 逻辑并，两个条件都为"真"则返回TRUEOR 逻辑或，其中一个条件为"真"则返回TRUENOT 逻辑否，如果条件为"假"则返回TRUE 5.1、查找工资大于10000并且 job_id 含有MAN的员工信息 select first_name,employee_id,salary from employees where salary>

= 1000 and job_id like'% MAN%'

5.2. find the name and employee_id of employees whose salary is greater than or equal to 10000 or whose job_id contains MAN

Select first_name,employee_id,job_id,salary from employees where salary > = 10000 or job_id like'% MAN%'

5.3.Lookup the names and job_id of employees whose job_id is not in the work id of 'HR_EMP','ST_MAN','ST_CLERK'

Select last_name,job_id from employees where job_id not in ('HR_EMP','ST_MAN','ST_CLERK')

5.4. list the names and salaries of employees whose wages are not in the range of 5000-12000

SQL > select last_name,salary from employees where salary not between 5000 and 12000 order by salary

LAST_NAME SALARY

--

Olson2100

Philtanker2200

Markle2200

Landry2400

Gee 2400

Vargas2500

Patel2500

Colmenares2500

Marlow2500

Sullivan 2500

Perkins 2500

OConnell 2600

Grant2600

Matos2600

Himuro2600

Mikkilineni2700

Seo 2700

Atkinson 2800

Geoni2800

Tobias2800

Jones2800

Rogers2900

Baida2900

Gates2900

Feeney3000

Cabrio3000

Walsh3100

Fleaur3100

Khoo3100

Davies3100

Stiles3200

Nayer3200

Taylor3200

McCain3200

Bissot3300

Mallin3300

Dellinger 3400

Rajs 3500

Dilly 3600

Ladwig3600

Chung3800

Everett 3900

Bell 4000

Bull 4100

Sarchand 4200

Lorentz 4200

Whalen4400

Pataballa 4800

Austin4800

Higgins 12008

Greenberg 12008

Hartstein 13000

Partners 13500

Russell 14000

De Haan 17000

Kochhar 17000

King 24000

57 rows selected.

6. Priority (you can use parentheses to change the priority order)

Priority

1 arithmetic operator 2 concatenator 3 comparator 4is [NOT] NULL, LIKE, [NOT] IN5 [NOT] BETWEEN6 is not equal to 7NOT8AND9OR

1. Find the name, job_id, salary and other information of employees whose salary is greater than 15000 in SA_REP or AD_PRES department.

SELECT last_name, job_id, salary FROM employees WHERE job_id = 'SA_REP' OR job_id =' AD_PRES' AND salary > 15000

2. Find the name and job_id of the employee whose job_id is SA_REP or whose salary is more than 15000 in AD_PRES department.

SELECT last_name, job_id, salary FROM employees WHERE (job_id = 'SA_REP' OR job_id =' AD_PRES') AND salary > 15000

3. List the names and salaries of employees whose salary is not between 5000 and 12000 and whose department is 20 or 50.

Select last_name,department_id,salary from employees where salary not between 5000 and 12000 and department_id=20 or department_id=50

Or

Select last_name,department_id,salary from employees where salary not between 5000 and 12000 and department_id in (2050)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.