SQL Server 2000 Syntax

4.            SQL Syntax

SQL (Structured Query Language) has four main commands for manipulating data:

·                  SELECT existing data from one or more tables.

·                  INSERT a new record into a table.

·                  DELETE one or more records from a table.

·                  UPDATE existing records in a table.

This section explains these commands with particular reference to the flexibility of the SELECT command. The Query Analyser available from the TOOLS menu of the SQL Executive should be used to run the examples against the PUBS database.

pubs

The pubs database is installed together with SQL Server and is used in most of the examples in this book. This small database contains data referring to book publishers and the titles they publish alongside details of the authors that write the titles and the stores that sell them.

A SQL batch program called INSTPUBS.SQL is installed with SQL Server to allow the reinstallation of a fresh PUBS database for training purposes. Please ask your system manager to install a new PUBS database if required.

SELECT Statement

The SQL SELECT statement is used to select a set of data from existing tables in the database. The syntax of the command is designed to define a set of data which includes the fields (columns) and the set of records (rows) which should be selected.

The structure of the command is as follows:

SELECT [ALL | DISTINCT]

   [TOP n [PERCENT] [WITH TIES]] 

   select_list

[INTO [new_table_name]]

[FROM {table_name | view_name}[(optimizer_hints)] [ ,...n ]]

[WHERE clause]

[GROUP BY [ALL] expression [ ,...n ] [WITH {CUBE | ROLLUP}]]

[HAVING clause]

[ORDER BY clause]

[COMPUTE clause]

