Sql Statement Dbm/502

DBM/502 - Database Management

Given the attached relational db tables:

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.

SELECT Store.ID, Store.SNAME, Employee.FNAME, Employee.LNAME, Employee.GRADE, Store.CITY, Store.STATE

FROM Employee INNER JOIN Store ON Employee.ID = Store.EID;

2. Without using the WHERE clause, what SQL statement should be used in the User table to only return 2 records with only the Country field and the values GB and USA in each record respectively?

SELECT DISTINCT User.COUNTRY

FROM [User];

3. Write a SQL statement to change Sally Smith's first name to Judy in the User table. It turned out that Sally was her middle name.

UPDATE User

SET FNAME = 'Judy'

WHERE ID = '3';

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

SELECT (SELECT Store.SNAME

FROM Store

WHERE Store.EID = Employee.ID) AS StoreName, Employee.LNAME, (SELECT Store.CITY

FROM Store

WHERE Store.EID = Employee.ID) AS City, (SELECT Store.STATE

FROM Store

WHERE Store.EID = Employee.ID) AS State, (SELECT Store.COUNTRY

FROM Store

WHERE Store.EID = Employee.ID) AS Country

FROM Employee

WHERE ((((SELECT Store.SNAME

FROM Store