Lesson 1 : What is SSRS?

Lesson 1 : What is SSRS?

SSRS or SQL server Reporting Services which is also called Microsoft SQL reporting, is a
component of the Microsoft BI stack.
The Microsoft Business Intelligence Stack mainly consists of SQL Database Engine,
SSRS, SSAS, SSIS and other tools
SSRS is a set of readymade tools, that helps you to create, deploy and manage reports.
You can extend reporting functionality using C# and VB as programming languages.
Microsoft SSRS or Business Intelligence SSRS, lets you create very rich reports
(Tabular/Graphical/Interactive/free-form) from various datasources with rich data
visualization (Charts, Maps, sparklines).
All these reports can be viewed via a web browsers.
SSRS allows are reports to be exported in various formats (Excel, PDF, word etc)
SSRS allows reports to be delivered via emails or dropped to a share location in an
automated fashion.
SSRS provides a host of security features, in order to control, who sees which reports
etc.
You can download some samples from here.
Next, lets look at how to install SSRS and get you started.

Lesson 2 : SQL Server Reporting Services Installation

Lesson 2 : SQL Server Reporting Services

Installation
Lets move forward with getting to know how to install sql server 2008 reporting services. Please
note, most of the steps are the same for installing sql server reporting services 2005.
Below are the steps to install SQL reporting services 2008 with express Database.
Step 1 : Download the 64 bit or 32 bit version of SQL 2008 Express with advances services from
here .
Step 2: Right Click on the downloaded file and "Run as Administrator".

Step 3: The files will extract itself.

Step 4: In case you get a pop-up saying "This program has compatibility issues", click Run Program.

Step 5: You should be getting a progress screen.

Step 6: The SQL server Installation Center should fire up.

Step 7 : Select installation on right hand tab and click on "New SQL server stand -alone installation"

Step 8: SQL server will load the setup files.

Step 9 : Click OK, it should take you to next page.

Step 10 : Click Install.

Step 11: Click next, then again next.

Step 12 : Click next

Step 13 : In feature selection, make sure you select 'Database Engine Services' and 'Reporting
Services'

Step 14 : Next, select Named Instance and give it any name (you can also choose Default
instance)
Step 15 : Click next

Step 16 : Select Mixed Mode --> enter in any password --> and specify SQL server admins.

Step 17 : Click next, and select 'Install, but do not configure under Reporting Services configuration.

Step 18 : Click Next

Step 19 : Click Next

Step 20 : Click Install.

Step 21: After Installation is done, click next

Step 22 : Click Next.

