So You Want To Create a Database With SQL

I’ve recently been introduced to the greatness that SQL (Structured Query Language) is. SQL is a language that allows you to manage data in a database. It sole purpose is to talk to databases. There are several different database systems such as MYSQL or SQLite (which I’ll be referencing).

To get started with SQLite you’ll have install it by running :

brew install sqlite3

in your terminal.

To create a new database simply run:

sqlite3 database_name.db

Once in the sqlite prompt create a table by running:

CREATE TABLE table_name (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
);

When creating your table, you want to add some columns and column datatypes.

Then to add some data to your table use the command: INSERT INTO

Example- INSERT INTO people (name, age) VALUES ('John', 32);

SQL Queries

SQL Queries are ways to retrieve data from your database. Queries are important because we can manipulate, analyze, and view our data. To do queries in SQL you want to use statements usingSELECT .

Example- SELECT * FROM table_name;

Some of the query modifiers ORDER BY, LIMIT, BETWEEN and COUNT .

ORDER BY

ORDER BY allows you to order the table rows returned by a certain SELECT statement.

Example-

SELECT column_name FROM table_name ORDER BY column_name ASC|DESC;

SELECT * FROM dogs ORDER BY age;

LIMIT

LIMIT allows you to determine the number of records you want to return from a dataset.

Example- SELECT * FROM dogs ORDER BY breed DESC LIMIT 3;

BETWEEN

BETWEEN is used when you want to select all “dogs” whose age is between 4 and 6.

Example- SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

SELECT name FROM dogs WHERE age BETWEEN 4 AND 6;

COUNT

COUNT will count the number of records that meet a certain condition.

Example- SELECT COUNT([column name]) FROM [table name] WHERE [column name] = [value]

These are just some of the great SQL queries that you can do. To find the a list of other commands go to https://www.bitdegree.org/learn/sql-commands-list

I hope this article spark your interest in creating your first SQL database.

Happy coding! -JS