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) or TEXT (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 with ADD:
ALTER TABLE favorite_places ADD COLUMN continent VARCHAR(20);
This would also work if COLUMN is removed:
ALTER TABLE favorite_places ADD continent VARCHAR(20);
We can also use CHANGE or DROP in place of ADD 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:
|
1 2 3 |
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 using SELECT.
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 with AND and OR as follows:
SELECT * FROM favorite_places WHERE year_visited = 2012 AND city = 'Bangkok';
Like
We can search for strings using LIKE 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 use ASC and DESC 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 use LIMIT 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 use OFFSET 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 use IS, IS NOT and NULL 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, use RENAME:
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 just SELECT 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.
Koren Leslie Cohen
Latest posts by Koren Leslie Cohen (see all)
- 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
- Front End Engineer Interview Questions - July 20, 2015
Comments