Part 2. Databases for beginners - What is a SQL? Getting started with PostgreSQL

By Lovisa Johansson - posted
Part 2. Databases for beginners - What is a SQL? Getting started with PostgreSQL

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.

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

database table

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

database table

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.

database table

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. database table

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;

database table
database table

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.

If you have any questions, comments or need feedback, please do not hesitate to contact us at support@elephantsql.com .