Skip to content

Opening and Viewing Databases

Introduction

Introduction

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

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.

# Open in Linux terminal
sqlite3 chinook.db 

# Open in Windows PowerShell
\.sqlite3.exe chinook.db

.table

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

.table

 

Output:

sqlite> .table
albums          employees       invoices        playlists
artists         genres          media_types     tracks
customers       invoice_items   playlist_track

Pragma

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.

PRAGMA table_info(albums);

Output:

0|AlbumId|INTEGER|1||1
1|Title|NVARCHAR(160)|1||0
2|ArtistId|INTEGER|1||0

 

SELECT *

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 * FROM albums;

 

Output:

344|Schubert: The Late String Quartets & String Quintet (3 CD's)|272
345|Monteverdi: L'Orfeo|273
346|Mozart: Chamber Music|274
347|Koyaanisqatsi (Soundtrack from the Motion Picture)|275

 

SELECT Columns

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 FirstName, Email FROM employees;

 

Output:

SELECT WHERE

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.

SELECT FirstName,LastName,Country FROM customers where firstname = "Mark";

Output:

Mark|Philips|Canada
Mark|Taylor|Australia

 

< > – 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

select * from invoice_items where InvoiceId > 400 and UnitPrice > 1;

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.

SELECT Name from Artists WHERE Name LIKE "julian";

% – 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’.

SELECT Name from Artists WHERE Name LIKE "the %";


The Black Crowes
The Clash
The Cult
The Doors
The Police
The Rolling Stones
The Tea Party

Challenges

Challenge 1
Find out what tables the database contains.

Challenge 2

Find out what column names and types the Album table contains

Challenge 3

Find out what artists are in the Artist table.

Challenge 4

Create a query that lists the first name and last name of all customers in the Customer table.

Challenge 5

Create a query that selects all rows in the Employee table where the Title is ‘Sales Support Agent’

Challenge 6

Create a query that lists all rows in the PlaylistTrack table where the TrackId is greater than 3000.