If you want to do quick searches in a database, it is best to have a good understanding of the way in which the tables of the database refer to each other. The database scheme has been drawn up for this purpose. It’s up to you to examine this thoroughly and find out the meaning of the arrows. Usually, for clarity, next to each column is its type.
De database contains these tables:
CUSTOMER
: customer information (address, email …)DISCOUNT_CODE
: discount dependant on discount codeMANUFACTURER
: manufacturer information (address, email …)MICRO_MARKET
: regions which form a micro marketPRODUCT
: products (price, description …)PRODUCT_CODE
: product code indicates the different kinds of productsPURCHASE_ORDER
: purchase informationTip to compose queries
First determine which columns from which tables you need to consult (either to write out, or to solve a function, or to check a condition).
Database schema always at hand 👉
The database schema associated with the exercise can be called up for each exercise by clicking on the database icon in the top right corner. Unfortunately this does not work in the VS Code extension, only on Dodona itself. You can of course also download that image1.
Order of columns
The order of the columns does not matter in queries in Dodona.
The first five rows of each table are shown below.
DISCOUNT_CODE
DISCOUNT_CODE | RATE |
---|---|
H | 16 |
M | 11 |
L | 7 |
N | 0 |
These were the first 4 rows. The full table DISCOUNT_CODE
consists of 4 rows.
MICRO_MARKET
ZIP_CODE | RADIUS | AREA_LENGTH | AREA_WIDTH |
---|---|---|---|
95051 | 255.590 | 689.856 | 478.479 |
94043 | 157.869 | 385.821 | 147.538 |
85638 | 758.648 | 328.963 | 482.164 |
12347 | 475.965 | 385.849 | 146.937 |
94401 | 368.386 | 285.848 | 173.794 |
These were the first 5 rows. The full table MICRO_MARKET
consists of 11 rows.
CUSTOMER
CUSTOMER_ID | DISCOUNT_CODE | ZIP | NAME | ADDRESSLINE1 | ADDRESSLINE2 | CITY | STATE | PHONE | FAX | CREDIT_LIMIT | |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | N | 95117 | Jumbo Eagle Corp | 111 E. Las Olivas Blvd | Suite 51 | Fort Lauderdale | FL | 305-555-0188 | 305-555-0189 | jumboeagle@example.com | 100000 |
2 | M | 95035 | New Enterprises | 9754 Main Street | P.O. Box 567 | Miami | FL | 305-555-0148 | 305-555-0149 | www.new.example.com | 50000 |
3 | L | 12347 | Small Bill Company | 8585 South Upper Murray Drive | P.O. Box 456 | Alanta | GA | 555-555-0175 | 555-555-0176 | www.smallbill.example.com | 90000 |
25 | M | 85638 | Wren Computers | 8989 Red Albatross Drive | Suite 9897 | Houston | TX | 214-555-0133 | 214-555-0134 | www.wrencomp.example.com | 25000 |
36 | H | 94401 | Bob Hosting Corp. | 65653 Lake Road | Suite 2323 | San Mateo | CA | 650-555-0160 | 650-555-0161 | www.bobhostcorp.example.com | 65000 |
These were the first 5 rows. The full table CUSTOMER
consists of 13 rows.
PRODUCT_CODE
PROD_CODE | DISCOUNT_CODE | DESCRIPTION |
---|---|---|
SW | M | Software |
HW | H | Hardware |
FW | L | Firmware |
BK | L | Books |
CB | N | Cables |
These were the first 5 rows. The full table PRODUCT_CODE
consists of 6 rows.
MANUFACTURER
MANUFACTURER_ID | NAME | ADDRESSLINE1 | ADDRESSLINE2 | CITY | STATE | ZIP | PHONE | FAX | REP | |
---|---|---|---|---|---|---|---|---|---|---|
19941212 | Birders United | 4000 Cormorant Circle | Building 19 | Burlington | OR | 95051 | 206-555-0183 | 206-555-0179 | bill@example.com | Bill Snider |
19948494 | Computer Support Center | 5632 Michigam Ave | Dearborn | RI | 48127 | 313-555-0181 | 313-555-0182 | www.comsup.example.net | Sam Wright | |
19955564 | Birders United | 4000 Cormorant Circle | Building 15 | Burlington | OR | 95051 | 206-555-0179 | 206-555-0179 | phil@example.com | Phil Waters |
19955565 | Birders United | 4000 Cormorant Circle | Building 16 | Burlington | OR | 95051 | 206-555-0180 | 206-555-0179 | birders@example.com | Birders |
19955656 | Soft Circle Opticians | 95 Eastway Clearview Drive | Building 1 | Boston | MA | 02100 | 617-555-0171 | 617-555-0172 | www.softcircle@example.com | Alfred Nelson |
These were the first 5 rows. The full table MANUFACTURER
consists of 30 rows.
PRODUCT
PRODUCT_ID | MANUFACTURER_ID | PRODUCT_CODE | PURCHASE_COST | QUANTITY_ON_HAND | MARKUP | AVAILABLE | DESCRIPTION |
---|---|---|---|---|---|---|---|
948933 | 19941212 | MS | 36.95 | 50 | 75.00 | TRUE | Computer Tool Kit |
958888 | 19955564 | HW | 799.99 | 0 | 1.50 | FALSE | Ultra Spacr 999Mhz Computer |
958889 | 19955565 | HW | 595.95 | 0 | 1.25 | FALSE | 686 7Ghz Computer |
964025 | 19963322 | SW | 209.95 | 300 | 41.00 | TRUE | Jax WS Application Development Environment |
964026 | 19963323 | SW | 259.95 | 220 | 51.00 | TRUE | Java EE 6 Application Development Environment |
These were the first 5 rows. The full table PRODUCT
consists of 30 rows.
PURCHASE_ORDER
ORDER_NUM | CUSTOMER_ID | PRODUCT_ID | QUANTITY | SHIPPING_COST | SALES_DATE | SHIPPING_DATE | FREIGHT_COMPANY |
---|---|---|---|---|---|---|---|
10398001 | 1 | 980001 | 10 | 449.00 | 2011-05-24 | 2011-05-24 | Poney Express |
10398002 | 2 | 980005 | 8 | 359.99 | 2011-05-24 | 2011-05-24 | Poney Express |
10398003 | 2 | 980025 | 25 | 275.00 | 2011-05-24 | 2011-05-24 | Poney Express |
10398004 | 3 | 980030 | 10 | 275.00 | 2011-05-24 | 2011-05-24 | Poney Express |
10398005 | 1 | 980032 | 100 | 459.00 | 2011-05-24 | 2011-05-24 | Poney Express |
These were the first 5 rows. The full table PURCHASE_ORDER
consists of 15 rows.
STUDENT_DETAIL
STUDENT_DETAIL_ID | NAME | CODE |
---|
These were the first 0 rows. The full table STUDENT_DETAIL
consists of 0 rows.