Business Analysis Course2017-05-23T01:30:19+00:00

Business Analysis Course

2. More on Databases

Session 2.
SQL Server 2008
Working with Databases and Tables

Ram N Sangwan

Agenda

•   SQL Server Database Overview

•   Types of Database

•   Working with Queries

•   Aggregates and Group By

SQL Server Database Overview

Objectives

•     At the end of this sub-module, you should be able to:

•    Define SQL SERVER database

•    Identify how to connect to SQL SERVER Database

•    Describe the different types of database services

What is SQL Server?

•       SQL Server  is a Relational Database Management System (RDBMS).

•       Microsoft SQL Server is designed to support high-volume transaction processing as well as data warehousing  and decision-support applications.

•       SQL Server is part of the core of a family of integrated products that includes development tools, system management tools, distributed system components, and open  development interfaces.

Overview of Client/ Server Architecture

Connecting to SQL Server

SQL Server Services

Summary

In this sub-module, we have discussed:

•   SQL SERVER RDBMS Database

•   Getting connected with SQL Server Authentication

•   Getting connected with Windows Authentication

Types Of Database

Objectives

At the end of this sub-module, you should be able to:

•       Identify different types of Database

•       Identify and describe different types of data access tools

Database

•       Database

◦    Collection of organized data in a particular order, preferably in rows and columns

•       Types of Database

◦    There are two main types of database; flat-file and relational

◦                   The typical flat-file database is split up using a common delimiter

◦                   The “relation” comes from the fact that the tables can be linked to each other

•       There are 5 system databases and 2 user databases are available with SQL server 2008

Types of Databases

SQL Server Management Tools

SQL Server Database Engine Components

An Introduction to SQL Server Management Studio

•        Support for writing and editing queries or scripts

•        Integrated source control for solution and script projects

•        Supports most administrative tasks for SQL Server

•        An integrated Web browser for quick browsing

Data Organization

•       Logical Structure

◦     The data in a database is organized into the logical components visible to users.

◦     Logical components include tables, views, procedures, and users.

•        Physical Structure

◦     Data is physically stored on the disk system with in the data files

◦     Clustered indexed actually forces the physical ordering of the data within the data files

•        Meta Data & Catalog

◦     Metadata describes the database’s structure, components, users, security, and so on.

◦     Catalog includes system catalog and database catalog.

Metadata Catalogs

•       System Tables Store Information (Metadata) About the System and Database Objects

•       Database Catalog Stores Metadata About a Specific Database

•       System Catalog Stores Metadata About the Entire System and All Other Databases

•       System Stored Procedures

EXEC sp_help Employees

•       System and Metadata Functions

SELECT USER_NAME(10)

•       Information Schema Views

