SQL Note 1:select and select Advanced Applications

T-SQL Note 1:select and select Advanced Applications

Summary of this chapter

1: Install AdventureWorks

2: Basic operators and expressions

3:between

4:like

5:escape

6:top

7:group by

7.1:group by all

7.2:having

8:select sentence Technology

8.1: Eliminate duplicate values with distinct

8.2: Returns the result of stitching

8.3 using into words

9: Sub-query

9.1: Sub-query type

9.2: A query instead of an expression

  9.3: Multilayer Nesting

10: Compare queries using EXISTS and in

11: Join

  11.1: Using derived tables

11.2:union

12:tablesample

13: Common-table Expression common_table_expression

1: Install AdventureWorks

This series of notes is based on the AdventureWorks database, and the installation help for AdventureWorks is as follows:

On the Management Studio toolbar, click File, point to Open, and then click File.

Browse to the file instawdb.sql, and click Open. The default location for the file is C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP.

Before running the script, locate the statement in the script SET @data_path = @sql_path + ‘AWDB\‘; and change the statement to point to the location of the instawdb.sql script. For example, SET @data_path = ‘C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks OLTP\‘; .

Executes the script.

2: Basic operators and expressions

Operator	Describe
!=	Slightly
!>	Slightly
!<	Slightly
<	Slightly
<=	Slightly
<>	Slightly
=	Slightly
>	Slightly
>=	Slightly
All	Compares scalar values and values in a single column set.
Any	Compares scalar values and values in a single column set. SOME and any are equivalent
Between	Automatically value based on type of SQL
CONTAINS	Perform a fuzzy search for a word or phrase
ESCAPE	Specifies that you want to search in literal form instead of being interpreted as a wildcard character
EXISTS	Specifies a subquery that tests whether a row exists
FREETEXT	Search for words in data based on meaning rather than literal values
Inch	WHERE color in (' Red ', ' blue ')
is not NULL	Slightly
Is NULL	Detecting null values
Like	Pattern matching based on wildcard characters
Not between	Slightly
Not in	Slightly
Not-like	Slightly
SOME	Compares scalar values and values in a single column set. SOME and any are equivalent

3:between

Use:

Select SalesOrderID, shipdate from Sales.SalesOrderHeader
where shipdate between ' 7/28/2002 ' and ' 7/29/2002 '

Result: 17 statements will be returned.

Maybe someone will use it, using:

where ' 7/28/2002 ' < shipdate and shipdate < ' 7/29/2002 '

Can't you? The answer is no. The result will return 0.

4:like

The following wildcard characters exist,

%:0~n an arbitrary character;

_:1 characters;

[]: Specify any single character in the range or list;

[^]: Specifies no longer any single character in the range;

5:escape

Where name like ' b/b% ' escape '/'

Parse: Represents all names beginning with ' b/b ', where/not understood as wildcards.

6:top

Top allows you to query the starting N rows based on the number or percentage of rows defined. Such as:

Select Top Ten from ...

Or:

DECLARE @percentage float

Set @percentage =1

Select Top (@percentage) percent * from Sales.SalesOrderHeader

7:group by

Specifies the group to place the output rows. If the SELECT clause <select list> contains aggregate functions, GROUP by calculates the rollup values for each group.

The above sentence is not well understood, and a better understanding should be interpreted as:

" because an aggregate function is used in a select sentence, the columns that are not aggregated must appear in the GROUP BY clause. "

Select OrderDate, sum (totalDue) totalduebyorderdate from Sales.SalesOrderHeader
where shipdate between ' 7/28/2002 ' and ' 7/29/2002 '
GROUP BY OrderDate

Results:

(2 rows affected)

7.1:group by All

In the above code, add all, i.e.:

Select OrderDate, sum (totalDue) totalduebyorderdate from Sales.SalesOrderHeader
where shipdate between ' 7/28/2002 ' and ' 7/29/2002 '
--group by OrderDate
GROUP BY all OrderDate

Results:

Warning:null value is eliminated by a aggregate or other SET operation.

(1124 rows affected)

This description: All contains all the groups and result sets, even those that contain any of those rows that do not meet the search criteria specified by the WHERE clause. If all is specified, a null value is returned for the summary column in the group that does not meet the search criteria.

