Advance SQL Cheat Sheet

This is part 2 of my SQL cheat sheet. Part one for basics such as building tables, adding values and simple queries can be found here. Now is time to get into some more advance queries.

First off you can use AUTOINCREMENT with your primary key. This will automatically give your primary key a value and you don’t have to put in a value for every new data point added.

AND/OR statements

When you are using the WHERE operator you can use AND/OR to combine WHERE operators. AND will give you an output where both statements are true and OR will give you an output when 1 or the statements is true.

IN

Instead of using multiple OR operators you can use IN. With the IN operator you give the query a list and it will look for anything in the list in the IN statement. IN operators is also used to create subqueries. This means you can find a query in one table but use it in another query from a different table.

LIKE

Use LIKE with any key words surrounded by %. For example it would be LIKE ‘%_____%’ and what this will do is give you an output of any entry that has the word between the % signs.

HAVING

HAVING is just like WHERE but is used in a GROUP BY

CASE

CASE is similar to if statements in python. First you must start the CASE and instead of if you use WHEN and THEN to tell what happens if WHEN is true. You can also use an ELSE statement at the end and use END to end is and name the query column with as AS operator.

SELECT * CASE WHEN column_1 >10 THEN ‘good’ WHEN column _1<10 THEN ‘bad ELSE ‘Whats going on’ END as ‘Good or Bad’ FROM table