USE OF INSERT,CREATE COMMANDS IN MSSQLSERVER ALSO CONSTRAINTS ON COLUMNS

/*
Hello friends today i am aryan verma going to bring out for you new
thing,Will learn about COMMANDS OF MSSQLSERVER
THRESE  are really have become more complexity for beginner programmers
they think that how to make grip on these?
So If you want to get rid of tension of these to do practice on
this article and prepare yourself for joins
AND YOU WILL LEARN NEW QUERIES WHICH ARE VERY ESSENTIAL FOR
A WEB DEVELOPER FOR TESTING OF HIS QUERIES
*/
—HOW WE CAN RETRIEVE DATA FROM  TABLES USING COMMANDS AND IMPLETEMENT CONSTRAINTS ON COLUMNS WITH QUERY
STEP 1: FIRST OF ALL MAKE A DATABASE FOR PRACTICE
AND GIVEN HIM NAME AS dbOrient WITH GIVEN BELOW COMMAND OR QUERY IN SQLSERVER
create database dbOrient
STEP 2: NOW USE YOUR CREATED DATABASES WITH GIVEN BELOW QUERY
USE dbOrient
STEP 3: NOW CREATE NEW TABLE WITH GIVEN BELOW QUERY
/*in this query you are observing that constraints are applying on a particular column
which are implemeting data integrity nothing else friends.
these are very helpful if you want to implement validations from database point of
view
select whole query and execute in SqlQuery editor window
*/
CREATE TABLE DRIVERS(LICENCENUM INT NOT NULL CONSTRAINT Unique_LicneceNum UNIQUE(LICENCENUM),
LASTNAME VARCHAR(25) NOT NULL CONSTRAINT df_LastName DEFAULT ‘UnkownLn’,
FIRSTNAME VARCHAR(25) NOT NULL CONSTRAINT df_FirstName DEFAULT ‘UnkownFn’,
LOCATION varchar(25) NOT NULL CONSTRAINT df_Location DEFAULT ‘UnkownLoc’,
CLASS VARCHAR(25) NOT NULL CONSTRAINT df_Class DEFAULT ‘UnkownClass’
)
STEP 4: NOW CHECK OUT THAT YOUR TABLE HAS BEEN CREATED IN YOUR DATABASE WITH QUERY AND HIS
COLUMNS ALSO CONSTRAINTS ON THAT TABLE WITH GIVEN BELOW QUERIES
/*for columns checking*/
EXEC SP_COLUMNS DRIVERS
/*for constraints checking in your table’s columns*/
EXEC SP_HELPCONSTRAINT DRIVERS
FOR MORE CLEARIFICATION CHECK OUT IN Picture
STEP 5: INSERT SOME  VALUES INTO YOUR TABLE WITH GIVEN BELOW QUERY
1)
INSERT INTO DRIVERS(LICENCENUM,LASTNAME,FIRSTNAME,LOCATION,CLASS)
VALUES(13232,’VERMA’,’ARYAN’,’NEW YORK’,’CAR’)
GO
INSERT INTO DRIVERS(LICENCENUM,LASTNAME,FIRSTNAME,LOCATION,CLASS)
VALUES(13030,’KAPOOR’,’ROHIT’,’SEATTLE’,’CAR’)
GO
INSERT INTO DRIVERS(LICENCENUM,LASTNAME,FIRSTNAME,LOCATION,CLASS)
VALUES(13131,’RANA’,’RAMAN’,’ANNAPOLIS’,’CAR’)
GO
INSERT INTO DRIVERS(LICENCENUM,LASTNAME,FIRSTNAME,LOCATION,CLASS)
VALUES(18414,’KHANNA’,’PAWAN’,’MIGAMI’,’TRUCK’)
STEP 6: CHECK OUT DATA HAS INSERTED INTO YOUR TALBE WITH GIVEN BELOW QUERY
SELECT *FROM DRIVERS
STEP 7: NOW CREATE A ONE MORE TABLE GIVEN BELOW QUERY
CREATE TABLE VECHILES(TAG VARCHAR(15) NOT NULL CONSTRAINT Unique_Tag UNIQUE(TAG),
LOCATION VARCHAR(15) NOT NULL,
CLASS VARCHAR(10) NOT NULL)
STEP 8: INSERT SOME RECORDS IN THIS TABLE
insert into vechiles(tag,location,class) values(‘D824HA’,’Miami’,’Truck’)
insert into vechiles(tag,location,class) values(‘H122JM’,’NewYork’,’car’)
insert into vechiles(tag,location,class) values(‘J291QR’,’Seattle’,’Car’)
insert into vechiles(tag,location,class) values(‘L990MT’,’Seattle’,’Truck’)
insert into vechiles(tag,location,class) values(‘P091YF’,’Miami’,’Car’)
Select *From Vechiles

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s