SQL Query on Multiple Tables—JOIN 1. Display employee number, first name, middle
ID: 3736856 • Letter: S
Question
SQL Query on Multiple Tables—JOIN
1. Display employee number, first name, middle name, last name and the job description for all the employees. The order of columns for the display must be the same as it is provided in the previous sentence. You will join MSC_employee and MSC_job tables.
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 New York.
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, sale price, vendor name and quantity in stock … and only for product details that have quantity in stock less than 400. In addition, the result needs to be sorted by ascending 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 whose state is Pennsylvania.. 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.
MSC Employee MSC job empl_number ob_code empl_ first name job_description emplm iddle_name empl_middle_name ob_code MSC Customer MSC_Order MSC Order_detail cust num ber ord_number PK FK ord_num ber cust name ne_number prod_number ord quantity FK cust _num ber PK cust st reet ord entrydate FK ord status cust city cust st ate cust zip ship_ quant ity MSC Product_Master ship_date PK prod_number sa lesman_ number prod description MSC Product detail sale_price FK prod_number MSC_Vender m ission_percen FK vend_number PK vend number qty_in_stock vend_name vend _street vend city vend state vend zipExplanation / Answer
Please find my Answer.
Please rate my answer if it helped you!!
/*================================================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;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.