A Guide to SQL
9th Edition
ISBN: 9781111527273
Author: Philip J. Pratt
Publisher: Course Technology Ptr
expand_more
expand_more
format_list_bulleted
Concept explainers
Expert Solution & Answer
Chapter 3, Problem 4CAT
Explanation of Solution
Running the script file of Colonial Adventure Tours:
The script file consists of create query for five tables and add values to be added in the created five table.
Steps to run the script file in oracle:
- Open the “SQL Workshop” in the menu bar.
- Then select “SQL Scripts” in the “LABEL”
- Click “Upload” option. The “Upload Script” window will appear on the screen.
- Click the “Choose File” option. Then specify the file path containing the script file and then click “Open” button.
- Click the “Upload” button.
Screenshot of “Upload Script”
- Once the “Upload” button is clicked, the below table is displayed on the screen...
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
You will need to add a CREATE DATABASE and USE database statement at the beginning of the script in the file of the Sample Database in order for SQL Script in the Sample Database to execute/run correctly
If you discover any ambiguity between the written Metadata and the actual table definitions, the path that I recommend is to use the SHOW TABLES Statement and the DESCRIBE Table Statement to eliminate any ambiguity between the written Metadata and the actual Metadata.
CustOrders.sql download
Aggregation Part of the Exercise
. Select the maximum price of any orders in the items_ordered table. Hint: Select the maximum price only.
. Select the average price of all of the orders that were purchased in the month of Dec.
. Return the total number of rows in the orders table?
. For all of the tents that were ordered in the orders table, what is the price of the lowest tent? Hint: Your query should return the price only.
Group By Exercises
. How many people are…
The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENACNE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys.
Use SQL to make the following changes to the Colonial Adventure Tours database (Figures 1-4 through 1-6 in Chapter 1). After each change, execute an appropriate query to show that the change was made correctly. If directed to do so by your instructor, use the information provided with the Chapter 3 Exercises to print your output or save it to a document. For any exercises that use commands not supported by your version of SQL, write the command to accomplish the task
Create a view named MAINE_TRIPS. It consists of the trip ID, trip name, start location, distance, maximum group size, type, and season for every trip located in Maine (ME)
a. Write and execute the CREATE VIEW command to create the MAINE_TRIPS view.b. Write and execute the command to retrieve the trip ID, trip name, and distancefor every Biking trip.c. Write and execute the query that the DBMS actually executes.d. Does updating the database through this view create any problems? If so, whatare they? If not, why not?
Chapter 3 Solutions
A Guide to SQL
Ch. 3 - Prob. 1RQCh. 3 - How do you delete a table using SQL?Ch. 3 - Prob. 3RQCh. 3 - Prob. 4RQCh. 3 - Prob. 5RQCh. 3 - Prob. 6RQCh. 3 - Prob. 7RQCh. 3 - Prob. 8RQCh. 3 - Prob. 9RQCh. 3 - Prob. 10RQ
Ch. 3 - Prob. 11RQCh. 3 - Prob. 12RQCh. 3 - Prob. 13RQCh. 3 - Use SQL to complete the following exercises....Ch. 3 - Prob. 2TDCh. 3 - Prob. 3TDCh. 3 - Prob. 4TDCh. 3 - Prob. 5TDCh. 3 - Prob. 6TDCh. 3 - Prob. 7TDCh. 3 - Prob. 1CATCh. 3 - Add the following row to the ADVENTURE_TRIP table:...Ch. 3 - Prob. 3CATCh. 3 - Prob. 4CATCh. 3 - Prob. 5CATCh. 3 - Prob. 6CATCh. 3 - Review the data for the TRIP table in Figure 1-5...Ch. 3 - Colonial Adventure Tours would like to increase...Ch. 3 - Prob. 1SCGCh. 3 - Add the following record to the VACATION_UNIT...Ch. 3 - Delete the VACATION_UNIT table.Ch. 3 - Prob. 4SCGCh. 3 - Prob. 5SCGCh. 3 - Prob. 6SCGCh. 3 - The SERVICE_REQUEST table uses the CHAR data type...
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- An EMPLOYEES table was added to the JustLee Books database to track employee information. Display a list of each employee’s name, job title, and manager’s name. Use column aliases to clearly identify employee and manager name values. Include all employees in the list and sort by manager name.arrow_forwardUse SQL to make the following changes to the Colonial Adventure Tours database (Figures 1-4 through 1-6 in Chapter 1). After each change, execute an appropriate query to show that the change was made correctly. If directed to do so by your instructor, use the information provided with the Chapter 3 Exercises to print your output or save it to a document. For any exercises that use commands not supported by your version of SQL, write the command to accomplish the task. Create a view named MAINE_TRIPS. It consists of the trip ID, trip name, start location,distance, maximum group size, type, and season for every trip located in Maine (ME). a. Write and execute the CREATE VIEW command to create the MAINE_TRIPS view.b. Write and execute the command to retrieve the trip ID, trip name, and distancefor every Biking trip.c. Write and execute the query that the DBMS actually executes.d. Does updating the database through this view create any problems? If so, whatare they? If not, why notarrow_forwardYou are assigned as the database administrator to collect and manage transactional data of the InstantRide operations. Your main task is to create SQL scripts to help other teams to retrieve the requested data. In the following activities, you will create the scripts, run against the database and send the result to the corresponding teams. Car Maintenance team wants to add new maintenance tasks to the MAINTENANCES table which was created in Chapter 8, Activity 1. However, the team also wants to insert the tasks in a batch into the database. In other words, they want to insert the rows all together without inserting one-by-one. Therefore, you will need to create a script to add the following tasks and ensure that they are added together: Car ID: 1001, Maintenance Type: 2, Due: 2020-06-01 Car ID: 1003, Maintenance Type: 2, Due: 2020-06-01arrow_forward
- Note: Use MYSQL Create a database named book-collection-db and table named: bookcollect. Your tableshould contain the following columns: Title, Author, Genre (Children’s, Romance,Thriller, Fantasy, etc.), Publisher, Copyright Date, ISBN Number, Category (Fiction/Nonfiction), Overview, Link Where to Buy, and Price. Create 30 rows containing list of bookwith varied genre.arrow_forwardNot all versions of this database include referential integrity constraints for all foreign keys. Use whatever commands are available for the RDBMS you are using, investigate if any referential integrity constraints are missing. Write any missing constraints and, if possible, add them to the associated table definitions.arrow_forwardOne of the TAL Distributors database's denormalized tables is as follows: The Customer table is no longer in third normal form due to which column or fields (CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum, RepName)? In this example, what is the denormalized table's normal form?arrow_forward
- Create both the tables Branch and Supervisor with appropriate attribute names, primary key, foreign key and data type. Insert the above records into Branch and Supervisor tables. Display all the records from Branch and Supervisor (Separately) Display all the details of Name, City and Salary. Display the total salary for every city in the supervisor table. Write a PL/SQL program to display BName from the Branch table for D5. Write a PL/SQL program to display all the details of Supervisor who is getting more than 1250 salary using cursor. Update city as ‘Suhar’ for Supervisor named Jack. Write a Procedure to receive a Supervisor SId as Input and display all the related values of that Supervisor. (Also write the calling program.) Delete all the branch details of operations.arrow_forwardUse FOR loop for your cursor. Write a PL/SQL block to read and display the job_id and number of employees per job_id. Accept input from the user to get the number of employees he wants to see in the output. Display also the number of records returned by your PL/SQL block.arrow_forwardYou are working with a database table that contains invoice data. The table includes columns for invoice_id and invoice_date. You want to remove duplicate entries for invoice_date. You write the SQL query below. Add a DISTINCT clause that will remove duplicate entries from the invoice_date column.arrow_forward
- Write the SQL statements necessary to remove the PET_OWNER table from the database. Assume that the PET table also needs to be removed. Do not run these commands in an actual database!arrow_forward4. This question continues from Q3. (a) Write the insert SQL to populate the Zoom, BB, and the QR tables with the values given below. For each table, use only one insert to complete the insert task. The Duration column is initially empty. (b) Can you insert the ZOOM table first? Provide a reason according to the relational database theory if it is not possible. (c) Write a Delete SQL to remove student ‘M M’ from the BB table. If it is not possible to remove the row, provide a reason according to the relational database theory. You must not use SID in the SQL because only the name is given. (d) Write an Update DML that will compute and update the duration in minutesin the Zoom table. Show the updated table and the DML.arrow_forwardPlease ensure that you include the result table as well as your SQL; you can copy and paste this from either your ssh client or SQL Developer. These tables exist in arion and are owned by the user dtoohey. You may, if you wish, create your own copies of the tables under your own account. If you do so, you should ensure that you copy the sample data in dtoohey’s tables. These queries are based on the View Ridge Gallery database you have been using in the Lab sessions. Please see Chapters 6 and 7 of Kroenke for background to the case and table structures. The sum of the acquisition price of works of art for each year (for example, if there were two works of art purchased for $1500 and $1000 in 2019, and one work of art purchased for $500 in 2020, then the sums would be $2500 and $500, for 2019 and 2020 respectively). Which artist has had the most works of art sold, and how many of the artist’s works have been sold?arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr