CREATE DATABASE:
* CREATE DATABASE ARAVIND;
DROP DATABASE:
* DROP DATABASE ARAVIND;
RENAME DATABASE:
* ALTER DATABASE ARAVIND MODIFY=BALA;
CREATE TABLE:
—-> CREATE TABLE TABLE_BALA(
INT INTEGER IDENTITY PRIMARY KEY,
NAME NVARCHAR(50),
PLACE NVARCHAR(50),
ADDRESS VARCHAR(50),
SALARY DECIMAL(18,2),
PHONEnUMBER VARCHAR(30),
EMAIL VARCHAR(320)) ——>
RENAME TABLE:
* SP_RENAME ‘OLD_TABLE_NAME’,’NEW_TBL_NAME’
DELETE TABLE:
* DELETE TBL_BALA WHERE ID=1 <-EXAMPLE
TRUNCATE TABLE:
* TRUNCATE TABLE TBL_BALA
DROP TABLE:
* DROP TABLE TABLE_BALA
COPY TABLE TO ANOTHER TABLE:
* SELECT * INTO TBL_DEMO FROM TBL_BALA
LOCAL TABLE SAVE TEMPDB DATABASE:
—->CREATE TABLE #LOCAL_TABLE_DEMO(
ID INTEGER,
NAME NVARCHAR(30),
PLACE NVARCHAR(30))<——-
GLOBAL TABLE:
—->CREATE TABLE ##GLOBAL_TABLE_DEMO(
ID INTEGER,
NAME NVARCHAR(3),
PLACE NVARCHAR(3))<——
RENAME TABLE COLUMN VALUE:
* SP_RENAME ‘TBL_DEM.PLACE’,’AREA’,’COLUMN’;
SELECT:
* SELECT * FROM TBL_BALA
SELECT PARTICULAR COLUMN:
* SELECT PLACE FROM TBL_BALA
dISTINCT sELECT:
* SELECT DISTINCT PLACE FROM TBL_BALA;
SELECT COUNT:
*SELECT COUNT(NAME) FROM TBL_BALA
*SELECT COUNT(*) FROM TBL_BALA
*SELECT COUNT(DISTINCT NAME) FROM TBL_BALA
SELECT TOP,FIRST,LAST CUSTOMRS:
*SELECT TOP 2 * FROM TBL_BALA
*SELECT FIRST(FLD_NAME) AS NAME FROM TBL_BALA
SELECT aS:
*TEMPORARY NAME
SELECT FLD_NAME AS NAME,
PLACE AS AREA,
pHnO,
eMAIL FROM TBL_BALA;
SELECT iN:
* REDUCE OR OPERATIONS
SELECT * FROM TBL_BALA WHERE NAME IN(ARAVIND,BALA,AB)
JOIN:
INNER JOIN:
* SELECT TBL_CUST.PLACE,TBL_CUST.NAME,TBL_SUP.PHNO,TBL_SUP.AMOUNT FROM TBL_CUST INNER JOIN TBL_SUP ON TBL_CUS.ID=TBL_SUP.ID;
lEFT jOIN:
* SELECT TBL_CUST.PLACE,TBL_CUST.AMOUNT,TBL_SUP_MAIL,TBL_SUP.DATE FROM TBL_CUST LEFT OUTER JOIN TBL_SUP ON TBL_CUST.ID=TBL_SUP.ID;
rIGHT jOIN:
* SELECT TBL_CUST.ADRESS,TBL_CUST.NUMBER,TBL_SUP.EMPnO,TBL_SUP.JOIN FROM TBL_CUST RIGHT OUTER JOIN TBL_SUP ON TBL_CUST.ID=TBL_SUP.ID;
fULL JOIN:
* SELECT TBL_CUS.NAME,TBL_CUS.PLACE,TBL_SUS.PHNO,TBL_SUS.MAIL FROM TBL_CUS FULL OUTER JOIN TBL_SUS ON TBL_CUS.ID=TBL_SUS.ID;
* select * from tbl_login as lo full outer join tbl_detailsof d on lo.fld_id=d.fld_id;
lIKE,wILDCARD:
HAVING ONLY TWO METHODS(‘_’,’_%’)
* SELECT * FROM TBL_BALA WHERE SALARY LIKE ‘200%’;
* SELECT * FROM TBL_BALA WHERE SALARY LIKE ‘_400’;
uNION:
* COMBINES TWO TABLE RESULTS…ITS CONTAIN TWO OR MORE WHERE STATEMENTS
SELECT JOIN_CUS.NAME,JOIN_CUS.PLACE,JOIN_SUP.PHNO FROM JOIN_CUS LEFT OUTER JOIN JOIN_SUP ON TBL_CUS.ID=TBL_SUP.ID
UNION
SELECT JOIN_CUS.NAME,JOIN_CUS.PLACE,JOIN_SUP.PHNO FROM JOIN_CUS RIGHT OUTER JOIN JOIN_SUP ON TBL_CUS.ID=TBL_SUP.ID
uNION aLL:
* SELECT JOIN_CUS.NAME,JOIN_CUS.PLACE,JOIN_SUP.PHNO FROM JOIN_CUS LEFT OUTER JOIN JOIN_SUP ON TBL_CUS.ID=TBL_SUP.ID
UNIONALL
SELECT JOIN_CUS.NAME,JOIN_CUS.PLACE,JOIN_SUP.PHNO FROM JOIN_CUS RIGHT OUTER JOIN JOIN_SUP ON TBL_CUS.ID=TBL_SUP.ID
CREATE unique INDEX idx_FLD_nAMeeu on TBL_DETAILSOF (FLD_nAME);
iNDEX:
* select * from TBL_DETAILSOF with(index(idx_FLD_nAM))
* SELECT * FROM TBL_USERS
fUNCTIONS:
* SELECT substring (‘ABARAVIND05’,9,4)
* SELECT datalength(‘ GREGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG’);
* SELECT len(‘ DEWWWWW ‘)
* SELECT Len (NULL) AS FE
* SELECT lower(‘fggggggegefgvwfergekijfwbfwjfbwfjwfgwfwfwefdwe’)
* SELECT ltrim(‘ DVDFGVV ‘)
* SELECT rtrim(‘ V KHHH ‘)
* SELECT nchar(66)
* SELECT replace(‘JHNHBGNHJHNNMLMKJ’,’J’,’9′)
* select substring (convert(NVARCHAR(30), getdate(), 120), 1, 16 ) ——get current time and date
* —–space used –** sp_spaceused tbl_detailsof
* find page type——-dbcc ind(‘aravind’->(database name),’tbl_detailsof’,-1)
* select * from sysobjects,select * from sys.object ,select * from information_schema.tables,select * from sys.tables, select * from sys.procedures
sUB qUERY:
* SELECT U.NAME,U.AGE,U.PH FROM TBL_DETAILSOF WHERE FLD_NAME NOT IN(‘ARAVIND’,’AB’,’BALA’)
* sELECT NAME,AGE,(SELECT FLD_PLACE FROM TBL_USER U WHERE FLD_ID=1) FROM TBL_DETAILSOF D WHERE D.FLD_ID=1; LIKE JOIN
trigger:
* FIRST OF ALL WE CREATE A TWO TABLES LIKE THAT—>CREATE TABLE TBL_TRIGDEMO(ID IDENTITY PRIMARY KEY,NAME NVARCHAR(50),CITY NAVRACHAR(50))
AND SECOND tABLE iS –> CREATE TABLE TBL_TRIGG(ID INTEGER,NAME NVARCHAR(50),CITY NVARCHAR(50), AUCTION NVARCHAR(100),SE_TIMESTAMP DATETIME)
tRIGGERS:
*aFTER tRIGGER
* iNSTEAD OF tRIGGER
aFTER tRIGGER:
* CREATE TRIGGER TRGDEMO ON TBL_TRIGDEMO
FOR UPDATE
AS DECLARE @IN_ID INTEGER,@IN_NAME NVARCHAR(50),@IN_CITY NVARCHAR(50),@IN_ACTION VARCHAR(100)
SELECT @IN_NAME=I.NAME FROM INSERTED I;
SELECT @IN_CITY=I.NAME FROM INSERTED I;
IF UPDATE(NAME)
SET @IN_ACTION=”UPDATE TRIGGER”
IF UPDATE(CITY)
SET @IN_ACTION=”UPDATE TRIGGER”
INSERT INTO TBL_TRIGG(NAME,CITY,ACTION,TIMESTAMP) VALUES (@IN_NAME,@IN_CITY,@IN_ACTION,GETDATE());
PRINT “AFTER UPDATE”
cOPY ANOTHER DATABASE TABLE DATA TO ANOTHER DATABASE DATA:
eXAMPLE: select * into nEWtABLE from AB.DBO.EMPLOYEE
IMAGE SAVE IN DATABASE:
insert into TBL_IMGSAVE (IMAGEsAVE) select bULKcOLUMN from openrowset(bulk ‘e:\IMG\KLOOP.JPG’, single_blob)AS IMAGE;
ALTER TABLE NEW COLUMN ADD :
ALTER TABLE TBL_NEW ADD FLD_NAME NAARCHAR(50)
/**——————-iMAGE SAVE IN SQL DATABASE————–**/
EXAMPLE
INSERT TBL_TUTORIAL (FLD_ID,FLD_fNAME,FLD_lNAME,FLD_EMAIL,FLD_PHONE,FLD_CITY,IMGSAVE)
SELECT ‘8’ FLD_ID ,’SIVA’ FLD_fNAME ,’KUMAR’ FLD_lNAME,’SIVAKUMARDB@GMAIL.COM’ FLD_EMAIL,’8725963148′ FLD_PHONE,’CHENNAI’ FLD_CITY,
bULKcOLUMN from openrowset(bulk ‘e:\IMG\KLOOP.JPG’, single_blob) as IMGSAVE
learnsomething 