Chapter 9 (35 pts)

SQL

Write the following SQL statements – each question in a new query. After writing the SQL statements, click on Execute Query. When prompted to save the file, name it (lastname)Chapter9(Question). For example: ParkerChapter9A.rpt. This file will include the SQL for that question and its results. You will attach this .rpt file to your assignment in Blackboard. There will be 10 attachments for this lesson.

Write and execute the following SQL statements that use scalar functions to:

1. Select the SalesOrderID and how many days between the OrderDate and Ship Date (labeled as Days to Ship) from the SalesOrderHeader table

(4 pts)

2. Select the BusinessEntityID, month name, day and year from the BirthDate field on the Employee table. Display as: (4 pts.)

BusinessEntityID Month Day year

—————- —————————— ———– ———–

1 March 2 1959

3. Select the UnitMeasureCode, Name (in uppercase) (labeled as Name) and ModifiedDate from the UnitMeasure table that only selects the date portion not the time portion(labeled as Mod Date). Display as: (4 pts)

UnitMeasureCode Name Mod Date

————— —————– ———–

BOX BOXES Jun 1 1998

4. Select from the Person Table the BusinessEntityID, Title, the first letter of First Name, first letter of MiddleName, and Last Name (label as Full Name) where there isn’t NULL in any part of the name. Display as: (4 pts)

businessentityid Full Name

—————- ——————

5 Ms. G A Erickson

5. Select all Names from SalesTaxRate table replacing ‘United Kingdom’ with ‘UK’ and displaying the length of each name (labeled as Name Length). Display as: (4 pts.)

Name Name Length

—————- ———–

Canadian GST + Alberta Provincial Tax 37

6. Rank the Products table based on listprice (highest first) with no gaps in numbers for all rows that have a makeflag =1, grouped by daystomanufacture. Output should be as follows: (3 pts.)

Ranking productid name Daystomanufacture ListPrice

————- ———– ——— ——————– ————–

7. Use the above information from F, but want to see like listprices with same ranking but no gaps in numbers for the rank. (3 pts.)

8. Use the above information from F but I would like to group the products in 5 groups based on their listprice within the daystomanufacture. (3 pts)

9. Use the above information from F, but want to see like listprices with same ranking and it is ok to have gaps in numbers. (3 pts.)

10. Using a Use Case, select the BusinessEntityID and MaritalStatus from the Employee table. When the MaritalStatus is = ‘M’ then display “Married” in a column called Type. If the MaritalStatus is Single then display “Single” in the Type column. (3 pts.)