Why SQLite is simple to use!
Tags: sql, activerecord
Published: 2020-08-13 04:24:20

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](https://mystery.knightlab.com/)**. 

## 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. 


![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/dd6fwk650pg5f3f48aea.jpg)

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](https://www.codecademy.com/articles/sql-commands)**.

#### 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](http://www-db.deis.unibo.it/courses/TW/DOCS/w3schools/sql/sql_functions.asp.html)** 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](https://sqlitebrowser.org/dl/)**.

#### End
SQLite has a multitude of uses. You'll find it within Active Record, Ruby on Rails, and amongst other use cases. It is a wonderful tool to learn. 

Thanks for reading!