JTBAWD(V): Two quiz on SQL, and I’ve figured out!

2021/4/11/ Sunday: Day 5

( Just for the record: JTBAWD is an abbreviation for my series of “Journal to Be a Web Developer”. I mean it, therefore I’m seriously working on it. )

There are always being a sense of achievement for me once realizing a rightful conclusion on a code or just a simple quiz. Alike this time. Our mentor Long-Ge put forward two quiz briefly after our course, and here there are:

On the Based information from the given field of SQ, named “awesome_ db_v2”, as well read by the use of SQLite (said mentor Long-Ge “An sloppily easy way”, not the best recommendation at all), and here are the quiz below:

1. Find out every monsters being beaten by heroes with level ‘S’.

2. As if possible, find out every monsters are being by which of a specific hero.

(Those quiz are literally interpreted directly from its Chinese sentences)

Here comes my steps on these two adorable quiz:

QUIZ 1

  • Find out every monsters being beaten by heroes with level ‘S’.

step 1: SELECT the elements we need on this quiz, which of ‘heroes.name’, ‘heroes.hero_level’ and ‘monsters.name’. Both ‘.name’ and ‘.hero-level’ following up the previous one could be treated as a function already set in this field; ‘heroes’ and ‘monsters’ are as well a table included these details we need.

step 2: using FROM to recall information from tables ‘heroes’ and ‘monsters’. We have ‘monsters’ itself on line FROM.

Step 3: forward make ‘heroes’ as our INNER JOIN. Why not ‘heroes’ as our LEFT JOIN? If so, overstocking could possibly occur and heroes’ names we need would not run rightfully. And could be even a bigger disaster as if we put ‘heroes’ as RIGHT JOIN — there only exists null, which means nothing above all, on SQLite system. So, DON’T.

(img 1–1 / Photo credit: WHC)

step 4: use Kill_by this method to detect which monsters are being killed by heroes with level of S. Give ‘monsters.kill_by’ on line ON, happening an equal mark ‘=’ to correspond each hero who killed them. ‘.id’ right here is to adjust ‘heroes’ as a group same-scale as ‘monsters.kill_by’, turns out as ‘heroes.id’.

step 5: on line WHERE, we need to accurate which information would be included in ‘heroes.hero_level’, therefore we give it a string, ‘S’. Surely understood, ‘.hero_level’ is indeed a method being set as well.

(img 1–2 / Photo credit: WHC)

As result: 19 rows would be returned, under the conditions of what we’ve given, each monster being beaten by heroes with level of S.

(img 1–3 / Photo credit: WHC)

QUIZ 2

  • As if possible, find out every monsters are being killed by which of a specific hero.

step 1: SELECT all of information with a symbolic point ‘*’.

step 2: On line FROM we have ‘monsters’ and ‘heroes’ as our LEFT JOIN. Seemly FROM and LEFT JOIN would be a pair here to effect an intersection.

step 3: Put two same-scale groups for each other on line ON, as the orders made from quiz 2, ‘monsters.kill_by’ must be corresponding to ‘heroes.id’ in case of avoiding some inconvenient consequences.

(img 2–1 / Photo credit: WHC)

The initial outcome by now we have is each monster of being killed by those who actually killed them — even included figures not mentioned in our LEFT JOIN. For a better result, we do need steps furthermore.

(img 2–2 / Photo credit: WHC)

step 4: On line WHERE, giving a method IS NOT NUL for ‘monsters.kill_by’ could definitely help us get away with NULLs in tables and only appear the figures we need in INNER JOIN.

(img 2–3 / Photo credit: WHC)
(img 2–4 / Photo credit: WHC)

Comparing img 2–4 to previous img 2–2, we could acknowledge numbers in result has been decreased from 59 to 44, without any NULLs.

Not yet, fellas.

As a becoming professional web developer , make my codes and relative information prettier is such a part couldn’t be ignored. I therefore give one more step to make my SQL right here concise:

step 5, ORDER BY ‘heroes.id’, utilizing this way to sequence our heroes’ names.

(img 2–5 / Photo credit: WHC)
(img 2–6 / Photo credit: WHC)

Take a forward look at img 2–6 and img 2–4: two rows in Result remain still but the right-sided sequence is being clarified, make our conclusion a greater appearance.

These two quiz, in my opinion, are frankly not a piece of cake nor troublesome, it kept me reform these involved methods of SQL and more proficiently to operate my orders through SQL in database. Quite a refresh practices to my SQL. What I really concern is, to empower my comprehensions of skills I’ve learned by writing. As Ruby, Ruby on rails, HTML/CSS, SQL; the following courses of JavaScript, REACT, etc. are what I’m being endowed by AstroCamp; by writing my growing process in English is to help me efficiently assimilate these skills! I would keep writing till I’ve earned my initial turning point on my brighter career.

Wish me luck!

Web Developer