SQL Advanced Tutorial 1

SQL TOP clause: TOP clause

The TOP clause is used to specify the number of records to return.

The TOP clause is useful for large tables with thousands of records.

Note: Not all database systems support the TOP clause.

SQL Server Syntax: SELECT TOP number|percent column_name (s) from table_name

Select the first two records in the Persons table: Select top2* from Persons/select TOP 50% from personssql like operator like operator

The LIKE operator is used to search for a specified pattern in a column in a WHERE clause.

SQL Like operator syntax select COLUMN_NAME (s) from the Table_namewhere column_name like pattern "Persons" table Select the person living in a city that begins with "N": SELECT * From Persons the WHERE city like ' N% ' "Persons" table selects people living in cities ending with "G": SELECT * from Personswhere urban like '%g ' "Persons" table Select Live People in cities with "lon": SELECT * from Personswhere city like '%lon% ' select people residing in cities that do not contain "lon" from the "Persons" table: SELECT * FROM Personswher E city isn't like '%lon% '

Note: "%" can be used to define wildcards (missing letters in a pattern)

SQL wildcard characters:

SQL wildcard characters

The SQL wildcard can override one or more characters when searching for data in a database.

SQL wildcard characters must be used with the LIKE operator.

In SQL, you can use the following wildcard characters:

Use the% wildcard: the "Persons" table selects people living in a city that begins with "Ne": SELECT * from Persons the where city like ' ne% ' "Persons" table select the person residing in the cities containing "Lond": SEL ECT * from Persons where city like '%lond% ' using _ Wildcard: "Persons" table in which the first character of the selected name is followed by the person "Eorge": SELECT * from Persons WHERE Primary Name like ' _eorge ' the last name of the record selected from the "Persons" table begins with "C", then an arbitrary character, then "R", then any character, followed by "ER": SELECT * from Persons
WHERE LastName like ' C_r_er ' uses the [charlist] wildcard: the "Persons" table selects the person living in the city that begins with "A" or "L" or "N": SELECT * from Persons
Where city like ' [aln]% ' "Persons" table to select the cities to live in not toPerson starting with "A" or "L" or "N": SELECT * from Persons
WHERE City is like ' [! aln]% ' SQL in operator: in operator

The in operator allows us to specify multiple values in the WHERE clause.

SQL in Syntax Select COLUMN_NAME (s) from Table_namewhere column_name in (value1,value2,...)

Select the person whose last name is Adams and Carter from the table above: SELECT * from Persons WHERE LastName in (' Adams ', ' Carter ') SQL between operator:

The between operator is used in the WHERE clause to select a range of data between two values.

Between operator

operator between ... and selects a range of data between two values. These values can be numeric, text, or date.

SQL between Syntax select COLUMN_NAME (s) from table_name WHERE column_name between value1 and value2

Show people in alphabetical order between "Adams" (included) and "Carter" (not included): SELECT * from Persons
WHERE LastName
BETWEEN' Adams ' AND' Carter ' SQL alias (alias):

By using SQL, you can specify aliases (alias) for column names and table names.

SQL alias table SQL alias syntax select COLUMN_NAME (s) from table_name as alias_name column SQL alias syntax select column_name as Alias_name From Table_namealias instance: using Table name aliases

Let's say we have two tables: "Persons" and "Product_orders", respectively. We assign them the alias "P" and "Po", respectively.

Now we want to list all orders for "John Adams".

We can use the following SELECT statement:

SELECT Po. OrderID, P.lastname, p.firstname from Persons AS p, Product_orders AS poWHERE p.lastname= ' Adams ' and p.firstname= ' John '

SELECT statements that do not use aliases:

SELECT Product_orders.orderid, Persons.lastname, persons.firstname from Persons, Product_orders WHERE persons.lastname = ' Adams ' and persons.firstname= ' John ' Alias instance: Use one of the column name aliases:

SELECT LastName AS Family, FirstName AS Name From Personssql JOIN

SQL join is used to query data from these tables based on the relationship between the columns in two or more tables.

Join and Key

Sometimes in order to get the complete result, we need to get the results from two or more tables. We need to execute the join.

Tables in the database can be linked by keys. The primary key (Primary key) is a column, and the value of each row in the column is unique. In the table, the value of each primary key is unique. The purpose of this is to cross-bind the data between tables without repeating all the data in each table.

The "id_o" column is the primary key in the Orders table, and the Id_p column in the Orders table is used to refer to people in the Persons table without using their exact names.

References two tables: SELECT persons.lastname, Persons.firstname, Orders.orderno
From Persons, Orders
WHERE persons.id_p = orders.id_p

