Scribd
Upload
48 views55 pages

4.1-Structured Query Language (SQL)

The document outlines the lecture content for a database systems course (CS220), focusing on SQL, including its introduction, syntax, and various clauses such as SELECT, FROM, WHERE, GROUP BY, and HAVING. It provides examples and test cases demonstrating the use of SQL functions like COUNT, NULL, DISTINCT, and their interactions with GROUP BY and HAVING clauses. The document concludes with a summary of key points regarding SQL operations and their implications.

Uploaded by

Thomas Cutler
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
48 views55 pages

4.1-Structured Query Language (SQL)

The document outlines the lecture content for a database systems course (CS220), focusing on SQL, including its introduction, syntax, and various clauses such as SELECT, FROM, WHERE, GROUP BY, and HAVING. It provides examples and test cases demonstrating the use of SQL functions like COUNT, NULL, DISTINCT, and their interactions with GROUP BY and HAVING clauses. The document concludes with a summary of key points regarding SQL operations and their implications.

Uploaded by

Thomas Cutler
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

CS220: DATABASE SYSTEMS

Summer
LECTURE OUTLINE
 SQL-Introduction
 SQL-Data Manipulation Language (DML) or Data Query
Language (DQL)
 SELECT
 WHERE
 FROM
 GROUP BY
 HAVING

2
SQL INTRODUCTION
WHAT IS SQL

4
SQL SYNTAX

5
EXAMPLE

6
SQL WORK FLOW

7
OTHER ASPECT OF SQL

8
SQL –DML / DQL

9
SELECT CLAUSE

10
SELECT CLAUSE EXAMPLE

11
FROM CLAUSE

12
FROM CLAUSE EXAMPLE

13
FROM CLAUSE EXAMPLE

14
WHERE CLAUSE

15
SCALAR EXPRESSION IN SQL

16
WHERE CLAUSE EXAMPLE

17
PATTERN MATCHING

18
PATTERN MATCHING -EXAMPLE

19
ESCAPE CHARACTER
ESCAPE CHARACTER
GROUP BY AND HAVING
GROUP BY CLAUSE
SPLIT APPLY COMBINE
POINT TO REMEMBER
GROUP BY CLAUSE- EXAMPLE
HAVING CLAUSE
HAVING CLAUSE- EXAMPLE
GROUP BY AND ORDER BY
GROUP BY AND HAVING
Relation b/w COUNT, NULL, DISTINCT, ALL
Test Case#1: Count(*); NULL in Salary

Conclusion: All the tuples have been counted.


Relation b/w COUNT, NULL, DISTINCT, ALL
Test Case#2: Count(Salary); NULL in Salary

Conclusion: Count ignores “NULL” and don’t put it into consideration while counting.
Relation b/w COUNT, NULL, DISTINCT, ALL
Test Case#3: Count(DISTINCT Salary); NULL in Salary

Conclusion: Count ignores “NULL” and don’t put it into consideration while counting whether
DISTINCT is being used or not.
Relation b/w COUNT, NULL, DISTINCT, ALL
Test Case#4: Count(ALL Salary); NULL in Salary

Conclusion: Count ignores “NULL” even if ALL is used. In fact, use of ALL is not making any sense
in here and the query behaves same without ALL.
Revisit!

Not need of ALL; count at


any case ignores NULL and
count the non-null values
only.
Relation b/w GROUP BY, HAVING &
Aggregated Attribute
Test Case#1: GROUP BY without Aggregated Attribute

Conclusion: GRIOUP BY can work without aggregated attribute and vice versa
Relation b/w GROUP BY, HAVING &
Aggregated Attribute
Test Case#2: GROUP BY with Aggregated Attribute

Conclusion: GRIOUP BY doesn’t


allow aggregated attribute to
appear within its clause but in the
SELECT clause if HAVING is not
there
Relation b/w GROUP BY, HAVING &
Aggregated Attribute
Test Case#3: GROUP BY + HAVING with Aggregated Attribute and SELECT without Aggregated attribute

Conclusion: The aggregated


attribute need not to be the part of
SELECT if GROUP BY and
HAVING is used.
Relation b/w GROUP BY, HAVING &
Aggregated Attribute
Test Case#4: GROUP BY + HAVING with Aggregated Attribute and SELECT with Aggregated attribute

Conclusion: The aggregated


attribute can be a part of SELECT
if it needs to be displayed in the
result
THANK YOU

You might also like