SQL Query on Multiple Tables—JOIN

1. Display employee number, first name, middle name, last nameand the job description for all the employees. The order of columnsfor the display must be the same as it is provided in the previoussentence. You will join MSC_employee and MSC_job tables.

2. Write and execute a SELECT statement that will return thecustomer’s name, city, salesman’s number assigned to customer, andthe assigned salesman’s last name for customers located in thestate of New York.

3. Display data from the MSC_Product_detail table by performinga Three Table Join with the MSC_Product_detail, MSC_Product_masterand MSC_Vendor tables. The SELECT statement will display productnumber, product description, sale price, vendor name and quantityin stock … and only for product details that have quantity in stockless than 400. In addition, the result needs to be sorted byascending value of quantity in stock.

4. Write a select statement that will display customer number,customer name and the number of orders belong to the customer whosestate is Pennsylvania.. You will need to join two tables, use aCOUNT aggregate and a GROUP BY clause in your SELECT statement.Override labels with a more descriptive but brief label.

5. Create and execute a SELECT statement that would provide datafor a vendor directory. Display should include vendor number,vendor name, street, city, state, zip code. Concatenate the addresselements to look like ‘1234 Main Street State College, PA 16802’.Override the column labels with meaningful descriptions. Displaydirectory by state (ascending).