Pseudocolumn

rowid: the physical location of the current data in the database

--View employee's job number, name, salary, and physical location id
select employee_id,first_name,salary,rowid
from employees;

rownum: number the query results that meet the query criteria (starting from 1)

-- View employee's job number,Name,salary, Add number for each row of data(1-start)
select employee_id,frist_name,salary,rownum
from employees;
​
-- View employee information with salary greater than 10000: Job No,Name,salary,No rownum.
select employee_id,first_name,salary,rownum
from employees
where salary>10000;
​
​
-- View employee table,Top 10 employee information(Job No,Name,salary,No)[No<=10]
​
select employee_id,first_name,salary,rownum
from employees
where rownum <= 10;
​
-- View employee table,Eleventh~20th employee information(Job No,Name,salary,No)
select employee_id,first_name,salary,rownum
from employees
where rownum >10 and rownum <=20; [×]

Subquery

--Employee information with x salary (job number, name, salary)?
 select * from employees where salary = X; --x is the highest salary?
 select max(salary) from employees; -- x

1. Subquery result is the value of 1 row and 1 column

-- Employee information with the highest salary(Job No,Name,salary)?
//Ideas:
1. Get the highest salary   maxSalary
   select max(salary) from employees;--maxSalary;
​
2. Get paid for maxSalary Employee information for(Job No,Name,salary)
   select employee_id,first_name,salary from employees where salary = (maxSalary);
3. merge SQL
    select employee_id,first_name,salary from employees where salary = (select max(salary) from employees);
    
-- Query the information of employees whose salary is greater than the average salary(Job No,Name,salary)
1. Get average wage avgSalary
    select avg(salary) from employees;--avgSalary
2. Get paid>avgSalary Employee information for?
    select * from employees where salary > (avgSalary);
3. merge
    select * from employees where salary > (select avg(salary) from employees);

2. The result of subquery is the value of n row and 1 column

-- Query and last name 'King' Employee information in the same department of?
1. get king Department id: --deptId
    select department_id from employees where last_name ='King';--DeptId
2. Query Department id by deptId Employee information for?
    select * from employees where department_id in (DeptId)
3. merge:
 select * from employees where department_id in (select department_id from employees where last_name ='King');

3. The result of subquery is n rows and N columns

-- View employee information(Job No,Name,salary),Top 10 employee information sorted by salary descending?
select employee_id,first_name,salary
from employees
where rownum <=10
order by salary desc;[error: Sort by reason and then take the top 10,actual sql Execution order: first get the top 10 and then sort]
​

Artificial thinking:
1. Sort by salary descending first---Sorted results   
    select employee_id,first_name,salary from employees order by salary desc; -- EMP1 surface-- n That's ok n Results temporary table for column
2. Take the top 10 results after sorting
    select * 
    from (EMP1) 
    where rownum <=10;
3. merge
    select * from
        (select employee_id,first_name,salary from employees order by salary desc) 
    where rownum <=10;

Paging query [key]

-- 1. View employee table,Eleventh~20th employee information(Job No,Name,salary,No)
    //Ideas:
    1. Get the EMP1 surface?[Add a to the table rownum Of RN S / n column of]
    select employee_id,first_name,salary,rownum as rn
    from employees;--EMP1
    2. query EMP1 in RN The value of is 11~20 Employee information between?[Query temporary table for step 1,Query again]
    select employee_id,first_name,salary,rn
    from (emp1)
    where rn >=11 and rn <=20;
    3. merge
    select employee_id,first_name,salary,rn
    from 
        (select employee_id,first_name,salary,rownum as rn from employees)
    where rn >=11 and rn <=20;

-- 2. Query the 11th item after employee information is sorted in descending order of salary~Article 20 employee information?[important, Written examination, 100%development]
​
    1. Sort by salary descending:
        select * from employees order by salary desc;-- t1
    2. Yes t1 Table add RN Sequence number column
        select employee_id,first_name,salary,rownum rn
        from (t1);--t2
    3. obtain RN 11~20 Employee information between?
       select employee_id,first_name,salary,rn
       from (t2)
       where rn >=11 and rn <=20;
        
    4. merge
        select employee_id,first_name,salary,rn
        from
            (select employee_id,first_name,salary,rownum rn
                 from 
                    (select * from employees order by salary desc))
        where rn >=11 and rn<=20;

​