Write a sql query for each of the following requirement

Assignment Help PL-SQL Programming
Reference no: EM13324544

Homework Assignment Three

Consider the relational schema of (part of) a firm database below. Write a SQL query for each of the following requirement. Run the queries using the database uploaded with the assignment and display the results.

1.     List the Vend_ID, Vend_Name, Vend_Contact of all vendors whose LAST name begins with 'C'. List them in descending order of Vend_City. (Query should return 2 rows)

2.      List the vendor city and the number of vendors in each city where the number of vendors is greater than 2.  Use the alias TotalVendors for the number of vendors.(Query should return 2 rows)

1760_Assignment.png

3.       Display Ord_ID, Ord_Date of all orders where condition (c) is met and simultaneously either one of conditions (a) and (b) are met

a)       Vend_ID starts with the letter "P"

b)      Ord_Status is "Rec"

c)       Ord_Tot_Cost is greater than or equal to $2000 but less   than or equal to $5500

(Query should return 4 rows)

4.       Display the loc_description and item_id of all items whose storage quantity is greater than 15. (Query should return 4 rows)

5.       Display the Rep_ID, Rep_Date and Item_ID of all items where either the Rep_Cost is less than $50 or the Item_Buy_Datedate is between 1/1/1910 and 1/1/1915.(Query should return 4 rows)

 6.       Display the ID and name of vendors who have NOT placed any order.(Query should return 3 rows)

7.       Display the item ID, store quantity and location description of the items having the lowest store quantity. Note: there are three such items.(Query should return 3 rows)

8.      Display the vendor ID and vendor name of the items whose item quantity is the largest.(Query should return 2 rows)

9.       Display the item ID and item quantity of all items whoseitem quantity is less than the average quantity. Also display the average quantity.(Query should return 6 rows)

10.    How many times the vendor has placed an order for the item ID = 1 (rename it as NUMORD)? Also display the vendor name and the location description for the item_ID = 1? Write a single query to obtain the results.(Query should return 1 row)

Reference no: EM13324544

Questions Cloud

Regulatory requirements exist should articulate with the law : Policies in those areas where regulatory requirements exist should articulate with the law. Explain the process you would follow to ensure this, using the example of privacy (privacy policy and privacy laws).
Example of social constructivism in the classroom : Which of the following is not one of the 12 principles of Brain Based Learning. Which is not a good example of Social Constructivism in the classroom? "Learning is the process of connecting symbols in a meaningful and memorable way" is best described..
What is the work done by the electrical force : Suppose you lower the object a distance 0.246 m (in the same direction the field is pointing). What is the work done by the electrical force
What are two key elements of a sis : CI-3110 - Give key reasons for the BP Texas City refinery explosion. What are recommendations to prevent further catastrophic event and what does SIS include
Write a sql query for each of the following requirement : MIS 4300 / ACCT 4300 Database Fundamentals Spring 2014. How many times the vendor has placed an order for the item ID = 1 (rename it as NUMORD)?
With what speed must the animal leave the ground : The best leaper in the animal kingdom is the puma, which can jump to a height of 3.7 m when leaving the ground at an angle of 45°. With what speed must the animal leave the ground
What is consolidated net income attributable : What is consolidated net income attributable to the controlling interest?
Find the mass of the second particle : Two equally charged particles, held 4.7 × 10-3 m apart, are released from rest. The initial acceleration of the first particle is observed to be 7.2 m/s2, find the mass of the second particle
Sniffing out fraudulent activity : What other data points should auditors look out for when sniffing out fraudulent activity?

Reviews

Write a Review

PL-SQL Programming Questions & Answers

  Create a database model

Create a database model and Submit the table creation statements for the Database Model.

  Write pl-sql procedures and functions

Write PL/SQL procedures and functions to populate and query that database

  Sql questions

Write a query to display using the employees table the EMPLOYEE_ID, FIRST_NAME, LAST_NAME and HIRE_DATE of every employee who was hired after to 1 January, 1995.

  Run the lab_03_01.sql script

Run the lab_03_01.sql script in the attached file to create the SAL_HISTORY table. Display the structure of the SAL_HISTORY table.

  Write sql queries

Write a query to display the last name, department number, and salary of any employee whose department number and salary both match the department number and salary of any employee who earns a commission.

  Explaining sql insert statement to insert new row in cds

Write down a SQL insert statement to insert new row in "CDS" table.

  Write down name of actors in ascending order

Write down actors (or actress, your choice, but not both) who have won at least two (2) Academy Awards for best actor/actress. Provide the actor name, movie title & year. Order the result by actor name."

  What is an sql injection attack

What is an SQL injection attack? Explain how it works, and what precautions must be taken to prevent SQL injection attacks.What are two advantages of encrypting data stored in the database?

  Determine resonant frequency in series rlc resonant circuit

Given the series RLC resonant circuit in the figure, operating at variable frequency, determine: The resonant frequency ω o ,  The circuit’s quality factor Q , The cut-off frequencies, f 1  & f 2  and the bandwidth BW

  Query that uses cube operator to return lineitemsum

Write summary query which uses CUBE operator to return LineItemSum (which is the sum of InvoiceLineItemAmount) group by Account(an alias for AccountDesciption).

  Query to show customers were missing for existing orders

As DBA, your manager called a meeting and asked why there are so many orders for customers that don't exist in the customer table. Write query which would shows which customers were missing for existing orders. Use a join or a subquery.

  Sql query into a relational algebra statement

Turn this SQL query into a relational algebra statement? SELECT Request.reqfor, Ordering.invamt, Ordering.invnbr, Ordering.invdat

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd