Basic Walkthrough SQLServer Part-2

 

Basic Walkthrough SQLServer Part-2

This part will provide simple introduction to SQLSERVER. Some might have some experience with relational database systems, while others might not have even thought about it. In simplest form RDBS (relational data base systems) is a database package that manages relational data. Now relational data is all about properties of an object that needs to be related via any other properties. Suppose we talk about a person. A person has name, last name, phones and may have vehicles. The person may have an income. The person may have expenses. The person may have choices and options. So, all the properties are linked to each other via a person. This way or that way, whatever is linked via another data is usually called relational data. The database that provides the relational data interlink (interface) is usually called relational database.

            So SQLSERVER is a Relational Database. Like other databases it also provides few database objects that help us save, organize and retrieve data. Other databases provide some other features and may to some length, while SQLSERVER has a reputation of providing a reliable database system that can easily be handled. The best part is that now SQLSERVER Express-C server is free to use an is highly suitable for new learners and matured developers. Let us discuss about some of SQLSERVER objects.

Tables

            Tables are the first one that I can think of. And mostly used right from simplest database to most complicated system. I our walkthrough we will be dealing with one simple table that will be very simple to understand and you will never need to ask the question “what is a table??” Definition wise a table is a simple collection of data in rows and columns. The first table I ever came across was a dBase table that the dBase guys used to call ‘database’ instead of ‘table’.

Views

            Second object is usually views. The views are actually a logical combination of columns from different tables. These columns usually linked via a link id. Link id is normally a common column among related tables to indicate the object that these columns should relate. From user’s perspective the views are same as tables (not in dBase). You use same command to retrieve data from views as from tables. While we will be using simplest form of DB application; with one table only; we won’t be dealing with views a lot except simple usage example towards the end of series.

Triggers

            Triggers are another object that play a vital role in database life. As the name the triggers get triggered when an event is sensed. Database manager will execute the code in trigger body to perform some actions described in the trigger when an event described in trigger header occurs. Normally triggers are called when insert, update or delete is performed on a table. So the triggers are create on tables.

Procedures

            Procedures are another part that plays an important role in data manipulation.  These are similar to procedures, sub routines and functions. These procedures are written and when we create these; database compiles and stores on server machine. These are later called to do actions as preplanned in the Procedure Body.

Packages

            Packages are little bigger than procedures. These are almost same as procedures. These packages are also compiled and later bound to the database. Any errors are usually phased out in compile time.

Sequences

            Sequences are some numerical series created by database based on criteria provided by the creator. These helps numbering the data being entered into the database automatically.

            In fact for introductory walkthrough, you do not need to remember all these. More you learn, more will you discover the objects. All objects re for some purpose; and if you are so simple, you may not need to learn all. But they are there, just in case you need these any time.

Let us start exploring some of these. We will create few basic tables to study behavior of some of objects. Tables will be created quickly and we will study other objects briefly.

To start with let us take a look at ways we can issue commands to SQLServer. First method is to use sqlcmd command window. Go to start>All Programs>Accessories>Command Prompt. Run sqlcmd.exe in the command window. SQL commands being entered at command line are not executed until we enter GO keyword at a line. While the same command can be executed in SSMS Query window, GO keyword may not be required for a single command.

 

 

 

You can connect to SAMPLE database using USE SAMPLE.

 

 

You can keep entering T-SQL commands until you enter GO. Sqlcmd keeps counting line till then. This behavior allows multiline SQLs to be entered.

 

 

Now let’s have some hands on with BCP. The Bulk copy program aka bcp is the console application used to export and import data from text files to SQL Server or vice versa.

 

bcp "select * from dbo.person" queryout result.txt  -d SAMPLE -U sa -c

 

First parameter is the query that you want to fetch results from, second is indirection. Switch -d specifies database name, -U username and -c indicates character format for the output.

 

            BCP is very powerful tool used in SQLServer, to export and import data.

 

