SQL Server all type of queries

Create table query
CREATE TABLE sale
(
datetime datetime,
PROD_ID INT PRIMARY KEY,
PROD_NAME VARCHAR(50),
PROD_TYPE VARCHAR(25),
UNITPRICE BIGINT,
QUANTITY BIGINT,
AMOUNT MONEY,
)
SELECT * FROM sale
truncate table sale
drop table sale

Insert values in a sales table
INSERT SALE VALUES(’01-01-2008′,’100′,’SOAP’,’HOUSEHOLD’,’10’,’100′,’27000′)
INSERT SALE VALUES(’01-02-2008′,’101′,’OIL’,’HOUSEHOLD’,’11’,’75’,’26000′)
INSERT SALE VALUES(’01-05-2008′,’102′,’TOWEL’,’HOUSEHOLD’,’12’,’110′,’25000′)
INSERT SALE VALUES(’01-25-2008′,’103′,’SHAMPOO’,’HOUSEHOLD’,’15’,’125′,’11000′)
INSERT SALE VALUES(’01-26-2008′,’104′,’BRUSH’,’HOUSEHOLD’,’120′,’115′,’15000′)
INSERT SALE VALUES(’01-27-2008′,’105′,’PASTE’,’HOUSEHOLD’,’10’,’116′,’1500′)
INSERT SALE VALUES(’01-28-2008′,’106′,’PASTE’,’HOUSEHOLD’,’10’,’122′,’5000′)
INSERT SALE VALUES(’01-28-2008′,’107′,’DETTOL’,’HOUSEHOLD’,’10’,’122′,’5000′)

Sub queries samples
select prod_name, prod_type, unitprice from sale where exists(select * from sale where unitprice > 10 and quantity > 50)

select prod_name from sale where unitprice= (select unitprice from sale where amount = (select amount from sale where prod_id = ‘102’))

Create and bind rule
create rule rultypte
as @prod_type in(‘household’,’accesories’,’kitchen’)

sp_bindrule ‘rultypte’,’sale.prod_type’

Create and execute procedure
create procedure procc
as
begin
select max(quantity) as LARGESTQUANTITY FROM SALE
END

EXEC PROCC

Create and alter procedure
create procedure procd
as
begin
select quantity * unitprice as amount FROM SALE
END
exec procd

alter procedure procd
as begin
select prod_name from sale
end
exec procd

Procedure which will use prod_name as input and then display result
create procedure prodt @prod_name varchar(25)
as
begin
print ‘list of products’
select Prod_name from sale
where prod_name = @prod_name
end

exec @prod_name = ‘brush’

Query for See the Information About the Recent Connection
select
connect_time, connection_id, session_id, client_net_address, auth_scheme
from
sys.dm_exec_connections order by connect_time desc

Create Trigger
CREATE TRIGGER INSTEADOFDEMO
ON SALE
INSTEAD OF INSERT
AS
PRINT’HAPPY NEW YEAR’

For video learning, please visit kaushalstreet

Advertisements

Kaushal Arwal has written 6 articles