Chapter 5 - What is SQL?

Just to get it out of the way, it is a query language for creating and managing relational databases (more on that later). Take your time with this section and try to understand it as best as you can. You'll be using it. A lot.

Part 1: Explaining it to a Child

Databases are a place to store lots of data in an organized way. Databases are made up of tables, and tables are made up of rows and columns.

Imagine you were opening a new school and all of your records were being stored on sheets of paper in an excel style format. Each sheet of paper has a big excel grid printed on it. You have one sheet of paper that holds all the student data with columns, like:

  • first_name
  • last_name
  • SSN
  • date_of_birth
  • student_id.

Another sheet of paper of paper holds all the instructor records with columns, like:

  • first_name
  • last_name
  • subject
  • salary
  • room_number.

And yet another sheet holds all the information about classes, like:

  • course_name
  • subject
  • required_books.

Your school might need 20+ sheets of grid paper to keep track of all their assets and resources and you keep them all in a nice big box for safe keeping.

The box is your database. It's the place that houses all of your data. The grid sheets are tables. Your database has a student table, a staff table, a course table, and so on.

Part 2: Explaining it to an Adult

Applications that run on your computer store information on your computer's hard drive. If you open a note taking application on your computer, write a note, and then exit the program, it will still be there the next time you open the application. That's because your computer stored the data in its hard drive before you closed the application. When you open the application, it simply asks the hard drive to retrieve the data and then displays it.

This works perfectly on your computer, but what about an application that lives online like Google Docs? It doesn't work to store the data on your hard drive because then it's only accessible on your machine. A database is a big centralized computer with a massive hard drive. It allows all of your users to access their data from any computer.

How do I access a data of information?

Through the server. Facebook stores millions of user's data in their database. When you click on a friend, your computer sends a request to server. The server asks the database for that friends specific data and sends it back to your computer, so that your browser can render it for you.

How do I add and remove data from a database?

Well, that depends on the database. Most databases use a language called SQL. SQL is a language used to create databases and tables on your database server and to query, add, remove, and alter information in those tables.

Part 3: Explaining it to a Future Developer

SQL (often pronounced "Sequel") stands for "Structured Query Language" and is a powerful programming tool that was specifically designed to allow programmers to create, populate, manipulate, and access databases so as to provide them with an easy method to deal with server-side storage.

Data using SQL is stored in tables on the server much like those you would create in Microsoft Excel or in Google Sheets, making the data easy to visualize and search through.

There are several different dialects of SQL, such as MySQL, MariaDB, PostgreSQL, and Microsoft SQL Server to name a few. These dialects vary in minor ways, and different companies will use different dialects, but the fundamental operation is the same.

How can I use a mySQL database?

This handy video can walk you through the below steps:

  1. In your Browser, open up DB Fiddle. This is a SQL Sandbox that we'll use to practice our SQL commands.

  2. Create a new database by typing CREATE DATABASE new_db;

  3. Tell your client that the following operations are going to take place on that new database by typingUSE new_db;

  4. Create a new table in that database

CREATE TABLE friends (
  id INT AUTO_INCREMENT NOT NULL,
  name VARCHAR(255) NOT NULL,
  cool BOOLEAN DEFAULT false,
  birthday DATE,
  PRIMARY KEY (id)
);

The above CREATE TABLE command will create a new table called friends. This table will have columns called id, name, cool, and birthday.

  • id

  • This column is of type INT, which is to say it's a 32-bit signed integer, having possible values ranging from −2,147,483,648 to 2,147,483,647.

  • This column further has the attributes of AUTO_INCREMENT and NOT NULL.

    • AUTO_INCREMENT will automatically set the value of the column whenever a row is created, starting with 1 and incrementing by 1 for each new record.

    • NOT NULL ensures that this column must always have a value. Null values are not allowed.

  • name

  • This column is of type VARCHAR. VARCHAR is short for Variable Character. The 255 option sets the maximum length for this field, in this case 255 characters.

  • This column also has the NOT NULL attribute, ensuring it must always have a value.

  • cool

  • This column is of type BOOLEAN. A BOOLEAN is simply true or false.

  • This column has a DEFAULT constraint. If no value is provided when a new record is created, the cool field for that record will default to false.

  • birthday

  • This column is of type DATE. A DATE value is stored in the format YYYY-MM-DD.

  • This column has no attributes or constraints, and if no value is provided for the birthday field when a new record is created, the value will be null.

  • PRIMARY KEY (id)

  • A primary key is a field which uniquely identifies each record in a table. We're telling our database that for this table, we want it to use the id field we created as our primary key for this table. Since id auto-increments, each row's id is guaranteed to be unique, allowing us to safely update or delete records from our table.

