Prompt: You work in the Procurement Department, and you need a list of select items carried for sale by COB 204 Company and details about the supplier and current stock balance. Develop a query that will produce output with the following attributes in this order: product name, supplier name, and the quantity of product in stock. Restrict the query to include only those products supplied by a supplier whose company name starts with the letter E. Additionally, restrict the query to only those products that currently have a quantity in stock that is greater than or equal to 50. Finally, sort the query output alphabetically by the product name. SQL Statement (case sensitive, must reflect what was used in SQL Browser to execute the prompt): SELECT Product.ProdName, Supplier.CompanyName, Product. FROM Product Supplier ON Product. =Supplier. WHERE Supplier. AND Product. 50 ORDER BY Product.ProdName; Query Result Count (enter number only, ex. 12): Result: rows

Prompt: You work in the Procurement Department, and you need a list of select items carried for sale by COB 204 Company and details about the supplier and current stock balance. Develop a query that will produce output with the following attributes in this order: product name, supplier name, and the quantity of product in stock. Restrict the query to include only those products supplied by a supplier whose company name starts with the letter E. Additionally, restrict the query to only those products that currently have a quantity in stock that is greater than or equal to 50. Finally, sort the query output alphabetically by the product name. SQL Statement (case sensitive, must reflect what was used in SQL Browser to execute the prompt): SELECT Product.ProdName, Supplier.CompanyName, Product. FROM Product Supplier ON Product. =Supplier. WHERE Supplier. AND Product. 50 ORDER BY Product.ProdName; Query Result Count (enter number only, ex. 12): Result: rows

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Task 2: The Driver Relationship team wants to ensure that the all driving license IDs in the active drivers table have the length of 7. Alter the ACTIVE_DRIVERS table created in Chapter 8, Activity 1 to check the length of the DRIVER_DRIVING_LICENSE_ID. Add a CHECK to the ACTIVE_DRIVERS table to enforce a driver license length of 7. Test Query: SELECT DISTINCT(constraint_name) FROM information_schema.check_constraints WHERE constraint_schema = 'InstantRide' AND constraint_name = 'ACTIVE_DRIVERS_chk_1' Expected Results CONSTRAINT_NAME ACTIVE_DRIVERS_chk_1
Write a query that uses INNER JOIN TO connect the EMPLOYEE, EMPPROJACT, PROJACT and PROJECT tables. Include the Project number , Department number, Project start and end date and AC STAFF WHERE They belong to department D11 , Salary is more than or equal to 65 percent of $15,000 AND Salary is less than or equal to 130 percent of $40,000
Reference table 6-28. Include both SQL commands and results/answers in your answers. In the NONCAT table, increase the price of each item in category BRD by five percent. (Hint: Multiply each price by 1.05.) In the NONCAT table, increase the length of the DESCRIPTION column to 40 characters.
Management is considering closing on Sundays and would like to have a feeling for the kinds of customers that come to the dealership on Sunday. Create a totals query that shows the count of encounters on the Sundays of July 2015—grouped by the credit description held by the customer of the encounter. The Sundays in July of 2015 are the 5th, 12th, 19th, and 26th. Your answer should have two columns (CreditDescription and encounter_count) and should be sorted alphabetically by credit description. This is what Ive been working with and doesn't give me results, just error messages. SELECT R.CreditDescription, E.CustomerID FROM Encounter E JOIN Customer C ON E.CustomerID = C.CustomerID JOIN CreditRating R ON C.CreditID = R.CreditID WHERE E.EncDate = STR_TO_DATE('2015-07-05','2015-07-12','2015-07-19','2015-07-26'); ORDER BY creditrating.creditid, ASC; creditrating creditid creditdescription minfico maxfico comments customer customerid cfirstname clastname…
Create a query to find the balance characteristics for all customers, including the total of the outstanding balances. The results of this query are shown in Figure P7.22. make sure the decimal are the same
Create a View to show the city name of any location that has a department. Only show the city name once in the results. You must use a non-correlated subquery.Call this view: Department_LocationsInclude a second query to display from the View after it is created. Refer to the attached for the table.
1. Write and execute a query that will end the employee project assignment for Mark Jones and the DT Work Order – Customization project. Give the row an end date of August 1, 2017.
Given the table PRODUCT(ProductID, ProductName, ProductClass, ProductWeight, ProductStyle, ProductColor, ProductPrice). Note: ProductWeight and ProductPrice are numeric fields, and the other attributes are character fields, i.e., store text. Write a SQL query to display the product class whose average price is at least 50
Question #10.Write a query to retrieve invoice_number, invoice_date, and “Balance Due” from the invoices table.Balance Due can be calculated by subtracting payment and credit from the original invoicedamount. Make sure to only select rows with positive “Balance Due” and sort the results basedon Balance Due. Refer to the sample result screen below.
Rewrite the following query using a join statement (no subqueries). (0.5) SELECT productid, productname, productprice FROM product WHERE productid IN (SELECT productid FROM soldvia GROUP BY productid HAVING SUM(noofitems) > 3);
The Binder Prime Company wants to recognize the employee who sold the most of its products during a specified period. Write a query to display the employee num-ber, employee first name, employee last name, email address, and total units sold for the employee who sold the most Binder Prime brand products between November 1, 2015, and December 5, 2015. If there is a tie for most units sold, sort the output by employee last name
Problem 54 One of the purchasing managers is interested in the impact of product prices on the sale of products of each brand. Write a query to display the brand name, brand type, average price of products of each brand, and total units sold of products of each brand. Even if a product has been sold more than once, its price should only be included once in the calculation of the average price. However, you must be careful because multiple products of the same brand can have the same price, and each of those products must be included in the calculation of the brand’s average price. Sort the result by brand name (Figure P7.54).