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.