T-SQL

--Decimal VS Money
--potential loss of precision when using multiplication or division
SELECT money = $0.46 / $345.70 = 0.00133
SELECT decimal = 0.46 / 345.70 = 0.00133063 --so prefer Deciaml rather than Money
--Money is (19,4) which means 19 total digits, 4 of which are right of the decimal point, Small Money is(10,4).
--Decimal can be setup however you want, like Decimal(10,2).

****************************************************************************************************************************************

CREATE DATABASE TEST
USE TEST
CREATE TABLE NEWS(TITLE VARCHAR(100) PRIMARY KEY,
DESCRIPTION VARCHAR(100),
ARTICLEID INT,
AUTHOR VARCHAR(100)
)
SELECT * FROM SYSOBJECTS WHERE XTYPE='U' --Tables
DELETE FROM SYSOBJECTS WHERE XTYPE='U'
-- Drops all user tables in the current database.
WHILE EXISTS(SELECT [name] FROM sys.tables WHERE [type] = 'U')
BEGIN
DECLARE @table_name varchar(50)
DECLARE table_cursor CURSOR FOR SELECT [name] FROM sys.tables WHERE [type] = 'U'
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
EXEC ('DROP TABLE [' + @table_name + ']')
PRINT 'Dropped Table: ' + @table_name
END TRY
BEGIN CATCH
END CATCH
FETCH NEXT FROM table_cursor INTO @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
END
*************************************************************BASIC COMMANDS***************************************************************************
USE ROOKIE --To work with perticular database
SELECT * FROM SYSOBJECTS --Lists all the objects of current database
SELECT * FROM SYSOBJECTS WHERE XTYPE='U' --Tables
SELECT * FROM SYSOBJECTS WHERE XTYPE='V' --Views
SELECT * FROM SYSOBJECTS WHERE XTYPE='P' --Stored procedures
SELECT * FROM SYSOBJECTS WHERE XTYPE='FN' --Scalar valued functions
SELECT * FROM SYSOBJECTS WHERE XTYPE='IF' --Inline table-valued functions
SELECT * FROM SYSOBJECTS WHERE XTYPE='TF' --Multistatement table-valued functions
SELECT * FROM SYSOBJECTS WHERE XTYPE='TR' --Triggers
SELECT * FROM SYSOBJECTS WHERE XTYPE='PK' --Primary keys defined in the tables
SELECT * FROM SYSOBJECTS WHERE XTYPE='F' --Foreign keys defined in the tables
SELECT * FROM SYSOBJECTS WHERE XTYPE='S'
SELECT * FROM SYSOBJECTS WHERE XTYPE='D'
SELECT * FROM SYSOBJECTS WHERE XTYPE='X'
SELECT * FROM SYSOBJECTS WHERE XTYPE='PC'
SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.VIEWS
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Books'
SELECT COUNT(*) AS NumberofColumns FROM SYSCOLUMNS WHERE ID=(SELECT id FROM SYSOBJECTS WHERE name='Employee')
BACKUP DATABASE ROOKIE FILE "C:MyDB.bkp"

*******************System/InbuiltStoredProcedures***************************************
SP_TABLES
SP_COLUMNS @TABLE_NAME='EMP'
SP_HELP
SP_RENAME 'EMP','EMP1'
SP_HELPINDEX DUMMYTABLE1
SP_SPACEUSED DUMMYTABLE1
SP_SERVER_INFO
SP_HELPUSER
SP_WHO

CREATE TABLE NEWS(TITLE VARCHAR(100) PRIMARY KEY,
DESCRIPTION VARCHAR(100),
ARTICLEID INT,
AUTHOR VARCHAR(100)
)
SELECT * FROM NEWS
INSERT INTO NEWS(TITLE,DESCRIPTION,ARTICLEID,AUTHOR) VALUES('AJAX','Asynchronous Javascript And XML',1,'JESSE JAMES GARRETTE');
INSERT INTO NEWS(TITLE,DESCRIPTION,ARTICLEID,AUTHOR) VALUES('ASP.NET','Web Technology news',2,'Unleashed');
INSERT INTO NEWS(TITLE,DESCRIPTION,ARTICLEID,AUTHOR) VALUES('C#.NET','Strong programming language',3,'Press');
INSERT INTO NEWS(TITLE,DESCRIPTION,ARTICLEID,AUTHOR) VALUES('SQL Server','Dedicated back-end for .Net app',4,'BPB');
INSERT INTO NEWS(TITLE,DESCRIPTION,ARTICLEID,AUTHOR) VALUES('HTML','Markup language for developing static web pages',5,'CR');
INSERT INTO NEWS(TITLE,DESCRIPTION,ARTICLEID,AUTHOR) VALUES('UML','Modelling language',5,'LPE');

******************************************************DATEANDTIME*******************************************************************************
SELECT GETDATE() AS DATETIMENOW --2007-11-07 12:32:57.190

SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,GETDATE())),DATEADD(m,1,GETDATE()))) --Last day of the month
SELECT CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '/' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/01' AS DATETIME) --First Day of the Month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) --Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0)) --Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+2,0)) --Last Day of Next Month
SELECT DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE()) --First Day of the Week
SELECT DATEADD(d,1,GETDATE()) --Returns a new datetime value based on adding an interval to the specified date.
SELECT DATEADD(m,1,GETDATE())
SELECT DATEADD(yy,1,GETDATE())
SELECT DATEADD(s,1,GETDATE())
SELECT DATEADD(DD, 0, DATEDIFF(DD, 0, GETDATE()))

