Write a query that uses the union operator, Database Management System

Write a Query that uses the UNION Operator

1. Use the Software Experts database to:  

a. Display the first and the last name of all consultants who worked on any projects for Morningstar Bank.

b. Display the name of each project that consultant Mark Myers has ever worked on, the name of the project's client, and the number of hours that Mark contributed to each project.

 2.  Use the Clearwater Traders database to:

 a. Display each source description from the ORDER_SOURCE table, along with a count of the total number of orders that have been derived from that source. Hint. You will need to use an outer join.

b. List the first and last name, order ID, source description, and order date of every customer who has ever placed an order using the same order sources as customer Alissa Chang. Use a query that contains a sub query.

c. List the inventory ID, item description, item size, color, and shipment quantity for every item that was received on the same date as inventory item 24 in shipment ID 5. Use a query that contains a sub query, and do not include the information for inventory item 24 in the output.

 3.   Use the Northwoods University database to:  

a. List the building code and room of every record in the LOCATION table, and also list the term description, call ID, section number, day, and time of every course ever taught at that location. Format the TIME field so that values appear at times not dates. Hint. You will need to use multiple outer joins.

b. List the first and last names of all faculty members who have ever taught any of the same courses as faculty member Kim Cox or who have ever taught a class in the same room as Kim. Use a query with multiple sub queries.

 4.  Use the Software Experts database to:  

a. List all of the skill descriptions in the SKILL table, as well as the ID of every consultant who is certified for a particular skill. Use an outer join so that if no consultants are certified for a particular skill, the query retrieves the NULL values for the consultant ID value. Hint. Create a view named CERTIFICATION_VIEW that contains IDs (consultant id and skill id) for consultant/skill combinations that are certified, and then perform the outer join using that view.

b. Display the name of each project that consultant Mark Myers has ever worked on, and the first and last name of the consultant who was the manager of the project. Concatenate the manager fields so that the values appear like "Sara Carlson", and assign the column heading "Project Manager" to the manager name output column. Hint. Nest the CONCAT function.

a. Write a query that uses the UNION operator to list the buildings code and room of every location at Northwoods University that is either currently in use as a faculty office or in use as a classroom during the Summer 2007 term.

b. Write a query that uses the INTERSECT operator to retrieve the skill descriptions that are the same for Software Experts consultants Sarah Carlson and Mark Myers.

c. Write a query that uses the MINUS operator to retrieve the skill descriptions that represent the difference between the skills possessed by Software Experts consultant Sara Carlson and the skills possessed by consultant mark Myers.

Posted Date: 3/5/2013 8:17:01 AM | Location : United States







Related Discussions:- Write a query that uses the union operator, Assignment Help, Ask Question on Write a query that uses the union operator, Get Answer, Expert's Help, Write a query that uses the union operator Discussions

Write discussion on Write a query that uses the union operator
Your posts are moderated
Related Questions
Define the five basic operators of relational algebra? Five basic operators of relational algebra are: 1. Union (∪∪∪∪) - Selects tuples which are in either P or Q or in both

Explain mechanism of nested queries? SQL gives a mechanism for nesting subqueries. A subquery is a select from where expression that is nested within one more query. A common u

Differentiate between Procedural and non procedural languages ? Procedural and non procedural languages - A procedural language specifies the operations to be performed on th

What is meant by buffer blocks and disk buffer   The blocks residing temporarily in main memory are referred to as buffer blocks. The area of memory where blocks reside t

The need for three level architecture The aim of the three level architecture is to divide each user's view of the database from the way the database is physically shown.

I need an ERD created for any original database(can be on anything). It needs to be 3NF. I then need all the CREATE STATEMENTS, INSERT STATEMENTS(anything you can think of as long

what is lossless & dependency preserving decomposition ?

Consider the following relations:  S (S#, SNAME, STATUS, CITY)  SP (S#, P#, QTY)  P (P#, PNAME, COLOR, WEIGHT, CITY) Give an expression within SQL for each of queries b

Do NOT use the timestamp type.  Use separate date and time data types only.  Figure out how to define an SQL insert statement that uses built-in SQL (or MySQL) functions to define

what factors might influence a company to outsource its IT infrascture.