Sql Server

T-SQL

Now we will see how to write simple SQL queries. For understanding T-SQL and to use examples below use Query Analyzer in SQL Server

The Select Statement

One can use a SQL query to retrieve the data one wants from the database. A query has three parts: SELECT, FROM, and WHERE

• SELECT tells SQL Server which columns one want to see as the result of the query
• FROM specifies which table or tables you want to use in the query.
• WHERE limits which rows one want to see
• The syntax isn't case sensitive, but generally people use uppercase and lowercase to distinguish the commands from the object names

A typical query looks like this:

SELECT col1, col2, col3......
FROM table1
WHERE col1 = search condition

Consider this simple query:

USE pubs
GO
SELECT * FROM authors
GO

• The phrase "USE pubs" ensures that statement queries the pubs database. One could instead select the database in the DB window on the Toolbar
• GO ensures that SQL Server has completed the previous command before the next one starts
• This query will return all columns and rows in the authors table. Inside SQL Server * is turned into name of columns of the table

But one of the three elements-the WHERE clause-is missing from this statement. So if one uses this statement, SQL Server will return all the rows. A query without a WHERE clause can return a large amount of data, increasing network traffic

Warning: An update or delete statement without a WHERE clause to restrict the affected rows could be disastrous

Because the authors table in the pubs sample database is small, one can run this query without worry. Click on the green arrow or press CTRL-E. Image 1 shows the results. One needs to scroll the window to see all the columns and all the rows


Selecting Columns

If one wants to see only certain columns, one can restrict the output by listing the names of the columns one want. One can run a system-stored procedure to determine the column names. From the Query window, type and run:

sp_help authors

One can type this query in the same window as the previous query, and then highlight it to run the line on its own. Or one can use the cursor or mouse to highlight the word authors, and then press ALT-X. This action runs the sp_help stored procedure on the highlighted table name. Both methods will yield the same output-a list of columns and their data types and length

Another way one can identify the column names is to add a line to the query:

SELECT * FROM authors
WHERE 0 = 1