Your installation is almost complete. In order to start using SSRS, you need to perform a few more
steps.
Step 23: Click Start --> All programs --> Microsoft SQL 2008 --> Configuration Tools --> Reporting
Services Configuration --> select your instance
Step 24 : Click web service URL on the left hand side and click the apply button.
Step 25 : Click database on the left hand side, select change database --> create a new report
server database and complete the steps.
Step 26 : Click report manager URL and click apply (Note down the URL displayed. It should be of
the form http://machinename/reports)
Step 27: open IE and hit the noted down URL
Step 28 : You should get a screen like this.

That's all. We are all set. Let's install some sample databases and get started learning SSRS 2008

Lesson 3: Installing Sample databases

(Adventure Works)
Next, lets install some sample databases for us to work with. One of the most commonly used
databases is the SQL adventureworks database. SQL server adventureworks database download is
available here.
Once you download the database, lets look at how to setup sql database.
Step 1: Install your downloaded database (sql server 2008 sample databases download), by
choosing the correct instance name.

Step 2: After installation, open SSMS --> your instance name, then check if the below databases are
visible.

Step 3: You can also install some sample reports from here
This video, gives a brief demo :
http://www.youtube.com/watch?feature=player_embedded&v=Z79S2AGinkU

Lesson 4 : Creating your first SSRS

report.

Now that you are all set with installing SSRS 2008 and some SQL server adventure works database,
Let us proceed with creating our first SSRS report, deploying the same and viewing in the web
browser. For report development , we will use Microsoft Business Intelligence Development studio.
Quite often people ask me, how to install Business intelligence Development studio. Installing

business intelligence Development studio, is as simple as adding components to an existing SQL

installation. Just run the SQL setup again, and choose add features to an existing installation, and
choose select the required features you need.
Lets follow a step by step process to create your first Business Intelligence Report.
Step 1: Open Business Intelligence Development Studio (BIDS) (Start --> Microsoft SQL server 2008
--> SQL server Business Intelligence studio
Step 2: Click File --> New project --> Report Server Project Wizard. Give any name and location.

Step 3: Once you click OK, you will be presented with the following screen.

Step 4 : Click Next. Now configure your connection string. I have chosen the type as 'Microsoft SQL
server'

Step 5 : In this step, you need to enter the query for the data, that you need the SSRS report to be
populated with. You can also use the query builder.

Step 6 : Click on query builder, and click on the right most top icon, to add tables

Step 7: Select the necessary table, and verify your query, then click the (!) icon to see the result of
the query after execution.

Step 8: If everything looks good, click on Next

Step 9 : Click Next

Step 10: Add the fields, as shown in the below screenshot.

Step 11 : Click next

Step 12 : Choose a style, and click next.

Step 13 : Choose a deployment location. It will be in the form (http://machinename/Reportserver)

Step 14 : Click Next.

Step 15 : You can preview the report in Business Intelligence Development Studio.

Step 16 : Now, Right click the project , and click deploy.

Step 17 : After successfully deploying the project, open up IE, and hit http://machinename/reports

Step 18 : Click on your report to open it.

Congratulations! You have made your first SSRS report.

Lesson 5: Knowing your development

env. (BIDS)

Alright, now we have published our first simple SSRS report using the Reporting Wizard. We
deployed the report, and viewed it via a web browser. Next, we will understand, how to develop
reports, without the custom report creation wizard. This will give much better control, over
developing custom reports. (Eg. you need to add custom calculations, embed maps etc.
When you fire up, Microsoft Business Intelligence Development studio - it looks like this.

The Left side portion, is where you set up your data connections, what parameters your report
should use, the queries, that return data for your report etc.
The middle portion, is where you actually develop the report. Eg. Setup tabular reports, matrix
reports or embed maps, graphics etc
The Right side portion, is called the solution explorer. It lists down all reports (also called RDL's or
report definition language) to be displayed. Report definition languages are pure XML file.

Pressing control+Alt+X brings up the toolbox.

Here you see a number of tools, that aid you in your report development. You simply need to drag
and drop the tools, that you need in your report, and then configure it accordingly.
We will have a look at some of them in the coming lessons.

Lesson 6: Making your first SSRS report (nonwizard) method

Lets proceed towards making a simple SSRS report.
This time, we will not be using the wizard to create our report, but we will make use of the toolbox,
and report designer to create our report.
Most of the report development that you do using Microsoft SQL server reporting services, would be
using the non-wizard method. This allows more flexibility to your report development.
Step 1: Open Microsoft Business Intelligence studio, and create a report server project.
Step 2: Next lets configure the datasources and datasets.

Step 3: right click on the "Data sources" folder and add a new data source.
Set the connection string to point to your new adventure works database.
Step 4: Once that is done, right click on the datasets folder, to add a dataset.
This is basically the place, where you will configure your query that returns some data, that you want
to be displayed in the report.
Select the datasource, that you just created and type in a query.
You could also use the "Query Designer" if you want.

After eveything is done, click ok

Step 5: Now from the toolbox, drag and drop a table control.

Step 6: From the "Report Data", drag and drop the columns defined in the dataset, that you want in
the report.

Step 7: That's it, click on the preview tab and congratulations, you have made your first Microsoft
SQL server report using a non-wizard method.

Lesson 7: Creating an SSRS

parameterized report
Lets proceed towards making a simple SSRS report with parameters.
This lesson, should give you an idea, as to how to control the data in the reports using parameters.
Lets consider the same report, that we made in Lesson 6.
We are going to expose the postal code as a parameter. This means, we will see the address of only
the entered postal code.
Step 1: Modify the query in the dataset, to make PostalCode as a parameter

Step 2: Once you click OK, go to the parameters folder in "Report Data" - you will find that
@postalcode automatically appears in the "Parameters" Fields.
Step 3: Lets explore more about the parameter. Double click on @postalcode.
This is place, where you configure, how your parameter should behave - should it allow blank values
or null values etc.
You can configure the visibility, data-types, default values etc.
Lets leave it as it is for now.

Step 4: Click preview, and enter in a postal code (91370 for example)

Note: Observe that the parameter appears as a text box here.

If you want pre-configured values (in a dropdown), double click on the parameter in "ReportData" -->
Parameters (here, @postalcode) and click "Available Values"/"Default value" and click "Get values
from a query". You can choose your dataset and corresponding column. Create multiple datasets to
suffice your parameter value requirements.

Lesson 8: SSRS Expressions

Next, lets move on to creating expressions in SQL server reports.
Expressions can be used for a number of things. The best part is that it operates on a cell by cell
basis.
Hence, you can write expressions to manipulate the data, or change the properties of cell etc. Even
if you are dealing with graphs and maps, many of the properties, you can control using expressions
(like color of graph, some values etc).
Basically you would want to write expressions, if you need to manipulate properties/data at runtime
Lets look at a very simple example.

Step 1: Create a report server project and connect to Adventure Works Database.
Step 2: Add the SalesOrderDetail table with a couple of columns to the Report.

Step 3: In the toolbox, add the table control to the report.

Step 4: Add 3 columns to the report - SalesOrderID, SalesOrderDetailID, OrderQty, LineTotal.

Step 5: Now lets write some expressions. We will display anything in LineTotal cell as Green, if the
number is above 2000, else we will display in Red.
Right click on the cell , and select text-box properties.

Step 6: Next to the colr dropdown, there is an Fx button.

Write the below expression.

Step 7 : Click OK and then on the Preview button.

You should be getting the final output as displayed below.

Step 8: Now lets write some expressions to manipulate some data.

Right click on the OrderQuantity Cell and click on expressions.
Enter the below expression and click OK.

Step 9: Click on the preview tab, you should get the below output.

I have demonstrated a very simple use of expressions. However , you can write very complex
expressions depending on your business needs. For instance, you can write expressions, to
calculate the standard deviation of so and so , and manipulate results accordingly.
Many time, you need to write expressions to deal with Dates and times
For information on: http://msdn.microsoft.com/en-us/library/ms157328.aspx

Lesson 9: Creating a SSRS tabular

report
Creating SSRS tabular reports, is one of the simplest and easiest task.
Tabular reports implies, just simple data representation using tables.
SSRS makes creating tabular report super simple - just drag and drop data elements.

It gives you the ability to add totals etc, as we do in excel.

Lets look at some basic steps to create a SSRS tabular report.
Step 1:
Create an SSRS report server project.
Step 2:
Next add a blank report to your project.
Step 3:
Next we need to create a Dataset.
In the Report data tab, right click on Datasets and complete the wizard.
I am using Adventure works database for this demo.

Step 4: Add a query to get some data to your report.

Step 5: Now drag and drop a table control from the tool box (Left hand pane)
In the table control, drag and drop some fields from your dataset.
Once done, your Design pane should look something like this.

Step 6: Click on the preview tab, and you should be able to see a very basic tabular report.
Step 7: You could also add some totals to your report.
In order to add totals, right click on the cell where you would like to add the totals, click on 'Add
Total'.
Click preview and you should be able to see a tabular report with totals.

Lesson 10: Formatting an SSRS report.

In this tutorial, lets look at how to format an SSRS report.
SSRS, just like Microsoft word or any other text tool, provides some WYSIWYG editor.
That means at design you can format the text (make it bold, italicize etc).
SSRS also has some ways where-in you can format the display of dates, currency etc.
Lets look at some of them in this tutorial.
Step 1:
Create a basic tabular report, using Adventure works database.
The report would look something like this.

Making the headings bold, Italics, underlined.

Step 2:
In the design view, select the cells in the report, where you want to apply the formatting.
You could either use any of the shortcut keys (like cntrl + B, cntrl + I, cntrl + U) on you can use the
icons in the toolbar.

Similarly try adding some background color or text color.

Play around with bullets and numbering.
The same toolbar, also allows you to set the alignment of text.
Step 3:
Some of the features are given in the screenshot below.

Step 4: Lets explore some other formatting features.

Lets create a report, which has some dates and some currency.
Preferably you can use the following query from AdventureWorks database.

SELECT

TOP (100) *

FROM

Sales.SalesOrderDetail

Step 5: Once you create a report, using the above query, your report would look something like this.

Step 6: Now lets play around with some Currency formatting and date formatting.
Right click on the unit price cell.

Click on text box properties.

On the left hand pane, click Number --> Currency

Step 7: Now lets do some formatting for dates.

Right click on ModifiedDate --> Number --> Date

Step 8: After choosing your money and date format, click preview and report should look something
like this.

Lesson 11: SSRS grouping and totals

Many times we need to group the data in order to drill down further.
To put it simply, imagine you have a report which gives you sales amount for each of the continent.
You might want to drill down into each of these continents and see the sales amount for each
country in the continent.
Next, you might want to drill down further to see the sales amount for each state in a country.
Next you might want to drill down from yearly sales to say quaterly...and so on a so forth.
This is where we introduce Groups and drill downs If you have used the Group By clause, for
aggregation, you might know, what I am talking about.
In any case, lets take an example from the adventure works database.
Lets consider 4 tables from AdventureWorks 2008 R2
1. Fact Internet Sales
2. DimDate
3. DimSalesTerritory
4. DimProduct

5. DimCustomer.
If you run the below query in SQL Server, you will get the following:
Query:

SELECT D.CalendarYear AS [Year]

,D.CalendarQuarter AS [Quarter]
,D.EnglishMonthName AS [Month]
,D.FullDateAlternateKey AS [Date]
,B.EnglishProductName AS [ProductName]
,C.FirstName + ' ' + LastName AS [CustomerName]
,ST.SalesTerritoryRegion AS [SalesRegion]
,ST.SalesTerritoryCountry AS [SalesCountry]
,A.SalesOrderNumber AS [OrderNumber]
,A.SalesAmount
FROM FactInternetSales A
JOIN DimProduct B
ON B.ProductKey = A.ProductKey
JOIN DimCustomer C
ON C.CustomerKey = A.CustomerKey
JOIN DimDate D
ON D.DateKey = A.OrderDateKey
JOIN DimSalesTerritory ST
ON ST.SalesTerritoryKey = A.SalesTerritoryKey

Now we have some data with us to work with.

Lets use this data in our report and group the data.
Then we will introduce drill through in our reports.
Step 1: Make a report, with a table control by dragging and dropping CustomerName, OrderNumber
and SalesAmount.
Hit preview - Your report should look something like this.

Step 2:
Now lets Add a product name, and lets put the customer Name, order number and Sales Amount
under it.
Right click on the grouping area --> Add group --> Parent Group --> Group by 'Product Name' (Also
Add a group header)

Hit preview. Your report should look something like this.

So lets add more groupings.

Year--Quarter--Month--Date--SalesRegion--SalesCountry--ProductName-Details
So your design view should look something like this:

And your output should look something like this:

Lesson 12: Using SSRS web services

SSRS
exposes
Its
bascially
It
1.
2.

all
a

Mainly
1.
2.

are
2
Using

Lets

there

functionality
of
reporting
XML
web
service
provides
Report
Report
ways

to

services
through
and
has
a
2

use
the
Microsoft.NET

SSRS

Using
look

at

an

example

to

use

SSRS

web

services

using

Web
service.
SOAP
API.
endpoints:
execution
Management
web

services:
Framework
RS.exe

Microsoft

C#.

Step
1:
Create a Visual studio 2010 console application (you can use any Visual Studio version)
I
am
calling
the
application
MyFirstSSRSWebService.
Step
Next click add service reference:

2:

Step
3:
Click advanced--> add web reference -- url - http://localhost/reportserver/reportservice2005.asmx
(replace
localhost
with
your
report
server
name).
Then click add reference

Step
You should be seeing the SSRS webservice that you just added, in the solution explorer:

4:

Step
5
I have created a report called MyFirstReport in my local, and given it a small description.

Step
Now

lets

Copy

try

to programmatically display
paste

this

the

report

name

following

using
using

and

the

c#

6:
description
code:

System;
MyFirstSSRSWebService.myPC;

namespace
{

MyFirstSSRSWebService
class

rs.Url

Program
{
static
void
Main(string[]
args)
{
ReportingService2005
rs
=
new
ReportingService2005();
rs.Credentials
=
System.Net.CredentialCache.DefaultCredentials;
= "http://localhost/reportserver_SSRSexpress/reportservice2005.asmx";
Property

Property

Property[]

name
=
name.Name

description
description.Name

properties
=
properties[0]
properties[1]

new
=

Property();
"Name";

new

Property();
"Description";

=
new
=
=

Property[2];
name;
description;
try
{

Property[]

foreach

returnProperties
=
rs.GetProperties(
"/HelloWorld/MyFirstReport",
properties);
(Property

Console.WriteLine(p.Name

catch

in
+

returnProperties)
{
":
"
+
p.Value);
}
Console.ReadKey();
}
(Exception

e)
{
Console.WriteLine(e.Message);
Console.ReadKey();
}
}
}

Step 7: Run the code, you should be seeing the following output

Likewise, I would encourage you to explore other methods of this web service as well.
One common activity people do, is to programmatically render a report in excel/pdf etc

Lesson 13: SSRS Subsriptions - Email

Based

Subscriptions are delivery mechanisms for delivering a report to an user.

Subscriptions allow us to schedule a report, to be run and delivered in many formats (Excel/PDF etc)
as an email or to be dropped in a file share at any pre-defined time.
There are mainly 2 types of subscriptions:
1.Standard
2.Data-Driven Subscription.
It also has 2 delivery mechanisms:
1.Email based
2.File Share based.
Lets discuss the email based subscription.
This demo is based on SSRS 2008 R2
Step 1:After I navigate to my ReportServer page, I should be seeing something like this:

Step 2: Select the report, and click subscribe.

Step 3: if the credentials are not stored, you will get the following error message.

Navigate again to the report and click manage:

Goto the data sources tab, and enter the credentials:

Click on apply and then click on subscriptions tab.

You should be seeing a screen something like this:

Click on New subscription.

you should be presented with a screen like this:

Select delivered by: Email

Type in To, CC, BCC
Select a report rendering format.
Select a schedule.

Click OK
The report should be available in your inbox, at your specified time.

Lesson 14: SSRS Matrix Reports

Tablix is a new feature introduced in SSRS 2008.
Tablix combines the features of a tabular report and cross tab features.
A tablix report, display the report data in rows and columns, and allows us to organize the data
in aggregated groups.
It also allows us to add drill down features to get into details of a report.
Tablix = Table + Matrix.
So essentially it allows to add pivot like features to our SSRS reports.
Lets take an example.
Step 1. Create a blank report
Step 2. Add a connection to the Adventure works database
Step 3. Create a dataset with the following query:
SELECT Sales.SalesTerritory.Name as Region,
Sales.SalesTerritory.CountryRegionCode,
Sales.SalesTerritory.[Group] as Territory,
Year(Sales.SalesOrderHeader.DueDate) as Year,
Month(Sales.SalesOrderHeader.DueDate) as Month,
Sales.SalesOrderHeader.TotalDue
FROM Sales.SalesTerritory
INNER JOIN Sales.SalesOrderHeader
ON Sales.SalesTerritory.TerritoryID = Sales.SalesOrderHeader.TerritoryID

Step 4: Drag and drop the Matrix control from the ToolBox.

Step 5: Drag and drop the territory to the rows and Year to the column:

Step 6: ON clicking Preview, you should be able to get something like this:

Step 7: However the report, does not have any aggregated data.
So lets drag and drop the TotalDue field to the 'Data' area in the report.

Step 8: On clicking preview, you should be able to see the aggregated report as below:

Go ahead and add some currency formatting to the data.

Lets look at groupings and drilldowns in the next tutorial.

Lesson 15: Drilldown Matrix Reports

In the last tutorial, we saw how to create a matrix report.
Matrix reports are very useful, and it allows total flexibility too.
For instance we could easily convert a matrix report to tabular.
In this tutorial, lets see how to add the drill down feature to an existing matrix report.
Lets take the same matrix report, which we created in the last tutorial.
This is how it looked:

Lets add some child groupings to this report, and explore some visibility toggling features.
Step 1: Right click on the Territory field and add a child group:

Step 2: Choose 'CountryRegionCode' to group by:

Step 3: Your design view would look something like this:

Step 4: Hit preview.

Congratulations - your matrix report is ready.

Lets proceed to see some visibility toggling features
Step 5: Click on the row groups - 'CountryRegionRegion' and select the group properties:

Step 6: In the visibility pane, check 'Hide' for 'When the report is initially run'
Also, check the 'display can be toggled by this report item' for Territory

Step 7: Select OK and hit preview.

You should be able to toggle the report.

Lesson 16: SSRS Subreports

SSRS Subreports are extremely helpful, when we need to embed multiple reports in a single report.
The
main
report
serves
as
a
container
for
multiple
sub-reports.
SSRS gives us complete control over what parameters to be passed to the sub report.
Many
times
this
is
useful
in
matrix
reports
too.
Lets

look

at

an

example

of

sub-report.

We
are
going
to
create
a
main
report
with
1
parameter.
As soon as someone selects a parameter, relevant data is displayed + the sub report is also filtered

and

displayed

in

the

main

report

itself.

Step 1: Create a main report (Steps to create a basic report can be found in the previous tutorials)
I
have
the
following
dataset:
SELECT

[TerritoryID]
,[Name]
,[CountryRegionCode]
,[Group]
,[SalesYTD]
,[SalesLastYear]
,[CostYTD]
,[CostLastYear]
,[rowguid]
,[ModifiedDate]
[AdventureWorks].[Sales].[SalesTerritory]

FROM
Where

Step

2:

CountryRegionCode

Create

Step 3: Your Main report would look something like this.

parameter

@CRC

called

CRC.

Step 4: Now lets add a sub-report and

Drag and drop a sub-report control from the toolbox.

Step 5: Now click on the sub-report properties.

pass

in

the

same

parameter

to

it.

Step 6: Lets us the previous tablix report that we created as our sub-report.

Step
Choose the parameters tab, and configure the parameters

7:

Step
8:
click
OK
You should be able to see the main and the sub-report.

and

hit

preview.

Lesson 17: Managing SSRS Security

As soon as you install SSRS 2008 R2, in your server and hit http:///reports, by default the the system
administrators get access to the report manager.
But if you wish to let other people see the reports that you deploy, you need to explicitly provide
access to these reports.
Providing access to reports is very simple and can be done at an item level( report level) or a folder
level.
Lets look at a very simple example.
I have installed a SSRS 2008 R2 report server, on my local machine and have created a folder
called Sandbox, in which i have added a report called Hello World.

By Default all admins in the system can access this report.

Lets say, we add more reports to this folder and we need to grant access to these reports.
This can be done, by clicking on Folder Settings and then clicking on security

Click on New role assignment.

This is the place where you define your role assignments.

If you need to grant all users of your domain access, then you may need to add
'Domain\DomainUSERS' and assign appropriate roles.
There are mainly 5 types of roles, that you can assign to an user or a group.
These roles are:
a. Browser
b. Content Manager
c. My Reports
d. Publisher
e. Report Builder.
Descriptions for each of these roles are provided in the UI itself.
If you wish to add just a particular user of your domain for your report, type the username and select
the appropriate role for the same.
Click OK, and your security is set

Lesson 18: SQL/SSRS Interview

questions
I thought of blogging some SQL/SSRS interview questions.
Below are some.
I will add more, when I complete the compilation
1. What is OLTP(Online Transaction Processing)?

OLTP stands for Online Transaction Processing.

It is mainly used, when you have frequent inserts/deletes/updates.
OLTP will have normalized tables.

OLAP stands for Online Analytical processing.

OLAP is used, when we need historical data to be preserved and do not have frequent
updates/deletes/inserts.
Tables are denormalized, and are used for multi-dimensional analysis/modelling.
OLAP forms the basis for data mining and knowledge discovery
2. What is normalization and what are the different forms of normalization?

Normalization is a way to arrange data in your database.

Primary goal being to reduce redundancy of data.
There are 6 normal forms (http://en.wikipedia.org/wiki/Database_normalization)
Generally most of the databases are normalised upto 3 NF (NF normal form)
First Normal Form (1NF): No repeating columns or groups of columns
Second Normal Form (2NF): No partial dependencies on a concatenated key.
Third Normal Form (3NF): No dependencies on non-key attributes
Fourth normal Form (4NF) : Isolate independant multiple relationships
Fifth normal Form (5NF): Isolate semantically related multiple relationships
3.What are views?

A view is a kind of virtual table or a stored query.

The result set of the SELECT statement forms the virtual table returned by the view.

Example:

CREATE VIEW view_name AS

SELECT column_name(s)
FROM table_name
WHERE condition
Example: http://www.youtube.com/watch?v=to_0_bODY2Y
4. What are indexed views?

A view with a unique clustered index is an indexed view.

Example:

CREATE TABLE sample_table(

A

INT PRIMARY KEY,

B int
)
GO

CREATE VIEW sample_view WITH SCHEMABINDING AS

SELECT A, B
FROM dbo.sample_table
GO

CREATE UNIQUE CLUSTERED INDEX idx_MyView ON sample_view(B)

5. What are stored procedures?

Stored procedures are group of SQL statements that are created previously and stored.
Stored procedures can accept parameters, and also have output parameters.
Permisions can be modifed for stored procedures.

Example:

CREATE PROCEDURE sp_Sample

@param varchar(10)
AS
SELECT A, B
FROM sometable
WHERE B = @param

EXECUTE sp_Sample ABC

Example: http://www.youtube.com/watch?v=jx63oKvU8Iw
6. Give an example for Stored Procedures with parameters and output parameters.

CREATE PROCEDURE GetImmediateManager

@employeeID INT,
@managerID INT OUTPUT
AS
BEGIN
SELECT @managerID = ManagerID
FROM HumanResources.Employee
WHERE EmployeeID = @employeeID
END
7.What is xml data type?
The xml data type lets you store XML documents and fragments in a SQL Server database.

Example:
http://www.youtube.com/watch?v=OO-AnA_52Og

8. Explain filestream with an Example.

FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing
varbinary(max) binary large object (BLOB)

data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up
FILESTREAM data.

Example: http://www.youtube.com/watch?v=ONYjKN21aIw
9.Explain stored procedure, function and the difference between them?
Stored procedures are group of SQL statements that are created previously and stored.

User defined function is a database object which has one or more sql statements , which can accept zero
or more parameters and return
either a value or table.

Difference:
1. Stored Procedure can have output parameters UDF s cannot.
2. Stored Procedures return integers UDFs can return scalar or tables.
3. Stored procedures can have Insert/Delete/update Statement UDFs cannot
4. Stored Procedures are compiled at compile time UDFs are compiled at runtime.
5. Stored procedure cannot be used in the select/where/having clause UDFs can be

Example:
Stored procedures: https://www.youtube.com/watch?v=jx63oKvU8Iw
User Defined Functions: https://www.youtube.com/watch?v=vBg216_AODA
10. When do we use a having clause and when do we use a where clause?
Having Clause applies to groups hence used with groupby clause.
Where is applied row by row

Example:
SELECT A, AVG(B)
FROM some_table
GROUP BY A
HAVING AVG(B) >10
11.Explain user defined functions and the difference forms of UDF
https://www.youtube.com/watch?v=vBg216_AODA

12. What are recursive stored procedures?

Stored Procedures calling itself in a repetative fashion are called Recursive stored procedures.
13.What are the different error handling techniques?
https://www.youtube.com/watch?v=PUYlJHMhoAs
14. Write a query to find the second/third/nth largest Salary in a table: Emp(EmpID,
EmpName, Salary)
SELECT TOP 1 salary
FROM (SELECT DISTINCT TOP 2 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

15. What is a trigger? What are the different types of them?

Answer: A trigger is an event (set of DDL statements) that is automatically fired when some sort of an
insert/delete/update occurs on a
database.
Triggers can be nested.

Types of triggers:
DDL trigger
DML trigger (instead of/after trigger)

Example:
CREATE TRIGGER T1

ON TABLE_NAME
AFTER INSERT
AS
BEGIN
ROLLBACK TRANSACTION
END
GO
16.Explain linked server with an example.
A Linked Server is a connection to an external data source. The remote data source can be DB2, Oracle, Access or any other data source
that uses an OLE DB provider.

Example:

sp_addlinkedserver [ @server = ] server

[ , [ @srvproduct = ] product_name ]
[ , [ @provider = ] provider_name ]
[ , [ @datasrc = ] data_source ]
[ , [ @location = ] location ]
[ , [ @provstr = ] provider_string ]
[ , [ @catalog = ] catalog ]
17.Explain collation and collation sensitivity
Collation:

Collation controls the way string values are sorted. The default collation sorts alphabetically

using the standard Latin character set. Other collations will sort in different orders Collation sensitivity:

Width sensitivity:When

a single-byte character (half-width) and the same character when represented as a

double-byte character (full-width) are treated differently then it is width sensitive

Accent sensitivity:

If a and , o and are treated in the same way, then it is accent-insensitive.

Case sensitivity:

If A and a, B and b, etc. are treated in the same way then it is case-insensitive. SQL

distinguishes using ASCII values of A(65) and a(97)

Kana Sensitivity: When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive

18.What is primary key/Unique Key and difference between them?

Primary key: The key of a relational table that uniquely identifies each record in the table. Primary Key doesnt allow NULLS

Clustered Indexes are created on Primary Keys.

Unique Key: Unique Key enforces uniqueness of the column on which they are defined. Unique Key allows one NULL value

Non-Clustered indexes are created on Unique Keys.

19.Explain 1:1, 1:m, m:m relationships1:1 -In a one-to-one relationship, a row in table A can have no more than one matching
row in table B, and vice versa. A one-to-one relationship is created if both of the related columns are primary keys or have unique
constraints.

1:many In 1:many type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one
matching row in table A.

many:many: In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa. 20.Explain

delete and truncate statement and the differenceDelete: The DELETE Statement is used to delete rows
from a table.
DELETE is a logged operation on a per row basis

Example:

DELETE FROM table_name [WHERE condition];

Truncate: The TRUNCATE command is used to delete all the rows from the table and free the space containing the table

21.Explain the merge statement with an example.

Merge Statements perform insert, update, or delete operations on a target table based on the results of a
join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting
rows in one table based on differences found in the other table.
22.What is an index? What are the different types of indexes? what is the difference between them?

An index can be created in a table to find data more quickly and efficiently. it is a structure within SQL that is used to quickly locate specific
rows within a table.

CLUSTERED INDEX: Clustered indexes define the logical order of the table. The leaf level of the clustered index has the actual data pages
of the table. Because of this there can only be one clustered index per table.

A table that does not have a clustered index is referred to as a heap

NON-CLUSTERED INDEX: Here, the leaf level of a nonclustered index has a pointer as part of each
index row. That pointer is either the clustered index key in the cases where the base table has a clustered
index or the Row Identifier in the cases where the table is a heap
23.Difference between char and nvarchar / char and varchar data-type?

char [ ( n ) ] : Fixed-length, non-Unicode string data. n defines the string length and must be a value from
1 through 8,000. The storage size is n bytes.
varchar [ ( n | max ) ] : Variable-length, non-Unicode string data. n defines the string length and can be a
value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB).
nvarchar [ ( n | max ) ] : Variable-length Unicode string data. n defines the string length and can be a
value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB).
24.What are joins? What are the different types of joins?
Joins are used to query data from more than 2 tables, based on a relationship between columns in these tables.

JOIN: Return rows when there is at least one match in both tables.

LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table

RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table

FULL JOIN: Return rows when there is a match in one of the tables

Lesson 19: SSRS Charts and Graphs

SSRS 2008R2 provides a rich set of visualization tools. Some of them are charts / graphs. These
are available in 3D formats to add a rich effects to your reports.
SSRS charts and graphs helps to summarize the data in visual format. It enables to represent very
large datasets as aggregated information available at a glance.
SSRS 2008 R2 also added sparklines to the visualization tools.
In addition, SSRS 2008 R2 also supports guages/Databasrs/Sparkline/Indicators.
One of the most exciting features of SSRS being, usage of Maps.
It allows an awesome representation of Geagraphic data using maps.
Lets look at some basic graphs and shapes.
Step 1:
As usual, lets create a blank report, connecting to adventure works database with dataset:

SELECT Sales.SalesTerritory.Name as Region,

Sales.SalesTerritory.CountryRegionCode,
Sales.SalesTerritory.[Group] as Territory,
Year(Sales.SalesOrderHeader.DueDate) as Year,
Month(Sales.SalesOrderHeader.DueDate) as Month,
Sales.SalesOrderHeader.TotalDue
FROM Sales.SalesTerritory
INNER JOIN Sales.SalesOrderHeader
ON Sales.SalesTerritory.TerritoryID = Sales.SalesOrderHeader.TerritoryID

Step 2:
Now lets drag and drop a chart control from the toolbox.
As soon as you do that , you get a pop-up, displaying various image shapes for the chart control.

Step 3: These are the set of shapes provided by SSRS for visualization purposes.
Let's choose the first shape.
You should be getting something like this on your scree:

Step 4: This is a blank chart,with no data in it.

You can click on the headings/text in the axis and change accordingly.
Alternatively , you can also write SSRS expressions to change text/descriptions dynamically.
Please refer my previous tutorial, on SSRS expressions.

Step 5: In the above example, i have changed the chart title to 'My First Chart'
Now drag and drop TotalDue to the summation of values, CountryRegionCode to the
CategoryGroups and year to the series Groups.
Something like this...

Step 6: Hit Preview, and you be getting something like this:

Step 7: Try these steps, with a number of other shapes and graphs/Sparklines/Indicators.

Lesson 20: SSRS 2008 R2 Document

Maps
A document map in an SSRS report, provides pointers/links to certain report items in your report.
If you have a document map in your report, it will appear in the left most pane. Clicking on any of the
links, in the document pane, jumps the users directly to the report item.
It is similar to the table of contents.
Please note that clicking on the document map links, refreshes the report.
Lets look at an example for a document map.
Step 1: Create a blank report by connecting to Adventure works database.
I have used the following query to create my dataset:

SELECT Sales.SalesTerritory.Name as Region,

Sales.SalesTerritory.CountryRegionCode,
Sales.SalesTerritory.[Group] as Territory,
Year(Sales.SalesOrderHeader.DueDate) as Year,
Month(Sales.SalesOrderHeader.DueDate) as Month,
Sales.SalesOrderHeader.TotalDue
FROM Sales.SalesTerritory
INNER JOIN Sales.SalesOrderHeader
ON Sales.SalesTerritory.TerritoryID = Sales.SalesOrderHeader.TerritoryID
Order by Sales.SalesTerritory.CountryRegionCode

Your design view after creating the report should look something like this.

Please observe the Row groups.

Adding Territory as a group is necessary here, else SSRS will repeat the Territory values in the
document map.
You can hide one of the territory column (the non-group one) in the report.
Next, goto the Territory column, and select the properties.
Go ahead and choose Territory in the DocumentMaps Property:

Now hit preview, and you should be ale to see the follow SSRS 2008 R2 report with a document
map:

Select any one Territory in the document map, and the report will jump to the appropriate page.

Lesson 21: SSRS report execution and

performance enhancements
SSRS 2008 R2 allows us to execute reports in 3 modes:
1. On Demand.
2. From Cache
3. From Snapshots
On demand:
This is normal approach that we follow, by hitting a report server URL. Each time a report is run,
data is returned from the database server and rendered to the report.
This approach ensures that our report is update and fresh.
The downside of this approach is that, if n users open up this report on their browsers, queries on
the report are executed n times.
Thus this approach at times might slow down the server.
Cache
One of the performance enhancements techniques is to cache a report when it is initially run.
This means that if another user requests for the report, the same report is served to the user from
the cache
This avoids people querying the database server from each report rendering.
To make sure that people do not receive too much stale data, we can set a time to invalidate a
cache.
This is a good performance enhancement technique for slow running reports.
Snapshots:
Report snapshots are created at a particular schedule for certain parameters.
Please note that parameters cannot be changes on snapshot reports.
SSRS 2008 R2 allows to schedule the snapshot creation times.
Users can directly render a report from a snapshot. However please note that not all reports can
have snapshots, especially the ones that prompt users for credentials.

Lession 22 :Intergrate SSRS Report Server WIth ASP.NET

Application
Introduction

In this article, we will cover how to use the deployed SSRS Report on to the ASP.NET Web application. In our
earlier articles we have seen the steps to create and deploy the report to the Reporting Server and administer
the deployed reports. In real time, we have seen requirements on accessing the reports from different clients.
Let us now move ahead for the step-by-step process on accessing the SSRS report in ASP.NET web pages
dynamically.
Steps
Before starting the steps to show the SSRS Report in ASP.NET Page, we should just have a look around on the
previous articles to get some fair idea on how to design the report and deploy it to the Report Server.
First, open visual studio 2010 IDE and create a new ASP.NET Project as shown in the screen below. In this
example we are going to use the report which we deployed in the Part 2 of this series of articles on SSRS.

Now in the ASPX Page, drag and drop a Scriptmanager tool, a button and a report viewer control from the
toolbox as shown in the screen below.

Double click on the button. This will open a button event as shown in the screen below.

In the button click event we need to write our custom code to access the report from the SSRS Server as shown
in the screen below.
Code:
1.reportViewerSam.ProcessingMode =
Microsoft.Reporting.WebForms.ProcessingMode.Remote;
2.reportViewerSam.ServerReport.ReportServerUrl = new
Uri("http://sp2010/ReportServer");

3.reportViewerSam.ServerReport.ReportPath = "/Part2Sample/Report1";
4.reportViewerSam.ServerReport.Refresh();

In the above code, we can have the Report Server URL http://sp2010/ReportServer which we need to
change as per the server URL. The report path on which the report is deployed is /Part2Sample/Report1. Also,
since we are accessing the server directly we need to provide the processing mode as Remote. Once we are
done with the above code, execute the project by clicking the Runbutton or press F5 (functional key) as shown
in the screen below.

Once the project is build successfully, we can see only the button as shown in the screen below.

Now click on Get Reports to access the report directly from the SSRS server. Clicking on Get Report will
load the report and we can see the report generating as shown in the screen below.

Once the report is generated completely, we can see the report with the well formatted structure that has been
deployed to the sever as shown in the screen below.

Conclusion
So, in this article we have seen how to show the deployed SSRS Report in the asp.net web application with
ease without doing any formatting or any design.

SSRS 2008 Tutorial: The Reporting Services Architecture

The Reporting Services Architecture

Reporting Services has a quite a few components that work together seamlessly to provide a complete
reporting solution. The full Reporting Services architecture includes development tools, administration tools,
and report viewers. There are a number of ways to get to Reporting Services programmatic-ally, including
URL, SOAP and WMI interfaces.
Figure 17-1 shows a simplified diagram of the main Reporting Services components that we'll be using in this
chapter.

In this chapter you'll learn about these components:

Report Server is the core engine that drives Reporting Services.

Report Manager is a Web-based administrative interface for Reporting Services.

Report Designer is a developer tool for building complex reports.

Report Builder is a simplified end-user tool for building reports.
The Report Server database stores report definitions. Reports themselves can make use of data from many
different data sources.

SSRS 2008 Tutorial: Using Report Designer

Reporting Services includes two tools for creating reports:

Report Designer can create reports of any complexity that Reporting Services supports, but requires you to
understand the structure of your data and to be able to navigate the Visual Studio user interface.
Report Builder provides a simpler user interface for creating ad hoc reports, directed primarily at business
users rather than developers. Report Builder requires a developer or administrator to set up a data model before
end users can create reports.
We'll start our tour of Reporting Services with Report Designer. Report Designer runs inside the Business
Intelligence Development Studio shell, and offers several ways to create reports. You can either use the Report
Wizard to quickly create a report, or you can use a set of design tools to build a report from scratch. You can
also use the design tools to modify a report created with the wizard.

Using the Report Wizard

The easiest way to create a report in Report Designer is to use the Report Wizard. Like all wizards, the Report
Wizard walks you through the process in step-by-step fashion. You can make the following choices in the
wizard:

The data source to use

The query to use to retrieve data
Whether to use a tabular or matrix layout for the report
How to group the retrieved data
What visual style to use
Where to deploy the finished report

Try It!
To create a simple report using the Report Wizard, follow these steps:

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

Launch Business Intelligence Development Studio.

Select File > New >Project.
Select the Business Intelligence Projects project type.
Select the Report Server Project Wizard template.
Name the new report ProductReport1 and pick a convenient location to save it in.
Click OK.
Read the first page of the Report Wizard and click Next.
Name the new data source AdventureWorksDS.
Click the Edit button.
Log on to your test server.
Select the AdventureWorks2008 database.
Click OK.
Click the Credentials button.

14. Select Use Windows Authentication.

15. Click OK.
16. Check the Make This a Shared Data Source checkbox. This will make this particular data source available to
other Reporting Services applications in the future.
17. Click Next.
18. Click the Query Builder button.
19. If the full query designer interface does not display by default, click the query designer toolbar button at the far
left end of the toolbar. Figure 17-2 shows the full query designer interface.
20.
21. Click the Add Table toolbar button.
22. Select the Product table and click Add.
23. Click Close.
24. Check the Name, ProductNumber, Color, and ListPrice columns.
25. Click OK.
26. Click Next.
27. Select the Tabular layout and click Next.
28. Move the Color column to the Group area, and the other three columns to the Detail area, as shown in Figure
17-3.
29. Click Next.
30. Select the Stepped layout and click Next.
31. Select the Ocean style and click Next.
32. Accept the default deployment location and click Next.
33. Name the report ProductReport1.
34. Check the Preview Report checkbox.
35. Click Finish.
Figure 17-4 shows the finished report, open in Report Designer.
Figure 17-4 shows the main features of Report Designer:

The Datasets window shows the data that is available to the report.
The main design window lets you view the report itself. You can see a preview of the report, work with the
report in a layout designer, or work with the query that returns the data for the report.
The Solution Explorer, Output, and Properties windows are the standard Visual Studio windows.
Modifying a Report
Now that you've created a report with the Report Wizard, you can modify it with the Report Designer. If
you've used any sort of visual report design tool in the past, you should have no problem making changes here.
Among the possibilities here:

You can change the available data or the sort order for the report by modifying the query on the Data tab.
You can resize or rearrange controls on the Layout tab.
You can use the Properties window to change properties of individual controls including their font, alignment,
colors, and so on.
Try It!
To modify the report that you just created, follow these steps:

1.
2.
3.
4.

Click the Design tab to make the report editable.

In the Report Data window, right-click on DataSet1 and select Dataset Properties.
In the Dataset Properties window, click the Query Designer button.
Select a Descending sort type for the ListPrice column and click OK.

5. Click OK.
6. Click in the textbox at the top of the report, where the report name is displayed.
7. Click a second time in the textbox to put it in edit mode and change the value of this control to Products By
Color.
8. Click on the header for the Product column.
9. Place the cursor between the column selectors above the Name and Product Number columns to display a
double-headed arrow. Hold down the mouse button and drag the cursor to the right to widen the Name column.
10. Place the cursor between the column selectors above the Product Number and ListPrice columns to display a
double-headed arrow. Hold down the mouse button and drag the cursor to the right to widen the Product
Number column.
11. Click on the Preview tab to view the modified report, as shown in Figure 17-5.
Designing a Report From Scratch
You can also use Report Designer to build your own reports starting from scratch. In general, you'll follow
these steps to create a report:

1.
2.
3.
4.

Create a Report project in Business Intelligence Design Studio or open an existing Report project.
Add a report to the project.
Create one or more datasets for the report.
Build the report layout.
Try It!
To create a fresh report in Report Designer, follow these steps:

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.

Select File > New > Project.

Select the Business Intelligence Projects project type.
Select the Report Server Project template.
Name the new report ProductReport2 and pick a convenient location to save it in.
Right-click on the Reports node in Solution Explorer and select Add > New Item.
Select the Report template.
Name the new report ProductReport2.rdl and click Add.
In the Report Data window, select New > Data Source.
Name the new Data Source AdventureWorksDS.
Select the Embedded Connection option and click on the Edit button.
Connect to your test server and choose the AdventureWorks2008 database.
Click OK.
Click OK again to create the data source.
In the Report Data window, select New > Dataset.
Name the dataset dsLocation.
Click the Query Designer button.
If the full Query Designer does not appear, click on the Edit As Text button.
Click the Add Table button.
Select the Location table.
Click Add.
Click Close.
Check the boxes for the Name and CostRate columns.
Sort the dataset in ascending order by Name and click OK.
Click OK again to create the dataset.
Open the toolbox window (View > Toolbox).
Double-click the Table control.
Switch back to the Report Data window.
Expand the dataset to show the column names.

29. Drag the Name field and drop it in the first column of the table control on the design tab.
30. Drag the CostRate field from the Report Data window and drop it in the second column of the table control.
31. Place the cursor between the column selectors above the Name and CostRate columns to display a doubleheaded arrow. Hold down the mouse button and drag the cursor to the right to widen the Name column.
32. Figure 17-6 shows the report in Design view.
Figure 17-6: Designing a report from scratch
33. Select the Preview tab to see the report with data.
SSRS 2008 Tutorial: Publishing a Report
Creating reports in Business Intelligence Development Studio is good for developers, but it doesn't help users
at all. In order for the reports you build to be available to others, you must publish them to your Reporting
Services server. To publish a report, you can use the Build and Deploy menu items in Business Intelligence
Development Studio. Before you do this, you need to check the project's configuration to make sure that
you've selected an appropriate server for the deployment.
Try It!
You can publish any report, but the first report you created is probably more visually interesting at this point.
To publish the first report, follow these steps:

1.
2.
3.
4.

Select File > Recent Projects and choose your ProductReport1 project.
Select Project ProductReport1 Properties.
Click the Configuration Manager button.
Fill in the Target Server URL for your Report Server. If you're developing on the same computer where
Reporting Services is installed, and you installed in the default configuration, this will be
http://localhost/ReportServer. Figure 17-7 shows the completed Property Pages.

5. Click OK.
6. Select Build > Deploy ProductReport1. The Output Window will track the progress of BIDS in deploying your
report, as shown in Figure 17-8. Depending on the speed of your computer, building the report may take some
time.
7. Launch a web browser and enter the address http://localhost/reports.
8. It may take several minutes for the web page to display; Reporting Services goes to sleep when it hasn't been
used for a while and can take a while to spin up to speed. Figure 17-9 shows the result.
9. Click the link for the ProductReport1 folder.
10. Click the link for the ProductReport1 report.
SSRS 2008 Tutorial: Using Report Builder
Report Designer gives you one way to create reports for Reporting Services, but it's not the only way. SQL
Server 2005 also includes a tool directed at end users named Report Builder. Unlike Report Designer, which is
aimed at Developers, Report Builder presents a simplified view of the report-building process and is intended
for business analysts and other end users.
Building a Data Model
Report Builder doesn't let end users explore all of a SQL Server database. Instead, it depends on a data model:
a preselected group of tables and relationships that a developer has identified as suitable for end-user reporting.

To build a data model, you use Business Intelligence Development Studio. Data models contain three things:

Data Sources connect the data model to actual data.

Data Source Views draw data from data sources.
Report Models contain entities that end users can use on reports.
Try It!
To create a data model, follow these steps:

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

If it's not already open, launch Business Intelligence Development Studio

Select File > New > Project.
Select the Business Intelligence Projects project type.
Select the Report Model Project template.
Name the new project AWSales and save it in a convenient location.
Click OK.
Right-click on Data Sources in Solution Explorer and select Add New Data Source.
Read the first page of the Add New Data Source Wizard and click Next.
Click New.
In the Connection Manager dialog box connect to the AdventureWorks2008 database on your test server and
click OK.
Click Next.
Name the new data source AdventureWorks and click Finish.
Right-click on Data Source Views in Solution Explorer and select Add New Data Source View.
Read the first page of the Add New Data Source View Wizard and click Next.
Select the AdventureWorks data source and click Next.
Select the Product(Production) table and click the > button to move it to the Included Objects listbox.
Select the SalesOrderDetail(Sales) table and click the > button to move it to the Included Objects listbox.
Click the Add Related Tables button.
Click Next.
Click Finish.
Right-click on Report Models in Solution Explorer and select Add New Report Model.
Read the first page of the Report Model Wizard and click Next.
Select the Adventure Works2008 data source view and click Next.
Keep the default rules selection, as shown in Figure 17-10, and click Next.

11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.

Choose the Update Statistics option and click Next.

Click Run to complete the wizard.
Click Finish. If you get a warning that a file was modified outside the source editor, click Yes.
Select Build > Deploy AWSales to deploy the report model to the local Reporting Services server.
Building a Report Report Builder itself is a ClickOnce Windows Forms application. That means that it's a
Windows application that end users launch from their web browser, but it never gets installed on their
computer, so they don't need any local administrator rights on their computer to run it. To get started with
Report Builder, browse to your Reporting Services home page. Typically, this will have a URL such as
http://ServerName/Reports (or http://localhost/Reports if you're running the browser on the same box with
SQL Server 2008 itself). Figure 17-11 shows the Reporting Services home page.
To run Report Builder, click the Report Builder link in the home page menu bar. Report Builder will
automatically load up all of the available report models and wait for you to choose one to build a report from.

Try It!
1. Open a browser window and navigate to http://localhost/Reports (or to the appropriate Report Server URL if
you're not working on the report server).
2. Click the Report Builder link.
3. Depending on your operating system, you may have to confirm that you want to run the application.
4. After Report Builder is loaded, select the AdventureWorks2008 report model and the table report layout. Click
OK. Figure 17-12 shows the new blank report that Report Builder will create.
Figure 18-12: New report in Report Builder

The Explorer window to the left of the design surface shows all of the tables in
the report model. Beneath that, the Fields window shows the attributes in the
currently-selected entity. Note that not everything in this window is a column in
the table: the report model also contains aggregate entities such as Total Safety
Stock Level and automatically calculated fields.

5.
6.
7.
8.
9.
10.
11.

Select the Product table.

Drag the Name field and drop it in the area labeled Drag and Drop Column Fields.
Click on Special Offer Products in the Explorer window to show related child tables.
Click on Sales Order Details.
Drag the Total Order Qty field and drop it to the right of the Name field.
Click where it says Click to Add Title and type Product Sales.
Click the Run Report button to produce the report shown in Figure 17-13.

12.
13.
14.
15.
16.
17.

Click the Sort and Group toolbar button.

Select to sort by Total Order Qty descending.
Click OK.
Select File > Save.
Name the new report Product Sales.
Click Save. This will publish the report back to the Reporting Services server that you originally downloaded
Report Builder from.
SSRS 2008 Tutorial: Using Report Manager
The Web home page for Reporting Services provides a complete interface for managing reports (as well as
other objects such as data sources and models) after they are created. This interface, known as Report
Manager, is intended primarily for database administrators, but as a developer you should know about its
capabilities for managing and modifying reports.
When you click on a report in Report Manager, you'll see the report's data, as shown in Figure 17-14.
Note that reports in Report Manager open in a tabbed interface. The four tabs allow you to perform various
functions:

View allows you to see the current data in the report.

Properties lets you adjust such things as the report's name, data source, security credentials, caching, and enduser security.

History shows you saved snapshots of the report.

Subscriptions lets you create subscriptions to the report. Subscriptions allow you to set up periodic delivery of
reports to end users by e-mail or file share.
Printing and Exporting Reports
When viewing reports in the Report Manager, users can print the reports directly from their browser. The print
button in the report toolbar utilizes an ActiveX control for client-side printing. The first time this button is
clicked on a given computer, the user is prompted to install the ActiveX control, as in Figure 17-15. After that,
the standard Windows print dialog box is displayed for the user to select a printer and paper size, etc.
Users can also export the report into any of several handy formats. Table 17-1 lists the available export
formats.

Export Format

Handles

XML

Creates a data file in XML format.

CSV

Creates a comma-delimited text file of report data.

PDF

Creates an Adobe Acrobat file with the formatted report.

MHTML

Creates a Web Archive file with the formatted report.

EXCEL

Creates a MS Excel spreadsheet with the formatted report.

TIFF

Creates a TIFF graphic of the formatted report.

Word

Creates a MS Word document with the formatted report.

Table 17-1: Export Formats

SSRS 2008 Tutorial: Exercises

Use Report Builder to create a report from the AdventureWorks2008 data model showing the minimum and
maximum order quantity for orders taken by each salesperson in the company. You'll find the necessary data in
the SalesOrderHeader and SalesOrderDetail tables.
Solutions to Exercises
1. Open a browser window and navigate to http://localhost/Reports (or to the appropriate Report Server URL if
you're not working on the report server).
2. Click the Report Builder link.
3. Select the AdventureWorks2008 report model and the table report layout.
4. Click OK.
5. Select the Sales Order Header table.
6. Drag the Sales Person ID field and drop it in the area labeled Drag and Drop Column Fields.
7. Click on Sales Order Details in the Explorer window.

8.
9.
10.
11.
12.

Expand the Total Order Qty field in the Fields window to show the alternative fields beneath it.
Drag the Min Order Qty field and drop it to the right of the Name field.
Drag the Max Order Qty field and drop it to the right of the Min Order Qty field.
Click where it says Click to Add Title and type Sales Performance.
Click the Run Report button to produce the report shown in Figure 17-15.

Introduction To Role-Based Security In SQL Server Reporting

Services
There are only two security guarantees after a successful installation of Reporting Services:

1) The Report Manager web application, where you can browse and view reports, will require
Windows to authenticate all clients.

2) The only users authorized to use the Report Manager application will be members of the servers
Administrators group (BUILTIN\Administrators). If you want to get your reporting project off the
ground and allow other people in your organization to view reports, youll need to add additional
groups and users into roles. In this article, we will highlight some basic features of Reporting
Services role-based security. Reporting Services Roles Reporting Services ships with 6 built-in
roles: System Administrator, System User, Browser, Content Manager, My Reports, and Publisher.
Placing a Windows user into one of these roles will give the user authorization to perform specific
actions with the report manager. For example, a user in the role of Browser can view folders and
reports, but not publish new reports. A user in the role of Publisher can create, view, and delete
reports, but cannot create new roles. These are the default settings of Reporting Services, which a
user in the System Administrator role can modify.

System Administrators
As mentioned earlier, the Reporting Services setup will place the local machines Administrators
group into the System Administrators role. Since this role is an all-powerful role, youll want take care
with any modifications you make. A true system administrator will probably also need admin
privileges on the server to take care of tasks such as starting and stopping services, so consider
adding the person or group into the local administrators group of the server (but think of the
ramifications first). If you want to add additional users or groups to the System Administrators role
without adding to the local administrators group, youll need to go into the System Role Assignments
page. To do this interactively you can navigate to the report manager (http://reportserver/reports, for
instance) and click Site Settings in the upper right hand of the screen. Towards the bottom of the
Site Settings screen youll find three links under a security heading.

Click on Configure site-wide security to bring yourself to the following screen:

Click on New Role Assignment to move to the next screen:

From here you can enter a username, or a group name into the textbox. For machines in a domain,
you can prefix the name with the domain name, (DOMAIN\scott, for example). Reporting Services
will verify the entry, so dont worry about spelling mistakes. Select the checkboxes for the role you
want to assign. Notice you can also create a new system role, though we will not cover the topic in
this article.

Item Level Security

A more common task will be adding users and groups into roles to view reports, or create and
manage reports. To perform this task you will first want to navigate to the top most level of the area
you want to administer. For example, to give access to the SampleReports folder and all reports
underneath
SampleReports,
navigate
to
the
SampleReports
folder
(http://reporting/Reports/Pages/Folder.aspx?ItemPath=%2fSampleReports, for example). Click on
the Properties tab along the top, then click on the Security link along the side. You should now see a
screen similar to the following:

Click New role assignment, and enter a group or user name into the textbox shown below:

You can select from the built-in roles shown above, or create a new role. Using the UI you can view
the tasks each role has permissions to perform. The security settings you set will flow downwards,
that is any folders and reports underneath the SampleReports folder will inherit these settings, so
you do not need to repeat this step for each report in the folder. You can break the inheritance by
defining a new security policy for a child item.

Conclusion
I hope that this article will give you a jumpstart on managing roles for Reporting Services. I
recommend you go to the official documentation for additional reading, as this article serves as only
a brief introduction to role-based security in Reporting Services.

How to fix Reporting Services permissions are insufficient for

performing operation (rsAccessDenied)

Learn how to configure and access SQL Server Reporting Services reports when you get The
permissions granted to user 'domain\username' are insufficient for performing this operation.
(rsAccessDenied)

If you are getting the following error while trying to access or deploy a SQL Services Reporting
Services (ssrs) report server on your localhost:
The permissions granted to user 'domain\username' are insufficient for
performing this operation. (rsAccessDenied)

Here's what to do:

1. Make sure you have access configured to the URL http://localhost/reports using the SQL Reporting Services
Configuration.
To
do
this:
1. Open Reporting Services Configuration Manager -> then connect to the report server instance -> then click on
Report Manager URL.
2. In the Report Manager URL page, click the Advanced button -> then in the Multiple Identities for Report
Manager, click Add.
3. In the Add a Report Manager HTTP URL popup box, select Host Header and type in: localhost
4. Click OK to save your changes.
2. Now
start/
run
Internet
Explorer using Run
as Administator...

3.
4.
5.
6.

7.
8.

9.

NOTE: If you don't see the 'Site Settings' link in the top left corner while at http://localhost/reports it is
probably because you aren't running IE as an Administator or you haven't assigned your computers
'domain\username' to the reporting services roles, see how to do this in the next few steps.
Then go to: http://localhost/reports (you may have to login with your Computer's username and password)
You should now be directed to the Home page of SQL Server Reporting Services
here:http://localhost/Reports/Pages/Folder.aspx
From the Home page, click the Properties tab, then click New Role Assignment
In the Group or user name textbox, add the 'domain\username' which was in the error message (in my case, I
added:DOUGDELL3-PC\DOUGDELL3 for the 'domain\username', in your case you can find the
domain\username for your computer in the rsAccessDenied error message).
Now check all the checkboxes; Browser, Content Manager, My Reports, Publisher, Report Builder, and then
click OK.
You're domain\username should now be assigned to the Roles that will give you access to deploy your
reports to the Report Server. If you're using Visual Studio or SQL Server Business Intelligence
Development Studio to deploy your reports to your local reports server, you should now be able to.
Hopefully, that helps you solve your Reports Server rsAccessDenied error message...
Just to let you know this tutorial was done on a Windows 7 computer with SQL Server Reporting Services
2008.

How to pass value to SSRS Report Parameter

Create class for ReportServerCredentials

public class ReportServerCredentials : IReportServerCredentials

{
private string _userName;
private string _password;
private string _domain;
public ReportServerCredentials(string userName, string password, string domain)
{
_userName = userName;
_password = password;
_domain = domain;
}
public WindowsIdentity ImpersonationUser
{
get
{
// Use default identity.
return null;
}
}
public ICredentials NetworkCredentials
{
get
{
// Use default identity.
return new NetworkCredential(_userName, _password, _domain);
}
}
public bool GetFormsCredentials(out Cookie authCookie, out string user, out string password,
out string authority)
{
// Do not use forms credentials to authenticate.
authCookie = null;
user = password = authority = null;
return false;
}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using Microsoft.Reporting.WebForms;
using System.Security.Principal;
using System.Net;
namespace SSRS_integareted
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//SummaryReportTypeDisplay();
}
MyReportViewer.ProcessingMode = ProcessingMode.Remote;
MyReportViewer.ServerReport.ReportServerUrl = new Uri("");//reportserverurl
from reportmanager//
MyReportViewer.ProcessingMode =
Microsoft.Reporting.WebForms.ProcessingMode.Remote;
MyReportViewer.ToolBarItemBorderColor = System.Drawing.Color.PowderBlue;
MyReportViewer.ToolBarItemBorderStyle = BorderStyle.Double;
string strUserName = "Reportserver";
string strPassword = "";//system password
string strDomain = "";//Domain Name
MyReportViewer.ServerReport.ReportServerCredentials = new
ReportServerCredentials(strUserName, strPassword, strDomain);
MyReportViewer.ServerReport.ReportPath = "Reportpath";
MyReportViewer.ServerReport.Refresh();
ReportParameter[] reportParameterCollection1 = new ReportParameter[5];
//Array
size describes the number of paramaters.
reportParameterCollection1[0] = new ReportParameter();
reportParameterCollection1[0].Name = "p1";
//Give Your Parameter
Name
reportParameterCollection1[0].Values.Add("Reports");
reportParameterCollection1[1] = new ReportParameter();
reportParameterCollection1[1].Name = "pID";
//Give Your
Parameter Name
reportParameterCollection1[1].Values.Add("1");
reportParameterCollection1[2] = new ReportParameter();
reportParameterCollection1[2].Name = "pFromDate";
//Give Your
Parameter Name
reportParameterCollection1[2].Values.Add("2013-04-03");
reportParameterCollection1[3] = new ReportParameter();
reportParameterCollection1[3].Name = "pToDate";
//Give Your

Parameter Name
reportParameterCollection1[3].Values.Add("2013-05-30");
reportParameterCollection1[4] = new ReportParameter();
reportParameterCollection1[4].Name = "pName";
//Give Your
Parameter Name
reportParameterCollection1[4].Values.Add("SSRS");
MyReportViewer.ServerReport.SetParameters(reportParameterCollection1);
MyReportViewer.ServerReport.Refresh();
}

2. An Example for the Beginners (But NOT for the

dummies)
A MySQL database server contains many databases (or schemas). Each database consists of one or
more tables. A table is made up of columns (or fields) and rows (records).
The SQL keywords and commands are NOT case-sensitive. For clarity, they are shown in uppercase.
The names or identifiers (database names, table names, column names, etc.) are case-sensitive in
some systems, but not in other systems. Hence, it is best to treat identifiers as case-sensitive.
You can use SHOW DATABASES to list all the existing databases in the server.
mysql> SHOW DATABASES;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| mysql
|
| performance_schema |
| test
|
+--------------------+
4 rows in set (0.00 sec)

The databases "mysql", "information_schema" and "performance_schema" are system databases

used internally by MySQL. A "test" database is provided during installation for your testing.
Let us begin with a simple example - a product sales database. A product sales database typically
consists of many tables, e.g., products, customers, suppliers, orders, payments, employees, among
others. Let's call our database "southwind" (inspired from Microsoft's Northwind Trader sample
database). We shall begin with the first table called "products" with the following columns (with the
data types as indicated) and rows:

Database: southwind
Table: products
productID
INT

productCode
CHAR(3)

name
VARCHAR(30)

quantity
INT

price
DECIMAL(10,2)

1001

PEN

Pen Red

5000

1.23

1002

PEN

Pen Blue

8000

1.25

1003

PEN

Pen Black

2000

1.25

1004

PEC

Pencil 2B

10000

0.48

1005

PEC

Pencil 2H

8000

0.49

2.1 Creating
and
Deleting
DATABASE and DROP DATABASE

Database

- CREATE

CREATE DATABASE and DROP DATABASE

You can create a new database using SQL command "CREATE DATABASE databaseName"; and delete
a database using "DROP DATABASE databaseName". You could optionally apply condition "IF
EXISTS" or "IF NOT EXISTS" to these commands. For example,
mysql> CREATE DATABASE southwind;
Query OK, 1 row affected (0.03 sec)
mysql> DROP DATABASE southwind;
Query OK, 0 rows affected (0.11 sec)
mysql> CREATE DATABASE IF NOT EXISTS southwind;
Query OK, 1 row affected (0.01 sec)
mysql> DROP DATABASE IF EXISTS southwind;
Query OK, 0 rows affected (0.00 sec)

IMPORTANT: Use SQL DROP (and DELETE) commands with extreme care, as the deleted entities are
irrecoverable. THERE IS NO UNDO!!!

SHOW CREATE DATABASE

The CREATE

DATABASE commands

uses some defaults. You can issue a "SHOW CREATE

DATABASE databaseName" to display the full command and check these default values. We
use \G(instead of ';') to display the results vertically. (Try comparing the outputs produced
by ';' and \G.)

mysql> CREATE DATABASE IF NOT EXISTS southwind;

mysql> SHOW CREATE DATABASE southwind \G
*************************** 1. row ***************************
Database: southwind
Create Database: CREATE DATABASE `southwind` /*!40100 DEFAULT CHARACTER SET latin1 */

Back-Quoted Identifiers (`name`)

Unquoted names or identifiers (such as database name, table name and column name) cannot
contain blank and special characters or crash with MySQL keywords (such as ORDER and DESC). You
can include blanks and special characters or use MySQL keyword as identifier by enclosing it with a
pair of back-quote, i.e., `name`. For robustness, the SHOW command back-quotes all the identifiers, as
illustrated in the above example.

MySQL Version Comments

MySQL multi-line comments are enclosed within /* and */; end-of-line comments begins with -(followed by a space) or #.
The /*!40100 ...... */ is known as version comment, which will only be run if the server is at or
above this version number 4.01.00. To check the version of your MySQL server, issue query " SELECT
version()".

2.2 Setting the Default Database - USE

The command "USE databaseName" selects a particular database as the default (or current) database.
You can refer to a table in the default database using tableName directly; but you need to use the
fully-qualified databaseName.tableName to refer to a table NOT in the default database.
In our example, we have a database named "southwind" with a table named "products". If we issue
"USE southwind" to set southwind as the default database, we can simply call the table as
"products". Otherwise, we need to reference the table as "southwind.products".
To display the current default database, issue command "SELECT DATABASE()".

2.3 Creating and Deleting a Table - CREATE

TABLE

TABLE and DROP

You can create a new table in the default database using command "CREATE TABLE tableName" and
"DROP TABLE tableName". You can also apply condition "IF EXISTS" or "IF NOT EXISTS". To create
a table, you need to define all its columns, by providing the columns' name, type, and attributes.
Let's create the table "products" for our database "southwind".
-- Remove the database "southwind", if it exists.
-- Beware that DROP (and DELETE) actions are irreversible and not recoverable!
mysql> DROP DATABASE IF EXISTS southwind;
Query OK, 1 rows affected (0.31 sec)

-- Create the database "southwind"

mysql> CREATE DATABASE southwind;
Query OK, 1 row affected (0.01 sec)
-- Show all the databases in the server
-to confirm that "southwind" database has been created.
mysql> SHOW DATABASES;
+--------------------+
| Database
|
+--------------------+
| southwind
|
| ......
|
+--------------------+
-- Set "southwind" as the default database so as to reference its table directly.
mysql> USE southwind;
Database changed
-- Show the current (default) database
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| southwind |
+------------+
-- Show all the tables in the current database.
-- "southwind" has no table (empty set).
mysql> SHOW TABLES;
Empty set (0.00 sec)
-- Create the table "products". Read "explanations" below for the column defintions
mysql> CREATE TABLE IF NOT EXISTS products (
productID
INT UNSIGNED NOT NULL AUTO_INCREMENT,
productCode CHAR(3)
NOT NULL DEFAULT '',
name
VARCHAR(30)
NOT NULL DEFAULT '',
quantity
INT UNSIGNED NOT NULL DEFAULT 0,
price
DECIMAL(7,2) NOT NULL DEFAULT 99999.99,
PRIMARY KEY (productID)
);
Query OK, 0 rows affected (0.08 sec)
-- Show all the tables to confirm that the "products" table has been created
mysql> SHOW TABLES;
+---------------------+
| Tables_in_southwind |
+---------------------+
| products
|
+---------------------+

-- Describe the fields (columns) of the "products" table

mysql> DESCRIBE products;
+-------------+------------------+------+-----+------------+----------------+
| Field
| Type
| Null | Key | Default
| Extra
|
+-------------+------------------+------+-----+------------+----------------+
| productID
| int(10) unsigned | NO
| PRI | NULL
| auto_increment |
| productCode | char(3)
| NO
|
|
|
|
| name
| varchar(30)
| NO
|
|
|
|
| quantity
| int(10) unsigned | NO
|
| 0
|
|
| price
| decimal(7,2)
| NO
|
| 99999.99
|
|
+-------------+------------------+------+-----+------------+----------------+
-- Show the complete CREATE TABLE statement used by MySQL to create this table
mysql> SHOW CREATE TABLE products \G
*************************** 1. row ***************************
Table: products
Create Table:
CREATE TABLE `products` (
`productID`
int(10) unsigned NOT NULL AUTO_INCREMENT,
`productCode` char(3)
NOT NULL DEFAULT '',
`name`
varchar(30)
NOT NULL DEFAULT '',
`quantity`
int(10) unsigned NOT NULL DEFAULT '0',
`price`
decimal(7,2)
NOT NULL DEFAULT '99999.99',
PRIMARY KEY (`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Explanations
We define 5 columns in the table products: productID, productCode, name, quantity and price.
The data types are:

productID is INT UNSIGNED - non-negative integers.

productCode is CHAR(3) - a fixed-length alphanumeric string of 3 characters.

a
variable-length
string
of
up
to
30
characters.
We use fixed-length string for productCode, as we assume that the productCode contains
exactly 3 characters. On the other hand, we use variable-length string for name, as its length
varies - VARCHAR is more efficient than CHAR.
quantity is also INT.
price is DECIMAL(10,2) a
decimal
number
with
2
decimal
places.
DECIMAL is precise (represented as integer with a fix decimal point). On the other
hand, FLOAT and DOUBLE (real numbers) are not precise and are approximated. DECIMAL type is
recommended for currency.
The attribute "NOT NULL" specifies that the column cannot contain the NULL value. NULL is a special

name is VARCHAR(30) -

value indicating "no value", "unknown value" or "missing value". We also set the default values of all
the columns. The column will take on its default value, if no value is specified during the record
creation.

We set the column productID as the so-called primary key. Values of the primary-key column must
be unique. Every table shall contain a primary key. This ensures that every row can be distinguished
from other rows. You can specify a single column or a set of columns as the primary key. An index is
build automatically on the primary-key column to facilitate fast search. Primary key is also used as
reference for other tables.
We set the column productID to AUTO_INCREMENT. with default starting value of 1. When you insert
a NULL (recommended) (or 0, or a missing value), into an AUTO_INCREMENT column, the maximum
value of that column plus 1 would be inserted. You can also insert a valid value to
an AUTO_INCREMENT column, bypassing the auto-increment.

2.4 Inserting Rows - INSERT INTO

Let's fill up our "products" table with rows. We set the productID of the first record to 1001, and
use AUTO_INCREMENT for the rest of records by inserting a NULL, or with a missing column value. Take
note that strings must be enclosed with a pair of single quotes (or double quotes).
-- Insert a row with all the column values
mysql> INSERT INTO products VALUES (1001, 'PEN', 'Pen Red', 5000, 1.23);
Query OK, 1 row affected (0.04 sec)
-- Insert multiple rows
-- Inserting NULL to an auto_increment column results in max_value + 1
mysql> INSERT INTO products VALUES
(NULL, 'PEN', 'Pen Blue', 8000, 1.25),
(NULL, 'PEN', 'Pen Black', 2000, 1.25);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
-- Insert value to selected columns
-- Missing value for an auto_increment column also results in max_value + 1
mysql> INSERT INTO products (productCode, name, quantity, price) VALUES
('PEC', 'Pencil 2B', 10000, 0.48),
('PEC', 'Pencil 2H', 8000, 0.49);
Query OK, 1 row affected (0.03 sec)
-- Missing columns get their default values
mysql> INSERT INTO products (productCode, name) VALUES ('PEC', 'Pencil HB');
Query OK, 1 row affected (0.04 sec)
-- 2nd column is defined to be NOT NULL
mysql> INSERT INTO products values (NULL, NULL, NULL, NULL, NULL);
ERROR 1048 (23000): Column 'productCode' cannot be null
-- Query the table
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+------------+
| productID | productCode | name
| quantity | price
|

+-----------+-------------+-----------+----------+------------+
|
1001 | PEN
| Pen Red
|
5000 |
1.23 |
|
1002 | PEN
| Pen Blue |
8000 |
1.25 |
|
1003 | PEN
| Pen Black |
2000 |
1.25 |
|
1004 | PEC
| Pencil 2B |
10000 |
0.48 |
|
1005 | PEC
| Pencil 2H |
8000 |
0.49 |
|
1006 | PEC
| Pencil HB |
0 | 9999999.99 |
+-----------+-------------+-----------+----------+------------+
6 rows in set (0.02 sec)
-- Remove the last row
mysql> DELETE FROM products WHERE productID = 1006;

Syntex
We can use the INSERT INTO statement to insert a new row with all the column values, using the
following syntax:
INSERT INTO tableName VALUES (firstColumnValue, ..., lastColumnValue)

-- All columns

You need to list the values in the same order in which the columns are defined in the CREATE TABLE,
separated by commas. For columns of string data type (CHAR, VARCHAR), enclosed the value with a
pair of single quotes (or double quotes). For
(INT, DECIMAL, FLOAT, DOUBLE), simply place the number.

columns

of

numeric

data

type

You can also insert multiple rows in one INSERT INTO statement:
INSERT INTO tableName VALUES
(row1FirstColumnValue, ..., row1lastColumnValue),
(row2FirstColumnValue, ..., row2lastColumnValue),
...

To insert a row with values on selected columns only, use:

-- Insert single record with selected columns
INSERT INTO tableName (column1Name, ..., columnNName) VALUES (column1Value, ...,
columnNValue)
-- Alternately, use SET to set the values
INSERT INTO tableName SET column1=value1, column2=value2, ...
-- Insert multiple records
INSERT INTO tableName
(column1Name, ..., columnNName)
VALUES
(row1column1Value, ..., row2ColumnNValue),
(row2column1Value, ..., row2ColumnNValue),
...

The remaining columns will receive their default value, such as AUTO_INCREMENT, default, or NULL.

2.5 Querying the Database - SELECT

The most common, important and complex task is to query a database for a subset of data that
meets your needs - with the SELECT command. The SELECT command has the following syntax:
-- List all the rows of the specified columns
SELECT column1Name, column2Name, ... FROM tableName
-- List all the rows of ALL columns, * is a wildcard denoting all columns
SELECT * FROM tableName
-- List rows that meet the specified criteria in WHERE clause
SELECT column1Name, column2Name,... FROM tableName WHERE criteria
SELECT * FROM tableName WHERE criteria

For examples,
-- List all rows for the specified columns
mysql> SELECT name, price FROM products;
+-----------+-------+
| name
| price |
+-----------+-------+
| Pen Red
| 1.23 |
| Pen Blue | 1.25 |
| Pen Black | 1.25 |
| Pencil 2B | 0.48 |
| Pencil 2H | 0.49 |
+-----------+-------+
5 rows in set (0.00 sec)
-- List all rows of ALL the columns. The wildcard * denotes ALL columns
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+-----------+----------+-------+
|
1001 | PEN
| Pen Red
|
5000 | 1.23 |
|
1002 | PEN
| Pen Blue |
8000 | 1.25 |
|
1003 | PEN
| Pen Black |
2000 | 1.25 |
|
1004 | PEC
| Pencil 2B |
10000 | 0.48 |
|
1005 | PEC
| Pencil 2H |
8000 | 0.49 |
+-----------+-------------+-----------+----------+-------+
5 rows in set (0.00 sec)

SELECT without Table

You can also issue SELECT without a table. For example, you can SELECT an expression or evaluate a
built-in function.
mysql> SELECT 1+1;
+-----+
| 1+1 |

+-----+
|
2 |
+-----+
1 row in set (0.00 sec)
mysql> SELECT NOW();
+---------------------+
| NOW()
|
+---------------------+
| 2012-10-24 22:13:29 |
+---------------------+
1 row in set (0.00 sec)
// Multiple columns
mysql> SELECT 1+1, NOW();
+-----+---------------------+
| 1+1 | NOW()
|
+-----+---------------------+
|
2 | 2012-10-24 22:16:34 |
+-----+---------------------+
1 row in set (0.00 sec)

Comparison Operators
For numbers (INT, DECIMAL, FLOAT), you could use comparison operators: '=' (equal
to), '<>' or '!=' (not equal to), '>' (greater than), '<' (less than), '>=' (greater than or equal
to), '<='(less than or equal to), to compare two numbers. For example, price > 1.0, quantity <=
500.
mysql> SELECT name, price FROM products WHERE price < 1.0;
+-----------+-------+
| name
| price |
+-----------+-------+
| Pencil 2B | 0.48 |
| Pencil 2H | 0.49 |
+-----------+-------+
2 rows in set (0.00 sec)
mysql> SELECT name, quantity FROM products WHERE quantity <= 2000;
+-----------+----------+
| name
| quantity |
+-----------+----------+
| Pen Black |
2000 |
+-----------+----------+
1 row in set (0.00 sec)

CAUTION: Do not compare FLOATs (real numbers) for equality ('=' or '<>'), as they are not precise.
On the other hand, DECIMAL are precise.

For strings, you could also use '=', '<>', '>', '<', '>=', '<=' to compare two strings
(e.g., productCode = 'PEC'). The ordering of string depends on the so-called collation chosen. For
example,
mysql> SELECT name, price FROM products WHERE productCode = 'PEN';
-- String values are quoted
+-----------+-------+
| name
| price |
+-----------+-------+
| Pen Red
| 1.23 |
| Pen Blue | 1.25 |
| Pen Black | 1.25 |
+-----------+-------+
3 rows in set (0.00 sec)

String Pattern Matching - LIKE and NOT LIKE

For strings, in addition to full matching using operators like '=' and '<>', we can perform pattern
matching using operator LIKE (or NOT LIKE) with wildcard characters. The wildcard '_'matches any
single character; '%' matches any number of characters (including zero). For example,

'abc%' matches strings beginning with 'abc';

'%xyz' matches strings ending with 'xyz';

'%aaa%' matches strings containing 'aaa';

'___' matches strings containing exactly three characters; and

'a_b%' matches strings beginning with 'a', followed by any single character, followed by 'b',

followed by zero or more characters.

-- "name" begins with 'PENCIL'
mysql> SELECT name, price FROM products WHERE name LIKE 'PENCIL%';
+-----------+-------+
| name
| price |
+-----------+-------+
| Pencil 2B | 0.48 |
| Pencil 2H | 0.49 |
+-----------+-------+
-- "name" begins with 'P', followed by any two characters,
-followed by space, followed by zero or more characters
mysql> SELECT name, price FROM products WHERE name LIKE 'P__ %';
+-----------+-------+
| name
| price |
+-----------+-------+
| Pen Red
| 1.23 |
| Pen Blue | 1.25 |
| Pen Black | 1.25 |
+-----------+-------+

MySQL also support regular expression matching via the REGEXE operator.

Arithmetic Operators
You can perform arithmetic operations on numeric fields using arithmetic operators, as tabulated
below:

Operator

Description

Addition

Subtraction

Multiplication

Division

DIV
%

Integer Division
Modulus (Remainder)

Logical Operators - AND, OR, NOT, XOR

You can combine multiple conditions with boolean operators AND, OR, XOR. You can also invert a
condition using operator NOT. For examples,
mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %';
+-----------+-------------+----------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+----------+----------+-------+
|
1001 | PEN
| Pen Red |
5000 | 1.23 |
|
1002 | PEN
| Pen Blue |
8000 | 1.25 |
+-----------+-------------+----------+----------+-------+
mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE
'Pen %';
+-----------+-------------+---------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+---------+----------+-------+
|
1001 | PEN
| Pen Red |
5000 | 1.23 |
+-----------+-------------+---------+----------+-------+
mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %');
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+-----------+----------+-------+
|
1003 | PEN
| Pen Black |
2000 | 1.25 |
|
1004 | PEC
| Pencil 2B |
10000 | 0.48 |
|
1005 | PEC
| Pencil 2H |
8000 | 0.49 |

+-----------+-------------+-----------+----------+-------+

IN, NOT IN
You can select from members of a set with IN (or NOT IN) operator. This is easier and clearer than the
equivalent AND-OR expression.
mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black');
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+-----------+----------+-------+
|
1001 | PEN
| Pen Red
|
5000 | 1.23 |
|
1003 | PEN
| Pen Black |
2000 | 1.25 |
+-----------+-------------+-----------+----------+-------+

BETWEEN, NOT BETWEEN

To check if the value is within a range, you could use BETWEEN ... AND ... operator. Again, this is
easier and clearer than the equivalent AND-OR expression.
mysql> SELECT * FROM products
WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000);
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+-----------+----------+-------+
|
1003 | PEN
| Pen Black |
2000 | 1.25 |
+-----------+-------------+-----------+----------+-------+

IS NULL, IS NOT NULL

NULL is a special value, which represent "no value", "missing value" or "unknown value". You can

checking if a column contains NULL by IS NULL or IS NOT NULL. For example,

mysql> SELECT * FROM products WHERE productCode IS NULL;
Empty set (0.00 sec)

Using comparison operator (such as = or <>) to check for NULL is a mistake - a very common mistake.
For example,
SELECT * FROM products WHERE productCode = NULL;
-- This is a common mistake. NULL cannot be compared.

ORDER BY Clause
You can order the rows selected using ORDER BY clause, with the following syntax:
SELECT ... FROM tableName
WHERE criteria
ORDER BY columnA ASC|DESC, columnB ASC|DESC, ...

The selected row will be ordered according to the values in columnA, in either ascending (ASC)
(default) or descending (DESC) order. If several rows have the same value in columnA, it will be

ordered according to columnB, and so on. For strings, the ordering could be case-sensitive or caseinsensitive, depending on the so-called character collating sequence used. For examples,
-- Order the results by price in descending order
mysql> SELECT * FROM products WHERE name LIKE 'Pen %' ORDER BY price DESC;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+-----------+----------+-------+
|
1002 | PEN
| Pen Blue |
8000 | 1.25 |
|
1003 | PEN
| Pen Black |
2000 | 1.25 |
|
1001 | PEN
| Pen Red
|
5000 | 1.23 |
+-----------+-------------+-----------+----------+-------+
-- Order by price in descending order, followed by quantity in ascending (default)
order
mysql> SELECT * FROM products WHERE name LIKE 'Pen %' ORDER BY price DESC, quantity;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+-----------+----------+-------+
|
1003 | PEN
| Pen Black |
2000 | 1.25 |
|
1002 | PEN
| Pen Blue |
8000 | 1.25 |
|
1001 | PEN
| Pen Red
|
5000 | 1.23 |
+-----------+-------------+-----------+----------+-------+

You can randomize the returned records via function RAND(), e.g.,
mysql> SELECT * FROM products ORDER BY RAND();

LIMIT Clause
A SELECT query on a large database may produce many rows. You could use the LIMIT clause to
limit the number of rows displayed, e.g.,
-- Display the first two rows
mysql> SELECT * FROM products ORDER BY price LIMIT 2;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+-----------+----------+-------+
|
1004 | PEC
| Pencil 2B |
10000 | 0.48 |
|
1005 | PEC
| Pencil 2H |
8000 | 0.49 |
+-----------+-------------+-----------+----------+-------+

To continue to the following records , you could specify the number of rows to be skipped, followed
by the number of rows to be displayed in the LIMIT clause, as follows:
-- Skip the first two rows and display the next 1 row
mysql> SELECT * FROM products ORDER BY price LIMIT 2, 1;
+-----------+-------------+---------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+---------+----------+-------+

|
1001 | PEN
| Pen Red |
5000 | 1.23 |
+-----------+-------------+---------+----------+-------+

AS - Alias
You could use the keyword AS to define an alias for an identifier (such as column name, table name).
The alias will be used in displaying the name. It can also be used as reference. For example,
mysql> SELECT productID AS ID, productCode AS Code, name AS Description, price AS
`Unit Price`
-- Define aliases to be used as display names
FROM products
ORDER BY ID;
-- Use alias ID as reference
+------+------+-------------+------------+
| ID
| Code | Description | Unit Price |
+------+------+-------------+------------+
| 1001 | PEN | Pen Red
|
1.23 |
| 1002 | PEN | Pen Blue
|
1.25 |
| 1003 | PEN | Pen Black
|
1.25 |
| 1004 | PEC | Pencil 2B
|
0.48 |
| 1005 | PEC | Pencil 2H
|
0.49 |
+------+------+-------------+------------+

Take note that the identifier "Unit Price" contains a blank and must be back-quoted.

Function CONCAT()
You can also concatenate a few columns as one (e.g., joining the last name and first name) using
function CONCAT(). For example,
mysql> SELECT CONCAT(productCode, ' - ', name) AS `Product Description`, price FROM
products;
+---------------------+-------+
| Product Description | price |
+---------------------+-------+
| PEN - Pen Red
| 1.23 |
| PEN - Pen Blue
| 1.25 |
| PEN - Pen Black
| 1.25 |
| PEC - Pencil 2B
| 0.48 |
| PEC - Pencil 2H
| 0.49 |
+---------------------+-------+

2.6 Producing Summary Reports

To produce a summary report, we often need to aggregate related rows.

DISTINCT
A column may have duplicate values, we could use keyword DISTINCT to select only distinct values.
We can also apply DISTINCT to several columns to select distinct combinations of these columns. For
examples,

-- Without DISTINCT
mysql> SELECT price FROM products;
+-------+
| price |
+-------+
| 1.23 |
| 1.25 |
| 1.25 |
| 0.48 |
| 0.49 |
+-------+
-- With DISTINCT on price
mysql> SELECT DISTINCT price AS `Distinct Price` FROM products;
+----------------+
| Distinct Price |
+----------------+
|
1.23 |
|
1.25 |
|
0.48 |
|
0.49 |
+----------------+
-- DISTINCT combination of price and name
mysql> SELECT DISTINCT price, name FROM products;
+-------+-----------+
| price | name
|
+-------+-----------+
| 1.23 | Pen Red
|
| 1.25 | Pen Blue |
| 1.25 | Pen Black |
| 0.48 | Pencil 2B |
| 0.49 | Pencil 2H |
+-------+-----------+

GROUP BY Clause
The GROUP BY clause allows you to collapse multiple records with a common value into groups. For
example,
mysql> SELECT * FROM products ORDER BY productCode, productID;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+-----------+----------+-------+
|
1004 | PEC
| Pencil 2B |
10000 | 0.48 |
|
1005 | PEC
| Pencil 2H |
8000 | 0.49 |
|
1001 | PEN
| Pen Red
|
5000 | 1.23 |
|
1002 | PEN
| Pen Blue |
8000 | 1.25 |
|
1003 | PEN
| Pen Black |
2000 | 1.25 |
+-----------+-------------+-----------+----------+-------+

mysql> SELECT * FROM products GROUP BY productCode;

-- Only first record in each group is shown
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+-----------+----------+-------+
|
1004 | PEC
| Pencil 2B |
10000 | 0.48 |
|
1001 | PEN
| Pen Red
|
5000 | 1.23 |
+-----------+-------------+-----------+----------+-------+
GROUP BY by itself is not meaningfull. It is used together with GROUP BY aggregate functions (such

as COUNT(), AVG(), SUM()) to produce group summary.

GROUP BY Aggregate Functions: COUNT, MAX, MIN, AVG, SUM, STD, GROUP_CONCAT
We can apply GROUP BY Aggregate functions to each group to produce group summary report.
The function COUNT(*) returns the rows selected; COUNT(columnName) counts only the nonNULL values of the given column. For example,
-- Function COUNT(*) returns the number of rows selected
mysql> SELECT COUNT(*) AS `Count` FROM products;
-- All rows without GROUP BY clause
+-------+
| Count |
+-------+
|
5 |
+-------+
mysql> SELECT productCode, COUNT(*) FROM products GROUP BY productCode;
+-------------+----------+
| productCode | COUNT(*) |
+-------------+----------+
| PEC
|
2 |
| PEN
|
3 |
+-------------+----------+
-- Order by COUNT - need to define an alias to be used as reference
mysql> SELECT productCode, COUNT(*) AS count
FROM products
GROUP BY productCode
ORDER BY count DESC;
+-------------+-------+
| productCode | count |
+-------------+-------+
| PEN
|
3 |
| PEC
|
2 |
+-------------+-------+

Besides COUNT(),
there
are
many
other GROUP
as AVG(), MAX(), MIN() and SUM(). For example,

BY aggregate

functions

such

mysql> SELECT MAX(price), MIN(price), AVG(price), STD(price), SUM(quantity)

FROM products;
-- Without GROUP BY - All rows
+------------+------------+------------+------------+---------------+
| MAX(price) | MIN(price) | AVG(price) | STD(price) | SUM(quantity) |
+------------+------------+------------+------------+---------------+
|
1.25 |
0.48 |
0.940000 |
0.371591 |
33000 |
+------------+------------+------------+------------+---------------+
mysql> SELECT productCode, MAX(price) AS `Highest Price`, MIN(price) AS `Lowest
Price`
FROM products
GROUP BY productCode;
+-------------+---------------+--------------+
| productCode | Highest Price | Lowest Price |
+-------------+---------------+--------------+
| PEC
|
0.49 |
0.48 |
| PEN
|
1.25 |
1.23 |
+-------------+---------------+--------------+
mysql> SELECT productCode, MAX(price), MIN(price),
CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`,
CAST(STD(price) AS DECIMAL(7,2)) AS `Std Dev`,
SUM(quantity)
FROM products
GROUP BY productCode;
-- Use CAST(... AS ...) function to format floating-point numbers
+-------------+------------+------------+---------+---------+---------------+
| productCode | MAX(price) | MIN(price) | Average | Std Dev | SUM(quantity) |
+-------------+------------+------------+---------+---------+---------------+
| PEC
|
0.49 |
0.48 |
0.49 |
0.01 |
18000 |
| PEN
|
1.25 |
1.23 |
1.24 |
0.01 |
15000 |
+-------------+------------+------------+---------+---------+---------------+

HAVING clause
HAVING is

similar to WHERE, but it can

whereas WHERE operates only on columns.

operate

on

the GROUP

mysql> SELECT
productCode AS `Product Code`,
COUNT(*) AS `Count`,
CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`
FROM products
GROUP BY productCode
HAVING Count >=3;

BY aggregate

functions;

-- CANNOT use WHERE count >= 3

+--------------+-------+---------+
| Product Code | Count | Average |
+--------------+-------+---------+
| PEN
|
3 |
1.24 |
+--------------+-------+---------+

WITH ROLLUP
The WITH ROLLUP clause shows the summary of group summary, e.g.,
mysql> SELECT
productCode,
MAX(price),
MIN(price),
CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`,
SUM(quantity)
FROM products
GROUP BY productCode
WITH ROLLUP;
-- Apply aggregate functions to all groups
+-------------+------------+------------+---------+---------------+
| productCode | MAX(price) | MIN(price) | Average | SUM(quantity) |
+-------------+------------+------------+---------+---------------+
| PEC
|
0.49 |
0.48 |
0.49 |
18000 |
| PEN
|
1.25 |
1.23 |
1.24 |
15000 |
| NULL
|
1.25 |
0.48 |
0.94 |
33000 |
+-------------+------------+------------+---------+---------------+

2.7 Modifying Data - UPDATE

To modify existing data, use UPDATE ... SET command, with the following syntax:
UPDATE tableName SET columnName = {value|NULL|DEFAULT}, ... WHERE criteria

For example,
-- Increase the price by 10% for all products
mysql> UPDATE products SET price = price * 1.1;
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+------------+
| productID | productCode | name
| quantity | price
|
+-----------+-------------+-----------+----------+------------+
|
1001 | PEN
| Pen Red
|
5000 |
1.35 |
|
1002 | PEN
| Pen Blue |
8000 |
1.38 |
|
1003 | PEN
| Pen Black |
2000 |
1.38 |
|
1004 | PEC
| Pencil 2B |
10000 |
0.53 |
|
1005 | PEC
| Pencil 2H |
0 | 9999999.99 |

+-----------+-------------+-----------+----------+------------+
-- Modify selected rows
mysql> UPDATE products SET quantity = quantity - 100 WHERE name = 'Pen Red';
mysql> SELECT * FROM products WHERE name = 'Pen Red';
+-----------+-------------+---------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+---------+----------+-------+
|
1001 | PEN
| Pen Red |
4900 | 1.35 |
+-----------+-------------+---------+----------+-------+
-- You can modify more than one values
mysql> UPDATE products SET quantity = quantity + 50, price = 1.23 WHERE name = 'Pen
Red';
mysql> SELECT * FROM products WHERE name = 'Pen Red';
+-----------+-------------+---------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+---------+----------+-------+
|
1001 | PEN
| Pen Red |
4950 | 1.23 |
+-----------+-------------+---------+----------+-------+

CAUTION: If the WHERE clause is omitted in the UPDATE command, ALL ROWS will be updated. Hence,
it is a good practice to issue a SELECT query, using the same criteria, to check the result set before
issuing the UPDATE. This also applies to the DELETE statement in the following section.

2.8 Deleting Rows - DELETE FROM

Use the DELELE FROM command to delete row(s) from a table, with the following syntax:
-- Delete all rows from the table. Use with
recoverable!!!
DELETE FROM tableName
-- Delete only row(s) that meets the criteria
DELETE FROM tableName WHERE criteria

extreme

For example,
mysql> DELETE FROM products WHERE name LIKE 'Pencil%';
Query OK, 2 row affected (0.00 sec)
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+-----------+----------+-------+
|
1001 | PEN
| Pen Red
|
4950 | 1.23 |
|
1002 | PEN
| Pen Blue |
8000 | 1.38 |
|
1003 | PEN
| Pen Black |
2000 | 1.38 |
+-----------+-------------+-----------+----------+-------+

care!

Records

are

NOT

-- Use this with extreme care, as the deleted records are irrecoverable!
mysql> DELETE FROM products;
Query OK, 3 rows affected (0.00 sec)
mysql> SELECT * FROM products;
Empty set (0.00 sec)

Beware that "DELETE FROM tableName" without a WHERE clause deletes ALL records from the table.
Even with a WHERE clause, you might have deleted some records unintentionally. It is always advisable
to issue a SELECT command with the same WHERE clause to check the result set before issuing
the DELETE (and UPDATE).

2.9 Loading/Exporting Data from/to a Text File

There are several ways to add data into the database: (a) manually issue the INSERT commands; (b)
run the INSERT commands from a script; or (c) load raw data from a file using LOAD DATA or
via mysqlimport utility.

LOAD DATA LOCAL INFILE ... INTO TABLE ...

Besides using INSERT commands to insert rows, you could keep your raw data in a text file, and load
them into the table via the LOAD DATA command. For example, create the following text file called
"products_in.csv", where the values are separated by ','. The file extension of ".csv" stands
for Comma-Separated Values text file.
\N,PEC,Pencil 3B,500,0.52
\N,PEC,Pencil 4B,200,0.62
\N,PEC,Pencil 5B,100,0.73
\N,PEC,Pencil 6B,500,0.47

You can load the raw data into the products table as follows:
mysql> LOAD DATA LOCAL INFILE 'd:/path-to/products_in.csv' INTO TABLE products
COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n';
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name
| quantity | price |
+-----------+-------------+-----------+----------+-------+
|
1005 | PEC
| Pencil 3B |
500 | 0.52 |
|
1006 | PEC
| Pencil 4B |
200 | 0.62 |
|
1007 | PEC
| Pencil 5B |
100 | 0.73 |
|
1008 | PEC
| Pencil 6B |
500 | 0.47 |

+-----------+-------------+-----------+----------+-------+

Notes:

You need to provide the path (absolute or relative) and the filename. Use Unix-style forwardslash '/' as the directory separator, instead of Windows-style back-slash '\'.
The default line delimiter (or end-of-line) is '\n' (Unix-style). If the text file is prepared in
Windows, you need to include LINES TERMINATED BY '\r\n'. For Mac, use LINES TERMINATED
BY '\r'.

The default column delimiter is "tab" (in a so-called TSV file - Tab-Separated Values). If you use
another delimiter, e.g. ',', include COLUMNS TERMINATED BY ','.
You must use \N (back-slash + uppercase 'N') for NULL.

mysqlimport Utility
You can also use the mysqlimport utility to load data from a text file. For example,
-- Syntax
> mysqlimport -u username -p --local databaseName tableName.tsv
-- The raw data must be kept in a TSV file of filename the same as tablename
> mysqlimport --help
-- Example
> mysqlimport -u username -p --local southwind d:/myproject/products_in.tsv

SELECT ... INTO OUTFILE ...

Complimenting LOAD

DATA command,

you
can
use SELECT
...
OUTFILE fileName FROM tableName to export data from a table to a text file. For example,

INTO

mysql> SELECT * FROM products INTO OUTFILE 'd:/path-to/products_out.csv'

COLUMNS TERMINATED BY ','
LINES TERMINATED BY '\r\n';
Query OK, 4 rows affected (0.00 sec)

2.10 Running a SQL Script

Instead of manually entering each of the SQL statements, you can keep many SQL statements in a
text file, called SQL script, and run the script. For example, use a programming text editor to prepare
the following script and save as "load_products.sql" in a directory of your choice (e.g.,
"d:\myproject").
DELETE FROM products;
INSERT INTO products VALUES (2001, 'PEC', 'Pencil 3B', 500, 0.52),
(NULL, 'PEC', 'Pencil 4B', 200, 0.62),
(NULL, 'PEC', 'Pencil 5B', 100, 0.73),
(NULL, 'PEC', 'Pencil 6B', 500, 0.47);

SELECT * FROM products;

You can run the script either:

1. via the "source" command
the southwind backup earlier:
2.
3.
4.
5.
6.

in

an

interactive

client.

For

example,

to

restore

-- Start mysql client

D:\myproject\mysql\bin> mysql -u username -p
......
mysql> source d:/myproject/load_products.sql
-- You can provide either absolute or relative filename of the script
-- Use Unix-style forward slash (/) as directory separator

7. via the "batch mode" of the mysql client program, by re-directing the input from the script:
> mysql -u username -p southwind < d:\myproject\load_products.sql

3. More Than One Tables

Our example so far involves only one table "products". A practical database contains
many related tables.
Products have suppliers. If each product has one supplier, and each supplier supplies only one
product (known as one-to-one relationship), we can simply add the supplier's data (name, address,
phone number) into the products table. Suppose that each product has one supplier, and a supplier
may supply zero or more products (known as one-to-many relationship). Putting the supplier's data
into the Product table results in duplication of data. This is because one supplier may supply many
products, hence, the same supplier's data appear in many rows. This not only wastes the storage but
also easily leads to inconsistency (as all duplicate data must be updated simultaneously). The
situation is even more complicated if one product has many suppliers, and each supplier can supply
many products, in a many-to-many relationship.

3.1 One-To-Many Relationship

Suppose that each product has one supplier, and each supplier supplies one or more products. We
could create a table called suppliers to store suppliers' data (e.g., name, address and phone
number). We create a column with unique value called supplierID to identify every suppliers. We
set supplierID as the primary key for the table suppliers (to ensure uniqueness and facilitate fast
search).
To relate the suppliers table to the products table, we add a new column into the products table the supplierID. We then set the supplierID column of the products table as a foreign key
references the supplierID column of the suppliers table to ensure the so-called referential
integrity.

Database: southwind
Table: suppliers
supplierID
INT

name
VARCHAR(3)

phone
CHAR(8)

501

ABC Traders

88881111

502

XYZ Company

88882222

503

QQ Corp

88883333

Database: southwind
Table: products

productID
INT

productCode
CHAR(3)

name
VARCHAR(30)

quantity
INT

price
DECIMAL(10,2)

sup

(Fore

2001

PEC

Pencil 3B

500

0.52

501

2002

PEC

Pencil 4B

200

0.62

501

2003

PEC

Pencil 5B

100

0.73

501

2004

PEC

Pencil 6B

500

0.47

502

We need to first create the suppliers table, because the products table references
the suppliers table. The suppliers table is known as the parent table; while the products table is
known as the child table in this relationship.
mysql> USE southwind;
mysql> DROP TABLE IF EXISTS suppliers;
mysql> CREATE TABLE suppliers (
supplierID INT UNSIGNED
name
VARCHAR(30)
phone
CHAR(8)
PRIMARY KEY (supplierID)
);

NOT NULL AUTO_INCREMENT,

NOT NULL DEFAULT '',
NOT NULL DEFAULT '',

mysql> DESCRIBE suppliers;

+------------+------------------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+------------+------------------+------+-----+---------+----------------+
| supplierID | int(10) unsigned | NO
| PRI | NULL
| auto_increment |
| name
| varchar(30)
| NO
|
|
|
|
| phone
| char(8)
| NO
|
|
|
|
+------------+------------------+------+-----+---------+----------------+
mysql> INSERT INTO suppliers VALUE
(501, 'ABC Traders', '88881111'),
(502, 'XYZ Company', '88882222'),
(503, 'QQ Corp', '88883333');
mysql> SELECT * FROM suppliers;
+------------+-------------+----------+
| supplierID | name
| phone
|
+------------+-------------+----------+
|
501 | ABC Traders | 88881111 |
|
502 | XYZ Company | 88882222 |
|
503 | QQ Corp
| 88883333 |
+------------+-------------+----------+

ALTER TABLE
Instead of deleting and re-creating the products table, we shall use the statement "ALTER TABLE" to
add a new column supplierID into the products table.
mysql> ALTER TABLE products
ADD COLUMN supplierID INT UNSIGNED NOT NULL;
Query OK, 4 rows affected (0.13 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> DESCRIBE products;
+-------------+------------------+------+-----+------------+----------------+
| Field
| Type
| Null | Key | Default
| Extra
|
+-------------+------------------+------+-----+------------+----------------+
| productID
| int(10) unsigned | NO
| PRI | NULL
| auto_increment |
| productCode | char(3)
| NO
|
|
|
|
| name
| varchar(30)
| NO
|
|
|
|
| quantity
| int(10) unsigned | NO
|
| 0
|
|
| price
| decimal(10,2)
| NO
|
| 9999999.99 |
|
| supplierID | int(10) unsigned | NO
|
| NULL
|
|
+-------------+------------------+------+-----+------------+----------------+

Next, we shall add a foreign key constraint on the supplierID columns of the products child table to
the suppliers parent table, to ensure that every supplierID in the products table always refers to
a valid supplierID in the suppliers table - this is called referential integrity.

Before we can add the foreign key, we need to set the supplierID of the existing records in
the products table to a valid supplierID in the suppliers table (say supplierID=501).
-- Set the supplierID of the existing records to a valid supplierID of supplier table
mysql> UPDATE products SET supplierID = 501;
-- Add a foreign key constrain
mysql> ALTER TABLE products
ADD FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID);
Query OK, 4 rows affected (0.26 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> DESCRIBE products;
+-------------+------------------+------+-----+------------+----------------+
| Field
| Type
| Null | Key | Default
| Extra
|
+-------------+------------------+------+-----+------------+----------------+
......
| supplierID | int(10) unsigned | NO
| MUL |
|
|
+-------------+------------------+------+-----+------------+----------------+
mysql> UPDATE products SET supplierID = 502 WHERE productID

= 2004;

mysql> SELECT * FROM products;

+-----------+-------------+-----------+----------+-------+------------+
| productID | productCode | name
| quantity | price | supplierID |
+-----------+-------------+-----------+----------+-------+------------+
|
2001 | PEC
| Pencil 3B |
500 | 0.52 |
501 |
|
2002 | PEC
| Pencil 4B |
200 | 0.62 |
501 |
|
2003 | PEC
| Pencil 5B |
100 | 0.73 |
501 |
|
2004 | PEC
| Pencil 6B |
500 | 0.47 |
502 |
+-----------+-------------+-----------+----------+-------+------------+

The syntax for ALTER TABLE is as follows:

ALTER TABLE tableName
{ADD [COLUMN] columnName columnDefinition}
{ALTER|MODIFY [COLUMN] columnName columnDefinition
{SET DEFAULT columnDefaultValue} | {DROP DEFAULT}}
{DROP [COLUMN] columnName [RESTRICT|CASCADE]}
{ADD tableConstraint}
{DROP tableConstraint [RESTRICT|CASCADE]}

SELECT with JOIN

SELECT command can be used to query and join data from two related tables. For example, to list the

product's name (in products table) and supplier's name (in suppliers table), we could join the two
table via the two common supplierID columns:
-- ANSI style: JOIN ... ON ...
mysql> SELECT products.name, price, suppliers.name

FROM products
JOIN suppliers ON products.supplierID = suppliers.supplierID
WHERE price < 0.6;
+-----------+-------+-------------+
| name
| price | name
|
+-----------+-------+-------------+
| Pencil 3B | 0.52 | ABC Traders |
| Pencil 6B | 0.47 | XYZ Company |
+-----------+-------+-------------+
-- Need to use products.name and suppliers.name to differentiate the two "names"
-- Join via WHERE clause (lagacy and not recommended)
mysql> SELECT products.name, price, suppliers.name
FROM products, suppliers
WHERE products.supplierID = suppliers.supplierID
AND price < 0.6;
+-----------+-------+-------------+
| name
| price | name
|
+-----------+-------+-------------+
| Pencil 3B | 0.52 | ABC Traders |
| Pencil 6B | 0.47 | XYZ Company |
+-----------+-------+-------------+

In the above query result, two of the columns have the same heading " name". We could
create aliases for headings.
-- Use aliases for column names for display
mysql> SELECT products.name AS `Product Name`, price, suppliers.name AS `Supplier
Name`
FROM products
JOIN suppliers ON products.supplierID = suppliers.supplierID
WHERE price < 0.6;
+--------------+-------+---------------+
| Product Name | price | Supplier Name |
+--------------+-------+---------------+
| Pencil 3B
| 0.52 | ABC Traders
|
| Pencil 6B
| 0.47 | XYZ Company
|
+--------------+-------+---------------+
-- Use aliases for table names too
mysql> SELECT p.name AS `Product Name`, p.price, s.name AS `Supplier Name`
FROM products AS p
JOIN suppliers AS s ON p.supplierID = s.supplierID
WHERE p.price < 0.6;

The database diagram is as illustrated. The link indicates a one-to-many relationship

between products and suppliers.

3.2 Many-To-Many Relationship

Suppose that a product has many suppliers; and a supplier supplies many products in a so-called
many-to-many relationship. The above solution breaks. You cannot include the supplierID in
the products table, as you cannot determine the number of suppliers, and hence, the number of
columns needed for the supplierIDs. Similarly, you cannot
thesuppliers table, as you cannot determine the number of products.

include

the productID in

To resolve this problem, you need to create a new table, known as a junction table (or joint table), to
provide the linkage. Let's call the junction table products_suppliers, as illustrated.

Database: southwind
Table: products_suppliers
productID
INT
(Foreign Key)

supplierID
INT
(Foreign Key)

2001

501

2002

501

2003

501

2004

502

2001

503

Database: southwind
Table: suppliers
supplierID
INT

name
VARCHAR(30)

phone
CHAR(8)

501

ABC Traders

88881111

502

XYZ Company

88882222

503

QQ Corp

88883333

Database: southwind
Table: products
productID
INT

productCode
CHAR(3)

name
VARCHAR(30)

quantity
INT

price
DECIMAL(10,2)

2001

PEC

Pencil 3B

500

0.52

2002

PEC

Pencil 4B

200

0.62

2003

PEC

Pencil 5B

100

0.73

2004

PEC

Pencil 6B

500

0.47

Let's create the products_suppliers table. The primary key of the table consists of two
columns: productID and supplierID, as their combination uniquely identifies each rows. This
primary key is defined to ensure uniqueness. Two foreign keys are defined to set the constraint to
the two parent tables.
mysql> CREATE TABLE products_suppliers (
productID
INT UNSIGNED NOT NULL,
supplierID INT UNSIGNED NOT NULL,
-- Same data types as the parent tables
PRIMARY KEY (productID, supplierID),
-- uniqueness
FOREIGN KEY (productID) REFERENCES products (productID),
FOREIGN KEY (supplierID) REFERENCES suppliers (supplierID)
);

mysql> DESCRIBE products_suppliers;

+------------+------------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| productID | int(10) unsigned | NO
| PRI | NULL
|
|
| supplierID | int(10) unsigned | NO
| PRI | NULL
|
|
+------------+------------------+------+-----+---------+-------+
mysql> INSERT INTO products_suppliers VALUES (2001, 501), (2002, 501),
(2003, 501), (2004, 502), (2001, 503);
-- Values in the foreign-key columns (of the child table) must match
-valid values in the columns they reference (of the parent table)
mysql> SELECT * FROM products_suppliers;
+-----------+------------+
| productID | supplierID |
+-----------+------------+
|
2001 |
501 |
|
2002 |
501 |
|
2003 |
501 |
|
2004 |
502 |
|
2001 |
503 |
+-----------+------------+

Next, remove the supplierID column from the products table. (This column was added to establish
the one-to-many relationship. It is no longer needed in the many-to-many relationship.)
Before this column can be removed, you need to remove the foreign key that builds on this column.
To remove a key in MySQL, you need to know its constraint name, which was generated by the
system. To find the constraint name, issue a "SHOW CREATE TABLE products" and take note of the
foreign key's constraint name in the clause "CONSTRAINT constraint_name FOREIGN KEY .... ". You
can then drop the foreign key using "ALTER
TABLE
products
DROP
FOREIGN
KEY constraint_name"
mysql> SHOW CREATE TABLE products \G
Create Table: CREATE TABLE `products` (
`productID`
int(10) unsigned NOT NULL AUTO_INCREMENT,
`productCode` char(3)
NOT NULL DEFAULT '',
`name`
varchar(30)
NOT NULL DEFAULT '',
`quantity`
int(10) unsigned NOT NULL DEFAULT '0',
`price`
decimal(7,2)
NOT NULL DEFAULT '99999.99',
`supplierID` int(10) unsigned
NOT NULL DEFAULT '501',
PRIMARY KEY (`productID`),
KEY `supplierID` (`supplierID`),
CONSTRAINT `products_ibfk_1` FOREIGN KEY (`supplierID`)
REFERENCES `suppliers` (`supplierID`)
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=latin1

mysql> ALTER TABLE products DROP FOREIGN KEY products_ibfk_1;

Now, we can remove the column redundant supplierID column.

mysql> ALTER TABLE products DROP supplierID;

Querying
Similarly, we can use SELECT with JOIN to query data from the 3 tables, for examples,
mysql> SELECT products.name AS `Product Name`, price, suppliers.name AS `Supplier
Name`
FROM products_suppliers
JOIN products ON products_suppliers.productID = products.productID
JOIN suppliers ON products_suppliers.supplierID = suppliers.supplierID
WHERE price < 0.6;
+--------------+-------+---------------+
| Product Name | price | Supplier Name |
+--------------+-------+---------------+
| Pencil 3B
| 0.52 | ABC Traders
|
| Pencil 3B
| 0.52 | QQ Corp
|
| Pencil 6B
| 0.47 | XYZ Company
|
+--------------+-------+---------------+
-- Define aliases for tablenames too
mysql> SELECT p.name AS `Product Name`, s.name AS `Supplier Name`
FROM products_suppliers AS ps
JOIN products AS p ON ps.productID = p.productID
JOIN suppliers AS s ON ps.supplierID = s.supplierID
WHERE p.name = 'Pencil 3B';
+--------------+---------------+
| Product Name | Supplier Name |
+--------------+---------------+
| Pencil 3B
| ABC Traders
|
| Pencil 3B
| QQ Corp
|
+--------------+---------------+
-- Using WHERE clause to join (lagacy and not recommended)
mysql> SELECT p.name AS `Product Name`, s.name AS `Supplier Name`
FROM products AS p, products_suppliers AS ps, suppliers AS s
WHERE p.productID = ps.productID
AND ps.supplierID = s.supplierID
AND s.name = 'ABC Traders';
+--------------+---------------+
| Product Name | Supplier Name |
+--------------+---------------+
| Pencil 3B
| ABC Traders
|
| Pencil 4B
| ABC Traders
|
| Pencil 5B
| ABC Traders
|
+--------------+---------------+

The database diagram is as follows. Both products and suppliers tables exhibit a one-to-many
relationship to the junction table. The many-to-many relationship is supported via the junction table.

3.3 One-to-one Relationship

Suppose that some products have optional data (e.g., photo, comment). Instead of keeping these
optional data in the products table, it is more efficient to create another table
calledproduct_details, and link it to products with a one-to-one relationship, as illustrated.

mysql> CREATE TABLE product_details (

productID INT UNSIGNED
NOT NULL,
-- same data type as the parent table
comment
TEXT NULL,
-- up to 64KB
PRIMARY KEY (productID),
FOREIGN KEY (productID) REFERENCES products (productID)
);
mysql> DESCRIBE product_details;
+-----------+------------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| productID | int(10) unsigned | NO
| PRI | NULL
|
|
| comment
| text
| YES |
| NULL
|
|
+-----------+------------------+------+-----+---------+-------+
mysql> SHOW CREATE TABLE product_details \G
*************************** 1. row ***************************
Table: product_details
Create Table: CREATE TABLE `product_details` (
`productID` int(10) unsigned NOT NULL,
`comment`
text,
PRIMARY KEY (`productID`),
CONSTRAINT `product_details_ibfk_1` FOREIGN KEY (`productID`) REFERENCES `products`
(`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

3.4 Backup and Restore

Backup: Before we conclude this example, let's run the mysqldump utility program to dump out
(backup) the entire southwind database.
-- Change the current working directory to \bin
> mysqldump -u guser -p --databases southwind > "d:\myproject\backup_southwind.sql"

Study the output file, which contains CREATE DATABASE, CREATE TABLE and INSERT statements to recreate the tables dumped.
The syntax for the mysqldump program is as follows:
-- Dump selected databases with --databases option
> mysqldump -u username -p --databases database1Name [database2Name ...] >
backupFile.sql
-- Dump all databases in the server with --all-databases option, except mysql.user
table (for security)
> mysqldump -u root -p --all-databases --ignore-table=mysql.user > backupServer.sql
-- Dump all the tables of a particular database
> mysqldump -u username -p databaseName > backupFile.sql
-- Dump selected tables of a particular database
> mysqldump -u username -p databaseName table1Name [table2Name ...] > backupFile.sql

Restore: The

utility mysqldump produces

SQL

script

(consisting

of CREATE

TABLE and INSERT commands to re-create the tables and loading their data). You can restore from

the backup by running the script either:

1. via the "source" command
the southwind backup earlier:
2.
3.
4.
5.
6.

in

an

interactive

client.

For

example,

to

restore

-- Start mysql client

D:\myproject\mysql\bin> mysql -u username -p
......
mysql> source d:/myproject/backup_southwind.sql
-- Provide absolute or relative filename of the script
-- Use Unix-style forward slash (/) as path separator

7. via the "batch mode" of the mysql client program by re-directing the input from the script:
> mysql -u username -p southwind < d:\myproject\backup_southwind.sql

4. More on Primary Key, Foreign Key and Index

4.1 Primary Key
In the relational model, a table shall not contain duplicate rows, because that would create ambiguity
in retrieval. To ensure uniqueness, each table should have a column (or a set of columns),
called primary key, that uniquely identifies every record of the table. For example, an unique

number customerID can

be
used
as
the
primary
key
for
the customers table; productCode forproducts table; isbn for books table. A primary key is called
a simple key if it is a single column; it is called a composite key if it is made up of several columns.
Most RDBMSs build an index on the primary key to facilitate fast search. The primary key is often
used to relate to other tables.

4.2 Foreign Key

A foreign key of a child table is used to reference the parent table. Foreign key constraint can be
imposed to ensure so-called referential integrity - values in the child table must be valid values in the
parent table.
We define the foreign key when defining the child table, which references a parent table, as follows:
-- Child table definition
CREATE TABLE tableName (
......
......
CONSTRAINT constraintName FOREIGN KEY (columName) REFERENCES parentTableName
(columnName)
[ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION]
-- On DELETE reference
action
[ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION]
-- On UPDATE reference
action
)

You can specify the reference action for UPDATE and DELETE via the optional ON UPDATE and ON
DELETE clauses:
1. RESTRICT (default): disallow DELETE or UPDATE of the parent's row, if there are matching rows
in child table.
2. CASCADE: cascade the DELETE or UPDATE action to the matching rows in the child table.
3. SET NULL: set the foreign key value in the child table to NULL (if NULL is allowed).
4. NO ACTION: a SQL term which means no action on the parent's row. Same as RESTRICT in
MySQL, which disallows DELETE or UPDATE (do nothing).
Try
deleting
a
record
in
the suppliers (parent)
table
that
is
referenced
by products_suppliers (child) table, e.g.,
mysql> SELECT * FROM products_suppliers;
+-----------+------------+
| productID | supplierID |
+-----------+------------+
|
2001 |
501 |
|
2002 |
501 |
|
2003 |
501 |
|
2004 |
502 |
|
2001 |
503 |

+-----------+------------+
-- Try deleting a row from parent table with matching rows in the child table
mysql> DELETE FROM suppliers WHERE supplierID = 501;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint
fails
(`southwind`.`products_suppliers`, CONSTRAINT `products_suppliers_ibfk_2`
FOREIGN KEY (`supplierID`) REFERENCES `suppliers` (`supplierID`))

The record cannot be deleted as the default "ON DELETE RESTRICT" constraint was imposed.

4.3 Indexes (or Keys)

Indexes (or Keys) can be created on selected column(s) to facilitate fast search. Without index, a
"SELECT * FROM products WHERE productID=x" needs to match with the productIDcolumn of all
the records in the products table. If productID column is indexed (e.g., using a binary tree), the
matching can be greatly improved (via the binary tree search).
You should index columns which are frequently used in the WHERE clause; and as JOIN columns.
The drawback about indexing is cost and space. Building and maintaining indexes require
computations and memory spaces. Indexes facilitate fast search but deplete the performance on
modifying the table (INSERT/UPDATE/DELETE), and need to be justified. Nevertheless, relational
databases are typically optimized for queries and retrievals, but NOT for updates.
In MySQL, the keyword KEY is synonym to INDEX.
In MySQL, indexes can be built on:
1. a single column (column-index)
2. a set of columns (concatenated-index)
3. on unique-value column (UNIQUE INDEX or UNIQUE KEY)
4. on a prefix of a column for strings (VARCHAR or CHAR), e.g., first 5 characters.
There can be more than one indexes in a table. Index are automatically built on the primary-key
column(s).
You can build index via CREATE TABLE, CREATE INDEX or ALTER TABLE.
CREATE TABLE tableName (
......
[UNIQUE] INDEX|KEY indexName (columnName, ...),
-- The optional keyword UNIQUE ensures that all values in this column are
distinct
-- KEY is synonym to INDEX
......
PRIMAY KEY (columnName, ...) -- Index automatically built on PRIMARY KEY column
);

CREATE [UNIQUE] INDEX indexName ON tableName(columnName, ...);

ALTER TABLE tableName ADD UNIQUE|INDEX|PRIMARY KEY indexName (columnName, ...)
SHOW INDEX FROM tableName;

Example
mysql> CREATE TABLE employees (
emp_no
INT UNSIGNED
NOT
name
VARCHAR(50)
NOT
gender
ENUM ('M','F') NOT
birth_date DATE
NOT
hire_date
DATE
NOT
PRIMARY KEY (emp_no) -- Index
);

NULL AUTO_INCREMENT,
NULL,
NULL,
NULL,
NULL,
built automatically on primary-key column

mysql> DESCRIBE employees;

+------------+------------------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+------------+------------------+------+-----+---------+----------------+
| emp_no
| int(10) unsigned | NO
| PRI | NULL
| auto_increment |
| name
| varchar(50)
| NO
|
| NULL
|
|
| gender
| enum('M','F')
| NO
|
| NULL
|
|
| birth_date | date
| NO
|
| NULL
|
|
| hire_date | date
| NO
|
| NULL
|
|
+------------+------------------+------+-----+---------+----------------+
mysql> SHOW INDEX FROM employees \G
*************************** 1. row ***************************
Table: employees
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: emp_no
.......
mysql> CREATE TABLE departments (
dept_no
CHAR(4)
NOT NULL,
dept_name VARCHAR(40) NOT NULL,
PRIMARY KEY (dept_no),
-- Index built automatically on primary-key column
UNIQUE INDEX (dept_name) -- Build INDEX on this unique-value column
);
mysql> DESCRIBE departments;
+-----------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no
| char(4)
| NO
| PRI | NULL
|
|
| dept_name | varchar(40) | NO
| UNI | NULL
|
|

+-----------+-------------+------+-----+---------+-------+
mysql> SHOW INDEX FROM departments \G
*************************** 1. row ***************************
Table: departments
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: dept_no
.......
*************************** 2. row ***************************
Table: departments
Non_unique: 0
Key_name: dept_name
Seq_in_index: 1
Column_name: dept_name
.......
-- Many-to-many junction table between employees and departments
mysql> CREATE TABLE dept_emp (
emp_no
INT UNSIGNED NOT NULL,
dept_no
CHAR(4)
NOT NULL,
from_date DATE
NOT NULL,
to_date
DATE
NOT NULL,
INDEX
(emp_no),
-- Build INDEX on this non-unique-value
column
INDEX
(dept_no),
-- Build INDEX on this non-unique-value
column
FOREIGN KEY (emp_no) REFERENCES employees (emp_no)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (dept_no) REFERENCES departments (dept_no)
ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (emp_no, dept_no) -- Index built automatically
);
mysql> DESCRIBE dept_emp;
+-----------+------------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| emp_no
| int(10) unsigned | NO
| PRI | NULL
|
|
| dept_no
| char(4)
| NO
| PRI | NULL
|
|
| from_date | date
| NO
|
| NULL
|
|
| to_date
| date
| NO
|
| NULL
|
|
+-----------+------------------+------+-----+---------+-------+
mysql> SHOW INDEX FROM dept_emp \G
*************************** 1. row ***************************
Table: dept_emp
Non_unique: 0
Key_name: PRIMARY

Seq_in_index: 1
Column_name: emp_no
........
*************************** 2. row ***************************
Table: dept_emp
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: dept_no
........
*************************** 3. row ***************************
Table: dept_emp
Non_unique: 1
Key_name: emp_no
Seq_in_index: 1
Column_name: emp_no
........
*************************** 4. row ***************************
Table: dept_emp
Non_unique: 1
Key_name: dept_no
Seq_in_index: 1
Column_name: dept_no
........

5. More SQL
5.1 Sub-Query
Results of one query can be used in another SQL statement. Subquery is useful if more than one
tables are involved.

SELECT with Subquery

In the previous many-to-many product sales example, how to find the suppliers that do not supply
any product? You can query for the suppliers that supply at least one product in
theproducts_suppliers table, and then query the suppliers table for those that are not in the
previous result set.
mysql> SELECT suppliers.name from suppliers
WHERE suppliers.supplierID
NOT IN (SELECT DISTINCT supplierID from products_suppliers);

Can you do this without sub-query?

A subquery may return a scalar, a single column, a single row, or a table. You can use comparison
operator (e.g., '=', '>') on scalar, IN or NOT IN for single row or column, EXISTS or NOT EXIST to
test for empty set.

INSERT|UPDATE|DELETE with Subquery

You can also use a subquery with other SQL statements such as INSERT, DELETE, or UPDATE. For
example,
-- Supplier 'QQ Corp' now supplies 'Pencil 6B'
-- You need to put the SELECT subqueies in parentheses
mysql> INSERT INTO products_suppliers VALUES (
(SELECT productID FROM products WHERE name = 'Pencil 6B'),
(SELECT supplierID FROM suppliers WHERE name = 'QQ Corp'));
-- Supplier 'QQ Copr' no longer supplies any item
mysql> DELETE FROM products_suppliers
WHERE supplierID = (SELECT supplierID FROM suppliers WHERE name = 'QQ Corp');

5.2 Working with Date and Time

Date and time are of particular interest for database applications. This is because business records
often carry date/time information (e.g., orderDate, deliveryDate, paymentDate,dateOfBirth), as
well as the need to time-stamp the creation and last-update of the records for auditing and security.
With date/time data types, you can sort the results by date, search for a particular date or a range of
dates, calculate the difference between dates, compute a new date by adding/subtracting an interval
from a given date.

Date By Example
Let's begin with Date (without Time) with the following example. Take note that date value must be
written as a string in the format of 'yyyy-mm-dd', e.g., '2012-01-31'.
-- Create a table 'patients' of a clinic
mysql> CREATE TABLE patients (
patientID
INT UNSIGNED NOT NULL AUTO_INCREMENT,
name
VARCHAR(30)
NOT NULL DEFAULT '',
dateOfBirth
DATE
NOT NULL,
lastVisitDate DATE
NOT NULL,
nextVisitDate DATE
NULL,
-- The 'Date' type contains a date value in 'yyyy-mm-dd'
PRIMARY KEY (patientID)
);
mysql> INSERT INTO patients VALUES
(1001, 'Ah Teck', '1991-12-31', '2012-01-20', NULL),
(NULL, 'Kumar', '2011-10-29', '2012-09-20', NULL),
(NULL, 'Ali', '2011-01-30', CURDATE(), NULL);
-- Date must be written as 'yyyy-mm-dd'
-- Function CURDATE() returns today's date
mysql> SELECT * FROM patients;
+-----------+---------+-------------+---------------+---------------+

| patientID | name
| dateOfBirth | lastVisitDate | nextVisitDate |
+-----------+---------+-------------+---------------+---------------+
|
1001 | Ah Teck | 1991-12-31 | 2012-01-20
| NULL
|
|
1002 | Kumar
| 2011-10-29 | 2012-09-20
| NULL
|
|
1003 | Ali
| 2011-01-30 | 2012-10-21
| NULL
|
+-----------+---------+-------------+---------------+---------------+
-- Select patients who last visited on a particular range of date
mysql> SELECT * FROM patients
WHERE lastVisitDate BETWEEN '2012-09-15' AND CURDATE()
ORDER BY lastVisitDate;
+-----------+-------+-------------+---------------+---------------+
| patientID | name | dateOfBirth | lastVisitDate | nextVisitDate |
+-----------+-------+-------------+---------------+---------------+
|
1002 | Kumar | 2011-10-29 | 2012-09-20
| NULL
|
|
1003 | Ali
| 2011-01-30 | 2012-10-21
| NULL
|
+-----------+-------+-------------+---------------+---------------+
-- Select patients who were born in a particular year and sort by birth-month
-- Function YEAR(date), MONTH(date), DAY(date) returns
-the year, month, day part of the given date
mysql> SELECT * FROM patients
WHERE YEAR(dateOfBirth) = 2011
ORDER BY MONTH(dateOfBirth), DAY(dateOfBirth);
+-----------+-------+-------------+---------------+---------------+
| patientID | name | dateOfBirth | lastVisitDate | nextVisitDate |
+-----------+-------+-------------+---------------+---------------+
|
1003 | Ali
| 2011-01-30 | 2012-10-21
| NULL
|
|
1002 | Kumar | 2011-10-29 | 2012-09-20
| NULL
|
+-----------+-------+-------------+---------------+---------------+
-- Select patients whose birthday is today
mysql> SELECT * FROM patients
WHERE MONTH(dateOfBirth) = MONTH(CURDATE())
AND DAY(dateOfBirth) = DAY(CURDATE());
-- List the age of patients
-- Function TIMESTAMPDIFF(unit, start, end) returns the difference in the unit
specified
mysql> SELECT name, dateOfBirth, TIMESTAMPDIFF(YEAR, dateOfBirth, CURDATE()) AS age
FROM patients
ORDER BY age, dateOfBirth;
+---------+-------------+------+
| name
| dateOfBirth | age |
+---------+-------------+------+
| Kumar
| 2011-10-29 |
0 |
| Ali
| 2011-01-30 |
1 |
| Ah Teck | 1991-12-31 |
20 |
+---------+-------------+------+

-- List patients whose last visited more than 60 days ago

mysql> SELECT name, lastVisitDate FROM patients
WHERE TIMESTAMPDIFF(DAY, lastVisitDate, CURDATE()) > 60;
-- Functions TO_DAYS(date) converts the date to days
mysql> SELECT name, lastVisitDate FROM patients
WHERE TO_DAYS(CURDATE()) - TO_DAYS(lastVisitDate) > 60;
-- Select patients 18 years old or younger
-- Function DATE_SUB(date, INTERVAL x unit) returns the date
-by subtracting the given date by x unit.
mysql> SELECT * FROM patients
WHERE dateOfBirth > DATE_SUB(CURDATE(), INTERVAL 18 YEAR);
-- Schedule Ali's next visit to be 6 months from now
-- Function DATE_ADD(date, INTERVAL x unit) returns the date
-by adding the given date by x unit
mysql> UPDATE patients
SET nextVisitDate = DATE_ADD(CURDATE(), INTERVAL 6 MONTH)
WHERE name = 'Ali';

Date/Time Functions
MySQL provides these built-in functions for getting the current date, time and datetime:

NOW(): returns the current date and time in the format of 'YYYY-MM-DD HH:MM:SS'.

CURDATE() (or CURRENT_DATE(), or CURRENT_DATE): returns the current date in the format

of 'YYYY-MM-DD'.
CURTIME() (or CURRENT_TIME(), or CURRENT_TIME): returns the current time in the format

of 'HH:MM:SS'.
For examples,
mysql> select now(), curdate(), curtime();
+---------------------+------------+-----------+
| now()
| curdate() | curtime() |
+---------------------+------------+-----------+
| 2012-10-19 19:53:20 | 2012-10-19 | 19:53:20 |
+---------------------+------------+-----------+

SQL Date/Time Types

MySQL provides these date/time data types:

DATETIME: stores both date and time in the format of 'YYYY-MM-DD HH:MM:SS'. The valid range

is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. You can set a value using the valid
format (e.g., '2011-08-15 00:00:00'). You could also apply functions NOW() or CURDATE() (time

will be set to '00:00:00'), but not CURTIME().

DATE: stores date only in the format of 'YYYY-MM-DD'. The range is '1000-01-01' to '9999-1231'. You could apply CURDATE() or NOW() (the time discarded) on this field.

TIME: stores time only in the format of 'HH:MM:SS'. You could apply CURTIME() or NOW() (the

date discarded) for this field.

YEAR(4|2): in 'YYYY' or 'YY'. The range of years is 1901 to 2155. Use DATE type for year

outside this range. You could apply CURDATE() to this field (month and day discarded).
TIMESTAMP: similar to DATETIME but stored the number of seconds since January 1, 1970 UTC
(Unix-style).
The
range
is '1970-01-01
00:00:00' to '2037-12-31
23:59:59'.
The differences between DATETIME and TIMESTAMP are:
1. the range,
2. support for time zone,
3. TIMESTAMP column could be declared with DEFAULT CURRENT_TIMESTAMP to set the
default value to the current date/time. (All other data types' default, including DATETIME,
must be a constant and not a function return value). You can also declare
a TIMESTAMP column with "ON UPDATE CURRENT_TIMESTAMP" to capture the timestamp of
the last update.

The

date/time

value

can

be

entered

manually

as

string

literal

(e.g., '2010-12-31

23:59:59' for DATAETIME). MySQL will issue a warning and insert all zeros (e.g., '0000-00-00
00:00:00'for DATAETIME), if the value of date/time to be inserted is invalid or out-of-range. '000000-00' is called a "dummy" date.

More Date/Time Functions

Reference: MySQL's "Date and Time Functions" @ http://dev.mysql.com/doc/refman/5.5/en/date-andtime-functions.html.
There are many date/time functions:

Extracting part of a date/time: YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND(), e.g.,

mysql> SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()), MINUTE(NOW()),

SECOND(NOW());

+-------------+--------------+------------+-------------+---------------+--------------+

| YEAR(NOW()) |
SECOND(NOW()) |

+-------------+--------------+------------+-------------+---------------+--------------+

|
|

2012 |

MONTH(NOW())

10 |

DAY(NOW())

24 |

HOUR(NOW())

11 |

MINUTE(NOW())

54 |

45

+-------------+--------------+------------+-------------+---------------+--------------+

Extracting
infomation: DAYNAME() (e.g., 'Monday'), MONTHNAME() (e.g., 'March'), DAYOFWEEK() (1=Sunday,
, 7=Saturday), DAYOFYEAR() (1-366), ...

mysql>
SELECT
DAYNAME(NOW()),
MONTHNAME(NOW()),
DAYOFWEEK(NOW()),
DAYOFYEAR(NOW());
+----------------+------------------+------------------+------------------+
| DAYNAME(NOW()) | MONTHNAME(NOW()) | DAYOFWEEK(NOW()) | DAYOFYEAR(NOW()) |
+----------------+------------------+------------------+------------------+
| Wednesday
| October
|
4 |
298 |
+----------------+------------------+------------------+------------------+

Computing

another

date/time: DATE_SUB(date,

INTERVAL expr unit), DATE_ADD(date,

INTERVAL expr unit), TIMESTAMPADD(unit, interval, timestamp), e.g.,

mysql> SELECT DATE_ADD('2012-01-31', INTERVAL 5 DAY);

2012-02-05
mysql> SELECT DATE_SUB('2012-01-31', INTERVAL 2 MONTH);
2011-11-30

Computing
interval: DATEDIFF(end_date, start_date), TIMEDIFF(end_time, start_time), TIMESTAMPDIF
F(unit, start_timestamp, end_timestamp), e.g.,

mysql> SELECT DATEDIFF('2012-02-01', '2012-01-28');

4
mysql> SELECT TIMESTAMPDIFF(DAY, '2012-02-01', '2012-01-28');
-4

Representation: TO_DAYS(date) (days since year 0), FROM_DAYS(day_number), e.g.,

mysql> SELECT TO_DAYS('2012-01-31');

734898
mysql> SELECT FROM_DAYS(734899);
2012-02-01

Formatting: DATE_FORMAT(date, formatSpecifier), e.g.,

mysql> SELECT DATE_FORMAT('2012-01-01', '%W %D %M %Y');

Sunday 1st January 2012
-- %W: Weekday name
-- %D: Day with suffix
-- %M: Month name
-- %Y: 4-digit year
-- The format specifiers are case-sensitive
mysql> SELECT DATE_FORMAT('2011-12-31 23:59:30', '%W %D %M %Y %r');

Saturday 31st December 2011 11:59:30 PM

-- %r: Time in 12-hour format with suffix AM/PM

Example
1. Create a table with various date/time columns. Only the TIMESTAMP column can have
the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP .
2. mysql> CREATE TABLE IF NOT EXISTS `datetime_arena` (
3.
`description` VARCHAR(50) DEFAULT NULL,
4.
`cDateTime`
DATETIME
DEFAULT '0000-00-00 00:00:00',
5.
`cDate`
DATE
DEFAULT '0000-00-00',
6.
`cTime`
TIME
DEFAULT '00:00:00',
7.
`cYear`
YEAR
DEFAULT '0000',
8.
`cYear2`
YEAR(2)
DEFAULT '00',
9.
`cTimeStamp`
TIMESTAMP
DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
10.
);
11.
12. mysql> DESCRIBE `datetime_arena`;
13. +-------------+-------------+------+-----+---------------------+----------------------------+
14. | Field
| Type
| Null | Key | Default
| Extra
|
15. +-------------+-------------+------+-----+---------------------+----------------------------+
16. | description | varchar(50) | YES
|
| NULL
|
|
17. | cDateTime
| datetime
| YES
|
| 0000-00-00 00:00:00 |
|
18. | cDate
| date
| YES
|
| 0000-00-00
|
|
19. | cTime
| time
| YES
|
| 00:00:00
|
|
20. | cYear
| year(4)
| YES
|
| 0000
|
|
21. | cYear2
| year(2)
| YES
|
| 00
|
|
22. | cTimeStamp | timestamp
| NO
|
| CURRENT_TIMESTAMP
| on update
CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+---------------------+----------------------------+

23. Insert values manually using string literals.

24. mysql> INSERT INTO `datetime_arena`
25.
(`description`, `cDateTime`, `cDate`, `cTime`, `cYear`, `cYear2`)
26.
VALUES
27.
('Manual Entry', '2001-01-01 23:59:59', '2002-02-02', '12:30:30',
'2004', '05');

28.
29. mysql> SELECT * FROM `datetime_arena` WHERE description='Manual Entry';
30. +--------------+---------------------+------------+----------+-------+-------+---------------------+
31. | description | cDateTime
| cDate
| cTime
| cYear | cYear2 |
cTimeStamp
|
32. +--------------+---------------------+------------+----------+-------+-------+---------------------+
33. | Manual Entry | 2001-01-01 23:59:59 | 2002-02-02 | 12:30:30 | 2004 |
05 |
2010-04-08 14:44:37 |
+--------------+---------------------+------------+----------+-------+-------+---------------------+

34. Checking the on-update for TIMSTAMP.

35. mysql> UPDATE `datetime_arena` SET `cYear2`='99' WHERE description='Manual
Entry';
36.
37. mysql> SELECT * FROM `datetime_arena` WHERE description='Manual Entry';
38. +--------------+---------------------+------------+----------+-------+-------+---------------------+
39. | description | cDateTime
| cDate
| cTime
| cYear | cYear2 |
cTimeStamp
|
40. +--------------+---------------------+------------+----------+-------+-------+---------------------+
41. | Manual Entry | 2001-01-01 23:59:59 | 2002-02-02 | 12:30:30 | 2004 |
99 |
2010-04-08 14:44:48 |
+--------------+---------------------+------------+----------+-------+-------+---------------------+

42. Insert values using MySQL built-in functions now(), curdate(), curtime().
43. mysql> INSERT INTO `datetime_arena`
44.
(`description`, `cDateTime`, `cDate`, `cTime`, `cYear`, `cYear2`)
45.
VALUES
46.
('Built-in Functions', now(), curdate(), curtime(), now(), now());
47.
48. mysql> SELECT * FROM `datetime_arena` WHERE description='Built-in Functions';
49. +--------------------+---------------------+------------+----------+-------+-------+---------------------+
50. | description
| cDateTime
| cDate
| cTime
| cYear |
cYear2 | cTimeStamp
|
51. +--------------------+---------------------+------------+----------+-------+-------+---------------------+
52. | Built-in Functions | 2010-04-08 14:45:48 | 2010-04-08 | 14:45:48 | 2010 |
10 | 2010-04-08 14:45:48 |
+--------------------+---------------------+------------+----------+-------+-------+---------------------+

53. Insert invalid or out-of-range values. MySQL replaces with all zeros.
54. mysql> INSERT INTO `datetime_arena`
55.
(`description`, `cDateTime`, `cDate`, `cTime`, `cYear`, `cYear2`)
56.
VALUES
57.
('Error Input', '2001-13-31 23:59:59', '2002-13-31', '12:61:61',
'99999', '999');
58.
59. mysql> SELECT * FROM `datetime_arena` WHERE description='Error Input';
60. +-------------+---------------------+------------+----------+-------+--------+--------------------+
61. | description | cDateTime
| cDate
| cTime
| cYear | cYear2 |
cTimeStamp
|
62. +-------------+---------------------+------------+----------+-------+--------+--------------------+
63. | Error Input | 0000-00-00 00:00:00 | 0000-00-00 | 00:00:00 | 0000 |
00 |
2010-04-08 14:46:10 |
+-------------+---------------------+------------+----------+-------+--------+--------------------+

64. An useful built-in function INTERVAL can be used to compute a future date, e.g.,
65. mysql> SELECT `cDate`, `cDate` + INTERVAL 30 DAY, `cDate` + INTERVAL 1 MONTH
FROM `datetime_arena`;
66. +------------+---------------------------+----------------------------+
67. | cDate
| `cDate` + INTERVAL 30 DAY | `cDate` + INTERVAL 1 MONTH |
68. +------------+---------------------------+----------------------------+
69. | 2002-02-02 | 2002-03-04
| 2002-03-02
|
70. | 2010-04-08 | 2010-05-08
| 2010-05-08
|
71. | 0000-00-00 | NULL
| NULL
|
+------------+---------------------------+----------------------------+

5.3 View
A view is a virtual table that contains no physical data. It provide an alternative way to look at the
data.

Example
-- Define a VIEW called supplier_view from products, suppliers and products_suppliers
tables
mysql> CREATE VIEW supplier_view
AS
SELECT suppliers.name as `Supplier Name`, products.name as `Product Name`
FROM products
JOIN suppliers ON products.productID = products_suppliers.productID
JOIN
products_suppliers
ON
suppliers.supplierID
=
products_suppliers.supplierID;
-- You can treat the VIEW defined like a normal table

mysql> SELECT * FROM supplier_view;

+---------------+--------------+
| Supplier Name | Product Name |
+---------------+--------------+
| ABC Traders
| Pencil 3B
|
| ABC Traders
| Pencil 4B
|
| ABC Traders
| Pencil 5B
|
| XYZ Company
| Pencil 6B
|
+---------------+--------------+
mysql> SELECT * FROM supplier_view WHERE `Supplier Name` LIKE 'ABC%';
+---------------+--------------+
| Supplier Name | Product Name |
+---------------+--------------+
| ABC Traders
| Pencil 3B
|
| ABC Traders
| Pencil 4B
|
| ABC Traders
| Pencil 5B
|
+---------------+--------------+

Example
mysql> DROP VIEW IF EXISTS patient_view;
mysql> CREATE VIEW patient_view
AS
SELECT
patientID AS ID,
name AS Name,
dateOfBirth AS DOB,
TIMESTAMPDIFF(YEAR, dateOfBirth, NOW()) AS Age
FROM patients
ORDER BY Age, DOB;
mysql> SELECT * FROM patient_view WHERE Name LIKE 'A%';
+------+---------+------------+------+
| ID
| Name
| DOB
| Age |
+------+---------+------------+------+
| 1003 | Ali
| 2011-01-30 |
1 |
| 1001 | Ah Teck | 1991-12-31 |
20 |
+------+---------+------------+------+
mysql> SELECT * FROM patient_view WHERE age >= 18;
+------+---------+------------+------+
| ID
| Name
| DOB
| Age |
+------+---------+------------+------+
| 1001 | Ah Teck | 1991-12-31 |
20 |
+------+---------+------------+------+

5.4 Transactions
A atomic transaction is a set of SQL statements that either ALL succeed or ALL fail. Transaction is
important to ensure that there is no partial update to the database, given an atomic of SQL
statements. Transactions are carried out via COMMIT and ROLLBACK.

Example
mysql> CREATE TABLE accounts (
name

VARCHAR(30),

balance

DECIMAL(10,2)

);

mysql> INSERT INTO accounts VALUES ('Paul', 1000), ('Peter', 2000);

mysql> SELECT * FROM accounts;
+-------+---------+
| name

| balance |

+-------+---------+
| Paul

| 1000.00 |

| Peter | 2000.00 |
+-------+---------+

-- Transfer money from one account to another account

mysql> START TRANSACTION;
mysql> UPDATE accounts SET balance = balance - 100 WHERE name = 'Paul';
mysql> UPDATE accounts SET balance = balance + 100 WHERE name = 'Peter';
mysql> COMMIT;
-- Commit the transaction and end transaction
mysql> SELECT * FROM accounts;
+-------+---------+
| name | balance |
+-------+---------+
| Paul | 900.00 |
| Peter | 2100.00 |
+-------+---------+
mysql> START TRANSACTION;
mysql> UPDATE accounts SET balance = balance - 100 WHERE name = 'Paul';
mysql> UPDATE accounts SET balance = balance + 100 WHERE name = 'Peter';
mysql> ROLLBACK;
-- Discard all changes of this transaction and end Transaction
mysql> SELECT * FROM accounts;
+-------+---------+
| name | balance |
+-------+---------+
| Paul | 900.00 |

| Peter | 2100.00 |
+-------+---------+

If you start another mysql client and do a SELECT during the transaction (before the commit or
rollback), you will not see the changes.
Alternatively, you can also disable the so-called autocommit mode, which is set by default and
commit every single SQL statement.
-- Disable autocommit by setting it to false (0)
mysql> SET autocommit = 0;
mysql> UPDATE accounts SET balance = balance - 100 WHERE name = 'Paul';
mysql> UPDATE accounts SET balance = balance + 100 WHERE name = 'Peter';
mysql> COMMIT;
mysql> SELECT * FROM accounts;
+-------+---------+
| name | balance |
+-------+---------+
| Paul | 800.00 |
| Peter | 2200.00 |
+-------+---------+
mysql> UPDATE accounts SET balance = balance - 100 WHERE name = 'Paul';
mysql> UPDATE accounts SET balance = balance + 100 WHERE name = 'Peter';
mysql> ROLLBACK;
mysql> SELECT * FROM accounts;
+-------+---------+
| name | balance |
+-------+---------+
| Paul | 800.00 |
| Peter | 2200.00 |
+-------+---------+
mysql> SET autocommit = 1;

-- Enable autocommit

A transaction groups a set of operations into a unit that meets the ACID test:
1. Atomicity: If all the operations succeed, changes are committed to the database. If any of the
operations fails, the entire transaction is rolled back, and no change is made to the database.
In other words, there is no partial update.
2. Consistency: A transaction transform the database from one consistent state to another
consistent state.
3. Isolation: Changes to a transaction are not visible to another transaction until they are
committed.
4. Durability: Committed changes are durable and never lost.

5.5 User Variables

In MySQL, you can define user variables via:
1. @varname :=value in a SELECT command, or
2. SET @varname := value or SET @varname = value command.
For examples,
mysql> SELECT @ali_dob := dateOfBirth FROM patients WHERE name = 'Ali';
mysql> SELECT name WHERE dateOfBirth < @ali_dob;
mysql> SET @today := CURDATE();
mysql> SELECT name FROM patients WHERE nextVisitDate = @today;

6. More on JOIN
6.1 INNER JOIN
In an inner join of two tables, each row of the first table is combined (joined) with every row of
second table. Suppose that there are n1 rows in the first table and n2 rows in the second table,INNER
JOIN produces all combinations of n1n2 rows - it is known as Cartesian Product or Cross Product.

Example
mysql> DROP TABLE IF EXISTS t1, t2;
mysql> CREATE TABLE t1 (
id
INT PRIMARY KEY,
`desc` VARCHAR(30)
);
-- `desc` is a reserved word - must be back-quoted
mysql> CREATE TABLE t2 (
id
INT PRIMARY KEY,
`desc` VARCHAR(30)
);
mysql> INSERT INTO
(1, 'ID 1
(2, 'ID 2
(3, 'ID 3

t1
in
in
in

VALUES
t1'),
t1'),
t1');

mysql> INSERT INTO

(2, 'ID 2
(3, 'ID 3
(4, 'ID 4

t2
in
in
in

VALUES
t2'),
t2'),
t2');

mysql> SELECT * FROM t1;

+----+------------+
| id | desc
|

+----+------------+
| 1 | ID 1 in t1 |
| 2 | ID 2 in t1 |
| 3 | ID 3 in t1 |
+----+------------+
mysql> SELECT * FROM t2;
+----+------------+
| id | desc
|
+----+------------+
| 2 | ID 2 in t2 |
| 3 | ID 3 in t2 |
| 4 | ID 4 in t2 |
+----+------------+
mysql> SELECT *
FROM t1 INNER JOIN t2;
+----+------------+----+------------+
| id | desc
| id | desc
|
+----+------------+----+------------+
| 1 | ID 1 in t1 | 2 | ID 2 in t2 |
| 2 | ID 2 in t1 | 2 | ID 2 in t2 |
| 3 | ID 3 in t1 | 2 | ID 2 in t2 |
| 1 | ID 1 in t1 | 3 | ID 3 in t2 |
| 2 | ID 2 in t1 | 3 | ID 3 in t2 |
| 3 | ID 3 in t1 | 3 | ID 3 in t2 |
| 1 | ID 1 in t1 | 4 | ID 4 in t2 |
| 2 | ID 2 in t1 | 4 | ID 4 in t2 |
| 3 | ID 3 in t1 | 4 | ID 4 in t2 |
+----+------------+----+------------+
-- SELECT all columns in t1 and t2 (*)
-- INNER JOIN produces ALL combinations of rows in t1 and t2

You can impose constrain by using the ON clause, for example,

mysql> SELECT *
FROM t1 INNER JOIN t2 ON t1.id = t2.id;
+----+------------+----+------------+
| id | desc
| id | desc
|
+----+------------+----+------------+
| 2 | ID 2 in t1 | 2 | ID 2 in t2 |
| 3 | ID 3 in t1 | 3 | ID 3 in t2 |
+----+------------+----+------------+

Take note that the following are equivalent:

mysql> SELECT *
FROM t1 INNER JOIN t2 ON t1.id = t2.id;
mysql> SELECT *
FROM t1 JOIN t2 ON t1.id = t2.id;

-- default JOIN is INNER JOIN

mysql> SELECT *
FROM t1 CROSS JOIN t2 ON t1.id = t2.id;

-- Also called CROSS JOIN

-- You can use USING clause if the join-columns have the same name
mysql> SELECT *
FROM t1 INNER JOIN t2 USING (id);
+----+------------+------------+
| id | desc
| desc
|
+----+------------+------------+
| 2 | ID 2 in t1 | ID 2 in t2 |
| 3 | ID 3 in t1 | ID 3 in t2 |
+----+------------+------------+
-- Only 3 columns in the result set, instead of 4 columns with ON clause
mysql> SELECT *
FROM t1 INNER JOIN t2 WHERE t1.id = t2.id;
mysql> SELECT *
FROM t1, t2 WHERE t1.id = t2.id;

-- Use WHERE instead of ON

-- Use "commas" operator to join

6.2 OUTER JOIN - LEFT JOIN and RIGHT JOIN

INNER JOIN with constrain (ON or USING) produces rows that are found in both tables. On the other

hand, OUTER JOIN can produce rows that are in one table, but not in another table. There are two
kinds of OUTER JOINs: LEFT JOIN produces rows that are in the left table, but may not in the right
table; whereas RIGHT JOIN produces rows that are in the right table but may not in the left table.
In a LEFT JOIN, when a row in the left table does not match with the right table, it is still selected but
by combining with a "fake" record of all NULLs for the right table.
mysql> SELECT *
FROM t1 LEFT JOIN t2 ON t1.id = t2.id;
+----+------------+------+------------+
| id | desc
| id
| desc
|
+----+------------+------+------------+
| 1 | ID 1 in t1 | NULL | NULL
|
| 2 | ID 2 in t1 |
2 | ID 2 in t2 |
| 3 | ID 3 in t1 |
3 | ID 3 in t2 |
+----+------------+------+------------+
mysql> SELECT *
FROM t1 LEFT JOIN t2 USING (id);
+----+------------+------------+
| id | desc
| desc
|
+----+------------+------------+
| 1 | ID 1 in t1 | NULL
|
| 2 | ID 2 in t1 | ID 2 in t2 |
| 3 | ID 3 in t1 | ID 3 in t2 |
+----+------------+------------+

mysql> SELECT *
FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;
+------+------------+----+------------+
| id
| desc
| id | desc
|
+------+------------+----+------------+
|
2 | ID 2 in t1 | 2 | ID 2 in t2 |
|
3 | ID 3 in t1 | 3 | ID 3 in t2 |
| NULL | NULL
| 4 | ID 4 in t2 |
+------+------------+----+------------+
mysql> SELECT *
FROM t1 RIGHT JOIN t2 USING (id);
+----+------------+------------+
| id | desc
| desc
|
+----+------------+------------+
| 2 | ID 2 in t2 | ID 2 in t1 |
| 3 | ID 3 in t2 | ID 3 in t1 |
| 4 | ID 4 in t2 | NULL
|
+----+------------+------------+

As the result, LEFT JOIN ensures that the result set contains every row on the left table. This is
important, as in some queries, you are interested to have result on every row on the left table, with
no match in the right table, e.g., searching for items without supplier. For example,
mysql> SELECT t1.id, t1.desc
FROM t1 LEFT JOIN t2 USING (id)
WHERE t2.id IS NULL;
+----+------------+
| id | desc
|
+----+------------+
| 1 | ID 1 in t1 |
+----+------------+

Take note that the followings are equivalent:

mysql> SELECT *
FROM t1 LEFT JOIN t2 ON t1.id = t2.id;
mysql> SELECT *
FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id;
mysql> SELECT *
FROM t1 LEFT JOIN t2 USING (id);
+----+------------+------------+
| id | desc
| desc
|
+----+------------+------------+
| 1 | ID 1 in t1 | NULL
|
| 2 | ID 2 in t1 | ID 2 in t2 |
| 3 | ID 3 in t1 | ID 3 in t2 |
+----+------------+------------+

-- join-columns have same name

-- WHERE clause CANNOT be used on OUTER JOIN

mysql> SELECT *
FROM t1 LEFT JOIN t2 WHERE t1.id = t2.id;
ERROR 1064 (42000): You have an error in your SQL syntax;