Sunday, 14 July 2013

Sql Server Query samples - Part 2

















Views
     Views are the additional layer on the table.Which enables to protect our sensitive data based on the need.

Create Table Products
(
productid        INT,
productname  VARCHAR(40),
productdesc   VARCHAR(40),
price              INT,
)

Now from the above table i want to expose the important details instead of all columns so i am creating a view which will virutally create a visualization of that table.

Create View Product_View
As
Select productid, productname, price from Products


Auto Increment, Select, Insert into,Select Top,Select into,Distinct

         Auto Increment means instead of insert the value each time in your it will insert the value automatically to the corresponding column with one value increment than previous value.This can be specify in the table creation itself by a Keyword IDENTITY .This keyword have two values Seed and Increment

Create Table Products
(
productid        INT   IDENTITY(1000,1),
productname  VARCHAR(40),
productdesc   VARCHAR(40),
price              INT,
)

In the above table , Products productid is set as autoincrement starts the value inserting from 1000 and increment the for 1 for each insert .
Example : 

INSERT into Products(productname,productdesc,price) values('TV','Golden eye',25000);
INSERT into Products(productname,productdesc,price) values('TV','LED',15000);
INSERT into Products(productname,productdesc,price) values('Fridge','LCD',35000);

When you select the records from the Products table ,You can see the product id column value is inserted automatically with increment in each row.

Select * from Products

Output











      

     Now if we want to make a backup of data from existing table,but you don't have a create schema for table in this case we can create the Schema and backup data from existing table into new  by using Select into

SELECT  * INTO Product_BackUp 
FROM Products
WHERE 1=0

From the above code now we are taken a backup of table structure from Products to Product_BackUp it only have the structure of table not data.To get a backup including data following query will resolve that.

SELECT Productid,price INTO Product_BackUp
From Products

(OR)

SELECT * INTO Product_BackUp
From Products

Note :
Table Product_BackUp is created at the Runtime, We don't want to create the table .Now we select the records from the Product_BackUp,

Select * From Product_BackUp

Output :













If  you want to select the top records from the table then following query will be used.Below Query result in Top 2 records from the table.

SELECT TOP 2  * FROM Products












If you want to select Top 2 records other than 1 Record Then That  means records which is in the 2nd and 3rd based on condition, Result must have 1001 and 1002 productid value , it must leave 1000

SELECT   productid,
                 productname,
                 productdesc,price 
FROM     (      SELECT *,Row_number() over(order by productid) as 'Row'
                       FROM Products 
                 ) As  rtable

where Row > 1

Output











      If you want a records should be unique in display then we should use Distinct to avoid duplication in displaying the result.

Select Distinct * from Products

From the Above query we can see the basic things of sql server.