Why SQLite is simple to use!

#sql #activerecord

Coding can be frustrating. There are some parts that will come easily to you. You'll catch on fast and maybe even question if it was really that simple. SQLite may at first seem complicated upon your first glance, but it is truly a simple tool you can learn to read and play with.

What is SQLite?

SQLite (Structured Query Language) is a relational database management system (RDBMS).
Relational database management systems allow us to store records of information we desire and want to connect. The information we want to store is recorded within large tables.

Storing your data within SQLite gives you a large number of options to choose from to manage your data in the way you want. Database engines process query commands (sometimes very complex) that can take in data from many tables to generate, for example, reports and or data summaries.

SQLite's name comes from the fact that it is simpler to use and easy to read for new developers and or someone who just loves tables.

Using SQLite within the terminal

You can create tables, access the data within, modify data, along with dropping tables right from your terminal. You can even view your desired table as well.

If you want to create an SQLite database file, run:

sqlite3 new_sqlite.db

Now you're within the SQLite prompt. Next, run:

create table new_table(id);

This will create a table called new_table.

To check if your table was created, type:
.tables

To exit simply type:

.quit

SQLite Query Commands ex.

Let us go over some SQL statements first!

SELECT - returns desired data from a database

"SELECT name, age FROM people WHERE gender = 'F' "

INSERT INTO - inserts new data into the table

INSERT INTO people (name, age, gender) VALUES ('sam', 28, 'F');

DROP TABLE - deletes the table

DROP TABLE people;

CREATE TABLE - creates a new table

CREATE TABLE cats ( FurColor string, Loud boolean, ToeBeans integer )

ALTER TABLE - changes table columns, add, drop, or modify

1 . Add column

ALTER TABLE cats
ADD Fluffy boolean;

2 . Drop column

ALTER TABLE cats
DROP COLUMN Fluffy;

3 . Modify column

ALTER TABLE cats
ALTER COLUMN Fluffy string;

These statements are very straightforward and quick to pick up. There are many more statements to use. You can find more here.

SQLite Functions ex.

These functions return a value calculated from columns

AVG()

SELECT AVG(column_name) FROM table_name

COUNT()

SELECT COUNT(column_name) FROM table_name;

FIRST()

SELECT FIRST(column_name) FROM table_name;

There are so many more. You can find them here with many examples.

DB Browser

The DB Browser tool enables you to view your tables, check the data seeded, and update data. There are many more options available as well to engage with your data and tables.

This is also a better visual depiction of your tables than what you will see within your terminal.

Download DB Browser here.