Learn SQL – SELECT statement
Hi, is possible to have only one line of SQL statements, but is better to have break lines in each query sentences when we have a big query.
Small query is ok in one line, like:
SELECT * FROM customers
Big queries is better in multiple lines, like:
SELECT * FROM customers WHERE customer_id = 1 ORDER BY first_name;
And as much as possible is better to specify the name of the columns that we want to get instead of use * (all) because if we have a database with lots of information (many columns, records) and if we want to bring back all that data, that’s going to put a lot of pressure on the database server (MySQL for example), as well as the network.
Order of the columns in SELECT
Ok, now if we are using the SELECT statement and we specify the name of the columns, then in that order as we specified the data will be displayed, for example:
SELECT last_name, first_name, customer_id FROM customers;
Arithmetic expression inside SELECT
We can insert arithmetic expression in columns, like this:
SELECT last_name, points + 100 FROM customers;
Or we can manipulate this to see points column first and aside the sum in other new column:
SELECT last_name, points, points + 100 FROM customers;
Result: remember we can use +, -, *, %, /, etc. or or combine them but do not forget the order of the operators, we can use parenthesis too.
Adding “alias” to the column
Using “AS” statement we can add an alias to the column as name
SELECT last_name, points, points + 100 AS discount FROM customers;
Add space in column alias
We have to surrounding the alias with single quotes
SELECT last_name, points, points + 100 AS 'discount factor' FROM customers;
Show only unique rows in case of duplicated row with same data:
For this case we have 2 records with the same state data: record 1 and 2 with VA in state column.
If we wanted to get a unique list of states (column) in the results? well for this we can use DISTINCT statement, like this:
SELECT DISTINCT state FROM customers;
Result: We’ll retrieve a unique list of states from the customers table.
with the DISTINCT keyword we can remove duplicates.
Set 10% more expensive for products in products table:
SELECT name, unit_price, unit_price * 1.1 AS new_price FROM products;
By Cristina Rojas.