7.2:having

Specifies the search criteria for a group or aggregation. Having can only be used with the SELECT statement. The having is usually used in the GROUP by clause. If you do not use the GROUP by clause, you have the same behavior as the WHERE clause.

It is equivalent to retrieving the contents of a query before the group.

The following example uses HAVING a simple clause to SalesOrderDetail retrieve a total of more than one from $100000.00 a table SalesOrderID .

SELECT SalesOrderID, SUM (LineTotal) as SubTotal
From Sales.SalesOrderDetail
--where ModifiedDate between ' 7/28/2002 ' and ' 7/29/2002 '
GROUP by SalesOrderID
Having SUM (LineTotal) > 100000.00
--having SalesOrderID = 43875
--having ModifiedDate between ' 7/28/2002 ' and ' 7/29/2002 '--error
ORDER by SalesOrderID;

8:select sentence Technology

Select sentence technology has many, in addition to the simplest splicing, and so on, the following describes the most useful for individuals.

8.1: Eliminate duplicate values with distinct

SELECT * FROM HumanResources.Employee

Result: (290 rows affected)
Select DISTINCT HireDate from HumanResources.Employee

Result: (164 rows affected)

The explanation already repeats the cancellation.

8.2: Returns the result of stitching

DECLARE @Shifts varchar (20)
SET @Shifts = ' '
SELECT @Shifts = @Shifts + s.name + ', ' from Humanresources.shift s
SELECT @Shifts

The results returned are: Day,evening,night,

This is a great benefit for us to handle simple queries and improve efficiency.

8.3 using into words

The INTO clause is used to create a new table (for me, backup data).

A typical usage is to copy data to a new table (the new table can be created as a permanent table, temporary table, or global temporary table), as in the following code:

SELECT CustomerID, Name, SalesPersonID, Demographics
Into store_archive
From Sales.Store

Result: (701 rows affected)

Description, 1: A new table was created store_archive,2:701 rows of data were copied to store_archive.

Of course, if you just want to create a new table and don't want to copy any data, there's a neat way to do this:

SELECT CustomerID, Name, SalesPersonID, Demographics
Into store_archive
From Sales.Store
WHERE 1=0

9: Sub-query

A subquery is a query that is nested within a SELECT, INSERT, UPDATE, or DELETE statement or other subquery. Subqueries can be used anywhere that an expression is allowed to be used.

A join can always be represented as a subquery. Subqueries are often (but not always) represented as joins. This is because joins are symmetric: the same results will be obtained regardless of the order in which tables A and B are joined. For subqueries, this is not the case.

One difference between using joins instead of using subqueries to handle this problem and similar problems is that joins enable you to display columns from multiple tables in the results. For example, if you want to include the name of a product subcategory in the results, you must use a joined version.

9.1: Sub-query type

Subqueries can be specified in many places (all must be mastered):

• Use aliases. For more information, see sub-queries that use aliases.
• Use in or not in. For more information, see Subqueries using in and subqueries with not in.
• In the UPDATE, DELETE, and INSERT statements. For more information, see sub-queries in UPDATE, DELETE, and INSERT statements.
• Use the comparison operator. For more information, see sub-queries using comparison operators.
• Use any, SOME, or all. For more information, see the comparison operator modified with any, SOME, or all.
• Use EXISTS or not EXISTS. For more information, see Subqueries that use EXISTS and subqueries that use not EXISTS.
• Instead of an expression. For more information, see Subqueries for alternative expressions.

9.2: A query instead of an expression

It is important to focus on subqueries that replace expressions. In Transact-SQL, in addition to the ORDER by list, any use of an expression in SELECT, UPDATE, INSERT, and DELETE statements can be overridden by a subquery.

The following example shows how to use this enhancement. This query finds out the price, average price, and the difference between all mountain bike products.

Use AdventureWorks; Goselect Name, ListPrice, (select AVG (ListPrice) from production.product) as Average,     ListPrice-(select AVG (listpri CE) from production.product) as    differencefrom production.productwhere Productsubcategoryid = 1

9.3: Multilayer Nesting

The subquery itself can include one or more child queries. You can nest any number of subqueries in a single statement.