[FOR XML [ AUTO | RAW | EXPLICIT ]

Field List

The select list is the list of fields or expressions that are required in the selected table. These correspond to the fields in the result set:

SELECT au_fname, au_lname FROM authors

The asterisk can also be used to select all fields from a table.

SELECT * FROM authors

An alias can be given to the field name to rename the field in the result table:

SELECT au_lname AS surname, au_fname AS firstname FROM authors

Expressions can also be specified for a field expression:

SELECT au_lname + au_fname AS fullname FROM authors

Expressions can be used in the select list:

SELECT s.*, t.price, qty * price AS qtyprice

   FROM sales s

   INNER JOIN titles t ON s.title_id = t.title_id

Scalar functions can be applied to fields or expressions for more complex queries:

SELECT UPPER(au_lname), CAST(address + ',' + city + ',' +

   state + space(1) + zip as varchar(45))

   FROM authors

WHERE Clause

The WHERE clause is used to narrow down the rows selected for the result table.

SELECT * FROM authors WHERE au_lname = 'White'

AND and OR and NOT can be used:

SELECT * FROM authors

   WHERE (state = 'CA' or state = 'UT')

   AND (NOT contract = 1)

The IN clause can be used instead of OR:

SELECT * FROM authors

   WHERE state IN ('CA','UT')

The BETWEEN syntax can be used also to select between given values:

SELECT * FROM titles WHERE price BEWTEEN 10.00 AND 29.00

NULL values can be identified in a WHERE clause:

SELECT * FROM stores WHERE zip IS NULL

The TOP n clause can be used to limit the number of records returned from  a SELECT command.

Wild Cards

Wild cards can be used in selection criteria, for example to select any AUTHORS containing the letter 'a' in the surname:

SELECT * FROM authors WHERE au_lname LIKE '%A%*'

The LIKE syntax allows a wide variety of pattern matching templates. The above example utilises the % character as a wild card symbolising any sequence of characters. Angle brackets are used to define a range of characters. The following example to picks out book titles with an identifier beginning with a character in the range B to M:

SELECT * FROM titles WHERE title_id LIKE '[B-M]%'

The underscore character indicates any single character, # any single digit, angle brackets picks any single character within the brackets, and [^] will pick any character not within the angle brackets. This example selects Titles which do not have P or M as the first letter of the identifier and have 1 as the third character:

SELECT * FROM titles

   WHERE title_id LIKE '[^PM]_1%'

An escape character can be defined to allow one of the wild cards to be used as a literal in the expression. This example finds any occurrence of the % character in the PAYTERMS field of the STORES table:

SELECT * from sales

   WHERE payterms LIKE '%\%%' ESCAPE '\'

SQL Server is often configured not to be case sensitive. You may need to check this option or use expressions of the form WHERE UPPER(au_lname) = 'SMITH'.

FROM Clause

The FROM clause specifies which tables are involved in the SELECT statement. The clause is mandatory:

SELECT * FROM authors

If more than one table is required in the query then they may be separated by commas:

SELECT * FROM titleauthor, authors, titles

   WHERE titleauthor.au_id = authors.au_id AND

   titleauthor.title_id = titles.title_id

The tables may be assigned an alias if required to shorten or provide an alternative name in the statement:

SELECT * FROM titleauthor ta, authors a, titles t

   WHERE ta.au_id = a.au_id AND

   ta.title_id = t.title_id

The alias name also allows for a recursive query that uses the same table twice to show an employees manager for example:

SELECT employee.surname, manager.surname ;

   FROM employee, employee manager ;

   WHERE employee.manager = manager.id

SQL Server uses a fully qualified name to identify a table. There are four parts to the fully qualified name: server.database.owner.table. The FROM clause can specify a table that is located in another database or even another table:

USE northwind

SELECT * FROM pubs.dbo.authors

ORDER BY

The ORDER BY clause allows for a result table to be ordered in any desired sequence:

SELECT * FROM authors ORDER BY au_lname, au_fname

The order sequence may also refer to the output fields in the result table using a number indicating the sequence of the field in the select list:

SELECT au_lname, au_fname FROM authors ORDER BY 2,1

The DESC keyword may be used to reverse the sort order of a column in the ORDER part of the SELECT statement:

SELECT title, ytd_sales, type as category

   FROM titles

   WHERE type = 'business'

   ORDER BY 2 DESC

Natural Join

WHERE clauses are often used for joining tables together using the primary and foreign keys. Relational databases allow for any two tables to be joined together with an expression in the first table matching any expression in the second table as long as the width and data type of the expression is identical.

Joins allow considerable flexibility to the programmer in joining tables together although, nearly always, the programmer will want to join one table to another using the foreign and primary keys.

SELECT * FROM titleauthor ta, authors a, titles t

   WHERE ta.au_id = a.au_id AND

   ta.title_id = t.title_id

The more modern syntax for a natural join is to use the INNER JOIN syntax as follows:

SELECT * FROM titleauthor ta

   INNER JOIN authors a ON ta.au_id = a.au_id

   INNER JOIN titles t ON ta.title_id = t.title_id

Specifying two tables in a SELECT statement without specifying a join condition will create a Cartesian product of both tables. This means that a 100 record table joined to a 200 record table with no WHERE clause will create a 20,000 record result table.

GROUP BY Clause

The GROUP BY command can be used with the aggregate functions to count up the number of occurrences of a value or to summate, average or perform statistical calculations on a table:

SELECT title_id, SUM(qty) AS totalsales 

   FROM sales  GROUP BY title_id

SELECT title_id, COUNT(*) FROM sales GROUP BY title_id

The following example shows the maximum, minimum and average order level for each title together with the total number of records for each title and a count of the number of different stores ordering the title:

SELECT title_id, COUNT(*) AS ordercount,

   COUNT(stor_id) AS storecount,

   MAX(qty) AS maxqty,

   MIN(qty) AS minqty,

   AVG(qty) AS avgqty,

   SUM(qty) AS sumqty

   FROM sales

   GROUP BY title_id

The ALL keyword can be used to include all the groupings present in the table even if there are no occurrences selected in the query. The aggregated fields for the additional groups are set as NULL values:

SELECT title_id, SUM(qty) AS totalsales 

   FROM sales

   WHERE YEAR(ord_date) = 1994

   GROUP BY ALL title_id

The CUBE and ROLLUP options on the GROUP BY command are specific to SQL Server and add additional summary records into the selection. This can help in creating results sets for complex management report.

Another aggregation command is the COMPUTE BY clause and remember that the SQL OLAP manager provides full management reporting facilities.

HAVING Clause

The WHERE clause filters the rows that are used in the query. The HAVING clause operates on a query that employs a GROUP BY clause but only after the grouping has been performed.

This allows the summary records to be selected on the basis of their aggregated values. The procedure is similar to performing a second WHERE selection on the final results table.

The following statement selects titles that have sold more than 50 copies:

SELECT title_id, COUNT(*) AS ordcount,

   FROM sales

   GROUP BY title_id

   HAVING ordcount > 50

DISTINCT

The DISTINCT clause is not often used but may be used to prevent duplicate rows from appearing in the results table.

The following command creates  a results table with one record for each Title Type in the TITLES table:

SELECT DISTINCT titles.type FROM titles

A similar result may be obtained with the GROUP BY clause.

Inner (Natural) Join

A natural join is the operation that joins tables together using a where clause or the more modern INNER JOIN syntax. The following statement will create a view that joins the TITLES and SALES  tables:

SELECT t.title_id, t.title, SUM(s.qty) AS totalqty

   FROM titles t

   INNER JOIN sales s ON t.title_id = s.title_id

   GROUP BY t.title_id, t.title

A natural, or inner, Join discussed above will only display records that qualify the join condition and that occur in both tables. A title that has no sales will not be included in the view.

Outer Join

An outer join allows for records to be displayed from either table even if there is no corresponding record on one or other side of the join. The Outer Join may be a left or right outer join depending on whether all the records in the first or the second table are required. A full outer join will include all records from both tables.

The syntax for a left outer join between the TITLES and the SALES tables allows all TITLES to be displayed:

SELECT t.title_id, t.title, SUM(s.qty) AS totalqty

   FROM titles t

   LEFT OUTER JOIN sales s ON t.title_id = s.title_id

   GROUP BY t.title_id, t.title

Missing values where there are no corresponding SALES records for a Title are represented as NULL values.

A LEFT outer join includes all records from the table mentioned in the FROM clause, the RIGHT outer join includes all records from the joined table, and a FULL outer join includes all the records from both tables.

Sub Queries

Subqueries can be useful in creating views with complex selection criteria. The following example could be expressed as a normal JOIN and GROUP BY but is more clearly expressed as follows:

SELECT * FROM titles WHERE title_id IN

( SELECT title_id from sales 

   GROUP BY title_id

   HAVING SUM(qty)> 25  )

Subselections are particularly useful when working with views or temporary sets of data and can be used to check if records are in or not in another table

SELECT * FROM titles t

   WHERE title_id NOT IN 

   (SELECT title_id FROM sales s

   WHERE t.title_id = s.title_id )

This same query could use the less specific NOT EXISTS clause:

SELECT * FROM titles t

   WHERE NOT EXISTS 

   (SELECT title_id FROM sales s

   WHERE t.title_id = s.title_id )

A join may always be expressed as a subquery

UNION

The UNION command can be used to create a single view from two tables with a similar structure. The following example creates a single table from the authors and employee tables:

SELECT

   a.au_id AS cid, a.au_lname AS lastname, a.au_fname AS fname

   FROM authors a

   UNION

   (SELECT e.emp_id AS cid, e.fname AS firstname,

   e.lname AS lastname FROM employee e)

Duplicates are removed from the resulting query unless the UNION ALL keyword is specified.

Care needs to be taken where the table structures are not identical The CAST or CONVERT scalar functions can be used to change a data type in a SELECT statement.

FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64]

SQL Server 2000 allows for rapid creation of XML from standard select statements. This is useful in creating components that use XML to communicate information:

SELECT * FROM authors

FOR XML AUTO

The modes are as follows:

·                  AUTO defines an element with the same name as the table for each record and represents fields at attributes.

·                  RAW uses an element names <row> instead of the element named after the table.

·                  EXPLICIT allows precise definition of the XML tree.

XMLDATA specifies full data type information using an external schema.

The ELEMENTS clause is used together with AUTO to include the columns of the select statement as sub-elements instead of attributes in the XML.

SELECT * FROM authors FOR XML AUTO, XMLDATA, ELEMENTS

SQL Server 2000 has additional commands that allow a stored procedure to read an XML file (OPENXML).

SELECT .. INTO

A new table can be created with a SELECT INTO command provided that the user has CREATE TABLE permissions on the database.

SELECT *

   INTO contractauthor

   FROM authors

   WHERE contract = 1

INSERT Statement

The INSERT statement allows new records to be added into a table:

INSERT [INTO]

   {table_name | view_name} [(column_list)]

   {DEFAULT VALUES | values_list | select_statement}

The INSERT statement requires that the values satisfy any validation constraints specified on the table otherwise the transaction will fail.

INSERT INTO authors

   (au_id, au_lname, au_fname, contract )

   VALUES ( "999-99-9001", "Crook", "Stamati", 1 )

There are constraints defined on the Authors table that will prevent a new record from being added if the identifier is not unique or if the firstname, lastname, or contract field values are not specified.

The INSERT command may also be used in combination with a SELECT statement to add records into a table:

INSERT INTO bestseller

   ( title_id, qty )

   SELECT title_id, SUM(qty) FROM sales s

   GROUP BY title_id

   HAVING SUM(qty) > 25

UPDATE Statement

The UPDATE statement allows values in existing records to be changed:

UPDATE {table_name | view_name}

SET [{table_name | view_name}]

   {column_list

   | variable_list

   | variable_and_column_list}

      [, {column_list2

         | variable_list2

         | variable_and_column_list2}

         ...   [, {column_listN

               | variable_listN

               | variable_and_column_listN}]]

[WHERE clause]

The Update clause can be used to update any field and usually involves a WHERE clause. Take care to specify the WHERE clause carefully or all the records will be updated:

UPDATE authors

   SET au_lname = 'Crank',

      au_fname = 'Stanley'         

   WHERE au_lname = 'Crook'

The WHERE clause is often used in conjunction with the Primary Key expression to update a single record in the table.

The UPDATE command can also set values into a table by making calculations using data from another table:

UPDATE sales

   SET qtyprice = qty * (SELECT price

      FROM titles t WHERE sales.title_id = t.title_id )

DELETE Statement

The DELETE statement allows for deletion of table records using a WHERE clause to specify the records for deletion:

DELETE [FROM] {table_name | view_name}

   [WHERE clause]

The DELETE statement must satisfy any referential integrity constraints set up in the database before records are deleted:

DELETE authors

   WHERE au_lname = 'Crook'

The TRUNCATE TABLE authors command could be used to delete all the records in the table. Take care to backup after such a command because a Truncated Delete is not logged.