SQLite3 Tutorial 4

SQLite3 TutorialWelcome to part 4 of my SQLite3 Tutorial! I cover an enormous amount of information on SQLite3 here.

I cover all of the following: Review of most everything from previous SQlite3 tutorials, SQLite3 ENUMs, Composite Primary Keys, dates, data types, data affinities, joins, inner join, left join, natural join, cross join, ON, GROUP BY, select statements inside of select statements, views, triggers and more. If you missed the previous parts look here SQLite3 Tutorial, Part 2 & Part 3.

If you like videos like this, it helps to tell Google+ with a click here

Code From the Video

-- CREATE DATABASE

sqlite3 studentdb.db

-- There are no ENUMs in SQLite, so we need a separate table for sex

CREATE TABLE sex_type(
sex_id TEXT PRIMARY KEY NOT NULL,
sex_type INTEGER);

-- Fill the sex_type table

INSERT INTO sex_type(sex_id, sex_type) VALUES ('M',1);
INSERT INTO sex_type(sex_id, sex_type) VALUES ('F',2);

--------

CREATE TABLE student(
name VARCHAR(23) NOT NULL, -- VARCHAR is treated as TEXT and 23 is ignored
sex CHARACTER(1) NOT NULL, -- CHARACTER and the length restriction is ignored
id_number INTEGER PRIMARY KEY AUTOINCREMENT, 
foreign key(sex) references sex_type(sex_id));

--------

-- Creating an ENUM that represents the type of test (Quiz or Test)

CREATE TABLE test_type(
test_id TEXT PRIMARY KEY NOT NULL,
test_type INTEGER);

-- Fill the test_type table

INSERT INTO test_type(test_id, test_type) VALUES ('Q',1);
INSERT INTO test_type(test_id, test_type) VALUES ('T',2);

--------

CREATE TABLE test(
  date     DATE NOT NULL, -- DATE is seen as a NUMERIC type
  type_test TEXT NOT NULL,
  test_id INTEGER PRIMARY KEY AUTOINCREMENT,
  FOREIGN KEY (type_test) REFERENCES test_type (test_id));


CREATE TABLE test_score(
student_id INTEGER NOT NULL,
test_id INTEGER NOT NULL,
score INTEGER NOT NULL,
FOREIGN KEY (test_id) REFERENCES test (test_id),
FOREIGN KEY (student_id) REFERENCES student (id_number),
PRIMARY KEY (test_id, student_id)); -- A Composite Primary Key

--------

CREATE TABLE absence(
  student_id INTEGER NOT NULL,
  date       DATE NOT NULL,
  PRIMARY KEY (student_id, date),
  FOREIGN KEY (student_id) REFERENCES student (id_number));
  
-------

INSERT VALUES

INSERT INTO student (name, sex) VALUES ('Sally','F');
INSERT INTO student (name, sex) VALUES ('Mark','M');
INSERT INTO student (name, sex) VALUES ('Paul','M');
INSERT INTO student (name, sex) VALUES ('Peter','M');
INSERT INTO student (name, sex) VALUES ('Michael','M');
INSERT INTO student (name, sex) VALUES ('Thomas','M');
INSERT INTO student (name, sex) VALUES ('Rebecca','F');
INSERT INTO student (name, sex) VALUES ('Willow','F');
INSERT INTO student (name, sex) VALUES ('Sarah','F');
INSERT INTO student (name, sex) VALUES ('Emily','F');


-- date, type_test, test_id

INSERT INTO test VALUES (date('now'),'Q',1); -- New Test: NULL for Autoincrement

-- student_id, test_id, score

INSERT INTO test_score VALUES (1,1,24);
INSERT INTO test_score VALUES (2,1,22);
INSERT INTO test_score VALUES (3,1,-1);
INSERT INTO test_score VALUES (4,1,19);
INSERT INTO test_score VALUES (5,1,25);
INSERT INTO test_score VALUES (6,1,22);
INSERT INTO test_score VALUES (7,1,18);
INSERT INTO test_score VALUES (8,1,16);
INSERT INTO test_score VALUES (9,1,20);
INSERT INTO test_score VALUES (10,1,19);

-- student_id, date

INSERT INTO absence VALUES (3,date('now')); -- One student was absent

------- NEW TEST

-- date, type_test, test_id

