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

  Can we offer an example of a query

Can we offer an example of a Query in which we can use a parentheses to affect the order of execution in which the WHERE Clause has both AND and OR Logical Operators?

  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

  How to select the primary key

How to select the primary key from the candidate keys? How do foreign keys relate to candidate keys? provide examples from either your workplace or class assignments

  Write queries in sql and relational algebra

Write given queries in SQL and relational algebra: Determine cities from which one can fly to Toronto with one change of planes, and using same type of the aircraft for both legs of trip.

  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 programme in oracle to make employee table

List the employee_id and employee_name of the employees form the l_employees table with the top 4 credit limits.

  Describe the meaning of keyword exists and not exists

Describe the meaning of the keyword EXISTS. Describe how any and all relate to EXISTS and NOT EXISTS. Write a query which will display names of any customers who are interested in all artists.

  Creating sql script queries

Get the movie title for all movies that are comedy or horror, rename the output attribute name to be "Happily Scared"

  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."

  Write sql statement-display major of student-no duplications

Write a SQL statement to display the Major of the student with no duplications. Write a SQL statement to display the First and Last name of students who live in the Zip code 88888.

  Trigger named products_insert

Create a trigger named Products_INSERT that inserts the current date for the DateAdded column of the Products table if the value for that column is null. Test this trigger with an appropriate INSERT statement.

  Write sql statement to add new record to part table

Write SQL statement which creates the stored procedure which adds new record to the Part table, and returns value of newly created PartID PK value in out parameter.

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