SQL Verify that the stored procedure does not already exist.

IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;

SQL CATCH Block System Functions

The following system functions are available in the CATCH block and can be used to determine additional error information:

ERROR_NUMBER() Returns the number of the error

ERROR_SEVERITY() Returns the severity

ERROR_STATE() Returns the error state number

ERROR_PROCEDURE() Returns the name of the stored procedure or trigger where the error occurred

ERROR_LINE() Returns the line number inside the routine that caused the error

ERROR_MESSAGE() Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times

Example 1:

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )

AS

BEGIN TRY

BEGIN TRANSACTION -- Start the transaction

-- Delete the Employee's phone numbers

DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID

-- Delete the Employee record

DELETE FROM Employees WHERE EmployeeID = @EmployeeID

-- If we reach here, success!

COMMIT

END TRY

BEGIN CATCH

-- Whoops, there was an error

IF @@TRANCOUNT > 0

ROLLBACK

-- Raise an error with the details of the exception

DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int

SELECT @ErrMsg = ERROR_MESSAGE(),

@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH

--------------------------------------------------------------http://www.databasejournal.com/features/mssql/article.php/3587891

http://technet.microsoft.com/en-us/library/ms175976.aspx

-----------------------------------------------------------------

Example 2:

USE AdventureWorks;

GO

-- Verify that the stored procedure does not already exist.

IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL

DROP PROCEDURE usp_GetErrorInfo;

GO

-- Create procedure to retrieve error information.

CREATE PROCEDURE usp_GetErrorInfo

AS

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

GO

BEGIN TRY

-- Generate divide-by-zero error.

SELECT 1/0;

END TRY

BEGIN CATCH

-- Execute error retrieval routine.

EXECUTE usp_GetErrorInfo;

END CATCH;

Example 3:

Verify that the stored procedure does not exist.

IF OBJECT_ID ( 'usp_ExampleProc', 'P' ) IS NOT NULL

DROP PROCEDURE usp_ExampleProc;

GO

-- Create a stored procedure that will cause an

-- object resolution error.

CREATE PROCEDURE usp_ExampleProc

AS

SELECT * FROM NonexistentTable;

GO

BEGIN TRY

EXECUTE usp_ExampleProc

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() as ErrorNumber,

ERROR_MESSAGE() as ErrorMessage;

END CATCH;

Example 4:

BEGIN TRY

-- Generate a divide-by-zero error.

SELECT 1/0;

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

END CATCH;

Example 5:

BEGIN TRANSACTION;

BEGIN TRY

-- Generate a constraint violation error.

DELETE FROM Production.Product WHERE ProductID = 980;

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() as ErrorState,

ERROR_PROCEDURE() as ErrorProcedure,

ERROR_LINE() as ErrorLine,

ERROR_MESSAGE() as ErrorMessage;

IF @@TRANCOUNT > 0

ROLLBACK TRANSACTION;

END CATCH;

IF @@TRANCOUNT > 0

COMMIT TRANSACTION

Example 6:

Check to see whether this stored procedure exists.

IF OBJECT_ID ('usp_GetErrorInfo', 'P') IS NOT NULL

DROP PROCEDURE usp_GetErrorInfo;

GO

-- Create procedure to retrieve error information.

CREATE PROCEDURE usp_GetErrorInfo

AS

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() as ErrorState,

ERROR_LINE () as ErrorLine,

ERROR_PROCEDURE() as ErrorProcedure,

ERROR_MESSAGE() as ErrorMessage;

GO
-- SET XACT_ABORT ON will cause the transaction to be uncommittable when the constraint violation occurs.

SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRANSACTION;

-- A FOREIGN KEY constraint exists on this table. This statement will generate a constraint violation error.

DELETE FROM Production.Product WHERE ProductID = 980;

-- If the DELETE statement succeeds, commit the transaction.

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

-- Execute error retrieval routine.

EXECUTE usp_GetErrorInfo;

-- Test XACT_STATE:

-- If 1, the transaction is committable.

-- If -1, the transaction is uncommittable and should be rolled back.

-- XACT_STATE = 0 means that there is no transaction and a commit or rollback operation would generate an error.

-- Test whether the transaction is uncommittable.

IF (XACT_STATE()) = -1

