SQL Basics

• Summarize values from several rows in a single value
• DISTINCT can first be used to exclude duplicate values
• Often used for statistical calculations
• To combine aggregate functions with fields that can return several values, you need an additional clause. See the next chapter

• The aggregate functions above are available across the following DBMSs: >Oracle, MySQL, SQL Server, PostgreSQL
• When COUNT( ) is given a column name, rows with NULL in that column are not counted

SELECT AVG(column_name) AS <column_name>
FROM   <table>	

Calculate the average quantity from OrderItems

-- Oracle / MySQL / SQL Server
SELECT AVG(quantity) AS GemAantal
FROM   OrderItems;

SELECT COUNT(*) AS <column_name>
FROM   <table>	

Count the rows in Customers

-- Oracle / MySQL / SQL Server
SELECT COUNT(*) AS AantalKlanten
FROM   Customers;

SELECT MAX(column_name)
FROM   <table>	

Return the highest item_price from OrderItems

-- Oracle / MySQL / SQL Server
SELECT MAX(item_price) 
FROM   OrderItems;

SELECT MIN(column_name)
FROM   <table>	

Return the lowest item_price from OrderItems

-- Oracle / MySQL / SQL Server
SELECT MIN(item_price) 
FROM   OrderItems;

SELECT SUM(column_name)
FROM   <table>	

Calculate the sum of quantity from OrderItems

-- Oracle / MySQL / SQL Server
SELECT SUM(quantity) 
FROM   OrderItems;

36. How many vendors are there? (1)

37. How many vendors have supplied products to us? (1)

38. What is the total value of all orders? (quantity * item_price) (1)

39. What is the average product price for vendor 'DLL01'? (1)

40. Count the products whose descriptions contain 'inch' (1)