SQL 달리기

1번 )

SELECT 
	count(name) as count_name
FROM users u 
WHERE name LIKE '김%'

2 번 )

SELECT 
	DATE(p.created_at) AS created_at,
	ROUND(AVG(p.point)) AS avg_points
FROM point_users p
GROUP BY 1

3 번 )

SELECT 
	u.user_id,
	u.email,
	IFNULL ( p.point, 0 ) as "point"
FROM users u LEFT OUTER JOIN point_users p ON u.user_id = p.user_id
ORDER BY p.point DESC

4-1 번 )

SELECT 
	a.name,
    a.count_id,
    a.total_amount
FROM (
    SELECT 
    	c.CustomerName as name,
    	COUNT(o.CustomerID) as count_id,
    	COALESCE(SUM(o.TotalAmount), 0) as total_amount
    FROM 
    	customers c LEFT OUTER JOIN orders o ON c.CustomerID = o.CustomerID
    GROUP BY c.CustomerName
    HAVING COALESCE(SUM(o.TotalAmount), 0) >= 0
) a
ORDER BY a.count_id DESC

4-2 번 )

SELECT
	b.Country,
    b.name as Top_Customer,
    MAX(b.top_customer) as Top_Spent
FROM(
    SELECT 
    	c.Country as Country,
        c.CustomerName as name,
    	SUM( o.TotalAmount ) as top_customer
    FROM orders o LEFT OUTER JOIN customers c ON c.CustomerID = o.CustomerID
    GROUP BY o.CustomerID
    HAVING SUM(o.TotalAmount) = ( 
        SELECT SUM(o.TotalAmount) 
        FROM orders o1
        WHERE o1.CustomerID  = o.CustomerID
        GROUP BY o.CustomerID
    )
) b
GROUP BY b.Country

4-3 번 )

SELECT 
    e.Name,
    e.Department,
    e.Salary,
    a.top_ear AS Top_Earner,
    a.top_sal AS Top_Salary
FROM Employees e LEFT OUTER JOIN (
    SELECT 
        Department, 
        Name AS top_ear, 
        MAX(Salary) AS top_sal
    FROM Employees e
    GROUP BY Name, Department
    HAVING MAX(Salary) = (
        SELECT MAX(e1.Salary)
        FROM Employees e1
        WHERE e1.Department = e.Department
    )
) a ON e.Department = a.Department

4-4 번 )

SELECT 
    e.Department, 
    AVG(e.Salary) AS Avg_Salary
FROM Employees e
GROUP BY e.Department
HAVING AVG(e.Salary) = (
    SELECT MAX(avg_sal)
    FROM (
        SELECT AVG(Salary) AS avg_sal
        FROM Employees
        GROUP BY Department
    ) a
)

5-1번 )

SELECT
	oc.cName,
    SUM(oc.o_count) as OrderCount,
    SUM(oc.o_count * p.Price) as Price 
FROM products p RIGHT OUTER JOIN ( 
    SELECT 
    	c.CustomerName cName,
    	c.CustomerID cID,
    	o.OrderID oID,
        o.ProductID pID,
    	o.Quantity o_count
    FROM orders o CROSS JOIN customers c ON o.CustomerID = c.CustomerID 
    GROUP BY o.OrderID
    HAVING pID > 0
) oc ON p.ProductID = oc.pID
GROUP BY oc.cName
ORDER BY oc.cName

5-2 번 )

SELECT
	p.Category,
	p.ProductName,
	SUM(o.Quantity) as count_o
FROM orders o LEFT OUTER JOIN products p ON p.ProductID = o.ProductID
GROUP BY p.ProductName
HAVING SUM(o.Quantity) = (
    	SELECT MAX(o1.Quantity)
    	FROM oders o1)

5-3 번 )

SELECT 
	a.name,
   	a.dep,
    a.sal
FROM (
	SELECT
    	e.Name as name,
    	e.Department as dep,
    	MAX(e.Salary) as sal
    FROM employees e
    GROUP BY e.Department
) a

5-4 번 )

SELECT 
	e1.Name,
    p.ProjectName,
    p.Budget
FROM 
	projects p LEFT OUTER JOIN ( 
        SELECT 
        	ep.EmployeeID as eID,
        	ep.ProjectID as pID,
        	e.Name as Name
        FROM employeeprojects ep CROSS JOIN employees e on ep.EmployeeID = e.EmployeeID ) e1 ON p.ProjectID = e1.pID
WHERE p.Budget >= 10000