SQL Server
Client Server architecture
Query
OLTP
Results
OLAP
Client SQL Server
Authentication modes
Windows Authentication
Mixed Authentication
System DataBases
Master DataBase:
The master database records all the system-level
information for a SQL Server system. This includes
instance-wide metadata such as logon accounts . Also,
master is the database that records the existence of all
other databases and the location of those database files
and records
model DataBase:
is used as the template for all databases created on an
instance of SQL Server
System DataBases
Msdb DataBase
is used by SQL Server Agent for scheduling alerts and
jobs and by other features
TembDB
is used by SQL Server Agent for saving Temporary user
objects that are explicitly created, such as: global or
local temporary tables, temporary stored procedures,
table variables, or cursors
Introduction to
Transact-SQL
The Transact-SQL Programming Language
Implements Entry-Level ANSI SQL-92 ISO Standard
Contains Additional Unique Functionality
Types of Transact-SQL Statements
Data Definition Language Statements
Data Control Language Statements
Data Manipulation Language Statements
Data Definition Language Statements
Define the Database Objects
CREATE object_name
ALTER object_name
DROP object_name
Data Control Language Statements
Set or Change Permissions
GRANT
DENY
REVOKE
Data Manipulation Language Statements
USE DML Statements to Change Data or Retrieve
Information
SELECT
INSERT
UPDATE
DELETE
Comments
In-line Comments
Example 1
SELECT productname
, (unitsinstock - unitsonorder) -- Calculates inventory
, supplierID
FROM products
GO
Block Comments
Example 3
/*
This code retrieves all rows of the products table
and displays the unit price, the unit price increased
by 10 percent, and the name of the product.
*/
USE northwind
SELECT unitprice, (unitprice * 1.1), productname
FROM products
GO
Using the SELECT Statement
Select List Specifies the Columns
WHERE Clause Specifies the Condition Restricting the
Query
FROM Clause Specifies the Table
Partial Syntax
SELECT [ALL | DISTINCT] <select_list>
FROM {<table_source>} [,…n]
WHERE <search_condition>
Specifying Columns
USE northwind
SELECT employeeid, lastname, firstname, title
FROM employees
GO
employeeid lastname firstname title
1 Davolio Nancy Sales Representative
2 Fuller Andrew Vice President, Sales
3 Leverling Janet Sales Representative
4 Peacock Margaret Sales Representative
5 Buchanan Steven Sales Manager
6 Suyama Michael Sales Representative
7 King Robert Sales Representative
8 Callahan Laura Inside Sales Coordinator
9 Dodsworth Anne Sales Representative
Using the WHERE Clause to Specify Rows
USE northwind
SELECT employeeid, lastname, firstname, title
FROM employees
WHERE employeeid = 5
GO
employeeid lastname firstname title
5 Buchanan Steven Sales Manager
Filtering Data
Using Comparison Operators
Using String Comparisons
Using Logical Operators
Retrieving a Range of Values
Using a List of Values as Search Criteria
Retrieving Unknown Values
Using Comparison Operators
Example 1
USE northwind
SELECT lastname, city
FROM employees
WHERE country = 'USA'
GO
lastname city
Davolio Seattle
Fuller Tacoma
Leverling Kirkland
Peacock Redmond
Callahan Seattle
This example retrieves the orderid and customerid columns
with order dates that are older than 8/1/96 from the orders
table.
USE northwind
SELECT orderid, customerid
FROM orders
WHERE orderdate < ’8/1/96’
GO
Using String Comparisons
USE northwind
SELECT companyname
FROM customers
WHERE companyname LIKE '%Restaurant%'
GO
companyname
GROSELLA-Restaurante
Lonesome Pine Restaurant
Tortuga Restaurante
Wild Cards: %, _ ,[] ,[^]
Using Logical Operators
Example 1
USE northwind
SELECT productid, productname, supplierid, unitprice
FROM products
WHERE (productname LIKE 'T%' OR productid = 46)
AND (unitprice > 16.00)
GO
productid productname supplierid unitprice
14 Tofu 6 23.25
29 Thüringer Rostbratwurst 12 123.79
62 Tarte au sucre 29 49.3
The following example retrieves products with product names that begin
with
the letter T or that have a product identification number of 46 and a price
greater than $16.00. Compare the query in Example 1 to that in Example 2.
Notice that because the expressions are grouped differently, the queries are
processed differently and return different result sets.
USE northwind
SELECT productid, productname, supplierid, unitprice
FROM products
WHERE (productname LIKE ’T%’)
OR (productid = 46 AND unitprice > 16.00)
Retrieving a Range of Values
Example 1
USE northwind
SELECT productname, unitprice
FROM products
WHERE unitprice BETWEEN 10 AND 20
GO
productname unitprice
Chai 18
Chang 19
Aniseed Syrup 10
Genen Shouyu 15.5
Pavlova 17.45
Sir Rodney’s Scones 10
… …
Using a List of Values as Search Criteria
Example 1
USE northwind
SELECT companyname, country
FROM suppliers
WHERE country IN ('Japan', 'Italy')
GO
companyname country
Tokyo Traders Japan
Mayumi’s Japan
Formaggi Fortini s.r.l. Italy
Pasta Buttini s.r.l. Italy
Retrieving Unknown Values
USE northwind
SELECT companyname, fax
FROM suppliers
WHERE fax IS NULL
GO
companyname fax
Exotic Liquids NULL
New Orleans Cajun Delights NULL
Tokyo Traders NULL
Cooperativa de Quesos ‘Las Cabras’ NULL
… …
Formatting Result Sets
Sorting Data
Eliminating Duplicate Rows
Changing Column Names
Using Literals
Sorting Data
Example 1
USE northwind
SELECT productid, productname, categoryid, unitprice
FROM products
ORDER BY categoryid, unitprice DESC
GO
productid productname categoryid unitprice
38 Cote de Blaye 1 263.5000
43 Ipoh Coffee 1 46.0000
2 Chang 1 19.0000
… … … …
63 Vegie-spread 2 43.9000
8 Northwoods Cranberry Sauce 2 40.0000
61 Sirop d'érable 2 28.5000
… … … …
Eliminating Duplicate Rows
country
Australia
Brazil
Example 1 Canada
USE northwind Denmark
SELECT DISTINCT country Finland
FROM suppliers France
ORDER BY country Germany
GO Italy
Japan
Netherlands
Norway
Singapore
Spain
Sweden
UK
USA
Changing Column Names
USE northwind
SELECT firstname AS First, lastname AS Last
,employeeid AS 'Employee ID:'
FROM employees
GO
First Last Employee ID:
Nancy Davolio 1
Andrew Fuller 2
Janet Leverling 3
Margaret Peacock 4
Steven Buchanan 5
Michael Suyama 6
Robert King 7
Laura Callahan 8
Anne Dodsworth 9
Using Literals
USE northwind
SELECT firstname, lastname
,'Identification number:', employeeid
FROM employees
GO
First Last Employee ID:
Nancy Davolio Identification Number: 1
Andrew Fuller Identification Number: 2
Janet Leverling Identification Number: 3
Margaret Peacock Identification Number: 4
Steven Buchanan Identification Number: 5
Michael Suyama Identification Number: 6
Robert King Identification Number: 7
Laura Callahan Identification Number: 8
Anne Dodsworth Identification Number: 9
Grouping and Summarizing
Data
Listing the TOP n Values
Example 1
USE northwind
SELECT TOP 5 orderid, productid, quantity
FROM [order details]
ORDER BY quantity DESC
GO
Example 2
USE northwind
SELECT TOP 5 WITH TIES orderid, productid, quantity
FROM [order details]
ORDER BY quantity DESC
GO
Using the GROUP BY Clause
USE northwind USE northwind
SELECT productid, orderid SELECT productid
,quantity ,SUM(quantity) AS total_quantity
FROM orderhist FROM orderhist
GO GROUP BY productid
GO
productid orderid quantity productid total_quantity
1 1 5 1 15
1 1 10 2 35
Only rows that
2 1 10 satisfy the WHERE 3 45
clause are grouped
2 2 25
productid total_quantity
3 1 15
2 35
3 2 30
USE northwind
SELECT productid
,SUM(quantity) AS total_quantity
FROM orderhist
WHERE productid = 2
GROUP BY productid
GO
Using the GROUP BY Clause with the HAVING
Clause
USE northwind USE northwind
SELECT productid, orderid SELECT productid, SUM(quantity)
,quantity AS total_quantity
FROM orderhist FROM orderhist
GO GROUP BY productid
HAVING SUM(quantity)>=30
GO
productid orderid quantity
1 1 5
1 1 10 productid total_quantity
2 1 10 2 35
2 2 25 3 45
3 1 15
3 2 30
Joining Multiple Tables
Using Inner Joins
Example 1
USE northwind
Select categoryname,productname
From categories inner join products
On [Link] = [Link]
GO
Using Outer Joins
Example 1
Select companyname,orderdate
From customers left outer join orders
On [Link] = [Link]
GO
Using Cross Joins
Example 1
Select [Link],[Link]
From suppliers cross join shippers
Joining More Than Two Tables
Example 1
Select productname,orderdate
From products inner join [order details]
On [Link]=[order details]. productid
Inner join orders
On [order details].orderid= [Link]
Where orderdate=‘7/8/96’
GO
Joining a Table to Itself
Example 3
Select [Link],[Link]
From emp as mgr inner join emp as e
On [Link]= [Link]
Combining Multiple Result Sets
Use the UNION Operator to Create a Single Result Set
from Multiple Queries
Each Query Must Have:
Similar data types
Same number of columns
Same column order in select list
USE northwind
SELECT (firstname + ' ' + lastname) AS name
,city, postalcode
FROM employees
UNION
SELECT companyname, city, postalcode
FROM customers
GO
Modifying Data
Inserting a Row of Data by Values
Must Adhere to Destination Constraints or the
INSERT Transaction Fails
Use a Column List to Specify Destination Columns
Specify a Corresponding List of Values
USE northwind
INSERT customers
(customerid, companyname, contactname, contacttitle
,address, city, region, postalcode, country, phone
,fax)
VALUES ('PECOF', 'Pecos Coffee Company', 'Michael Dunn'
,'Owner', '1900 Oak Street', 'Vancouver', 'BC'
,'V3F 2K1', 'Canada', '(604) 555-3392'
,'(604) 555-7293')
GO
Inserting a Row of Data by Values
Must Adhere to Destination Constraints or the
INSERT Transaction Fails
Use a Column List to Specify Destination Columns
Specify a Corresponding List of Values
USE northwind
INSERT customers
(customerid, companyname, contactname, contacttitle
,address, city, region, postalcode, country, phone
,fax)
VALUES ('PECOF', 'Pecos Coffee Company', 'Michael Dunn'
,'Owner', '1900 Oak Street', 'Vancouver', 'BC'
,'V3F 2K1', 'Canada', '(604) 555-3392'
,'(604) 555-7293')
GO
Using the INSERT…SELECT Statement
All Rows That Satisfy the SELECT Statement Are Inserted
Verify That the Table That Receives New Row Exists
Ensure That Data Types Are Compatible
Determine Whether Default Values Exist or Whether Null
Values Are Allowed
USE northwind
INSERT customers
SELECT substring(firstname, 1, 3)
+ substring (lastname, 1, 2)
,lastname, firstname, title, address, city
,region, postalcode, country, homephone, NULL
FROM employees
GO
Creating a Table Using the SELECT INTO Statement
Use to Create a Table and Insert Rows into the Table
in a Single Operation
Create a Local or Global Temporary Table
Create Column Alias or Specify Column Names in the
Select List for New Table
USE northwind
SELECT productname AS products
,unitprice AS price
,(unitprice * 1.1) AS tax
INTO #pricetable
FROM products
GO
Using the DELETE Statement
The DELETE statement removes one or more rows in a
table according to the WHERE clause condition, if
specified
Each Deleted Row Is Logged in the Transaction Log
USE northwind
delete emp where empname=‘heba’
GO
Deleting Rows Based on Other Tables
delete [order details]
from orders as o inner join [order details]as od
on [Link]=[Link]
where orderdate='4/7/98'
Updating Data
USE northwind
UPDATE products
SET unitprice = (unitprice * 1.1)
GO
Updating Rows Based on Other Tables
update products
set unitprice =unitprice+2
from products inner join suppliers
on [Link]=[Link]
where country='usa'
Ex: Create Database
create database test
on primary
(
name='test_data',
filename='e:\[Link]',
size=10mb,
filegrowth=10%,
maxsize=40mb
)
log on
(
name='test_log',
filename='e:\[Link]',
size=10mb,
filegrowth=10%,
maxsize=40mb
)
Transaction Logging
1 Data modification is
sent by application
Modification is recorded
Buffer
3 in transaction log on disk
Cache
Disk
Data pages are located in, Disk
2 or read into, buffer cache
and modified
Checkpoint writes
4 committed transactions
to database
Tip: Place log on separate drive for performance
Creating Secondary Files
create database test
on primary
(
name='test_data',
filename='d:\[Link]',
size=10mb,
filegrowth=10%,
maxsize=40mb
),
(
name='test_sec',
filename='d:\[Link]',
size=10mb,
filegrowth=10%,
maxsize=40mb
)
log on
(
name='test_log',
filename='d:\[Link]',
size=10mb,
filegrowth=10%,
maxsize=40mb
)
Creating Filegroups
What Are Filegroups?
When to Create File groups
Practice: Creating File groups
When to Create Filegroups
Use multiple files in a single filegroup for performance
Use multiple filegroups to control data placement
Creating File Groups
create database test
on primary
(
name='test_data',
filename='d:\[Link]',
size=10mb,
filegrowth=10%,
maxsize=40mb
),
filegroup fg1
(
name='test_sec',
filename='d:\[Link]',
size=10mb,
filegrowth=10%,
maxsize=40mb
)
log on
(
name='test_log',
filename='d:\[Link]',
size=10mb,
filegrowth=10%,
maxsize=40mb
)
Adding Objects To File Groups
create table emp
(empID int ,
empName varchar(50))
on fg1
Modifying File Groups:
alter database test
modify filegroup fg1 default
Adding File Groups to an existed DataBase:
alter database test
add filegroup fg2
Adding Secondary files to file groups:
alter database test
add file
(
name='test_sec2',
filename='d:\[Link]',
size=10mb,
filegrowth=10%,
maxsize=40mb
)
to filegroup fg2
Practice: Creating Filegroups
In this practice, you will:
Create a filegroup by using SQL Server
Management Studio
Create a filegroup by using Transact-SQL
Lesson 3: Creating Schemas
What Are Schemas?
How Object Name Resolution Works
Practice: Creating a Schema
What Are Schemas?
Namespaces for database objects
Person
Contact
([Link])
Sales
Customer
([Link])
dbo
ErrorLog AdventureWorks
([Link])
Advantage of schema
Greater flexibility when organizing database objects into
namespaces, because the grouping of objects into
schemas does not depend on object ownership.
Simpler permission management, because permission
can be granted at the schema scope as well as on the
individual objects.
Improved manageability, because dropping a user does
not necessitate the renaming of all objects that the user
owns.
How Object Name Resolution Works
Person
SELECT * FROM Contact
Lance
(Default schema = Person)
Contact
SELECT * FROM [Link]
Sales
SELECT * FROM ErrorLog
Anders SELECT * FROM Contact
(Default schema = Sales)
dbo
ErrorLog
What Are System-Supplied Data Types?
Category Data types
Integer int 4bytes, bigint 8 bytes, smallint 2bytes, tinyint 1byte
Exact decimal, numeric ( 38 digits after the decimal point)
Numeric
Approximate Float(15), real(7) digits after the decimal point
Monetary Money(8), smallmoney(4) digits after the decimal point
Date and time Datetime (milliseconds), smalldatetime(minutes)
Char (0-8000 chars), varchar (0-8000 chars)
Non-Unicode
Character , text (0-2GB)
Unicode Nchar(0-4000 chars), nvarchar(0-4000)
Image image
Global identifier uniqueidentifier
XML xml
Special bit
What Are Alias Data Types?
Based on system-supplied types
Used for common data elements with a specific
format
Created by using the CREATE TYPE statement
CREATE TYPE [Link]
FROM char(2)
NULL
• Create a database called “test” with 2 a file groups called
“F1”&”F2” on different hard disk drives(D:/ and e:/)
• Create an alias data type called “mydatatype” that is DateTime ,
not null.
• Create a table called “Orders” contains “Order_ID” as a primary
key int, “Order_Date” which uses the alias data type,
“Customer_Name” with type varchar(50). the table itself will be
on file group “F2”).
