Scribd
Upload
8 views2 pages

Lecture Notes3 Filtering On Data

The document provides SQL queries for retrieving product and customer purchase information from a farmers market database. It includes commands for selecting product details, sorting purchases by date and time, and calculating total amounts paid by customers. Additionally, it demonstrates how to format customer names in different cases and limit results based on recent transactions.

Uploaded by

n.durgaprasadyadav
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views2 pages

Lecture Notes3 Filtering On Data

The document provides SQL queries for retrieving product and customer purchase information from a farmers market database. It includes commands for selecting product details, sorting purchases by date and time, and calculating total amounts paid by customers. Additionally, it demonstrates how to format customer names in different cases and limit results based on recent transactions.

Uploaded by

n.durgaprasadyadav
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Qn : Get all products from farmer market?

Ans : select * from farmers_market.product;

Qn : select the product IDs and their corresponding product names & sizes from the
`product` table.

Ans : SELECT product_id, product_name, product_size FROM farmers_market.product;

Qn : If we want to sort this entire data in descending order of a particular


column, in this case by product_id?

Ans: SELECT product_id, product_name, product_size FROM farmers_market.product


ORDER BY product_id ASC;

Qn: How to sort the purchases by market date (most recent) and transaction time
both?

Ans: SELECT market_date, transaction_time, quantity, cost_to_customer_per_qty from


`farmers_market.customer_purchase`
ORDER BY market_date DESC, transaction_time;

Question: Your manager is only interested in looking at the 10 most recent


transactions in the `customer_purchases` table.
Ans:
-- SELECT market_date, transaction_time, quantity, cost_to_customer_per_qty from
`farmers_market.customer_purchase`
-- ORDER BY transaction_time DESC LIMIT 10;

Question: What if the manager asks you to fetch the 2nd & 3rd most recent purchase
(skip the 1st one)?
-- SELECT market_date, transaction_time, quantity, cost_to_customer_per_qty from
`farmers_market.customer_purchase`
-- ORDER BY transaction_time DESC LIMIT 2 OFFSET 2

#Question: In the customer purchases, we have quantity and cost per qty separate,
query the total amount that the customer has paid along with date, customer id,
vendor_id, qty, cost per qty and the total amount?;

-- select * from farmers_market.customer_purchase;

-- select market_date, customer_id, vendor_id, quantity, quantity *


cost_to_customer_per_qty as total_amount from `farmers_market.customer_purchase`;

-- select market_date, customer_id, vendor_id, quantity, ROUND(quantity *


cost_to_customer_per_qty, 2) as total_amount from
`farmers_market.customer_purchase`;

-- select customer_id, customer_first_name, customer_last_name,


CONCAT(customer_first_name, ' ', customer_last_name) as customer_full_name from
`farmers_market.customer`;

-- select customer_id, customer_first_name, customer_last_name,


UPPER(CONCAT(customer_first_name, ' ', customer_last_name)) as customer_full_name
from `farmers_market.customer`;
select customer_id, customer_first_name, customer_last_name,
LOWER(CONCAT(customer_first_name, ' ', customer_last_name)) as customer_full_name
from `farmers_market.customer`;

You might also like