Practice LAB: Using the VLABS environment, log into SQL SERVER and use the table

Question

Practice LAB: 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:

1.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

2.Display empl_first_name, empl_last_name and job_code for employee number 220.

3.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.

4.Display the number (tally) of customers who are located in the state of Florida.

5.Display the minimum, maximum and average commission of all the products.

6.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.

Be sure to include the "row(s) affected" messages.

Explanation / Answer

1. SELECT order_number,line_number,product_number from MSC_ORDER_DETAIL where ship_quantity=NULL

2. SELECT empl_first_name, empl_last_name , job_code from EMPLOYEE_TABLE where empl_number=220

3. SELECT product_number, vedor_number, quantity_in_stock from MSC_product_detail where quantity_in_stock >200 and quantity_in_stock<300 order by quantity_in_stock desc

4. SELECT Count(*) from Customer_Table_Name where Location='Florida'

5. SELECT MIN(*), MAX(*), AVG(*) from COMMISION group by product_id

6. SELECT employee_number, first_name, last_name from empl_table where last_name like '%c%'

Obviously I dont have that database, but queries will be like these. you have to put right table name and right field name.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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