Oracle SQL Developer HW:

1.Write a SELECT statement that returns three columns:

vendor_id vendor_id from the Vendors table

vendor_name vendor_name from the Vendors table

contact_name A concatenation of vendor_contact_first_name and vendor_contact_last_name with a space in between

The result set should have one row for each vendor whose contact has the same last name as another vendors contact, and it should be sorted by vendor_contact_last_name. Hint: Use a self-join.

2. Use the UNION operator to generate a result set consisting of two columns from the Vendors table: vendor_name and vendor_state. If the vendor is in California, the vendor_state value should be CA; otherwise, the VendorState value should be Outside CA. Sort the final result set by vendor_name.

3. Write a SELECT statement that returns one column from the Customers table named full_name that joins the last_name and first_name columns.

Format this column with the last name, a comma, a space, and the first name like this:

Doe, John

Sort the result set by last name in ascending sequence.

Return only the customers whose last name begins with letters from M to Z.

4. Write a SELECT statement that returns these column names and data from the Order_Items table:

item_id The item_id column

item_price The item_price column

discount_amount The discount_amount column

quantity The quantity column

price_total A column thats calculated by multiplying the item price by the quantity

discount_total A column thats calculated by multiplying the discount amount by the quantity

item_total A column thats calculated by subtracting the discount amount from the item price and then multiplying by the quantity

Only return rows where the item_total is greater than 500.

Sort the result set by item total in descending sequence.

5. Write a SELECT statement that returns these columns from the Orders table:

order_id The order_id column

order_date The order_date column

ship_date The ship_date column

Return only the rows where the ship_date column contains a null value.

6. Write a SELECT statement that creates a row with these columns:

price 100 (dollars)

tax_rate .07 (7 percent)

tax_amount The price multiplied by the tax

total The price plus the tax

To calculate the fourth column, add the expressions you used for the first and third columns.

Use a FROM clause that specifies the Dual table.