5 basic commands you’ll need to know if you want to be proficient in SQL

SQL is a powerful language that can be used for all sorts of data analysis tasks.

It’s also a perfect fit for people who want to get into coding because it’s so similar to many other programming languages.

In this article, we’ll break down 5 SQL commands you need to know to get started along with examples so that by the end, you’ll know enough about SQL to start using it on your own projects!

1. SELECT

The first command you need to know if you’re starting in SQL is SELECT. It’s the most basic command in SQL and is used to get data from a table.

Some uses of SELECT include:

• Selecting all of the data from a table
• Selecting specific columns from a table
• Selecting data based on certain criteria (using WHERE)

Examples:

SELECT * FROM tablename

This will give you all of the data from the tablename table. You can also select specific columns by specifying their name after SELECT:

SELECT id, name FROM tablename

This will give you the id and name columns from the tablename table.

SELECT DISTINCT

If you want to select only unique values, you can use SELECT DISTINCT. This command removes duplicate values from the results:

SELECT DISTINCT id FROM tablename

This will give you a list of all the unique ids from the tablename table.

SELECT COUNT

The SELECT COUNT command returns the number of rows in a table:

SELECT COUNT(*) FROM tablename

This will return the total number of rows in the tablename table. You can also count specific columns

2. WHERE

WHERE is another very common command in SQL. It’s used to filter the data that appears in a SELECT statement:

Some uses of WHERE include:

• Filtering data by a certain column
• Filtering data by a certain value
• Filtering data by date range

Examples:

SELECT * FROM tablename WHERE id = 100

This will return only the rows from the tablename table where id equals 100. Multiple conditions can be specified using AND or OR:

SELECT * FROM tablename WHERE (id = 100) OR (name = ‘John’)

This would return all of the rows from the tablename table where either id=100, or name=’John’.

SELECT * FROM tablename WHERE id BETWEEN 100 AND 200

This would return all of the rows from the tablename table where id is between 100 and 200.

SELECT * FROM tablename WHERE id NOT IN (100,200)

This would return all of the rows from the tablename table where id is not equal to 100 or 200.

3. ORDERBY

ORDERBY is also commonly used in SQL. It’s used to sort the results of a SELECT statement. These results can either be sorted by descending order or ascending.

Some uses of ORDERBY include:

• Sort results in ascending order: SELECT * FROM tablename ORDERBY id
• Sort results in descending order: SELECT * FROM tablename ORDERBY id DESC
• Sort results alphabetically: SELECT * FROM tablename ORDERBY name
• Sort results by date: SELECT * FROM tablename ORDERBY created_at

Examples:

SELECT * FROM tablename ORDER BY name

This would return all of the rows from the tablenname table and order by their names. If you want to use multiple columns for sorting, specify them in an comma-separated list:

SELECT * FROM tablename WHERE id > 100 ORDER BY age DESC, name ASC

This will give us all of the rows where ID is greater than 100 and will order those values by descending age first, then ascending name second.

4. GROUPBY

GROUPBY is a statement in SQL used to group the data in a SELECT statement by a certain column.

Some uses of GROUPBY include:

• Summarizing data
• Finding the max or min value for a column
• Getting the average, median, or standard deviation for a column

Examples:

SELECT id, name, SUM(age) AS “Age” FROM tablename GROUP BY id

This will return a table with three columns: ID, Name, and Age. The Age column will have the sum of all the age values in the tablename table grouped by ID.

SELECT max(age) as “Oldest Person” from tablename GROUP BY id

This will return a table with one column: Oldest Person. The Oldest Person column will have the max age value from the tablename table grouped by ID.

SELECT avg(age) as “Average Age” from tablename GROUP BY id

This will return a table with one column: Average Age. The Average Age column will have the average age value for all rows in the tablename table, grouped by ID.

5. LIKE

The LIKE operator is used to match a pattern in a character string. The percent sign (%) is used as a wildcard character, which means that it can represent any number of characters.

Some uses of LIKE include:

• Matching a pattern in a column
• Finding specific values in a column

Examples:

SELECT id, name FROM tablename WHERE name LIKE ‘A%’

This would return all of the rows where the first column (name) contains the letter A at least once.

SELECT id, name FROM tablename WHERE name LIKE ‘%end’

This would return all of the rows where there are columns named “end”.

SELECT * FROM tablename WHERE name LIKE ‘John%’

This will return all of the rows from the tablename table where the name column contains the string John followed by any number of characters (%). The % can be used at the beginning, end, or anywhere in the string.

Start Mastering SQL

The SQL commands we’ve discussed in this blog post are powerful tools that can help you get the most out of your data.

Use these commands to help you analyze and optimize your data, and you’ll be well on your way to mastering SQL.