Introduction to SQL

Introduction to SQL
SQL
Let's get back to relational databases and take a look at the basics of how to access tables and work with table data. 

SQL (Structured Query Language) is the most common computer language for managing data in relational databases. With the power of SQL, you can insert, modify or remove data, and manage data tables.

Your First SQL Statement
To do anything with tables, you need to write a statement, also known as a query. A statement is a request written according to SQL syntax. Your statement should specify what data to select and how to process it. 

SELECT
The SELECT operator takes the selection of one or many rows or columns from a table. SELECT statements look like this:

-- Select columns from the table
SELECT 
    column_1,
    column_2, 
    column_3 ...  
FROM 
    table_name;
 Save
We have two keywords in our statement: SELECT and FROM. SELECT specifies the necessary columns from the database table. FROM specifies the table from which the data should be taken. To keep things tidy and easy to read, we indent the lines after operators. The statement ends with a semicolon  ;. The beginning of a single-line comment is marked with two hyphens:  --.

Note that commands are written in capital letters, and line breaks are inserted after each keyword. These are common, but not required, practices.

Now let's see how this statement works by trying to get data from columns in the Stock table:

Stock
ID	Name	Price	Amount
19960	Hermione Granger's wand	10	15
79789	Delorean DMC-12	500	7
51339	Luke Skywalker's lightsaber	15	3
16307	Daenerys Targaryen's necklace	20	2
-- Getting the columns containing names and prices from the Stock table
SELECT 
    Name, 
    Price
FROM 
    Stock;
 Save
Here's the result of our query:

Stock
Name	Price
Hermione Granger's wand	10
Delorean DMC-12	500
Luke Skywalker's lightsaber	15
Daenerys Targaryen's necklace	20
To select all columns from the table, add  * to the SELECT operator:

SELECT 
    *
FROM 
    Stock;
 Save
To add a condition to our query, we'll use the WHERE operator:

SELECT 
    *
FROM 
    Stock
WHERE 
    Price < 100; -- Defining the condition of row selection
 Save
The order of operators is strictly defined:

1)SELECT 

2)FROM

3)WHERE

The query above will return the following result:

Stock
ID	Name	Price	Amount
19960	Hermione Granger's wand	10	15
51339	Luke Skywalker's lightsaber	15	3
16307	Daenerys Targaryen's necklace	20	2
PostgreSQL: Using Relational Databases in Express.js Apps
If you decide to use relational databases for your application, you'll need to choose an appropriate database management system (DBMS) and use its Node.js driver to connect it to your app.

We recommend using PostgreSQL (or Postgres), a free and open-source relational database management system. PostgreSQL is widely used by companies for their database needs.

Install the npm module called pg-promise first:

npm install pg-promise
 Save
pg-promise is a PostgreSQL client for Node.js that is based on a powerful query-formatting engine and supports methods that return a promise.

Connect your database to the Express.js app:

var pgp = require('pg-promise')(/* options */)
var db = pgp('postgres://username:password@host:port/database') //connect to your local databasw

db.one('SELECT $1 AS value', 123) //create a query to pull desired data
  .then(function (data) {
    console.log('DATA:', data.value)
  })
  .catch(function (error) {
    console.log('ERROR:', error)
  })
 Save
Going deeper into SQL
This lesson just covers the basics of SQL — do you want to know more? Perfect! We've prepared a completely free, self-paced course for SQL newcomers, chock full of helpful theory and exercises. Check SQL101 out!