Database System Concepts
Database System Concepts
7th Edition
ISBN: 9780078022159
Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher: McGraw-Hill Education
Bartleby Related Questions Icon

Related questions

Q: Find the title name of the latest published book, the name of its author, and the publisher's name.…
A: 5. SELECT T.title_name, A.au_fname, A.au_lname, P.pub_name  FROM Titles T, Authors A, Publishers P,…
Q: What did each card cost? Show the card number, player last name, team name, price, price with 5%…
A:
Q: List the years and their total gross earnings (sum of earnings per year), but do not include gross…
A: ANSWER:-
Q: Create the following SQL Server queries that access the Northwind database. Place them in txt file…
A: This question comes from MySQL which is a topic in computer science. In this question we are going…
Q: Use SQL commands to finish the questions. Please show BOTH SQL commands and results in your answers.…
A: Hi.. Check below for answer query. Here i give a correct query if you already have table and…
Q: ts: You will need to add data to both tables. Remember that the movie table has th duction co., not…
A: Description:   1- If a user wants to insert records one by one then needs to use the following…
Q: What is the purpose of the WHERE clause in SQL? Which comparison operators can you use in a WHERE…
A: In SQL, WHERE clause is used to extract only particular field or row that satisfies a criteria from…
Q: ? List each order number and the date it shipped. SQL comm
A: Solution - In the given question, we have to write the SQL command for the given query.
Q: Text SQL queries for the following use cases: f)Which store location has the highest sales? g) How…
A: ANSWER:-
Q: Create the following table in your database with the following schema: Table: Products Column Name 1…
A: As per our guidelines, only 3 sub parts will be answered. So, please repost the remaining questions…
Q: Find the album title, band name, format, and total length of all albums released since January 1,…
A: SQL : In order to update, insert, delete, create and alter database tables and views, among other…
Q: Using SQL Show the distinct location_id for all the locations where the company has departments
A: SQL  SQL stands for Structured Query Language, and it is a programming language used to store,…
Q: Display customer ID, customer name, and the order ID and the order date of all orders for customer…
A: 1- In this assignment requires, select, where clause, aggregate function, group by, and order by.…
Q: Create a procedure, ADD_EMPLOYEE., to insert a new employee into the EMPLOYEES table. The procedure…
A: Call ADD_EMPLOYEE for the name Jane Harris in department 15. leaving other parameters with their…
Q: 2. Determine which orders haven’t yet shipped and the name of the customer who placed the order.…
A: According to the information given:- We have to follow the instruction in order to perform the…
Q: How do I list List the ISBN, title, author last name(s), retail price, and the promotional gift for…
A: We have to ginen an Er Diagram in this ER diagram 8 entities And each entities hold some properties.…
Q: Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle,…
A: SELECT Statement:The query begins with the SELECT keyword, indicating that we want to retrieve…
Q: 7. List all customers who were referred to the bookstore by another customer. List each customer’s…
A: The answer to the question is given below:
Q: Use SQL and TAL Distributors database to complete the following questions: List the customer…
A: SELECT statement :-    It is used to retrieve information from the table or database. The syntax for…
Q: Using the output shown in Figure P7.12 as your guide, generate a list of customer purchases,…
A: Here we have to write an SQL query to have the given table/list of data in the fig. P7.12, Key…
Q: Answer the query using SQL. For each department that controls more than 3 projects, retrieve the…
A: Introduction of DBMS: A database is a collection of data that are used to produce useful information…
Q: Write a SQL query. List names of all employees who have sold to customer Rachel Patterson (Note: You…
A: In this database problem, we are tasked with creating an SQL query that lists the names of all…
Q: Using the university schema, use SQL to do the following: For each student who has retaken a course…
A: SQL is also known as structured query language.
Q: Display the customer number, customer name, order number, and order date for each order. Sort the…
A: solution is given below
Q: write my sql , p/sql pr t-sql procedures to retrieve and output the item id, description, location,…
A: Your mysql query is given below.
Q: Determine the number of different customers who have placed an order for books written or cowritten…
A: 9. Determine the number of different customers who have placed an order for books written…
Q: List the names of the production companies and the titles of their movies. This time, include all…
A: Given that, The names of the tables are Movie and Production Assume that, The attributes of Movie…
Q: List the movie titles and the names of their production companies. Only include production companies…
A: SQL is the language that operates a databases, it includes database creations, deletions, fetching…
Q: e name of a field in an SQL query? When
A: How do you qualify the name of a field in an SQL query? When is it necessary to do so? give few…
Q: use sql to answer the following question Find all orders that have been issued after the order date…
A: SQL is an ANSI/ISO standard, there are various versions of the SQL language. Still, to be compliant…
Q: 3. Determine the amount of total profit generated by the book purchased on order 1002. Display the…
A: ANSWER:-
Q: SELECT distinct ID, name FROM student WHERE ID (SELECT ID FROM takes WHERE year < 2017); A
A: Given: Using the university schema, fill in the blank to complete the SQL query that finds the ID…
Q: Using a subquery in the FROM clause, find the average unit price of in-stock trucks of any…
A: Required SQL query is given below:
bartleby

Concept explainers

Multiple Table
In a database, a table is used to store data in the form of rows and columns. Rows are aligned horizontally and identified by one or more values in a specific column. Columns are vertically aligned and identified by a unique column name.
Question

Use SQL commands to finish the questions. Please include BOTH SQL commands and results in your answers.

1. List the first and last names of customers with credit limits of $1,000 or more.

2. List the order number for each order placed by customer number 125 on 11/15/2021.

3. List the item ID and description of each item that is not in category ‘HOR’.

4. List the item ID, description, and on-hand value for each item where on-hand value is at least $1,500. Assign the name ON_HAND_VALUE to the computed column. (Hint: On-hand value is the production of the units on hand and price.)

5. Use the IN operator to list the item ID and description of each item in category FSH or BRD or CAT.

6. Find the total of the balances for all customers represented by sales rep 10 with balances that are less than their credit limits.

7. List the item ID, description, and on-hand value of each item whose number of units on hand is more than the average number of units on hand for all items. (Hint: Use a subquery.)

8. What is the price of the least expensive item in the database?

9. List the sum of the balances of all customers for each sales rep but restrict the output to those sales reps for which the sum is more than $150. Order the results by sales rep ID.

10. List the item ID of any item with an unknown description.

ITEM ITEM_ID AD72 BC33 CA75 DT12 FM23 FS39 FS42 KH81 LD14 LP73 PF19 QB92 SP91 UF39 WB49 DESCRIPTION Dog Feeding Station Feathers Bird Cage (12x24x18) Enclosed Cat Litter Station Dog Toy Gift Set Fly Mask with Ears Folding Saddle Stand Aquarium (55 Gallon) Wild Bird Food (25 lb) Locking Small Dog Door Large Pet Carrier Pump & Filter Kit Quilted Stable Blanket Small Pet Carrier Underground Fence System Insulated Water Bucket FIGURE 1-2 Sample data for KimTay Pet Supplies 14216 14219 14222 14224 14228 14231 14233 14237 INVOICES INVOICE_NUM INVOICE_DATE ON HAND 12 10 15 27 CUST_ID 11/15/2021 125 11/15/2021 227 11/16/2021 294 182 11/16/2021 11/18/2021 435 11/18/2021 125 11/18/2021 435 11/19/2021 616 41 12 5 24 14 23 5 32 18 7 34 FIGURE 1-2 Sample data for KimTay Pet Supplies (Continued) CATEGORY DOG BRD CAT DOG HOR HOR FSH BRD DOG DOG FSH HOR CAT DOG HOR 14216 14219 14219 14222 14224 14228 14228 14231 14233 14233 14233 14237 LOCATION B B C LD14 KH81 FS42 PF19 UF39 KH81 QB92 WB49 LP73 B C с A С A B A с B A Copyright 2021 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). itorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it C Introduction to Kim Tay Pet Supplies and StayWell Student Accommodation Databases INVOICE_LINE INVOICE_NUM ITEM_ID QUANTITY CA75 AD72 DT12 PRICE $79.99 $79.99 3 2 $39.99 $39.99 4 1 4 1 1 2 1 4 4 3 $24.95 $39.99 $124.99 $19.99 $49.99 $59.99 $74.99 $119.99 $39.99 $199.99 $79.99 QUOTED_PRICE $37.99 $79.99 $39.99 $47.99 $18.99 $124.99 $74.99 $189.99 $19.99 $109.95 $74.95 $54.95 5
expand button
Transcribed Image Text:ITEM ITEM_ID AD72 BC33 CA75 DT12 FM23 FS39 FS42 KH81 LD14 LP73 PF19 QB92 SP91 UF39 WB49 DESCRIPTION Dog Feeding Station Feathers Bird Cage (12x24x18) Enclosed Cat Litter Station Dog Toy Gift Set Fly Mask with Ears Folding Saddle Stand Aquarium (55 Gallon) Wild Bird Food (25 lb) Locking Small Dog Door Large Pet Carrier Pump & Filter Kit Quilted Stable Blanket Small Pet Carrier Underground Fence System Insulated Water Bucket FIGURE 1-2 Sample data for KimTay Pet Supplies 14216 14219 14222 14224 14228 14231 14233 14237 INVOICES INVOICE_NUM INVOICE_DATE ON HAND 12 10 15 27 CUST_ID 11/15/2021 125 11/15/2021 227 11/16/2021 294 182 11/16/2021 11/18/2021 435 11/18/2021 125 11/18/2021 435 11/19/2021 616 41 12 5 24 14 23 5 32 18 7 34 FIGURE 1-2 Sample data for KimTay Pet Supplies (Continued) CATEGORY DOG BRD CAT DOG HOR HOR FSH BRD DOG DOG FSH HOR CAT DOG HOR 14216 14219 14219 14222 14224 14228 14228 14231 14233 14233 14233 14237 LOCATION B B C LD14 KH81 FS42 PF19 UF39 KH81 QB92 WB49 LP73 B C с A С A B A с B A Copyright 2021 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). itorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it C Introduction to Kim Tay Pet Supplies and StayWell Student Accommodation Databases INVOICE_LINE INVOICE_NUM ITEM_ID QUANTITY CA75 AD72 DT12 PRICE $79.99 $79.99 3 2 $39.99 $39.99 4 1 4 1 1 2 1 4 4 3 $24.95 $39.99 $124.99 $19.99 $49.99 $59.99 $74.99 $119.99 $39.99 $199.99 $79.99 QUOTED_PRICE $37.99 $79.99 $39.99 $47.99 $18.99 $124.99 $74.99 $189.99 $19.99 $109.95 $74.95 $54.95 5
CUSTOMER CUST_FIRST_ ID 126 Joey 182 227 294 375 492 NAME 314 Tom 543 616 Billy Sandra 435 James 05 10 15 20 Melanie Samantha Smith Elmer Angie Sally 721 Leslie 795 Randy LAST_ NAME Smith Rufton Pincher Rascal Jackson Gonzalez Jackson Cruz ADDRESS 17 Fourth St 21 Simple Cir Smith 53 Verde Ln 22 Jackson Farm Rd Hendricks 27 Locklear In 14 Rock Ln 1 Rascal Farm Rd 199 18th Ave 123 Sheepland Rd Blacksmith 75 Stream Rd SALES REP REP_ID FIRST NAME LAST NAME ADDRESS Susan Richard Garcia Miller Smith Jackson Donna Daniel CITY Cody Garland 42 Blackwater Way 16 Rockway Rd Wapiti Powell Ralston Cody Elk Butte Garland Powell Ralston Elk Butte Cody STATE POSTAL EMAIL WY WY WY WY WY WY WY WY WY WY 82414 82435 82440 82440 82414 82433 82450 82435 82440 82440 WY 82433 WY 82414 CITY 42 Mountain Ln Cody WY 87 Pikes Dr Ralston WY 312 Oak Rd Powell WY 19 Lookout Dr Elk Butte WY jsmith 17@ example.com billyruff@ example.com spinch2@ example.com ssmith5@ example.com trascal3@ example.com mjackson5@ example.com jgonzo@ example.com ejackson4@ example.com BALANCE CREDIT_REP LIMIT ID $80.68 $500.00 05 Ismith12@ example.com $43.13 $750.00 10 $156.38 $500.00 15 $58.60 $17.25 $250.00 15 $500.00 10 $252.25 $250.00 05 $230.40 $1,000.00 15 $45.20 $500.00 10 ahendricks7@ $315.00 $750.00 05 example.com scruz5@ example.com $8.33 $500.00 15 $166.65 $1,000.00 10 rblacksmith6@ $61.50 example.com STATE POSTAL CELL PHONE 82414 307-824-1245 82440 82440 82433 307-406-4321 307-982-8401 307-883-9481 $500.00 05 COMMISSION RATE $12,743.16 0.04 $20,872.11 0.06 $14,912.92 0.04 $0.00 0.04
expand button
Transcribed Image Text:CUSTOMER CUST_FIRST_ ID 126 Joey 182 227 294 375 492 NAME 314 Tom 543 616 Billy Sandra 435 James 05 10 15 20 Melanie Samantha Smith Elmer Angie Sally 721 Leslie 795 Randy LAST_ NAME Smith Rufton Pincher Rascal Jackson Gonzalez Jackson Cruz ADDRESS 17 Fourth St 21 Simple Cir Smith 53 Verde Ln 22 Jackson Farm Rd Hendricks 27 Locklear In 14 Rock Ln 1 Rascal Farm Rd 199 18th Ave 123 Sheepland Rd Blacksmith 75 Stream Rd SALES REP REP_ID FIRST NAME LAST NAME ADDRESS Susan Richard Garcia Miller Smith Jackson Donna Daniel CITY Cody Garland 42 Blackwater Way 16 Rockway Rd Wapiti Powell Ralston Cody Elk Butte Garland Powell Ralston Elk Butte Cody STATE POSTAL EMAIL WY WY WY WY WY WY WY WY WY WY 82414 82435 82440 82440 82414 82433 82450 82435 82440 82440 WY 82433 WY 82414 CITY 42 Mountain Ln Cody WY 87 Pikes Dr Ralston WY 312 Oak Rd Powell WY 19 Lookout Dr Elk Butte WY jsmith 17@ example.com billyruff@ example.com spinch2@ example.com ssmith5@ example.com trascal3@ example.com mjackson5@ example.com jgonzo@ example.com ejackson4@ example.com BALANCE CREDIT_REP LIMIT ID $80.68 $500.00 05 Ismith12@ example.com $43.13 $750.00 10 $156.38 $500.00 15 $58.60 $17.25 $250.00 15 $500.00 10 $252.25 $250.00 05 $230.40 $1,000.00 15 $45.20 $500.00 10 ahendricks7@ $315.00 $750.00 05 example.com scruz5@ example.com $8.33 $500.00 15 $166.65 $1,000.00 10 rblacksmith6@ $61.50 example.com STATE POSTAL CELL PHONE 82414 307-824-1245 82440 82440 82433 307-406-4321 307-982-8401 307-883-9481 $500.00 05 COMMISSION RATE $12,743.16 0.04 $20,872.11 0.06 $14,912.92 0.04 $0.00 0.04
Expert Solution
Check Mark
Step 1

SQL :

SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. Here are some common SQL commands:

 

  • SELECT: This command is used to retrieve data from one or more tables in a database.
  • INSERT: This command is used to add new data to a table.
  • UPDATE: This command is used to modify existing data in a table.
  • DELETE: This command is used to remove data from a table.
  • CREATE TABLE: This command is used to create a new table in a database.
  • DROP TABLE: This command is used to delete a table from a database.
  • ALTER TABLE: This command is used to modify the structure of an existing table.
  • CREATE INDEX: This command is used to create an index on one or more columns of a table to speed up data retrieval.
  • GROUP BY: This command is used to group rows in a table based on one or more columns.
  • ORDER BY: This command is used to sort the result set of a query in ascending or descending order based on one or more columns.
  • JOIN: This command is used to combine rows from two or more tables based on a related column between them.
  • DISTINCT: This command is used to retrieve only unique values from a column.
  • WHERE: This command is used to filter data based on a specified condition.
  • HAVING: This command is used to filter the result set of a GROUP BY query based on a specified condition.
  • LIMIT: This command is used to limit the number of rows returned by a query.

 

 

bartleby

Trending nowThis is a popular solution!

bartleby

Step by stepSolved in 2 steps

See solution
Check out a sample Q&A here
Blurred answer
Still need help?
Follow-up Questions
Read through expert solutions to related follow-up questions below.
Follow-up Question

Please solve question no. 10 including screenshots of SQL results.

Solution
Bartleby Expert
by Bartleby Expert
SEE SOLUTION
Follow-up Question

Need help with solutions to questions 7 to 9.

Solution
Bartleby Expert
by Bartleby Expert
SEE SOLUTION
Follow-up Question

Only answered 3 questions. The other 7 are missing.

Solution
Bartleby Expert
by Bartleby Expert
SEE SOLUTION
Follow-up Questions
Read through expert solutions to related follow-up questions below.
Follow-up Question

Please solve question no. 10 including screenshots of SQL results.

Solution
Bartleby Expert
by Bartleby Expert
SEE SOLUTION
Follow-up Question

Need help with solutions to questions 7 to 9.

Solution
Bartleby Expert
by Bartleby Expert
SEE SOLUTION
Follow-up Question

Only answered 3 questions. The other 7 are missing.

Solution
Bartleby Expert
by Bartleby Expert
SEE SOLUTION
Knowledge Booster
Background pattern image
Computer Science
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
Recommended textbooks for you
Text book image
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Text book image
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Text book image
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
Text book image
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education
SEE MORE TEXTBOOKS