Project 1

1. Click on the following link to open the Online SQL Editor.

2. Click on File>Open DB and select the file “emptyDb.db” in ClassesICT

3. Create the table celebs just like in the example, with three columns:

  • id that stores INTEGER;
  • name that stores TEXT;
  • age that stores INTEGER.
Help
SQL
CREATE TABLE celebs (
id INTEGER, 
name TEXT, 
age INTEGER
);

You need to erase the previous query before writing a new one

Once you are happy with your query, you should copy/paste it in the Word document Project 1 and erase it from the Online Editor.

If you just add some queries and run your code, it will execute the previous query again, which can be a problem when you want to only do something once (like creating a table or adding a record).

You can find the history of all your queries on sqliteonline by clicking on this bottom right button:

sqlite

And then selecting History:

history

4. Add 4 celebrities to the celebs table:

  • Justin Bieber who is 23 and has the id of 1.
  • Beyonce Knowles who is 34 and has the id of 2.
  • Jeremy Lin who is 27 and has the id of 3.
  • Taylor Swift who is 27 and has the id of 4.

Important

Each time you change something to your table, use the command:

SELECT * FROM celebs;

to check the result.

Help
SQL
INSERT INTO celebs (id, name, age) 
VALUES (1, 'Justin Bieber', 23);

5. Use a SELECT to retrieve all the names from the celebs table.

Help
SQL
SELECT name FROM celebs;

6. Add a new column named twitter_handle to the table.

Help
SQL
ALTER TABLE celebs 
ADD COLUMN twitter_handle TEXT;

7. Update the table to include Taylor Swift’s twitter handle: @taylorswift13.

Help
SQL
UPDATE celebs 
SET twitter_handle = '@taylorswift13' 
WHERE id = 4;

8. Delete all of the rows that have a NULL value in the twitter handle column.

Help
SQL
DELETE FROM celebs 
WHERE twitter_handle IS NULL;

9. Create a new table awards with constraints on the values:

  • id should accept INTEGER and is a PRIMARY KEY;
  • recipient should accept TEXT and should be NOT NULL;
  • award_name should accept TEXT and should have a default value equal to Grammy.
Help
SQL
CREATE TABLE awards (
id INTEGER PRIMARY KEY,
recipient TEXT NOT NULL,
award_name TEXT DEFAULT 'Grammy'
);