REQUIRED: Use the alias table technique for each query. For each exercise provid
ID: 3870308 • Letter: R
Question
REQUIRED: Use the alias table technique for each query. For each exercise provide the SQL command as well as query results in your document. If the results are long, provide a representative sample.
Ex 1. Write an SQL command that displays all of the fields and data from the Categories table. {Hint: Use the shortcut, fast method!}
Ex. 2. Create a phone list of the customer contacts that is sorted by the Company Name. Include the company name, contact name, phone and fax in the output. Don’t forget to sort! (Your results should be all 91 customers in alphabetical listing by company, but only do a screen capture of the top 10 or 15.)
Ex. 3. Rewrite the query you created in Ex. 2 to display ONLY those records from the USA. Alias the Company Name as Company and the Contact Name as Customer.
Ex. 4. Create a list of products and their categories with a price greater than or equal to $20. (Only 3 columns should be displayed) (For “categories” check the table design).
Ex. 5. Modify the query in Ex. 4. This time list only those products with a price between $40 and $90.
Explanation / Answer
Hi,
As DB design was not given, I have made some assumptions with respect to database design. Please replace the below table/columns names with actual table/column names as given in DB design.
Ans 1-
Select * from Categories;
Ans 2- //Assume table name is Customer.
select companyname,contactname,phone,fax from Customer order by companyname asc;
Ans 3- //Assume to determine the location, we have a location column in your Customer table
Select companyname as Company,contactname as Customer,phone,fax from Customer where location='USA' order by companyname asc;
Ans 4-//Assume that we have a Product table and it has columns called as category and price
Select productname,category,price from product where price>=20;
Ans 5-//Assume that we have a Product table and it has columns called as category and price
Select productname,category,price from product where price between 40 and 90;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.