SQL SERVER | SQL for Beginners - Software Development PDF Download

 Aspiring Database Developers should be able to efficiently query and maintain databases.

This module will help students learn the Structured Query Language (SQL) to query and manage databases.

    SQL Server :

    Is a data engine introduced by Microsoft.

    Provides an environment used to create databases.

    Allows secure and efficient storage and management of data.

    Provides other components and services that support the business intelligence platform to generate reports and help in data analysis.

    A business application can have the following elements:

    The user interface or the presentation element, through which data is entered.

    The application logic or the business rule element, which helps in validating the entered data.

    The data storage or the data management element, which manages the storage and retrieval of data.

Based on preceding three elements, the application architectures can be categorized as:

    Single-tier architecture

    Two-tier architecture

    Three-tier architecture

    N-tier architecture

    Single-tier architecture:

    Combines all the elements of a business application as a single process.

    The executable unit is  installed on all computers that user need to work on.

    In this case,it becomes complicated  to modify the application or to fix any bugs.This is because after modifying the application,it is required to       recompile and redistribute the entire application to all computers.

Two-tier architecture:

    Divides the business application into two parts, one part handles the data, while the other provides the user interface.

    Two parts can be located on a single computer or on seperste computers over a network

    The part that handles the UI is called Client tier

    The part that implements the application logic and manages the input data based on business rules is called server tier.

    In this architecture the maintenance,upgrade,and general administration of data is easier,as it exists only on one server and not on all the clients

    Is also called the client-server architecture.

    Most RDBMSs such as Microsoft access,SQL Server and Oracle adhere to client server architecture

 Three-tier architecture:

       It divides the business application into three tiers.

       The first tier is called the client tier.

        The second or middle tier is called the business tier.

        The third tier is called the server tier.

        When implementing the complex solutions in case of two-tier architecture, the tier  on which the business logic is implemented becomes             overloaded as  a  result it takes more time to execute. Therefore to provide more flexibility 2 tier can be split into 3 tier

 

 

Busniess tier consists of application logic that implement rules and check data.advantage of it is that allows  you to change the business rules without affecting the other  two tiers

For ex. In banking app. For loans user tier is front end used by customer to specify loan details.The server tier consists of the RDBMS where the data is stored.The business tier consists of business rules such as loan limit and interest rate charged to a customer.If there is any change in interest,only middle

N-tier architecture:

   Is also called as multi-tier architecture.

    Has one component near the client tier, which is responsible to do the client side validation and send the data to the presentation tier.

    The n-tier architecture consists of the following layers:

    Client tier

     UI-centric processing components

     Data-centric processing objects

     Database server

 

 

As the business complexities increased, the business tier become larger and  unmanageable. This lead to the evolution of n-tier architecture where business services model   is divided into smaller manageable units.

Banking application when further expanded, can depict the example of n tier architecture.

Client tier deal with user interface i.e forms menu toolbars

Server tier with data handling and saving to database server.

Business logic: rules , interest rates ,fixed deposit ,ATM  all  these form middle tier.

However there are some rules that must be placed on user interface  and database.You can place these code on UI centric application or data centric processing.

Components of SQL SERVER:

 

 

Database engine:

   Provides support to store, query, process, and secure data on the database server.

   Allows you to create and manage database objects, such as tables.

   Provides the following background services:

   Service Broker

   Replication

   Full-text search

   Notification services

   Service Broker:

   Provides support for asynchronous communication between clients and the database server.

   Enables reliable query processing.

   The following figure shows an example of order processing system.

 

Replication:

   Allows you to copy and distribute data and database objects from one database server to another.These severs are located on remote                locations.

 

   The following figure shows an example of order processing system.

 

 

Full-text search:

   Allows you to implement fast and intelligent search in large databases.

   Notification services:

   Allow you to generate and send notification messages to the users or administrators about any event.

   Allow developers to build notification applications that send timely, personalized information updates, helping to enhance customer                      relationships.

   For ex. Database administrator must be notified when a table is created or deleted.

Integration services:

   These services allow you to gather and integrate the varied data in a consistent format in a common database called the data warehouse.

   A data warehouse is similar to a physical warehouse that stores raw material or products for further distribution.

   The SQL Server 2008 Integration Services (SSIS) Import and Export Wizard helps you to complete the process of selecting the data source,       the destination, and the objects that will be transferred to create a data warehouse.

Analysis services:

   These services help in data analysis in a BI application.

   These are built on a data warehouse that contains the consolidated data from  various data resources. These are based on data mining              solutions. Data mining allows user to query and present data in reports that enable business forecasts such as expected sales or profit              estimates in next financial year.

