Tutorial 6
Download
1 / 8

Tutorial 6 - PowerPoint PPT Presentation


  • 121 Views
  • Uploaded on

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

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

PowerPoint Slideshow about ' Tutorial 6' - ozzy


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



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


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


ad