INSERT INTO test VALUES ('2013-06-10','T',2); -- Put quotes around date

-- student_id, test_id, score

INSERT INTO test_score VALUES (1,2,48);
INSERT INTO test_score VALUES (2,2,44);
INSERT INTO test_score VALUES (3,2,42);
INSERT INTO test_score VALUES (4,2,-1);
INSERT INTO test_score VALUES (5,2,40);
INSERT INTO test_score VALUES (6,2,45);
INSERT INTO test_score VALUES (7,2,50);
INSERT INTO test_score VALUES (8,2,39);
INSERT INTO test_score VALUES (9,2,-1);
INSERT INTO test_score VALUES (10,2,-1);

-- student_id, date

INSERT INTO absence VALUES (4,'2013-06-10'); 
INSERT INTO absence VALUES (9,'2013-06-10'); 
INSERT INTO absence VALUES (10,'2013-06-10'); 

-- SELECT QUERIES

-- Show test results for all students for the quiz given on 2013-06-08
-- We need to pull this information from 2 tables this time

SELECT student_id, score, type_test, date
FROM test, test_score
WHERE date = '2013-06-08'
AND test.test_id = test_score.test_id;

-- Print out the students name with the scores
-- You have to match the student ids for tables test_score and student
-- That way they will only show the test score that corresponds with each
-- individual student

SELECT name, score, type_test, date
FROM test, test_score, student
WHERE date = '2013-06-08'
AND test.test_id = test_score.test_id
AND test_score.student_id = student.id_number;

-- List all students along with their number of absences
-- Since we are using an aggregate query here to group data we have to define 
-- how we want the information to be grouped when it is displayed on the screen. 
-- That is why we define id_number as the way to group information. It is saying 
-- that we should calculate the number of absences for each id_number.

SELECT name AS NAME, 
COUNT(absence.date) AS ABSENCES
FROM student, absence
WHERE absence.student_id = student.id_number
GROUP BY id_number;

-- SQLite JOINS

-- Above we defined INNER JOINs by separating tables with a comma. You can also
-- define them with the word INNER JOIN

-- An INNER JOIN is the most common join. An INNER JOIN returns only those 
-- records from tables that match. The JOIN CONDITION defines the results.

SELECT name, score, test_id
FROM test_score JOIN student
ON student_id = id_number;



-- To show all students with the number of ansences even if they have none we
-- have to use a LEFT JOIN. 

-- The LEFT JOIN says that we need a row for each piece of data listed on the 
-- left of the join. Don't forget to change WHERE into ON

SELECT name AS NAME, 
COUNT(absence.date) AS ABSENCES
FROM student LEFT JOIN absence
ON absence.student_id = student.id_number
GROUP BY id_number;



-- A NATURAL INNER JOIN is similar to a LEFT JOIN in that it returns all columns
-- that match in both tables.

SELECT score, test_id 
FROM student NATURAL JOIN test_score
WHERE student_id = id_number;



-- A CROSS INNER JOIN (Cartesian Join) combines all the records from 2 tables. 
-- This can sometimes make a mess and should normally be avoided

SELECT score, test_id  
FROM student CROSS JOIN test_score;



-- Applying Functions in SQLite

-- Find the Best and Worst Scores on all quizes and tests

-- test_score : student_id, test_id, score
-- test : date, type_test, test_id
-- student : name, sex, id_number

SELECT test.date AS DATE,
MIN(test_score.score) AS WORST,
MAX(test_score.score) AS BEST
FROM test_score, test
WHERE test_score.test_id = test.test_id
GROUP BY test.date;

-- Print the average score on each test

SELECT test.date AS DATE,
AVG(test_score.score) 'AVG SCORE'
FROM test_score, test
WHERE test_score.test_id = test.test_id
GROUP BY test.date;

-- List all students that had a test score over 40

SELECT name, test_score.score 
FROM test_score, student
WHERE test_score.score > 40 AND test_score.student_id = student.id_number
GROUP BY name;

SELECT COUNT(name) AS 'SCORED OVER 40'
FROM student
WHERE student.name in
(SELECT name 
FROM test_score, student
WHERE test_score.score > 40 AND test_score.student_id = student.id_number
GROUP BY name);

-- VIEWS IN SQLite --

-- A view is used to store a queries result. It is not part of the schema