SELECT * FROM INFORMATION_SCHEMA.TABLES`

Summary

•       In this lesson, we have discussed:

•   Database

•   Types of Database

•   Physical Structure

•   Logical Structure – Database Objects

•   Meta Data & Catalog

•   System Catalog

•   Database Catalog

Working with Queries

Objectives

At the end of this sub-module, you should be able to:

◦    Identify how to retrieve information form database using select statement

◦    Recognize how to restrict rows in query result

◦    Illustrate sorting of query result

◦    Use of Top Clause

Types of TSQL statements

•     DQL- Data Query Languageà commands used to get data from the database and impose ordering upon it.

•      DML- Data Manipulation Languageà commands allow the users move data into and out of a database and also modify the data in the database.

•      DDL- Data Definition Languageà is used to create, alter and delete database object.

•      DCL- Data Control Languageà consists of commands that control the user access to the database object.

•      TCL- Transaction Control Languageà commands allow the users to control transactions.

Data Definition Language (DDL)

•     Define the database objects

◦     CREATE object_name

◦     ALTER object_name

◦     DROP  object_name

•     Must Have the appropriate permissions

Data Control Language (DCL)

•     Set or Change Permissions

◦    GRANT

◦    DENY

◦    REVOKE

•     Must Have the appropriate permissions

•     By Default, only sysadmin, dbcreator, db_owner, and db_securityadmin roles can execute

Data Manipulation Language (DML)

•     USE DML Statements to Change Data or Retrieve Information

◦     SELECT

◦     INSERT

◦     UPDATE

◦     DELETE

•     Must Have the appropriate permissions

Tool Command Language (TCL)

•     USE TCL Statements when executing a transaction or a batch.

◦     Rollback

◦     Commit

•     Must Have the appropriate permissions

Using the SELECT Statement

•     Select List Specifies the Columns

•     FROM Clause Specifies the Table

•     WHERE Clause Specifies the Condition Restricting the Query

Select Command-Examples

Select – Range of Values

•      Range of Values can be selected using BETWEEN, IN, IS or LIKE operator

Column and Table alias

•     Alias A column alias is useful sometimes in cutting down the clutter in  an SQL statement:

•     The readability of a SELECT statement can be improved by giving a table an alias, also known as a correlation name or range variable.

•     A table alias can be assigned either with or without the AS keyword:

TOP & Order BY

•     Lists Only the First n Rows of a Result Set

•     Specifies the Range of Values in the ORDER BY Clause

•     Returns Ties if WITH TIES Is Used

Summary

In this sub-module, we have discussed:

◦    Simple Select Statements

◦    Column Aliasing

◦    Table Aliasing

◦    WHERE Clause

◦    ORDER BY Clause

◦    Use of Top Clause

Aggregates and Group By

Objectives

At the end of this sub-module, you should be able to:

•    Recognize how to use Aggregate Functions in query statements

•    Applying Group by Clause in Select Statement

•    Illustrate  Having Clause

•    Illustrate Compute and Compute By Clause

 Using Aggregate Functions

Using Aggregate Functions (Contd.).

Syntax:

SELECT            [column,] group_function(column), …

FROM                                     table

[WHERE         condition]

[GROUP BY   column]

[ORDER BY    column]

GROUP BY Clause

•     Each group summarizes the data for all the rows in the table that have the same value

•     When you group data, you can display only summary or grouped data

•     You cannot display values from individual rows

•     You can group by more than one column, each group in the query shows the aggregate values for all grouping columns

GROUP BY Clause (Contd.).

•     Group By is added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called, and without the GROUP BY function it was impossible to find the sum for each individual group of column values

•      You can use the GROUP BY clause to divide the rows in a table into groups

GROUP BY Clause (Contd.).

Examples:

USE pubs

SELECT pub_id ,SUM(price) as Total

FROM titles

GROUP BY pub_id

SELECT pub_id, type, SUM(price) Total_price

FROM titles

GROUP BY pub_id, type

GROUP BY and ALL

•     Transact-SQL provides the ALL keyword in the GROUP BY clause

•     ALL is meaningful only when the SELECT statement also includes a WHERE clause

•     If you use ALL, the query results include all groups produced by the GROUP BY clause, even if some of the groups have no rows that meet the search conditions

•      Without ALL, a SELECT statement that includes GROUP BY does not show groups for which no rows qualify

HAVING Clause

•      The HAVING clause sets conditions on the GROUP BY clause similar to the way WHERE interacts with SELECT

•     You can limit the groups that appear in a query by specifying a condition that applies to groups as a whole

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY   group_by_expression]
[HAVING group_condition]
[ORDER BY column]

HAVING Clause (Contd.).

USE Northwind

SELECT productid, SUM(quantity) AS total_quantity

 FROM orderhist

 GROUP BY productid

 HAVING SUM(quantity)>=30

SELECT pub_id, AVG(price)

FROM titles

GROUP BY pub_id

HAVING (AVG(price) > 10)

USE pubs

SELECT pub_id, total = SUM(ytd_sales)

FROM titles GROUP BY pub_id

HAVING SUM(ytd_sales) > 40000

COMPUTE and COMPUTE BY Clauses

•     COMPUTE :  When used with select, displays only Grand Total Summary of the group

Thank You

Leave A Comment

Show Buttons
Hide Buttons