Question

5. Write a SELECT statement that uses regular expression functions to get the username and domain name parts of the email addresses in the Administrators table. Return these columns: The email_address column A column named user_name that contains the username part of the email_address column (the part before the @symbol) A column named domain name that contains the domain name part of the email_address column (the part after the symbol) Note: The username part of the email addresses contains only letters, and the domain name part contains only letters and a period. 6. Write a SELECT statement that uses the ranking functions to rank products by the total quantity sold. Return these columns: The product_name column from the Products table A column named total_quantity that shows the sum of the quantity for each product in the Order_Items table A column named rank that uses the RANK function to rank the total quantity in descending sequence A column named dense_rank that uses the DENSE_RANK function to rank the total quantity in descending sequence 7. Write a SELECT statement that uses the analytic functions to get the highest and lowest sales by product within each category. Return these columns: The category_name column from the Categories table The product_name column from the Products table A column named total_sales that shows the sum of the sales for each product with sales in the Order_Items table A column named highest sales that uses the FIRST VALUE function to show the name of the product with the highest sales within each category A column named lowest_sales that uses the LAST_VALUE function to show the name of the product with the lowest sales within each category

Table structure:

administrators = admin_id, email_address, password, first_name, last_name

categories = category_id, category_name

customers = customer_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id

order_items = item_id, order_id, product_id, item_price, discount_amount, quantity

orders = order_id, customer_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, card_expires, billing_address_id

products = product_id, category_id, product_code, product_name, description, list_price, discount_percent, date_added

Answer 1

5.
SELECT email_address, REGEXP_SUBSTR(email_address, '([^@]+)') AS "user_name", REGEXP_SUBSTR(email_address, '([^@]+[a-z]*$)') AS "domain_name" FROM administrators;

6.

SELECT product_name, SUM(quantity) AS total_quantity, RANK() OVER (ORDER BY total_quantity DESC) rank, DENSE_RANK () OVER (ORDER BY total_quantity DESC) dense_rank FROM Products , Order_Items WHERE Products.product_id = Order_Items.product_id GROUP BY Order_Items.product_id

7.

SELECT `product_name`, categories.category_id, `category_name`, SUM(`quantity`) AS total_quantity, FIRST_VALUE(`category_name`) OVER ( PARTITION BY `category_id` ORDER BY total_quantity DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) highest_sales, LAST_VALUE(`category_name`) OVER ( PARTITION BY `category_id` ORDER BY `total_quantity` DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) lowest_sales FROM `Products` , `Order_Items`, `Categories` WHERE Products.product_id = Order_Items.product_id and products.category_id = categories.category_id GROUP BY Order_Items.product_id;