BEGIN

PRINT 'The transaction is in an uncommittable state. Rolling back transaction.'

ROLLBACK TRANSACTION;

END;

-- Test whether the transaction is committable.

IF (XACT_STATE()) = 1

BEGIN

PRINT 'The transaction is committable. Committing transaction.'

COMMIT TRANSACTION;

END;

END CATCH;

SQL Error Handling (Try, Catch)

Syntax:

BEGIN TRY

Try Statement 1

Try Statement 2

...

Try Statement M

END TRY

BEGIN CATCH

Catch Statement 1

Catch Statement 2

...

Catch Statement N

END CATCH



Example 1 : Simple TRY…CATCH without RAISERROR function

BEGIN TRY

DECLARE @MyInt INT;

– Following statement will create Devide by Zero Error

SET @MyInt = 1/0;

END TRY

BEGIN CATCH

SELECT ‘Divide by zero error encountered.’ ErrorMessage

END CATCH;

GO

ResultSet:

ErrorMessage

Divide by zero error encountered.

Example 2 : Simple TRY…CATCH with RAISERROR function

BEGIN TRY
DECLARE @MyInt INT;
– Following statement will create Devide by Zero Error
SET @MyInt = 1/0;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);

SELECT @ErrorMessage = ERROR_MESSAGE();

RAISERROR (@ErrorMessage, 16, 1);

END CATCH;
GO
ResultSet:
Msg 50000, Level 16, State 1, Line 9

Divide by zero error encountered.

Example 3 :

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )

AS

BEGIN TRANSACTION -- Start the transaction

-- Delete the Employee's phone numbers

DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID

-- Delete the Employee record

DELETE FROM Employees WHERE EmployeeID = @EmployeeID

-- See if there is an error

IF @@ERROR <> 0

-- There's an error b/c @ERROR is not 0, rollback

ROLLBACK

ELSE

COMMIT -- Success! Commit the transaction

SQL

DBMS - Database Management System

A Database Management System (DBMS) is a computer program that can access data in a database.

The DBMS program enables you to extract, modify, or store information in a database.

Different DBMS programs provides different functions for querying data, reporting data, and modifying data.

RDBMS - Relational Database Management System

A Relational Database Management System (RDBMS) is a Database Management System (DBMS) where the database is organized and accessed according to the relationships between data.

RDBMS was invented by IBM in the early 1970's.

RDBMS is the basis for SQL, and for all modern database systems like Oracle, SQL Server, IBM DB2, Sybase, MySQL, and Microsoft Access.

SQL Column and Row check to get date between

declare @StDt datetime, @EdDt datetime
set @StDt='2009-06-17 11:00:00.000'
set @EdDt= '2009-06-18 12:30:00.000'
select * from Notification N
where
(N.Start_DateTime between @StDt and @EdDt
or N.End_DateTime between @StDt and @EdDt
or @StDt between N.Start_DateTime and N.End_DateTime
or @EdDt between N.Start_DateTime and N.End_DateTime)

SQL Get Duplicate Records(Pairs of Duplicate Row) in table

Select Ename
from emp
group by Ename
having count(*) > 1
select sal,Ename,count(*)
from emp
group by sal,Ename
having count(*) > 1
select sal,Ename
from emp
group by sal,Ename
having count(*) > 1
Return all pairs of city IDs that have the same city name
select * from emp
select c1.empno, c2.empno, c1.Ename
from emp c1, emp c2
where c1.empno ename = c2.Ename
Delete Duplicate Records – Rows
Example:1
DELETE
FROM emp
WHERE Empno NOT IN (SELECT MAX(Empno)FROM empGROUP BY ename)
Example:2
DELETE
FROM emp
WHERE EXISTS (
SELECT * FROM emp AS b
WHERE
b.[ename] = emp.[ename]
--AND b.[col2] = emp.[col2]
--AND b.[col3] = emp.[col3]
GROUP BY b.[ename]--, b.[col2], b.[col3]
HAVING emp.[Empno] > MIN(b.[Empno]
)

SQL Declare, If-esle, cast

declare @x char(10)
set @x='hai'
if(@x<>'hmai')//<>,=,>,<,>=.<=,!=
begin
print(@x)
end
declare @x char(10)
set @x='hai'
if(@x is not null)
begin
print(@x)
end
declare @x char(10)
set @x='hai'
if((@x<>'hai1')or(@x<>'hai2'))//or,and
begin
print(@x)
end
else
begin
print('else')
end
declare @x char(200)
set @x='select * from prgm'
Exec(@x)
Declare @ValidRule as varchar(10)
Set @ValidRule = 'False'
Declare @a as varchar(10)
Declare @prg as varchar(10)
Set @prg=null
Set @a=select * from program P where prg_id=isnull('+cast(@prg as varchar)+',P.prg) )//if @prg is null P.prg is assign to

