Overview of SQL Server 2008 R2 and Transact SQL

Major Features of SQL Server:

1. Easy Installation
   - click http://msdn.microsoft.com/en-us/library/ms143219.aspx
2. Integration with Internet
   - click http://msdn.microsoft.com/en-us/library/ms189887.aspx
3. Scalability and Availability
   - click http://msdn.microsoft.com/en-us/library/ms152468.aspx
4. Support for the Client/Server Model
   - The Microsoft SQL Server is a type of relational model database server that stores and manages data. This server uses the client-server model, which means that a

requester, or client, asks something of the server. The server then searches for this information and returns an answer to the client. Microsoft offers free tools to

help you manage your client relationship, and all of these tools are for industry professionals, especially developers and administrators.
5. Operating System Compatibility
   - Microsoft Windows Server 2003 Service Pack 2, or later
   - Microsoft Windows XP Service Pack 2 or later
   - Microsoft Windows Vista
   - Microsoft Windows Server 2008
   - Microsoft Windows 7
6. Multiple-Protocol Compatibility
   - click http://msdn.microsoft.com/en-us/library/ms187892.aspx
7. SMP (Symmetrical Multi Processing) Compatibility and Scalability
  - click http://www.microsoft.com/sqlserver/2008/en/us/parallel-data-warehouse.aspx
8. Data Warehousing
  - click http://www.microsoft.com/sqlserver/2008/en/us/parallel-data-warehouse.aspx
9. ANSI/ISO-92 Compliant
  - click http://msdn.microsoft.com/en-us/library/ms177259.aspx
10. Data Replication Support
  - click http://msdn.microsoft.com/en-us/library/ms143241.aspx
11. Full-Text Search
  - click http://msdn.microsoft.com/en-us/library/ms142571.aspx

SQL Server Architecture:

1. Client/Server Database System
  - click http://msdn.microsoft.com/en-us/library/bb726025.aspx
2. Desktop Database System
  - when clients use local SQL Server databases, a copy of the SQL Server database runs on the client, and manages all the SQL Server databases on the client.
  - Applications are connected to the database engine in the same manner that they are connected across the network, to a database engine running on the remote server

SQL Server Databases:

1. System Databases
   - click http://msdn.microsoft.com/en-us/library/ms190190.aspx
2. User Databases
   - databases created by the User

Database Objects:

- table, column, row, data type, constraint, default, rule, index, view, stored procedure and trigger

SQL Server Security
- click http://msdn.microsoft.com/en-us/library/cc280562.aspx

Constants in Query Result sets

select country + ':' + city_code + ' => ' + city_name from city_master

Assigning Result Set Column Names

select PNR_no as 'PNR Number' from reservation

select aircraft_code as 'Aircraft', fare as 'Regular First Class Fare', fare*.75 as 'Discounted First Class Fare' from flight_details
- display flight details, such as, the aircraft code, regular fare, and discounted fare for the first class. A 25% discount is being offered. Label the columns as

Aircraft, Regular First Class Fare, and Discounted First Class Fare.

Computed Value in the Selected List

select aircraft_code, class_code, fare, fare*0.1 as Discount from flight_details

Eliminating Duplicates with Distinct

select distinct aircode from flight

Limiting Result Sets Using Top and Percent

select top 3 * from meal

select top 40 percent * from meal

Filtering Rows from a Table:

1. Vertical Filtering - selects specific columns from a table.
2. Horizontal Filtering - select specific rows from a table.
   - the 'where' clause in a query acts as a horizontal filter to limit the number of rows returned for a specified condition.

select * from reservation where aircraft_code='IC01'

select 'Mr. ' + name as 'Male Passenger' from passenger where sex='m'
- display the names of all male passengers with the courtesy title 'Mr.', attached to the name

Using Operators and Wild Card Characters

= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal
! Not

- represents a single character select meal_code from meal where meal_code like'c_'
% represents a string of any length select meal_code from meal where meal_code like'co%'
[] represents a single character within the range enclosed in the brackets select * from flight where aircraft_code like '9W0[1-2]'
[^] represents any single character not within the range enclosed in the brackets select * from flight where aircraft_code like '9W0[^1-2]'

select Name, [PP No], [Meal Pref] from passenger where PNR_No like '[1-2]'
- list the passenger name, PP No, and meal preference details for passengers having PNR numbers, 1 or 2, from the passenger table.

select * from meal where meal_name like '%non-vegetarian%'
- display the types of non-vegetarian meals offered on flights

select * from status_master where description like '%[^d]'
- display the status code and description, where the description does not end with the letter 'd'

Logical Operators

and joins two conditions and returns true only when both conditions are true select * from reservation where aircraft_code='IC01' and class_code='EX'
or joins two conditions and returns true when either of the conditions is true select * from reservation where class_code='EX' or class_code='e'
not negates the search condition select * from passenger where not Pnr_no >= 2

You can include multiple logical operators in a single 'select' statement. 'Not' is evaluated first,then 'and', and finally 'or'.

select * from flight_days where day_code='1' or day_code='4'
- display the aircraft_code of the airlines that fly on sunday and Wednesday

Sorting Rows with Order By

- a sort can be ascending (asc) or descending (desc). By default, records are sorted in 'asc' order.

select * from passenger order by name

select * from flight where destination = 'NY' order by source
- display the details of all flights to city code 'NY', sorted by source city name

select * from flight where destination='Lon' order by Journey_hrs
- display the sorted details of flights to city code 'Lon', with the least duration flight displayed first

System Global Variables

- hold information useful to the database user.
- these variables are prefixed with the @@ sign.

select @@version returns the version of SQL server
select @@trancount returns number of transactions currently open on the connection
select @@servername returns name of local servers running SQL server
select @@rowcount returns number of rows affected by the latest SQL statement
select @@nestlevel returns level of the current stored procedure execution
select @@LANGUAGE returns language being used currently
select @@SERVICENAME returns SQL server service name on the current computer
select @@PROCID returns ID of the current stored procedure
select @@CONNECTIONS returns number of connections established with the server since it was started