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.

Players

player_id	firstname	lastname	birthday	country
1	Paul	Pogba	1993-03-15	France
3	Casey	Short	1990-08-23	United States
1	Zlatan	Ibrahimović	1981-10-03	Sweden

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 firstname and lastname for you player with player id 3, you could write your query like:

SELECT firstname, lastname
FROM players
WHERE player_id = 3;