Oracle442 application scenarios ---------- basic application scenarios and application scenarios

Oracle442 application scenarios ---------- basic application scenarios and application scenarios

 

/// // Basic knowledge //////////////////

 

Application Scenario 178: the simplest select statement

SELECT * FROM Employees;

Use Case 179: Specify the column to be queried

COL EMP_NAME FORMAT A20
Col sex format A10
Col title format A10
SELECT Emp_name, Sex, Title from hrman. Employees;

Use Case 180: Use the DISTINCT keyword

SELECT Title from hrman. Employees;

Select distinct Title from hrman. Employees;

Use Case 181: Use 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;

Application Scenario 182: Change the column title

COL name FORMAT A20
COL gender FORMAT A4
COL job FORMAT A10
Col id card FORMAT A20
SELECT EMP_NAME AS name, sex as gender, title as title, wage as salary, idcard as id card from hrman. Employees;

Use Case 183: Set query Conditions

COL EMP_NAME FORMAT A20
SELECT Emp_Name, Wage from hrman. Employees WHERE Wage> 3000 AND Wage <4000;

Application Scenario 184: Use the BITWEEN keyword in the query Condition

COL EMP_NAME FORMAT A20
SELECT Emp_Name, Wage from hrman. Employees WHERE Wage BETWEEN 3000 AND 4000;

Application Scenario 185: Key to using 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', 'Li si', 'wang wu ');

Application Scenario 186: Fuzzy search

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 '2017 _ adx _';

Application Scenario 187: sorting result set

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;

Application Scenario 188: Sort multiple columns

COL EMP_NAME FORMAT A20
Col sex format A20
SELECT EMP_NAME, Sex, Wage from hrman. Employees
Order by Sex, Wage;

Use Case 189: grouping statistics

COL job FORMAT A10
SELECT Title AS Title, AVG (Wage) AS average 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 having avg (Wage) & gt; 4000;

Use Case 190: Connection Query

Internal Connection
COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1.Dep _ name, t2.Emp _ name from hrman. Orders t1, HRMAN. Employees t2
WHERE t1.Dep _ id = t2.Dep _ id;

External link:
COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1.Dep _ name, t2.Emp _ name from hrman. Orders 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. Orders 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. ments 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. ments t1
ON t1.Dep _ id = t2.Dep _ id;

Cross join
COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1.Dep _ name, t2.Emp _ name from hrman. Employees t2 cross join hrman. ments t1;

Application Scenario 191: null value judgment in connection Query

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT t1.Dep _ name, t2.Emp _ name from hrman. Orders 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. Orders t1 left join hrman. Employees t2
ON t1.Dep _ id = t2.Dep _ id
WHERE t2.Emp _ id is null;

Application Scenario 192: A simple subquery

Query 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. Orders ments WHERE Dep_name = 'hangzhou ');

Returns the values of the 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 author ');

Application Scenario 193: using appliances and function return values in queries

Information of all employees whose salaries are lower than grade in the statistical 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: subqueries with IN keywords and returned values

Query employee information in the 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 author ');

Scenario 195: EXISTS keywords and subqueries

Query 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. Orders ments d
WHERE e. Dep_id = d. Dep_id AND d. Dep_name = 'personnel author ');

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. Orders ments d
WHERE e. Dep_id = d. Dep_id AND d. Dep_name = 'personnel author ');

Application Scenario 196: Merge queries using the UNION keyword

Query the department managers of each department from the Employee list.

COL Dep_name FORMAT A20
COL Emp_name FORMAT A20
SELECT Dep_Id, Dep_Name from hrman. Orders ments
UNION
SELECT Dep_Id, Emp_Name from hrman. Employees WHERE Title = 'department manager ';

Records of employees whose salaries exceed 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;

Application Scenario 197: Use the DECODE function in the select statement

SELECT Emp_name, DECODE (Sex, 'male', 'Mr ', 'female', 'Lady ', 'unknown') AS Sex
From hrman. Employees;

Application Scenario 198: Use the select and CASE functions in the play

SELECT Emp_name, CASE Sex WHEN 'male' then' Mr. 'when' female 'then' Ms. 'else' unknown 'end AS Sex
From hrman. Employees;

The following table lists the employee wage levels in the Employees table:

SELECT Emp_name, Wage, case when Wage <= 3000 THEN 'low' WHEN Wage> 3000 AND Wage <5000 then' in 'when Wage> = 5000 then' high 'end AS GRADE
From hrman. Employees;

Use Case 199: Save query results

Save the names and positions of all employees in the OFFICE to the following table:

COL Emp_name FORMAT A20
COL Title FORMAT A20
Create table hrman. Office
SELECT e. Emp_Name, e. Title
From hrman. Employees e, HRMAN. Departments d
WHERE e. Dep_id = d. Dep_Id AND d. Dep_Name = 'hangzhou ';
SELECT * from hrman. Office;

Use Case 200: insert data statement

Insert into hrman. attributes VALUES (100, 'public relations ');
SELECT * from hrman. Orders ments;

Insert into hrman. Employees (Emp_Name, Sex, Title, IdCard, Dep_Id)
VALUES ('xiaoming ', 'male', 'employee', '000000', 2 );

Application Scenario 201: use the default value 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 ('lil', 'male', 'salesman', '123', 210123456789, 2 );

Use Case 202: modify data statements

Increase the salary of all records in the table by 10%:

Update hrman. Employees SET Wage = Wage * 1.1;

Increase the employee salaries of all departments in the table for "office" by 10%

Update hrman. Employees SET Wage = Wage * 1.1
WHERE Dep_id = (SELECT Dep_id from hrman. Orders ments WHERE Dep_name = 'hangzhou ');

Application Scenario 203: when modifying data, the same value cannot be used in the unique constraint column.

Alter table hrman. Employees
Add constraint UK_EMPNAME
UNIQUE (Emp_name );

Update hrman. Employees SET Emp_name = 'zhang san' WHERE Emp_name = 'Li si ';

Application Scenario 204: Modifying data does not violate check constraints.

Alter table hrman. Employees
Add constraint CK_EMPWAGE CHECK (WAGE> 0 );

Update hrman. Employees SET Wage =-1 WHERE Emp_Name = 'zhang san ';

Application Scenario 205: foreign key constraints cannot be violated when data is modified

Is the table HRMAN. Create a foreign key constraint in the DEP_id column of the Departments and the DEP_ID column of the HRMAN. Employees table.
Alter table hrman. Employees
Add constraint FK_EMP_DEPID
Foreign key (Dep_id) references hrman. sums (Dep_id );

Update hrman. Employees SET Dep_id = 200 WHERE Emp_Name = 'zhangsan ';

Application Scenario 206: delete a data statement

Delete from hrman. Employees WHERE Emp_Name = 'xiaoming ';

Truncate table hrman. Employees;

Copyright Disclaimer: you are welcome to reprint it. I hope you can add the original article address while reprinting it. Thank you for your cooperation.