User Account

All Pages

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

  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

  Create report using wizard or based on sql query

Create a report using the wizard or based on an SQL query that that shows Courses that instructors are approved to teach. Show Course Number, First Name, and Last Name of Instructor.

  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.

  Write statement which creates table named part

Write the statement which creates table named Part, with Id field as an int idendity PK, a SupplierId int field, a Description string field of size 25, a Count int field, a Class string field

  Write a pl/sql block and declare a variable v_sal

Write a PL/SQL block and declare a variable  v_sal  of the type NUMBER. Include the following statement in the Execution section:

  Select distinct cmdclient

SELECT DISTINCT CMDclient.'Client Code SCA' as GuestCode, CMDextras.ArrivalDate as arr, CMDextras.DepartureDate as dep, CMDapr.FirstName as fname, CMDapr.Surname as lname

  Create report to display scr courses in alphabetical order

Create the detail report which will display all SCR courses in alphabetical order, with a course name and instructor name in the group header.

  Create a view named productsummary

Create a view named ProductSummary that uses the view you created in exercise 4. This view should return some summary information about each product.

  Sql statement which select names and owners of great danes

Write SQL statement which would select each of the following: names and owners of all Great Danes and all attributes of poodles whose balance is no greater than $50.

  Select statement to return one row

Write a SELECT statement that returns one row for each general ledger account number which contains three columns.

  Write a script that uses two variables

Write a script that uses two variables to store (1) the count of all of the products in the Products table and (2) the average list price for those products. If the product count is greater than or equal to

  Provide sql statements to create the database

Provide SQL statements to create the database and populate it with sample data (at least four rows per table).

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!

Submit Assignment

Academics

Major Subjects

Majors

Get In Touch

whatsapp: +1-415-670-9521

Phone: +1-415-670-9521

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

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