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 4TD
Explanation of Solution
Running the script file for TAL Distributors:
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 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-01
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…
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_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_forward
- use the sql to answer the floowing two question (data base code had been given below) To perform below tasks, refer to tables in the City Jail database. List all criminals along with the crime charges files. The report needs to include Criminal ID, name, Crime Code, and fine amount List all criminals along with crime status and appeal status (if applicable – sounds like an Outer join). The reports needs to include the Criminal ID, name, crime classification ,date charges , appeal filing date and appeal status. Show all criminals, regardless of whether they have filed an appeal or not.arrow_forwardThe 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.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_forward
- One 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_forwardWrite 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_forwardNote: 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_forward
- 4. 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_forwardCreate 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_forwardWrite a query to display the first name, last name, and email address of employees hired from January 1, 2005, to December 31, 2014. Sort the output by last name and then by first name. Write the answer to each query and the result of the query (screen capture). You should submit all design work, program documentation, and relevant sample screen shots of your implementation. A script file with all database creation and table populating is required.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
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning