Database Database


What is the average sale price of all animals?


Download 466 b.
bet8/19
Sana14.08.2018
Hajmi466 b.
1   ...   4   5   6   7   8   9   10   11   ...   19

What is the average sale price of all animals?

  • What is the average sale price of all animals?



What is the total value of the order for PONumber 22?

  • What is the total value of the order for PONumber 22?

    • Use any common math operators on numeric data.
    • Operate on data in one row at a time.




How many cats are in the Animal list?

  • How many cats are in the Animal list?



Count the number of animals in each category.

  • Count the number of animals in each category.



Count number of Animals in each Category, but only list them if more than 10.



Count Animals born after 6/1/2004 in each Category, but only list Category if more than 10.

  • Count Animals born after 6/1/2004 in each Category, but only list Category if more than 10.



List the CustomerID of everyone who bought something between 01-Apr-2004 and 31-May-2004.

  • List the CustomerID of everyone who bought something between 01-Apr-2004 and 31-May-2004.



List LastNames of Customers who bought between 4/1/2004 and 5/31/2004.

  • List LastNames of Customers who bought between 4/1/2004 and 5/31/2004.







List the Last Name and Phone of anyone who bought a registered White cat between 6/1/2004 and 12/31/2004.

  • List the Last Name and Phone of anyone who bought a registered White cat between 6/1/2004 and 12/31/2004.





List the Last Name and Phone of anyone who bought a registered White cat between 6/1/04 and 12/31/04.

  • List the Last Name and Phone of anyone who bought a registered White cat between 6/1/04 and 12/31/04.



Build Relationships First

  • Build Relationships First

  • Avoid multiple ties between tables

  • SQL

    • FROM Table1
    • INNER JOIN Table2
    • ON Table1.ColA = Table2.ColB
  • Join columns are often keys, but they can be any columns--as long as the domains (types of data) match.



Potential problem with three or more tables.

  • Potential problem with three or more tables.

  • Access uses predefined relationships to automatically determine JOINs.

  • JOINS might loop.

  • Most queries will not work with loops.





Save a query

  • Save a query

  • Any SELECT statement

  • Can use the View within other SQL queries.



To be updateable, a view must focus on one primary table. (OrderItem)

  • To be updateable, a view must focus on one primary table. (OrderItem)

    • Goal is to change data in only one table. (OrderItem)
    • Data can be displayed from other tables. (Item)
    • Never include or attempt to change primary keys from more than one table. (Item.ItemID)






































Download 466 b.

Do'stlaringiz bilan baham:
1   ...   4   5   6   7   8   9   10   11   ...   19




Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©fayllar.org 2020
ma'muriyatiga murojaat qiling