My name is Mason Ellwood, and I’m currently working on Flatiron School’s Online Full Stack Web Development Program. Each week, I’ll be writing about my experience, what I’m learning, and tips on learning to code.
In this post I am going to be skipping through a lot of material that was covered within The Flatiron School, this mainly entailing Object Oriented Ruby. At this point, or if you are following along with me learning Ruby you should have a basic understanding of what OO Ruby is and what it entails. If not be at the point that it will be easy to pick up where I left off on your own. Doing this will allow me to write about the current material I am working through and allow you to quickly catch up to where I am in the school, giving you a better understanding of what the school offers in terms of material covered as a whole.
So here we go!
What the heck is SQL:
SQL is a structured data language that is used for managing data in a database. Its sole purpose is for talking with the database. You may also people talking about SQL in terms of a special purpose or domain specific programming language.
SQL has taken a few forms as MySQL, PostgreSQL, or SQLite which for this course we will be using. And as a web developer, you will be working closely with databases to manage data that is associated with your application. But how do we create a database and access information?
First you have to make sure that SQLite is installed. Because I am using a Mac, this makes this process much easier, and a majority of the time unnecessary because it comes preinstalled on your machine. In your terminal create a new folder that will hold you database file. Then in your terminal navigate to that folder and type sqlite3. This should return something along the line of what is shown below.
This below tells you that there is a version of SQLite downloaded on your machine. If you receive an alternative message or error, please visit homebrew and download their gem package, this will install SQLite on your machine and create the appropriate result. To create a database now that you know that SQLite is live, run (SQLite test_sqlite.db). Within the (sqlite>) prompt not accessible to you, type (create table test_table(id);) and then (.quit). Then open up the folder that you creates your database by using (open .).
This will then create a database for you that you will now be able to operate on. Keep in mind that all SQL statements that you write in your terminal, inside of SQLite prompt must be terminated with a semicolon, though this excludes .quit.
SQL Database Basics:
A relational database like SQLite stores data in a structure we refer to as a table. A table in a database if a lot like a spreadsheet. When defining columns in out table, then we can store related data in those columns. This stored data is referred to as records, which are placed in rows within columns in our database.
Keep in mind though with everything there is the convention of typing. Column names follow simple rules, such as always use lower case and when column names are multiple words, use snake case.
So let’s start from the beginning. Let’s create a database and define its schema.
Once the database has been initiated, you then need to define your table. Running something like CREATE TABLE cats; will result in error. This is because SQLite expects definitions of the structure being created. When we create database tables, we need to specify some column names, along with the type of data we are planning to store in each column.
Capitalization is arbitrary but rather convention for visually separating SQL commands from names. This will save you time later, cutting time down through debugging.
End Notes:
Awesome! So now you know how to create and add columns to your table, within your newly created database. SQL so far has been surprisingly fun, and learning to query a database is a great skill to be acquainted with. In the next post, I will get into how to operate, add and subtract from your database.
Please leave comments or questions below, and I will answer them as well as I can.
Leave a Reply