CREATE VIEW ScoreOver40 AS 
SELECT name, test_score.score
FROM test_score, student
WHERE test_score.score > 40 
AND test_score.student_id = student.id_number
GROUP BY name;

drop view ScoreOver40; -- Delete the view

-- TRIGGERS in SQLite --

-- Triggers are operations that are automatically performed when a specific 
-- event occurs

-- test : date, type_test, test_id
-- test_score : student_id, test_id, score
-- student : name, sex, id_number

-- Will Hold Data When a Student Has a Makeup Test

CREATE TABLE Log(
id INTEGER PRIMARY KEY,
test_id INTEGER NOT NULL, 
date     DATE NOT NULL,
student_id INTEGER NOT NULL,
FOREIGN KEY (test_id) REFERENCES test_score (test_id),
FOREIGN KEY (student_id) REFERENCES test_score (student_id));

-- The Trigger that updates the Log when test_score is updated

CREATE TRIGGER test_score_update
AFTER UPDATE OF score ON test_score
BEGIN
INSERT INTO Log(test_id, date, student_id)
VALUES(new.test_id, date('now'), new.student_id); 
-- Don't reference table instead use new
END;

select * from absence; -- Show all absences

UPDATE test_score 
SET score=20
WHERE test_id=2 AND student_id=9;

20 Responses to “SQLite3 Tutorial 4”

  1. craig says:

    Great tutorial, thanks.

  2. Hello, thanks a lot for all of your tutos !
    I have one question about this one and an other more general (by the way I’m french so if there is some sentences that aren’t int good English it is normal :) )
    My first question is : do you know how to use the auto completion in sqlite3 ? Because while I watched your video I see you typing really fast and I might know I to do the same :)

    And secondly how did you learn all of this, because the is a huge collection of videos on your site. Do your learn all on the way of making your videos ? Or do you have some references to learn more ?

    Thanks one more time !

    • Derek Banas says:

      You’re very welcome :) There is no auto complete with SQLite. It probably seems like there is only because of how I edit the video. Sorry about the confusion.

      I have always enjoyed learning. That is pretty much all I have ever done. Every job I ever had required me to learn constantly. I don’t have any other hobbies.

      I got pretty good at making videos and teaching through practice and from taking the advice I received. I was terrible when I first started.

      There isn’t anything particularly special about me. I’m just used to this stuff and I really enjoy teaching.

      Thank you for stopping by my little site :)
      Derek

  3. david says:

    Thank you so much. I love your videos. This series in sql was particularly inspiring to me. At first I was a bit suspicious about leaving all the java and compiler stuff and going into the “wierd world of command line” but I thought “you know what, I really trust this guy to be leading me in the best direction and compelling me to learning what actually matters” I dove deeply into this series sqlite and I am so glad I did :)

  4. david says:

    I have a question that I hope you may answer.
    supose I have a table

    CREATE TABLE desabafo (key int primary key, date datetime default current_timestamp, descricao text);

    where descricao might be a rather long text and I would like to output it in .mode column and .headers on but I need to format it to say 15 characters per line in order to get a nice output. how would I accomplish this?(hope I explained myself clearly)

  5. Jeff says:

    Just watched all 4 tutorials and followed along the source code. What a powerful tool for being so small. I’m following along with both your Java and Android lessons and I’m at the point where they both pause for SQL. Is there anything in particular I would miss if I substituted these android SQL tutorials for the Java ones?

  6. EYla says:

    Grate stuff :)

  7. Phyo says:

    Thank you, Derek. Just want to let you know I watched all 4 tutorials on SQLite :-)

  8. Naresh Guggilla says:

    Hey Derek,

    Very nice tutorials on SQLite. Thanks for your time on this. I’m watching your android tutorials and it is a great learning experience. :)

  9. Reed says:

    Really great tutorials on sqlite, moving at break-neck speeds although that may be because only been learning 7 days now. And once out found out what the ‘BLEEP’ a foreign key did #4 is sinking in… SLOWLY. Thanks.

  10. Glenn says:

    I found the answer…
    sqlite> SELECT test.date AS DATE,
    …> MIN(test_score.score) AS WORST,
    …> MAX(test_score.score) AS BEST
    …> FROM test_score, test
    …> WHERE test_score.test_id = test.test_id
    …> and test_score.score >=0
    …> group by test.date;

    This also works:
    …> and test_score.score not like ‘%-%’

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Google+