SQL Begin, Rollback & Commit Transaction

BEGIN TRAN;
select * from tbl1;
DELETE FROM tbl1 where age=32 OR age=79;
select * from tbl1;
ROLLBACK TRAN;
select * from tbl1;

BEGIN TRAN;
select * from tbl1;
DELETE FROM tbl1 where age=32 OR age=79;
select * from tbl1;

COMMIT TRAN;
select * from tbl1;

SQL String Functions

select * from tbl1;

SELECT Fname,LEFT(Fname, 2) FROM tbl1;

SELECT Fname,RIGHT(Fname, 2) FROM tbl1;

SELECT Fname,LEN(Fname) FROM tbl1;

SELECT Fname,SUBSTRING(Fname, 2, 4) FROM tbl1;

SELECT Fname,PATINDEX('%na%', Fname)FROM tbl1;

SELECT Fname+city FROM tbl1;

SELECT Fname,REPLACE(Fname, 'a','') FROM tbl1;

INSERT INTO tbl1(Fname,age,city) SELECT Fname,age+12,city FROM tbl1;--we can select from other table also

SQL Case, Trim Statement

select * from tbl3
SELECT sno,total,average
, CASE WHEN average 50 THEN 'Fail'
WHEN average>50 THEN 'Pass'
ELSE 'Not Eligible'
END as result
FROM tbl3;
SELECT sno,average,average%2 FROM tbl3;
Example:2
case when ltrim(rtrim(a.Program)) = ltrim(rtrim(pgm.prgm_name))
and ltrim(rtrim(a.Partner)) = ltrim(rtrim(Partner.Partner_Name))
and ltrim(rtrim(a.Measure)) = ltrim(rtrim(msre.Msre_Name))
then 1
else 0
end Flag

SQL View Statement

In SQL, a VIEW is a virtual table based on the result-set of a SELECT statement.

A view contains rows and columns, just like a real table.

The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from a single table.

Note: The database design and structure will NOT be affected by the functions, where, or join statements in a view.

Syntax

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

Note: The database does not store the view data! The database engine recreates the data, using the view's SELECT statement, every time a user queries a view.

CREATE VIEW tbl3View AS SELECT sno,m1,m2 FROM tbl3 WHERE m1>10;/*Error Incorrect syntax near ';' i don't know why?*/

CREATE VIEW tbl3View AS SELECT sno,m1,m2 FROM tbl3 WHERE m1>10

select * from tbl3View;

drop view tbl3View;

SQL IN clause - Copy tables into another database

The IN clause can be used to copy tables into another database:

SELECT Persons.* INTO Persons IN 'Backup.mdb' FROM Persons

create database sampleBackup;

SELECT tbl3.* INTO tbl3 IN 'sampleBackup.mdb' FROM tbl3;

DROP DATABASE sampleBackup;

use sample;

SQL Select INTO Statement

The SELECT INTO statement is most often used to create backup copies of tables or for archiving records.

Syntax SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source

SELECT * INTO tbl3Backup FROM tbl3;

select * from tbl3Backup;

select * from tbl3;

drop table tbl3Backup;

SELECT * INTO tbl3Backup FROM tbl3 where m1>10;

select * from tbl3;

drop table tbl3Backup;

SELECT sno,m1,m2 INTO tbl3Backup FROM tbl3;

select * from tbl3Backup;

drop table tbl3Backup;

SELECT t1.Fname,t1.age,t2.address INTO tbl3Backup FROM tbl1 AS t1,tbl2 AS t2 WHERE t1.Fname=t2.Fname;/*we use all type JOIN*/