SELECT DATEDIFF(d,GETDATE(),DATEADD(d,1,GETDATE())) --Returns the number of date and time boundaries crossed between two specified dates.
SELECT DATEDIFF(m,GETDATE(),DATEADD(m,1,GETDATE()))
SELECT DATEDIFF(yy,GETDATE(),DATEADD(yy,2,GETDATE()))

SELECT DATEPART(d,GETDATE()) --Returns an integer representing the specified datepart of the specified date.
SELECT DATEPART(m,GETDATE())
SELECT DATEPART(yy,GETDATE())

SELECT CONVERT(varchar,GETDATE(),1) AS DATE --11/07/07
SELECT CONVERT(varchar,GETDATE(),101) AS DATE --11/07/2007
SELECT CONVERT(varchar,GETDATE(),102) AS DATE --2007.11.07
SELECT CONVERT(varchar,GETDATE(),103) AS DATE --07/11/2007
SELECT CONVERT(varchar,GETDATE(),107) AS DATE --Nov 07, 2007
SELECT CONVERT(varchar(5),right(GETDATE(),7),101) AS TIMEIS --12:37
SELECT CONVERT(varchar(7),right(GETDATE(),7),101) AS TIMEIS --12:37PM

SELECT DATENAME(DW, GETDATE()) + ' ' + CONVERT(varchar,GETDATE(),107) + ' ' + right(GETDATE(),7) --Wednesday Nov 07, 2007 12:57PM

******************************************************************VIEWS*******************************************************************************
CREATE TABLE Publisher(
PubID INT NOT NULL PRIMARY KEY,
PubName VARCHAR(100),
City VARCHAR(100),
State VARCHAR(100),
Country VARCHAR(100)
)
INSERT INTO Publisher VALUES(0736,'New Moon Books','Boston','MA','USA')
INSERT INTO Publisher VALUES(0877,'Binnet & Hardly','Washington','DC','USA')
INSERT INTO Publisher VALUES(124,'Binnet & Hardly','Washington','DC','USA')

SELECT * FROM View_Publisher

CREATE VIEW View_Publisher AS SELECT PubID,PubName FROM Publisher --CREATING A VIEW
ALTER VIEW View_Publisher AS SELECT PubID FROM Publisher --ALTERING A VIEW
SP_RENAME View_Publisher,viewpub --RENAMING A VIEW
DROP VIEW viewpub --DELETING A VIEW
UPDATE View_Publisher SET PubName='Old Moon Books' WHERE PubID=736 --UPDATING VALUES OF COLUMNS IN A VIEW
DELETE FROM View_Publisher WHERE PubID=124 --DELETING VALUES OF A COLUMN IN A VIEW

--View is a virtual table consists of columns from one or more tables.
--Views ensure the security of data by restricting access to specific rows/columns.
--A querey fired on a view runs slower than table
--a trigger/index can't be defined on a view
--Altered columns are not appeared when the base table has changed.

****************************************************************CONSTRAINTS***************************************************************************
--A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s).
--1.Entity Integrity-ensures that there are no duplicate rows in a table.
--2.Domain Integrity
--3.Referential Integrity

--Creating a table with PRIMARY KEY Constraints
CREATE TABLE Employee(
EmployeeId INT NOT NULL PRIMARY KEY,
LName VARCHAR(30) NOT NULL,
FName VARCHAR(30) NOT NULL,
Address VARCHAR(100) NOT NULL,
HireDate DATETIME NOT NULL,
Salary MONEY NOT NULL
)
INSERT INTO Employee VALUES(1,'palsa','bixam','hyd',1-10-2007,6000)
INSERT INTO Employee VALUES(2,'yadlapally','naresh','srpt',2-10-2007,5000)
INSERT INTO Employee VALUES(3,'kothapally','suresh','kandukur',2-10-2007,5000)
INSERT INTO Employee VALUES(4,'chanda','naveen','krmngr',2-10-2007,5000)
INSERT INTO Employee VALUES(5,'maddali','siddarth','hyd',2-10-2007,5000)
SELECT * FROM Employee
DROP TABLE Employee
DELETE FROM Employee WHERE EmployeeId=2
--Creating a table with primary keys(candidate key)
CREATE TABLE Employee(
EmployeeId INT NOT NULL,
LName VARCHAR(30) NOT NULL,
FName VARCHAR(30) NOT NULL,
Address VARCHAR(100) NOT NULL,
HireDate DATETIME NOT NULL,
Salary MONEY NOT NULL,
CONSTRAINT pk_emploee_name PRIMARY KEY (EmployeeId,LName,FName)
)
ALTER TABLE employee ADD CONSTRAINT pk_employee PRIMARY KEY (EmployeeId)
ALTER TABLE employee DROP CONSTRAINT pk_employee
--Creating a table with FOREIGN KEY Constraints
CREATE TABLE Books(
BookID INT NOT NULL PRIMARY KEY,
AuthorID INT NOT NULL FOREIGN KEY REFERENCES Authors(AuthorID) ON DELETE CASCADE ON UPDATE CASCADE,
BookName VARCHAR(100) NOT NULL,
BookPrice MONEY NOT NULL
)
CREATE TABLE Books(
BookID INT NOT NULL PRIMARY KEY,
AuthorID INT FOREIGN KEY REFERENCES Authors(AuthorID) ON DELETE CASCADE ON UPDATE CASCADE,
BookName VARCHAR(100) NOT NULL,
BookPrice MONEY NOT NULL
)
CREATE TABLE Authors(
AuthorID INT NOT NULL PRIMARY KEY,
AuthorName VARCHAR(100) NOT NULL
)
CREATE TABLE Authors(
AuthorID INT UNIQUE,
AuthorName VARCHAR(100) NOT NULL
)
DROP TABLE Books
SELECT * FROM Books
INSERT INTO Books VALUES(1001,1,'Unleashed',500)
INSERT INTO Books VALUES(1002,2,'Press',300)
INSERT INTO Books VALUES(1003,3,'Dummies',400)
INSERT INTO Books VALUES(1004,NULL,'Spectrum',200) --we can assign NULL into Foreign Key Column(possible when refered column is UNIQUE Key)
INSERT INTO Books VALUES(1005,NULL,'Spectrum',200)
INSERT INTO Books VALUES(1006,NULL,'HiTech',300)
DELETE FROM Books WHERE AuthorID=5

