Lab 3 Database Systems Solution

Goal

Become accustomed to writing SQL queries.
 
Enjoy the beauty and accuracy of the relational model.
 
Get some still-easy lab points.
 
Check that your instance of our beloved CAP3 database is exactly the same as mine in the
script on our class web site. You need to have a clean instance when you begin this
assignment. If you’ve modiOied it in any manner, please drop everything in CAP3 and
reload from the scripts on our class web page.
Use CAP3 to answer all of these query questions. Write only one query per question. Be
certain to end each query with a semi-colon. Your Oinal script should execute all of the
queries in sequence from one command. Remember that CAP3 is one snapshot in time in
the life of that database. The queries you write should return the correct answer for all
time, not just for this snapshot.
List the ordno and dollars of all orders.
 
List the name and city of agents named Smith.
 
List the pid, name, and priceUSD of products with quantity more than 208,000.
 
List the names and cities of customers in Dallas.
 
List the names of agents not in New York and not in Tokyo.
 
List all data for products not in Dallas or Duluth that cost US$1 or more.
 
List all data for orders in January or March.
 
List all data for orders in February less than us$500.
 
List all orders from the customer whose cid is C005.
Test, test, and test again. Then test some more. When you think you've tested enough, go back and keep testing. Then get someone else to test for you while you test theirs.

Push your work to your GitHub repository early and often. While you’re in there . . .

•     Be sure to write meaningful commit messages.

•     Practice using diff to see the differences between successive versions of your code.

Practice reverting to an earlier version so that you’ll have that option in the future

 
Powered by