Assignment: Using the VLABS environment, log into SQL SERVER and use the tables

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 cust_number and cust_name for records that have no value for the field cust_city in the table MSC_CUSTOMER

Display empl_first_name, empl_last_name and job_code for employee number 210.

Display the product number, vendor number and quantity in stock value for product detail where the quantity in stock is in the 100 to 200 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 Pennsylvania.

Display the minimum, maximum and average selling price of all the products.

Display employee number, first name and last name when the employee’s last name contains a letter ‘R’ (upper or lower case) anywhere in the last name.

Explanation / Answer

[1]
SELECT cust_number , cust_name
FROM   MSC_CUSTOMER
WHERE cust_city IS NULL;

[2]

SELECT empl_first_name, empl_last_name ,job_code
FROM    MSC_EMPLOYEE
WHERE   empl_no = 20;

[3]

SELECT product number, vendor number , quantity
FROM    MSC_PRODUCT
WHERE   quantity BETWEEN 100 AND 200
ORDER BY quantity DESC;

[4]
SELECT count(*)
FROM MSC_CUSTOMER
WHERE state = 'Pennsylvania';

[5]

SELECT min(price),max(price),avg(price)
FROM   MSC_PRODUCT;

[6]

SELECT empl_no,empl_first_name,empl_last_name
FROM    MSC_EMPLOYEE
WHERE empl_last_name like '%R%';

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

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