Rohit Dhand

There is only one command to retrieve data back from the table and it is known as “Select”. Select can be used with different operators. Following are the different formats of Select command that can be used: Ø   Select * from Dept To Select all columns and all rows from Dept table Ø   Select Deptno , Dname from Dept To Select Column Deptno and Dname from Dept table Ø   Select Deptno * 10 as 'Calculated Deptno' from Dept This will multiply Deptno with 10 and the name of the column displayed will be ‘Calculated Deptno’ Ø   Select * from Dept where Deptno = 10 To select all rows where Deptno is equal to 10 Other Examples of Select Query Ø   Select * from Dept where Dname = 'MARKETING' Ø   Select * from Dept where Deptno = 10 or Loc = 'BOSTON' Ø   Select * from Dept where Deptno = 10 and LOC = 'BOSTON' Ø   Select * from Dept where Deptno between 10 and 30 Ø   Selec

SQLSERVER_9: Insert, Delete and Update Command Insert, Delete and Update commands are the Data Manipulation Commands in SQL Server. a.      Insert Insert command is used to create new records / rows in the Table. The following command is a basic example of Insert Command. Ø   Insert into Emp ( Empno , Ename ) values ( 101 , 'ABC' ) The above command inserts into table named “Emp” and in columns “Empno”, “Ename” with values 101 and ABC. The above command has following advantages: a.       We can skip any column in which we don’t want to insert any data. b.      We can also change the order of columns. The another type of insert command: Ø   Insert into Emp values ( 101 , 'ABC' ) In the above command, we cannot change the Column Order and even we can’t skip any column value. b.      Update Update command is used to update existing values within the table. Ø   Update Emp set Ename = 'New Name' where

Sometimes we forget to include constraints while a table is created. So SQL Server provides mechanism to include constraints in an already existing table. The following commands shows how to do the same: Alter table EmpTemp add constraint Salary_check check ( salary between 1000 and 5000 ) In the above command we are adding a constraint “Salary_check” on table “EmpTemp” and on column “Salary” i.e. its value between 1000 and 5000 only. Below are some commands to add constraints to an existing table. 1.   Alter table temp1 add Constraint Salary_check unique ( salary ) 2.   Alter table temp1 add constraint salary_primary primary key ( salary ) 3.   Alter table temp1 add constraint fk_dept foreign key ( Deptno ) references dept ( deptno )

In the real world, we have to use constraints that are not only dependent upon one table rather on two tables. This kind of setup is known as Primary Key - Foreign key constraint or also known as “Referential Integrity”. For e.g. lets see the example below: a.     Dept Table b.     Emp Table In order to determine the relationship, we have taken a column “Deptno” in Emp table which is validated from “Dept” table. It means “Emp” table’s column Deptno is having all the values from the Dept table and it cannot have a value that is not there in the Dept Table. To define the foreign key constraint, we can use the following syntax: FOREIGN KEY / REFRENTIAL INTEGRITY Create Table EmpTemp1 ( deptno numeric ( 10 ) constraint fk references dept ( deptno ) )

Constraints or validations are a well known phenomenon in and Database Management system to ensure that the columns can receive values that are well within the limit and restrictions imposed. For e.g. we can impose a check on a column named “Age” that the value should be between 20 – 40 only. This can be achieved by the following constraint Create Table EmpTemp ( Age numeric ( 10 ) constraint age_check check ( age between 20 and 40 ) ) There are different kinds of constraints in SQL Server: A    PRIMARY KEY CONSTRAINT This constraint is to ensure that we can differentiate between two rows in a table. There can only be one primary key in a table and it cannot be null and it will not allow duplicate values. Create Table EmpTemp (       Code numeric ( 10 ) constraint pk_primary primary key ) B.    UNIQUE This constraint is to ensure that we are not going to insert a duplicate value. Create Table EmpTemp (        Code numeri

SQL Server provides Alter and Drop commands for dropping tables and Database, as well changing the structure of Table. i.                    Drop This command is used to drop the Table or Database. We can use the command is the following way: Drop Table Dept      - This command will drop the table named “DEPT” Drop Database Test   - This command will drop the database named “TEST” ii.                  Alter This command is used to alter the Structure of Table. We can add, remove columns or even constraints with the help of this command. Alter table emptemp add address varchar ( 20 ) The above command will add a new column named “address” in the table called “emptemp” Alter table emptemp drop column address The above command will delete column named “address” in the table called “emptemp” Alter table emptemp alter column address varchar ( 200 ) The above command will change the column length to 200 for address. iii.