The following query finds the name of the employee who is the salesperson.

Use AdventureWorks; Goselect LastName, Firstnamefrom person.contactwhere ContactID in    (SELECT ContactID     from HumanResources.Employee     WHERE EmployeeID in        (SELECT SalesPersonID from         Sales.SalesPerson)

10: Compare queries using EXISTS and in

The following example compares two semantically equivalent queries. The first query EXISTS is used, and the second query is used IN .

Use AdventureWorks;  Goselect A.firstname, A.lastnamefrom person.contact as Awhere EXISTS (SELECT * from  HumanResources.Employee as B WHERE A.contactid = B.contactid and a.lastname = ' Johnson '); GO

The following query uses the IN .

Use AdventureWorks; Goselect A.firstname, A.lastnamefrom person.contact as Awhere a.lastname in (SELECT a.lastname from HumanResources.Employee as b WHERE a.contactid = B.contactid and a.lastname = ' Johnson '); GO

The following is the result set of any of these queries.

FirstName                                          LastName------------------------------------------------------------Barry                                              johnsondavid                                              Johnsonwillis                                             Johnson (3 row (s) affected)

11: Join

   Joins let you retrieve data from two or more tables based on the logical relationships between the tables.

   The columns used in the join condition do not have to have the same name or the same data type. However, if the data types are different, they must be compatible, or types that can be implicitly converted by SQL Server.

Joins can be divided into the following categories:

• An inner join (a typical join operation, using a comparison operator similar to = or <>). Inner joins include equal joins and natural joins.
  An inner JOIN uses comparison operators to match the rows in two tables based on the values in the universal columns of each table. For example, retrieve all lines of the students and courses table with the same student identification number.
• Outer Joins. An outer join can be a left outer join, a right outer join, or a full outer join.
  You can specify an outer join in the FROM clause by using one of the following sets of keywords:
  • Left JOIN or left OUTER join.
    The result set for a left outer join includes all rows from the left table specified in the OUTER clause, not just the rows that match the joined columns. If a row in the left table does not have a matching row in the right table, all select list columns from the right table are null values in the associated result set row.
  • Right join or right OUTER join
    A right outer join is a reverse join of a left outer join. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value is returned for left table.
  • Full join or full OUTER join
    A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column of the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.
• Cross Join
  A cross join returns all the rows in the left table. Each row in the left table is combined with all rows in the right table. Cross joins are also called Cartesian product.

11.1: Using derived tables

A derived table is a SELECT statement that is used as a table in the FROM clause.

SELECT DISTINCT S.purchaseordernumber
From Sales.SalesOrderHeader S
INNER JOIN (SELECT SalesOrderID
From Sales.SalesOrderDetail
WHERE UnitPrice between) d on
S.salesorderid = D.salesorderid

11.2:union

   Merges the results of two or more queries into a single result set that contains all the rows of all queries in the union query.

The following is a list of the basic rules for merging two query result sets using UNION:

• The number of columns and the order of the columns must be the same in all queries.
• The data type must be compatible.

Select ProductModelID, Name from Production.ProductModel WHERE ProductModelID not in (3, 4) UNION SELECT ProductModelID, N Ame from dbo. Gloves ORDER by Name;

12:tablesample

The TABLESAMPLE clause restricts the number of rows returned from the table in the FROM clause to a percentage of the number of samples or rows. For example:

SELECT FirstName, LastName from Person.person tablesample (PERCENT);

SELECT FirstName, LastName from Person.person tablesample (ROWS);

13: Common-table Expression common_table_expression

Specifies a temporary named result set, which is called a common table expression (CTE). The expression originates from a simple query and is defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. This clause can also be used in the CREATE VIEW statement as part of the statement's SELECT definition statement. A common table expression can include a reference to itself. This type of expression is called a recursive common table expression.

Use AdventureWorks;
GO
With Dirreps (ManagerID, Directreports) as
(
SELECT ManagerID, COUNT (*)
From HumanResources.Employee as E
WHERE ManagerID is not NULL
GROUP by ManagerID
)
SELECT ManagerID, Directreports
From Dirreps
ORDER by ManagerID;
GO

SQL Note 1:select and select Advanced Apps