UMGC IFSM 330 Assignment Mythical Creatures II
This assumes you have worked through the SQL commands referenced in the weekly tutorial(s) and you have prepared the SQLiteonline.com environment (see the Setting_Up_SQLiteonline_com.docx for instructions).
Your basic study trajectory here will be:
· Work through this document and write SQL to answer all the questions listed below. As part of your SQL, you will capture screenshots and paste them where indicated below.
· Refer to this document as you complete the SQL Check online quiz which verifies your assignment.
· You can take the quiz twice. Your highest score will post to the Grades area. Note the questions may change from one attempt to the next.
· When you have completed the online quiz, submit the Word document.
· You must complete the quiz AND submit a complete Word document with SQL queries and screenshots to receive credit for this assignment.
· Remember, you can always reach out for help to your instructor if your SQL is not behaving for you.
For reference, the commands we may use in this assignment include:
· SQL Select
· SQL Select Distinct
· SQL Where
· SQL And, Or, Not
· SQL ORDER BY
· SQL INSERT INTO
· SQL Null Values (optional)
· SQL UPDATE
· SQL DELETE
· SQL SELECT TOP / LIMIT
· SQL MIN and MAX
· SQL COUNT, AVG, SUM
· SQL LIKE
· SQL WILDCARDS
· SQL IN
· SQL BETWEEN
· SQL ALIASES (Column syntax only)
Name: _________________________________
Single Table Queries
This is where you start to turn things in. Make sure to put your name up top.
Construct SQL to answer each of the following questions. For each query, paste your SQL code below, and also paste a screenshot of the first 10 or so rows the query gives you.
Give an answer to the question based only on what you see in your SQLite database. (Do not use Excel or other tools to answer these questions; since we are trying to learn SQLite here, you should do these exercises in SQLite.)
You will eventually submit this document as part of your week’s assignment.
After you have completed your first draft of this document, you will use the SQL Check online quiz to verify your answers. The SQL Check online quiz uses the same numbering system given here, so you can quickly find the question you need.
You are welcome to update your assignment document with results from the SQL Check, and submit the assignment document after you’ve checked your assignment online.
NOTE: The question numbering below is in sequential order, from smallest to largest, but some numbers are skipped. Be aware of this when you work through the SQL Check online quiz.
· [SQL Week 2 Question 100-007] Get a listing of the id number (idLitCharacters), Name, Creature SubType for all the pirates, mermaids, and fairies. Only include these three Subtypes. Order by ascending idLitCharacters.
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-008] You need a big scary one. Get a listing of all the names of Basilisks which have a weight greater than 475. Make sure you print the ID, the name, the Creature SubType, and the weight. Order by ascending idLitCharacters.
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-009] You want to get some name ideas for somebody who is either a mad scientist or supervillain. What are those names? Print out the Lit Character ID as well as the name and Sub Type. Order by ascending idLitCharacters.
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-010] You need a list of non-male creatures. Select the idLitCharacter, Name, Creature SubType, and Gender for everybody whose gender is not designated as Male. Order by ascending idLitCharacters. Take a screenshot of the top of your output. Then scroll down until you see ID 51, Amara the Mermaid. Take a screenshot of Amara and the rows immediately beneath her as well.
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-011] Select the idLitCharacters, name, Gender, Creature SubType. Your newest author wants a specific list of names which meet one of two criteria. The first criteria to meet would be a female pirate. The second criteria to meet would be any cute tiny male creature of weight 10 or below. Your output should contain some female pirates and some cute tiny male creatures. Sort the results first alphabetically on Gender and then within that field by idLitCharacters ascending.
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-012] Display the idLitCharacters, name, Gender, Creature SubType, weight, and height for everybody whose height is greater than or equal to 200. Sort it so the heaviest ones are on the top, and within the weight then ascending by idLitCharacters.
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-013] You want the big ones. Make a list of all the ID, the name, the Creature SubType, and weight and height. You only want those whose weight is greater than or equal to 150 or those whose height is greater than or equal to 65. Sort your results alphabetically by Name and then ascending by idLitCharacter.
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-018] Your newest novel has a scene in which all the miscreants are on a bridge, but collectively they are too heavy for it so the bridge fails. It’s very exciting for your audience. What is the sum of the weight of all the miscreants here? We define miscreants here as Pirates, Supervillains, Mad Scientists, and Cowboys of any gender.
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-019] You are going to have a scene in which all the basilisks are on one side of a balance scale, and the single smallest dinosaur is on the other side of the scale. The dinosaur is bigger, of course. By how many pounds? (It’s OK if you need to run two separate queries and then manually subtract them.)
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-020] Use the LIKE command to select the idLitCharacters, name, and creature subtype of all the creatures which start with MER (i.e. mermen, mermaids, etc.). Sort it alphabetically by name.
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-021] What is the average weight of all creatures for which the name contains the string ‘us’ ? The phrase can be anywhere in the name – so “Usman” would count, as would “Beusaleth” and “McManus.” (Your database may vary; these names may or may not be in your version of the database.) Note your answer to four decimal places.
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-022] Select all the fields for all the creatures that contain the word “the” in their names, such as “Roderick the Brave.” Do not include entries for which “The” is the first word, such as “The Fire Breather.” Order alphabetically by Name, then ascending by idLitCharacters.
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-023] List all creatures which have two consecutive L’s anywhere in their name (such as “Elling” or “Llewers” or “Dingell”) and which are not Knights. Sort by number of appearances (fewest appearances on top) and then ascending by idLitCharacters.
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-024] Use the SQL IN command to select all fields from all creatures which are Knights, Pirates, Fairies, or Jackalopes and who have had 1 or more appearances to date. Sort it by idLitCharacters, this time in DESCENDING order, so the highest ID is on the top. Use the SQL LIMIT command to display only 10 results.
· How many of each type were in your top 10 results?
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-025] You want something small and cute. How many creatures have a height between 10 and 15 inches and a weight between 5 and 10 pounds? (Hint: you should use the BETWEEN command)
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-026] Your publisher has decided to reclassify all Knights from Real to Magical with regard to their Realm. Make this update in your database. Before you do this update, you should be able to verify you have 100 creatures with Realm of Magical. After this update, how many creatures have a Magical Realm?
· If you get an error code 1175 that grumbles that you are using safe update mode, run the following code before your update:
· SET SQL_SAFE_UPDATES = 0;
· Note – this command has you modify the database. If you are then going to go back and answer some other questions after you do this command, be sure you refresh your database (i.e. re-load and re-run the script which made the database in the first place) before answering those questions.
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-027] DELETE. It’s time to delete stuff.
· Note – this command has you modify the database. If you are going to go back and answer some other questions after you do this command, be sure you refresh your database (i.e. re-load and re-run the script which made the database in the first place) before answering those questions.
· First, let’s verify your database is fully refreshed. Run the following checksum code. (There’s a lot of code here; it’s best to copy/paste into SQL.)
· SELECT ((COUNT(*) * AVG(Weight)) % 99) as checksum from litcharacters;
· You should get a checksum answer of 47.
· Delete all the dinosaurs who have already made an appearance. In other words, delete anything with a Creature SubType of Dinosaur and more than 0 appearances.
· Run the checksum code again. What is your checksum now?
<Paste/Enter SQL Code here>
<Paste Screenshot here>
· [SQL Week 2 Question 100-028] Note – this command has you modify the database. If you are going to go back and answer some other questions after you do this command, be sure you refresh your database (i.e. re-load and re-run the script which made the database in the first place) before answering those questions.
· First, check your database is fully refreshed. Run the following checksum code. (There’s a lot of code here; it’s best to copy/paste into SQL.)
· SELECT ((COUNT(*) * AVG(Weight)) % 99) as checksum from litcharacters;
· You should get a checksum answer of 47.
· You have some more characters to include. Insert the below characters in to the database:
· (‘Mogwai’, ‘Magical’, ‘Other’, ‘Fairy’, ‘Female’, ’25’, ‘158’,’2′),
· (‘Menehune’, ‘Real’, ‘Human’, ‘Superhero’, ‘Male’, ’56’, ‘195’,’5′)
· Now what is your checksum?
<Paste/Enter SQL Code here>
<Paste Screenshot here>
| Assignment Mythical Creatures I for students.2215 | Page 1 |
