In this exercise, you will create a database to contain tables of batting statis

Question

In this exercise, you will create a database to contain tables of batting statistics for major league baseball teams. You will then create a table named teamstats in the baseball_stats database and add records to the new table from a fi le named team_stats.txt in your Projects directory for Chapter 7. 1. Log in to MySQL Monitor with your root account or with the user name and password you created. 2. Enter the following command to create a database named baseball_stats: mysql> CREATE DATABASE baseball_stats;[ENTER ] 3. After you see the “Query OK” message, enter the following command to select the baseball_stats database: mysql> USE baseball_stats;[ENTER ] 4. After you see the “Database changed” message, type the following command to ensure that you selected the baseball_stats database: mysql> SELECT DATABASE();[ENTER ] 5. Enter the following command to create the teamstats table. The Team field uses the VARCHAR data type. Eleven of the columns use INT data types, and the remaining two fields use FLOAT data types. Each of the statistical fi eld names uses common baseball abbreviations, such as G for games, AB for atbats, R for runs, and HR for home runs. mysql> CREATE TABLE teamstats (Team VARCHAR(50), FirstYear INT,[ENTER ] G INT, W INT, L INT, Pennants INT, WS INT,[ENTER ] R INT, AB INT, H INT, HR INT, AVG FLOAT,[ENTER ] RA INT, ERA FLOAT);[ENTER ] 6. After you see the “Query OK” message, enter the following command to display the structure of the new table: mysql> DESCRIBE teamstats;[ENTER ] 7. Enter a LOAD DATA statement that inserts records from the team_stats.txt fi le in your Projects directory for Chapter 7 into the teamstats table. Replace path_to_PHP_folders with the full path for your PHP_Projects directory for Chapter 7. mysql> LOAD DATA INFILE 'path_to_PHP_folders/ Chapter.07/Projects/team_stats.txt'[ENTER ] INTO TABLE teamstats;[ENTER ] 8. After you see the “Query OK” message, enter the following command to view all the records in the teamstats table: mysql> SELECT * FROM teamstats;[ENTER ] 9. Take a screenshot of the records in the teamstats table and save it as a YourName_TeamStats.JPG 10. Upload the screenshot to the assignment dropbox in Blackboard

Explanation / Answer

Step1)
a) Log into the MYSQL monitor with your root account.
b) Enter the following command to create a database named baseball_stats:
mysql> CREATE DATABASE baseball_stats;
c) After that you see "Query OK" message, then enter the following command to select the baseball_stats database:
mysql> use baseball_stats;
d)After that you see that "Database changed" message, then type the following command to ensure the selected database:
mysql> SELECT DATABASE();

Step 2)
a) Return to the MySQL Monitor and create the table teamstats:
mysql> CREATE TABLE teamstats( Team varchar(50), G int, AB int, B int AVG float);
b)After that you see "Query OK" message, then enter the following command to display the structure of the new table:
mysql> DESCRIBE teamstats;

c) Enter the following SQL statement which return the team and home run leader fields for the teams:
mysql>SELECT team, G FROM teamstats
-> INTO OUTFILE 'path/hrleaders.txt'
-> FIELDS TERMINATED BY ' '
-> LINES TERMINATED BY ' ';
d) Enter Load data statement that insert records from team_stats.txt file into the hrleaders table:
mysql> LOAD DATA LOCAL INFILE 'path/team_stats.txt'
-> INTO TABLE hrleaders;

e)After that you see "Query OK" message, then enter the following command to view all the records in teamstats table:
mysql> SELECT * FROM hrleaders;

mysql> SHOW TABLES;

Step 3) NOw run these Queries
a) Return to the MySQL Monitor and enter the following statement, which return the team G(games played), and AB(at bats) fields from table teamstats:
mysql> SELECT team, G, AB FROM hrleaders;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.