Monday, January 26, 2015

Classes starting soon, register asap

This class is going to start as advertised and you are encourage to sign up as the seats for this hands-on course are limited. Hurry up!
Summary:

Databases, organized repositories of information, have become indispensable in today's world. In this introductory course you will learn about databases and the basics of Structured Query Language (SQL) including sorting, distinct, and aggregate functions and grouping of data. All SQL statements will initially be written from one table. Most practical, modern and relational databases will include a large number of tables. SQL queries have to access information from several tables. This course will then introduce you to querying more than one table. With this skill you will be able to query two or more tables in a database. This is a hands-on course.


New in 2015: You will also get an introduction to Power Shell.

Dates: (Section 1) Section 1 - Feb 17 - Mar 12, 2015; TTh; 5:30 pm - 7:30 pm; 10 mtgs.
Venue: Honolulu Community College;

Note: SQL Server 2012 Express will be used and a knowledge of SQL Server is not assumed. Students will be using the free version.

For details : Access http://pcatt.org/index.php/search and look up the course or write to:
Hodentek@live.com with course name in the Subject line.

Creating a scalar function in SQL Server 2012

General:
Consider the following equation:

y = x + x*x + x*x*x
where * is a symbol for multiplication

Here y is a function of x (the value of y depends on the value assigned to x) written symbolically as y=f(x)

A Scalar function in general can depend on more than one variable such as y=f(x,z,t) and return a single value.

How do you create a function in SSMS?

The following function named TestMultiply takes two parameters @param1 and @param2 and returns their product, a single value.

Create function [dbo].[TestMultiply]
( @param1 int,
  @param2 int
)
Returns INT
AS

BEGIN

RETURN @param1 * @param2
END
GO


This is implemented in SQL Server (in SQL Server Management Studio) by opening a query window and typing in the above code and executing the query as shown:


How do we evaluate the function?

First of all we should call the function in a query window by selecting it and providing the two parameters.

Here is how it is evaluated for @param1=5 and @param2=10



How can you create a function that uses values in the database tables?

Let us go to the AdventureWorks2012 database and review the Sales.SalesOrderDetail table. The following shows the structure of the table:



In this table we focus on two columns, UnitPrice and OrderQty. We plan to write a function that multiplies OrderQty with UnitPrice and return a value in the function we call TotalOrder. The function we create is as follows:

Create Function [dbo].[TotalOrder]
(
 @OrderQty int,
 @UnitPrice money
)
Returns MONEY

as

BEGIN
Return @OrderQty * @UnitPrice
END
GO


When you execute the query with the above a scalar function called TotalOrder will be created as shown:



In order to use this function you can call it as shown(shown with some results):



There is a lot more to understand. This just Basic stuff.

Sunday, January 25, 2015

Learn Querying SQL Server 2012 using LinqPad - Part 4 Stored Procedures_2

In Learn Querying SQL Server 2012 using LinqPad - Part 3 we considered a stored procedure, albeit simple which did not have any output parameters.  LinqPad can also be used for evaluating stored procedures with an out put parameter. Make sure you review Parts 1, 2, and 3.

Let us modify the stored procedure in Part 3 so that the FullName of a Person is returned from the stored procedure as shown here.

Create Procedure PPFull
@fname nvarchar(10),
@lname nvarchar(20),
@fullname nvarchar(25) OUTPUT

as

Select @fullname=FirstName+','+Lastname from Person.person
Where FirstName=@fname and LastName=@lname
go


Note that @fullname varible is declared as an OUTPUT Parameter.

This can be executed in SQL Server Management Studio as shown.


The same can be executed in LinqPad as shown here.



Saturday, January 24, 2015

Learn Querying SQL Server 2012 using LinqPad - Part 3 Stored Procedures

You can execute stored procedures with LinqPad. In order to do so you need to know the details of the stored procedure which can reviewed in SQL Server Management Studio as shown here for a stored procedure in AdventureWorks2012. Review Learn Querying SQL Server 2012 using LinqPad parts 1 and 2.

Create a simple test stored procedure as shown:

Create Procedure dbo_PP
@fname as nvarchar(10),
@lname as nvarchar(20)

as
Select * from Person.person
Where
FirstName=@fname and LastName=@lname

go


This stored procedure requires two arguments, @fname and @lname for its evaluation.

Let us examine and execute this procedure first in SSMS and then in LinqPad.
In SSMS:
You find this stored procedure in the Programmability folder as shown.
This can be executed in SSMS as shown. Two paramters are provided


Once this procedure is created you can access it in LinqPad (review posts Part1 and Part2 )


It can be executed two ways in LinqPad; as a C# expression or SQL 

Evaluated as SQL:


Now evaluted as C# Expression:


Now as SQL:


This is how you get the return value:








Tuesday, December 30, 2014

It was a mixed year, 2014

2014 was a mixed year. I lost my brother Dr. Vijaya Raghavan Krishnaswamy, a neuro-surgeon by profession. He left a void that cannot be filled.

I did not complete the book I was writing.

