Monday, 29 February 2016
Example for SQL commands – DDL,
DML
--Create database Db_Product and check it in Databases Folder
create database
Db_Product
--using product database (Selected in databases dropdown)
use Db_Product
--create table Tbl_Items with 3 cols - Id int, ItemName
vc(30), ItemPrice money (Refresh tables folder)
create table
Tbl_Items(Id int,ItemName varchar(30),ItemPrice money)
--view data present in table
select * from Tbl_Items
--insert rows of data for Tbl_Items
insert into
Tbl_Items values(1,'Biryani',150.00)
insert into
Tbl_Items(Id,ItemName) values(1,'Noodles')
insert into
Tbl_Items(Id,ItemName,ItemPrice) values(1,'Coke',20)
--view data present in table
select * from Tbl_Items
--view data of two cols in table
select Id,
ItemName from Tbl_Items
--adding a new column Availablity (varchar(10))to the table
Alter table
Tbl_Items
Add Availability varchar(10)
--view data present in table
select * from Tbl_Items
--modify the datatype of availablity column to char
Alter table
Tbl_Items
Alter column
Availability char
--update Id as 2 for an item where ItemName is Noodles
update Tbl_Items set
Id=2 where
ItemName='Noodles'
--update Id as 3 for an item where ItemName is Coke
update Tbl_Items set
Id=3 where
ItemName='Coke'
--update Availability of all the items to A
update Tbl_Items set
Availability='A'
--view data present in table
select * from Tbl_Items
--delete an item data where id is 2
delete from
Tbl_Items where Id=2
--view data present in table
select * from Tbl_Items
--delete column ItemPrice
alter table
Tbl_Items
drop column
ItemPrice
--view data present in table
select * from Tbl_Items
--change column name to Status
sp_rename 'Tbl_Items.Availability','Status'
--view data present in table
select * from Tbl_Items
--changing Tablename to Tbl_ProductItems
sp_rename 'Tbl_Items','Tbl_ProductItems'
--to view data after changing table name
select * from Tbl_ProductItems
--clear the entire table data
truncate table
Tbl_ProductItems
--to view data present in table
select * from Tbl_ProductItems
--delete table
drop table
Tbl_ProductItems
--refresh the tables folder and you cant see the table there
--get data from deleted table
select * from Tbl_ProductItems --Invalid
object name
--delete database(can't delete if you are using it)
drop database
Db_Product
--refresh the database folder and see the database will be
missing
--You can't use this database any more
