Common Data Manipulation language

DISTINCT:
Specifies that a record of the specified column has no duplicate data in the result set
SELECT DISTINCT EMAIL from HR. EMPLOYEES;


ROWNUM:
Query the top n records in a result set using RowNum columns
SELECT * from HR. EMPLOYEES WHERE rownum<=3;


Show Column Aliases:

SELECT Emp_name as name from HR. EMPLOYEES;


Set query criteria:
SELECT SALARY from HR. EMPLOYEES WHERE salary>2000 and SALARY <4000;


Using the between keyword in query criteria
SELECT SALARY from HR. EMPLOYEES WHERE SALARY between and 4000;


Using the IN keyword in a query condition
Specifying a list of values for a query
SYS as [email protected]>select first_name,last_name, SALARY from HR. EMPLOYEES WHERE SALARY in (' 2000 ', ' 3000 ');

First_Name last_name SALARY
-------------------- ------------------------- ----------
Anthony Cabrio 3000
Kevin Feeney 3000



To implement a fuzzy query:
Using the LIKE keyword and wildcard characters to implement a fuzzy query
% contains 0 or more strings of any character
-Any single character
？ Any single character
# indicates the number of 0~9
[] Specifies any single character in the range or collection, [A~f] represents one character in A~f

SYS as [email protected]>select first_name,last_name,phone_number from HR. EMPLOYEES WHERE phone_number like '%80% ';

First_Name last_name Phone_number
-------------------- ------------------------- --------------------
Shelley Higgins 515.123.8080
Trenna Rajs 650.121.8009



