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 MIN and MAX
• SQL COUNT
• SQL LIKE
• SQL WILDCARDS
• SQL IN
• SQL BETWEEN
• SQL ALIASES (Column syntax only)
• [SQL Week 1 Question 100-002] List the different creature types are there in the litcharacters database? For example, if you had three humans and one animal, that would be two types – human and animal.
• [SQL Week 1 Question 100-003] Make output which includes only the Name, Creature Type, and Creature SubType for all the Animals. Order by ascending idLitCharacters (you may display the idLitCharacters if you like but it is not required. Whether or not you display the idLitCharacters field, you need to sort by it.)
• [SQL Week 1 Question 100-004] Your editor wants a list of the Name, Creature Type, Creature SubType and Gender for all new creatures – those who have not made an appearance yet (so their Appearance total is 0.) Order by ascending idLitCharacters (you may display the idLitCharacters if you like but it is not required. Whether or not you display the idLitCharacters field, you need to sort by it.)
• [SQL Week 1 Question 100-005]
Now your editor wants a list of the Name, Creature Type, Creature SubType and Gender for all well-worn creatures – those who have made 5 or more appearances. Include those who have made exactly 5 appearances. Order by ascending idLitCharacters.
• [SQL Week 1 Question 100-006] They want to know the mid-popular ones as well. Create a list of the Name, Creature Type, Creature SubType and Gender for all mid-popular creatures - those who have made between 3 and 5 appearances. Include those who have made exactly 3 and exactly 5 appearances. Order by ascending idLitCharacters.
• [SQL Week 1 Question 100-014] You want to know for all humans whose realm is Magical, what is the maximum weight in pounds?
• [SQL Week 1 Question 100-015] What is the lightest weight of any of the creatures who is taller than 60 inches?
• [SQL Week 1 Question 100-016] How many creatures are of type Human?
• [SQL Week 1 Question 100-017] Get the average weight of all human type creatures. Note your answer to four decimal places. You are NOT expected to use SQL to limit the answer to 4 decimal places. Simply ‘note’ the answer to 4 decimal places for the purposes of the quiz.