Explore BrainMass
Share

Explicit SQL Statement

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

Given the attached tables (see attachment):

1- Provide an explicit JOIN SQL statement that returns the store ID, store name, manager employee first and last name fields, manager employee grade, the city and state fields of the store. Only unique rows should be returned. Note that the 4th field in the Employee table is the employee grade field, the 2nd field in the Store table is the Store name, and the 3rd field in the Store table is the manager's ID. All ID fields are of a numeric type.

2- Write a query that returns the first name, last name, id, city and state fields from the Employee, User and Store tables. To return these fields from all three tables, UNION keywords must be applied. Note that the first name, last name and id are to be returned in one single alphabetical field, with a space separating first from last, and a dash character, '-', separating last from ID. For the Store table, the store name is returned, along with the store id, separated by a dash character.

3- Write a query that contains a subquery, which returns the store name, manager last name, city, state and country fields from the Store table. Hint: A JOIN should not be used; A subquery should be used instead.

© BrainMass Inc. brainmass.com October 25, 2018, 3:48 am ad1c9bdddf
https://brainmass.com/computer-science/sql/explicit-sql-statement-354207

Attachments

Solution Preview

/*1- Provide an explicit JOIN SQL statement that returns the store ID, store name,
manager employee first and last name fields, manager employee grade, the city
and state fields of the store. Only unique rows should be returned.
Note that the 4th field in the Employee table is the employee grade field, the 2nd field in
the Store table is the Store name, and the 3rd field in the Store table is the manager's ID.
All ID fields are of a numeric type.*/

SELECT s.SoteId,s.StoredName,e.FirstName as [Employee First Name]
,e.LastName as ...

Solution Summary

The solution discusses the SQL statement.

$2.19
See Also This Related BrainMass Solution

Use the Internet or computer magazines to investigate one of the following DBMSs

Use the Internet or computer magazines to investigate one of the following DBMSs: DB2, SQL Server, MySQL, Oracle, or Sybase. Then prepare a report that explains how the DBMS handles tow of the following distributed database functions: deadlock, fragmentation, replication, the data dictionary or log, and distributed queries.

View Full Posting Details