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

SQL Having

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SQL HAVING Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value 

The "custmast"  Then

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

SELECT CustName,SUM(Qty) FROM custmast
GROUP BY CustName
HAVING SUM(Qty)>110


Output :

custname	qty
Kodee	120

Posted by Siva 0 comments

Read More

SQL GROUP BY

The GROUP BY Statement

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_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

Now we want to find the total sum (Qty) of each customer.

Select Custname,Sum(Qty)  as Tot From Custmast 

Group by CustName

custname	Tot
Siva	50
Bala	30
Kanna	100
Vijay	30
Kodee	120

GROUP BY More Than One Column

SELECT Custname,City,SUM(Qty) as Tot FROM Orders
GROUP BY Custname,City

Posted by Siva 0 comments

Read More