How can I store and modify data in my new table?

Creating a new row/record

  1. Tell your SQL server what database you're using

    • USE new_db;

  2. Add a new row to the table we previously created.

INSERT INTO friends (name, cool, birthday) VALUES ('Ming', true, '1984-08-13');

  • Note the fact that the first set of parentheses specifies the columns that correlate exactly with the second set of parentheses, which specifies the values for each column.

  • We also did not have to specify an id because that will be auto-generated for us, thanks to the AUTO_INCREMENT attribute we added when we created our table.

  • Add another new row

INSERT INTO friends (name) VALUES ('Ahmed');
  • The only value that we must provide for this table is name, because it is not allowed to be null, and it does not have a DEFAULT value set.

Viewing the data stored in the database

  1. Tell your SQL server what database you're using

    • USE new_db;

  2. Query all columns of all rows in your table.

    • SELECT * FROM friends;

  3. If you want to filter your results, use the WHERE clause in your SQL statement.

    • SELECT * FROM friends WHERE name = 'Ahmed'

  4. You can also limit what columns are returned

    • SELECT name, birthday FROM friends;

Updating a record in the database

  1. Tell your SQL server what database you're using

    • USE new_db;

  2. Update the table, specifying the new values you wish to set.

  3. UPDATE friends SET name = 'Kaitlyn' WHERE id = 1;

  4. Note the use of the WHERE clause in the UPDATE statement.

  5. UPDATE will update every record which matches the provided conditional or all rows if no conditional is supplied. id is the primary condition we will generally wish to match, since id is guaranteed to be unique by virtue of being the PRIMARY KEY.

Deleting a record in the database

  1. Tell your SQL server what database you're using

  2. USE new_db;

  3. Update the table, specifying the new values you wish to set.

  4. DELETE FROM friends WHERE id = 1;

  5. Much like UPDATE, DELETE will operate on every record which matches the provided conditional. id is the primary condition we will generally wish to match, since id is guaranteed to be unique by virtue of being the PRIMARY KEY

Assignment (Required)

Utilizing the material you've learned from this chapter you will write the SQL required to create new database, new table, and finally fill the new table with data. This assignment will require you to use DB Fiddle.

Scenario

Barns and Nobbles, a global leader in the retail books and literature space, has tasked your team with creating a prototype application that tracks all of the inventory for a Barns and Nobbles branch located somewhere in the Pacific North West. You're in charge of creating the database that your teams' application will access to create, read, update, or delete data from.

  1. Start by creating a new database called, "bn_inventory".

  2. Next, you'll create a new table called, "books" that will store all necessary data about the books that every branch can potentially carry. Don't forget to start with "use bn_inventory"! The table should consist of five tables:

  3. id

  4. title
  5. author
  6. in-stock
  7. quantity
  8. primary key

For each field, make sure that you specify the proper data type. For instance, the field "name" should likely be VARCHAR whereas "in-stock" might be a BOOLEAN.

  1. Write the SQL to create at least three rows of data. Because this is just a prototype feel free to create dummy data.

  2. Write the SQL to View all of the data within your "books" table.

  3. Write the SQL to Update a row by accessing that row specifically by id.

  4. Finally, write the SQL to Delete a row by accessing that row specifically by id.

After completing all of the steps above, give yourself a pat on the back! You've completed your part of the application!

Submission

Write all of your SQL queries in a Google Document titled, "MySQL Prework Assignment". You'll be submitting a link to your Google Document in Bootcampspot so make sure that you choose the "Anyone with the link can View" share option! Be sure to submit this link under the assignment, "Pre-work: MySQL" in Bootcamptspot.

If you have any issues submitting your prework, check out this video for step-by-step instructions on how to do so! Prework Submission Video