SQLite is a simple database format that is great for beginners to learn how Structured Query Language works
- SQLite3 – How to open a database
- .table – How to view the names of all the tables in a database
- PRAGMA table_info – How to see the column names and data types in a table
- SELECT – How to view the contents of table
sqlite3 – How to open/create a database
SQLite databases are stored in a single file which you can access directly using the sqlite3 command. If the file does not exist(or if it exists but in a different directory!) then a new database file will be created.
In the following the example we open the chinook.db database.
.table – Show all table names
This command returns a list of the names of the tables in the database. Useful if you don’t know the name of a table.
PRAGMA – View details about a table’s structure
If you don’t know what columns are contained within a table, you can use the PRAGMA table_info statement to list all available columns.
Example – view the album table structure.
SELECT * – Select all rows from a table
If you just want to display the entire contents of a database table, the you can use the SELECT * FROM statement. This will get the entire contents of the table.
Example – display all data in the albums table
SELECT column_name FROM table_name – View specific column data
If you just want to select a single column or certain columns, then you can specify the column names you want, separated by a comma.
Example – display all the first names and last names in the employees table.
SELECT WHERE – Select filtered results from a table
If you want to only select rows from the table where data in a column matches a certain value, you need to use the WHERE clause.
< > – Greater Than > and Less Than Filter
If you want to filter data from a table where values are greater or less than an amount, then you can use < and > operators
A Like B – None case-sensitive match
When using the equals = operator, SQL will only search for an exact, case sensitive match. If you want to do a case non sensitive search then use like instead of equals.
% – Wildcard operator
A wildcard operator(%) can be used for more extensive matching. A wildcard matches and character or number of characters.
Here in the example wildcard operator serves to find any artist whose name begins with the word ‘the’.
Find out what tables the database contains.
Find out what column names and types the Album table contains
Find out what artists are in the Artist table.
Create a query that lists the first name and last name of all customers in the Customer table.
Create a query that selects all rows in the Employee table where the Title is ‘Sales Support Agent’
Create a query that lists all rows in the PlaylistTrack table where the TrackId is greater than 3000.