Kai Ler's Portfolio

5.4 - SQL Cheatsheet

2 min read

Sources:

Querying data

SELECT * FROM player

* can be replaced with any columns player is the table we are querying from

SELECT player_name AS name

rename the field to another name Can also do spacings with ‘Full name’

Filtering data

WHERE weight = 190

can replace with inequality operators

WHERE weight > 90 AND height > 190

can also use OR

WHERE player_name like 'Aaron'

can also use = instead of like

WHERE player_name like 'Aaron%

can place % as a wildcard for after or before, depending on where we put it like ‘A%n’ means every filed that starts with A and ends with n

WHERE player_name like T_m%

_ means any character

WHERE player_name in ('John', 'Bob)

for text fields can multiple or statements for exact matches

WHERE weight between 190 and 200

for integer filtering

WHERE player is not null

select all player fields where they are not null

ORDER BY weight ASC

can also be DESC

DATEDIFF(enddate, startdate)

This compares the 2 dates and we can equate it to 1 if we want the difference in the date to only be 1

Joining tables

SELECT 
	player_atrributes.player_api_id,
	player.player_name,
	player_atrributes.date,
	player_atrributes.overall_rating
FROM
	Player_Attributes
	inner join player on Player_Attributes.player_api_id=player.player_api_id

To simplify

SELECT 
	a.player_api_id,
	b.player_name,
	a.date,
	SUM(a.overall_rating) AS rating,
	COUNT(a.overall_rating) AS count
FROM
	Player_Attributes a
	INNER JOIN player b ON a.player_api_id = b.player_api_id
GROUP BY 
	a.player_api_id,
	b.player_name,
	a.date
	HAVING rating > 85
ORDER BY
	rating DESC
  • implicit AS function
  • we need to specify the table we are getting the data from if we are using joins
  • GROUP BY to specify how we want the data to be grouped, i.e. data is split by those fields
  • HAVING keyword only applies to the result of the GROUP BY function and numeric values

Graph View