Date Formate in Sql

Show Current Date-

  Show Current Date Then Use-

 
  SELECT GETDATE()  AS CurrentDate
OutPut-
           2016-06-08 12:11:00.083

 More Format In Date Then Use-

 
Ex. 1:
 SELECT CONVERT(varchar(20),GETDATE(),100)  AS CurrentDate
OutPut-
           Jun  8 2016 12:13PM 

 Ex.2:
 SELECT CONVERT(varchar(20),GETDATE(),101)  AS CurrentDate
OutPut
       06/08/2016

Ex.3:
 SELECT CONVERT(varchar(20),GETDATE(),102)  AS CurrentDate
OutPut
      2016.06.08

Ex.4:
 SELECT CONVERT(varchar(20),GETDATE(),103)  AS CurrentDate 
OutPut
     08/06/2016

Ex.5 

• CONVERT(varchar(20),GETDATE(),104)  =  08.06.2016.
• CONVERT(varchar(20),GETDATE(),105 )=  08-06-2016
• CONVERT(varchar(20),GETDATE(),106) =  08 Jun 2016
• CONVERT(varchar(20),GETDATE(),107) =  Jun 08, 2016
• CONVERT(varchar(20),GETDATE(),108) =  12:24:08
• CONVERT(varchar(20),GETDATE(),109) =  Jun 08, 2016
• CONVERT(varchar(20),GETDATE(),110) =  06-08-2016
• CONVERT(varchar(20),GETDATE(),111) = 2016/06/08
• CONVERT(varchar(20),GETDATE(),112) = 20160608
• CONVERT(varchar(20),GETDATE(),113) = 08 Jun 2016 12:26:08

ONLY FATCH TIME IN DATE THEN USE

EX.1
 SELECT (LTRIM( RIGHT(CONVERT(varchar(20),GETDATE(),100),7))) as  CURRENTTime
 OutPut
     12:28PM

Read More

Except vs NotNull in Sql Server

EXCEPT & INTERSECT command in SQL Server

EXCEPT

In this Article EXCEPT command use to commind two queries and find dinctinct records. EXCEPT returns any left query values which are not exist in right query. You can use Except command and easily find distinct value of one table when you want filter values.

SYNTAX

The Syntax of EXCEPT command

SELECT ColumnName1,ColumnName2,....ColumnNameN FROM TableName1
EXCEPT
SELECT ColumnName1,ColumnName2,....ColumnNameN FROM TableName2

Note - You Should know about same number of column in all Queries Example of Except Command in SQL server you can more clear in example

Example

. SELECT * from Table1
EXCEPT
select * from Table2

Note -You can write this query and execute (f5) after that You got result 'name which is ditinct and not match in right table.'
Result - You can see output record.
Image

Intersect

In this Article Intersect command use to commind two queries and find common records. You can use Intersect command and easily find common values.

SYNTAX

The Syntax of Intersect command

SELECT ColumnName1,ColumnName2,....ColumnNameN FROM TableName1
Intersect
SELECT ColumnName1,ColumnName2,....ColumnNameN FROM TableName2

Note - You Should know about same number of column in all Queries Example of Intersect Command in SQL server you can more clear in example

Example

SELECT * from Table1
INTERSECT
select * from Table2

Note -You can write this query and execute (f5) after that You find result 'name which is common in both table.'
Result - You can see output record.
Image

What is a NULL Value in Sql?

In this tutorial we describe that NULL value is a field with does not have any value. You can Insrt a new record and update without any value then field have 'no value' and field will be saved with a NULL value
Note - NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

What is Not NULL Value in Sql?

In this tutorial we describe that Not NULL value is a field which have value ,doesn't null value.

Difference between Except vs Not In

Except

EXCEPT filters the DISTINCT values from the left-hand table that do not appear in the right-hand table. It's essentially the same as doing a NOT EXISTS with a DISTINCT clause.
It also expects the two tables (or subset of columns from the tables) to have the same number of columns in the left and right hand side of the query

NOT IN

NOT IN does not filter for DISTINCT values and returns all values from the left-hand table that do not appear in the right-hand table.
NOT IN requires you compare a single column from one table with a single column from another table or subquery.
For example, if your subquery was to return multiple columns:

SELECT * FROM TableA AS nc WHERE ID NOT IN (SELECT ID, Name FROM TableB AS ec)

Read More

Sql Commands

Sql Select Query

SQL Select all data from Table as you can see in my previous Article
http://learningmvctutorial.blogspot.in/2017/03/sql-select.html

Insert Query

In SQL Server you can use Insert data in table help of Insert Query

Syntax

INSERT INTO TableName(ColumnName1,ColumnName2,ColumnName3) VALUES(@value1,@value2,@value3)

Note- In this Insert Query those Column you want to insert in table then define column name and give there values according field data type.
This is Syntax and you can see example so more clear about this query

Example

INSERT INTO Tbl_Employee(Name, Salary,City) VALUES ('Radhe',40000,'Jaipur')

Note -You can write this query and Run f5 then Show message "(1 row(s) affected)"
Result - You can run select query and see output record.

Update Query

In SQL Server you can use update record in table help of Update Query

Syntax

UPDATE TableName SET ColumnName1=@value1,ColumnName2=@value2 WHERE ColumnName Condition OPERATORE

Note- In this Update Query those existing Column you want to update in the table then define column name and set values according field data type if you want to give where condition then give if you not give where condition then reflect all this full of table.
This is Syntax and you can see example so more clear about this query

Example

UPDATE Tbl_Employee SET Name = 'Sita', Salary=12000 WHERE Id=4

Note -You can write this query and Run f5 then Show message "(1 row(s) affected)"
Result - You can run select query and see output record.

Delete Query

In SQL Server Delete query is used to delete record in SQL server.You can see example

Syntax

DELETE FROM TableName WHERE ColumnName Condition operator

Note- In this Delete Query those record you want to delete from table then use this delete query if you want to delete record according condition then use 'where Clause' and use which record you want to delete use this condition , if you not give where condition then reflect all this full of table.
This is Syntax and you can see example so more clear about this query

Example

DELETE FROM Tbl_Employee SET Name = 'Sita', Salary=12000 WHERE Id=4

Note -You can write this query and Run f5 then Show message "(1 row(s) affected)"
Result - You can run select query and see output record.

SQL In Operator

In SQL Server In Operator use to multiple value use in where clause.You  can use In operator and apply more than 1 values in where clause and get result according match  values you can see In operator example

Syntax

SELECT ColumnName FROM TableName WHERE ColumnName IN (value1,value2,...)

Note- Use this syntax find easily match record using In operator, Basically use In Sytax and give vales in where clause whatever you want to match.
This is Syntax and you can see example so more clear about this query

Example

SELECT Name FROM Tbl_Employee WHERE City IN ('Chittorgarh','Jaipur')

Note - You can write this query and Run f5 then You find result employee name those belongs city is Chittorgarh and Jaipur"
Result - You can run select query and see output record.

SQL Between Operator

In SQL Server Between Operator is used to select values within a range Commonly use for rang. You can see example

Syntax

SELECT ColumnName FROM TableName WHERE ColumnName Between value1 and value2

Note- Use this Syntax find record within range using Between operator, Basically use In syntax and give vales in where clause whatever you want to define rang.
This is Syntax and you can see example so more clear about this query

Example

SELECT Name FROM Tbl_Employee WHERE Salary Between 1000 and 30000

Note -You can write this query and Run f5 then You find result employee name those Salary between 1000 to 30000"
Result - You can run select query and see output record.

ALTER  Commands
The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table.

You would also use ALTER TABLE command to add and drop various constraints on a an existing table.
Add new column in existing table through Alter Command

ALTER TABLE table_name ADD column_name datatype;

Drop column in existing table through Alter Command

ALTER TABLE table_name DROP COLUMN column_name;

Change The Data Type of  column in existing table

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Add  NOT NULL constraint to a column

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

Read More

INSERT INTO statement in SQL

INSERT INTO SELECT Statement

In this article describe about Insert data one table to existing table The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table.

Example

In this article Firstly create 2 tables like - tbl_1,tbl_2 and insert some values in tbl_1 Using 'INSERT INTO' Statement data Insert into tbl_2 table , you will find all records in tbl_2 table

Create Table

Create table tbl_1
(
Id int,
name nvarchar(10)
)

Note -

Table tbl_1 created with two field and same for tbl_2 with same field and datatype, name may be different but datatype same

Above Example Created Table tbl_1 after that Use Insert into Statement

Syntex

Note -

INSERT INTO [TableName]
SELECT * FROM [TableName]

You also see example then you can better understand

Example

INSERT INTO tbl_2
SELECT * FROM tbl_1

Note -

In this example Tbl_2 inserted all record which are in tbl_1, You can see above using select statement
SELECT * FROM tbl_2

SELECT INTO STATEMENT

you can want to make replica of table then use select into
select * into tbl_2 from tbl_1 In this no need create table ,following example we can't create table tbl_2 only create tbl_1 and just make replica so table and data same copy in replica of table this is explain in my previous article

Read More

Sql Like Operator

The SQL LIKE Operator

In this Article describe Like Operator. The SQL LIKE clause is used to compare a value to similar values using wildcard operators. The LIKE operator is used to search for a specified pattern 'start letter and end letter and Contain letter/words' in a column. There are two wildcards used in LIKE operator.
1.(%) Percent Sign.
2.(_) Under Score Sign.
Percent sign (%) - This sign represents zero, one or multiple characters. Under Score ( _ ) - This sign represents a single number or character And use in Combination.

Syntax

SELECT ColumnName FROM TableName WHERE ColumnName LIKE pattern

We use like operator many types in Sql query.-

1.Start with letter

SELECT * FROM Tbl_Employee WHERE name LIKE 'M%'

Note - In this example those name start with 'M' letter, You can get result using like Operator

2. End with letter

Example-
SELECT * FROM Tbl_Employee WHERE name LIKE '%M'
Note - In this example those name End with 'M' letter, You can get All result those name end with 'M' letter using like Operator

3.Contain letter in word

Example-
SELECT * FROM Tbl_Employee WHERE name LIKE '%M%'
in any position. Note - In this example those name contain 'M' letter in word at any position, You can get All result those name contain 'M' letter using like Operator

4.Contain in letter in word

Example-
SELECT * FROM Tbl_Employee WHERE name LIKE '%M%'
Note - In this example those name End with 'M' letter, You can get All result thoses name end with 'M' letter using like Operator

SQL _ Wildcard

1. Start letter with ( _ )

Example-
SELECT * FROM Tbl_Employee WHERE name LIKE '_e'
Note - In this example those name start with '_' means any charter but second place character is e , You can get All result those name have second position contain 'e' .

2. End letter with (_)

Example-
SELECT * FROM Tbl_Employee WHERE name LIKE 'ra_'
Notes- In this example use like operator with _ Wildcard search result with start with ra and end with any letter.

3. Middle word have wildcard

Example-
SELECT * FROM Tbl_Employee WHERE name LIKE 'J_c_'
Notes-In this example use like operator with _ Wildcard. The following SQL statement selects all Employee with a City starting with "J", followed by any character, followed by "c", followed by any character. You can get this type of result easily.

SQL [charlist] Wildcard

This type of pattern use where you want to fetch some records those name start with some specific character

Example-
SELECT * FROM Tbl_Employee WHERE name LIKE '[rjm]%';
Notes - In this example use like operator with charlist. The following SQL statement selects those Employee who's name starting with "r","j" & "m"apply this pattern you can find output easily.

The following SQL statement selects all employee's name start with "r", "j", or "m"
You can use different type of like see above example -

