Creating a Table
To store data into the relational database you must create a table in the database and for this purpose you need to use the “create command”
Syntax:
create table tablename
(colname datatype [constraint])
Rules
The table name should start with alphabet
Spaces and special symbols are not allowed (underscore)
A table can contain a max of 1024 columns
A table name can contain max of 128 characters.
Example
create table student(sid int constraint sid_pk primary key, sname varchar(30) not null, course varchar(30) constraint course_defdefault 'cpp')
Create table marks
(sid int constraint sid_fk references student(sid),
C int constraint c_chk check(c>=0 and c<=100) ,
cpp int constraint cpp_chk check (cpp>=0 and cpp<=100),
sql int constraint sql_chk check (sql>=0 and sql<=100),
total int, avg float, grade varchar(25))
The above example is column level constraint no needs to use foreign key word
create table orders
(orderid int, orderdate date not null,
custname varchar(50) not null,
item varchar(20),
quantity int not null,
constraint cid_item_pk primary key(orderId, item))
Here we are specifying the constraint at table level.
create table sentorders
(orderid int,
sentdate date,
item varchar(20),
qty int,
foreign key(orderId,Item) references orders(orderId, item))
The above example is table level constraint no needs to use foreign key word
Note:
Unique, primary, check, foreign key can be imposed in two different ways
1 column level
2 table level
While giving a name to the constraint they follow some conventions like
1 <colname_constrainttype>
2 <tablename_colname_constrainttype>
create table banks
(Custid int unique,
cname varchar(50),
balance decimal(7,2) not null)
create table bank
(Custid int constraint custid_uq unique,
cname varchar(50),
balance decimal(7,2) not null)
create table bank_Details
(citycode varchar(10),
branchcode varchar(10) , constraint cc_bc_uq unique(citycode, branchcode))
create table dept
(deptno int constraint deptno_pk primary key,
dname varchar(50),
location varchar(50))
create table emp(empno int, ename varchar(100),
job varchar(100),
mgr int,
hiredate datetime,
sal money,
comm money,
deptno int,
constraint deptno_ref foreign key(deptno)
references dept(deptno))
create table emp
(empid int primary key, ename varchar(30) not null,
job varchar(30) not null,
mgr int references emp(empid), sal money not null,
comm money not null,
check(comm<=sal*20/100))
In the above example empid is table level constraint
So here mgr is referring some table column empid
Primary key & foreign key col name cannot be same but data type and size should be same.
Altering Table Structure
After creating the table you may want to add additional columns and constraints to the table or delete a column on constraint or change the data type of a column
The following example changes data type of sname char of student table to char with a max length of 50 and not null constraint available on it will be deleted.
Alter table student
Alter sname char(50) null
Points to remember
Changing the data type of the column to completely different type will not be possible if the column contains data.
Adding not null constraint to a column will not be possible if the column already contains Null
Adding columns
Alter table student
Add fname varchar(40),
Address varchar(30))
Increasing the Width of column
Alter table students
Alter column sname varchar(100)
Changing the data type of the column
Alter table students
Alter column sname nvarchar(25)
Adding constraints
Alter table student
Add constraint sid_pk primary key(sid)
If we get error so run
Alter table student
Alter column sid int not null and then run
Alter table student
Add constraint sid_pk primary key(sid)
The following example add the foreign key constraint on column sid of the table “Marks”
Alter table marks
Add constraint sid_fk foreign key(sid)
References student(sid)
If you want to add constraint to the column without checking on the existing data in the column then use with “No Check”
The following example add checks constraint on column “c” of the table marks by using No Check option
Alter table marks
With No Check add check(c>=0 and c<=100)
Adding a not null constraint
Alter table student
Alter column sname varchar(25) not null
Removing a not null constraint
Alter table student
Alter column sname varchar(25) null
Adding a column to the table with constraint
Alter table students
Add sid int constraint sid_uq unique
Adding a check constraint on the fee column
Alter table students
Add constraint fee_chk check(fees>1000)
Adding a primay key constraint on the sno column
Alter table students
Alter column sno int Not Null
Alter table students
Add constraint sno_pk primary key(sno)
Drop Column
This is used for deleting columns from the table
Syntax:
Alter table <Tablename>
Drop column <columnlist>
The following example deletes the column fname and address of the table student
Alter table student
Drop column fname , address
Drop constraint
This options is used for delete constraint from the table.
Syntax:
Alter table <tablename>
Drop constraint <constaint name>
The following example deletes the foreign key constraint available on the table marks
Alter table marks
Drop constraint sid_fk1
We can delete only one constraint at a time.
Deleting primary key col and primary key constraint will not be possible if it is referred by a foreign key.
Renaming the Table
To rename a table use the stored procedure
Sp_rename ‘oldname’ ,’newname’
Example:
The following example renames the table student1 as stu1
Sp_rename ‘student1’,’stu1’
Renaming a column of the table
To rename a column of the table also we have to use the stored procedure
Sp_rename but the syntax changes as below
Syntax:
Sp_rename ‘tablename.colname’,’newname’
Example
The following example renames the column sid available in the table stu1 as studentid
sp_rename ‘stu.sid’,’studentid’
the foreign key constraint refers to primary key column and primary key table by using there unique id and not the name.
hence rename the primary key table or primary key column will not affect the foreign key.
Inserting Rows into the Table
Syntax:
Insert [into] <tablename> values (<values>)
The following example insert rows into the table student
Insert student values(1001,’a’,’sql’)
The following example insert rows into the table marks
Insertmarks values(1001,71,80,78,null,null,null)
Second syntax of insert
Insert [into] <tablename> (<colname>)
Values(<values>)
When you have to insert rows into the table without providing values for one or more columns then use this syntax
While using this syntax if you exclude a column from the list then the column will be inserted with default value if available and otherwise “Null”. Hence you cannot exclude a column that has No Default value and has Not Null constraint.
The following example insert rows into the student by excluding the course column
Insert student(sid ,sname) values(104,’d’)
Identity
When there is an identity col in the table , then to insert rows to the table you must this insert syntax
Insert [into] <tablename>]
(<colname>) values(<values>)
The following example creates a table with student1 by specifying identity on sid column
create table student1(sid int primary key identity (1000,1),
sname varchar(20) not null,
course varchar(20) default('cpp'))
insert student1222(sname, course) values('a','sql')
Inserting Values for identity column Manual
There may be situation where you want to Insert valus for identity column Manual
In, that case you have to set “IDENTITY_Insert” option On for the table into which you want to insert values for identity columns manuals and for this purpose use the following statement.
set identity_insert student1222on
Even, identity_insert option is set to On, while inserting rows into the identity table you must use the second syntax of insert statement
Note:
Identity_insert option will be on only temporily until sql server is closed you can set “ IDENTITY_INSERT” option On” for only one table at a time.
After setting identity_insert option on whatever the last value you insert into the identity column Manual will become current value of identity and identity will continue from that value if you set identity_insert option off
Creating a table from Another Table
Syntax:
Whenever you have to create a New Table from an existing Table use the following syntax of “select statement”
Select
*/ <collist> into <tablename>
From source table [where <condition>])
Example
The following example creates a table with a name student2 from the table student.
Select * into student2 from student
While creating a table from another table if you don’t want to copy all the rows from the existing table, than you can specify conditions in the “where clause” so that only the rows that satisfy the given condition will be inserted into the new table
When you want to create a table from another table without copying the data, then within the where clause specify any false condition like 1=0
Creating a table from another table will copy structure of the table but not constraints and indexes conditions
If identity available on existing table then identity will be copied to new table.
Inserting rows from one table to another
You can insert rows from one table to another provided data types of the column in those 2 tables is same
Syntax:
Insert [into] tablename <select statement>]
The following example inserts rows into the table student3 from student table
Insert student3 select * from student
Copying particular columns & particular records
Select empno,ename, sal into emp1 from emp
Copying only structure but not data
Select * into emp12 fromemp where 1=2
Update command
The following example updates the course of the student with id=1004 to oracle
Update student set course=’oracle’
Where sid=1004
Update the employee commission is 500 whose comm is n ull
Update emp set comm=500
Where comm is null
Increment employee commission by 200 whose commission is not null
Update emp set comm=comm+200
Where comm is not null
Increment employee salary by 10 % and commission by 20 % those who are working for dept 10 and 20
Update emp set sal=sal*1.1
Comm=comm*1.2
Where dept in (10,20)
Increment employee salary as follows
If clerk is 10 %
Salesman 15%
Manager 20 %
Others 5%
update emp
set sal= case job
when 'clerk' then sal*1.1
when 'salesman' then sal*1.15
when 'manager' then sal*1.2
else
sal*1.05
end
Row Fragmentation
It occurs when varchar fields are updated.
The value belongs to one row stored in two different rows.
The fields which are not frequently updated those fields must be declared with varchar type
The fields which are frequently updated those fields must be declared with char type.
The following example updates the marks table by calculating total and average
Update marks
Set total=c+cpp+sql,
Aveg=(c+cpp+sql)/3.0
The following example updates marks table by calculating grade based on average
Update marks set grade=
Case
When aveg>=90 then ‘distinction’
When aveg>=70 then ‘first class’
When aveg>=55 then ‘second class’
When aveg >=35 then ‘third class’
Else ‘fail’
End
