SQL - SELECT

SELECT yearID, playerID, salary FROM Salaries LIMIT 10;

 

SELECT * FROM Salaries ORDER BY salary ASC LIMIT 20;

 

SELECT * FROM Salaries ORDER BY salary DESC LIMIT 20;

 

SELECT * FROM Salaries ORDER BY salary DESC, playerID ASC;

 

SELECT * FROM Salaries

WHERE yearID='2010'

AND teamID='NYA'

ORDER BY salary DESC LIMIT 20;

 

-- SUM, AVG

 

SELECT * FROM Salaries WHERE playerID='rodrial01' ORDER BY yearID ASC;

 

SELECT SUM(salary)

FROM Salaries

WHERE playerID='rodrial01';

 

-- Concat Function, DISTINCT

SELECT nameFirst || ' ' || nameLast FROM People LIMIT 10;

                    -- MySQL version : SELECT CONCAT(nameFirst, nameLast) FROM People LIMIT 10;

SELECT nameFirst || ' ' || nameLast FROM People WHERE playerID='rodrial01';

 

SELECT COUNT(DISTINCT(nameFIrst || ' ' || nameLast)) FROM People;

                    -- DISTINCT -> unique 한 값을 필터

 

SELECT nameFirst || ' ' || nameLast AS name, COUNT(*) FROM People GROUP BY name HAVING COUNT(*) > 1;

 

SELECT yearID, teamID, SUM(Salary) AS total_salary FROM Salaries GROUP BY yearID, teamID ORDER BY total_salary DESC;

                    -- GROUP BY 쓰려면 SUM과 같이 하나로 합치는 무언가를 사용해야 함