Day 3: #100DaysofCode - Oh No, SQL

Originally posted here

I learned SQL in college while taking Business Information Technology Management ("BITM") courses and I remember struggling a bit. Just because I could not visualize the database. I work with excel often, but it's visual, and easy. But with SQL you have to think about how EXCEL works. Here are some things I have learned. If you have a mac just run: which sqlite3. If you get a result /usr/bin/sqlite3 then you are all set with SQL and can code along if you like.

To get a complete list of commands, you can type .help into the sqlite prompt, you can also type .quit and it will save you the headache by taking you out the sql prompt all together.

Best Websites for SQL

If you're more visual like me, download this:

https://sqlitebrowser.org/about/

How to create a database in your terminal:

sqlite3 your_database_name.db

Next create a TABLE:

sqlite> CREATE TABLE your_table_name (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER,);

Add a new column, gender, to your table:

sqlite> ALTER TABLE your_table_name ADD COLUMN gender TEXT;

Add information into to the table:

sqlite> INSERT INTO your_table_name(first_name, last_name, age, gender) VALUES ('Jon', 'Snow', 30, 'Male');

Selecting Data in your table:

So now how do you select particular information out of a TABLE? How do you make queries? To help out with that lets pretend you added the following information to our table.

sqlite> INSERT INTO your_table_name(first_name, last_name, age, gender) VALUES ('Jon', 'Snow', 30, 'Male'), ('Eliza', 'Thornberry', 12, 'female'), ('Tony', 'Stark', 50, 'Male'), ('Penny', 'Proud', 20, 'female');

SELECT Data in a Table:

SELECT [names of columns we are going to select] FROM [table we are selecting from];

We specify the names of the columns we want to SELECT and then tell SQL the table we want to select them FROM. We want to select all the rows in our table, and we want to return the data stored in any and all columns in those rows. To do this, we could pass the name of each column explicitly:


SELECT id, first_name, last_name, age, gender FROM your_table_name;

OR

SELECT * FROM your_table_name;


So your table should look something like that:

id  first_name, last_name, age,  gender) 
1    Jon        Snow       30     Male
2    Eliza      Thornberry  12    Female
3    Tony       Stark       50    Male
4    Penny      Proud       20    Female

If you have duplicate data (for example, two people with the same name) and you only want to select unique values, you can use the DISTINCT keyword. For example:

SELECT DISTINCT name FROM your_table_name;

SELECTING SPECIFIC Data in a Table

If you need to retrieve a specific table row you should use the WHERE keyword to select data based on specific conditions. Like so,

SELECT * FROM [table name] WHERE [column name] = [some value];

For example:

sqlite> SELECT * FROM your_table_name WHERE first_name = "Tony";

That statement should return the following:

3|Tony|Stark|50|Male

You can also use specific comparison operators, like < or > to select specific data. For example:

SELECT * FROM your_table_name WHERE age < 22;

Your results should be:

2|Eliza|Thornberry|12|female
4|Penny|Proud|20|female

Updating Data in a Table

If you want to update and/change data in a table use the following:

UPDATE [table name] SET [column name] = [new value] WHERE [column name] = [value];

An example below:

sqlite> UPDATE your_table_name SET first_name = "Iron Man" WHERE first_name = "Tony";

DELETE Data in a Table

DELETE FROM [table name] WHERE [column name] = [value];

If you wanted to delete Tony Stark because you cried when you watched Avengers End Game then you would use the following:

sqlite> DELETE FROM your_table_name WHERE id = 3;

Next DELETE/DROP a TABLE COMPLETELY:

sqlite> DROP TABLE your_table_name;

Now your your_database_name.db is empty and you would have to start all over to create a new table :)

Fun little SQL facts:

1. Try to use lowercase and camel_case when writing your name in the creating a table.
2. Always and I mean always end with a semi-colon. All SQL statements that you write in your terminal, inside the sqlite prompt, sqlite3>, must be terminated with a semi-colon ;.
3. sqlite3>.tables, will list all the tables you created/ have in your your_database_name database and sqlite3>.schema will show the structure of your table.
Primary Key columns are auto-incrementing. As long as you have defined an id column with a data type of INTEGER PRIMARY KEY, a newly inserted row's id column will be automatically given the correct value.