select * from tbl3Backup;

SQL Group By/ Having

GROUP BY
SELECT SUM(m1) FROM tbl3;
SELECT SUM(m1) FROM tbl3 GROUP BY sno;
SELECT SUM(m1) as Sum_Of_m1 FROM tbl3 GROUP BY sno;
SELECT sno,SUM(m1),avg(m1) FROM tbl3 GROUP BY sno;
SELECT m1,SUM(m1) FROM tbl3 GROUP BY sno;/*invalid*/

HAVING... was added to SQL because the WHERE keyword could not be used against aggregate functions (like SUM), and without HAVING... it would be impossible to test for result conditions.

SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value

SELECT SUM(m1) FROM tbl3 where m1>10;

SELECT SUM(m1) FROM tbl3 where SUM(m1)>10;/*invalid*/

SELECT sno,SUM(m1) FROM tbl3 GROUP BY sno HAVING m1>10;/*invalid*/

SELECT sno,SUM(m1) FROM tbl3 GROUP BY sno HAVING SUM(m1)>10;

Aggregate/ Scalar functions

create table tbl3 (sno varchar(10),m1 int,m2 int,total int,average int);

insert into tbl3 (sno,m1,m2) values ('1',10,20);

insert into tbl3 (sno,m1,m2) values ('2',30,40);

insert into tbl3 (sno,m1,m2) values ('3',50,60);

select * from tbl3;

UPDATE tbl3 SET total = m1+m2, average=total/2;

Aggregate functions

Aggregate functions operate against a collection of values, but return a single value.

Note: If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause!!

SELECT AVG(m1) FROM tbl3 WHERE m1>20;

SELECT COUNT(sno) FROM tbl3;

SELECT COUNT(*) FROM tbl3;

insert into tbl3 (sno,m1,m2) values ('3',5,60);

SELECT COUNT(DISTINCT sno) FROM tbl3;

/*SELECT FIRST(column) AS [expression] FROM table*/

SELECT FIRST(sno) AS lowest_m1 FROM tbl3 ORDER BY m1;

/*SELECT LAST(column) AS [expression] FROM table*/

SELECT LAST(m1) AS highest_m1 FROM tbl3 ORDER BY m1;

SELECT MAX(m1) FROM tbl3;

SELECT MIN(m1) FROM tbl3;

SELECT SUM(m1) FROM tbl3;

Scalar functions

Scalar functions operate against a single value, and return a single value based on the input value.

SQL Select Maximun/ Minimun value in table

Nth Maximun
select * from
(select row_number() over(order by sal desc) id, * from emp ) a
where a.id =2 //Where 2 is second Maximum

select * from tblStudent a where &n-1=(select count(*) from tblStudent b where a.mark lt b.mark)
(OR)
select * from tblStudent a where &n=(select count(*) from tblStudent b where a.mark<=b.mark)
Example
select * from tblStudent a where 2=(select count(*) from tblStudent b where a.mark<=b.mark)
//Where 2 is second Maximum

Nth Minimum
select * from
(select row_number() over(order by sal asc) id, * from emp ) a
where a.id =2 //Where 2 is second Minimum

select * from tblStudent a where &n-1=(select count(*) from tblStudent b where a.mark>b.mark)
(OR)
select * from tblStudent a where &n=(select count(*) from tblStudent b where a.mark>=b.mark)
Example
select * from tblStudent a where 2=(select count(*) from tblStudent b where a.mark>=b.mark)
//Where 2 is second Minimum

2nd Maximum
Select max(mark) From tblStudent where mark NOT IN (Select max(mark) From tblStudent)

Select top(1) From tblStudent where mark NOT IN (Select max(mark) From tblStudent) Order By mark desc

Select top(1) From tblStudent where mark IN (Select max(mark) From tblStudent where mark NOT IN (Select max(mark) From tblStudent))

SQL To get Serial Number for each record

select identity(int,1,1) as Serial_Numbers,Ename,Sal into #tmptable from Emp
select * from #tmptable
drop table #tmptable

SQL Truncate Vs Delete

