SQL Tables: Bread and butter of any SQL query

In this section, I will try to go over everything there is to know about tables in SQL.

Disclaimer: I will be writing queries for MySQL since that is one of the most popular open-source databases out there. Let me know if you would like to see a similar post about all the other RMDBS.

Creating a simple table

The general syntax for creating a table is:

CREATE TABLE table_name(
 column_name DATA_TYPE CONSTRAINT
);

Let’s create a table my_table with 3 columns. We need to specify the datatype associated with that column. I will be discussing other data types in a future article. If needed, we can also provide constraints on the individual column here. Currently, we don’t have any constraints

CREATE TABLE my_table (
    id INT,
    name VARCHAR(15),
    alias VARCHAR(25)
);

Inserting data into the table

The general syntax for inserting data into a table is as follows:

INSERT INTO table_name (column...)
VALUES (value...);

Specifying columns is optional, if columns aren’t specified then by default all the columns are selected. The sequence in which values are specified is very important, it should match the columns specified or the columns of the table.

Let’s add a single row item into our table

INSERT INTO my_table
VALUES (1, "Bruce", "Batman");

Here we don’t specify the columns in our query, so by default, it selects all the columns. If we only want to add data into specific columns then we can specify them in our query.

We can also add multiple rows in one query, as follows:

INSERT INTO my_table
VALUES 
    (2, "Clark", "Superman"),
    (3, "Barry", "Flash");

renaming table

We can still rename a table and the columns of the table after creation.
NOTE: Once modified, the table will be permanently changed. There is no undo unless there has been a backup created.

The general syntax is:

-- Altering table name
ALTER TABLE old_table_name
RENAME TO new_table_name;

-- Altering column name
ALTER TABLE table_name
  RENAME COLUMN old_column_name
  TO new_column_name;

Let’s change our table name to “superheroes”, and the “name” column to “real_name”.

ALTER TABLE my_table
RENAME TO superheroes;

ALTER TABLE superheroes
    RENAME COLUMN name
    TO real_name;

Please note, I am using “superheroes” as the table name in my 2nd part since I have already changed the table name in the 1st part.

Display, add, and delete columns

Let’s look at some basic queries for column manipulation

Display columns

We can use: DESCRIBE table_name to get the details about the columns.
It returns details with the field, type, null, key, default, and extra columns.

Adding columns

The general syntax for adding a column is:
ALTER TABLE table_name
ADD column_name datatype constraints

Now let’s add a new column “has_super_powers” and constraint that to only have either Y or N

ALTER TABLE superheroes
	ADD has_super_powers CHAR(1)
    CONSTRAINT super_power_check 
    CHECK (has_super_powers IN ('Y', 'N'));

There are multiple ways to add a constraint, here I am going with the above method, this way I can provide a name to my constraint.

Deleting columns

The general syntax for deleting a column is:
ALTER TABLE table_name
DROP column_name

If we want to delete the newly created column, then we can do so by using the following query

ALTER TABLE superheroes
	DROP has_super_powers;

Display, add, and delete ROws

Let’s look at some basic queries for rows manipulation

Display rows

We have already gone through this section in the “SELECT” portion of this article: SQL Simplified: Let’s go back to the basics
But to quickly go over it, we can use the following general syntax to display rows in a table
SELECT * FROM table_name;

Adding rows

We can use the INSERT statement to add row(s) to our table.

Deleting rows

In order to delete rows, make sure to use the WHERE clause in your statement, otherwise, we will end up deleting all the rows from the table. The general syntax is:
DELETE FROM table_name
WHERE condition;

Let’s say we want to delete our 3rd row with id = 3, then we can use the following query:
NOTE: We may need to set the SQL_SAFE_UPDATES to 0, in order to delete the row without providing a primary key

SET SQL_SAFE_UPDATES = 0;
DELETE FROM superheroes
WHERE id = 3;
SET SQL_SAFE_UPDATES = 1;

Updating data in a table

Now let’s look at how we can update the data within a table.

Updating a column of data

Here, we want to update the “has_super_powers” to have a value of Y for all the rows, so we can use the following query to do so:

UPDATE superheroes
SET has_super_powers = 'Y';

Setting the column to anything other than ‘Y’ or ‘N’ will result in an error since we have added a constraint to it which forces the values to be either ‘Y’ or ‘N’

Updating a row of data

Looking at the above data, we know that Batman doesn’t have any superpowers so let’s update the value for that particular row.
NOTE: It’s very important to include WHERE clause along with SET clause when updating a specific row. Without WHERE the entire table would be updated

UPDATE superheroes
SET has_super_powers = 'N'
WHERE alias = "Batman";

deleting table

In order to delete the table, we can simply call DELETE TABLE table_name

---

I will be adding more details to this article as I come across other topics related to TABLES in SQL.