SELECT * FROM Tbl_Employee WHERE name LIKE '[a-z]%'; Note - In this example select all employee those name consist and start with 'a' to 'z' range you can get all record. if you want the you change rang also like you give '[a-m]%' use this pattern then only find within range all records.

You can use same pattern not start with character in this following example see above-

SELECT * FROM Tbl_Employee WHERE name LIKE '[!rjm]%'; Note - In this example select all employee those name not start with "r", "j", or "m" .Using this pattern you can get all records.

Read More

Html Formatting

Text Editing

You can edit text easly in html using many predefine tags. HTML also defines special elements and tags for defining text with a special meaning. Using those type of Formatting elements like-

•  <b> - Use this element to bold text
• <strong> - This is also same bold tag but give Important text
• <i> - Use this for Italic text 
• <em> - This is use for Emphasized text
• <mark> - This is use Marked text
• <small> - This is use Small text
• <del> - This is use Deleted text
• <ins> - This is use Inserted text
• <sub> - This is use Subscript text
• <sup> - This is use Superscript text

Html <b> Element Example -

Example

<b> This is example of bold tag < /b>

Note -you can write any text within <b> tag
Result - This is example of bold tag

Html <strong> Element  Example-

Example

<strong> This is example of strong tag < /strong>

Note -you can write any text within <strong> tag
Result - This is example of strong tag

Html <i> Element Example

Example

<i> This is example of Italic tag < /i>

Note -you can write any text within <i> tag
Result - This is example of Italic tag

Html <em> Element Example

Example

<em> This is example of Italic tag < /em>

Note -The HTML <em> element defines emphasized text, with added semantic importance. <i> tag
Result - This is example of emphasized tag

Html <small> Element Example

Example

<small> This is example of small tag < /small>

Note -The HTML <small> element defines smaller text
Result - This is example of small element

Html <mark> Element Example

Example

<mark> This is example of mark tag < /mark>

Note -The HTML <mark> element defines marked or highlighted text
Result - This is example of mark element

Html <del> Element Example

Example

<del> This is example of del tag < /del>

Note -The HTML <del> element defines deleted (removed) text
Result -
This is example of del element

Html <ins>Element Example

Example

<ins> This is example of ins tag < /ins>

Note -The HTML <ins> element defines inserted (added) text.
Result -
This is example of ins element

Html <sub> Element Example

Example

<sub> This is example of sub tag < /sub>

Note -The HTML <sub> element represent subscripted text.
Result - This is example of _{sub element}

Html <sup>Element Example

Example

<sup> This is example of sup tag < /sup>

Note -The HTML <sup> element represent superscripted text.
Result - This is example of ^{superscripted text}

Read More

SQL Group By Clause

What is Group By clause ?

In this Article Group by clause use in Select query in Sql Server when you fetch data with collection on basis particular unique field then use Group by Clause. Group by clause with where condition and order by clause.

The GROUP BY statement is used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

Syntex

SELECT ColumnName FROM TableName WHERE condition GROUP BY ColumnName ORDER BY ColumnName

Example

SELECT COUNT(Id) as Counts,City FROM Tbl_Employee GROUP BY City
Create table Tbl_Employee with some field and group by city.
if you run query with GROUP BY Clause then you find group wise city records.
In case if you use Group By Clause then always use aggregate functions.If you not use aggregate function then face error.
You can see in example

Select All records in image- image
Group by clause on city -

The HAVING Clause

The HAVING clause use in SQL because the WHERE keyword could not be used with aggregate functions.

Syntex

SELECT ColumnName, Aggregate Function(ColumnName) FROM TableName WHERE ColumnName operator value GROUP BY ColumnName HAVING Aggregate Function(ColumnName) operator value

SELECT COUNT(Salary) AS Counts,Salary,City FROM Tbl_Employee GROUP BY City ,Salary HAVING Salary > 25000
Note - You should aware having condition apply then this field must be in aggregate function and group by clause

Read More