1. Truncate will be most faster since it won't log more information while deleting the data.
2. We can't use WHERE clause in Truncate
3. Truncate statement will not fire the trigger.
4. While using truncate statement the table identity column counter reset to 0. But in the case of delete it will maintain the old counter value.
5. You can't use truncate if the table referenced by a foreign key constraint. 6. Both we can Rollback.

CREATE TABLE TruncateTest ( id int )
INSERT INTO TruncateTest VALUES(1)
SELECT * FROM TruncateTest

BEGIN TRANSACTION;
TRUNCATE TABLE TruncateTest
ROLLBACK TRANSACTION
SELECT * FROM TruncateTest

DROP TABLE TruncateTest

SQL Rename Command

SQL RENAME Command

The SQL RENAME command is used to change the name of the table or a database object.

If you change the object's name any reference to the old name will be affected.

You have to manually change the old name to the new name in every reference.

Syntax to RENAME a table:
RENAME old_table_name To new_table_name;
For Example:
To change the name of the table employee to my_employee, the query would be like
RENAME employee TO my_emloyee;

SQL Alter/ Modify Table

ALTER

/*ALTER TABLE table_name ADD column_name datatype*/

ALTER TABLE tbl1 ADD phone varchar(10);

/*ALTER TABLE table_name DROP COLUMN column_name*/

ALTER TABLE tbl1 DROP COLUMN phone;

ALTER TABLE tbl1 ADD phone varchar(10),mail varchar(4);

ALTER TABLE tbl1 DROP COLUMN phone,mail;

Modify a Table

To modify a table, you use an ALTER TABLE command.You
can use an ALTER TABLE command to add, modify, or drop (remove) columns or constraints.
An ALTER TABLE command has the following syntax

ALTER TABLE table_name predicate

where predicate can be any of the following:

• ADD COLUMN field type[(size)] [NOT NULL] [CONSTRAINT constraint]
• ADD CONSTRAINT multifield_constraint
• ALTER COLUMN field type[(size)]
• DROP COLUMN field
• DROP CONSTRAINT constraint

Example
ALTER TABLE table_name MODIFY column_name datatype;

For Example: To modify the column salary in the employee
table, the query would be like

ALTER TABLE employee MODIFY salary number(15,2);//this is error in sql server

(or)

ALTER TABLE employee ALTER COLUMN salary number(15,2);)//this is correct in sql server

SQL Indexes

Indexes are created on columns in tables or views.

The index provides a fast way to look up data based on the values within those columns.For example, if you create an index on the primary key and then search for a row of data based on one of the primary key values,SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.

CREATE INDEX index1 ON tbl1 (Fname); /*Simple Index*/

CREATE UNIQUE INDEX Uindex1 ON tbl1 (Fname);/*UNIQUE Index*/

CREATE INDEX index2 ON tbl1 (Fname DESC);

CREATE INDEX index3 ON tbl1 (Fname,city);

/*DROP INDEX table_name.index_name*/

DROP INDEX tbl.index1;

SQL Union/ Union All Query

UNION and UNION ALL
SELECT Fname FROM tbl1SELECT Fname FROM tbl2;
SELECT Fname FROM tbl1
UNION
SELECT Fname FROM tbl2;
SELECT Fname FROM tbl1 UNION ALL SELECT Fname FROM tbl2;

SQL Join Query

create table tbl2 (Fname varchar(10),address varchar(15));
insert into tbl2 values ('AFname','Aaddress');
insert into tbl2 values ('BFname','Baddress');
insert into tbl2 values ('AFname','AAaddress');
insert into tbl2 values ('DFname','DAaddress');

JOIN
SELECT tbl1.Fname,tbl1.age,tbl2.address FROM tbl1,tbl2;
SELECT tbl1.Fname,tbl1.age,tbl2.address FROM tbl1,tbl2 WHERE tbl1.Fname=tbl2.Fname; SELECT t1.Fname,t1.age,t2.address FROM tbl1 AS t1,tbl2 AS t2;
SELECT t1.Fname,t1.age,t2.address FROM tbl1 AS t1,tbl2 AS t2 WHERE t1.Fname=t2.Fname;

INNER JOIN
SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

The INNER JOIN returns all rows from both tables where there is a match.If there are rows in first table that do not have matches in second tables,those rows will not be listed.

