Lab week 10

1
Lab week 10

• Aggregates and sub-queries
• And assignment details

2
Use North wind
3
1 Building joined queries

• Select the product name and the company name of
  the supplier for each product.
• Select the supplier name, the product name and
  the order Id for each order detail. (Note the
  number of rows)
• Select the supplier name, the product name, the
  order id and the customers company name.
• Omit the order id and the product Id from the
  previous query and add the word distinct after
  the select clause.

4
2 Using sub queries to negate

• The last query on the previous slide shows us all
  the suppliers and the customers to which they
  supply.
• List all the suppliers who supply to the company
  named Chop-suey Chinese
• How many joins do you need?
• Using a sub-query, list all the suppliers who
  DONT supply to the company named Chop-suey
  Chinese
• Do you need as many joins in the sub-query?
• Can you make the query any shorter than it is now?

5
Queries

• How many shippers are there in the Northwind
  database that have never shipped any orders?
• List the name of each customers company and the
  total amount they have spent with us, in
  descending amount spent order.
• List the name of each customers company and the
  total amount they have spent with us, for all
  companies that have spent more than the average
  amount with us.

6
More queries

• List all orders that have more than the average
  number of order details.
• List sum of the cost of all order details, summed
  over the product id and over the orderid.

7
Assignment details

• Look up the case study that you were given in
  Software Engineering.
• Design an Entity Relationship diagram to hold all
  of the data required for your system.
• This assignment is due in on Tuesday, 13th
  December 2005.
• Use ERWin to document your tables.

8
ERWin

• Program files gt CA Associates gt AllFusion gt ERWin
  Data Modeler gt ERWin

9
Starting ERWin
10
(No Transcript)
11
Pick your model type
12
Blank model
13
To add

• Entities
• Right click on the entity icon in the browser
• The browser is the left panel on the screen

14
To add attributes

• Expand the entity icon.
• Right click on the attributes icon and add an
  attribute.
• Right click on the attribute, to open the
  properties.
• Here, primary keys can be set and data types can
  be allocated.

15
Adding foreign keys
16
How to

• Open the parent relationship from the course.
• Choose the student as child entity.
• Note, if you tick identifying, the key will
  become part of the primary key of the child
  entity.

17
Result of identifying
18
Non-identifying
19
To get physical

• Drop down the logical box to get physical.