Common SQL commands

SQL is a vital part of database management systems. It provides a lot of useful functionality to store and later update data. In this shot, we will cover the most commonly used SQL commands, namely:

• CREATE DATABASE: Creates a new database
• CREATE TABLE: Creates a table in a database
• INSERT INTO: Inserts rows into a table
• SELECT: Selects and displays data from a table
• UPDATE: Updates values in a table
• DELETE: Deletes rows in a table
• DROP TABLE: Deletes tables in a database

How to create database

Before we can manipulate data, we need to have a database to store it in. This can be done using CREATE DATABASE followed by the name of the database we need to make. For our purpose, let’s create a database called edpresso that’llstore tables of edpresso shots and the names of edpresso employees. If you are using MySQL, type in the following command to create the database:

CREATE DATABASE edpresso;

For sqlite3, type:

sqlite3 edpresso.db

The screenshots in this shot will be from commands run on sqlite3.

How to create a table

Now that we have our database, let’s create an employee table with shot table in it. For the first table, type the following:

CREATE TABLE Employees (
    ID int,
    Name varchar(255),
    Age int,
    Position varchar(255)
);

CREATE TABLE Shots (
    ID int,
    Title varchar(255),
    Category varchar(255)
);

Now, to check if your tables have been made, type .tables to see all the tables in your database.

How to insert data

Now that our tables are ready, we can insert data into them. To add a new row of data to our tables, use the following syntax:

INSERT INTO Employees
VALUES (ID_Value, Name_Value, Age_Value, Position_Value);

INSERT INTO Shots
VALUES (ID_Value, Title_Value, Category_Value);

Using INSERT INTO, we can add multiple rows into our tables. In the picture below, four rows were available to the Employees table, and two rows were added to the Shots table.

How to view data

Now that we have data in our table, we can use the SELECT command to view them. If we wish to view all the data in a table, we can use the * wildcard and list the table name after the FROM clause, like this:

SELECT * FROM tablename;

Let’s do this for our tables and see what shows up:

Now, what if we want to see only certain columns from our Employees table? SQL allows us to do this by specifying the column names and separating them by commas. This is shown below:

SELECT columnname1, columnname2, ... FROM tablename;

Let’s see if we can fetch employee names and their positions from the Employees table with the above syntax:

We also filter rows based on values using the WHERE clause. The syntax for this is:

SELECT columnname1, columnname2, ...
FROM tablename
WHERE condition

Let’s retrieve the names of all employees who are older than 30 using the WHERE clause:

How to update data

Now, let’s suppose we realized we have the name of the employee with ID 4 spelled wrong, and we need to update his name from ‘Muaaz’ to ‘Mueez’. The UPDATE command would be the simplest way of changing this. The generic syntax for it is:

UPDATE tablename
SET columnname1 = value1, columnname2 = value2, ...
WHERE condition;

Let’s do this for our specific context and then display the results with a SELECT statement:

How to delete data

There are times when rows in a table need to be deleted. For instance, let’s suppose Maria is moving and needs to resign. To delete her data from the Employees table, we would use the DELETE command, like this:

DELETE FROM tablename WHERE condition;

Let’s try this for our case:

How delete a table

Finally, let’s go over how we can delete a table from a database. The syntax for this is:

DROP TABLE tablename;

Let’s use this command to drop the Shots table:

Putting it all together

In the widget below, we’ve put all the commands studied above together. Play around with the code and see what changes you can make to the tables: