SQL Basics (2)

1. SQL TOP

(1) TOP clause

• The OP 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.

(2) syntax for SQL Server

• SELECT TOP number|percent column_name (s) from table_name

(3) Example

• SELECT TOP 2 * from Persons

• SELECT TOP PERCENT * from Persons

2.SQL like

(1) Like operator

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

(2) syntax

• SELECT column_name (s) from table_name WHERE column_name like pattern

(3) Example

• SELECT * from Persons WHERE city like ' N% '

• SELECT * from Persons WHERE city like '%g '

• SELECT * from Persons WHERE city like '%lon% '

• SELECT * from Persons WHERE city is not like '%lon% '

3.SQL wildcard characters

(1) 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:

Wildcard characters	Describe
%	Override One or more characters
_	replaces only one character
[Charlist]	any single character of the word columns
[^charlist] Or [!charlist]	Any single character that is not in the word columns

(2) Use% wildcard characters

• SELECT * from Persons wherecity like ' ne% '

• SELECT * from Persons wherecity like '%lond% '

(3) Using _ wildcard characters

• SELECT * from Persons wherefirstnamelike ' _eorge '

• SELECT * from Persons wherelastnamelike ' C_r_er '

(4) using [charlist] wildcard characters

• SELECT * from Persons wherecity like ' [aln]% '

• SELECT * from Persons wherecity like ' [! aln]% '

4.SQL in

(1) in operator

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

(2) syntax

• SELECT column_name (s) from table_name WHERE column_name in (value1,value2,...)

(3) Example

• SELECT * from Persons WHERE LastName in (' Adams ', ' Carter ')

5.SQL between

(1) between operator

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

(2) syntax

• SELECT column_name (s) from table_name WHERE column_name between value1 and value2

(3) Example

• SELECT * from Persons WHERE LastName between ' Adams ' and ' Carter '

• SELECT * from Persons WHERE LastName not between ' Adams ' and ' Carter '

(4) Important matters

• Different databases for between ... The and operator is handled differently.

6.SQL Aliases

(1) SQL Aliases (alias)

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

(2) syntax

• SQL Alias syntax for table:

  SELECT column_name (s) from table_name Asalias_name

• SQL Alias syntax for columns:

SELECT column_name as Alias_namefromtable_name

(3) Example

• SELECT LastName as Family, FirstName asname from Persons

7.SQL JOIN

(1) SQL JOIN

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

(2) 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 (PrimaryKey) 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.

(3) referencing two tables

• SELECT Persons.lastname,persons.firstname, Orders.orderno frompersons, Orders WHERE persons.id_p = orders.id_p

(4) Using Join

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

• Note: INNER join is the same as join.

(5) 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

8.SQL INNER JOIN

(1) SQL INNER join keyword

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

(2) INNER JOIN keyword syntax

• SELECT column_name (s) from Table_name1inner JOIN table_name2 on Table_name1.column_name=table_name2.column_name

(3) Example

• SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom personsinner JOIN Orderson persons.id_p=orders.id_ Porder by Persons.lastname

• The INNER JOIN keyword returns a row when there is at least one match in the table. If there is no match, the rows are not listed.

9.SQL left JOIN

(1) 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).

(2) Left JOIN keyword syntax

• SELECT column_name (s) from Table_name1left 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.

(3) Example

• SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom personsleft JOIN Orderson persons.id_p=orders.id_ Porder 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).

10.SQL Right JOIN

(1) 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).

(2) SQL right join keyword syntax

• SELECT column_name (s) from Table_name1right JOIN table_name2 on Table_name1.column_name=table_name2.column_name

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

(3) Example

• SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom personsright JOIN Orderson persons.id_p=orders.id_ Porder 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 Basics (2)