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

• 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

• 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.

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.