oracle10g Training Log ☆ 3

Aug 22 posted in Oracle (RSS), 284 views.

SQL> conn / as sysdba
SQL> shutdown immediate; --- Stop the database
rn

---------- The following case is not open database
SQL> conn zou / zou
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
rn
Solution:
SQL> conn / as sysdba
SQL> startup; --- start Database
rn

--------------- Check in June to a unit of people, and the wages of more than 10000
QL> select * from employees
2 where substr (hire_date, 4,1) = '6 'and salary> 10000;
rn

rn

--------------- Check full name stevenking employees or employees working more than 10 years
SQL> select * from employees
2 where lower (first_name | | last_name) = 'stevenking'
3 or months_between (sysdate, hire_date)> 120;
rn

---------------------- Multi-table query
pk: primary key primary key property: each line of distinction between the data does not allow for the null, not allowed to repeat
rn
FK: foreign key foreign key properties: for multi-table queries, foreign key values must come to regard the primary key, or do not fill in
------------ Equivalent query
SQL> select first_name, department_name
2 from employees, departments
3 where employees.department_id = departments.department_id;
rn

Note: The multi-table query, if the n-table queries, the join condition to write at least n-1 Ge, and the use and connect
rn

SQL> select first_name, salary, department_id, department_name
2 from employees, departments
3 where employees.department_id = departments.department_id; - this syntax error, because the two tables have a department_id
rn

SQL> select employees.first_name, employees.salary, departments.department_id, departments.department_name
2 from employees, departments
3 where employees.department_id = departments.department_id;
rn

SQL> select e.first_name, e.salary, d.department_id, d.department_name
2 from employees e, departments d
3 where e.department_id = d.department_id;
rn
Note: The multi-table query, it is best, "table name. One of the" in order to avoid ambiguity
rn

---------- Look at the IT department staff members, salary
select first_name, salary
from employees, departments
where department_name = 'IT' and employees.department_id = departments.department_id;
rn

Note: The multi-table query to add search criteria, Canada and
rn

--------------- 3 tables or more than three tables
SQL> select sname, cname, grade
2 from student, course, sc
3 where student.sno = sc.sno and course.cno = sc.cno;
rn

---------------- Search Department of Computer Science of the boys won the election for which the number of sub-class
SQL> select sname, cname, grade
2 from student, course, sc
3 where student.sno = sc.sno and course.cno = sc.cno and sdept = 'computer' and ssex = 'M';
rn

------------ Non-equivalent query
rn
SQL> select first_name, salary, grade_level
2 from employees, job_grades
3 where salary between LOWEST_SALARY and HIGHEST_SALARY;
rn

rn
-------------, Inquiries and
-------------- Left outer join
----------- What people do not department
select first_name, department_name
from employees left outer join departments
on employees.department_id = departments.department_id;
rn

------------- Right outer join
select first_name, department_name
from employees right outer join departments
on employees.department_id = departments.department_id;
rn

rn
------------- Full outer join
rn
select first_name, department_name
from employees full outer join departments
on employees.department_id = departments.department_id;
rn
SQL> select *
2 from student left outer join sc
3 on student.sno = sc.sno;
rn
Note: There is no where Canada where, there is where Canada and
rn

--------------------- Set of functions
max, min, avg, sum, count
rn
-------------- max maximum, min minimum, sum sum
SQL> select max (salary), min (salary), sum (salary)
2 from employees;
rn
MAX (SALARY) MIN (SALARY) SUM (SALARY)
----------- ----------- -----------
24000 2500227100
rn
------------ Sector number is 100, maximum, minimum, wage sum
SQL> select max (salary), min (salary), sum (salary)
2 from employees;
3 where department_id = 100;
rn
------------- avg average
SQL> select avg (commission_pct)
2 from employees ;----- have a commission with the sum divided by the number of people
rn
AVG (COMMISSION_PCT)
-------------------
.2125
rn
SQL> select avg (nvl (commission_pct, 0))
2 from employees ;----- divided by the sum of all the number of available
rn
AVG (NVL (COMMISSION_PCT, 0))
--------------------------
.032692308
rn
-------------- count (*) calculated the number of rows
SQL> select count (*)
2 from employees
3 where department_id = 100;
rn
------------- Calculation of a commission a few people
SQL> select count (*)
2 from employees
3 where commission_pct is not null;
rn

-------------- count (column name) in accordance with terms of a non-empty column the number of rows
SQL> select count (commission_pct)
2 from employees;
rn
-------------- count (distinct column name) to remove duplicate records, calculate the number of rows
rn
SQL> select count (distinct sdept) from student;
rn

rn
---------------- group by group
--------- How many people were seeking each department?
select count (*)
from student
group by sdept;
rn
select sdept, count (*)
from student
group by sdept;
rn
Note: 1. In the group by not behind the column can not select the back does not appear
2. In select behind a set of functions that arise outside the column, you must appear in the group by the back
rn
------------- Average wage in each sector
select department_id, avg (salary)
from employees
group by department_id;
rn

select department_name, avg (salary)
from employees, departments
where employees.department_id = departments.department_id
group by departments.department_id, department_name;
rn

rn
--------------- Seeking men and women, respectively, each department the number of people
select ssex, sdept, count (*)
from student
group by ssex, sdept;
rn

rn
--------------- Department of boys in each of the respective order to the number of people
select sdept, count (*)
from student
where ssex = 'M'
group by sdept;
rn

----------------- having
select department_id, avg (salary)
from employees
group by department_id
having avg (salary)> 8000
order by avg (salary) desc;
rn

Note: The set of functions (min, max, sum, count, avg) filters must be having