Create a Comma-Separated List using a SQL Query?

CREATE TABLE #test

(

                ID INT IDENTITY,

                NAME VARCHAR(50)

)

INSERT INTO #test(NAME) VALUES ('Ram')

INSERT INTO #test(NAME) VALUES ('Shyam')

INSERT INTO #test(NAME) VALUES ('Ghanshyam')

INSERT INTO #test(NAME) VALUES ('Krishna')

SELECT * FROM #test

ID	NAME
1	Ram
2	Shyam
3	Ghanshyam
4	Krishna

SELECT STUFF((SELECT ',' + NAME FROM #test t FOR XML PATH('')),1,1,' ') AS 'OUTPUT'

OUTPUT

Ram,Shyam,Ghanshyam,Krishna

OR

DECLARE @NameList varchar(MAX)

SELECT @NameList = COALESCE(@NameList + ',', '') +

   CAST(NAME AS VARCHAR(MAX))

FROM #test

SELECT @NameList AS 'OUTPUT'

OUTPUT

Ram,Shyam,Ghanshyam,Krishna

NOTE:-

STUFF is combination of

STUFF = SUBSTRING + REPLACE

For more info on COALESCE visit link

How to check time is between two times in c sharp?

TimeSpan startTime = new TimeSpan(9, 0, 0);

TimeSpan endTime = new TimeSpan(18, 0, 0);

TimeSpan now = DateTime.Now.TimeOfDay;

//TimeSpan now = new TimeSpan(0, 0, 0);

if (now >= startTime || end <= endTime)

{

            // Your code here..

}

Note:-

Time is calculate in 24-hour format

SQL Tutorial Tips

DDL (Data Definition Language) - To create and manage database objects.

CREATE- Create used to create a new database object, such as table.

ALTER- Alter used to modify the database objects.

DROP- Drop used to delete the object.

DML (Data Manipulation Language) - To store and manage data in database objects.

INSERT- Insert used to insert a new data record in a table.

UPDATE- Update used to modify an existing record in a table.

DELETE- Delete used to delete a record from a table.

DCL (Data Control Language) - To allow or deny access to database objects.

GRANT- Grant used to assign permission to user to access a database objects.

REVOKE- Revoke used to deny permission to user to access a database objects.

DQL (Data Query Language) - To query data from the database objects.

SELECT- Select used to select data from the database in different ways and formats.

Let's Start............

Create table #Test

(

       Column_Name1 int,

       Column_Name2 varchar(50),

       Column_Name3 varchar(50)

)

INSERT INTO #Test VALUES ('1','Ram','IT')

INSERT INTO #Test VALUES ('2','Shyam','Accountant')

INSERT INTO #Test VALUES ('3','Ghanshyam','Operation')

SELECT * FROM #Test

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT
2	Shyam	Accountant
3	Ghanshyam	Operation

Retrieving Specific columns

SELECT Column_Name1, Column_Name2, Column_Name3 FROM #Test

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT
2	Shyam	Accountant
3	Ghanshyam	Operation

Retrieving all columns

SELECT * FROM #Test

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT
2	Shyam	Accountant
3	Ghanshyam	Operation

Customizing the Display

SELECT 'ID' = Column_Name1, 'NAME' = Column_Name2, 'Department' = Column_Name3 

FROM #Test

OR

SELECT Column_Name1 'ID', Column_Name2 'NAME', Column_Name3 'Department'

FROM #Test

OR

SELECT Column_Name1 AS 'ID', Column_Name2 AS 'NAME', Column_Name3 AS 'Department'

FROM #Test

ID	NAME	Department
1	Ram	IT
2	Shyam	Accountant
3	Ghanshyam	Operation

Concatenating the Text Values in the Output

SELECT Column_Name2 + 'works in ' + Column_Name3 + ' Department' FROM #Test

(No column name)

Ramworks in IT Department

Shyamworks in Accountant Department

Ghanshyamworks in Operation Department

Concatenating Column Values

SELECT CAST (Column_Name1 AS VARCHAR (50)) +' '+ Column_Name2 +' '+ Column_Name3

FROM #Test

(No column name)

1 Ram IT

2 Shyam Accountant

3 Ghanshyam Operation

Retrieving Selected Rows

SELECT * FROM #Test WHERE Column_Name1 = 1

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT

Using Comparison Operators to Specify Condition

SELECT * FROM #Test WHERE Column_Name1 = 1

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT

Comparison Operators

Operator	Description
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than equal to
<>	Not Equal to
!=	Not Equal to
!<	Not less than
!>	Not greater  than

Retrieving Records That Match One or More Condition

OR- Retrieve a true value when at least one condition is satisfied.

SELECT * FROM #Test WHERE Column_Name1 = 1 OR Column_Name2 = 'Sachin'

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT

AND- Retrieve a true value when both the conditions are satisfied.

SELECT * FROM #Test WHERE Column_Name1 = 1 AND Column_Name2 = 'Ram'

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT

Retrieving Records That Contain Values in a Given Range

BETWEEN- Retrieve records in given range.

SELECT * FROM #Test WHERE Column_Name1 BETWEEN 1 AND 2

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT
2	Shyam	Accountant

NOT BETWEEN- Retrieve records not in given range.

SELECT * FROM #Test WHERE Column_Name1 NOT BETWEEN 1 AND 2

Column_Name1	Column_Name2	Column_Name3
3	Ghanshyam	Operation

Retrieving Records That Contain Any Values from a Given Set of Values

IN- Retrieve records in given set of values range.

SELECT * FROM #Test WHERE Column_Name1 IN (1, 2)

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT
2	Shyam	Accountant

NOT IN- Retrieve records not in given set of values range.

SELECT * FROM #Test WHERE Column_Name1 NOT IN (1, 2)

Column_Name1	Column_Name2	Column_Name3
3	Ghanshyam	Operation

Retrieving Records That Match a Pattern

LIKE- The LIKE keyword is used to search a string by using wildcards.

--Begin with 'Ram'

SELECT * FROM #Test WHERE Column_Name2 LIKE 'Ram%'

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT

--Ending with 'Shyam'

SELECT * FROM #Test WHERE Column_Name2 LIKE '%Shyam'

Column_Name1	Column_Name2	Column_Name3
2	Shyam	Accountant
3	Ghanshyam	Operation

--Containing 'Ghan' in records

SELECT * FROM #Test WHERE Column_Name2 LIKE '%Ghan%'

Column_Name1	Column_Name2	Column_Name3
3	Ghanshyam	Operation

--Search string length 3 and (_) Position character can be anything

SELECT * FROM #Test WHERE Column_Name2 LIKE 'Ra_'

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT

Wildcard characters

Wildcard	Description
%	Represents any string of zero or more character(s)
_	Represents a single character
[]	Represents any single character within the specified range
[^]	Represents any single character not within the specified range

Retrieving Records That Contain NULL Values

IS NULL- Retrieve records that contain null values.

SELECT * FROM #Test WHERE Column_Name1 IS NULL

IS NOT NULL- Retrieve records that do not contain null values.

SELECT * FROM #Test WHERE Column_Name1 IS NOT NULL

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT
2	Shyam	Accountant
3	Ghanshyam	Operation

Retrieving Records to be Displayed in a Sequence

ASC- Sorting records in ascending order. 

SELECT * FROM #Test ORDER BY Column_Name1 ASC

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT
2	Shyam	Accountant
3	Ghanshyam	Operation

DESC- Sorting records in descending order. 

SELECT * FROM #Test ORDER BY Column_Name1 DESC

Column_Name1	Column_Name2	Column_Name3
3	Ghanshyam	Operation
2	Shyam	Accountant
1	Ram	IT

Note:-

If you do not specify the ASC or DESC keywords with the column name in the ORDER BY clause, the records are sorted in the ascending order.

Retrieving Records from the Top of a Table

SELECT TOP 1 * FROM #Test ORDER BY Column_Name1 ASC

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT

SELECT TOP 1 * FROM #Test ORDER BY Column_Name1 DESC

Column_Name1	Column_Name2	Column_Name3
3	Ghanshyam	Operation

Retrieving Records without Duplication Values

DISTINCT- Eliminate rows with duplicate values in a column.

SELECT DISTINCT Column_Name2 FROM #Test

Column_Name2

Ram

Shyam

Ghanshyam

SELECT DISTINCT * FROM #Test

Column_Name1	Column_Name2	Column_Name3
1	Ram	IT
2	Shyam	Accountant
3	Ghanshyam	Operation

Join

Inner Join

Outer Join

Left Outer Join

Right Outer Join

Full Outer Join

Cross Join

Self Join

CREATE TABLE #table1

(

       ID INT,

       Name VARCHAR(10)

)

INSERT INTO #table1 VALUES (1,'One')

INSERT INTO #table1 VALUES (2,'Two')

INSERT INTO #table1 VALUES (3,'Three')

INSERT INTO #table1 VALUES (4,'Four')

INSERT INTO #table1 VALUES (5,'Five')

CREATE TABLE #table2

(

       ID INT,

       Name VARCHAR(10)

)

INSERT INTO #table2 VALUES (1,'One')

INSERT INTO #table2 VALUES (2,'Two')

INSERT INTO #table2 VALUES (3,'Three')

INSERT INTO #table2 VALUES (6,'Six')

INSERT INTO #table2 VALUES (7,'Seven')

INSERT INTO #table2 VALUES (8,'Eight')

SELECT * FROM #table1

ID	Name
1	One
2	Two
3	Three
4	Four
5	Five

SELECT * FROM #table2

ID	Name
1	One
2	Two
3	Three
6	Six
7	Seven
8	Eight

INNER JOIN- Returns only matches rows from both the table.

SELECT t1.*, t2.*

FROM #table1 t1

INNER JOIN #table2 t2 ON t1.ID = t2.ID

ID	Name	ID	Name
1	One	1	One
2	Two	2	Two
3	Three	3	Three

LEFT JOIN- Returns all rows from the table specified on the left side of the LEFT JOIN keyword and matching rows from table specified on the right side and non-matching rows displayed  NULL in the column that get data from the table specified on the right side.

SELECT t1.*, t2.*

FROM #table1 t1

LEFT JOIN #table2 t2 ON t1.ID = t2.ID

ID	Name	ID	Name
1	One	1	One
2	Two	2	Two
3	Three	3	Three
4	Four	NULL	NULL
5	Five	NULL	NULL

RIGHT  JOIN- Returns all rows from the table specified on the right side of the RIGHT JOIN keyword and matching rows from table specified on the left  side and non-matching rows displayed  NULL in the column that get data from the table specified on the left side.

SELECT t1.*, t2.*

FROM #table1 t1

RIGHT JOIN #table2 t2 ON t1.ID = t2.ID

ID	Name	ID	Name
1	One	1	One
2	Two	2	Two
3	Three	3	Three
NULL	NULL	6	Six
NULL	NULL	7	Seven
NULL	NULL	8	Eight

FULL OUTER JOIN- Returns all the matching and non-matching rows from both the table. Non- matching rows displayed NULL in the column for which data is not available. 

SELECT t1.*, t2.*

FROM #table1 t1

FULL OUTER JOIN #table2 t2 ON t1.ID = t2.ID

ID	Name	ID	Name
1	One	1	One
2	Two	2	Two
3	Three	3	Three
4	Four	NULL	NULL
5	Five	NULL	NULL
NULL	NULL	6	Six
NULL	NULL	7	Seven
NULL	NULL	8	Eight

Note:-

OUTER keyword is optional for LEFT and RIGHT joins.

CROSS JOIN- Two tables join each row from one table with each row of the other table. First table each rows multiplied by the number of rows in the second table. 

SELECT t1.*, t2.*

FROM #table1 t1

CROSS JOIN #table2 t2

ID	Name	ID	Name
1	One	1	One
1	One	2	Two
1	One	3	Three
1	One	6	Six
1	One	7	Seven
1	One	8	Eight
2	Two	1	One
2	Two	2	Two
2	Two	3	Three
2	Two	6	Six
2	Two	7	Seven
2	Two	8	Eight
3	Three	1	One
3	Three	2	Two
3	Three	3	Three
3	Three	6	Six
3	Three	7	Seven
3	Three	8	Eight
4	Four	1	One
4	Four	2	Two
4	Four	3	Three
4	Four	6	Six
4	Four	7	Seven
4	Four	8	Eight
5	Five	1	One
5	Five	2	Two
5	Five	3	Three
5	Five	6	Six
5	Five	7	Seven
5	Five	8	Eight

SELF JOIN- Table is joined with itself.

CREATE TABLE #Employee

(

       EmployeeID INT,

       EmployeeName VARCHAR(50),

       ManagerID INT

)

INSERT INTO #Employee VALUES (1,'Ram',4)

INSERT INTO #Employee VALUES (2,'Shyam',1)

INSERT INTO #Employee VALUES (3,'Ghanshyam',2)

INSERT INTO #Employee VALUES (4,'Krishana',3)

SELECT * FROM #Employee

EmployeeID	EmployeeName	ManagerID
1	Ram	4
2	Shyam	1
3	Ghanshyam	2
4	Krishana	3

SELECT a.EmployeeID, a.EmployeeName,a.ManagerID, b.EmployeeName 'ManagerName'  

FROM #Employee a

JOIN #Employee b ON a.ManagerID = b.EmployeeID

EmployeeID	EmployeeName	ManagerID	ManagerName
1	Ram	4	Krishana
2	Shyam	1	Ram
3	Ghanshyam	2	Shyam
4	Krishana	3	Ghanshyam

Sub queries

A sub query is an SQL statement that is used within another SQL statement.

CREATE TABLE #table1

(

       ID INT,

       Name VARCHAR(10)

)

INSERT INTO #table1 VALUES (1,'One')

INSERT INTO #table1 VALUES (2,'Two')

INSERT INTO #table1 VALUES (3,'Three')

INSERT INTO #table1 VALUES (4,'Four')

INSERT INTO #table1 VALUES (5,'Five')

CREATE TABLE #table2

(

       ID INT,

       Name VARCHAR(10)

)

INSERT INTO #table2 VALUES (1,'One')

INSERT INTO #table2 VALUES (2,'Two')

INSERT INTO #table2 VALUES (3,'Three')

INSERT INTO #table2 VALUES (6,'Six')

INSERT INTO #table2 VALUES (7,'Seven')

INSERT INTO #table2 VALUES (8,'Eight')

SELECT * FROM #table1

ID	Name
1	One
2	Two
3	Three
4	Four
5	Five

SELECT * FROM #table2

ID	Name
1	One
2	Two
3	Three
6	Six
7	Seven
8	Eight

IN / NOT IN- Used when sub- query returns more than one values.

SELECT * FROM #table1 WHERE Name IN (SELECT Name FROM #table2)

ID	Name
1	One
2	Two
3	Three

SELECT * FROM #table1 WHERE Name NOT IN (SELECT Name FROM #table2)

ID	Name
4	Four
5	Five

EXISTS / NOT EXISTS- Used to check sub- query records exists or not.

SELECT * FROM #table1 WHERE EXISTS (SELECT * FROM #table2)

ID	Name
1	One
2	Two
3	Three
4	Four
5	Five

SELECT * FROM #table1 WHERE NOT EXISTS (SELECT * FROM #table2)

Note:- 

1. ORDER BY and COMPUTE BY clause cannot use in sub query.

2. The EXISTS keyword is used to check the existence of rows in the result set of an inner query according to the condition specified in the inner query.

Nested Sub queries

A nested sub query can contain one or more sub queries. Sub queries are used when the condition of a query is dependent on the results another query. 

Example:-

Outer query results depend on Level 1 inner query results and Level 1 inner query results depend on Level 2 inner query results.

Syntax:-

SELECT * FROM table_name

WHERE column = /*Level 1 inner query*/

(SELECT column FROM table_name

WHERE column = /*Level 2 inner query*/

(SELECT column FROM table_name WHERE ID=1)

)

SELECT * FROM #table1 WHERE Name = (SELECT Name FROM #table2 WHERE ID=1)

ID	Name
1	One

Correlated Sub queries

Correlated Sub query is a sub-query that uses values from the outer query. In this case the inner query has to be executed for every row of outer query.

SELECT * FROM #table1 WHERE Name = (SELECT Name FROM #table2 WHERE #table1.Name=#table2.Name)

ID	Name
1	One
2	Two
3	Three

User Defined Database

CREATE DATABASE- keyword is used to create a database.

Syntax:- 

CREATE DATABASE database_name

RENAME DATABASE- SP_RENAMEDB keyword is used to rename/modify a database.

Syntax:- 

SP_RENAMEDB old_database_name, new_database_name

Note:-

You cannot rename currently using database.

DROP DATABASE- keyword is used to delete a database.

Syntax:- 

DROP DATABASE database_name

Note:-

You cannot drop currently using database.

Table

CREATE TABLE- keyword is used to create a table.

Create table table_name

(

       Column_Name1 int,

       Column_Name2 varchar(50),

       Column_Name3 varchar(50)

)

MODIFY TABLE- ALTER keyword is used to modify a table.

Alter table table_name

(

       Column_Name1 int,

       Column_Name2 varchar(50),

       Column_Name3 varchar(50)

)

RENAME TABLE- SP_RENAME keyword is used to rename/modify a table.

Syntax:- 

SP_RENAME old_table_name, new_table_name

DROP TABLE- keyword is used to delete a table.

Syntax:- 

DROP TABLE table_name

ADD COLUMN- ALTER TABLE – ADD keyword is used to add a column in table.

Syntax:- 

ALTER TABLE table_name ADD Column_Name4 varchar(50)

RENAME COLUMN- SP_RENAME keyword is used to rename/modify a table column.

Syntax:-

SP_RENAME table_name.old_column_name, new_column_name

DROP COLUMN- ALTER TABLE – DROP COLUMN keyword is used to delete a table column.

Syntax:-

ALTER TABLE table_name DROP COLUMN column_name

CHANGE COLUMN DATA TYPE- ALTER TABLE – ALTER COLUMN keyword is used to change column data type.

Syntax:-

ALTER TABLE table_name ALTER COLUMN Column_Name4 varchar(100)

Table Constraints

Primary key constraint

Unique constraint

Foreign key constraint

Check constraint

Default constraint

PRIMARY KEY CONSTRAINT- A primary key constraint is defined on a column or a set of column whose values uniquely identify all the rows in a table. A primary key column can not contain NULL values.

Create table #Test

(

       Column_Name1 int CONSTRAINT constraint_name PRIMARY KEY

)

INSERT INTO #Test VALUES ('1') --OK

INSERT INTO #Test VALUES ('1') –-Error

Msg 2627, Level 14, State 1, Line 93

Violation of PRIMARY KEY constraint 'constraint_name'. Cannot insert duplicate key in object 'dbo.#Test'. The duplicate key value is (1).

The statement has been terminated.

UNIQUE CONSTRAINT- The unique constraint is used to enforce uniqueness on non-primary key column. A unique constraint column allow one NULL values.

Create table #Test

(

       Column_Name1 int CONSTRAINT constraint_name UNIQUE

)

INSERT INTO #Test VALUES ('1') --OK

INSERT INTO #Test VALUES ('1') –-Error

Msg 2627, Level 14, State 1, Line 93

Violation of UNIQUE KEY constraint 'constraint_name'. Cannot insert duplicate key in object 'dbo.#Test'. The duplicate key value is (1).

The statement has been terminated.

FOREIGN KEY CONSTRAINT- A foreign key constraint is used to remove inconsistency in two tables when the data in one table depends on the data in another table.

Create table table1

(

       Column_Name1 int CONSTRAINT constraint_name PRIMARY KEY

)

INSERT INTO table1 VALUES ('1')

Create table table2

(

       Column_Name1 int CONSTRAINT constraint_name FOREIGN KEY(Column_Name1) REFERENCES

table1(Column_Name1)

)

INSERT INTO table2 VALUES ('1') --OK

INSERT INTO table2 VALUES ('2') –-Error

Msg 547, Level 16, State 0, Line 102

The INSERT statement conflicted with the FOREIGN KEY constraint "constraint_name1". The conflict occurred in database "master", table "dbo.table1", column 'Column_Name1'.

The statement has been terminated.

CHECK CONSTRAINT- A check constraint is used to limit the value to be inserted in column. You can define multiple check constraint on single column.

Create table table1

(

       Column_Name1 varchar(3) CONSTRAINT constraint_name CHECK(Column_Name1 IN ('MON','TUE','WED','THU','FRI','SAT'))

)

INSERT INTO table1 VALUES ('MON') --OK

INSERT INTO table1 VALUES ('SUN') --Error

Msg 547, Level 16, State 0, Line 96

The INSERT statement conflicted with the CHECK constraint "constraint_name". The conflict occurred in database "master", table "dbo.table1", column 'Column_Name1'.

The statement has been terminated.

Note:-

A check constraint can be specified by using the following elements, such as arithmetic operator (+, -, /, *, %), comparison operator (=, >, <,>=, <=, <>, !=) or keywords, such as IN, LIKE and BETWEEN.

DEFAULT CONSTRAINT- A default constraint is used assign a default value to column when user not insert values for such a column.

Create table table1

(

       Column_Name1 varchar(3),

       Column_Name2 varchar(3) CONSTRAINT constraint_name DEFAULT('SUN')

)

INSERT INTO table1 (Column_Name1) VALUES ('MON')

SELECT * FROM table1

Column_Name1	Column_Name2
MON	SUN

MANIPULATING DATA IN A TABLES

INSERT ROWS

Create table #Test

(

       Column_Name1 int NOT NULL,

       Column_Name2 varchar(50) NOT NULL,

       Column_Name3 varchar(50) NULL

)

INSERT INTO #Test VALUES ('1','Ram','IT')

OR

INSERT INTO #Test (Column_Name1, Column_Name2, Column_Name3) VALUES ('1','Ram','IT')

OR

INSERT INTO #Test (Column_Name1, Column_Name3, Column_Name2) VALUES ('1','IT','Ram')

OR

INSERT INTO #Test VALUES ('1','Ram', NULL)

INSERT PARTIAL DATA

INSERT INTO #Test VALUES ('1','Ram', NULL)

OR

INSERT INTO #Test (Column_Name1, Column_Name2) VALUES ('1','Ram')

COPY DATA FROM AN EXISTING TABLE INTO A NEW TABLE

SELECT * INTO #new_table_name

FROM #Test --old table name

OR

SELECT * INTO #new_table_name

FROM #Test --old table name

WHERE Column_Name1 = 1

UPDAT DATA IN A TABLE

UPDATE #Test

SET Column_Name3 = 'IT'

OR

UPDATE #Test

SET Column_Name3 = 'IT'

WHERE Column_Name1 = '1'

Note:-

If you don’t specify the where condition its update all the rows.

DELETE DATA FROM A TABLE

DELETE FROM #Test

OR

DELETE FROM #Test WHERE Column_Name1 = '1' 

Note:-

If you don’t specify the where condition its delete all records.

DELETE ALL THE RECORDS FROM A TABLE

TRUNCATE TABLE #Test

Note:-

Truncate table does not support WHERE clause.

Views

A view is a virtual table. Just like a real table, the view contains columns and rows. The view contains those fields are defined by a query.

A view can be used for show only specific column based on user permissions instead of direct access the base tables.

--CREATE VIEW

CREATE VIEW view_name

AS

SELECT column_name1,column_name2,column_name3,....

FROM table_name

SELECT * FROM view_name

--ALTER VIEW

ALTER VIEW view_name

AS

SELECT column_name1,column_name2,....

FROM table_name

SELECT * FROM view_name

--DROP VIEW

DROP VIEW view_name

Note:-

Update view is possible only in that case

1. SELECT statement not contains DISTINCT keyword.

2. SELECT statement not contains Aggregate function.

3. FROM not contains multiple tables.

For More Info visit Microsoft