SQL JOIN-Using Join:select persons.lastname, Persons.firstname, Orders.orderno
From Persons
INNER JOIN Orders
On persons.id_p = orders.id_p
ORDER by Persons.lastname

Different SQL joins:

In addition to the INNER join (inner join) we used in the example above, we can also use several other connections.

The following lists the JOIN types that you can use, and the differences between them.

• JOIN: Returns a row if there is at least one match in the table
• Left JOIN: Returns all rows from the table, even if there is no match in the right table
• Right JOIN: Returns all rows from the correct table even if there is no match in the left table
• Full JOIN: Returns a row if there is a match in one of the tables

SQL INNER JOIN keyword:SQL INNER JOIN keyword

The INNER JOIN keyword returns a row when there is at least one match in the table.

INNER Join keyword Syntax select column_name (s) from table_name1 INNER JOIN table_name2 on Table_name1.column_name=table_ Name2.column_name

Note: INNER join is the same as join.

INNER JOIN (INNER join) instance

Now, we want to list everyone's orders.

SELECT Persons.lastname, Persons.firstname, Orders.orderno
From Persons
INNER JOIN Orders
On persons.id_p=orders.id_p
ORDER by Persons.lastname

The INNER JOIN keyword returns a row when there is at least one match in the table. If the rows in "Persons" do not match in "Orders," the rows are not listed. SQL LEFT JOIN keyword: SQL LEFT JOIN keyword

The left JOIN keyword returns all rows from the table (TABLE_NAME1), even if there are no matching rows in the right table (table_name2).

Left JOIN keyword Syntax select column_name (s) from table_name1 left JOIN table_name2 on Table_name1.column_name=table_ Name2.column_name

Note: In some databases, the left join is called the left OUTER join.

Left JOIN connection (left JOIN) instance

Now we want to list all the people as well as their orders-if any.

SELECT Persons.lastname, Persons.firstname, Orders.orderno from Persons left JOIN Orders on persons.id_p=orders.id_p orde R by Persons.lastname

The left JOIN keyword returns all rows from the table (Persons), even if there are no matching rows in the right table (Orders). SQL RIGHT JOIN keyword SQL RIGHT join keyword

The right JOIN keyword returns all rows from the table (table_name2), even if there are no matching rows in the left table (table_name1).

Right JOIN keyword Syntax select column_name (s) from table_name1 right JOIN table_name2 on Table_name1.column_name=table_ Name2.column_name

Note: In some databases, the right join is called a OUTER join.

Right Join instance

Now, we want to list all the orders and the people who ordered them-if any.

SELECT Persons.lastname, Persons.firstname, Orders.orderno from Persons right JOIN Orders on persons.id_p=orders.id_p ORD ER by Persons.lastname

The right JOIN keyword returns all of the rows from the table (Orders), even if there are no matching rows in the left table (Persons). SQL full join keyword SQL full join keyword

The full JOIN keyword returns a row whenever there is a match in one of the tables.

Full join keyword Syntax select column_name (s) from table_name1 full join table_name2 on Table_name1.column_name=table_ Name2.column_name

Note: In some databases, full join is called full OUTER join.

Fully connected (full join) instances

Now, we want to list all the people, their orders, all the orders, and the people who ordered them.

SELECT Persons.lastname, Persons.firstname, Orders.orderno from Persons full JOIN Orders on persons.id_p=orders.id_p orde R by Persons.lastname

The full JOIN keyword returns all rows from the left table (Persons) and the right table (Orders). If the rows in "Persons" do not match in the table "orders", or if the rows in "orders" do not have a match in the table "Persons", the rows are also listed. SQL Union and UNION ALL operator SQL UNION operator

The UNION operator is used to combine the result set of two or more SELECT statements.

Note that the SELECT statement inside the UNION must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same.

SQL UNION Syntax Select column_name (s) from table_name1 UNION SELECT column_name (s) from table_name2

Note: By default, the UNION operator chooses a different value. If duplicate values are allowed, use UNION all.

SQL UNION All Syntax select COLUMN_NAME (s) from table_name1 UNION all SELECT column_name (s) from table_name2

In addition, the column name in the union result set is always equal to the column name in the first SELECT statement in the Union.

List all the different employee names in China and the United States:

SELECT E_name from Employees_china UNIONSELECT E_name from Employees_usa

Note: This command cannot list all employees in China and the United States. In the above example, we have two employees of the same name, and only one of them is listed. The UNION command will only pick a different value.

UNION All

The union ALL command is almost equivalent to the Union command, but the union ALL command lists all values.

SQL Statement 1 UNION all SQL Statement 2

List all employees in China and the United States:

SELECT E_name from Employees_china UNION ALLSELECT E_name from Employees_usa

SQL Advanced Tutorial 1