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;

All columns 

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;

Result:

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;

Result:

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;

Result:

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;

Result:

Credits Mosh 

By Cristina Rojas.