Query Formulation

1. List the organization numbers in the Users table. Your output should not have duplicates.

2. List the user name (first and last), phone number, and email address of users having a prefix of 445 in the phone number. A phone number consists of an area code terminating in a right parenthesis, prefix, hyphen, and exchange (last four digits).

3. List all columns of the expense category table with a limit between $100 and $300 including the end points 100 and 300. Sort the result in ascending order by limit.

4. List expense reports with a status (1) approved or denied and (2) submitted date from September to October 2020. Include the expense report number, submitted date, and status in the result. For the expense report status, you should match on any case of approved or denied. In PostgreSQL, you surround a date constant with single straight quotes. Text constants in PostgreSQL are case sensitive. Hint: You can use the UPPER function to convert text to upper case in the status condition.

5. List the number and name (first and last) of users who work in information technology (organization name begins with IT). Your query must not use the organization numbers (3, 4 or 6) of IT organization units in the WHERE clause. Pretend that the user knows only the organization name, not the organization number. Thus, the WHERE clause should not have a condition involving the organization number compared to a constant.

6. List the user number, user name (first and last), organization number, and organization name of organizations with account in the organization name. Allow any case (upper or lower) for characters in the word account. Allow account to occur in any part of the organization name. Hint: You can use the UPPER function to convert text to upper case in the organization name condition.

7. List details about expense reports with a status date in September 2020 and the words Course or Presentation in the description. You can match the case exactly for the condition on description or use a function to convert the value and match on upper case. The results should include the expense report number, submitted date, status date, description, and status.

8. List details about expense items with expense amount less than the limit of the related expense category and expense date after September 25, 2020. The result should include the expense item number, expense date, expense amount, expense category name, and expense category limit.