SQL 101: Create a Table, Insert Data, and Perform Queries

What is SQL?

SQL stands for Structured Query Langauge and can be pronounced either “S-Q-L” or “sequel”. SQL is a language that communicates and manages data within a relational database management system (RDBMS). It allows us to perform all of the CRUD functionalities (create, read, update, delete) via statements using SELECT, DELETE, INSERT, UPDATE, CREATE, and DROP within a database.

The following examples will assume that you have basic knowledge of relational databases. Different relational databases (SQLite, PostgreSQL, MySQL) do not support all of the same SQL data types. For the examples below, we will use data types found in a SQLite database.

Creating a Table

For this example, we’re going to create a simple students table with columns for id name grade gpa tardies and assign a data type to each. We will create the new students table in the database with a SQL statement usingCREATE TABLE:

CREATE TABLE students
  (id INTEGER PRIMARY KEY,
   name TEXT,
   grade INTEGER,
   gpa FLOAT,
   tardies INTEGER);

Inserting Data into the Table

Now our students table exists but it is totally empty. Let’s see how we can add some student data to it using INSERT statements:

INSERT INTO students (name, grade, gpa, tardies)
  VALUES ("Jake", 12, 3.5, 4);
INSERT INTO students (name, grade, gpa, tardies)
  VALUES ("Emily", 10, 3.7, 2);
INSERT INTO students (name, grade, gpa, tardies)
  VALUES ("Sam", 11, 3.5, 3);
INSERT INTO students (name, grade, gpa, tardies)
  VALUES ("Jordan", 10, 3.6, 2);
INSERT INTO students (name, grade, gpa, tardies)
  VALUES ("Victoria", 9, 3.2, 3);

Each INSERT will create a new row of data in our students table. We first specify the name of the table and then add which columns we want to add data for. VALUES are the data that will be inserted into the specified columns, respectfully. Notice we do not have to specify an id for the student as and ordered id will be created automatically for each new row of data, which in this table is a student.

Querying the Table

There are many complex ways to query specific data from tables. Let’s start out with some of the basics. Every query will begin with a SELECT statement.

First, let’s see how we can get all of the data from our students table using the wildcard * to select every column in the table:

SELECT * FROM students

Let’s see how we could get only the names of the students from the table by specifying the name column:

SELECT name FROM students;

Simple, right? Let’s add a little complexity and order the names alphabetically (ascending order is the default) using ORDER BY:

SELECT name FROM students ORDER BY name;

Now let's select data from the name and gpa columns and order them by GPA, from lowest to highest using DESC:

SELECT name, gpa FROM students ORDER BY gpa DESC

Let’s see how we can get the names of students who have a GPA that is higher than 3.5 by implementing a WHERE statement and greater than operator:

SELECT name FROM students WHERE gpa>3.5

How can we change the above statement to get all of the students' data (name, grade, GPA, tardies) who have a GPA greater than 3.5 and are in 10th grade or higher? We can use the bitwise operator AND with the * wildcard:

SELECT * FROM students WHERE gpa>3.5 AND grade>=10

Now, for something a bit different. Let's see how we can perform simple calculations on rows using Aggregate Functions in SQL. These functions will always return a single value:

How can we get the highest GPA from the students table?

SELECT MAX(gpa) FROM students

How about the lowest GPA?

SELECT MIN(gpa) FROM students

And the average students GPA?

SELECT AVG(gpa) FROM students

How many total tardies do students have?

SELECT SUM(tardies) FROM students

How about the average GPA of the students in the 10th grade?

SELECT AVG(gpa) FROM students WHERE grade==10

Conclusion

This has been a very basic introduction to the world of SQL. To continue learning about SQL, check out my blog SQL 102: Joins and Join Tables!