DBMS Lecture: Order By, Functions, Aggregate, Group By, HAVING, Tables Access, Slides of Introduction to Database Management Systems

Download DBMS Lecture: Order By, Functions, Aggregate, Group By, HAVING, Tables Access and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database

Management

System

Lecture - 30

Order By Example

Q: Display the students’ data in the

ascending order of names

SELECT * from STUDENT

ORDER BY stName

Practice Query

Display the name and cgpa of

students for all those students

who are in second or above

semester in descending order of

names

Functions in SQL

Built-in functions are pre-written

programs to perform specific

tasks

Accept certain arguments and

return the result

Using Functions

SELECT upper(stName), lower(stFName), stAdres, len(convert(char, stAdres)), FROM student

Aggregate Function Example

SELECT avg(cgpa) as 'Average

CGPA', max(cgpa) as 'Maximum

CGPA' from student

Output is……

Group By Clause

SELECT stName, avg(cgpa) as 'Average

CGPA', max(cgpa) as 'Maximum CGPA' from student

 SELECT prName, max(cgpa) as ‘Max

CGPA', min(cgpa) as ‘Min CGPA' FROM student GROUP BY prName

HAVING Clause

We can restrict groups by using

having clause; groups satisfying

having condition will be selected

HAVING Clause

SELECT select_list [ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ]

Where and having can be combined

Accessing Multiple Tables

Cartesian Product Inner join Outer Join Full outer join Semi Join Natural Join