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 the PHPMyAdmin account already created for you, create the table “Customer” 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. “RepNum” 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]

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.

Question 1:

------------

Evaluation Criteria:

1. Check if all columns are added properly.

2. customerNum should be added as a Primary Key. Check for that.

3. repNum should be of type TINYINT

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 “RepNum” of 35. Bring your laptop to your instructor to prove it was created correctly.

Question 2:

------------

Check if the created query looks like this: SELECT * FROM `customer` WHERE `repNum` = 35

Result has “Brookings” and “The Everything Shop”

Creating a MySQL “search”

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

Question 3:

------------

Check if the created query looks like this: SELECT * FROM `customer` WHERE `balance` BETWEEN 3500 AND 7000

Some will use BETWEEN command, some will use Balance > 3500 AND Balance < 7000

Result should be “Al’s”, Fergonson, Everything Shop”

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.

Question 4:

------------

Insert query should be successfully executed.

INSERT INTO customer (customerName, street, city, state, zip, balance, creditsLimit, repNum) VALUES ("Johnson's Dept Store", "372 Oxford", "Sheldon", "FL", 33553, 2106.00, 10000.00, 65)

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.

Question 5:

------------

Running a DELETE query should delete row 4 i.e. customerNum = 4

DELETE FROM customer WHERE customerNum = 4

Creating a MySQL “Update” from scratch

Using PHPMyAdmin, click on “SQL”. For practice, manually create a MySQL query to change the customer 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.

Question 6:

------------

customerName in row 2 should be changed to the student's name.

UPDATE customer SET customerName="Vivek" WHERE customerNum=2

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 |

| | |

|Place description here: |Place description here: |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

|Instructor/TA signature | |

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? |

| | |

Question 8:

------------

part 1: Data Dictionary

part 2: Print view

Both these options are under structure.

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

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

Google Online Preview   Download