Tutorial 6
This presentation is the property of its rightful owner.
Sponsored Links
1 / 8

Tutorial 6 PowerPoint PPT Presentation


  • 96 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Tutorial 6

An Image/Link below is provided (as is) to download presentation

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

Tutorial 6

More multi-table queries and aggregates


Tutorial 5 and 6 objectives

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


Tutorial 61

Tutorial 6


Using northwind

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


Tutorial 6

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


Northwind data model

Northwind data model


Testing the joins

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

Tugas Individu

Kunjungi

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

Membuat ringkasan hasil kunjungan ke URL di atas.


  • Login