Microsoft SQL Server 2008 Analysis Services (SSAS):

   Provides online analytical processing (OLAP) for BI applications.

   A BI application is an applicationthat is used by top management of an organization for data analysis to make future decisions.BI tools are the    tools that help in creating reports that enable reports that enable data analysis

Reporting services :

   These services provide support to generate complete reports on data in the database engine or in the data warehouse.

   These services provide secure and restricted access to these reports.

   Microsoft SQL Server 2008  Reporting Services (SSRS):

   Helps in creating Web-based reports that is based on the content stored in a variety of data sources.

 The following figure shows the usage of the various SQL Server core components in a BI application.

Features of SQL SERVER

1.Built in Support for XML:

Allows you to store and manage XML data invariables or columns of XML datatype.

XML data is stored in a structured format that can be used across different platforms.

2.CLR Integration:

Allows you to implement  programming logic in any of the .net supported language.

3.Scalibilty:

Allows  partitioning of database tables to help in parallel processing  of queries which helps in mproving the performance.

4.Service Oriented Architecture:

Provides a distributed,ansynchronous application framework for large scale applications.This allows database clients to send requests to the database server even if the server is not available to the process request immediately.

5.Support for Web Services:

Allows yopu to data acess from web services with http endpoints.

6.High Level of Security:

High security by enforcing policies for log on passwords, Permissions.

7.High availability

Available to all times with the help of database mirroring, failover clustering and database snapshots.

8.Support for data migration and analysis:

Provide support  to migrate data from disperate data sources to  a common database.In addition it helps in building data warehouse to support BI  .

Is the core language used to store, access, and modify data.

   Can be categorized as:

   Data Definition Language (DDL):

   Is used to define the database, data types, structures, and constraints on the data.

 

Data Manipulation Language (DML):

   Is used to manipulate the data in the database objects.

 

Data Control Language (DCL):

   Is used to control the data access in the database.

 

Data Query Language (DQL):

   Is used to query data from the database objects.

 

Some of the DDL statements are:

   CREATE

   ALTER

   DROP

Some of the DML statements are:

   INSERT

   UPDATE

   DELETE

   Some of the DCL statements are:

   GRANT

   REVOKE

Data types:

   Specify the type of data that an object can contain.

   Can be associated with each column, local variable, or an expression defined in the database.

 

Data type                                        Range                                                                                    Used to store

int                                –2^31 (–2,147,483,648) to 2^31–1 (2,147,483,647)                                         Integer data (whole numbers)

float                              –1.79E 308 to –2.23E–308, 0 and 2.23E–308 to 1.79E 308                         Floating precision data

 money                          –922,337,203,685,477.5808 to 922,337,203,685,477.5807                             Monetary data

char(n)                            n characters, where n can be 1 to 8000                                                      Fixed length character data

varchar(n)                       n characters, where n can be 1 to 8000                                                       Variable length character data

