Getting started with basic SQL 0 students

Data from "SQL Must know"

A Retrieving data

SELECT

L Retrieving a single column

Select column from table;

L Retrieving multiple columns

Select column, column from table;

L Retrieve all columns

Select * FROM table;

Two Sort Retrieve data

L Sort

Select column from table order by column;

L Sort by multiple columns

Select column, column,... from table order by condition A, condition B;

Sort by condition a first, then row b

L Sort By Column position

Select column from Table order by 2, 3;

Sort by second and third columns

L Sort by specified position

Desc Descending, ASC ascending

Select column from table order by column DESC, column,...

Three Filtering data

L USE the WHERE clause

Select column from table where condition

Operator	Describe
=	Equals
<>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
Between	Within a range
Like	Search for a pattern
!=	Not equal to
!<	Not less than
!>	No greater than
Is NULL	is a null value

L Check Individual values

Select column from table where column operator value

L mismatch Check

Select column from table where column is not equal to condition

L Range Value Check

Select column from table where column between value and value

L NULL value Check

Select column from table where column is NULL;

Four Advanced Data filtering

L Combine where words

L operator

L and

Select column from table where column condition and column conditions

L OR

Select column from table where column condition or column condition

Combination of L and and OR

SQL takes precedence over the and operator before processing the OR operator, and if the or is to be treated first, enclose it in parentheses.

L in

The in operator is used to specify a range of conditions in which each condition can be matched.

Select column from table where column in (condition, condition,...)

Strings remember to enclose them in single quotes

L not

Negate any conditions that are followed by not.

Select column from table where not column condition

Five Filtering with wildcard characters

Wildcard: A special numeric character used to match a portion of a value

Wildcard searches can only be used with text fields.

Note: Depending on the DBMS and its configuration, the search can be case-sensitive.

L Like operator

L percent sign (%) wildcard character

% indicates any number of occurrences of any character

Select column from table where column like '% text% ';

L underscore (_) wildcard characters

Use the same as%, but the underscore matches only a single character

L square brackets ([]) wildcard characters

Specifies a character set that must match one of the characters at the specified position (the position of the wildcard character)

For example, in order to find all contacts whose names start with J or M

Select column from table where column like ' [jm]% '

This wildcard character can be used to negate a

Six Create a calculated field

L Stitching Fields

In a SELECT statement in SQL, you can use a special operator to stitch together two columns: a plus or two vertical bar.

P.S. I can only use | | In Oracle, I don't know if I have the wrong call.

Removing spaces can be done using the SQL RTrim () function

P.S Why I did not add RTrim and no space, and the book is not the same ~

L Use aliases

A column alias is an alternate name for a field or value

L Perform arithmetic calculations

Select Calculation formula as alias from table where condition

Seven Using functions

L Text Processing function

UPPER () converts text to uppercase

Select column, Upper (column) as Alias from table order by column

Common Text Processing functions

Left ()	Returns the character to the left of the string
LENGTH ()	Returns the length of a string
LOWER ()	Convert a string to lowercase
LTRIM ()	Remove the left space of the string
Right ()	Returns the character to the right of the string
RTRIM ()	Remove the blanks to the right of the string
SOUNDEX	Returns the Soundex value of a string
UPPER ()	Convert a string to uppercase

Soundex is an algorithm that converts any text string into an alphanumeric pattern that describes its speech identity

EXAMPLE:

Select column from table where SOUNDEX (column) =soundex (' Michael Green ')

It matches all of the contact names that sound similar to Michael Green.

L Date and time processing functions

DATEPART (): Returns a portion of a date

Retrieve all orders for 2004

Select column from table where DATEPART (yy, column) = 2004; Year All orders

Note: Oracle does not have the DATEPART () function

DD-MMM-YYYY format dates are generally handled correctly by Oracle

Select column from table where To_number (To_char (order_date, ' YY ')) = 2004;

To_number () is used to convert extracted components into numerical values.

The To_char () function is used to extract the components of the date

Select column from table where order_date between to_date (' 01-jan-2004 ') and to_date (' 31-dec-2004 ');

L Numeric processing function

ABS ()	Absolute
COS ()	Cosine
EXP ()	Exponential value
PI ()	Pi
SIN ()	Sinusoidal
SQRT	Square root
TAN	Tangent

Eight Summarize data

L SQL Aggregation function

AVG ()	Average
COUNT ()	Number of rows
MAX ()	Maximum Value
MIN ()	Minimum value
SUM ()	The sum of a column

AVG ()

Select AVG (column) as Alias from table;

COUNT

Select COUNT (column) as Alias from table;

A few other similar, do not repeat.

L Aggregate different values

Select AVG (DISTINCT column) as Alias from table where condition

L Combined Aggregation function

Select COUNT (*) as Alias

Min (column name) as Alias

Max (column name) as Alias

AVG (column name) as Alias from table;

Nine Grouped data

L Data Grouping

Select column, COUNT (*) as Alias from table group by to group by column;

L Filter Grouping

Select column, COUNT (*) as Alias from table group by to be grouped by column having count (*) condition

The difference between having and where: the Where is filtered before the data is grouped, having a filter after the data is grouped.

L Grouping and sorting

The Select column, COUNT (*) as Alias from table group by to have the count (*) Condition of the column as a group by the Order by column;

L Select sentence Order

Select

From

where

GROUP BY

Having

ORDER BY

10. Using sub-queries

L Filtering with subqueries

If the information is stored in a number of tables

Select column from table where column in (select column from table where condition)

In a SELECT statement, subqueries are always handled from inside out.

l use subqueries as calculated fields

Select column, column, (SELECT COUNT (*) from column where condition) as Alias from table order by column;

11. Junction table

One of the most powerful features of SQL is the ability to join tables in the execution of data queries.

If the data is stored in more than one table, how can I retrieve the data with a single SELECT statement? The answer is to use junctions.

• Create a junction

Select  from INNER JOIN  on condition;

The junction condition is given by a specific on clause rather than a WHERE clause.

Getting started with basic SQL 0 students