Basic Walkthrough SQLServer Part-3

 

Basic Walkthrough SQLServer Part-3

This section will help you walk through development of simplest database program (utility). I will make it to happen so smoothly towards the end of this part you will not even believe that the utility is ready for use.

Of course, intention to keep it simple, will prevent us from having Graphical User Interface (GUI); still the main purpose of looking closely at SQLSERVER will be served and along with the knowledge (know how) there will be another part, which is called amusement while learning. If we have to enjoy the product (SQL Server) which is free, we must learn main features in smallest possible time; which is different person to person.

Once we succeed to setup simplest form of database, I promise I will introduce all to remote control unit with no additional expense (in fact REMOTE CONTROL  was developed to use existing components). This unit helps to retrieve data from your computer via email commands defined by you. This is advised that the data transferred via email is not encrypted; so please use only in utmost urgency. Still this unit will be nicest tool to learn and experiment with Database and System.

            So, let us start. As we tried few commands in previous part, I will prefer to take you along with me to a nice short development tour. Mostly I will use Windows Command Line, which means that for purpose of understanding commands, I will be using ‘cmd.exe’ DOS BOX. Don’t worry id you are not familiar with one or other term, because I will be showing snapshots to help you get used to.

First off all I would like to create a table with few columns. The definition is shown below. I will leave it up to you to see and guess which column is used for what data.

CREATE TABLE ADDRESS (                        FNAME CHAR(16) NOT NULL,                        MNAME CHAR(12),                        LNAME CHAR(16) NOT NULL,                        PHONE CHAR(15),                        CELL  CHAR(15),                        ADDR  CHAR(35)                       )

This will create the table in the current database that you are connected to, it should have been as below.

Well, nothing discouraging so far, connect to the database and re-issue the Create Table command.

After creating table, I would emphasize on creating a primary key on First Name (FNAME) column. This will help us keep consistent data in our database. The key will be created as combination of two columns, FNAME and LNAME. Which basically help us search through the data when there are really big number of rows and will help us keep distinct data.

ALTER TABLE ADDRESS ADD CONSTRAINT My_FNAME_LNAME_PK PRIMARY KEY( FNAME, LNAME)

So, if we create primary key as a combination of FNAME and LNAME, there can’t be two identical combinations. For example, you can add ‘Tai Tang’ first time, but in subsequent entries ‘Tai Tang’ will not be accepted.

After creating primary key, you will insert some data. You can do this manually because as a sample data of imaginary records, there will be only four rows for our hands on. So, when you will enter data at sqlcmd, following will be scene.

insert into address values ( 'Tai','','Tang','647-647-6477','416-614-6144','64 Pluto Way Brampton,ON,CA') insert into address values ( 'Sabar','','Raikoti','905-509-5099','419-914-9144','1032 Mavis Rd Mississauga,ON,CA') insert into address values ( 'David','M.','Brown','905-264-2644','647-746-7466','1216 Morning Star Drive Miss,ON,CA') insert into address values ( 'Dilshaad','Sufi','Akhtar','905-932-9322','416-417-4177','3456 Horner Ave Etobicoke,ON,CA')

If you want to check how primary key stops wrong data creep in try to enter same row as row #1 again.

 

1> insert into address values ('Tai','','Tang','647-647-6477','416-614-6144','64 Pluto Way Brampton,ON,CA') 2> go Msg 2627, Level 14, State 1, Server XPSQL1\SQLEXPRESS, Line 1 Violation of PRIMARY KEY constraint 'My_FNAME_LNAME_PK'. Cannot insert duplicate key in object 'dbo.ADDRESS'. The statement has been terminated. 1>

 

This will prevent duplicate data and if you really need two similar names exist in the table you need to set primary key more widely. Try eliminating ‘a’ from ‘Tang’.

 

1> insert into address values ('Tai','','Tng','647-647-6477','416-614-6144','64 Pluto Way Brampton,ON,CA') 2> go (1 rows affected).

Now it allows. The check is done only on participating key columns. Others can have duplicate data.

            For our Address Book utility this table is set up. Further we will see few commands to retrieve data. Try some simple SQL to retrieve data in ADDRESS Table:

1> Select Fname,Lname,Phone,Cell from address 2> go Fname            Lname            Phone           Cell ---------------- ---------------- --------------- --------------- David            Brown            905-264-2644    647-746-7466 Dilshaad         Akhtar           905-932-9322    416-417-4177 Sabar            Raikoti          905-509-5099    419-914-9144 Tai              Tang             647-647-6477    416-614-6144 Tai              Tng              647-647-6477    416-614-6144 (5 rows affected)

Ops, I wanted only David, he called me last night.

1> Select Fname,Lname,Phone,Cell from address where fname='David' 2> go Fname            Lname            Phone           Cell ---------------- ---------------- --------------- --------------- David            Brown            905-264-2644    647-746-7466 (1 rows affected)

So, it’s easy to select one out of many. While comparing strings you have to specify the correct case for all letters or you need to do comparison using some function as below.

1> Select Fname,Lname,Phone,Cell from address where upper(fname)='DAVID' 2> go Fname            Lname            Phone           Cell ---------------- ---------------- --------------- --------------- David            Brown            905-264-2644    647-746-7466 (1 rows affected)

Another situation, I think the gentleman called me three weeks ago; mm I can’t recall the name but phone number was 416-509…… may be 647-509 . . .. Well I remember middle three digits were 509 . . . a kind of . . . let me try

1> Select Fname,Lname,Phone,Cell from address where phone like ('%509%') 2> go Fname            Lname            Phone           Cell ---------------- ---------------- --------------- --------------- Sabar            Raikoti          905-509-5099    419-914-9144 (1 rows affected)

Oh, yeah, he was a nice guy. I gotta help him, let me call him.

There will be many situations; when you may remember one of the properties associated with a contact and then you can get hold of his/her number. Your friend’s friend once met with you and he was talking about some vacancies that are about to open at his workplace. He actually liked chat with you and had promised to recommend your name. They have a reputed setup and just yesterday you heard that S & R Software is hiring.  How will you get to him because your friend has gone back home for a long break?

1> insert into address values ('John','D','walker','419-932-9322','647-417-4879','promised help with JOB') 2> go (1 rows affected)

 Oh yes, you could hardly remember that you had entered this fact in the database (Table). Your database was new and you were really interested to enter everything if you could. You only remember ‘promised’ word and nothing else. Let us try.

1> Select Fname,Lname,Phone,Cell from address where mname like ('%promised%') 2> go Fname            Lname            Phone           Cell ---------------- ---------------- --------------- --------------- (0 rows affected)

Well don’t despair. Give another try.

 

1> Select Fname,Lname,Phone,Cell from address where addr like ('%promised%') 2> go Fname            Lname            Phone           Cell ---------------- ---------------- --------------- --------------- John             walker           419-932-9322    647-417-4879 (1 rows affected)

You were lucky, because SQLServer had helped? No; lucky, because you had entered something in your table. Now you have his name and go ahead call him, he liked chat with you.

            The best thing about database (or data table) is that once you create it, you can keep adding new contact for ever. There are simple setups to double check that the data that you are entering is valid. I mean no mistake or error. This is done at database level and you do not need to develop the software. Triggers and some constraints will help us do some sanity checks. We will talk about triggers and other constraints in next part.

            Actually, for our walk through the table ADDRESS is ready. But I believe that, because we have a hi-fi database system, why not try some house-keeping kind of things. I mean few features that allow us to maintain our database in top position. We are ok for now and will discuss Triggers and some of constraints in next part.

 

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.