DROP TABLE Authors
SELECT * FROM Authors
INSERT INTO Authors VALUES(1,'bixam')
INSERT INTO Authors VALUES(2,'sidarth')
INSERT INTO Authors VALUES(3,'riyaaz')
INSERT INTO Authors VALUES(NULL,'suresh')
INSERT INTO Authors VALUES(NULL,'vamshi')
DELETE FROM Authors WHERE AuthorID=4
UPDATE Authors SET AuthorID=5 WHERE AuthorName='suresh'
-----------------------------------------------------------
CREATE TABLE Books(
BookID INT NOT NULL PRIMARY KEY,
AuthorID INT NOT NULL,
BookName VARCHAR(100) NOT NULL,
Price MONEY NOT NULL
)
CREATE TABLE Authors(
AuthorID INT NOT NULL PRIMARY KEY,
AuthorName VARCHAR(100) NOT NULL
)
ALTER TABLE Books ADD CONSTRAINT fk_author FOREIGN KEY (AuthorID) REFERENCES Authors (AuthorID) ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE Books ADD CONSTRAINT fk_author FOREIGN KEY (AuthorID) REFERENCES Authors (AuthorID) ON DELETE NO ACTION ON UPDATE NO ACTION
ALTER TABLE Books DROP CONSTRAINT fk_author
--Creating table with a UNIQUE Key Constraints
DROP TABLE SalesHistory
CREATE TABLE SalesHistory(
SaleID INT NOT NULL UNIQUE,
Product VARCHAR(150) NULL UNIQUE,
SaleDate datetime NULL,
SalePrice money NULL
)
CREATE TABLE SalesHistory(
SaleID INT NOT NULL,
Product VARCHAR(150) NULL,
SaleDate datetime NULL,
SalePrice money NULL
CONSTRAINT uc_SaleID UNIQUE (SaleID,SaleDate)
)
ALTER TABLE SalesHistory ADD CONSTRAINT uc_SaleID UNIQUE(SaleID)
ALTER TABLE SalesHistory DROP CONSTRAINT uc_SaleID
--Creating table with a CHECK Constraints
CREATE TABLE Employee(
EmployeeId INT NOT NULL,
LName VARCHAR(30) NOT NULL,
FName VARCHAR(30) NOT NULL,
Address VARCHAR(100) NOT NULL,
HireDate DATETIME NOT NULL,
Salary MONEY NOT NULL CHECK (salary > 0)
)
ALTER TABLE Employee ADD CONSTRAINT check_sale CHECK (salary>0)
ALTER TABLE Employee DROP CONSTRAINT check_sale
-- disable the check_sale constraint in the employee table
ALTER TABLE employee NOCHECK CONSTRAINT check_sale
-- enable the check_sale constraint in the employee table
ALTER TABLE employee CHECK CONSTRAINT check_sale
-----------------------------------------------------------------
ALTER TABLE EMP ALTER COLUMN EMPNO INT NOT NULL
ALTER TABLE EMP ADD CONSTRAINT EMP_PK primary key(EMPNO)
ALTER TABLE EMP DROP CONSTRAINT EMP_PK primary key(EMPNO)
ALTER TABLE EMP ADD CONSTRAINT UniqueValues UNIQUE(EMPNO)
ALTER TABLE EMP DROP CONSTRAINT UniqueValues
ALTER TABLE EMP ADD CONSTRAINT Multiconstraint UNIQUE(EMPNO,DEPTNO)
ALTER TABLE EMP DROP CONSTRAINT Multiconstraint

