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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.