SQL Having

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

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

Example :



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


Output :

custname	qty
Kodee	120

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

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

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

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