************************************************************STORED PROCEDURES*************************************************************************
CREATE TABLE USERS(
UserID INT NOT NULL PRIMARY KEY,
UserName VARCHAR(20),
Password VARCHAR(20)
)
--------------------------------------------------------------------
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='USERS'
--------------------------------------------------------------------
SELECT * FROM USERS
SELECT COUNT(*) FROM USERS
SELECT * FROM USERS WHERE USERNAME LIKE '[ABCDEFGHIJKLMN]%'
UPDATE USERS SET UserName=replace(UserName,'a','@') WHERE UserName='Bixam'
---------------------------------------------------------------------------
INSERT INTO USERS VALUES(101,'Bixam','Palsa')
INSERT INTO USERS VALUES(102,'Suresh','Kothapally')
INSERT INTO USERS VALUES(103,'Siddarth','Maddali')
INSERT INTO USERS VALUES(104,'Naveen','Chanda')
INSERT INTO USERS VALUES(105,'Dilip','Pogaku')
----------------------------------------------------------------------------
--StoredProcedure with ONE OUTPUT Parameter
CREATE PROCEDURE NEWUSEROUTID(@UID INT OUTPUT)
AS
BEGIN
SELECT @UID=MAX(USERID) FROM USERS
END
DECLARE @uid INT
EXEC [dbo].[NEWUSEROUTID] @uid=@UID OUTPUT
SELECT @uid AS NEWUSERID

--StoredProcedure with MORE OUTPUT Parameters
CREATE PROCEDURE GETUNPWD(@UID INT,@UNAME VARCHAR(100) OUTPUT,@PWD VARCHAR(100) OUTPUT)
AS
BEGIN
SET @UNAME=(SELECT UserName FROM USERS WHERE UserID=@UID)
SET @PWD=(SELECT Password FROM USERS WHERE UserID=@UID)
END
DECLARE @uname varchar(100),@pwd varchar(100)
EXEC [dbo].[GETUNPWD] 101,@uname = @UNAME OUTPUT,@pwd=@PWD OUTPUT
SELECT @uname AS USERNAME,@pwd AS PASSWORD

--StoredProcedure with INPUTOUTPUT Parameter(i.e declaring an Output parameter, SQL Server treats it as an InputOutput Parameter.)
ALTER PROCEDURE GETIDUNPWD(@UID INT OUTPUT,@UNAME VARCHAR(100) OUTPUT,@PWD VARCHAR(100) OUTPUT)
AS
BEGIN
SET @UID=@UID+1
SET @UNAME=(SELECT UserName FROM USERS WHERE UserID=@UID)
SET @PWD=(SELECT Password FROM USERS WHERE UserID=@UID)
END

--Select StoredProcedure
CREATE PROCEDURE USERINFO AS SELECT * FROM USERS

--Insert StoredProcedure
CREATE PROCEDURE ADDUSER(@UID INT,@UNAME VARCHAR(20),@PWD VARCHAR(20))
AS
INSERT INTO USERS(USERID,USERNAME,PASSWORD) VALUES(@UID,@UNAME,@PWD)

--Update StoredProcedure
CREATE PROCEDURE UPDATEUSER(@UNAME VARCHAR(20),@PWD VARCHAR(20),@UID INT)
AS
UPDATE USERS SET USERNAME=@UNAME,PASSWORD=@PWD WHERE USERID=@UID

--Delete StoredProcedure
CREATE PROCEDURE DELETEUSER(@UID INT)
AS
DELETE FROM USERS WHERE USERID=@UID
----------------------------
SELECT * FROM EMP
CREATE PROCEDURE COUNTEMPS(@DNO INT,@ECNT INT OUTPUT)
AS
BEGIN
SELECT @ECNT=COUNT(*) FROM EMP WHERE DEPTNO=@DNO
END
--EXECUTING A STORED PROCEDURE HAVING OUTPUT PARAMETER
DECLARE @ECNT int
EXEC COUNTEMPS 20,@ECNT = @ECNT OUTPUT
SELECT @ECNT AS EMPLOYEECOUNT
-------------------------------------------------------------------------------------------------------------------------
--Error Handling in Stored Procedures
--3800 SQL server error messages, in the master catalog's "sysmessages" table.
--There are two types of errors you can face when you execute a stored procedure in a SQL server.
--One is Fatal and another one is NonFatal. Fatal errors normally terminates the execution of the SP and terminates the connection between the SQL server and the client application while nonfatal errors do not.
--Here is an example for Fatal error, I am trying to execute a SELECT SQL statement in a table which does not exist.
CREATE PROCEDURE SPFATAL1
AS
BEGIN
SELECT * FROM ROOKIE
PRINT 'Table Does not exist.'
END
EXEC dbo.SPFATAL
--As this table doesn't exist the SQL server raises a Fatal Error and the execution of the procedure terminates. So the PRINT statement will not be executed and we will not get the error message 'Table Does not exist'. Instead we get the error message raised by the Server.
--With one example the nonfatal errors can be illustrated. Let us assume that you are trying to Insert NULL value to a filed which is designed a Primary key. This will raise a nonfatal error and will allow you to execute the entire procedure.
--In three ways you can get the catch the errors in Stored procedures. Using @@ERROR, SP_ADDMESSAGE, and RAISERROR functions within SQL the Server. Let us discuss the one by one.
ALTER PROCEDURE ADDUSER(@UID INT,@UNAME VARCHAR(20),@PWD VARCHAR(20))
AS
BEGIN
INSERT INTO USERS(USERID,USERNAME,PASSWORD) VALUES(@UID,@UNAME,@PWD)
IF @@ERROR <> 0
BEGIN
PRINT 'An error occurred while adding the new user.'
RETURN(99)
END
ELSE
BEGIN
PRINT 'The new author information has been loaded.'
RETURN(0)
END
END
SELECT * FROM USERS
INSERT INTO USERS(USERID,USERNAME,PASSWORD) VALUES(101,'Ramesh','Palsa')

