SQL LCASE( ) Function

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

SQL LCASE() Syntax SELECT LCASE(column_name) FROM table_name;
Syntax for SQL Server
Subject_No
Subject_Name
Dept_Name
SU01
Limit Process
Mathematics
SU02
Linear Programming
Mathematics
SU03
Atomic and Molecular Structure
Chemistry
SU04
Cell Biology
Botany
SU05
Genetics
Zoology
SU06
Computer Architecture
Computer Science
SU07
Relativity and structure of matter
Physics
Subject_Name                      
 Dept_Name
limit process                     
 Mathematics
linear programming                
 Mathematics
atomic and molecular  structure    
 Chemistry
cell biology                      
 Botany
genetics                           
 Zoology
computer architecture             
 Computer Science
relativity and structure of matter
 Physics

SELECT LOWER(column_name) FROM table_name;

SQL LCASE() Example
We have the following "Subject" table:
Now we want to select the content of the "Subject_Name" and "Dept_Name" columns above, and convert the " Subject_Name " column to  lowercase .
We use the following SELECT statement:

SELECT LCASE(Subject_Name) AS Subject_Name,Dept_Name 
FROM subject;

The result-set will look like this:

SQL UCASE( ) Function

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

SQL UCASE() Syntax

SELECT UCASE(column_name) 
FROM table_name;

Syntax for SQL Server

SELECT UPPER(column_name) 
FROM table_name;

SQL UCASE() Example

We have the following "Subject" table:
Subject_No
Subject_Name
Dept_Name
SU01
Limit Process
Mathematics
SU02
Linear Programming
Mathematics
SU03
Atomic and Molecular Structure
Chemistry
SU04
Cell Biology
Botany
SU05
Genetics
Zoology
SU06
Computer Architecture
Computer Science
SU07
Relativity and structure of matter
Physics


Now we want to select the content of the "Subject_Name" and "Dept_Name" columns above, and convert the " Subject_Name " column to uppercase.
We use the following SELECT statement:

SELECT UCASE(Subject_Name) AS Subject_Name,Dept_Name 
FROM subject;

The result-set will look like this:

Subject_Name
Dept_Name
LIMIT PROCESS
Mathematics
LINEAR PROGRAMMING
Mathematics
ATOMIC AND MOLECULAR STRUCTURE      
Chemistry
CELL BIOLOGY
Botany
GENETICS
Zoology
COMPUTER ARCHITECTURE
Computer Science
RELATIVITY AND STRUCTURE OF MATTER
Physics

SQL HAVING Clause


The HAVING Clause

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



SQL HAVING Example

We have the following "result" table:
Student_No
Subject_No
Year_Exam
Marks
ST101
SU03
1
69
ST102
SU01
1
61
ST101
SU04
1
70
ST101
SU05
1
87
ST103
SU01
2
51
ST103
SU03
1
59
ST103
SU01
3
56
ST108
SU01
1
78
ST105
SU05
2
98


Now we want to find if any of the student have a  average marks of less than 60.
We use the following SQL statement:

SELECT Student_No,AVG(Marks) 
FROM result 
GROUP BY Student_No 
HAVING AVG(Marks)<60;


The result-set will look like this:

Student_No
AVG(Marks)
ST103
55.3333






Now we want to find if the student "ST101" or "ST102" have a Average marks of more than 60.
We add an ordinary WHERE clause to the SQL statement:



SELECT Student_No,AVG(Marks) 
FROM result 
WHERE Student_No='ST101' OR Student_No='ST102' 
GROUP BY Student_No 
HAVING AVG(Marks)>60;



The result-set will look like this:

Student_No
 AVG(Marks)
ST101     
    75.3333
 ST102     
    61.0000







SQL GROUP BY Statement


Aggregate functions often need an added GROUP BY statement.

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

SQL GROUP BY Example
We have the following "result" table:
Student_No
Subject_No
Year_Exam
Marks
ST101
SU03
1
69
ST102
SU01
1
61
ST101
SU04
1
70
ST101
SU05
1
87
ST103
SU01
2
51
ST103
SU03
1
59
ST103
SU01
3
56
ST108
SU01
1
78
ST105
SU05
2
98
Now we want to find the total sum (total Marks) of each Student.
We will have to use the GROUP BY statement to group the Student.
We use the following SQL statement:

SELECT Student_No,SUM(Marks) AS Total_Marks FROM result GROUP BY Student_No;

The result-set will look like this:

Student_No
Total_Marks
ST101
226
ST102
61
ST103
166
ST105
98
ST108
78


Let's see what happens if we omit the GROUP BY statement:

SELECT Student_No,SUM(Marks) AS Total_Marks FROM result;

The result-set will look like this:

Student_No
Total_Marks
ST101
629



GROUP BY More Than One Column

We can also use the GROUP BY statement on more than one column, like this:

SELECT Student_No,Year_Exam,SUM(Marks) AS Total_Marks FROM result GROUP BY Student_No,Year_Exam;

The result-set will look like this:

Student_No
Year_Exam
Total_Marks
ST101
1
226
ST102
1
61
ST103
1
59
ST108
1
78
ST103
2
51
ST105
2
98
ST103
3
56