Sort result set:
Order by {< Sort expression >[asc| DESC]} [,... N
SELECT first_name,last_name,salary from HR. EMPLOYEES WHERE rownum<=10 ORDER by SALARY DESC

To sort multiple columns:
SELECT First_name,last_name,salary,phone_number from HR. EMPLOYEES WHERE rownum<=10 ORDER by Salary,phone_number;

Use grouping statistics:
Use the GROUP BY clause to specify grouping criteria for query results
All columns in the select list for each non-aggregate expression should be included in the group by list, and the group by expression must exactly match the select list expression
GROUP BY [ALL] < grouping expressions >[,.... N]
SYS as [email protected]>select department_id,avg (SALARY) from HR. EMPLOYEES GROUP by department_id;
department_id AVG (SALARY)
------------- -----------
100 8601.33333
30 4150

Having
Having is usually used in conjunction with the GROUP BY clause, as in the WHERE clause
Having: clause search conditions are applied after grouping operations
Where: clause search criteria are applied before grouping
SYS as [email protected]>select department_id,avg (SALARY) from HR. EMPLOYEES GROUP by department_id have AVG (SALARY) >4000;

department_id AVG (SALARY)
------------- -----------
100 8601.33333
30 4150
7000
20 9500

Connection query:
Inner joins use comparison operators (= numbers), columns that contain null values do not match any values, cannot be included in the result set, and null values do not match other null values
SYS as [email protected]>select T1. First_name,t1. Last_name,t2. Department_name from HR. Departments T2,HR. EMPLOYEES T1 WHERE T1. Department_id=t2. department_id;

SYS as [email protected]>select T1. First_name,t1. Last_name,t2. Department_name from HR. Departments T2 INNER JOIN HR. EMPLOYEES T1 on T1. Department_id=t2. department_id;

Outer joins: Each row of data in the primary table matches the data columns from the table, matches the returned to the result set, does not conform, and will be filled with the above values and returned to the result set

Left [OUTER] join: The table on the left side of the join clause is the primary table, and all records in the primary table appear in the result set, and if the records in the primary table do not have matching data in the right table, the column values for the right table in the result set are empty
SYS as [email protected]>select T1. Department_name,t2. First_Name from HR. Departments T1 left OUTER JOIN HR. EMPLOYEES T2 on T1. Department_id=t2. department_id;

Right [OUTER] join: The table to the right of the clause is the primary table, and all records in the primary table will appear in the result set, and if the records in the primary table do not have matching data in the left table, the columns of the right table in the result set
SYS as [email protected]>select T1. First_name,t2. Department_name from HR. EMPLOYEES T1 right OUTER JOIN HR. Departments T2 on T1. Department_id=t2. department_id;

Full [OUTER] Join complete external connection: The connection includes all rows in the join table, regardless of whether they match (related to the set of left and right outer joins)
SYS as [email protected]>select T1. First_name,t2. Department_name from HR. EMPLOYEES T1 full OUTER JOIN HR. Departments T2 on T1. Department_id=t2. department_id;

Cross connect: Infrequently, every two rows in two tables may be grouped together into one row in the result set, typically for all possible combinations of records that are exhaustive for two tables
SELECT T1. First_name,t2. Department_name from HR. EMPLOYEES T1 Cross JOIN HR. Departments T2
The query does not exist for the employee's department:
SYS as [email protected]>select T1. First_name,t2. Department_name from HR. EMPLOYEES T1 right OUTER JOIN HR. Departments T2 on T1. Department_id=t2. department_id WHERE T1. First_Name is NULL;


Use a simple subquery:
A subquery is a SELECT statement that is nested in a SELECT statement
SYS as [email protected]>select first_name,last_name,salary from HR. EMPLOYEES WHERE department_id= (SELECT department_id from HR. Departments WHERE department_name= ' Marketing ');

Find all employees below the average salary:
SYS as [email protected]>select first_name,salary from HR. EMPLOYEES WHERE salary< (SELECT AVG (SALARY) from HR. EMPLOYEES);

In keyword and subquery that returns multiple values
SYS as [email protected]>select first_name,last_name,salary from HR. EMPLOYEES WHERE department_id in (SELECT department_id from HR. Departments WHERE department_name= ' Marketing ' OR department_name= ' Purchasing ');


Merge query that uses the Union keyword:
Combine the results of two or more queries into a single result set that contains all the rows of all queries in a union query and automatically filters for duplicate records
SYS as [email protected]>select department_id,department_name from HR. Departments
UNION SELECT department_id,first_name from HR. EMPLOYEES WHERE salary>1000;
UNION All: Duplicate records will not be filtered

Use the Decode function in the SELECT statement:
Compares the input value to the function's argument list, returns a corresponding value based on the input value, and returns the default value if there is no corresponding value in the argument list
DECODE (< input value >,< value 1>,< result 1>[,< value 2>,< result 2>][,< default value;])
SYS as [email protected]>select first_name,decode (SALARY, ' 2600 ', ' low ', ' 10000 ', ' High ', ' Very_low ') as SALARY from HR. EMPLOYEES;

Using the case function in the SELECT statement (same as the function of the Decode function)
case< input values >WHEN< values 1>then< results 1>
[when< value 2>then< result 2>]
[else< default result;]
END
SYS as [email protected]>select first_name,case SALARY when 2600 then ' low ' when 10000 then ' High ' ELSE ' Very_low ' END As SALARY from HR. EMPLOYEES;
Case
when< logical Expression 1>then< result 1>
[when< logical expression 2>then< result 2>]
[else< default result;]
END

SELECT first_name,case when salary<=2000 then "low" when salary>=3000 and salary<=10000 then ' High ' ELSE ' Very_lo W ' END as SALARY from HR. EMPLOYEES;

To save the query results:
Save query results to a new table
CREATE table < new table name > as
<select clauses >
<from clauses >
<where clauses >
SYS as [email protected]>create TABLE HR. Emp_user as SELECT first_name,last_name,salary from HR. EMPLOYEES;
SYS as [email Protected]>desc HR. Emp_user;
Name Null? Type
----------------------------------------------------------------- -------- -------------------------------------- ------
First_Name VARCHAR2 (20)
Last_Name not NULL VARCHAR2 (25)
SALARY Number (8,2)

Common Data Manipulation language