CodeYourFuture · BekirKuscu · Sep 9, 2023 · Sep 9, 2023 · Sep 9, 2023 · Sep 11, 2023
diff --git a/.DS_Store b/.DS_Store
diff --git a/Big-Spender/readme.md b/Big-Spender/readme.md
@@ -48,7 +48,8 @@ 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
+-- INSERT
+ 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 +69,44 @@ INSERT YOUR QUERY HERE
 **You:** Then here's the query for that:
 
 ```sql
-INSERT YOUR QUERY HERE
+-- INSERT 
+SELECT * FROM expense_areas 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
+-- INSERT 
+SELECT * FROM expense_areas WHERE expense_area = '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
+-- CREATE 
+SELECT SUM(amount),date FROM spends GROUP BY 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
+-- INSERT 
+SELECT SUM(amount) FROM spends WHERE date between '2021-03-31' and '2021-05-01
 ```
 
 **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
+-- INSERT 
+SELECT SUM(amount), supplier FROM suppliers JOIN spends ON suppliers.id = spends.supplier_id GROUP BY 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 +118,8 @@ INSERT YOUR QUERY HERE
 **You:** Then you need an extra clause. Here's the query:
 
 ```sql
-CREATE YOUR QUERY HERE
+-- CREATE 
+SELECT SUM(amount), date FROM spends GROUP BY date;
 ```
 
 **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?

diff --git a/E-Commerce/readme.md b/E-Commerce/readme.md
@@ -46,11 +46,34 @@ erDiagram
 Write SQL queries to complete the following tasks:
 
 - [ ] List all the products whose name contains the word "socks"
+'''
+SELECT * FROM products WHERE product_name LIKE '%socks%';
+'''
 - [ ] List all the products which cost more than 100 showing product id, name, unit price, and supplier id
+'''
+SELECT * FROM products p JOIN product_availability pa ON p.id=pa.prod_id WHERE unit_price >100;
+'''
 - [ ] List the 5 most expensive products
+'''
+SELECT product_name, MAX(unit_price) price FROM products p JOIN product_availability pa ON p.id=pa.prod_id GROUP BY product_name ORDER BY price DESC 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
+'''
+SELECT product_name, supplier_name FROM products p JOIN product_availability pa ON p.id=pa.prod_id 
+JOIN suppliers s ON pa.supp_id=s.id
+WHERE s.country = 'United Kingdom';
+'''
 - [ ] List all orders, including order items, from customer named Hope Crosby
+'''
+SELECT * FROM orders o JOIN customers c ON o.customer_id=c.id JOIN order_items oi ON o.id=oi.order_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
+'''
+SELECT product_name, unit_price, quantity FROM orders o JOIN order_items oi ON o.id=oi.order_id
+JOIN product_availability pa ON oi.product_id=pa.prod_id AND oi.supplier_id=pa.supp_id
+JOIN products p ON oi.product_id=p.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
 
 ## Acceptance Criteria

diff --git a/E-Commerce/testing.js b/E-Commerce/testing.js
@@ -0,0 +1,9 @@
+
+
+
+
+
+
+
+
+