Tutorial 6

1. Tutorial 6 More multi-table queries and aggregates

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

3. Tutorial 6

4. 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(*)

5. (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.

6. Northwind data model

7. Testing the joins select customers.customerId, CompanyName, orderId from customers left join orders on customers.customerID=orders.customerID where orders.customerID is null

8. Tugas Individu Kunjungi http://www.w3schools.com/sql/default.asp Membuat ringkasan hasil kunjungan ke URL di atas.