loading...

Day 3: #100DaysofCode - Oh No, SQL

sincerelybrittany profile image Brittany ・3 min read

#100DaysofCode (38 Part Series)

1) Day 1 of #100DaysofCode - Understanding Iterators 2) Day 2: #100DaysofCode - I updated to Catalina and All HELL Broke Loose 3 ... 36 3) Day 3: #100DaysofCode - Oh No, SQL 4) Day 4: #100DaysofCode - More Iterators 5) Day 5 - #100DaysofCode - Setting up a Sinatra App 6) Day 6 : #100DaysofCode - Setting up a Sinatra App : Part 2 - Config.ru 7) Day 7: #100DaysofCode - Setting up a Sinatra App - Part 3 8) Day 8: #100DaysofCode - Setting up Sinatra Database - Part 4 9) Day 9: #100DaysofCode - ActiveRecord and a Database 10) Day 10: #100DaysofCode - ActiveRecord and a Database 11) Day 11 : #100DaysofCode - RESTful Routes 12) Day 12: #100DaysofCode and Day 1 of #Javascript30 13) Day 13 - #100DaysofCode - #Javascript30 - Day 2 - CSS + JS Clock 14) Day 14: #100DaysofCode - Finalized my Sinatra Project -Security 15) #Day 15: #100DaysofCode - View my Sinatra Project 16) Day 16 - #100DaysofCode - Understanding MVC 17) Day 17 : #100DaysofCode - Knowing your Ruby Version & what Errno::EADDRINUSE means is important! 18) Day 18 of #100daysofCode - 3 challenges 19) Day 19 of #100daysofCode - Hashes 101 20) Day 20: #100DaysofCode - Practice makes perfect 21) Day 21 : #100DaysofCode - Cascading Style Sheets 101 22) Day 22 - #100daysofCode - CSS 101 Part 2 23) Day 23 - #100DaysofCode - Updated My Portfolio 24) Day 24 - #100DaysofCode - Practicing CSS Grid and Emmet Shortcuts on VSCode 25) Day 25 - #100DaysofCode - Intro to Rails 26) Day 26 - #100DaysofCode - Accessing Rails Commands 27) Day 27 : #100DaysofCode - Still reviewing the basic rails concepts . . . 28) Day 28 : #100DaysofCode - Adding Dev.to blogs to personal page 29) Day 29 : #100DaysofCode - Very Simple Rails App CRUD Practice 30) Day 30 : #100DaysofCode - Very basic rails continued 31) Day 31 - #100DaysofCode - Rails Routes 101 32) Day 32 : #100DaysofCode - Resources for learning to code 33) Day 33 - #100DaysofCode - Setting up my app on Heroku 34) Day 34 - #100DaysofCode - New/edit action versus create/update action 35) Day 35 : #100DaysofCode - A Code Challenge Completed 36) Day 36 - #100DaysofCode - Rails form_for versus form_tag 37) Day 37 : #100DaysofCode - 30 seconds of code 38) Day 38 - #100DaysofCode - Built my first basic rails application

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.
  4. 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.

#100DaysofCode (38 Part Series)

1) Day 1 of #100DaysofCode - Understanding Iterators 2) Day 2: #100DaysofCode - I updated to Catalina and All HELL Broke Loose 3 ... 36 3) Day 3: #100DaysofCode - Oh No, SQL 4) Day 4: #100DaysofCode - More Iterators 5) Day 5 - #100DaysofCode - Setting up a Sinatra App 6) Day 6 : #100DaysofCode - Setting up a Sinatra App : Part 2 - Config.ru 7) Day 7: #100DaysofCode - Setting up a Sinatra App - Part 3 8) Day 8: #100DaysofCode - Setting up Sinatra Database - Part 4 9) Day 9: #100DaysofCode - ActiveRecord and a Database 10) Day 10: #100DaysofCode - ActiveRecord and a Database 11) Day 11 : #100DaysofCode - RESTful Routes 12) Day 12: #100DaysofCode and Day 1 of #Javascript30 13) Day 13 - #100DaysofCode - #Javascript30 - Day 2 - CSS + JS Clock 14) Day 14: #100DaysofCode - Finalized my Sinatra Project -Security 15) #Day 15: #100DaysofCode - View my Sinatra Project 16) Day 16 - #100DaysofCode - Understanding MVC 17) Day 17 : #100DaysofCode - Knowing your Ruby Version & what Errno::EADDRINUSE means is important! 18) Day 18 of #100daysofCode - 3 challenges 19) Day 19 of #100daysofCode - Hashes 101 20) Day 20: #100DaysofCode - Practice makes perfect 21) Day 21 : #100DaysofCode - Cascading Style Sheets 101 22) Day 22 - #100daysofCode - CSS 101 Part 2 23) Day 23 - #100DaysofCode - Updated My Portfolio 24) Day 24 - #100DaysofCode - Practicing CSS Grid and Emmet Shortcuts on VSCode 25) Day 25 - #100DaysofCode - Intro to Rails 26) Day 26 - #100DaysofCode - Accessing Rails Commands 27) Day 27 : #100DaysofCode - Still reviewing the basic rails concepts . . . 28) Day 28 : #100DaysofCode - Adding Dev.to blogs to personal page 29) Day 29 : #100DaysofCode - Very Simple Rails App CRUD Practice 30) Day 30 : #100DaysofCode - Very basic rails continued 31) Day 31 - #100DaysofCode - Rails Routes 101 32) Day 32 : #100DaysofCode - Resources for learning to code 33) Day 33 - #100DaysofCode - Setting up my app on Heroku 34) Day 34 - #100DaysofCode - New/edit action versus create/update action 35) Day 35 : #100DaysofCode - A Code Challenge Completed 36) Day 36 - #100DaysofCode - Rails form_for versus form_tag 37) Day 37 : #100DaysofCode - 30 seconds of code 38) Day 38 - #100DaysofCode - Built my first basic rails application

Posted on Jun 4 by:

sincerelybrittany profile

Brittany

@sincerelybrittany

Web Developer | Software Engineer 👩🏾‍💻 | Determined | Music & Dance | #100DaysofCode | #WomenWhoCode

Discussion

markdown guide