Texas A&M University



Intro. To Databases Lab – Flipped Version A

Your names here:

You are working on this as a group!!

Complete one problem at a time

Have the Instructor or TA sign off after you complete each problem so you may move on.

You will be graded on your overall completion.

You are allowed notes, books, etc…

Part 1 - Creating a table in PHPMyAdmin

Using UMBC’s PHPMyAdmin account already created for you, create the table “Products” pictured below. Notice the table pictured below DOES NOT tell you what datatypes to use. You need to use the BEST fitting datatypes.



Hints: 1. “Inventory Count” is not an int or varchar!! (Pick something that won’t take up as much memory

2. Your key will be UNDERLINED when the table is created and submitted, and listed as “index” at the bottom

3. Do not use unsigned for now

[pic]

Answer:

[pic]

Part 2 - Manually adding data to the table using PHPMyAdmin

Using PHPMyAdmin, add the first FIVE rows pictured above to your table. Capitalization does matter!! (Especially when trying to match!) Bring your laptop to your instructor to prove it was created correctly.

| | |

|Instructor/TA signature | |

Your names here:

Answer:

[pic]

Manually querying a table in PHPMyAdmin

Using PHPMyAdmin, use the “search” feature (one of the items at the very top) feature to create the query to display only those ROWS that contain “Inventory Count” of 289. Bring your laptop to your instructor to prove it was created correctly.

| | |

|Instructor/TA signature | |

Answer:

[pic]

Your names here:

Creating a MySQL “search”

Using PHPMyAdmin, use the “search” feature to create the query to display only those ROWS that have a ProductRetailCost BETWEEN $70and $80. Bring your laptop to your instructor to prove it was created correctly.

| | |

|Instructor/TA signature | |

Answer:

[pic]

Your names here:

Creating a MySQL “Insert” from scratch

Using PHPMyAdmin, click on “SQL”. For practice, manually create a MySQL query to insert row 6 in the table pictured above. Then create the query to enter row 7, but don’t submit. Bring your laptop to your instructor to prove it was created correctly.

| | |

|Instructor/TA signature | |

Answer:

[pic]

[pic]

Your names here:

Creating a MySQL “Delete” from scratch

Using PHPMyAdmin, click on “SQL”. For practice, manually create a MySQL query to delete row 3. Then create the query to delete row 4, but don’t submit. Bring your laptop to your instructor to prove it was created correctly.

| | |

|Instructor/TA signature | |

[pic]

[pic]

Your names here:

Creating a MySQL “Update” from scratch

Using PHPMyAdmin, click on “SQL”. For practice, manually create a MySQL query to change the product name in row 1 to your instructors name. Then create the query to update row 2 with your name, but don’t submit. Bring your laptop to your instructor to prove it was created correctly.

| | |

|Instructor/TA signature | |

Answer:

[pic]

[pic]

Your names here:

Exporting the table

Export your table in Excel (or if you don’t have Excel, choose whatever you have) and SQL. Open both files and tell me the difference of exporting the table in Microsoft Excel (or whatever) and SQL.

|Excel |SQL |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

|Instructor/TA signature | |

Answer:

Excel will export the data as tabular CSV which has no reference to SQL. The data will simply be represented as rows and columns.

SQL will export the data as insert queries which are tied to the SQL query language. These may be directly used to populate equivalent data in someone else’s database.

Your names here:

Printing to share DB and Table structure information

Hard copies are still really useful. When meeting, make sure everyone is one the same page with a DB and/or table design is critically important. Find and name the features (they are different) found in PHPMyAdmin to display the information of your DB/table as follows:

|DB |specific table |

| |[pic] |

|[pic] | |

| | |

| | |

| | |

| | |

| | |

| | |

|Feature to do this? |Feature to do this? |

| | |

| | |

|Instructor/TA signature | |

Answer: DB, click on database, followed by data dictionary.

specificTable, click on the table, followed by structure, followed by print view

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download