Part 2 of Databases for beginners will give a brief understanding of the standard database language SQL. We will show how to create a table, how to insert data into a table and how to view the data in your database.
Our previous article explained databases and import database concepts were defined. We also showed how to create a database for free, in the cloud. In this part of the series, we show how to use SQL in the simplest way possible. We will create a database table called players and insert player data into the table.
2. Get started with SQL
SQL is the standard language for accessing relational databases. This tutorial will teach you how to use SQL to access and manipulate data in your database. Standard SQL commands are "SELECT", "INSERT", "UPDATE", "DELETE", "CREATE", and "DROP" and they can be used to accomplish almost everything that one needs to do with a database.
A tab to ElephantSQL SQL browser can be found in the console for your database instance. From there you can execute SQL queries directly from your web browser.
Press the "browser"-tab on your new database instance. Our first mission will be to create a new database table and add some data to it.
2.1 Create a new database table
PostgreSQL CREATE statement is used to create a new empty database table. The basic syntax for CREATE is specified below, where  is optional:
CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ... );
Execute the following query in your ElephantSQL SQL browser to create the players table:
CREATE TABLE players ( player_id SERIAL PRIMARY KEY, firstname VARCHAR(50), lastname VARCHAR(50), birthdate DATE, country VARCHAR(50) );
You have to specify what types of data that you want to store inside each and every table column when creating a SQL table. We have chosen three different datatypes in the query above. More information about datatypes can be found here.
SERIAL: Creates an auto incrementing integer. player_id will be automatically generated for each insert.
PRIMARY KEY: A primary key indicates that a column or group of columns can be used as a unique identifier for rows in the table. player_id is the primary key.
VARCHAR(n): A character string with variable length, where maximum length is n.
DATE: Stores year, month, and day values
2.2 Insert data into players
A new table in PostgreSQL contains no data. PostgreSQL INSERT statement is used to insert data into a table. The basic syntax for INSERT is specified below, where  is optional:
INSERT INTO TABLE_NAME (column1 [, column2,...columnN]) VALUES (value1[, value2,...valueN]);
The command requires the table name and column values. Execute the following query in your ElephantSQL SQL browser to populate the players table with two new players, Paul Pogba and Casey Short.
INSERT INTO players (firstname,lastname,birthdate,country) VALUES ( 'Paul', 'Pogba', '1993-03-15', 'France'); INSERT INTO players (firstname,lastname,birthdate,country) VALUES ( 'Casey', 'Short', '1990-08-23', 'United States');
Data is inserted one row at a time. You can insert more than one row, as in the example above.
2.3 Select data from a database table
PostgreSQL SELECT statement is used to fetch the data from a database table which returns data in the form of the result table. The basic syntax for SELECT is specified below, where  is optional:
SELECT "column1" [,"column2","column3",..] FROM "tablename" [where "condition"];
"column1" [,"column2","column3",..] determine which columns that should be returned in the results. You can select as many column names that you'd like, or you can use a "*" to select all columns.
SELECT * FROM players;
The where clause is optional. It specifies rows that will be returned or displayed, based on the criteria described after the keyword WHERE.
If you need the birthdate for you player with player 3, you could write your query like:
SELECT firstname, lastname FROM players WHERE player_id = 3;
2.4 Update data in a database table
PostgreSQL UPDATE statement is used to update data in a database table. The basic syntax for SELECT is specified below, where  is optional:
UPDATE tablename SET column1 = value1, column2 = value2...., columnN = valueN WHERE ["condition"];
UPDATE players SET lastname = 'new_lastname' WHERE player_id = 3;
We have now tried to execute some standard SQL queries from the web browser, and it's time to show how to do this programmatically and how to use your database in combination with your application.