****************************************************************FUNCTIONS*****************************************************************************
--Scalar valued functions
CREATE FUNCTION NEWUSERID() RETURNS INT
AS
RETURN(SELECT MAX(USERID) FROM USERS)

SELECT dbo.NEWUSERID()
-----------
CREATE FUNCTION DateOnly(@InDateTime datetime) RETURNS varchar(10)
AS
BEGIN
DECLARE @MyOutput varchar(10)
SET @MyOutput = CONVERT(varchar(10),@InDateTime,101) --RETURN CONVERT(varchar(10),@InDateTime,101)
RETURN @MyOutput
END

SELECT dbo.DateOnly(GETDATE())
-----------------------------------------------------------------------
--Inline table-valued functions
CREATE FUNCTION fnGetAuthors(@au_name varchar(50)) RETURNS TABLE
AS
RETURN SELECT AuthorID,AuthorName FROM Authors WHERE AuthorName = @au_name

SELECT * FROM fnGetAuthors('bixam')
-------------------
CREATE FUNCTION LookByFName(@FirstLetter char(1)) RETURNS TABLE
AS
RETURN SELECT * FROM Employee WHERE LEFT(fname, 1) = @FirstLetter

SELECT * FROM dbo.LookByFName('N')
-------------------------------------------------------------------------------------------------------------------------
--Multistatement table-valued functions
CREATE FUNCTION fnGetEmployees(@ID Int) RETURNS @EmployeeList Table ( ID Int,
Name VarChar(50),
Salary Money
)
AS
BEGIN
IF @ID IS NULL
BEGIN
INSERT INTO @EmployeeList(ID, Name, Salary) SELECT EmployeeID, FName, Salary FROM Employee
END
ELSE
BEGIN
INSERT INTO @EmployeeList(ID, Name, Salary) SELECT EmployeeID, FName, Salary FROM Employee WHERE EmployeeID = @ID
END
RETURN
END
SELECT * FROM fnGetEmployees(1)
DROP FUNCTION myProc
*****************************************************************CURSORS******************************************************************************
DECLARE cur_books CURSOR FOR SELECT BookName FROM Books
DEALLOCATE cur_books

DECLARE @BookName varchar(100)
OPEN cur_books
FETCH NEXT FROM cur_books INTO @BookName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @BookName
FETCH NEXT FROM cur_books INTO @BookName
END
CLOSE cur_books
*****************************************************************TRIGGERS*****************************************************************************
CREATE TABLE Employees(
EmployeeId int NOT NULL PRIMARY KEY,
FirstName varchar(255),
LastName varchar(255)
)
SELECT * FROM Employees
INSERT INTO Employees VALUES(1,'Bixam','Palsa')
INSERT INTO Employees VALUES(2,'Suresh','kotha')
INSERT INTO Employees VALUES(3,'aaa','kotha')
INSERT INTO Employees VALUES(4,'Siddu','Maddali')
INSERT INTO Employees VALUES(5,'Naresh','Yadlapally')
INSERT INTO Employees VALUES(7,'Naresh','Karate')
INSERT INTO Employees VALUES(8,'Trigger','Trigger1')
----------------------------------------------------
CREATE TABLE Notifications(
Id int NOT NULL PRIMARY KEY,
TableName varchar(255),
)
DROP TABLE Notifications
SELECT * FROM Notifications

CREATE TRIGGER trg_notification ON Employees FOR INSERT,UPDATE,DELETE AS UPDATE Notifications WITH (ROWLOCK) SET ChangeId=NEWID() WHERE TableName='Employees'
CREATE TRIGGER trg_notification ON Employees FOR INSERT,UPDATE,DELETE AS INSERT INTO Notifications VALUES(1,'Employees')
DROP TRIGGER trg_notification
CREATE TRIGGER trgInsert ON Employees FOR INSERT AS Print ('AFTER Trigger [trgInsert] – is executed!')
CREATE TRIGGER trgUsers ON USERS FOR INSERT,UPDATE,DELETE AS PRINT('USERS TABLE IS MODIFIED')

******************************************************************INDEXES*****************************************************************************
--Note: Cannot create more than one clustered index on a table.
CREATE TABLE DUMMYTABLE1(
EMPID INT,
EMPNAME VARCHAR(8000)
)
Insert Into DummyTable1 Values (4, Replicate ('d',2000))
Insert Into DummyTable1 Values (6, Replicate ('f',2000))
Insert Into DummyTable1 Values (1, Replicate ('a',2000))
Insert Into DummyTable1 Values (3, Replicate ('c',2000))
Insert Into DummyTable1 Values (10, Replicate ('j',2000))
Insert Into DummyTable1 Values (2, Replicate ('b',2000))
Insert Into DummyTable1 Values (5, Replicate ('e',2000))
Insert Into DummyTable1 Values (8, Replicate ('h',2000))
Insert Into DummyTable1 Values (9, Replicate ('i',2000))
Insert Into DummyTable1 Values (7, Replicate ('g',2000))

SELECT * FROM DUMMYTABLE1

