SQL Alias

You can give a table or a column another name by using an alias

Syntax 


SELECT column_name AS alias_name
FROM table_name AS alias_name

The "custmast" table

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Sandnes	60	80
5	Kodee	Srivilliputtur	60	30

Example 

  Select cm.custcode as Ccode,cm.custname as Cname from custmast as cm where cm.custcode=1


Output

Ccode	Cname
1	Siva

Posted by Siva 0 comments

Read More

SQL Top

The TOP clause is used to specify the number of records to return.

Syntax

SELECT TOP number column_name(s)
FROM table_name

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Sandnes	60	80
5	Kodee	Srivilliputtur	60	30

Example :1


Select Top 2 custcode, custname from custmast

custcode	custname
1	Siva
2	Bala

Example :2


Select Top 3 * from custmast

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200

Posted by Siva 0 comments

Read More

SQL Between

The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.

SQL BETWEEN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

The "Custmast" Table  ( Note Date Format ( MM/DD/YYYY)

custcode	Date	custname	city	rate
1	02/22/2008	Siva	Srivilliputtur	70
2	06/08/2009	Bala	Sivakasi	56
3	02/15/2010	Kanna	Madurai	200
4	02/04/2011	Vijay	Chennai	30
5	07/08/2011	Kodee	Sandnes	80
5	08/06/2012	Kodee	Srivilliputtur	30

Example: 1

SELECT     *

FROM         custmast

WHERE     Date BETWEEN '01/01/2009' and '6/08/2011'

Output :

custcode	Date	custname	city	rate
2	06/08/2009	Bala	Sivakasi	56
3	02/15/2010	Kanna	Madurai	200
4	02/04/2011	Vijay	Chennai	30

Example: 2

SELECT     *

FROM         custmast

WHERE     custcode BETWEEN 1 and  3

Output :

custcode	Date	custname	city	rate
1	02/22/2008	Siva	Srivilliputtur	70
2	06/08/2009	Bala	Sivakasi	56
3	02/15/2010	Kanna	Madurai	200

Posted by Siva 0 comments

Read More

SQL IN

The IN operator allows you to specify multiple values in a WHERE clause.

SQL IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

In "Custmast" Table

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Sandnes	60	80
5	Kodee	Srivilliputtur	60	30

Example: 1

  

SELECT * FROM Persons
WHERE City IN ('Srivilliputtur','Chennai')

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
4	Vijay	Chennai	30	30
5	Kodee	Srivilliputtur	60	30

Example: 2

  

SELECT * FROM Persons
WHERE City Not IN ('Srivilliputtur','Chennai')

custcode	custname	city	qty	rate
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
5	Kodee	Sandnes	60	80

Posted by Siva 0 comments

Read More