5

A beginner SQL cheatsheet — Part I: Syntax

3 years ago

source link: https://dev.to/carlotasoto/a-beginner-sql-cheatsheet-part-i-syntax-1o08
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

neoserver,ios ssh client

A beginner SQL cheatsheet — Part I: Syntax

May 25

・4 min read

SQL is a basic in the world of data analytics (and almost everything is data analytics these days). Even if you're pursuing other fields within tech (like Product Management or Marketing), you will probably need to look at big chunks of data sooner or later... And SQL-speaking tools and databases are incredibly popular.

Indeed, I'm one of those folks that started learning SQL for the first time not long ago. When I first started, I created a cheatsheet for my own reference. It's very basic, but it has proven to be quite useful to me 😅 so I thought it might be useful to other beginners as well.

Before starting, let me expand a bit more:

🤓 Learning SQL: where to start?

I took this course in Udemy and I found it excellent. You will end up with a good understanding of all these basic SQL commands, which I promise you will use in real life. Something I also loved about this course is that it uses PostgreSQL to teach you everything in a practical way, including tons of exercises. PostgreSQL is one of the top used databases in the world, so you can get yourself familiar with the SQL language and with the PostgreSQL interface in one shot, which is great.

👊 How to use this cheatsheet

Here you will find the syntax of the basic SQL statements. Take it as an introductory reference to help you build your own datasheet if you wish, expanding it with more advanced commands. In the next post, I will share query examples illustrating how to use the commands listed here as well.

Here you go.

🔹 SELECTS

-- Returns the complete table 
SELECT * FROM table_name;

-- Returns all the columns in the table, but only the first 10 rows
SELECT * FROM table_name;

-- Returns specific columns 
SELECT column_name_1, column_name_2
FROM table_name;

-- Returns the number of rows in the table 
SELECT COUNT (*) FROM table_name;

-- Returns only the unique values in the column 
SELECT DISTINCT column_name FROM table_name;

-- Returns the number of unique values in the column 
SELECT COUNT (DISTINCT column_name) FROM table_name;

Enter fullscreen modeExit fullscreen mode

🔹 ORDER BY

---Returns the two columns, with the rows ordered according to the values in column 1 in descending order (5,4,3... or Z,Y,Z...)
SELECT column_name_1, column_name_2 FROM table_name
ORDER BY column_name_1 ASC; 

---Returns the two columns, with the rows ordered according to the values in column 1 in ascending order (1,2,3... or A,B,C...)
SELECT column_name_1, column_name_2 FROM table_name
ORDER BY column_name_1 DESC;

Enter fullscreen modeExit fullscreen mode

🔹 WHERE statements

They allow you to impose conditions upon the columns on your table.

General syntax:

-- Returns the two selected columns, showing only the rows in which the stated conditions are true 
SELECT column_name_1, column_name_2 FROM table_name
WHERE condition_1 AND condition_2;

Enter fullscreen modeExit fullscreen mode

Examples:

-- Returns the two selected columns, but only the rows in which column_1 equals to a specific value
SELECT column_name_1, column_name_2 FROM table_name
WHERE column_name_1 = 'specific_value';

-- Returns the two selected columns, but only the rows in which column_1 is greater than a specific value
SELECT * FROM table_name
WHERE column_name_1 > 'specific_value';

-- Returns all the columns in the table, but only the rows in which both column_1 and column_2 are equal to or greater than a specific value
SELECT * FROM table_name
WHERE column_name_1 >= 'specific_value' AND column_name_2 >= 'specific_value';

-- Returns how many rows there are in the table in which both column_1 and column_2 are greater than a specific value
SELECT COUNT (*) FROM table_name
WHERE column_name_1 > 'specific_value'
AND column_name_2 > 'specific_value';

-- Returns all the columns in the table, but only the rows in which column_1 starts in "abc"
SELECT * FROM table_name
WHERE column_name_1 LIKE 'abc%';

-- Returns all the columns in the table, but only the rows in which column_1 ends in "abc"
SELECT * FROM table_name
WHERE column_name_1 LIKE '%abc';

Enter fullscreen modeExit fullscreen mode

🔹 AGGREGATION FUNCTIONS, GROUP BY, and HAVING

Aggregation functions perform mathematical operations to the rows in a column, similar to what you do in Excell with SUM(). Some of the most common ones are (indeed) SUM(), AVG(), MAX(), or MIN().

They often work together with the AS clause, which allows us to give an alias to the result of the aggregate, and with GROUP BY, which will group together rows with the same value.

General syntax:

-- Returns two columns (column 1 and the result of the aggregate under the new name)
SELECT column_name_1, AGG(column_name_2) AS new_column
FROM table_name

-- Returns two columns (column 1 and the result of the aggregate under the new name) grouping the rows by the values in column 1 
SELECT column_name_1, AGG(column_name_2)
FROM table_name

Enter fullscreen modeExit fullscreen mode

The clause HAVING is super useful. It allows you to use the result of an aggregate in an expression while impossing conditions, something that you won't be able to do using WHERE. For example:

-- Returns two columns (column 1 and the result of the aggregate) showing only the rows in which the result of the aggreate is greater than a specific value
SELECT column_name_1, AGG(column_name_2)
FROM table_name
HAVING AGG(column_name_2) > 'specific_value'

Enter fullscreen modeExit fullscreen mode

🔹 JOINs

Lastly, JOINS are statements that combine information from multiple tables. The most commonly used is the INNER JOIN, which is the only one I will include here, for the sake of beginner-friendly simplicity. But there is more: for diving deeper into JOINs, check out this. (Note: In PostgreSQL, if you write JOIN in a query it will be interpreted as an INNER JOIN.)

General syntax:

-- In this example, "column_name" is a column that exists in both table 1 and table 2. This command will merge both tables, using the "column_name" as the reference to combine the rows in the two tables. 
SELECT * FROM table_1
JOIN table_2
ON table_1.column_name=table_2.column_name

-- With the query written like the one above, "column_name" will show in the resulting mega-table two times. To avoid this, list the columns you want to see instead of using * in the SELECT. 
SELECT column_name.table_1, column_1, column_2, column_3 FROM table_1
JOIN table_2
ON table_1.column_name=table_2.column_name

Enter fullscreen modeExit fullscreen mode

See you soon in post nº 2 👋

Recommend

About Joyk

Aggregate valuable and interesting links.
Joyk means Joy of geeK