Week 78 - SQL & Query Techniques

Session Variables!

Did you even know that you can use variables in your SQL queries outwith stored procedures? No? Well, I'm here to tell you you can.

CREATE TABLE w78 ASSELECTSEQ4() AS sales_id,CASEWHEN MOD(SEQ4(), 4) = 0 THEN 'Product A'WHEN MOD(SEQ4(), 4) = 1 THEN 'Product B'WHEN MOD(SEQ4(), 4) = 2 THEN 'Product C'ELSE 'Product D'END AS product_name,UNIFORM(1, 10, RANDOM())::INTEGER AS quantity_sold,DATEADD('day', -UNIFORM(1, 365, RANDOM())::INTEGER, CURRENT_DATE()) AS sales_date,UNIFORM(20, 100, RANDOM())::FLOAT * UNIFORM(1, 10, RANDOM())::INTEGER AS sales_amountFROM TABLE(GENERATOR(ROWCOUNT => 1000));

Run the above code, and then create a variable called "sales_avg".

Run the below script:

SELECT *FROM w78WHERE sales_amount between $sales_avg - 50 and $sales_avg +50;

Your result should look like the below (numbers will vary)