Assignment: Using the VLABS environment, log into SQL SERVER and use the tables
ID: 3729955 • Letter: A
Question
Assignment: Using the VLABS environment, log into SQL SERVER and use the tables in IST210-ONLINE folder with the prefix of MSC, and perform the following queries:
Use the NULL keyword to display the order number, line number and product number for records that have no value for the field ship_quantity in the table MSC_ORDER_DETAIL
Display empl_first_name, empl_last_name and job_code for employee number 220.
Display the product number, vendor number and quantity in stock value for product detail where the quantity in stock is in the 200 to 300 range from the MSC_product_detail table sorted in descending order by quantity in stock value.
Display the number (tally) of customers who are located in the state of Florida.
Display the minimum, maximum and average commission of all the products.
Display employee number, first name and last name when the employee’s last name contains a letter ‘C’ (upper or lower case) anywhere in the last name.
Explanation / Answer
Solution:
The first four subparts have been answered as per Chegg guidelines, please repost others.
Queries:
Use the NULL keyword to display the order number, line number and product number for records that have no value for the field ship_quantity in the table MSC_ORDER_DETAIL
SELECT orderNo, lineNo, productNo FROM MSC_ORDER_DETAIL WHERE ship_quantity IS NULL
Display empl_first_name, empl_last_name and job_code for employee number 220.
SELECT empl_first_name, empl_last_name, job_code FROM MSC_ORDER_DETAIL WHERE employeeNo= 220
Display the product number, vendor number and quantity in stock value for product detail where the quantity in stock is in the 200 to 300 range from the MSC_product_detail table sorted in descending order by quantity in stock value.
SELECT product number, vendor number, quantity MSC_product_detail WHERE quantity>=200 AND quantity<=300 ORDER BY quantity DESC
Display the number (tally) of customers who are located in the state of Florida.
SELECT COUNT(customerNo) FROM Customers WHERE location= 'Florida'
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.