hi

Tables in Sql Server

Tables in Sql Server which stores the data in the formate of Rows and columns.
In SQL Server, tables can be created graphically using SQL Server Management Studio (SSMS) or using a query.
Syntax for a creating Table using Query
CREATE  TABLE  TableName
(
ColumnName1 Datatype [CONSTRAINT],
ColumnName2 Datatype [CONSTRAINT],
ColumnName3 Datatype [CONSTRAINT],
ColumnNameN Datatype [CONSTRAINT]
)
Note:- We will understand “CONSTRAINT” in next tutorial
For example create table for student
CREATE TABLE Student
(
ID INT NOT NULL,
Name NVARCHAR(50) NULL,
SchoolName NVARCHAR(50) NULL,
Gender TINYINT NULL
)



To create Student table, graphically, using SQL Server Management Studio
1. Right click on Tables folder in Object explorer window
2. Select New Table
3. Fill Column Name, Data Type and Allow Nulls, as shown below and save the table as Student.

WHAT IS NULL
NULL represents unknown data. A Column with NULL value means column doesn’t have data/value. NULL is different from a value 0, spaces or empty string, they are not equivalent.

Reterieveing Data From Table

We can use the SELECT statment like below to fetch records from the Customer Table.
SELECT * FROM student

Inserting Records
We can add records to the Table by using INSERT statement as shown in the below DEMOs
1: Adding records to the Student Table with all the column values.
INSERT INTO Student(ID, Name, SchoolName,Gender)
VALUES (1, ‘Munesh’, ‘ABC’,0)
No Need to mention Column Names if we are passing all the column values and values are specified in the same order as the columns position in the Table.
INSERT INTO Student
VALUES (2, ‘Rahul’, ‘PQR’,1)
Column names need be specified if we are not inserting all the column values and also if the order of the values is different from the actual column position in the table.
Let us verify the Student Table Data.
SELECT * FROM Student


 2: Insert without Optional column Gender value.
In student table there are some column which accept Null like name ,school name, gender. lets insert null to Gender column
INSERT INTO Student (ID,Name,SchoolName)
VALUES (2, ‘Anshuman’, ‘AAA’)



Output

3: Sql Server will Give an exception if we don’t pass NOT NULL column value.
INSERT INTO dbo.Student
( ID, Name )VALUES  ( 3, ‘Anshuman’ )

— here if SchoolName is Not Null column and if you do not insert value for it ,it will give exception
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column ‘SchoolName, table ‘Student; column does not allow nulls. INSERT fails.

The statement has been terminated.
ALTER TABLE
We can modify the Table structure using ALTER TABLE Statement.
ALTER TABLE Student
ADD DateOfBirth DATETIME NULL
SELECT * FROM student
And see the table which contain one more column with name DateOfBirth

Add one record to table with dateOfBirth
INSERT INTO dbo.Student
( ID, Name, SchoolName, Gender,DateOfBirth )
VALUES  ( 4, — ID – int
‘Govind’, — Name – nvarchar(50)
‘GIT’, — SchoolName – nvarchar(50)
0,  — Gender – tinyint
’01/01/1991′ — DateOfBirth
)



SELECT * FROM student



Try to add a NOT NULL column City to the Student Table which having four rows.
ALTER TABLE Student
ADD City NVARCHAR(50) NOT NULL


ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column ‘CITY’ cannot be added to non-empty table ‘Student because it does not satisfy these conditions.
From the above DEMO result it is clear that we can add NOT NULL column to only an Empty Table.
DROP TABLE
DROP TABLE statement deletes both data and table definition permanently from the database. This action cannot be undone, so always double verify before issuing this command.
 Drop the Student Table
DROP TABLE Student
Try to access a Table which is Dropped.
SELECT * FROM Student
RESULT:
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘Student.


Share this

Related Posts

Previous
Next Post »