Multi table query of Oracle Foundation (4)

Time:2020-11-7
Department name of the Department where all employees belong
select last_name,name
from s_emp,s_dept;
S1 table (ID, name)
 
Table S1
id name
1 s
2 a
3 b
 
 
 
 
 
 
 
 
 
S2 table (ID, age)

S2 table
id name
1 s
2 a
3 b
 
 
 
 
 
 
 
 
Spliced table: s table
 
S table
s1.id s1.name s2.id s2.age
1 s 1 20
1 a 2 21
1 b 3 22
2 s 1 20
2 a 2 21
2 b 3 22
3 s 1 20
3 a 2 21
3 b 3 22
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Check the names and ages of all the people
select name,age
from s1,s2;
The essence of multi table query is to query a table
Multi table queries produce Cartesian products
Eliminate Cartesian product: use join condition to eliminate Cartesian product. Put connection condition in where
The join condition is often used for the value of the primary key = the value of the foreign key
select s1.name,s2.age
from s1,s2
where s1.id=s2.id;
1. Equal connection: connection connected with equal sign
1) Department name of the Department where all employees belong
 select e.id,e.last_name,d.name
 from s_emp e,s_dept d
 where e.dept_id=d.id;
2) Query employee’s name and department‘s region name (3 tables)
     select e.last_name,r.name
  from s_emp e,s_dept d,s_region r
  where e.dept_id=d.id
  and d.region_id=r.id;
3) Query department name including Sa’s employee name salary
 select d.name,e.last_name||e.first_name,e.salary
 from s_emp e,s_dept d
 where e.dept_id=d.id
 and lower(d.name) like ‘%sa%’;
Note: write connection condition first and then limit condition (eliminate useless data)
 
4) Inquire about employees with salary between 1000 and 2000 in European Sales Department
 select r.name,d.name,e.salary
 from s_emp e,s_dept d,s_region r
 where e.dept_id=d.id and d.region_id=r.id
 and r.name=’Europe’
 and e.salary between 1000 and 2000
 and d.name=’Sales’;
5) The name of the query department is 5, and the salary of the employees in the Department is not equal to 1500, and the salary is sorted in descending order
 select d.name,e.salary
 from s_emp e,s_dept d
 where e.dept_id=d.id
 and length(d.name)=5
 and e.salary!=1500
 order by e.salary DESC;
2. Unequal connection: the connection condition does not use the equal sign connection
            >,drop table s_gender;
create table s_gender(
 id number(5) primary key,
 minSal number(7),
 maxSal number(7),
 name varchar2(20));
insert into s_gender
Values (1, 01000, ‘blue collar’);
insert into s_gender
Values (210001500,’white collar ‘);
insert into s_gender
Values (3150 02500, ‘gold collar’);
commit;
Query salary grade of all employees?  
  select e.last_name,g.name
  from s_emp e,s_gender g
  where e.salary between g.minSal and g.maxSal;
3. Use in where
1) External connection: query the data with null foreign key
Use: (+) (Oracle database only)
Rule: (+) is placed on the party with less query data (i.e. the party with null value in the table)
(1) Left outer join: (+) to the right of the equal sign (standard SQL:.. left join… On… Left outer join)
 
Query the name of the Department where the employee belongs, including the employees without department number (all employees)
   select e.last_name,d.name
   from s_emp e,s_dept d
   where e.dept_id=d.id(+);
Note: the data with null foreign key value cannot be queried by such connection.
   ——————————————-  
Standard:
   select e.last_name,d.name
   from s_emp e left join s_dept d
   on e.dept_id=d.id;
(2) Right outer join: (+) is placed to the left of the equal sign
Query the Department names of all employees, but you need to query all departments_ dept values(1000,’teaching’,2);
commit;
 select e.last_name,d.name
 from s_emp e,s_dept d
      where e.dept_id(+)=d.id;
—————————————-
Standard:
 select e.last_name,d.name
 from s_emp e right join s_dept d
 on e.dept_id=d.id;
2) Full join (standard SQL statement: full join… On…)
Query the name of the Department where the employee belongs. If there is no department number, all departments will be found
   select e.last_name,d.name
   from s_emp e full join s_dept d
   on e.dept_id=d.id;
3) Self join: join from the same table
Query the last of all employees’ managers_ Name, salary include employees without managers?
   select w.last_name,w.salary,m.last_name,m.salary
   from s_emp w,s_emp m
   where w.manager_id=m.id(+);
4) Collective connection
Union: Union, duplicate columns display only one row
Union all: Union, does not eliminate duplicate rows
Minus: difference set
Intersect: intersection
Rownum: pseudo column, pagination technology, logical location
It can only be equal to 1
Cannot be greater than or equal to any positive integer
Can be less than or equal to any positive integer
  select *
  from s_dept
Where rownum < = 2; / / query the first two pieces of data in the table
ROWID: the physical location where the data is saved to the file
  select rowid
  from s_dept;
(1) the first five records in the Department table?
   select *
   from s_dept
   where rownum<=5;
(2) data from items 3 to 5 in the Department table?
   select *
   from s_dept
   where rownum<=5
   minus
   select *
   from s_dept
   where rownum<=2;

Recommended Today

JS function

1. Ordinary function Grammar: Function function name (){ Statement block } 2. Functions with parameters Grammar: Function function name (parameter list){ Statement block } 3. Function with return value Grammar: Function function name (parameter list){ Statement block; Return value; } Allow a variable to accept the return value after calling the function Var variable name […]