CodeYourFuture · nsaimk · Sep 2, 2023 · Sep 2, 2023 · Sep 2, 2023 · Sep 2, 2023
diff --git a/Big-Spender/readme.md b/Big-Spender/readme.md
@@ -48,7 +48,7 @@ You are working with Claire and Farnoosh, who are trying to complete a missing r
 **You:** Absolutely. Here's the SQL query you need:
 
 ```sql
-INSERT YOUR QUERY HERE
+select * from spends where amount between 30000 and 31000;
 ```
 
 **Claire:** That's great, thanks. Hey, what about transactions that include the word 'fee' in their description?
@@ -68,39 +68,39 @@ INSERT YOUR QUERY HERE
 **You:** Then here's the query for that:
 
 ```sql
-INSERT YOUR QUERY HERE
+select * from spends where description ilike '%FEE%';
 ```
 
 **Farnoosh:** Hi, it's me again. It turns out we also need the transactions that have the expense area of 'Better Hospital Food'. Can you help us with that one?
 
 **You:** No worries. Here's the query for that:
 
 ```sql
-INSERT YOUR QUERY HERE
+select spends.* from spends join expense_areas on spends.expense_area_id = expense_areas.id where expense_areas.expense_area like 'Better Hospital Food';
 ```
 
 **Claire:** Great, that's very helpful. How about the total amount spent for each month?
 
 **You:** You can get that by using the GROUP BY clause. Here's the query:
 
 ```sql
-CREATE YOUR QUERY HERE
+select date_trunc('month', date), sum(amount) from spends group by date_trunc('month', date);
 ```
 
 **Farnoosh:** Thanks, that's really useful. We also need to know the total amount spent on each supplier. Can you help us with that?
 
 **You:** Sure thing. Here's the query for that:
 
 ```sql
-INSERT YOUR QUERY HERE
+select supplier_id, sum(amount) from spends group by supplier_id order by supplier_id;
 ```
 
 **Farnoosh:** Oh, how do I know who these suppliers are? There's only numbers here.
 
 **You:** Whoops! I gave you ids to key the totals, but let me give you names instead.
 
 ```sql
-INSERT YOUR QUERY HERE
+select suppliers.supplier, sum(amount) from spends join suppliers on spends.supplier_id = suppliers.id group by suppliers.id order by suppliers.supplier;
 ```
 
 **Claire:** Thanks, that's really helpful. I can't quite figure out...what is the total amount spent on each of these two dates (1st March 2021 and 1st April 2021)?
@@ -112,7 +112,7 @@ INSERT YOUR QUERY HERE
 **You:** Then you need an extra clause. Here's the query:
 
 ```sql
-CREATE YOUR QUERY HERE
+select supplier_id, sum(amount) from spends where date in ('2021-03-01', '2021-04-01') group by supplier_id order by supplier_id;
 ```
 
 **Farnoosh:** Fantastic. One last thing, looks like we missed something. Can we add a new transaction to the spends table with a description of 'Computer Hardware Dell' and an amount of £32,000?
@@ -124,8 +124,7 @@ CREATE YOUR QUERY HERE
 **You:** Sure thing. To confirm, the date is August 19, 2021, the transaction number is 38104091, the supplier invoice number is 3780119655, the supplier is 'Dell', the expense type is 'Hardware' and the expense area is 'IT'. Here's the query for that:
 
 ```sql
-INSERT YOUR QUERIES HERE
-
+insert into spends(date, amount, description, transaction_no, supplier_inv_no, supplier_id, expense_type_id, expense_area_id) values ('2021-08-19', 32000, 'Computer Hardware Dell', 38104091, 3780119655, 66, 42, 46);
 ```
 
 **Claire:** Great, that's everything we need. Thanks for your help.

diff --git a/E-Commerce/e-commerce ERD.png b/E-Commerce/e-commerce ERD.png
diff --git a/E-Commerce/readme.md b/E-Commerce/readme.md
@@ -40,18 +40,42 @@ erDiagram
     }
     customers ||--o{ orders : places
 ```
+    ## ERD is in this folder with .png extension
 
 ### Query Practice
 
 Write SQL queries to complete the following tasks:
 
 - [ ] List all the products whose name contains the word "socks"
+    ```sql
+    SELECT * FROM products  WHERE product_name ILIKE '%socks%';
+    ```
 - [ ] List all the products which cost more than 100 showing product id, name, unit price, and supplier id
+    ```sql
+        SELECT prod_id, product_name, unit_price, supp_id FROM product_availability JOIN products ON prod_id = id WHERE unit_price > 100;
+    ```
 - [ ] List the 5 most expensive products
+    ```sql
+        SELECT GREATEST(unit_price) FROM product_availability LIMIT 5;
+    ```
 - [ ] List all the products sold by suppliers based in the United Kingdom. The result should only contain the columns product_name and supplier_name
+
+    **note for reviewer: this query returns 9 rows so i am not sure whether it is right or not**
+    ```sql
+        SELECT product_name FROM products JOIN product_availability ON products.id = product_availability.prod_id JOIN suppliers ON product_availability.supp_id = suppliers.id WHERE country ILIKE 'United Kingdom';
+    ```
 - [ ] List all orders, including order items, from customer named Hope Crosby
+    ```sql
+        SELECT o.id AS order_id, o.order_date, o.order_reference, oi.product_id, oi supplier_id, oi.quantity FROM orders o JOIN order_items oi ON o.id = oi.order_id JOIN customers c ON o.customer_id = c.id WHERE c.name = 'Hope Crosby';
+    ```
 - [ ] List all the products in the order ORD006. The result should only contain the columns product_name, unit_price, and quantity
+    ```sql
+        SELECT p.product_name, pa.unit_price, oi.quantity FROM products AS p JOIN product_availability AS pa ON p.id = pa.prod_id JOIN order_items AS oi ON pa.prod_id = oi.product_id JOIN orders AS o ON oi.order_id = o.id WHERE o.order_reference = 'ORD006';
+    ```
 - [ ] List all the products with their supplier for all orders of all customers. The result should only contain the columns name (from customer), order_reference, order_date, product_name, supplier_name, and quantity
+    ```sql
+        SELECT c.name, o.order_reference, o.order_date, p.product_name, s.supplier_name, oi.quantity FROM customers AS c JOIN orders AS o ON c.id = o.customer_id JOIN order_items AS oi ON o.id = oi.order_id JOIN products AS p ON oi.product_id = p.id JOIN suppliers AS s ON oi.supplier_id = s.id;
+    ```
 
 ## Acceptance Criteria