Alter Table DummyTable1 Add EmpIndex Int IDENTITY(1,1)
Alter Table DummyTable1 Delete EmpIndex Int IDENTITY(1,1)
TRUNCATE TABLE DUMMYTABLE1
DROP TABLE DUMMYTABLE1
ALTER TABLE DUMMYTABLE1 ADD SALARY MONEY
UPDATE DUMMYTABLE1 SET SALARY=1225 WHERE EMPID=6

CREATE INDEX IDX_SAL ON DUMMYTABLE1(SALARY)
CREATE UNIQUE NONCLUSTERED INDEX DummyTable1_empid ON DummyTable1 (empid) --Just Like Text Book Index (key--ref)
CREATE UNIQUE CLUSTERED INDEX DummyTable2_EmpIndex ON DummyTable1(EMPID) --As a Telephone Book (number--itsdata)
CREATE UNIQUE CLUSTERED INDEX INDX_ENAME ON DummyTable1(EMPNAME)

EXEC sp_helpindex DUMMYTABLE1
EXEC sp_spaceused DUMMYTABLE1
************************************************************TABLES*******************************************************
CREATE TABLE Projects(
PROJECTNAME VARCHAR(100) PRIMARY KEY,
PROJECTDESC VARCHAR(500),
PROJECTCODE NVARCHAR(500)
)
INSERT INTO Projects(PROJECTNAME,PROJECTDESC,PROJECTCODE) VALUES('PPEPM','Web based solution for promoting business education among the physicians','.NET')
INSERT INTO Projects(PROJECTNAME,PROJECTDESC,PROJECTCODE) VALUES('PTS-Optometrists','Web based solution for promoting business education among the physicians','ASP')
INSERT INTO Projects(PROJECTNAME,PROJECTDESC,PROJECTCODE) VALUES('PTS-Opticals','Web based solution for promoting business education among the physicians','ASP')

SELECT * FROM Projects



CREATE TABLE tblAccount(
AccNumber VARCHAR(100),
dBalance DECIMAL(10,4)
)
SELECT * FROM tblAccount
INSERT INTO tblAccount(AccNumber,dBalance) VALUES('B105',500.256)
INSERT INTO tblAccount(AccNumber,dBalance) VALUES('B106',NULL)
INSERT INTO tblAccount(AccNumber,dBalance) VALUES('B104',0.256)

---------------------------------------
CREATE TABLE DOCGROUPS(
PRACTICEID VARCHAR(100) NOT NULL,
GROUPNAME VARCHAR(200),
PHYSICIAN VARCHAR(200)
)
DROP TABLE GROUPS
USE ROOKIE
INSERT INTO DOCGROUPS VALUES('rajani@yahoo.com','Opthalmology','Vamshi')
INSERT INTO DOCGROUPS VALUES('rajani@yahoo.com','Opthalmology','Naresh')
INSERT INTO DOCGROUPS VALUES('rajani@yahoo.com','Cardiology','Suresh')
INSERT INTO DOCGROUPS VALUES('rajani@yahoo.com','Cardiology','Chanda')
INSERT INTO DOCGROUPS VALUES('rajani@yahoo.com','Dermatology','Bixam')
INSERT INTO DOCGROUPS VALUES('rajani@yahoo.com','Dentists','Palsa')
INSERT INTO DOCGROUPS VALUES('rajani@yahoo.com','Opthalmology','Ramesh')
SELECT DISTINCT GROUPNAME FROM DOCGROUPS
SELECT DISTINCT PHYSICIAN FROM DOCGROUPS WHERE GROUPNAME='Opthalmology'
SELECT * FROM DOCGROUPS
DELETE FROM DOCGROUPS WHERE PHYSICIAN='Palsa'
ROLLBACK
UPDATE DOCGROUPS SET PHYSICIAN='Gopal' WHERE GROUPNAME='Opthalmology'
---------------------------------------
CREATE TABLE IMAGES(
IMAGE_ID INT NOT NULL IDENTITY PRIMARY KEY,
IMAGE_DATA IMAGE,
IMAGE_TITLE VARCHAR(MAX),
IMAGE_TYPE VARCHAR(MAX),
IMAGE_LENGTH BIGINT
)
DROP TABLE IMAGES
SELECT * FROM IMAGES
DELETE FROM IMAGES WHERE IMAGE_ID=2
-------------------------------------------------------------------------
CREATE TABLE EMP(
EMPNO INT NOT NULL PRIMARY KEY,
EMPNAME VARCHAR(100),
EMPMAIL VARCHAR(100),
EMPBIRTHDATE DATETIME,
EMPHIREDATE DATETIME,
DEPTNO INT,
EMPPHOTO VARCHAR(500)
)
CREATE TABLE EMP(
EMPNO INT NOT NULL IDENTITY,
EMPNAME VARCHAR(20),
DEPTNO INT,
PRIMARY KEY (EMPNO,DEPTNO),
FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
)
TRUNCATE TABLE EMP
INSERT INTO EMP(EMPNO,EMPNAME,EMPMAIL,EMPBIRTHDATE,EMPHIREDATE,DEPTNO,EMPPHOTO) VALUES(99203,'Anthem','anthem@ppepm.org','10/25/1980','10/25/2007',10,null)
INSERT INTO EMP(EMPNO,EMPNAME,EMPMAIL,EMPBIRTHDATE,EMPHIREDATE,DEPTNO,EMPPHOTO) VALUES(73506,'Aetna','aetna@ppepm.org','10/25/1980','10/25/2007',20,null)
INSERT INTO EMP(EMPNO,EMPNAME,EMPMAIL,EMPBIRTHDATE,EMPHIREDATE,DEPTNO,EMPPHOTO) VALUES(99153,'Medicare','medicare@ppepm.org','10/25/1980','10/25/2007',30,null)
INSERT INTO EMP(EMPNO,EMPNAME,EMPMAIL,EMPBIRTHDATE,EMPHIREDATE,DEPTNO,EMPPHOTO) VALUES(99153,'Medicare','medicare@ppepm.org','10/03/1985','11/05/2007',30,null)
INSERT INTO EMP(EMPNO,EMPNAME,EMPMAIL,EMPBIRTHDATE,EMPHIREDATE,DEPTNO,EMPPHOTO,EMPSAL) VALUES(1,'Bixam','bixam@yahoo.com','10/03/1985','11/05/2007',10,null,7000)
INSERT INTO EMP(EMPNO,EMPNAME,EMPMAIL,EMPBIRTHDATE,EMPHIREDATE,DEPTNO,EMPPHOTO,EMPSAL) VALUES(2,'Ramesh','ramesh@yahoo.com','11/06/1985','11/06/2007',20,null,3000)
INSERT INTO EMP(EMPNO,EMPNAME,EMPMAIL,EMPBIRTHDATE,EMPHIREDATE,DEPTNO,EMPPHOTO,EMPSAL) VALUES(3,'Suresh','suresh@yahoo.com','11/06/1985','11/06/2007',30,null,3450)
INSERT INTO EMP(EMPNO,EMPNAME,EMPMAIL,EMPBIRTHDATE,EMPHIREDATE,DEPTNO,EMPPHOTO,EMPSAL) VALUES(5,'Mahesh','mahesh@yahoo.com','11/22/1982','11/06/2006',80,null,3857)

