SQL – Server
Client / Server Technology
INTRODUCTION
Client requests services from server. Server is the provider of services.
1. Machine Language - Low Level Language
2. Assembly Language - Low Level Language
3. Middle Level Language - ‘C’
4. High Level Language - ‘C++’, ‘VB’ etc.
SQL – Structured Query Language
Used in RDBMS - Relational DataBase Management System.
LAN - Local Area Network
WAN - Wide Area Network
MAN - MetroPolitan Area Network
NOS - Network Operating System
SQL is an API
API – Application Program Interface
Database:
A database is a collection of related tables and other database objects. Few of the Database objects are created.
Table:
Table is a collection of rows and columns.
View:
View is an alternate way to look at information in one or more tables.
Default:
It is the value, which the SQL – Server assign to a column if the user fails to provide the value.
Rule:
It controls the data that can be entered into a column.
Stored Procedure:
It is collection of pre-compiled SQL Statements.
Trigger:
It is a special form of a stored procedure that gets executed when data gets modified in a table.
Indexes:
Indexes are a set of pointers that are ordered by the values of a column (Key Field). An index provides easy access to data in a table.
Data Types:
Data Types specify what type of data a column will hold and how the data is stored.
Constraints:
Constraints is a component, which is used for maintaining data integrity. It controls the value that can be stored in a column.
Datatypes
Datatypes specify what type of information is being stored in the column.
Types of Datatypes:
1. Character Datatypes
2. Binary Datatypes
3. Date, Time Datatypes
4. Numeric Datatypes
5. Logical Datatypes
6. User-Defined Datatypes
1. Character Datatypes:
1. Char:
To store fixed length strings.
(Fills blanks for the remaining length) (0 – 255 Characters)
ex: Char name(20)
If name=’RAVI ’ then for the remaining 16 characters the char datatypes allocate empty spaces.
2. Varchar:
To store variable length strings
(blanks are avoided)
ex: Varchar name(20)
In this case if name = ‘RAVI ’ then only 4 characters are allocated.
The remaining 16 characters are truncated.
3. Text:
To store the string of virtually unlimited size.
(upto giga bytes)
Binary Datatypes:
Stores strings consisting of binary values instead of characters.
1. Binary:
For fixed length binary string.
2. Varbinary:
For variable length binary string.
3. Image:
For storing large binary string.
Date, Time Datatypes:
1. Small Datatime:
From jan 1, 1900 to Jun 6 2079 .
2. Date / Time:
From Jan 1, 1753 to Dec 31, 1999 .
4. Numeric Datatypes:
1. Int:
Stores integer values from -231 to +231
2. Small Int:
From -215to +215
Bytes Required : 2 Bytes
3. Tny int:
0 to 255
Bytes Required : 1 bytes
4. Float:
Upto 15 digits precision
Requested Bytes : 8 bytes
5. Real:
Upto 7 digits precision
Requested Bytes : 4 bytes
5. Logical Datatype:
To view the existing defined datatypes
1. Int:
To store 0 or 1
Or
False or True
6. User defined Datatypes:
It is not a new datatype but it is the way of describing an existing datatype.
Syntax:
Sp_addtype, type_name, System type, {null/non null}
Ex:
Sp_addtype age, int
From here onwards we course age as a datatype for int
[int also be used]
To view the existing user defined datatype:
Sp_help
To drop (delete) the existing userdefined datatype:
Syntax:
Sp_droptype type name
Ex:
Sp_droptype age.
Note:
This is possible only when the user defined datatype is not used in any where.
Chapter – 1
Start à Programs à Microsoft Sql Server 7.0 -> Query Analyser
To create a database:
Syn;
Create database database_name
Eg:
Create database Meena
To create a table:
Syntax:
Create table table_name(column name1 data type, column name2 datatype……………)
Eg:
Create table hari(sno int, sname varchar(20))
To run the Query:
1. Type the Query
2. Select the Query
3. Press F5 Key
To insert the table:
Syntax:
Insert table_name values(value1,value2,………..)
Eg:
Insert hari values(1,’reka’)
To show the table:
Select * from table_name
Eg:
Select * from hari
create table hari(sno int,sname varchar(20))
insert hari values(1,'reka')
insert hari values(2,'suba')
insert hari values(3,'mala')
select * from hari
create table hari1(eno int,ename varchar(20),salary int, job varchar(20),doj datetime, hra int,da int, pf int,netsalary int)
insert hari1 values (1,'valli',8000,'manager',23/6/08 ,100,10,10,10000)
select * from hari1
To alter and update the fields and records:
Syn:
Alter table table_name and salary int
Eg:
Alter table hari add salary int
Syn:
Update table_name set fields name=values where recordno=values
Eg:
Update hari set salary=4000 where sno=1
Alter table hari add salary int
Update hari set salary=4000 where sno=1
Update hari set salary=8000 where sno=2
Update hari set salary=3000 where sno=3
Select * from hari
To Drop a table:
Syn:
Drop table table_name
Eg:
Drop table hari1
To Delete a row:
Syn:
Delete table_name where recordno=values
Eg:
Delete hari where sno=3
To show all the table names:
Sp_help
Chapter – 2
Comparison Operators:
=, >, <, >=, <=, <>, !=, !< and !>
Range Operators:
Between and Not Between
List Operators:
IN and Not IN
String Matching Operators:
Like and Not Like
Comparison with unknown values:
Is Null and Is Not Null
Combination of all these operators:
AND, OR and Negations (NOT)
Create table kk(sno int, sname varchar(20), course varchar(20), fees int)
Insert kk values (1,’reka’,’hdca’,7000)
Insert kk values (2,’suba’,’hdcp’,4000)
Insert kk values (3,’mala’,’dca’,3000)
Insert kk values (4,’kala’,’pcp’,5000)
Insert kk values (5,’shanthi’,’doa’,2000)
Insert kk values (6,’dhivya’,’.Net’,8000)
Select * from kk
Select * from kk where fees between 3000 and 6000
Select * from kk where fees not between 3000 and 6000
Select * from kk where course in(‘hdca’,’pcp’)
Select * from kk where course =‘dca’ or course=’pcp’
Select * from kk where sname LIKE ‘dhi%’
Select * from kk where sname LIKE ‘%thi’
Select * from kk where sname LIKE ‘%al%’
Select * from kk where sname LIKE ‘%a’
Select * from kk where sname LIKE ‘___a’
Select * from kk where sname LIKE ‘_h_v_a’
Select * from kk where sname LIKE ‘___n___’
Select * from kk where sname LIKE ‘_a_a’
Select * from kk where sname LIKE ‘______’
Select * from kk
Select distinct * from kk
Select sno from kk
Select sname from kk
Select course from kk
Select fees from kk
Select sum(fees) from kk
Select avg(fees) from kk
Select max(fees) from kk
Select min(fees) from kk
Select count(*) from kk
Chapter – 3
Create table fruit(sno int, sname varchar(20), dept varchar(20), fees int)
Insert fruit values(1,’reka’,’botany’,5000)
Insert fruit values(2,’suba’,’botany’,7000)
Insert fruit values(3,’mala’,’zoology’,9000)
Insert fruit values(4,’ramya’,’zoology’,6000)
Insert fruit values(5,’kala’,’physics’,4000)
Insert fruit values(6,’dhivya’,’physics’,2000)
Insert fruit values(7,’vidhya’,’chemistry’,10000)
Insert fruit values(8,’chitra’,’chemistry’,8000)
Insert fruit values(9,’kavitha’,’maths’,1000)
Insert fruit values(10,’sudha’,’maths’,3000)
Select * from fruit
Select * from fruit order by sno desc
Select * from fruit order by sno asc
Select dept, sum(fees)from fruit group by dept
Select dept, totalfees=sum(fees)from fruit group by dept
Select dept, sum(fees)from fruit group by dept having dept = ‘physics’
Select dept, totalfees = sum(fees)from fruit group by dept having dept = ‘physics’
Select dept, sum(fees)from fruit group by dept having dept = ‘botany’ or dept = ‘zoology’
Select dept, totalfees = sum(fees)from fruit group by dept having dept = ‘botany’ or dept = ‘zoology’
Select sno, sname, dept, fees from fruit order by sno compute sum (fees)
Select * from fruit order by sno compute sum (fees)
Select sno, sname, dept, fees from fruit order by dept compute sum (fees) by dept
Select * from fruit order by dept compute sum (fees) by dept
Chapter – 4
Select abs (-45)
Select upper (‘ayyappan’)
Select lower (‘AYYAPPAN’)
Select left (‘Welcome’,3)
Select right (‘welcome’,4)
Select replace (‘welcome’ , ’e’ , ’i’)
Select sqrt (81)
Select power (5,3)
Select ceiling (10.34)
Select floor (15.24)
Select round (12.57)
Select ascii (‘A’) 65
Select ascii (‘a’) 97
Select ascii (‘B’) 66
Select ascii (‘b’) 98
Select ascii (‘C’) 67
Select ascii (‘c’) 99
Select ascii (‘D’) 68
Select ascii (‘d’) 100
Select ascii (‘E’) 69
Select ascii (‘e’) 101
Select ascii (‘F’) 70
Select ascii (‘f’) 102
Select ascii (‘G’) 71
Select ascii (‘g’) 103
Select ascii (‘H’) 72
Select ascii (‘h’) 104
Select ascii (‘I’) 73
Select ascii (‘i’) 105
Select ascii (‘J’) 74
Select ascii (‘j’) 106
Select ascii (‘K’) 75
Select ascii (‘k’) 107
Select ascii (‘L’) 76
Select ascii (‘l’) 108
Select ascii (‘M’) 77
Select ascii (‘m’) 109
Select ascii (‘N’) 78
Select ascii (‘n’) 110
Select ascii (‘O’) 79
Select ascii (‘o’) 111
Select ascii (‘P’) 80
Select ascii (‘p’) 112
Select ascii (‘Q’) 81
Select ascii (‘q’) 113
Select ascii (‘R’) 82
Select ascii (‘r’) 114
Select ascii (‘S’) 83
Select ascii (‘s’) 115
Select ascii (‘T’) 84
Select ascii (‘t’) 116
Select ascii (‘U’) 85
Select ascii (‘u’) 117
Select ascii (‘V’) 86
Select ascii (‘v’) 118
Select ascii (‘W’) 87
Select ascii (‘w’) 119
Select ascii (‘X’) 88
Select ascii (‘x’) 120
Select ascii (‘Y’) 89
Select ascii (‘y’) 121
Select ascii (‘Z’) 90
Select ascii (‘z’) 122
Select char (100)
Select char (88)
Chapter – 5
Joins:
There are five types of joins
1. Unrestricted Join
2. Natural Join
3. Equi Join
4. Self Join
5. Outer Join
Create table asdf (sno int, sname varchar(20))
Insert asdf values(1,’reka’)
Insert asdf values(2,’suba’)
Insert asdf values(3,’mala’)
Insert asdf values(4,’ramya’)
Insert asdf values(5,’kala’)
Select * from asdf
Create table lkj (sno int, dept varchar(20))
Insert lkj values(1,’botany’)
Insert lkj values(2,’zoology’)
Insert lkj values(3,’physics’)
Insert lkj values(4,’chemistry’)
Insert lkj values(5,’maths’)
Select * from lkj
1. Unrestricted join:
A join that includes two or more tables without a where clause is an unrestricted join. If we have 4 rows in a table and 3 rows in another table and when we combine these rows.
The resultant output is 4 * 3 rows.
Ex:
Select * from asdf, lkj
2. Natural join:
It avoids redundant data. It compares the data of one column in a table with another column of a different table.
The output have columns that are unique.
Ex:
Select * from asdf, lkj where asdf.sno = lkj.sno
3. Equi Join:
Equi join is a type of join where the columns in two table are compared for equality and they would result in the output having two columns having identical values.
Ex:
Select * from asdf union select * from lkj
4. Self join:
Rows from a table are carlated to rows in the same table.
Eg:
Create table emp(Emp_code varchar(20), Mgr_code varchar(20), Emp_name varchar(20))
Insert emp values(‘E001’,’NULL’,’Raja’)
Insert emp values(‘E045’,’E001’,’Rani’)
Insert emp values(‘E048’,’E001’,’Ravi ’)
Select * from emp
Select a.emp_code, a.emp_name, b.emp_name as ‘Manager’ from emp a, emp b where a.mgr_code = b.emp_code
5. Outer Join:
An outer join displays all data from one table and matching join. Join data from the second table. The outer join operator *= will include all rows from the first table and the requestive of a statement restrictions.
Similarly =* will include all rows from the second table requestive of the statement restrictions.
Ex:
Select * from asdf, lkj where asdf.sno *= lkj.sno
This query gives the result as all the elements in a first table (asdf) and the restricted columns in a second table
Select * from asdf, lkj where asdf.sno =* lkj.sno
Sub Queries:
Query with in another query is called sub query.
Chapter – 6
Create table jj (no int, name varchar(20))
Insert jj values (1,’raja’)
Insert jj values(2,’suba’)
Insert jj (name) values (‘shanthi’)
Select * from jj
Create table jjj (no int not null, name varchar(20))
Insert jjj values (1,’raja’)
Insert jjj values(2,’suba’)
Insert jjj (name) values (‘shanthi’) -> ERROR
Select * from jjj
IDENTITY:
Create table ss (no int identity, name varchar(20))
Insert ss values (‘reka’)
Insert ss values (‘suba’)
Insert ss values (‘mala’)
Insert ss values (‘kala’)
Select * from ss
Create table sss (no int identity (10,5), name varchar(20))
Insert sss values (‘reka’)
Insert sss values (‘suba’)
Insert sss values (‘mala’)
Insert sss values (‘kala’)
Select * from sss
Create table kq(no int identity, name varchar(20) default ‘niranjan’)
Insert kq values (‘rani’)
Insert kq values (‘kala’)
Insert kq default values
Insert kq default values
Select * from kq
Group Selection:
Use master
Go
Create table prac (itemno int primary key, itname varchar(20) not null)
Go
Set implicit_transactions on
Go
Insert prac values (1, ‘brinjal’)
Go
Insert prac values (2,’tomato’)
Go
Insert prac values (3,’potato’)
Go
Select * from prac
Go
Commit transaction
Go
Set implicit_transactions off
Go
Group Selection:
Declare @a1 int, @a2 int, @a3 int
Select @a1 = 5
Select @a2 = 5
Select @a3 = @a1 * @a2
Print ‘Result is’
Print @a3
Create table fruit(sno int, sname varchar(20), dept varchar(20), fees int)
Insert fruit values(1,’reka’,’botany’,5000)
Insert fruit values(2,’suba’,’botany’,7000)
Insert fruit values(3,’mala’,’zoology’,9000)
Insert fruit values(4,’ramya’,’zoology’,6000)
Insert fruit values(5,’kala’,’physics’,4000)
Insert fruit values(6,’dhivya’,’physics’,2000)
Insert fruit values(7,’vidhya’,’chemistry’,10000)
Insert fruit values(8,’chitra’,’chemistry’,8000)
Insert fruit values(9,’kavitha’,’maths’,1000)
Insert fruit values(10,’sudha’,’maths’,3000)
Select * from fruit
Group Selection:
Declare meena cursor scroll for select * from fruit
Open meena
Fetch first from meena
Fetch next from meena
Fetch prior from meena
Fetch last from meena
Fetch absolute 5 from meena
Fetch relative 3 from meena
Fetch relative -2 from meena
Close meena
Chapter – 7
Create table asdf (sno int, sname varchar(20))
Insert asdf values(1,’reka’)
Insert asdf values(2,’suba’)
Insert asdf values(3,’mala’)
Insert asdf values(4,’ramya’)
Insert asdf values(5,’kala’)
Select * from asdf
Create table lkj (sno int, dept varchar(20))
Insert lkj values(1,’botany’)
Insert lkj values(2,’zoology’)
Insert lkj values(3,’physics’)
Insert lkj values(4,’chemistry’)
Insert lkj values(5,’maths’)
Select * from lkj
To create a view:
Syntax:
Create view view_name as join two tables
Eg:
Create view op as select * from asdf union select * from lkj
Select * from op
Select * from asdf
Select * from lkj
To drop the table:
Syntax:
Drop view view_name
Eg:
Drop view op
Declare @f int, @s int, @r char(20)
Select @f = 5
Select @s = 6
Select @r = convert (char(20), @f * @s)
Print ‘total =’
Print @r
Part – II
Chapter – 1
Create table fruit(sno int, sname varchar(20), dept varchar(20), fees int)
Insert fruit values(1,’reka’,’botany’,5000)
Insert fruit values(2,’suba’,’botany’,5000)
Insert fruit values(3,’mala’,’zoology’,5000)
Insert fruit values(4,’ramya’,’zoology’,5000)
Insert fruit values(5,’kala’,’physics’,5000)
Insert fruit values(6,’dhivya’,’physics’,5000)
Insert fruit values(7,’vidhya’,’chemistry’,5000)
Insert fruit values(8,’chitra’,’chemistry’,5000)
Insert fruit values(9,’kavitha’,’maths’,5000)
Insert fruit values(10,’sudha’,’maths’,5000)
Select * from fruit
Declare @sname varchar(20), @msg varchar (255)
Select @sname = ‘niranjan’
If exists (select * from fruit where sname = @sname)
Begin
Select @msg = ‘There is student name ‘ + @sname
Print @msg
End
Else
Begin
Select @msg = ‘There is no student name ‘ + @sname
Print @msg
End
go
Declare @sname varchar(20), @msg varchar (255)
Select @sname = ‘reka’
If exists (select * from fruit where sname = @sname)
Begin
Select @msg = ‘There is student name ‘ + @sname
Print @msg
End
Else
Begin
Select @msg = ‘There is no student name ‘ + @sname
Print @msg
End
go
Declare @i int
Select @i = 1
While @i <= 10
Begin
Print @i
Select @i = @i + 1
end
Declare @i int
Select @i = 10
While @i >= 1
Begin
Print @i
Select @i = @i - 1
end
declare @x int, @y int, @z int
set @x=100
set @y=50
set @z=@x*@y
print @x
print @y
print 'product of 2 numbers=' +convert(varchar,@z)
Chapter – 2 & 3
Set Options: (On | Off | Value)
Ex:
Setrowcount 50 = This will instruct the server to return only the first 50 rows of data
Setnocounton = tells the server to stop reporting the number of rows returned.
Deallocating Cursor:
Removes the definition and releases all the data structures allocating with the cursor.
Syntax:
DEALLOCATE Cursor_name
Ex:
Deallocate Meena
Data Modification through waves:
We can insert update and delete from views by using the queries.
We can modify more than one table by using views.
Restrictions on views:
1. We cannot delete rows from the view because this could affect multiple base tables.
2. We cannot update column for more than one table in a single update statement.
3. We cannot insert, update or delete into a view containing the distinct class.
To get the view information:
Select name from sysobjects where type = “v”
To see the list of columns in a view:
Sp_help FD
Views doesnot private maximum security.
Alternate for a view can catering security would be are procedure.
Indexes:
Index is a named database structure that is used by SQL server to locate a row of a table very quickly. The index might speed up the data retrieval but it would slow updates.
Types of indexes:
Clustered, unique, Composite, Non-Clustered. The index column if used in the where clause can improve the performance of select, update or delete.
Creating an index:
Syntax:
Create (Unique | Clustered | Non-Clustered) Index Index _ name on table_name (Column [..])
Ex:
Select * from emp
Create index E_Name_Index on Emp (EmpName)
Ex:
Create Clustered Index eno on EMP1 (EmpNo)
Clustered index is ideal for primary key, on column that is often used for a range search.
Non – Clustered Index:
This is the default type of index
Create nonclustered index emp_ind on emp(salary)
Unique index:
An unique index is created on a column that should not allow duplicate value to be entered in the column. That is no two rows can have identical index value.
Create unique index ind_name on fruit (sno)
Composite index:
Used when search operations have to be performed on 2 or more columns as unit as many as 16 columns can be combined into a single composite index.
The total length of the composite index can be exceed 256 bytes.
Ex:
Create index reka_ind on reka (sno, course)
Chapter – 4
There are two types of Temporary table
Global Temporary Table
Local Temporary Table
Global Temporary Table:
Create table ##tempno (sno int, sname varchar(20))
Insert ##tempno values (1,’reka’)
Insert ##tempno values (2,’suba’)
Select * from ##tempno
Local Temporary table:
Create table tempno (sno int, sname varchar(20))
Insert tempno values (1,’reka’)
Insert tempno values (2,’suba’)
Select * from tempno
Create table fruit(sno int, sname varchar(20), dept varchar(20), fees int)
Insert fruit values(1,’reka’,’botany’,5000)
Insert fruit values(2,’suba’,’botany’,7000)
Insert fruit values(3,’mala’,’zoology’,9000)
Insert fruit values(4,’ramya’,’zoology’,6000)
Insert fruit values(5,’kala’,’physics’,4000)
Insert fruit values(6,’dhivya’,’physics’,2000)
Insert fruit values(7,’vidhya’,’chemistry’,10000)
Insert fruit values(8,’chitra’,’chemistry’,8000)
Insert fruit values(9,’kavitha’,’maths’,1000)
Insert fruit values(10,’sudha’,’maths’,3000)
Select * from fruit
Group Selection:
Declare meena cursor scroll for select * from fruit
Open meena
Fetch first from meena
Fetch next from meena
Fetch prior from meena
Fetch last from meena
Fetch absolute 5 from meena
Fetch relative 3 from meena
Fetch relative -2 from meena
Close meena
Chapter – 5
Insert
Update
Delete
Droping
View
Index etc ….
Before Concept
PART – III
Chapter -1
Triggers:
In Triggers We can insert, update, delete the tables.
Create table emp (sno int, sname varchar(20), salary int)
Insert emp values (1,’reka’,7000)
Insert emp values (2,’suba’,5000)
Insert emp values (3,’mala’,8000)
Select * from emp
Group Selection:
Insert:
Create trigger banu on emp for insert as if (select sno from inserted) > 500
Begin
Print ‘sno cannot be > 500’
Rollback transaction
End
Insert emp values (499,’muthu’,4000)
Select * from emp
Insert emp values(501,’muthu’,5000)
Select * from emp
Group Selection:
Update:
Create trigger banu1 on emp for update as if (select sno from inserted) > 100
Begin
Print ‘eno cannot be > 100’
Rollback transaction
End
Update emp set no=99 where sname =’reka’
Select * from emp
Update emp set no=101 where sname =’suba’
Select * from emp
Group Selection:
Delete:
Create trigger banu2 on emp for delete as if(select count (*) from deleted) < 2
Begin
Print ‘your should not delete more than two records’
Rollback transaction
End
Delete emp where sno=2
Select * from emp
Delete emp where sno=499
Select * from emp
To drop a trigger:
Syntax:
Drop trigger trigger_name
Eg:
Drop trigger banu
Note:
We can use all insert, update, delete in a single trigger.
All create statement (Database, Table, Index, Procedure, Default, Rule, Trigger and view.
All drop statements,
Alter table and alter database, truncate table grant and revoke, update statistics reconfigure.
Load databases and transaction
All disk statements and select into (because it create tables)
Stored Procedure:
Syntax:
Create proc procedure_name as sql statements
(return (status value))
Eg:
Create proc reka as select * from reka
Return
To execute procedure:
Exec proc_name
Or
Execute proc_name
Ex:
Exec reka1
Select * from fruit
Sp_help
To drop an existing procedure:
Syntax:
Drop proc proc_name
Ex:
Drop proc reka1
Ex:
Create proc reka1 as select * from reka, fruit where reka.sno = fruit.sno
Using parameters:
Syntax:
Create proc procedure name
@para_name datatype1 = default value (output)
as
SQL statements
Return (status value)
Create proc ad1 @a int =10, @b int = 20 output as
Declare @c
Select @c = @a + @b
Print @c
Drop proc ad1
Exec ad1 50, 50
Exec sp_recompile ad1
Create proc mme @na varchar(20) with recompile
As
Select * from emp
where empnane = @na
Select * from emp
Exec sp_recompile fruit
Useful system procedures:
Sp_who : Current logins and operations
Sp_lock : Current lock and table identifiers
Sp_help : Objects in database or detailed information
Sp_helpdb : Database on the server
Sp_configure : Current system configuration settings.
Sp_helpdevice: Physical storage and backup devices in the server.
Creating a database:
Syntax:
Create database database_name [on default | database_derice} [=size], [,database_device [=size][..]
Ex:
Create database meena
Ex: 2
Create database meena on default
Ex: 3
Create database meena ojn def_device=2
Altering the size of a database:
Syntax:
Alter database database_name
[on {default \ database_device}
[=size], [,database_device [=size]..]
Ex:
Alter database meena on def_device = 2
Decreasing the size of the database:
Syntax:
Dbcc shrink db(database_name, new size)
Ex:
Dbc shrink db(meena, 1024)
System database:
Master
Model
Tempdb
Msdb
Create rule gender
As
@sex in (‘M’, ‘F’)
Chapter – 2
Constraints:
1. Primary key:
Syntax:
Does not accept duplicate values
Create table tb_name (col_name datatype constraint const_name)
Eg:
Create table mee (no int constraint kk primary key, name varchar(20))
Insert mee values (10, ‘meena’)
Select * from mee
Drop a constraint:
Alter table mee drop constraint kk
Unique constraint:
Create table mee1 (no int constraint kkk unique, name varchar(20))
Insert mee1 values (10, ‘meena’)
Select * from mee1
Check Constraint:
Create table mee2 (no int check (no < 50), name varchar(20))
Insert mee2 values (38,’reka’)
Insert mee2 values (51,’reka’) -> ERROR
Select * from mee2
Default Constraint:
Create table mee3 (no int default 50, name varchar(20))
Insert mee3 values (1, ‘raja’)
Insert mee3 (name) values (‘raja’)
Select * from mee3
No comments:
Post a Comment