Type EXIT to close the command window. Start SQL Server Management Studio from Start > All Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio. You will see splash screen and then you will start up by connecting to SQL Server Instance. Expand Databases, right click SAMPLE and click on New Query.

 

           

            A Query window will open where you can enter commands. This is almost same as the one we had with sqlcmd.exe with the difference of GUI interface and other goodies like selection, editing, executing and formatting of the SQL statements. When you open a query window by selecting database name then this window gets connected to the database, otherwise you can use ‘USE’ statement to connect to the database.

 

 

 

The execution is started by pressing Execute button on the top, unless specific statement is selected, all statements separated by GO line are executed sequentially. The green play button is the DEBUG button in the query window. This feature is very useful for finding bugs in your SQL code.

 

            Now you can select database “SAMPLE”, right click it and select tasks. In the menu you can fine “Back Up” and “Restore” somewhere in the middle and “Import Data” and Export Data” towards the bottom.

 

 

 

 

These features will be discussed later, for now just note down how to brows to these tools. For backup and restore, we can use ‘sqlcmd.exe’ and for Import/Export ‘bcp.exe’ on command line interface.

 

Once you finish your work with the query window, you can close this window, at this time you will be reminded to save the contents if the window which you can ignore if you do not want to.

 

Well for now let us use SQL command window for creating tables view and triggers. So, open ‘cmd’ window to execute following commands one by one.

 

Use sample Go CREATE TABLE [dbo].[TRANS](       ID    INT IDENTITY(1,1) NOT NULL,     AMOUNT  DECIMAL(6,2) NOT NULL,     CODE    CHAR(6) ) GO   CREATE TABLE TOTAL (  WEEKNUM    SMALLINT  NOT NULL  DEFAULT 0,  INCOME     DECIMAL (6, 2)  NOT NULL  DEFAULT 0,  EXPENSE    DECIMAL (6, 2)  NOT NULL  DEFAULT 0,  TIMEUPDATE TIMESTAMP  NOT NULL  )   GO

 

 

After creating these two tables, you will create a trigger that will update your TOTAL table. See first line for syntax to create trigger when you name the code file as trig1.sql.

 

 

sqlcmd -i trig1.sql

create trigger update_total on trans for insert as   begin   declare @amt decimal(8,2); declare @cod char(3);   set @amt = (select amount from inserted); set @cod = (select code from inserted) if @amt <> 0   if exists (select 1 from total where weeknum=DATEPART(wk,getdate()))       begin       if lower(@cod)='in' update total set income= income + @amt, timeupdate=getdate() where weeknum=DATEPART(wk,getdate());       if  lower(@cod)='out' update total set expense= expense + @amt, timeupdate=getdate() where weeknum=DATEPART( wk,getdate());       end else       begin       if  lower(@cod)='in' insert into total values(DATEPART( wk,getdate()),@amt,0,getdate());     if  lower(@cod)='out' insert into total values(DATEPART( wk,getdate()),0,@amt,getdate());    end    end go

 

Only thing to note here is that the triggers can be created in Command Window not CLP. You can create it in Command Editor, but for that you will have to change terminator character to ‘@’ instead of ‘;’ (look bottom left of CE).

            After creating these three objects try entering data into TRANS table as below. You will see that TOTAL table will be populated automatically. This is feature of the database; not the program. The TOTAL table will create one row for each week. When week changes it will generate another row. This table will reflect weekly income and expenses. On regular basis you will enter all transactions into TRANS table as below. Remember to enter code as either ‘in’ for income or ‘out’ for expenses. Any other code will be accepted but will be useless.

 

insert into TRANS values (123.45,’in’) insert into TRANS values (34.40,’out’) insert into TRANS values (100,’in’) insert into TRANS values (15,’out’) insert into TRANS values (.75,’in’) go

           

And lastly check contents of TOTAL table.

 

1> select * from total 2> go WEEKNUM INCOME   EXPENSE  TIMEUPDATE ------- -------- -------- -----------------------      49   224.20    49.40 2018-12-04 22:25:21.000   (1 rows affected)

 

Cool, get the calculator and see if the calculation is correct. Last update stamp only shows the last time when the data was entered.

            In fact, we have created a very useful utility. This can further be customized as required. You will see how triggers can help us validate data while data entry in following sections. Till then happy SQLing.