Oracle442 Application Scenarios------------Basic Application Scenarios

Basic Knowledge//////////////////

Scenario 178: The simplest SELECT statement

SELECT * from Employees;

Scenario 179: Specifying the columns to query

COL Emp_name FORMAT A20
COL SEX FORMAT A10
COL TITLE FORMAT A10
SELECT Emp_name, Sex, Title from Hrman. Employees;

Scenario 180: Using the DISTINCT keyword

SELECT Title from Hrman. Employees;

SELECT DISTINCT Title from Hrman. Employees;

Scenario 181: Using RowNum

COL Emp_name FORMAT A20
COL SEX FORMAT A10
COL TITLE FORMAT A10
SELECT Emp_name, Sex, Title from Hrman. Employees
WHERE rownum<=3;

Scenario 182: Changing the displayed column headings

COL name FORMAT A20
COL Sex FORMAT A4
COL Post FORMAT A10
COL ID FORMAT A20
SELECT emp_name as name, sex as gender, title as position, wage as salary, Idcard as identity card from Hrman. Employees;

Scenario 183: Setting query criteria

COL Emp_name FORMAT A20
SELECT emp_name, wage from Hrman. Employees WHERE wage > wage < 4000;

Scenario 184: Using the Bitween keyword in query criteria

COL Emp_name FORMAT A20
SELECT emp_name, wage from Hrman. Employees WHERE wage between and 4000;

Scenario 185: Using in key in query conditions

COL Emp_name FORMAT A20
COL TITLE FORMAT A20
SELECT Emp_name, TITLE, wage from Hrman. Employees WHERE emp_name in (' Zhang San ', ' John Doe ', ' Harry ');

Scenario 186: Implementing a fuzzy query

COL Emp_name FORMAT A20
COL TITLE FORMAT A20
COL Idcard FORMAT A20
SELECT Emp_name, TITLE, Idcard from Hrman. Employees
WHERE idcard like '%ddd% ';

COL Emp_name FORMAT A20
COL TITLE FORMAT A20
COL Idcard FORMAT A20
SELECT Emp_name, TITLE, Idcard from Hrman. Employees
WHERE idcard like ' 110123_adx_ ';

Scenario 187: Sorting Result Sets

COL Emp_name FORMAT A20
COL TITLE FORMAT A20
COL Idcard FORMAT A20
SELECT Emp_name, TITLE, Idcard from Hrman. Employees
ORDER by Emp_name;

COL Emp_name FORMAT A20
COL TITLE FORMAT A20
SELECT Emp_name, TITLE, wage from Hrman. Employees
ORDER by wage DESC;

Scenario 188: Sorting Multiple columns

COL Emp_name FORMAT A20
COL SEX FORMAT A20
SELECT Emp_name, Sex, wage from Hrman. Employees
ORDER by Sex, wage;

Scenario 189: Using grouping statistics

COL Post FORMAT A10
SELECT title as position, AVG (wage) as average public capital from Hrman. Employees GROUP by Title;

COL Sex FORMAT A10
COL Title FORMAT A10
SELECT Sex, Title, AVG (wage) from Hrman. Employees GROUP by Title;

SELECT dep_id, AVG (wage) from Hrman. Employees
GROUP by dep_id has AVG (wage) > 4000;

Scenario 190: Connection Query

Internal connection
COL dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1. Dep_name, T2. Emp_name from Hrman. Departments T1, Hrman. Employees T2
WHERE t1. Dep_id=t2. dep_id;

External links:
COL dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1. Dep_name, T2. Emp_name from Hrman. Departments T1 INNER JOIN Hrman. Employees T2
on T1. Dep_id=t2. dep_id;

COL dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1. Dep_name, T2. Emp_name from Hrman. Departments T1 left JOIN Hrman. Employees T2
on T1. Dep_id=t2. dep_id;

COL dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1. Dep_name, T2. Emp_name from Hrman. Employees T2 right JOIN Hrman. Departments T1
on T1. Dep_id=t2. dep_id;

COL dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1. Dep_name, T2. Emp_name from Hrman. Employees T2 full JOIN Hrman. Departments T1
on T1. Dep_id=t2. dep_id;

Cross Connect
COL dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1. Dep_name, T2. Emp_name from Hrman. Employees T2 Cross JOIN Hrman. Departments T1;

Scenario 191: Determining null values in a connection query for null values

COL dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1. Dep_name, T2. Emp_name from Hrman. Departments T1 left JOIN Hrman. Employees T2
on T1. Dep_id=t2. dep_id;

COL dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1. Dep_name, T2. Emp_name from Hrman. Departments T1 left JOIN Hrman. Employees T2
on T1. Dep_id=t2. dep_id
WHERE T2. EMP_ID is NULL;

Scenario 192: A simple subquery

Find all employees in the office:
COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title from Hrman. Employees WHERE dep_id =
(SELECT dep_id from Hrman. Departments WHERE dep_name = ' Office ');

Returns the values of two departments:
COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title from Hrman. Employees WHERE dep_id =
(SELECT dep_id from Hrman. Departments WHERE dep_name = ' office ' OR dep_name = ' personnel department ');

Scenario 193: Using the tool and function return values in a query

Information on all employees who are below the grade wage in the statistics table

COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, wage from Hrman. Employees WHERE Wage <
(SELECT AVG (wage) from Hrman. Employees);

Application Scenario 194:in keyword and return value subquery

Inquire about employee information in office and personnel department

COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, wage from Hrman. Employees WHERE dep_id in
(SELECT dep_id from Hrman. Departments WHERE dep_name = ' office ' OR dep_name = ' personnel department ');

Application Scenario 195:exists keyword and sub-query

Find employee information in the Personnel department
COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, wage from Hrman. Employees E
WHERE EXISTS
(SELECT dep_id from Hrman. Departments D
WHERE e.dep_id = d.dep_id and D.dep_name= ' Personnel Department ');

Use the IN keyword:
COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, wage from Hrman. Employees E
WHERE e.dep_id in
(SELECT dep_id from Hrman. Departments D
WHERE e.dep_id = d.dep_id and D.dep_name= ' Personnel Department ');

Scenario 196: Merge query with the Union keyword

Query the employee in the table for department managers in each department

COL dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT dep_id, dep_name from Hrman. Departments
UNION
SELECT dep_id, emp_name from Hrman. Employees WHERE Title = ' department manager ';

Employee records with a salary greater than 3000:

COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, wage from Hrman. Employees
UNION
SELECT Emp_name, Title, wage from Hrman. Employees WHERE wage > 3000;

High efficiency:
COL Emp_name FORMAT A20
COL Title FORMAT A20
SELECT Emp_name, Title, wage from Hrman. Employees
UNION All
SELECT Emp_name, Title, wage from Hrman. Employees WHERE wage > 3000;

Scenario 197: Using the Decode function in the SELECT statement

SELECT emp_name, DECODE (Sex, ' Male ', ' Mr. ', ' female ', ' lady ', ' unknown ') as sex
From Hrman. Employees;

Scenario 198: Using Select and the case function in the play

SELECT Emp_name, Case Sex when ' male ' then ' Mister ' when ' woman ' Then ' lady ' ELSE ' unknown ' END as Sex
From Hrman. Employees;

Query the employee salary scale in employees:

SELECT emp_name, wage, case where wage<=3000 then ' low ' when wage>3000 and wage<5000 then ' when wage>=5000 the N ' High ' END as GRADE
From Hrman. Employees;

Scenario 199: Saving Query Results

Save the name and job title information for all employees in the office to the table in office:

COL Emp_name FORMAT A20
COL Title FORMAT A20
CREATE TABLE Hrman. Office as
SELECT E.emp_name, E.title
From Hrman. Employees e, Hrman. Departments D
WHERE e.dep_id = d.dep_id and d.dep_name = ' Office ';
SELECT * from Hrman. Office;

Scenario 200: Inserting data statements

INSERT into Hrman. Departments VALUES (100, ' PR ');
SELECT * from Hrman. Departments;

INSERT into Hrman. Employees (Emp_name, Sex,title, Idcard, dep_id)
VALUES (' xiaoming ', ' Male ', ' clerk ', ' 110123456789 ', 2);

Scenario 201: Using default values when inserting data

ALTER TABLE Hrman. Employees ADD inputdate Date DEFAULT (sysdate);

INSERT into Hrman. Employees (Emp_name, Sex,title, Idcard, wage, dep_id)
VALUES (' Xiao Li ', ' Male ', ' clerk ', ' 210123456789 ', 2500, 2);

Scenario 202: Modifying Data statements

Increase all recorded wages in the table by 10%:

UPDATE Hrman. Employees SET wage=wage*1.1;

10% increase in employee salaries for "office" for all departments in the table

UPDATE Hrman. Employees SET wage=wage*1.1
WHERE dep_id = (SELECT dep_id from Hrman. Departments WHERE dep_name = ' Office ');

Scenario 203: You are not allowed to use the same value in a Uniqueness constraint column when modifying data

ALTER TABLE Hrman. Employees
ADD CONSTRAINT Uk_empname
UNIQUE (Emp_name);

UPDATE Hrman. Employees SET emp_name= ' Zhang San ' WHERE emp_name= ' John Doe ';

Scenario 204: Modifying data is not a violation of check constraints

ALTER TABLE Hrman. Employees
ADD CONSTRAINT ck_empwage CHECK (wage>0);

UPDATE Hrman. Employees SET wage=-1 WHERE emp_name= ' Zhang San ';

Scenario 205: Cannot violate foreign KEY constraints when modifying data

Hrman for table. Create a FOREIGN KEY constraint in the dep_id column of the Departments dep_id column and table Hrman.employees
ALTER TABLE Hrman. Employees
ADD CONSTRAINT Fk_emp_depid
FOREIGN KEY (dep_id) REFERENCES Hrman. Departments (DEP_ID);

UPDATE Hrman. Employees SET dep_id=200 WHERE emp_name= ' Zhang San ';

Scenario 206: Delete a data statement

DELETE from Hrman. Employees WHERE emp_name = ' Xiao Ming ';

TRUNCATE TABLE Hrman. Employees;

Copyright NOTICE: Welcome reprint, Hope in your reprint at the same time, add the original address, thank you with

Oracle442 Application Scenarios------------Basic Application Scenarios