Goals

• I know how to create a database in SQLite
• I can create, alter and drop tables as well as insert, update and delete records
• I understand what column constraints are and where to use them

Create Database

Open the Git Bash in your SQLite folder on the Desktop

From (Git-)Bash:

$ sqlite3 test.db

Directly from SQLite Command Line:

sqlite> .open test.db

In each case, SQLite3 attempts to open a database called "test.db", which does not exists. Therefore, it is created.

Students Database

Download here & import the database

sqlite> .open [database-file].sqlite

sqlite> .read powercoders_export.sql

Insert a new record (without id, since our database uses AUTOINCREMENT):

INSERT INTO students (name, country) 

VALUES ('Matthias Hüni', 'Switzerland');

Insert many records at once

INSERT INTO students (name, country) 

VALUES ('Mohammed Khairi', 'Iraq'),

('Bashar Said', 'Syria'),

('Ranny Kaddoura', 'Syria'),

('Mohamad Husam Ebish', 'Syria');

Update the gender of our female students (since 'male' was the default)

UPDATE students 

SET gender='female' 

WHERE name in ('Fortuna Mebrathu', 'Sangeerththani Ramesh', 'Tsigereda Nebai Kidane', 'Marwa Ahmad');

Remove students from Switzerland (since we're a school for refugees)

DELETE FROM students 

WHERE country='Switzerland';

Count students grouped by country & gender and order by gender & count:

SELECT country, gender, COUNT(id) 

FROM students 

GROUP BY country, gender 

ORDER BY gender, COUNT(id) DESC;