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
Textbook Question
Chapter 7, Problem 8CAT
Write the commands to obtain the following information from the system catalog. Do not execute these commands unless your instructor asks you to do 30.
- a. List every column in the GUIDE table and its associated data type.
- b. List every table that contains a column named TRIP_ID.
- c. List the table name, column name, and data type for the columns named TRIP_ID, TRIP_NAME, and TYPE. Order the results by table name within column name. (That is, column name is the major sort key and table name is the minor sort key.)
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Task 13: Write the command to display only tables within the system catalog that are of the type BASE TABLE. Display only the following columns: TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE.
1
Task 14: Create an index named ITEM_INDEX1 on the ITEM_ID column in the INVOICE_LINE table.
1
Task 15: Create an index named ITEM_INDEX2 on the CATEGORY column in the ITEM table.
1
Task 16: Create an index named ITEM_INDEX3 on the CATEGORY and LOCATION columns in the ITEM table.
1
Task 17: Create an index named ITEM_INDEX4 on the CATEGORY and LOCATION columns in the ITEM table. List categories in descending order.
1
Task 18: Delete the index named ITEM_INDEX3.
1
Task 19: List every table that you have created thus far. Display the TABLE_SCHEMA and TABLE_NAME columns.
1
Task 20: List every column in the ITEM table and its associated data type.
1
Task 21: Alter the INVOICE_LINE table by adding…
List all the views contained within the system catalog.
Write the command to display only tables within the system catalog that are of the type BASE TABLE. Display only the following columns: TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE.
WEEK 9 ASSIGNMENT#6
Part II:
Complete the following exercise:
Using a subquery, select student first name where the student is in the student course table.
Using a subquery, select the course id where the average GPA is 2.
Using a subquery, select the course id where the average GPA is greater than 2.
Using a subquery, select student first name where the student is not in the student course t
Chapter 7 Solutions
A Guide to SQL
Ch. 7 - What is a view?Ch. 7 - Which command creates a view?Ch. 7 - Prob. 3RQCh. 7 - What happens when a user retrieves data from a...Ch. 7 - What are three advantages of using views?Ch. 7 - Which command deletes a view?Ch. 7 - Prob. 8RQCh. 7 - Which command terminates previously granted...Ch. 7 - Prob. 10RQCh. 7 - How do you create an index? How do you create a...
Ch. 7 - Prob. 12RQCh. 7 - Does the DBMS or the user make the choice of which...Ch. 7 - Describe the information the DBMS maintains in the...Ch. 7 - The CUSTOMER table contains a foreign key,...Ch. 7 - Prob. 16RQCh. 7 - Prob. 17RQCh. 7 - Prob. 18RQCh. 7 - Prob. 19RQCh. 7 - When would you usually specify primary key...Ch. 7 - Prob. 21RQCh. 7 - Prob. 22RQCh. 7 - Prob. 23RQCh. 7 - Use SQL to make the following changes to the TAL...Ch. 7 - Create a view named ITEM_ORDER. It consists of the...Ch. 7 - Create a view named ORDER_TOTAL. It consists of...Ch. 7 - Write, but do not execute, the commands to grant...Ch. 7 - Prob. 5TDCh. 7 - Perform the following tasks: a. Create an index...Ch. 7 - Delete the index named ITEM_INDEX3.Ch. 7 - Write the commands to obtain the following...Ch. 7 - Prob. 9TDCh. 7 - Prob. 10TDCh. 7 - Toys Galore currently has a credit limit of 7,500....Ch. 7 - Use SQL to make the following changes to the...Ch. 7 - Create a view named RESERVATION_CUSTOMER. It...Ch. 7 - Create a view named TRIP_INVENTORY. It consists of...Ch. 7 - Write, but do not execute, the commands to grant...Ch. 7 - Prob. 5CATCh. 7 - Create the following indexes: a. Create an index...Ch. 7 - Prob. 7CATCh. 7 - Write the commands to obtain the following...Ch. 7 - Prob. 9CATCh. 7 - Ensure that the only legal values for the TYPE...Ch. 7 - Prob. 11CATCh. 7 - Use SQL to make the following changes to the...Ch. 7 - Create a view named CONDO_OWNERS. It consists of...Ch. 7 - Create a view named CONDO_FEES. It consists of two...Ch. 7 - Write, but do not execute, the commands to grant...Ch. 7 - Prob. 5SCGCh. 7 - Prob. 6SCGCh. 7 - Delete the OWNER_INDEX 3 index from the OWNER...Ch. 7 - Write the commands to obtain the following...Ch. 7 - Prob. 9SCGCh. 7 - Ensure that the only legal values for the BDRMS...Ch. 7 - Prob. 11SCG
Additional Engineering Textbook Solutions
Find more solutions based on key concepts
Would you select a decoder/driver with active-HIGH or active-LOW outputs to drive a common-cathode 7-segment LE...
Digital Fundamentals (11th Edition)
What are the design issues for character string types?
Concepts Of Programming Languages
Consider the adage Never ask a question for which you do not want the answer. a. Is following that adage ethica...
Experiencing MIS
Porter’s competitive forces model: The model is used to provide a general view about the firms, the competitors...
Management Information Systems: Managing The Digital Firm (16th Edition)
Using your text editor, enter (that is, type in) the C++ program shown in Display 1.8. Be certain to type the f...
Problem Solving with C++ (10th Edition)
Which of the following are illegal variable names in Python, and why? x 99bottles july2009 theSalesFigureForFis...
Starting Out with Python (3rd Edition)
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
- Write the commands to obtain the following information from the system catalog. Do not execute these commands unless your instructor specifically asks you to do so. a. List every column in the CONDO_UNIT table and its associated data type. b. List every table that contains a column named OWNER_NUM.arrow_forwardWrite the commands to obtain the following information from the system catalog. Do not execute these commands unless your instructor asks you to do so. a. List every table that you own. b. List every column in the ITEM table and its associated data type.arrow_forwardWhich of the following characters can be used in a table name? a. b. ( c. % d. !arrow_forward
- Update the VID_STATUS attribute of the VIDEO table using a subquery to set the VID_STATUS to OUT for all videos that have a null value in the DETAIL_ RETURNDATE attribute of the DETAILRENTAL table.arrow_forwardSelect all columns from the jobs table using an asterisk.2. Select only job title, min salary, and max salary columns from the jobs table.3. Select all columns from the d_partners table by writing out all of the columns inthe select statement.4. Using the d_partners table, display the id, and then concatenate the first_nameand last_name together with a space in between the two columns. Give theconcatenated column an alias of NAME. Be sure to use the optional ASkeyword.5. Using the job_grades table, show the grade level, and then subtract thelowest_sal from the highest_sal to show the amount of variance there is for eachgrade level. Give the calculated column an alias of Salary_Variance. Do notuse the optional AS keyword.6. Using the employees table, show an unduplicated list of department_id’s.7. Change the SELECT statement you built for question 4, adding a single columncalled Partner_Information that concatenates the partner’s expertise,auth_expense_amt, and manager_ id, so the…arrow_forward3. Using the EMPLOYEE table that already exists, use a subquery to insert the remaining rows from the EMPLOYEE table into the EMP_1 table. Remember, your subquery should only retrieve the columns needed for the EMP_1 table and only the employees shown in the figure.arrow_forward
- The option for referential integrity between the Owner table and the Pet table is restrict and an attempt is made to update the row for owner 005 (OwnerNum = 005) in the Owner table to become 500 (OwnerNum = 500). What is the result? (Use only the data in the question. Do not assume other data exists if it is not present in the tables below.) The primary keys are underlined. These are the OwnerNum column in the Owner table and the PetNumber in the Pet table. The foreign key is italicized. This is the OwnerNum column in the Pet table. Owner Table OwnerNum LastName FirstName Address City State Zip 005 Smith Jim 100 Main Dune FL 37465 009 Jones Jane 3245 West Sanford FL 32773 012 Johnson Frank 2 Century Easton FL 37256 020 Doe Sally 101 Basel Orange FL 30998 Pet Table PetNumber PetName DOB Species OwnerNum 100 Fido 1/1/2005 Canine 009 200 Ajax 12/3/2003 Canine 020 300 Sunshine…arrow_forwardModify the SELECT statement to select the title and release date of PG-13 movies that are released after February 1, 2008. Run your solution and verify the result table shows just the titles and release dates for The Dark Knight and Crazy Rich Asians. CREATE TABLE Movie ( ID INT AUTO_INCREMENT, Title VARCHAR(100), Rating CHAR(5) CHECK (Rating IN ('G', 'PG', 'PG-13', 'R')), ReleaseDate DATE, PRIMARY KEY (ID)); INSERT INTO Movie (Title, Rating, ReleaseDate) VALUES ('Casablanca', 'PG', '1943-01-23'), ('Bridget Jones\'s Diary', 'PG-13', '2001-04-13'), ('The Dark Knight', 'PG-13', '2008-07-18'), ('Hidden Figures', 'PG', '2017-01-06'), ('Toy Story', 'G', '1995-11-22'), ('Rocky', 'PG', '1976-11-21'), ('Crazy Rich Asians', 'PG-13', '2018-08-15'); -- Modify the SELECT statement:SELECT *FROM MovieWHERE ReleaseDate < '2000-01-01';arrow_forwardThe option for referential integrity between the Owner table and the Pet table is restrict. An attempt is made to delete the row for owner number 012 in the Owner table. What is the result? (Use only the data in the tables below. Do not assume other data exists if it is not present in the tables below.) The primary keys are underlined. These are the OwnerNum column in the Owner table and the PetNumber in the Pet table. The foreign key is italicized. This is the OwnerNum column in the Pet table. Owner Table OwnerNum LastName FirstName Address City State Zip 005 Smith Jim 100 Main Dune FL 37465 009 Jones Jane 3245 West Sanford FL 32773 012 Johnson Frank 2 Century Easton FL 37256 020 Doe Sally 101 Basel Orange FL 30998 Pet Table PetNumber PetName DOB Species OwnerNum 100 Fido 1/1/2005 Canine 009 200 Ajax 12/3/2003 Canine 020 300 Sunshine 6./12/2004 Feline 009 400 King…arrow_forward
- Task 10 Task 10: List all the tables contained within the system catalog, but only display the first 10 records with a TABLE_TYPE of SYSTEM VIEW. Results SELECT * FROM (SELECT TABLE_NAME FROM ALL_TABLES ORDER BY TABLE_NAME) WHERE ROWNUM < = 10; ERROR 1248 (42000) at line 1: Every derived table must have its own alias Task 11 Results Task 11: List all the columns contained within the system catalog, but only display the first 11 records that are in the KimTay TABLE_SCHEMA.arrow_forwardGiven the employee table and the data that it contains, and assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would create an error? a.) ALTER TABLE employee ALTER COLUMN email TYPE VARCHAR (100); b.) ALTER TABLE employee ALTER COLUMN city TYPE VARCHAR (100); c.) ALTER TABLE employee ALTER COLUMN postal_code TYPE VARCHAR (100); d.) ALTER TABLE employee ALTER COLUMN state VARCHAR (50);arrow_forwardTask 3: For each invoice, list the invoice number, invoice date, item ID, quantity ordered, and quoted price for each invoice line that makes up the invoice.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 PtrNp Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
- 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 LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
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
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
dml in sql with examples; Author: Education 4u;https://www.youtube.com/watch?v=WvOseanUdk4;License: Standard YouTube License, CC-BY