![Computer Science: An Overview (12th Edition)](https://www.bartleby.com/isbn_cover_images/9780133760064/9780133760064_largeCoverImage.gif)
Concept explainers
Answer Problem 13 using SQL.
PROBLEM 13
13. Using the commands SELECT, PROJECT, and JOIN, write a sequence of instructions to answer each of the following questions about parts and their manufacturers in terms of the following
Part relation
PartName | Weight |
Bolt 2X | 1 |
Bolt 2Z | 1.5 |
Nut V5 | 0.5 |
Manufacturer relation
CompanyName | PartName | Cost |
Company X | Bolt 2Z | .03 |
Company X | Nut V5 | .01 |
Company Y | Bolt 2X | .02 |
Company Y | Nut V5 | .01 |
Company Y | Bolt 2Z | .04 |
Company Z | Nut V5 | .01 |
- a. Which companies make Bolt 2Z?
- b. Obtain a list of the parts made by Company X along with each part’s cost.
- c. Which companies make a part with weight 1?
![Check Mark](/static/check-mark.png)
Learn your wayIncludes step-by-step video
![Blurred answer](/static/blurred-answer.jpg)
Chapter 9 Solutions
Computer Science: An Overview (12th Edition)
Additional Engineering Textbook Solutions
C Programming Language
Introduction to Java Programming and Data Structures, Comprehensive Version (11th Edition)
Database Concepts (7th Edition)
Starting Out with C++ from Control Structures to Objects (8th Edition)
Starting Out with Java: From Control Structures through Data Structures (4th Edition) (What's New in Computer Science)
Starting Out with C++ from Control Structures to Objects (9th Edition)
- Write the SQL code to calculate the ASSIGN_CHARGE values in the ASSIGNMENT table in the Ch07_ConstructCo database. (See Figure P7.1.) Note that ASSIGN_CHARGE is a derived attribute that is calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS.arrow_forwardIn SQL This sample database consists of the following tables(see image for tables and there is one more at the bottom in this text):• Customers: stores customer’s data• Products: stores a list of scale model cars• ProductLines: stores a list of product line categories• Orders: stores sales orders placed by customers• OrderDetails: stores sales order line items for each sales order• Payments: stores payments made by customers based on their accounts• Employees: stores all employee information as well as the organization structuresuch as who reports to whom• Offices: stores sales office data Write SQL code for the following:We want to add a new sale order for the customer (customerNumber = 145) in thedatabase. The steps of adding a sale order are described as follows:(1) Get latest sale order number from “orders” table, and use the next sale ordernumber as the new sale order number(2) Insert a new sale order into “orders” table for the customer (customerNumber =145). For this order, the…arrow_forwardIn SQL This sample database consists of the following tables(see image for tables and there is one more at the bottom in this text):• Customers: stores customer’s data• Products: stores a list of scale model cars• ProductLines: stores a list of product line categories• Orders: stores sales orders placed by customers• OrderDetails: stores sales order line items for each sales order• Payments: stores payments made by customers based on their accounts• Employees: stores all employee information as well as the organization structuresuch as who reports to whom• Offices: stores sales office data Write SQL code for the following:We want to add a new sale order for the customer (customerNumber = 145) in thedatabase. The steps of adding a sale order are described as follows:(1) Get latest sale order number from “orders” table, and use the next sale ordernumber as the new sale order number(2) Insert a new sale order into “orders” table for the customer (customerNumber =145). For this order, the…arrow_forward
- sql queries Employees(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE,JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID,DEPARTMENT_NAME)Department(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)Location(LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) Write a query in SQL to display the Entire data of All those employees whoseDEPARTMENT_ID is greater than the DEPARTMENT_ID of all those employees who earn morethan 50,000 per month Write a query in SQL to display the minimum, maximum salary and DEPARTMENT_NAMEof all those departments whose minimum salary is greater than 4000 in ascending order.arrow_forwardSQL QUERIES Employees(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE,JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID,DEPARTMENT_NAME)Department(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)Location(LOCATION_ID,STREET_ADDRESS,POSTAL_CODE,CITY,STATE_PROVINCE,COUNTRY_ID) Write a query in SQL to display the DEPARTMENT_NAME of all those Departments whoseDEPARTMENT_ID is greater than the DEPARTMENT_ID of Physics department. Write a Query in SQL to show the FIRST_NAME of all those employees whose salary is lessthan the salary of all those employees whose MANAGER_ID is 101 or 102.arrow_forwardQUESTION 2. Write a PL/SQL PROCEDURE that can list the Top-N customers (id and name) and the amounts spent in a specified year together with their rank. The customer rank can be obtained from the function created in the previous question. The total purchase is calculated for each customer using the quantity and price columns. The total purchased is sorted from the highest to the lowest. The procedure will have two input parameters: year (NUMBER) and TOP_N (NUMBER). Hint: • use ONE EXPLICIT CURSOR (corresponding to one select statement). • Us the topN technique (from clause subquery) and rownum to limit the customers. Expected Output for Top 3 customers in 2018 using: execute topSpenders (2018. 3) 3 Gary Jenkins s422.64 Excellent 45 Fred Fontain $359.21 Excellent 16 Ralph Foster $351.64 Excellent Expected Output for Top 10 customers in 2018 using: begin topSpenders(2018,10 end: 3 Gary Jenkins s422.64 Excellent 45 Fred Fontain $359.21 Excellent 16 Ralph Foster $351.64 Excellent 81…arrow_forward
- Write the appropriate SQL query for each of the following questions: Display the list of students who are involved in the IT club Display the details of all the activities that have taken place in 'Salalah Hall'arrow_forwardPart II (50 points, each problem is worth 5 points) Write SQL queries for the following database: id Customers name email id Categories name Ho Ho 8 Orders id customer_id B id Products category_id name price но HO Order_Product id order_id product_id quantity 1. Display all customers whose email is null or ends with 'fiu.edu' or 'gmail.com' 2. Display the number of orders placed by a customer whose name is ‘Jason Bourne' (assume there is only one customer with this name). 3. Insert a new product (category_id = 1, name = 'apple', price = 3.00) into the product table. Assume Products.id has a default value. 4. Create the Order_Product table as follows: id is the primary key and its data type is serial, quantity is an integer which doesn't allow null, order_id and product_id are foreign keys and will be set to null on delete. 5. Display the average price of all the products. For example, there are only three products in the database with a price of 7, 8 and 9. Then the average price would…arrow_forwardSQL Statement: SELECT FROM Shippers; Edit the SQL Statement, and click "Run SQL" to see the result. Show Transcribed Text Get your own SQL server Your Database: Tablenames Records Customers Categories Employees OrderDetails Orders Products Shippers Suppliers 91 8 10 518 196 77 3 29 1. Prepare a list of orders by Shipper showing the Shipper ID, Order ID, Product ID and Quantity. List should be in shipper orders 1. Prepare a list of orders by employee showing the employee ID, Order ID, Product ID and Quantity. List should be in employee order 1. Prepare a list of orders by customers showing the customer ID, Order ID, Product ID and order quantity. List should be in customer order Please give SQL Statementsarrow_forward
- Write the code steps to perform the following within SQL Create a database bearing the name of the department in which you study. This database includes three tables. The first is for teachers. The following columns ( id, address, name, age ) contain a specific column value that cannot be empty in this table The second table is for employees, holds the following columns (, Salary id, address, name, age) the value of a specified column as the primary key of this table. The third and last table is for students and includes the following columns (id, address, name, age) and a specific column value that cannot be empty Fill in the tables with data and do the following:1. Arrange employees in descending order . 2. Find the average ages of the people in all the tables.3. Merging the first table for teachers with the second table for employees. If the number of elements of the combined table is greater than 15 or less than 15, write a message explaining thatarrow_forwardBOOKS BookID BookName YearOfPublication AuthorID Price Bk1 Databases 2007 A1 1000 Bk2 Programming 2020 A2 500 … …. From the tables given above, write the following in form of relational algebra Selects rows from the table Books where Book Name is ‘History’ and 'price' is 1000 or those books published after 2015arrow_forwardSchema: Frequents(kid, store) Sells(store, candy) Likes(kid, candy) d) Use SQL to list the names of all the candies that are liked by at least one kid, along with the names of the stores that sell them. Sort the result in alphabetical order by candy name.arrow_forward
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningDatabase 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 LearningA Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
![Text book image](https://www.bartleby.com/isbn_cover_images/9781305627482/9781305627482_smallCoverImage.gif)
![Text book image](https://www.bartleby.com/isbn_cover_images/9781285196145/9781285196145_smallCoverImage.gif)
![Text book image](https://www.bartleby.com/isbn_cover_images/9781111527273/9781111527273_smallCoverImage.gif)