Because the WHERE condition is never met for this query (zero can't equal one), the query won't produce output, as shown in Image 2. But the query will return a page header that includes column names, with the order and spacing in which they'll appear on the final query. Now one can choose which columns you want to see by listing them in the



SELECT clause

SELECT au_id, au_lname, au_fname, phone
FROM authors

Note that commas separate the column names. For clarity, FROM is now on the line below SELECT. If one runs this query, one'll see that the output consists of only the four requested columns

Changing the Output Column Order

When one execute a query with select * from, the column order will be same as the column order specified in the CREATE TABLE statement but when one selects the columns from the column_list order does not need to be same as the table column order. One can rearrange the column order in the query output by rearranging the columns in the column list

Changing Column Header

The top row of the output lists the names of the columns one selected. Programmers devised the column names, so the names might not mean much to the person who sees the output report. One can use T-SQL or ANSI standard syntax to rename the output columns. If one uses the T-SQL syntax, supply the label first, followed by an equal sign, then the column name:

The three ways in which we can change column headings are:

• Select column_heading=column_name FROM Table_name
  
  Example
  SELECT EmployeeId=emp_id, lastname=lname FROM employee
• SELECT column_name as column heading From table_name
  
  Example
  SELECT emp_id as Employeeid , lname as LastName, fname as Firstname FROM employee
• SELECT column_name column heading From table_name
  
  Example
  SELECT emp_id 'Employee Id' , lname LastName, fname Firstname FROM employee

One can use square brackets ([])or single quotation marks to encapsulate column headings

Using Literals

One can use Literals to make output more readable. A literal is a string surrounded by single quotation marks included in the column_list and displayed as another column in the query result

Examples:

SELECT 'Employee Name:',fname +','+lname
FROM employee

OR

SELECT fname, lname, 'Employee ID is' , emp_id
FROM employee

Important Mathematical Functions

Mathematical functions enable one to return mathematical data using the syntax:

• Arithmetic operators can be used with data types: int, smallint, tinyint, numeric, decimal, float, real, money and smallmoney
• With arithmetic operators two types of precedence exists:
• Datatype Precedence- Arithmetic precedence exists when arithmetic operations are performed on different data types. In case of usage of different data types, the smaller data type is converted to higher data type. Like if we multiply smallint by an int, the result will be an int, but in case of money result will always be of data type money
• Operator precedence is used when multiple operators are used. It uses same math concept of BODMAS

Select function_name(parameters)

1.       Abs (numeric_expr): Provides absolute value

Example. Select Abs (-123)-[123]

2.       Ceiling (numeric_expr) : Provides smallest integer greater than or equal to specified value

Example. Select ceiling(123.3), ceiling(-123.3)-[124,-123]

3.       Floor (numeric_expr) : Provides largest integer less than or equal to specified value

Example. Select floor (123.3), floor(-123.3.)-[123, -124]

4.       Round (numeric_expr, length) : Provides numeric expression rounded to the specified length in an integer value

Example. Select Round (1234.56,1), Round ($123.67,1)-[1234.6,123.7]

5.       Square (float_expr) : Provides square value

Example. Select square (9)-(81)

6.       SQRT (float_expr) :

Example. Select sqrt (9)-[3]

7.       POWER (numeric_expression, y) : Provides value of numeric expression to the power of y

Example. Select Power(2,3)-[8]

Remember when using mathematical functions with monetary data type always use it with a dollar sign otherwise the value will be treated as numeric value

Important String Functions

String functions are used for manipulation of character data type

·  + (expression, expression) : Concatenates two or more strings

Example

Select lname +','+ fname +'.' As Name from employee

OR

SELECT ('abc' + 'def') -[abcdef]

·  ASCII (char_expr) :Provides ASCII code for left-most character

Example

Select ASCII ('A')-[65]

·  Char (integer_expr) : Provides character equivalent of ASCII code value

Example: Select Char(97)-[a]

Example

Use Northwind
GO
SELECT FirstName + ' ' + LastName, + CHAR(13) + Address, + CHAR(13)+ City, Region +char(10)
FROM Employees

·  PATINDEX('% pattern%', expression): Returns starting position of first occurance in expression

Example

select patindex('%bv%','ronvcbvvbc')

·  Lower (char_expr): Converts to lower case

Example

Select Lower ('ABCD')-[abcd]

·  Upper (char_expr): Converts to upper case

Example

Select Upper ('abcd')-[ABCD]

·  Left (char_expr, integer_expr) : Provides character string starting from the left and preceeding integer_expr character

Example

Select left('Seattle' ,2) - [se]

OR

USE pubs
GO
SELECT LEFT (title, 5) as Titles
FROM titles

·  Right (char_expr, integer_expr) : Provides character string starting from the integer_expr character from Right

Example

Select Right ('Seattle' ,2) --[le]

·  LTRIM (char_expr) : Returns data without leading blanks Example

Select Ltrim (' Computers')-[Computers]

·  RTRIM (char_expr) : Returns data without trailing blanks

Example

Select Ltrim ('Computers ')-[Computers]

·  REPLACE ('string_expression1', 'string_expression2', 'string_expression3') :Replaces all occurrences of srting2 in string1 with string3

Example. Select Replace ('abcde', 'bc','oo') -[aoode]

·  Reverse (char_expr) : Returns reverse of character expression

Example

Select Reverse('ABCD')-[DCBA]

·  SUBSTRING(expression, start, length): Returns part of character string

Example

SELECT SUBSTRING('abcdef', 2, 3) as X

X
----
bcd

Date Functions

Select date_function(parameter)

1.       Dateadd (datepart,number,date): Adds the number of dateparts to the date Example

Select Dateadd (mm, 6, '1/1/01')-[ 2001-07-01] Select Dateadd (mm, -6, '1/1/01')-[ 2001-07-01]

2.       Datediff (datepart,date1,date2): Number of dateparts between two dates Example

Select datediff(dd, '10/3/01', '10/9/01')

3.       Getdate() :Returns current date and time in internal format Example

Select Getdate()

4.       Datepart (datepart, date): Returns integer value for specified datepart for date listed Example

SELECT DATEPART(DD,'10/17/01')

5.      

System Functions

Few important ones:

1.       Isdate (variable) :Checks for valid dateExample

Select Isdate('23')- Returns 1 if valid else returns 0 OR Select Isdate('12/1/01')

2.       IsNumeric: Checks for valid numeric format. Returns 1 if valid else 0 Example

Select IsNumeric(12)

OR

USE pubs
SELECT ISNUMERIC (zip)
FROM authors
GO

3.       IsNull (expression, value) : Returns specified value in place of NULL

Example

USE pubs
GO
SELECT SUBSTRING(title, 1, 15) AS Title, type AS Type,
ISNULL(price, 0.00) AS Price
FROM titles
GO

Restricting the Rows

The previous select statements return all the rows in a table, but one can use the WHERE clause to retrieve only a subset of the rows that meet certain criteria. The WHERE clause consists of a column name, a comparison operator, and a value or range of values. The comparison operator can be an equality or an operator such as less than or greater than

Examples include:

WHERE city = 'Oakland'
WHERE au_lname = 'Bennet'
WHERE zip > '84000'

 The quotes around the zip code value denote that this is a text value. Zip code is a text field, not a numeric field, so if one need to do a comparison, one must base it on the text value of the zip code field

Comparison Operators

One can use comparison operators like >,<, >=, =<, =, <>, !=, !>, !<, ()

Example

SELECT emp_id,lname,fname
FROM employee
WHERE pub_id='0877'

Range

One can retrieve rows based on a range of value using the BETWEEN keyword

Example

Select lname, emp_if
FROM Employee
WHERE hire_date between '10/1/92' AND '12/31/92'

OR

Select lname, emp_iD
FROM Employee
WHERE lname NOT between 'a' AND 'e'

   Smaller value must come first in BETWEEN clause

  Also one must enclose range in quotes if its character data types or date data types

Lists

One can use IN Keyword to match rows in a list

SELECT Column_list
FROM table_name
WHERE column_name [NOT] IN (value_list)
Example

SELECT emp_id, lname, fname
FROM employee where pub_id IN ('0877','9990')

OR

SELECT emp_id, lname, fname, pub_id
FROM employee where pub_id NOT IN ('0877','9990')

  One can use the NOT operator to exclude a certain set of data and retrieve the rest. The NOT operator excludes all records that meet the criteria one specifies

LIKE

Like is used with character and date data

SELECT column_name
FROM table_name
WHERE column_name [NOT] Like 'string'

Wildcards

1.       %- String of zero or more characters

2.       _ -Single Character

3.       []- Single character within specified range

4.       [^]- Single character not within specified range

  Wildcard characters when used with Like keyword are enclosed in a single quotation mark

Example

SELECT title_id, title
FROM titles
WHERE title LIKE '%computers%'

OR

SELECT lname
FROM employee
WHERE lname like 'a%'

OR

SELECT lname
FROM employee
WHERE lname like '%a%'
OR

SELECT lname
FROM employee
WHERE lname like '%a'

OR

SELECT title_id, title
FROM titles
WHERE title LIKE '[cdefi]%'

Unknown Values

  Null is equivalent to value "unknown"

  IS NULL and IS NOT NULL operators are used

SELECT column_list
FROM table_list
WHERE column_name IS [NOT] NULL

Example

SELECT title_id, title
FROM titles
WHERE title_id IS NOT NULL

OR

SELECT title_id, title
FROM titles
WHERE title_id IS NULL

Using Distinct to Eliminate duplicate Information

One can eliminate duplicates using Distinct keyword in the SELECT clause

SELECT DISTINCT column_list FROM table_Name
Where search_condition

Example

SELECT DISTINCT city
FROM authors

OR

SELECT DISTINCT city, state
FROM authors

Using ORDER BY clause

ORDER BY clause is used in SELECT statement to sort data

SELECT column_name
From Table_List
[order by column_name[ASC|DESC]]
Example

SELECT title_id, au_id
FROM titleauthor
ORDER BY title_id, au_id

• Default Order is Ascending
• Since sort order being used by SQL Server can make difference in the ORDER BY clause, one should check default sort order of SQL Server using sp_helpsort
• ORDER BY clause can not be used on text or image columns

SQL Order of Logical Operations (each operates from left to right)

NOT
AND
OR

Combining Search Conditions

As the previous example demonstrates, one can combine search conditions. But one need to be cautious when combining search conditions if one isn't familiar with Boolean logic: Don't confuse the AND and OR conditions. Suppose someone say, "Get me a list of all our authors from Utah and Texas." One knows what one want. But if one write the query as

SELECT au_fname, au_lname, phone, au_id
FROM authors
WHERE state LIKE 'ut' AND state LIKE 'tx'

the query will not return any data because it asked for all authors who live in both Utah and Texas. The result you want is data that meets both conditions, authors who live in either Utah or Texas. Instead, use this query:

SELECT au_fname, au_lname, phone, au_id
FROM authors
WHERE state LIKE 'ut' OR state LIKE 'tx'

  Note that one needs to repeat the search condition. One can't write WHERE state LIKE 'ut' OR 'tx' because SQL Server expects a complete search condition on either side of the OR.

Aggregate Functions

Aggregate functions can return summary values for an entire table or for group of rows in a table. Aggregate functions are usually used in conjunction with the GROUP BY clause and are used in HAVING Clause or in the column_list.

·  AVG: Returns average of the values in the numeric expression

Example

SELECT AVG(ytd_sales)
FROM Titles

·  COUNT(*): Returns number of selected rows

Example

SELECT Count(*) from employee [ Returns total number of rows in a table]

·  MAX: Returns highest value in the expression

Example

SELECT MAX(ytd_sales) FROM titles

·  MIN: Returns lowest value in the expression

Example

SELECT MIN (ytd_sales)
FROM titles

·  SUM: Returns total of values in the numeric expression

Example

SELECT SUM(qty)
FROM sales

·  TOP n: Returns the top n values in the result set

SELECT TOP 5 * FROM authors

SELECT TOP 5 * FROM sales where qty>20

Group By and Having Clause

The group by clause summary data that meets the WHERE clause criteria to be returned as single row. The HAVING clause set the criteria to determine which rows will be returned by the GROUP BY clause

Example

SELECT title_id, count (title_id) as Number_of_Authors
From Titleauthor
GROUP BY title_id Having count (title_id)>1

OR

SELECT title_id,ytd_sales
FROM Titles
WHERE (ytd_sales>=4000)
GROUP BY title_id,ytd_sales

1.       HAVING clause has same effect on the GROUP BY clause as the WHERE clause has on the SELECT statement

2.       GROUP BY clause must contain all nonaggregate columns from the SELECT column_list

3.       HAVING clause criteria columns must return only one value

Compute By Clause

The compute and compute by clause are used to produce new rows of summary and detail data. They use the aggregate functions. The COMPUTE clause return detail rows and a grand total summary row. The compute by clause return the new rows of summary data, much like Group By clause, but it returns the rows as sub-groups with summary values

One has to use Order By clause before using Compute By clause

Example

SELECT type, SUM (ytd_sales)
FROM titles
ORDER BY type COMPUTE SUM(ytd_sales) BY type

Implementing Joins

Joins are used to retrieve data from two or more tables. The results appear as a single table with columns from the entire table specified in the SELECT column_list and meeting the search criteria. In nutshell Joins connect two or more tables based on a join condition and produce results as new table with the rows that satisfy the join condition

We will probe Joins using both SQL Server syntax and ANSI syntax.

Inner Join: There can be two types of Inner Joins. One is EquiJoin and other is Natural Join

Using Pubs consider tables:

Publishers (pub_id, pub_name, city, state, country) Primary Key (pub_id)

Pub_info (pub_id, logo, pr_info) Primary Key (pub_id)

Authors (au_id, au_lname, au_fname, phone, address, city, state, zip, contact) Primary Key (au_id)

EquiJoin: In Equi Join Column values are compared for equality and redundant columns are displayed as columns in the result set

Example:

SQL Server syntax

SELECT *
FROM publishers, pub_info
WHERE publishers.pub_id=pub_info.pub_id

ANSI syntax

SELECT *
FROM Publishers
INNER JOIN pub_info ON publishers.pub_id = pub_info.pub_id

Natural Join: In Natural Joins redundant columns are not displayed twice

Example:

SQL Server syntax SELECT p. *, p1.logo, p1.pr_info
FROM publishers p, pub_info p1
Where p.pub_id = p1.pub_id

ANSI syntax

SELECT p.*, p1.logo, p1.pr_info
FROM publishers p
INNER JOIN pub_info p1 ON p.pub_id = p1.pub_id

Cross Join or Unrestricted Join

Returns combination of all rows of all tables in join as the result set. Each row of one table is joined with each row of another table.

Example:

SQL Server syntax

SELECT p.pub_name, p1.pr_info
FROM publishers p, pub_info p1

ANSI syntax

SELECT p.pub_name,p1.pr_info
FROM publishers p CROSS JOIN pub_info p1

Outer Join

Restricts rows from one table while allowing all rows from another table as a result set. Usually used for orphan records.

Outer joins. Outer joins can be a left, right, or full outer join. Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

LEFT JOIN or LEFT OUTER JOIN

The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

Example:

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

If Inner Join were used in above query then we would have got only the results of authors name whose city was same as publishers and not those authors who lived in city to which no publisher belonged to. In this case we got name of all the authors

Self Join

Correlates rows of a table with other rows in the same table.

A table can be joined to itself in a Self Join. For example, you can use a Self Join to find out the authors in Oakland, California who live in the same zip code area.

Because this query involves a Join of the authors table with itself

USE pubs
SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
FROM authors au1 INNER join authors au2
ON au1.zip = au2.zip
WHERE au1.city = 'Oakland'
AND au1.state = 'CA'
AND au1.au_id < au2.au_id


SubQueries

A Select statement within another SELECT, INSERT, UPDATE or DELETE statement. Subqueries can produce the same results as Joins.

Remember:

One can have as many nested subqueries in one as one want, there is no limit on this

The subquery is always enclosed in parentheses unless used in Update statement

Subquery can not contain an Order By, Compute, or SELECT INTO Clause

Subquery can not be used in the Order By clause

Example:

SELECT distinct au_fname,au_lname, state
FROM authors where state IN
(SELECT state from stores)

SELECT INTO

The SELECT INTO statement enables one to create new table based on query results.

One can create both temporary and permanent tables using SELECT INTO.

Syntax:

SELECT column_list
INTO new_table_name
FROM table_list
WHERE search_condition

Example:

SELECT * into mystores
FROM Stores
Go

OR

SELECT title_id, title
INTO #tmptables
FROM titles
Go

• When Creating permanent tables using select into, one must set the SELECT INTO/BULK COPY database option true.
  
  [sp_dboption 'pubs', 'select into/bulkcopy', true]
• Name of the new table must be unique within the database and must conform to the rules for SQL Server naming conventions
• When creating local temporary table (Local temporary table are those that are available only during the current user session to SQL Server and are delocated when the session is terminated) use # in front of new table name
• When creating global temporary table (Available to all user sessions to SQL Server and are deallocated when the last user session accessing the table is terminated) use ## in front of new table name