Regarding the blogs I did quite well with a record number of posts.
                                                 Year   Posts
Hodentek.blogspot.com           2013   94
                                                 2014   246

HodentekHelp.blogspot.com.  2013.  17
                                                 2014   56

HodentekMsss.blogspot.com   2013   34
                                                  2014  48
 
HoentekMobile.blogspot.com  2013   13
                                                  2014.  56

I started a new blog in 2014- http://hodentekPlus.blogspot.com.

I also found that a certain person was pilfering (http://hodentek.blogspot.in/2014/12/domain-name-service-dns-and-dns-changer.html) entire content from blog, my hardwork of about 10 years. I complained to Google without any success. I suppose this is not on Google's priority. Google wants every post that is stolen with proof of content stolen. There were over 900 posts in my blog and you can imagine the work involved.
 

Monday, December 15, 2014

Honolulu learning event: Basic SQL

You are welcome to attend my course at the Pacific Center for Advanced Technology Training (PACTT). Please register at the PCATT.org site.

Summary:

Databases, organized repositories of information, have become indispensable in today's world. In this introductory course you will learn about databases and the basics of Structured Query Language (SQL) including sorting, distinct, and aggregate functions and grouping of data. All SQL statements will initially be written from one table. Most practical, modern and relational databases will include a large number of tables. SQL queries have to access information from several tables. This course will then introduce you to querying more than one table. With this skill you will be able to query two or more tables in a database. This is a hands-on course.


New in 2015: You will also get an introduction to Power Shell.

Dates: (Section 1) Section 1 - Feb 17 - Mar 12, 2015; TTh; 5:30 pm - 7:30 pm; 10 mtgs.
Venue: Honolulu Community College;

Note: SQL Server 2012 Express will be used and a knowledge of SQL Server is not assumed. Students will be using the free version.

For details : Access http://pcatt.org/index.php/search and look up the course or write to:
Hodentek@live.com with course name in the Subject line.

Friday, December 12, 2014

Learn Querying SQL Server 2012 using LinqPad - Part 2

In Part 1 of this series I showed how you can connect to your instance of
SQL Server on your computer. If you have not seen this please go here:
http://hodentekmsss.blogspot.com/2014/12/learn-querying-sql-server-using-linq.html

In this post you will learn how the tables in the SQL Server database
AdventureWorks2012 are represented on the LinqPad and learn to run a few
statements or expressions to fetch information on the SQL Server to display in LinqPad.

This next image shows how the tables and other objects in SQL Server are
displayed grouped in LINQPad.

All the tables in HumanResources are grouped and you can expand to see
the individual tables in LinqPad. Also all the Stored procedures, Functions
and Views related to the HumanResources are shown under HumanResources in
LinqPad but they are shown under the object nodes Programmability |
StoredProcedures; Programmability | Functions and non-system Views in SSMS.

Evaluating Expressions in LinqPad

When you launch LinqPad and connect to SQL Server Instance as shown in the
previous post, the LinqPad UI appears as shown (only top part shown here).


Here the Green arrow at left (right pane) is a button which executes the statement or
query you enter the pane and it will show Execute(F5) if you hover over the
green button.

The one next to it shown in grey is the Stop button and stops the execution.  The next two icons when clikced displays the results either in rich text (Ctrl+Shift+T),or data grid (Ctrl+shift+G).

The Language drop-down has a number of options and to get the correct result
you should indicate whether it is C# Expression(appears as default) or the
other types shown here.


The Connection drop-down comes up with default <None> which means the query
will not be execcuted since it is not connected despite the fact that you see the connection on the left. When you click on the handle (downward pointing arrow) you will see that you can connect to the database shown in the left.


Querying examples:

1. Click the drop-down for Connection and choose AdventureWorks2012
2. Write an expression to evaluate
1+2+3  as shown and click the green arrow to execute the expression and you
will see the result in the results pane that gets displayed.


This is not really SQL related but the engine evaluates it.

3. Let us say we want to get all the Employees information from the
Employees table in the Human Resources group. Then create a new query by
clicking on the + tab right next to Query1 and enter

Employees

as shown(this will be Query2). Don't forget to click the green arrow to execute. You get the following displayed.


As far the results you have other options to review as shown in the next composite image


4. Let us do some filtering. Let us we need only two columns from the
Customers table
Type in C#Expression the following"

from c in Customers
select new {c.CustomerID, c.StoreID}

Click the green arrow for query execution and you will see the following:



5. Let us say you want to filter further to display fewer rows then you can type in the following and click execute.
from c in Customers
where c.CustomerID <10
select new {c.CustomerID, c.StoreID}


Click Execute and the following will be displayed:

6. Let us say you want it sorted in the increasing order of StoreID then you can do the following:
Type in the following:

from c in Customers.OrderBy(c=> c.StoreID)
where c.CustomerID <10
select new {c.CustomerID, c.StoreID}


Click Execute and after some processing you will see the following:


You have learnt in this post how to recognize the UI and how to run a few simple queries against the SQL Server 2012 database.

In the next Part(s) you will learn a few more topics