Database Aggregate Functions

Computer Science(0478)

IGCSE

9. Database

SQL 3

In learning you will teach, and in teaching you will learn

Objectives

13/02/2023

Computer Science - 0478

Learning Outcomes

Grade D - E

Grade B - C

Grade A – A*

At the end of the students will be able to:

•Write basic SQL script
using COUNT and SUM

•Write basic SQL script using
COUNT, SUM with the
where clause

•Write basic SQL script using
SELECT, WHERE, FROM,
COUNT, SUM aggregate
function, WHERE clause and
GROUP BY clause

13/02/2023

Computer Science - 0478

9.1 Database

13/02/2023

Computer Science - 0478

•Aggregate function

•SUM

•COUNT

•GROUP BY Clause

9.1 Database

Starter

Quiz on previous learning

SQL statement involving

•SELECT fields FROM Table

•ORDER BY clause

•WHERE clause

Multiple Choice

Which SQL function is used to count the number of rows in a SQL query?

• COUNT()

• NUMBER()

• SUM()

• COUNT(*)

Multiple Choice

If you don’t specify ASC or DESC after a SQL ORDER BY clause, the following is used by default

• ASC

• DESC

• There is no default value

• None of the mentioned

Multiple Choice

With SQL, how do you select all the records from a table named “Persons” where the value of the column “FirstName” ends with an “a”?

• SELECT * FROM Persons WHERE FirstName=’a’

• SELECT * FROM Persons WHERE FirstName LIKE ‘a%’

• SELECT * FROM Persons WHERE FirstName LIKE ‘%a’

• SELECT * FROM Persons WHERE FirstName=’%a%’

Multiple Choice

Which character returns all the data found in a table?

• %

• #

• *

• /

Multiple Choice

How would we script a SQL query to select "Description" from the Item table?

• SELECT Item.Description

• EXTRACT Description FROM Item

• SELECT Item FROM Description

• SELECT Description FROM Item

Multiple Choice

With SQL how can I return all items in the Item table sorted from the lowest priced to the highest priced?

• SELECT * FROM Items ORDER BY Price ASCENDING

• SELECT * FROM Items ORDER BY Price ASC

• SELECT * FROM Items BY Price LOWEST TO HIGHEST

• SELECT * FROM Items ORDER BY Price DESC

9.1 Database

Read, understand and complete structured query language (SQL) scripts to query data stored in a single
database table

13/02/2023

Computer Science - 0478

SQL Script

9.1 Database

Read, understand and complete structured query language (SQL) scripts to query data stored in a single
database table

13/02/2023

Computer Science - 0478

SQL Script

•SELECT COUNT(fieldname1) FROM TableName

•SELECT COUNT(fieldname1) AS ThisFieldName FROM TableName

•Example: SELECT COUNT(*) AS Num_Orders FROM Order

9.1 Database

Read, understand and complete structured query language (SQL) scripts to query data stored in a single
database table

13/02/2023

Computer Science - 0478

SQL Script

•SELECT SUM(fieldnameN) FROM TableName

•SELECT SUM(fieldnameN) AS ThisFieldName FROM TableName

•NOTE: FieldnameN should be numberic value

•Example: SELECT SUM(Order_Value) AS Total_OrderValue FROM Orders

9.1 Database

Read, understand and complete structured query language (SQL) scripts to query data stored in a single
database table

13/02/2023

Computer Science - 0478

SQL Script

Using GROUP BY Clause

•SELECT Field1, Count(Field1) FROM TableName GROUP BY Field1

•Example: SELECT COUNT(Memb_Type) AS NumMembers FROM Customers

GROUP BY Memb_Type

9.1 Database

Activity

9.1 Database

Quiz

Click to host:

https://quizizz.com/admin/quiz/5e1d6315cfe055001b82dcdb?source=quiz_sh

are

9.1 Database

Plenary

Students to post on Teams collaborative space either about:

•Reflection on what has been learnt

•Questions they may have

•Response to questions