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 Getdate()

The GetDate() function returns the current system date and time.

Syntax 


SELECT GetDate() FROM table_name

Notes : Sql Server only Getdate() function used Other Database Used now()

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 custname,Getdate() as Date from Custmast

custname	Date
Siva	2011-12-05 19:31:29.390
Bala	2011-12-05 19:31:29.390
Kanna	2011-12-05 19:31:29.390
Vijay	2011-12-05 19:31:29.390
Kodee	2011-12-05 19:31:29.390
Kodee	2011-12-05 19:31:29.390

Posted by Siva 0 comments

Read More

SQL Round()

The ROUND() function is used to round a numeric field to the number of decimals specified.

Syntax 


SELECT ROUND(column_name,decimals) FROM table_name


The "custmast"Table

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

Example :1


Select Round(Rate,0)  as Rate from custmast


Output 

Rate

70.00

56.00

201.00

30.00

81.00

30.00

Example :2


Select Round(Rate,1)  as Rate from custmast


Output 

Rate

70.00

56.00

200.60

30.00

80.70

30.00

Posted by Siva 0 comments

Read More

The LEN() Function

The LEN() function returns the length of the value in a text field.

SQL LEN() Syntax

SELECT LEN(column_name) FROM table_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 LEN(City) as LengthOfCity FROM Custmast

LengthOfCity

14

8

7

7

7

14

Posted by Siva 0 comments

Read More

SQL Upper() and Lower ( )

                                                                SQL UPPER()

The UPPER() function converts the value of a field to uppercase.

Syntax

SELECT UPPER(column_name) FROM table_name

The "Custamast" 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

Sql Upper() Example 

SELECT UPPER(Custname) FROM custmast

custname

SIVA

BALA

KANNA

VIJAY

KODEE

KODEE

                                                     

                                                                SQL LOWER()

The LOWER() function converts the value of a field to lowercase

 

Syntax

SELECT LOWER(column_name) FROM table_name

SQL LOWER() Example

SELECT LOWER(Custname) FROM custmast

custname

siva

bala

kanna

vijay

kodee

kodee

Posted by Siva 0 comments

Read More