SELECT * FROM EMP
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='EMP'
SELECT TOP 1* FROM EMP ORDER BY 1 ASC
SELECT TOP 1* FROM EMP ORDER BY 1 DESC
SELECT EMPNO,(FNAME + '.' + LNAME) AS NAME,DEPTNO FROM EMP

DELETE FROM EMP WHERE EMPNO=99103
DELETE FROM EMP WHERE DEPTNO=50
DROP TABLE EMP

UPDATE EMP SET EMPSAL=5050.434 WHERE EMPNO=10505
UPDATE EMP SET DEPTNO=20 WHERE EMPID=102

ALTER TABLE EMP DROP COLUMN EMPNAME
ALTER TABLE EMP RENAME COLUMN ENAME TO EMPNAME
ALTER TABLE EMP ALTER COLUMN EMPNO INT(5)
ALTER TABLE EMP ALTER COLUMN EMPNAME VARCHAR(30)
ALTER TABLE EMP ADD EMPPHOTO VARCHAR(100)
ALTER TABLE EMP ADD EMPHIREDTE DATETIME
ALTER TABLE EMP ADD EMPBIRTHDATE DATETIME
ALTER TABLE EMP ADD EMPSAL DECIMAL(10,4)
ALTER TABLE EMP DROP COLUMN EMPSAL
ALTER TABLE EMP ALTER COLUMN EMPNAME ENAME VARCHAR(25)
EXECUTE SP_RENAME 'EMP.EMPID','EMPNO'
SP_RENAME 'EMP.EMPHIREDTE','EMPHIREDATE'
-------------------------------------------------------------------------
CREATE TABLE DEPT(
DEPTNO INT,
DEPTNAME VARCHAR(20),
LOC VARCHAR(20)
)
INSERT INTO DEPT VALUES(10,'ASP','NOIDA')
INSERT INTO DEPT VALUES(20,'ASP.NET','HYD')
INSERT INTO DEPT VALUES(30,'C#.NET','PUNE')
INSERT INTO DEPT VALUES(40,'VB.NET','HYD')
INSERT INTO DEPT VALUES(50,'AJAX','PUNE')
INSERT INTO DEPT VALUES(60,'ADO.NET','AKRON')

SELECT * FROM DEPT ORDER BY DEPTNO

ALTER TABLE DEPT ALTER COLUMN DEPTNO INT NOT NULL
ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO)
DELETE FROM DEPT WHERE DEPTNO=60
UPDATE DEPT SET LOC='CHENNAI' WHERE DEPTNO=40
-----------------------------------------------------------------------------
create table job(
ID int,
title nvarchar (10),
averageSalary int
)
INSERT INTO job VALUES(1,'Developer',3000)
INSERT INTO job VALUES(2,'Tester',4000)
INSERT INTO job VALUES(3,'Designer',5000)
INSERT INTO job VALUES(4,'Programmer',6000)
INSERT INTO job VALUES(6,'Admin',7000)
DELETE FROM JOB WHERE ID=5
SELECT * FROM job
*************************************************************JOINS*********************************************************************
SELECT * FROM EMP
SELECT * FROM DEPT

SELECT * FROM EMP WHERE EMP.DEPTNO NOT IN(SELECT DISTINCT DEPTNO FROM DEPT)
SELECT * FROM DEPT WHERE DEPT.DEPTNO NOT IN(SELECT DISTINCT DEPTNO FROM EMP)

