Introduction to SQL (Part One)
This is an introduction to learning how to program with SQL. Perfect for beginners or people looking to refresh their memory on SQL.
NOTE: The data I use for images are made-up and DO NOT contain real personal information.
Statement Fundamentals
SELECT is the most used statement. It allows us to get information from a table.
Example syntax: SELECT column_name FROM table_name;
A semicolon (;) is used to end the statement.
In order to get all the columns we must use an asterisk (*) in the SELECT statement. You only need to do this if you need all the columns.
Using an asterisk (*) can increase traffic between database servers slowing down the retrieval, so again only use it when necessary.
Image example asterisk (*):
As you can see we use an asterisk (*) to SELECT everything FROM the payment sections. The FROM payment comes from the “Tables” in your database.
Image example without an asterisk (*):
In this picture, I specify what kind of column I want, so I selected the payment_id column and it returns all the data from payment_id. You can select any column you want FROM payment.
DISTINCT Function
Sometimes a table may contain duplicate values, so you want to use the DISTINCT Function in order to get unique/distinct values.
Example syntax: SELECT DISTINCT column FROM table;
The DISTINCT keyword operates on a column and you can also use parenthesis to make it easier to read.
Example syntax with parenthesis: SELECT DISTINCT(column) FROM table;
Image example:
Before using DISTINCT:
After using DISTINCT:
DISTINCT has removed all the duplicate values and has given us unique values
COUNT Function
The COUNT function returns the number of input rows. We can use COUNT on a specific column or just use COUNT(*), it will return the same result whether you use a specific column or (*)
Example syntax: SELECT COUNT(*) FROM payment;
COUNT can be added with DISTINCT: SELECT COUNT(DISTINCT customer_id) FROM payment;
Image example:
Asterisk(*) or not it will produce the same result
WHERE Function
The WHERE function allows us to specify conditions on columns for rows to be return
Example syntax:
SELECT column1, column2 FROM table
WHERE conditions;
The WHERE clause appears after the FROM clause of the SELECT statement. Conditions are used to filter the rows that will be returned from the SELECT statement
Image example:
You can use >, >=, <, <=, = in your where statement, but in the image, above I’m using WHERE payment_id >= 17510, so it will return payment_id values greater or equal to 17510.
You can use WHERE payment_id < 17510 to get values less than 17510
Also, there are logical operators that you should keep in mind. They will allow you to combine multiple comparison operators:
- AND
- OR
- NOT
Image example:
I use AND staff_id = 1 to return another column (in this case I’ve made it so it returns staff_id to 1) with payment_id.
ORDER BY Function
You can use ORDER BY to sort rows based on column value in ascending or descending order.
Example syntax:
SELECT column_1, column_2 FROM table
ORDER BY column_1 ASC/DESC
ASC = Ascending
DESC = Descending
Image example:
In the image above I use ORDER BY staff_id ASC to get values that go from least to high. You can use ORDER BY staff_id DESC to get values from highest to lowest.
LIMIT Command
The LIMIT command allows you to limit the number of rows you see. It is useful if you want to see the top 10 rows and not the whole row.
LIMIT becomes useful when combine with ORDER BY
LIMIT goes at the very end of the query request and is the last command to be executed, so put your LIMIT command dead last.
Image example:
BETWEEN Operator
The BETWEEN operator can be used to match a value against a range of values
Example syntax: Value BETWEEN low AND high
Can also combine with a NOT logical operator:
Value NOT BETWEEN low AND high
Image example:
I use WHERE customer_id BETWEEN 342 AND 343 to return values that are between 342 and 343.
IN Operator
In some cases, you may want to check on multiple possible value options.
We can use the IN operator to create a condition to check if a value is in a list of multiple options
Example syntax: value IN (option_1,option_2,option_3…)
Another example syntax:
SELECT color FROM table
WHERE color IN (‘red’,’blue’);
Image example:
In the image above I used WHERE amount IN (0.99) to give me values that are equal to 0.99.
* Can be combined with NOT IN such as WHERE amount NOT IN (0.99)
LIKE Operator
The LIKE operator allows us to perform pattern matching against string data using wildcard characters:
- Percent %
- Underscore _
Using an underscore allows you to replace a single character
* Can use multiple underscores
Example syntax (if you want all names to begin with ‘A’):
WHERE name LIKE ‘A%’
Example syntax (if you want all names to end with ‘a’):
WHERE name LIKE ‘%a’
Image examples:
I use WHERE first_name LIKE ‘J%’ to include all the first names that begin with the letter J.
You can also use WHERE first_name LIKE ‘%re%’ to produce all first_name that contains re in their name (in this case it will return names such as Jared, Karen, Irene, Laren, etc because re is contained in their names.
This sums up part one of SQL.
Part two of SQL: Introduction to SQL (Part Two)