Koren Leslie Cohen

  • About
  • Blog
  • Contact

0 comments

SQL

SQL Cheat Sheet

May 31, 2014 by Koren Leslie Cohen

web developer

SQL is used to create databases in which to store information.  Using SQL is like using an Excel spreadsheet, except the information is entered and manipulated differently.

Creating a Database

If you want to create a database in your terminal, you need to run SQL.  I am currently using sqlite3.  Once the program is running (your terminal prompt will look similar to this: sqlite >), you must create a database:

CREATE DATABASE dbname

If I want to keep track of travel information, I would enter CREATE DATABASE travel.

Creating Tables

Say we want to keep track of my favorite places to travel.  We can do so by creating the table favorite places.  In this table, we want two columns – city and year visited.

Columns can be one of three types: (1) strings, or sequences of characters inside single quotes, (2) numeric values including integers, fixed points and floats, and (3) dates and times, as either date, time or datetime.  Strings are either declared as VARCHAR (shorter strings with a defined number of characters) orTEXT (longer strings).

We would create the above table with the following syntax:

CREATE TABLE favorite_places (city VARCHAR(20), year_visited INTEGER);

Insert

Insert is used to insert items into an existing table:

INSERT INTO table (columns) VALUES (values);

If I want to add Bangkok in 2012 to my table, I would type the following:

INSERT INTO favorite_places (city, year_visited) VALUES (‘Bangkok’, 2012);

This can also be accomplished with:

INSERT INTO favorite_places VALUES (‘Bangkok’, 2012);

In the above example, it’s important to remember the ordering of the original entries.  If the order is unknown, we can specify the order:

INSERT INTO favorite_places (city, year_visited) VALUES (‘Bangkok’, 2012);

It’s also possible to add multiple rows at the same time:

INSERT INTO favorite_places VALUES (‘Bangkok’, 2012), (‘Marrakech’, 2010), (‘Ibiza’, 2010);

We can also insert items into a table using the following:

INSERT INTO favorite_places SET city = ‘Bangkok’, year_visited = 2012;

Update

Update is used to update items in a table:

UPDATE table SET column = true WHERE column = false AND value = value;

Say I added Tokyo to my table, but entered the year visited as 2013 instead of 2012.  I would update this entry with the following:

UPDATE favorite_places SET year_visited = 2012 WHERE year_visited = 2013 AND city = ‘Tokyo’;

It’s not necessary to add the additional parameter of AND city = ‘Tokyo’.  If, for example, we noticed that for each city visited in 2004, we accidentally typed in 1004, we could simply change the column values without adding the additional parameter, as follows:

UPDATE favorite_places SET year_visited = 2004 WHERE year_visited = 1004;

Alter

ALTER is used to alter an existing table.  Perhaps we want to add a column for continent, we can do so withADD:

ALTER TABLE favorite_places ADD COLUMN continent VARCHAR(20);

This would also work ifCOLUMN is removed:

ALTER TABLE favorite_places ADD continent VARCHAR(20); 

We can also useCHANGE orDROP in place ofADD to change the name of a column or to delete a column.

Select

To select all values from a table, enter: SELECT * FROM table;.  In the above example, this would return:

sqlite> SELECT * FROM favorite_places;
Bangkok|2012
Tokyo|2012

Another (longer) way of requesting the same information is SELECT favorite_places.city, favorite_places.year_visited FROM favorite_places;.  This would return the same output as above.  It does’t matter in which order we enter the columns.

Although it’s nice to be able to view all data, it’s more useful to be able to filter the data.  We do this by querying the table usingSELECT.

Query

Say we want to know which cities I visited in 2012:

SELECT * FROM favorite_places WHERE year_visited = 2012;

Notice that the= is singular in SQL.  Queries can also use the following comparators:

!=   >  >=  <   <=.

And Or

Operators can be combined withAND andOR as follows:

SELECT * FROM favorite_places WHERE year_visited = 2012 AND city = ‘Bangkok’;

Like

We can search for strings usingLIKE and% (wildcard) as follows:

SELECT * FROM favorite_places WHERE city LIKE ‘Bang_ok’;

In the above example, SQL would return all seven letter cities that start with ‘Bang’ and end with ‘ok’.  The below example would return all cities where the string included ‘ang’.

SELECT * FROM favorite_places WHERE city LIKE ‘%ang%’;

The wildcard can be placed at the beginning or end of a string, or both.

Order By

If we want to order the results, we can use ORDER BY:

SELECT * FROM favorite_places ORDER BY year_visited;

We can also useASC andDESC to choose whether the results are listed in ascending or descending order:

SELECT * FROM favorite_places ORDER BY year_visited DESC;

SELECT * FROM favorite_places ORDER BY city ASC, year_visited DESC;

Limit

We can useLIMIT to limit the results.  For example, if we wanted to know only ten favorite places:

SELECT * FROM favorite_places LIMIT 10;

This would create an upper bound on the number of rows returned.  We can also useOFFSET  following limit as LIMIT 10 OFFSET 20;.  This would skip the first 20 rows and return the next 10 rows.

Null

Say we want to see which cities have years missing, or query the table for only those cities with years.  We can useIS, IS NOT andNULL to do so:

SELECT * FROM favorite_places WHERE year_visited IS NULL;

SELECT * FROM favorite_places WHERE year_visited IS NOT NULL;

Rename

To rename a table or database, useRENAME:

RENAME TABLE favorite_places TO favorite_cities;

Delete

Deleting Data

To delete everything from our table:

DELETE FROM favorite_places;

If we want to delete specific entries from a table, we can use the same commands we use to select items. DELETE is essentially justSELECT followed by deletion of the content selected.

Deleting Tables & Databases

DROP is used to delete tables and databases:

DROP TABLE favorite_places;

DROP DATABASE travel; 

We can also use DROP SCHEMA to delete a database.  Deleting a database removes the schema and all data.

  • About
  • Latest Posts
Connect
Koren Leslie Cohen
Product manager at Facebook. Former senior product manager at Dollar Shave Club in Los Angeles and software engineer at J.Crew / Madewell in New York City. Recovering trial lawyer.
Connect
Latest posts by Koren Leslie Cohen (see all)
  • PM Career Story - April 28, 2022
  • How to Transition into Product Management - December 26, 2017
  • What I’ve Learned in My First Few Months as a Product Manager - October 14, 2015

Related Posts

Share

Facebook Google+ Twitter Pinterest Email

Comments Cancel reply

Your email address will not be published. Required fields are marked *

*

code

Back to Blog

  • GitHub
  • Instagram
  • LinkedIn
  • RSS
  • Twitter

Looking for something?

Copyright 2023 Koren Leslie Cohen