1. FOR or all products, list the product ID, product name, supplier ID, Supplier
ID: 3744090 • Letter: 1
Question
1. FOR or all products, list the product ID, product name, supplier ID, Supplier name, unit price, and a label that indicates whether the item is cheap (less than 10.00), moderately priced (between 10.00 dollars and 45.00 dollars), or expensive (more than 45.00). Place the data in ascending order by unit price. Use a CASE statement as shown on page 53 (T-SQL) to do this problem.
2.The supplierID, Companyname, city, postalcode and country for all customers thathave a FAX number and country is USAor Sweden.
3.The SupplierID, company name, contact name, phone number, and contact title for all customers whose title contains the word Accounting.
4.The EmployeeID, firstname, lastname, title, and countryfor all Employeeswhose title is the word CEOor whose titleof courtesyis Ms.or Dr.
5.The customer ID, company name, contact name, phone number, city, and country for all customers whose contact has the first name John or Tom.
6.The order ID, customer ID, customer name, customer contact title, order date, and required date for all orders for which the required date is within threeweeks of the orderdate.
7.The name of each day of the week and the number of orders placed on that day.
8.The customer ID and company name and the longest delay in shipping on any order to that customer. Express the delay in weeks and place the output in ascending order by the delay.
9.The order ID, product ID, product name, unit price,quantityshipped, and line cost (qty * price)for all products that were shipped within the last 10days of August, 2015.
10.The order ID, customer ID, company name, order day, and total order amount for all orders that were placed in November and December, 2014. Place the results in Ascendingorder by the total order amounT
Explanation / Answer
1) alter table products
add column case;
update table products
set case='cheap'
where price<=10.00;
update table products
set case='moderately priced'
where price between 10.00 and 45.00;
update table products
set case='expensive'
where price>=45.00;
select * from products
order by price;
2) select supplierID,Companyname,city,postalcode,country from customers
where country='usa' or country='sweden' and FAXnumber is not null;
3) select SupplierID,companyname,contactname,phonenumber,title from customers
where title like %Accounting%;
4) select EmployeeID, firstname, lastname, title,country from employees
where title='CEO' or courtesy='Ms.' or courtesy='Dr.';
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.