--INNER-JOIN(NATURAL-JOIN,CROSS-JOIN)
SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO
SELECT * FROM EMP NATURAL PREDICTION JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO
SELECT * FROM EMP CROSS JOIN DEPT/SELECT * FROM EMP,DEPT
--OUTER-JOIN(LEFT,RIGHT,FULL)
SELECT * FROM EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO
SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO
SELECT * FROM EMP FULL OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO
--THETA-JOIN(Based on any condition)
SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO

**************************************************DELETINGIDENTITYOFACOLUMN*************************************************
CREATE TABLE AUTOINC (
ID INT NOT NULL IDENTITY PRIMARY KEY,
NAME VARCHAR(20)
)
INSERT INTO AUTOINC VALUES('SIDDU')
INSERT INTO AUTOINC VALUES('BIXAM')
INSERT INTO AUTOINC VALUES('LASTBUT')
INSERT INTO AUTOINC(ID,NAME) VALUES(14,'NARESH')
UPDATE AUTOINC SET ID=7 WHERE NAME='BIXAM'
DELETE FROM AUTOINC WHERE ID=4
SELECT * FROM AUTOINC
DROP TABLE AUTOINC
SET IDENTITY_INSERT AUTOINC ON
SET IDENTITY_INSERT AUTOINC OFF
ALTER TABLE AUTOINC DROP CONSTRAINT IDENTITY(ID)
SELECT SCOPE_IDENTITY()
DBCC CHECKIDENT('AUTOINC', RESEED, 6)

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('Table_Name') AND indid < 2
--To determine if a column is an identity column
IF COLUMNPROPERTY( OBJECT_ID( '[dbo].[Orders]' ), 'OrderID', 'IsIdentity' ) = 1
PRINT 'Orders.OrderID is an identity column.'
--To determine if a table has a primary key
IF OBJECTPROPERTY( OBJECT_ID( '[dbo].[Customers]' ), 'TableHasPrimaryKey' ) = 1
PRINT '[dbo].[Customers] table has a primary key.'

*************************************************DELETINGDUPLICATERECORDS********************************************************
SELECT DISTINCT * INTO TMP FROM EMP
TRUNCATE TABLE EMP
INSERT INTO EMP SELECT * FROM TMP
DROP TABLE TMP
SELECT * FROM EMP
*************************************************DISPLAYINGDUPLICATERECORDS********************************************************
CREATE TABLE Student(StudentID int,
StudentName varchar(100),
CompanyName varchar(100),
EMail varchar(100),
Salary decimal(10,5),
CONSTRAINT pk_student PRIMARY KEY(StudentID)
)
ALTER TABLE Student ALTER COLUMN Salary int
ALTER TABLE Student DROP CONSTRAINT pk_student

SELECT * FROM Student
SELECT * FROM Student WHERE EMail IS NOT NULL
SELECT DISTINCT * FROM Student
INSERT INTO Student VALUES(1,'Bixam','TripuraExports Pvt Ltd','palsabixam@gmail.com',7000)
INSERT INTO Student VALUES(1,'Bixam','TripuraExports Pvt Ltd','palsabixam@gmail.com',7000)
INSERT INTO Student VALUES(2,'Suresh','InMage Systems Ltd','ksureshsrihari@gmail.com',25000)
INSERT INTO Student VALUES(3,'Naveen','SoftPro Systems Ltd','c.naveen@softprosys.com',8000)
INSERT INTO Student VALUES(4,'Saibabu','ADP Pvt Ltd','sai_chintala@adp.com',30000)
INSERT INTO Student VALUES(5,'Vidyadhar','ValuesLabs Ltd','vidyadhar@valuelabs.net',25000)
INSERT INTO Student VALUES(6,'Mahesh','Tripura Exports',NULL,4000)
--displaying duplicate records
SELECT StudentID,StudentName,CompanyName,EMail,Salary,COUNT(StudentID) AS NofTIMES FROM Student GROUP BY StudentID,StudentName,CompanyName,EMail,Salary HAVING COUNT(StudentID)>1
SELECT * FROM Student WHERE StudentID IN(SELECT StudentID FROM Student GROUP BY StudentID HAVING COUNT(StudentID)>1) ORDER BY StudentID
SELECT StudentID,COUNT(StudentID) AS NofTIMES FROM Student GROUP BY StudentID HAVING COUNT(StudentID)>1
--displaying unique records
SELECT DISTINCT * INTO TMP FROM Student
--deleting duplicate records
SELECT DISTINCT * INTO TMP FROM Student
TRUNCATE TABLE Student
INSERT INTO Student SELECT * FROM TMP
SELECT * FROM Student ORDER BY StudentID

DROP TABLE TMP
*******************************DISPLAYINGNthHIGHESTPAID******************************************************************************
SELECT DISTINCT * FROM Student ORDER BY StudentID
--Selecting top n paid student
SELECT TOP 1 Salary FROM (SELECT TOP 4 Salary FROM Student ORDER BY Salary DESC) EXPR ORDER BY Salary
SELECT * FROM Student WHERE Salary IN(SELECT TOP 1 Salary FROM (SELECT TOP 2 Salary FROM Student ORDER BY Salary DESC) EXPR ORDER BY Salary)