xml                                xml instances and xml type variables                                                         Store and return xml values

 

  The SELECT statement is used for accessing and retrieving data from a database.

   Syntax:

                     SELECT [ALL | DISTINCT] select_column_list 
          [INTO [new_table_name]] 
          [FROM {table_name | view_name} 
          [WHERE search_condition]

   To display all the details of employees, you can use the following query:

          SELECT * FROM HumanResources.Employee

 

 

  To view specific details, you can specify the column names in the SELECT statement, as shown in the following query:

          SELECT EmployeeID, ContactID, LoginID, Title

          FROM HumanResources.Employee

 

 

 

 Consider the following example that displays the Department ID and Department Names from the Department table of the AdventureWorks database.

The report should contain column headings different from those given in the table, as shown in the following figure

Department Number       Department Name

 

 

  You can write the query in the following ways:

          SELECT 'Department Number'= DepartmentID, ' Department Name'= Name   FROM HumanResources.Department

          SELECT DepartmentID 'Department Number', Name 'Department Name' FROM HumanResources.Department

          SELECT DepartmentID AS 'Department Number', Name AS 'Department Name'  FROM HumanResources.Department

 The SQL Server will display the same output for all the preceding queries, as shown in the following figure.

 

 

Literals are string values that are enclosed in single quotes and added to the SELECT statement.

The following SQL query retrieves the employee ID and their titles from the Employee table along with a literal ‘Designation’:

SELECT EmployeeID, 'Designation: ', Title

FROM HumanResources.Employee

 

 

Concatenating the text values in the output:

The concatenation operator is used to concatenate string expressions and is represented by the sign.

To concatenate two strings, you can use the following query:

                              SELECT 'snow' 'ball'

 

The following SQL query concatenates the data of the Name and GroupName columns of the Department table into a single column:

          SELECT Name ' department comes under ‘ GroupName ' group' AS Department FROM HumanResources.Department 

 

 

Calculating column values:

   Arithmetic operators are used to perform mathematical operations, such as addition, subtraction, division, and multiplication, on numeric columns or on numeric constants.

   SQL Server supports the following arithmetic operations:

      (for addition)

       - (for subtraction)

       / (for division)

       * (for multiplication)

       % (for modulo)

     The following SQL query retrieves the per day rate of the employees from the EmployeePayHistory table: 
          SELECT EmployeeID, Rate,

         Per_Day_Rate = 8 * Rate

       FROM HumanResources.EmployeePayHistory

          Rate is multiplied by 8, assuming that an employee works for eight hours in a day

   Selected rows can be retrieved using the WHERE clause in the SELECT statement.

   The following SQL query retrieves the department details from the Department table, where the group name is Research and Development:

          SELECT * FROM HumanResources.Department

          WHERE GroupName = 'Research and Development'

 

Comparison operators:

    Test for similarity between two expressions.

    Allow row retrieval from a table based on the condition specified in the WHERE clause.

    Cannot be used on text, ntext, or image data type expressions.

    Syntax:

          SELECT column_list

          FROM table_name

          WHERE  expression1 comparison_operator expression2

    The following SQL query retrieves records from the Employee table where the vacation hour is less than 5:

          SELECT EmployeeID, NationalIDNumber, Title, VacationHours FROM HumanResources.Employee      WHERE VacationHours < 5

 

Logical operators:

    Are used in the SELECT statement to retrieve records based on one or more conditions.

    Are of the following types:

    OR

    AND

    NOT

    Syntax:

          SELECT column_list

          FROM table_name

          WHERE conditional_expression1 {AND/OR} [NOT]

          conditional_expression2

    For example:

          SELECT * FROM HumanResources.Department

          WHERE GroupName = 'Manufacturing'

          OR GroupName = ' Quality Assurance'

    Range operator:

    Retrieves data based on a range.

    Are of the following types:

    BETWEEN

    NOT BETWEEN

    Syntax: 

SELECT column_list

FROM table_name

WHERE expression1 range_operator expression2 AND expression3

    For example:

                    SELECT EmployeeID, VacationHours                    FROM HumanResources.Employee                         WHERE VacationHours BETWEEN 20 AND 50

    Which of the following operators are logical operators?

1.  BETWEEN and NOT BETWEEN

2.  AND, OR, and NOT

3.  and %

4.  > and <

    IN keyword:

    Selects values that match any one of the values in a list.

    NOT IN keyword:

    Restricts the selection of values that match any one of the values in a list.

    Syntax:

SELECT column_list

FROM table_name

WHERE  expression list_operator (‘value_list’)

    For example:

                    SELECT EmployeeID, Title, LoginID FROM           HumanResources.Employee                          WHERE Title IN ('Recruiter', 'Stocker')

           

    The LIKE keyword:

    Is used to search a string by using the following wildcard characters:

    %

    _

    []

    [^]

    Matches the given character string with the specified pattern.

    For example:

          SELECT * FROM HumanResources.Department         WHERE Name LIKE 'Pro%'

    NULL values:

    Can be retrieved by using the IS NULL keyword with the SELECT statement.

    Syntax:

          SELECT column_list                                     FROM table_name                                          WHERE column_name unknown_value_operator

    For example:

          SELECT EmployeeID, EndDate                                FROM HumanResources.EmployeeDepartmentHistory  WHERE EndDate IS NULL

    ORDER BY clause:

    Can be used with the SELECT statement to display records in a specific order.

    Displays record in ascending or descending order.

    Syntax:

SELECT select_list

FROM table_name

[ORDER BY order_by_expression [ASC|DESC]

[, order_by_expression [ASC|DESC]…]

    For example:          SELECT GroupName, DepartmentID, Name                      FROM HumanResources.Department                         ORDER BY GroupName, DepartmentID

 

 

TOP keyword:

    Can be used with the SELECT statement to retrieve only the first set of rows from the top of a table.

    Syntax:

SELECT [TOP n [PERCENT]] column_name

[,column_name…]

FROM table_name

WHERE search_conditions

[ORDER BY [column_name[,column_name…]

    For example:

              SELECT TOP 10 * FROM HumanResources.Employee

    DISTINCT keyword:

    Eliminates the duplicate rows from the result set.

    Syntax:

SELECT [ALL|DISTINCT] column_names

FROM table_name

WHERE search_condition

    For example:

SELECT DISTINCT Title FROM

HumanResources.Employee

WHERE Title LIKE 'PR%'

           

    Write a query to display all the records of the ProductModel table where the product name begins with HL.

 

Use functions to customize the result set:

    SQL Server 2005 provides the following in-built functions to customize the result set:

    String functions

    Date functions

    Mathematical functions

    Ranking functions

    System functions

    String functions:

    Are used to manipulate the string values in the result set.

    Are used with the char and varchar data types.

    Syntax:

          SELECT function_name (parameters)

Function name           Parameters                                                    Example                                 Description

char                           (integer_expression)                                          SELECT char(65)                     Returns 'A', the character equivalent of                                                                                                                                                          the ASCII code value

left                            (character_expression, integer_expression)         SELECT left ('RICHARD',4)         Returns 'RICH', which is a part of the                                                                                                                                                             character string equal in size to the                                                                                                                                                               integer_expression characters from the                                                                                                                                                         left

len                             (character_expression)                                      SELECT len ('RICHARD')            Returns 7, the number of characters in                                                                                                                                                          the character_expression

ltrim                           (character_expression)                                      SELECT ltrim ('    RICHARD')       Returns 'RICHARD' without leading                                                                                                                                                               spaces. It removes leading blanks                                                                                                                                                                  from the character expression

reverse                     (character_expression)                                       SELECT reverse ('ACTION')         Returns 'NOITCA', the reverse of the                                                                                                                                                             character_expression

 

For example:

          SELECT Name = Title ' ' left(FirstName,1) '. ' LastName, EmailAddress FROM Person.Contact

 

Date functions are used to manipulate datetime values.

   The following table lists some of the date functions provided by SQL Server 2005.

Function name          Parameters                                    Example                                     Description

dateadd            (date part, number, date)              SELECT dateadd(mm, 3,’2009-01-01’)         Returns 2009-04-01, adds 3 months to the date

datename         (date part, date)             SELECT datename(month, convert(datetime,'2005-06-06'))   Returns June, date part from the listed date                                                                                                                                                    as a character value)

getdate               ()                                                   SELECT getdate()                                   Returns the current date and time

day                         (date)                                       SELECT day('2009-01-05')                      Returns 5, an integer, which represents the day

The following SQL query uses datename() and datepart() functions to retrieve the month name and year from a given date:

          SELECT EmployeeID, datename(mm, hiredate) ', ' convert(varchar, datepart(yyyy, hiredate)) as 'Joining' FROM HumanResources.Employee

 

 

Mathematical functions are used to manipulate the numeric values in a result set.

   The following table lists some of the mathematical functions provided by SQL Server .

Function name         Parameters                                     Example                                                   Description

ceiling                 (numeric_expression)                    SELECT ceiling(14.45)       Returns 15,  the smallest integer greater than or equal to the                                                                                                                          specified value

exp                    (float_expression)                              SELECT exp(4.5)              Returns 90.0171313005218,  the exponential value of the                                                                                                                                specified value

floor                    (numeric_expression)                       SELECT floor(14.45)         Returns14, the largest integer less than or equal to the                                                                                                                                 specified value

power                 (numeric_expression, y)                    SELECT power(4,3)              Returns 64, which is 4 to the value of  3

round                  (numeric_expression, length)          SELECT round(15.789, 2)         Returns 15.790, a numeric expression rounded off to the                                                                                                                                 length specified as an integer expression

 

The following table lists the usage of the round() function provided by SQL Server 2005.

Function                             Output

round (1234.567,2)              1234.570

round (1234.567,1)              1234.600

round (1234.567,0)              1235.000

round (1234.567,-1)              1230.000

round (1234.567,-2)             1200.000

round (1234.567,-3)            1000.000

 

The following SQL query retrieves the EmployeeID and Rate for a specified employee id from the EmployeePayHistory table:

SELECT EmployeeID,                           'Hourly Pay Rate' = round(Rate,2)

FROM HumanResources.EmployeePayHistory

 Identify the utility of the datepart function.

    Solution:

    The datepart function is used to extract different parts of a date value.

The management of AdventureWorks wants to increase the shift time from 8 hours to 10 hours. Calculate the end time of the shifts based on     their start time.

    Solution:

    SELECT ShiftID, StartTime, 'EndTime' = dateadd (hh, 10, StartTime) FROM HumanResources.Shift

Ranking functions:

    Generate sequential numbers for each row or to give a rank based on specific criteria.

Supported by SQL Server 2005 are:

    row_number()

    rank()

    dense_ rank()

    The row_number() function:

Returns the sequential numbers, starting at 1, for the rows in a result set based on a column.

    For example:

    SELECT EmployeeID, Rate, row_number() OVER(ORDER BY Rate desc)AS RANK FROM HumanResources.EmployeePayHistory

 

The rank() function:

    Returns the rank of each row in a result set based on specified criteria.

   For example:

          SELECT EmployeeID, Rate, rank() OVER(ORDER BY Rate desc)AS rank  FROM HumanResources.EmployeePayHistory

 

The dense_rank() function:

   Is used where consecutive ranking values need to be given based on a specified criteria.

   For example:

   SELECT EmployeeID, Rate,dense_rank() OVER(ORDER BY Rate desc)AS rank   FROM HumanResources.EmployeePayHistory

 

 

 

 

 System functions:

   Are used to query the system tables.

   Are used to access the SQL Server databases or user-related information.

   For example:

          SELECT host_id() as ‘HostID’

   The following table lists some of the system functions provided by SQL Server 2005.

Function                                 Definition

host_name ()                           Returns the current host computer name of a client process

suser_sid ([‘login_name’])         Returns the security identification (SID) number corresponding to the log on name of the user

user_id ([‘name_in_db’])           Returns the database identification number corresponding to the user name

db_id (['db_name'])                   Returns the database identification number of the database

db_name ([db_id])                    Returns the database name

Problem Statement:

   The management at AdventureWorks, Inc. wants to view a report that displays the employee ID, designation, and age of the employees who     are working as a marketing manager or a marketing specialist. The data should be displayed in uppercase.

The employee details are stored in the Employee table in the AdventureWorks database. How will you display the required data?

    Aggregate functions:

    Summarize the values for a column or a group of columns, and produce a single value.

    Syntax:

          SELECT aggregate_function([ALL|DISTINCT] expression) FROM table_name

    You can calculate summary values by using the following aggregate functions:

    Avg()

    Count()

    Min()

    Max()

    Sum()

    Avg():

    Returns the average of values in a numeric expression, either all or distinct.

    For example:

          SELECT 'Average Rate' = avg (Rate)   FROM HumanResources.EmployeePayHistory

    Count():

    Returns the number of values in an expression, either all or distinct.

    For example:

          SELECT 'Unique Rate' = count (DISTINCT Rate) FROM HumanResources.EmployeePayHistory

    Min():

    Returns the lowest value in the expression.

    For example:

          SELECT 'Minimum Rate' = min (Rate)            FROM HumanResources.EmployeePayHistory

    Max():

    Returns the highest value in the expression.

    For example:

          SELECT 'Maximum Rate' = max (Rate)           FROM HumanResources.EmployeePayHistory

    Sum():

    Returns the sum total of values in a numeric expression, either all or distinct.

    For example:

          SELECT 'Sum' = sum (DISTINCT Rate)         FROM HumanResources.EmployeePayHistory

 

    What would be the output of the following query?

          SELECT 'Maximum Rate' = max (UnitPrice)

          FROM Sales.SalesOrderDetail

The GROUP BY clause:

    Summarizes the result set into groups as defined in the SELECT statement by using aggregate functions.

    Uses the HAVING clause to further restrict the result set to produce the data based on a condition.

    Syntax:

SELECT column_list

FROM table_name

WHERE condition

[GROUP BY [ALL] expression [, expression]

[HAVING search_condition]

 

 For example:

     SELECT Title, Minimum = min(VacationHours), Maximum = max(VacationHours)              FROM HumanResources.Employee
WHERE VacationHours > 80 GROUP BY Title

 

 

 COMPUTE and COMPUTE BY:

   The COMPUTE clause is used to generate summary rows by using aggregate functions in the query results.

   The COMPUTE BY clause can be used to calculate summary values of the result set on a group of data.

   Syntax:

     SELECT column_list

     FROM table_name

     ORDER BY column_name

     COMPUTE aggregate_function (column_name) [, aggregate_function (column_name)...] 
[BY column_name [, column_name]...]

 

For example:

SELECT Title,   'Average  VacationHours' = VacationHours,     'AverageSickLeaveHours' = SickLeaveHours       FROM HumanResources.Employee                    WHERE Title IN ('Recruiter', 'Stocker')            ORDER BY Title, VacationHours, SickLeaveHours  COMPUTE  avg(VacationHours), avg (SickLeaveHours ) by Title

 

Ques: When grouping data, which of the following clauses helps eliminate the groups that do not match the condition specified?

    NOT IN

    HAVING

    WHERE

   COMPUTE