SELECT tbl1.Fname,age,city,tbl2.address
FROM tbl1
INNER JOIN tbl2 ON tbl1.Fname=tbl2.Fname;
SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.Fname=tbl2.Fname;

LEFT JOIN
SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

The LEFT JOIN returns all the rows from the first table , even if there are no matches in the second table.If there are rows in first table that do not have matches in second table,those rows also will be listed.

select * from tbl1;
select * from tbl2;
SELECT tbl1.Fname,age,city,tbl2.address
FROM tbl1
LEFT JOIN tbl2 ON tbl1.Fname=tbl2.Fname;
SELECT * FROM tbl1 LEFT JOIN tbl2 ON tbl1.Fname=tbl2.Fname;

RIGHT JOIN
SELECT field1, field2, field3
FROM first_table
RIGHT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield
select * from tbl1;
select * from tbl2;
SELECT tbl1.Fname,age,city,tbl2.address
FROM tbl1
RIGHT JOIN tbl2 ON tbl1.Fname=tbl2.Fname;
SELECT * FROM tbl1 RIGHT JOIN tbl2 ON tbl1.Fname=tbl2.Fname;
SELECT * FROM tbl1 RIGHT JOIN tbl2 ON tbl1.Fname=tbl2.Fname WHERE tbl1.city='Acity';

SQL Help Query

Returning information about all objects. The following example lists information about each object in the master database. EXEC sp_help;

Returning information about a single object. The following example displays information about the Contact table. EXEC sp_help Contact;

The following example reports on the types of indexes on the Customer table.
EXEC sp_helpindex 'Customer';

EXEC sp_helplogins 'sa'

Who are the users for database sp_who2

For get Stored Procedure Query sp_helptext ProcedureName

Basic SQL Query

create database sample;
/*DROP DATABASE database_name;*/
DROP DATABASE sample;
use sample;
create table tbl1 (Fname varchar(10),age int,city varchar(5));
insert into tbl1 values ('AFname',20,'Acity');
insert into tbl1 values ('BFname',20,'Bcity');
insert into tbl1 values ('BFname',20,'Bcity');
INSERT INTO tbl1 (Fname, age) VALUES ('CFname', 67);
select * from tbl1;

truncate table tbl1;
drop table tbl1;

select * from tbl1;
select DISTINCT Fname,age,city from tbl1;

SELECT * FROM tbl1 WHERE Fname LIKE 'a%';/*Fname start with an 'a'*/
SELECT * FROM tbl1 WHERE Fname LIKE '%e';/*Fname end with an 'e'*/
SELECT * FROM tbl1 WHERE Fname LIKE '%bn%';/*Fnames that contain the pattern 'bn'*/

UPDATE
UPDATE tbl1 SET Fname = 'CCFname' WHERE Fname = 'CFname';
UPDATE tbl1 SET Fname = 'CFname',age=7,city='Ccity' WHERE Fname = 'CCFname';

DELETE
DELETE FROM tbl1 WHERE Fname = 'BFname';
DELETE FROM tbl1;
DELETE * FROM tbl1;--Incorrect syntax near '*'.

ORDER BY
SELECT * FROM tbl1 ORDER BY Fname;
SELECT * FROM tbl1 ORDER BY Fname ASC;
SELECT * FROM tbl1 ORDER BY Fname, age;
SELECT * FROM tbl1 ORDER BY Fname DESC;

AND/ OR
SELECT * FROM tbl1 WHERE Fname='AFname' AND age<21;>
SELECT * FROM tbl1 WHERE Fname='AFname' OR age<21;>

IN
SELECT * FROM tbl1 WHERE (Fname='AFname' AND age<21) city="'Ccity';">
SELECT * FROM tbl1 WHERE Fname NOT IN ('AFname','BFname');

BETWEEN
SELECT * FROM tbl1 WHERE Fname BETWEEN 'AFname' AND 'BFname';
SELECT * FROM tbl1 WHERE age BETWEEN 10 AND 20;
SELECT * FROM tbl1 WHERE age NOT BETWEEN 10 AND 20;

Alias Name
SELECT Fname AS Name FROM tbl1;//Column Fname Alias
SELECT Fname FROM tbl1 AS t1;//Table Fname Alias
SELECT t1.Fname,t1.age FROM tbl1 AS t1;