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.,