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.