Display employee number, first name, last name and the job description for all t
ID: 3864226 • Letter: D
Question
Display employee number, first name, last name and the job description for all the employees. You will join MSC_employee and MSC_job tables.
Write and execute a SELECT statement that will return the customer’s name, city, salesman’s number assigned to customer, and the assigned salesman’s last name for customers located in the state of Pennsylvania.
Display data from the MSC_Product_detail table by performing a Three Table Join with the MSC_Product_detail, MSC_Product_master and MSC_Vendor tables. The SELECT statement will display product number, product description, vendor name and quantity in stock … and only for product details that have quantity in stock less than 500. In addition, the result needs to be sorted by ascending value of quantity in stock.
Write a select statement that will display customer number, customer name and the number of orders belong to the customer. You will need to join two tables, use a COUNT aggregate and a GROUP BY clause in your SELECT statement. Override labels with a more descriptive but brief label.
Create and execute a SELECT statement that would provide data for a vendor directory. Display should include vendor number, vendor name, street, city, state, zip code. Concatenate the address elements to look like ‘1234 Main Street State College, PA 16802’. Override the column labels with meaningful descriptions.
Explanation / Answer
/*================================================Answer 1 ============================================*/ /***Display employee number, first name, last name and the job description for all the employees*******/ SELECT employee_number, first_name, last_name, job_description FROM MSC_employee E, MSC_job J WHERE E.job_code = J.job_code; /*================================================Answer 2 ============================================*/ /* Write and execute a SELECT statement that will return the customer’s name, city, salesman’s number assigned to customer, and the assigned salesman’s last name for customers located in the state of Pennsylvania. */ SELECT MSC.name, MSC.street, MSC.city, MSC.state, MSC.zip, MS.saleman_number, MS.saleman_lastname FROM MSC_Customer MC, MSC_Saleman MS WHERE MSC.state = "Pennsylvania"; /*================================================Answer 3 ============================================*/ /* Display data from the MSC_Product_detail table by performing a Three Table Join with the MSC_Product_detail, MSC_Product_master and MSC_Vendor tables. The SELECT statement will display product number, product description, vendor name and quantity in stock … and only for product details that have quantity in stock less than 500. In addition, the result needs to be sorted by ascending value of quantity in stock.*/ SELECT MSC.product_number, MPM.product_description, MV.vendor_name, MPD.quantity FROM MSC_Product_detail MPD, MSC_Product_master MPM, MSC_Vendor MV WHERE MPD.product_number = MPM.product_number AND MV.vendor_number = MPD.vendor_name; /*================================================Answer 4 ============================================*/ /*Write a select statement that will display customer number, customer name and the number of orders belong to the customer. You will need to join two tables, use a COUNT aggregate and a GROUP BY clause in your SELECT statement. Override labels with a more descriptive but brief label.*/ SELECT MC.customer_name, count(order_number) AS 'no_of_order' FROM MSC_Order MO, MSC_Customer MC WHERE MC.customer_number = MO.customer_number GROUP BY M.customer_name; /*================================================Answer 5 ============================================*/ /* Create and execute a SELECT statement that would provide data for a vendor directory. Display should include vendor number, vendor name, street, city, state, zip code. Concatenate the address elements to look like ‘1234 Main Street State College, PA 16802’. Override the column labels with meaningful descriptions. */ SELECT vendor_number, vendor_name, CONCAT(street, " ", city, " ", state, " ", zip_code) AS 'Address' FROM MSC_Customer MC;