Using the VLABS environment, log into SQL SERVER and use the tables IST210-ONLIN

Question

Using the VLABS environment, log into SQL SERVER and use the tables IST210-ONLINE schema with the prefix of MSC, and perform the following queries: 1. Display employee number, first name, last name and the job description for all the employees. 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 Pennsylvania. 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. 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. 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.

Explanation / Answer

1. select empl_number, empl_first_name, empl_last_name, job_description from MSC_Employee e, MSC_job j WHERE j.job_code = e.job_code;

The above will return the employee information with the job code;

2. select cust_name, cust_city, empl_number, empl_last_name from MSC_Customer c, MSC_Employee e WHERE c.salesman_number = e.empl_number AND cust_state = 'PA';

The above query will retrieve the customer information with the assigned salesperson for that customer.

3. select p.prod_number,pm.prod_description,v.vend_name,p.qty_in_stock from MSC_Product_detail as p Join MSC_Product_master as pm on pm.prod_number =p.prod_number join MSC_Vendor as v on v.vend_number =p.vend_numberwhere p.qty_in_stock <500 order by p.qty_in_stock asc

4. select c.cust_number as Cust_Number, cust_name as Cust_Name, count ord_number as Total_Orders from msc_Customer c, msc_order o where c.msc_cust_number=o.msc_cust_number group by msc_cust_number;

5. select vend_number as Vend_No ,vend_name as Vend_Name ,vend_street+' ' + vend_city +' ,'+ vend_state +' '+vend_zip as Vend_Address from msc_Vendor;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.