today Sql work

iamAntimPal · iamAntimPal · commit 67d2b43ee92d · 2025-03-13T23:02:03.000+05:30
diff --git a/.gitattributes b/.gitattributes
@@ -1,2 +1,7 @@
 *.sql linguist-detectable=true
 *.sql linguist-language=SQL
+
+
+*.md linguist-vendored
+*.json linguist-vendored
+*.py linguist-vendored
diff --git a/1075. Project Employees I.sql b/1075. Project Employees I.sql
@@ -0,0 +1,82 @@
+1075. Project Employees I
+Solved
+Easy
+Topics
+Companies
+SQL Schema
+Pandas Schema
+Table: Project
+
++-------------+---------+
+| Column Name | Type    |
++-------------+---------+
+| project_id  | int     |
+| employee_id | int     |
++-------------+---------+
+(project_id, employee_id) is the primary key of this table.
+employee_id is a foreign key to Employee table.
+Each row of this table indicates that the employee with employee_id is working on the project with project_id.
+ 
+
+Table: Employee
+
++------------------+---------+
+| Column Name      | Type    |
++------------------+---------+
+| employee_id      | int     |
+| name             | varchar |
+| experience_years | int     |
++------------------+---------+
+employee_id is the primary key of this table. It's guaranteed that experience_years is not NULL.
+Each row of this table contains information about one employee.
+ 
+
+Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.
+
+Return the result table in any order.
+
+The query result format is in the following example.
+
+ 
+
+Example 1:
+
+Input: 
+Project table:
++-------------+-------------+
+| project_id  | employee_id |
++-------------+-------------+
+| 1           | 1           |
+| 1           | 2           |
+| 1           | 3           |
+| 2           | 1           |
+| 2           | 4           |
++-------------+-------------+
+Employee table:
++-------------+--------+------------------+
+| employee_id | name   | experience_years |
++-------------+--------+------------------+
+| 1           | Khaled | 3                |
+| 2           | Ali    | 2                |
+| 3           | John   | 1                |
+| 4           | Doe    | 2                |
++-------------+--------+------------------+
+Output: 
++-------------+---------------+
+| project_id  | average_years |
++-------------+---------------+
+| 1           | 2.00          |
+| 2           | 2.50          |
++-------------+---------------+
+Explanation: The average experience years for the first project is (3 + 2 + 1) / 3 = 2.00 and for the second project is (3 + 2) / 2 = 2.50
+
+'''SQL
+
+# Write your MySQL query statement below
+select project_id , round(avg(experience_years), 2) as average_years
+from project as p
+left join employee as e
+on p.employee_id = e.employee_id
+group by project_id;
+
+'''
diff --git a/1251. Average Selling Price.sql b/1251. Average Selling Price.sql
@@ -75,10 +75,12 @@ Average selling price for product 2 = ((200 * 15) + (30 * 30)) / 230 = 16.96
 Note that the average selling price is 0 if the product was not sold.
 
 # Write your MySQL query statement below
-SELECT a.product_id,ROUND(SUM(b.units*a.price)/SUM(b.units),2) as average_price
-FROM Prices as a
-JOIN UnitsSold as b
-ON a.product_id=b.product_id AND (b.purchase_date BETWEEN a.start_date AND a.end_date)
-GROUP BY product_id;
 
+# Write your MySQL query statement below
+SELECT p.product_id, IFNULL(round(SUM(p.price*u.units)/sum(u.units),2),0) as average_price
+FROM Prices p 
+LEFT JOIN UnitsSold u
+ON p.product_id = u.product_id AND 
+u.purchase_date BETWEEN p.Start_date and p.end_date
+GROUP BY p.product_id
 -- SELECT product_id, ROUND(AVG(COALESCE(price, 0)), 2) AS average_price
