Tutorial 6

1 / 8

# Tutorial 6 - PowerPoint PPT Presentation

Tutorial 6. More multi-table queries and aggregates. Tutorial 5 and 6 objectives. Displaying Data from Multiple Tables [  ]Write SELECT statements to access data from more than one table using equality and non-equality joins

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Tutorial 6' - ozzy

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Tutorial 6

More multi-table queries and aggregates

Tutorial 5 and 6 objectives
• Displaying Data from Multiple Tables
• [  ]Write SELECT statements to access data from more than one table using equality and non-equality joins
• [  ]View data that generally does not meet a join condition by using outer joins
• [  ]Join a table to itself using a self-join
• Aggregating Data using Group Functions
• [  ]Identify the available group functions
• [  ]Use group functions
• [  ]Group data using the GROUP BY clause [  ]Include or exclude grouped rows by using the HAVING clause
Using Northwind…
• Show the customerID, company name and orderId of all customers.(O2)
• Show the CustomerId, companyname and OrderId of all customers who do not have orders (O2).
• Show the lastname and title of all employees (worker) who report to another employee (boss), along with the boss’s last name (O3).
• (O4)[  ]Identify the available group functions
• Min(), max(), sum(), avg(), count(), count(*)
(O5)[  ]Use group functions
• Find the most expensive item in stock
• Find the least expensive item in stock
• Find the total value of all stock in stock
• Count the number of types of products in stock.
• Count the number of countries the employees come from.
• Find the average number of units we hold in stock.
• Count the employees from each country.
• (O6)[  ]Group data using the GROUP BY clause
• List all employee titles and the number of employees who have that title.
• (O7)Include or exclude grouped rows by using the HAVING clause
• List all suppliers who supply 2 or more products and the number of products they supply.
Testing the joins

select customers.customerId, CompanyName, orderId from

customers left join orders on customers.customerID=orders.customerID

where orders.customerID is null

### Tugas Individu

Kunjungi

http://www.w3schools.com/sql/default.asp

Membuat ringkasan hasil kunjungan ke URL di atas.