Ex. 2.4 Create database and table using SQL
docx
keyboard_arrow_up
School
Pennsylvania State University *
*We aren’t endorsed by this school
Course
210
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
6
Uploaded by BailiffWrenMaster830
Ex. 2.4 Create movies database and tables using SQL / ALTER
Purpose:
Write SQL to c
reate the moviesBasic database, build the movies_basic table, and alter the table MySQL Requirements:
Make sure you have access to the moviesBasic csv data file to load. It should be posted on Canvas.
zyBooks: 2.3 Managing Databases
2.4 Tables
Introduction
There are two ways to do everything that we have done so far. We can use the built-in wizard to create the database or we can write the SQL statements ourselves. In this exercise, we will write the SQL statements to create the database and the table.
Required Screenshots
1.
Part 3 Alter Movies_Basic table - Step #20
2.
Part 3 Alter Movies_Basic table – Step #24
Total Screenshots: 2
PART 1. DROP DATABASE
STEPS
1.
Start up the MySQL workbench 2.
Connect it to my MySQL instance. 3.
Drop existing moviesBasic database from the previous exercise.
1
This will instantly add irreversibly delete the table from the database so we are going to review the SQL first. 4.
Click Review SQL just to make sure we are doing what we intend to do. 5.
Click Execute and that schema is gone.
2
PART 2. CREATE MOVIES DATABASE AND TABLE USING SQL
6.
Open up a new SQL tab. 7.
Type CREATE DATABASE moviesBasic; 8.
Click execute button
in the output pane it says create database. 1 row affected.
That is usually a good sign. 9.
In navigator pane, right click and refresh
all so movies database shows up
And there is the moviesBasic database. Create movies_basic table 10. Set the movieBasic database as the default schema, (right click in navigation window by movies
This ensures that all of our SQL statements apply automatically to the moviesBasic database.
Expand the moviesBasic database and you can see that it has no contents
11. T
ype
the following in the query window and then click execute
3
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
12. Right-click in the navigation window to refresh.
PART 3. ALTER MOVIES_BASIC TABLE
Now that we have an existing movies_basic table, if we want to make permanent changes to that table structure, we use the ALTAR TABLE statement. We can make multiple changes with a single ALTER TABLE statement.
add a column called box_office_gross as a FLOAT.
rename the column numrating to numberrating
change director to director VARCHAR(50) instead of 45 13. Open a new SQL tab
14. Type ALTER TABLE movies_basic
15. Type ADD COLUMN box_office_gross FLOAT,
16. RENAME COLUMN numrating to numberrating,
17. CHANGE COLUMN director director VARCHAR(50);
18. Click Execute
4
19. Click on the grid icon.
20. Take a screen shot to submit.
Revert back to the original table
Drop box_office_gross 21. Type ALTER TABLE movies_basic
DROP COLUMN box_office_gross;
5
22. Change numberrating back to numrating
23. Change director back to VARCHAR(45)
24.
Submit a screenshot.
----------
6
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help