Mythical Creatures I
Divad
Setting_Up_SQLiteonline_com_v2.docxUMGC IFSM 330 Setting up SQLiteonline.com
In this class, we’ll be using SQLiteonline.com to install databases, run scripts and view output. SQLiteonline runs from a browser; there is no software to install.
Now, we are going to fire up SQLiteonline.com, run a script to install a database with a single table, and run some queries on this database.
Part 1 – Prepare the database and query environment, Open a browser and go to www.sqliteonline.com
1. When you first access SQLiteonline.com, you should see something like the following screenshot:
A: Shows we’re using the SQLite database and there is a database preloaded called ‘demo’.
B: Is the area where we will enter our SQL install commands and SQL database queries
C: Shows output of queries.
D: Is the ‘Run’ button. We click there to run a command or query.
2. We’re going to start by deleting (dropping) the ‘demo’ database. Right click next to demo and choose drop. Then click OK to confirm.
Now we have an empty database. Click in the Query Area, and select/highlight the query SELECT * FROM demo; (use your mouse or CTRL-A on the keyboard) and DELETE that query (DELETE KEY or BACKSPACE).
We now have an empty data base and a blank query environment where we can begin work.
3. NOTE: SQLiteonline.com saves NOTHING for you. You will need to keep a copy of your queries and screenshots of output in Word (Part 4 of this document). Furthermore, It’s always smart to back your work up, so make a note to periodically save a copy somewhere else (i.e. email yourself a copy, or save it to your Google Drive as well.) Backing up only takes a minute and can save hours of rework.
4. The process that we just completed in SQLiteonline.com, removing/dropping the demo database and clearing the SQL Query area is a process you will do EVERY TIME you begin work again in SQLiteonline.com. Part 2, beginning on the next page, describes how to run an SQL INSTALL script to install a database in SQLiteonline.com. This is also a process that you will do EVERY TIME you begin work in SQLiteonline.com.
Part 2 – Download the Script and get your MythicalCreatures Set Up
The names in Mythical Creatures table are thanks to our friends at https://www.fantasynamegenerators.com/. Got a dragon and don’t know what to name it? They will have fantastic suggestions.
1. Please download the MythicalCreatures.sql file from the Assignments Folder in LEO (the same folder you downloaded these instructions from) and save the file to your hard drive.
2. If you double click the file it should open automatically in NotePad (Windows) or a text editor on a Mac.
As we did before, with your cursor/mouse in Notepad, select the entire sql text (CTRL-A) and copy (CTRL-C) and paste (CTRL-V) the text into the Query area of SQLiteonline.com.
Text is selected:
SQLiteonline.com, after pasting (CTRL-V) the text into the SQL Query area. Note the DB (database) is still empty.
Click Run to Run/Execute the Database install. After Run, we now have the database LitCharacters showing at the left.
Be careful not to click Run Twice, otherwise you will have all of the data—twice.
Click the ARROW to the right of LitCharacters, to show the Column Headings:
Note our install command is still in the SQL query area. We should remove that text, so we can run other queries from a blank screen. The database stays at the left, unless we actually DELETE/DROP the database (as we did before). Within the SQL query area, click CTRL-A to select the text and DELETE or BACKSPACE on the keyboard to delete the query.
Part 3 – Verify your MythicalCreatures Database
1. You are a literary agent working for Super Random House, Inc. Your publishing company maintains a database of characters which have appeared or may appear in the future in its novels. The novels run the gamut from fiction to nonfiction, biography to fantasy, and of course there must always be a basilisk or a robot available. Super Random House pseudo-randomly selects characters and assigns them to an author when it commissions a book. For example, it could select one pirate, one knight, and one supervillain, and commission an author to write a short story using those characters. Super Random House keeps track of previous uses of its characters to avoid over- or under-use of any specific one.
2. Your database has one table in it, and the table is called ‘litcharacters.’
3. Here’s a sample of the first few rows of a litcharacters table. Note your first few rows may or may not contain the exact same information, but they should be of the same general idea. The data fields are:
a. idLitCharacters – a unique identifier (the primary key, if you’ve heard of those)
b. Name – a text field with the name of the character
c. Realm – a text field telling us what realm the character belongs to
d. CreatureType – designates creature as Human, Animal, or a few additional types
e. CreatureSubType – gives more specifics about the exact sort of creature
f. Gender - gender
g. Height – height in inches
h. Weight – weight in pounds
i. For example, in the table below, our first literary character is a male Merman (human on top, fish tail on the bottom) named Kaerio, clearly a magical character, but with a human type, and he is 65 inches tall and weighs 181 pounds.
j. Rounding out the scales is entry number 8, our Super Claw Lizard, at over 26,000 pounds.
|
idLitCharacters |
Name |
Realm |
CreatureType |
CreatureSubType |
Gender |
Height |
Weight |
|
1 |
Kaerio |
Magical |
Human |
Merman |
Male |
65 |
181 |
|
2 |
Hissing Hornet |
Real |
Human |
Supervillain |
Male |
61 |
205 |
|
3 |
Rugby Defiant Eldon |
Real |
Human |
Pirate |
Male |
67 |
168 |
|
4 |
Belet the Hero |
Real |
Human |
Knight |
Female |
70 |
270 |
|
5 |
Mistress Shaggy Wraith |
Real |
Human |
Supervillain |
Female |
66 |
175 |
|
6 |
Swift Shadow |
Real |
Human |
Supervillain |
Female |
83 |
179 |
|
7 |
Doctor Heavenly Daggers |
Real |
Human |
Superhero |
Female |
89 |
183 |
|
8 |
Superchelosaurus (Super Claw Lizard) |
Real |
Animal |
Dinosaur |
Female |
202 |
26974 |
|
9 |
The Voiceless Arsonist |
Real |
Human |
Supervillain |
Female |
58 |
184 |
|
10 |
Alectrochelodraco (Eagle Claw Dragon) |
Real |
Animal |
Dinosaur |
Male |
207 |
7409 |
k. Let’s verify your MythicalCreatures database is working correctly.
4. Run the following query to list all the data in the table LitCharacters. A screenshot of the first few rows is below. Forgot how to run a query? Do this:
a. Make sure your SQLiteonline.com SQL query area is empty. We want to work from a ‘blank slate’.
b. Type the SQL into the SQL Query area (so you literally type SELECT * from LitCharacters;)
c. Leave your cursor blinking away after the semicolon
d. Mouse up to the RUN button and press it to execute the code
SELECT * FROM LitCharacters;
2. Remove/DELETE your SELECT * FROM LitCharacters; SQL query and from a blank SQL query area, run the following query and verify that you have 250 records in the table:
SELECT count(*) FROM LitCharacters;
Part 4.1: Using Snipping Tool (Windows) or the Screenshot App (Mac) to Capture Output
All of the screenshots/images in these instructions were captured and copied and pasted into Word using the Snipping Tool in Windows.
Note you should typically NOT take a picture of the entire screen, but select the pertinent output that you want to capture. If the output in submitted screenshots is too small to easily read or if the submitted screenshots do not show enough of the output, the assignment will be returned for resubmission.
Instructions for using the Snipping Tool can be found here:
If you have Windows 10, you may receive a message that the Snipping Tool is being replaced by a new App called ‘Snip & Sketch’.
Snip & Sketch instructions can be found here:
The Snipping Tool equivalent in Mac is the Screenshot App. Instructions for using the Screenshot App can be found here:
https://support.apple.com/en-us/HT201361
Pay particular attention to the second section How to capture a portion of the screen, which will allow you to select specific output to capture.
Part 4.2: Using a Browser to Capture Output
Most major browsers can also take and save screenshots. Since our output is based within a browser this might be a simple option for some.
Firefox: https://support.mozilla.org/en-US/kb/firefox-screenshots
Firefox and Microsoft Edge keyboard shortcut for Screenshots: CTRL-Shift-S
Chrome: in Chrome-based browsers, look for a small camera icon:
Part 4.2: Using Word to type SQL code for SQLiteonline
Quotes are often a part of SQL code, but Word creates quotes for written text not programming or SQL code. Quotes in Word can sometimes be curved, sometimes not.
But SQLiteonline.com can give errors on “curved/angled” quotes. SQLiteonline prefers "straight up and down" quotes the type that are created by Notepad.
However, Notepad within Windows is very basic and not always easy to use. A more advanced, but free, open source alternative to Notepad for MS Windows is Notepad++, available here:
https://notepad-plus-plus.org/
Some students seem to use Microsoft Word for SQL code without a problem, others encounter problems and it’s not even clear the quotes are causing the errors.
Examples:
Arial: “curved/angled”
Arial Unicode MS: “curved/angled”
Courier New: “curved/angled”
Times New Roman: “curved/angled”
From notepad: "straight up and down"
|
|
Setting Up SQLonline.com |
Page 10 |
