SQL , usage of case

SQL , usage of case
0

#1

Here is the requirement:

The goal is a table that shows how many students have earned which letter_grade. You can output the letter_grade by using CASE with the number_grade column, outputting ‘A’ for grades > 90, ‘B’ for grades > 80, ‘C’ for grades > 70, and ‘F’ otherwise. Then you can use COUNT with GROUP BY to show the number of students with each of those grades.


CREATE TABLE student_grades (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    number_grade INTEGER,
    fraction_completed REAL);
    
INSERT INTO student_grades (name, number_grade, fraction_completed)
    VALUES ("Winston", 90, 0.805);
INSERT INTO student_grades (name, number_grade, fraction_completed)
    VALUES ("Winnefer", 95, 0.901);
INSERT INTO student_grades (name, number_grade, fraction_completed)
    VALUES ("Winsteen", 85, 0.906);
INSERT INTO student_grades (name, number_grade, fraction_completed)
    VALUES ("Wincifer", 66, 0.7054);
INSERT INTO student_grades (name, number_grade, fraction_completed)
    VALUES ("Winster", 76, 0.5013);
INSERT INTO student_grades (name, number_grade, fraction_completed)
    VALUES ("Winstonia", 82, 0.9045);
    
SELECT name,number_grade,round(fraction_completed*100) AS percent_completed FROM student_grades;


/*this part*/
SELECT COUNT(*),
    CASE 
        WHEN number_grade> 90
            THEN "A"
        WHEN number_grade> 80
            THEN "B"
        WHEN number_grade> 70
            THEN "C"
        ELSE
            "F"
    END as "letter_grade"
FROM student_grades;
    

What i get is this: 6 students all have the grade B.,…
What went wrong?


#2

Don’t forget to use the GROUP BY.


#3

I like SQL challenges.


#4

haha, yeah… i can see that.

may I ask you that to what point I know I am employable when it comes to learning SQL?


#5

Good question. Many sql jobs require you to take some kind of quiz/test before talking with a hiring manager. There are various websites that offer sql quizzes (some free and some cost a little money). I would keep working through the tutorial you are doing now on Kahn Academy. It is fairly comprehensive. I would also learn how to set up a database on your local system and be able to build your own databases with various tables, relationships, queries to validate your understanding of what you have learned.

Below is an actual quiz I was given on an SQL Data Analyst job interview a couple years ago. I ended up not getting the job due to the pay being too low, but I think it is a good representation of what might be asked for an entry level sql developer.

Background: I had a general phone interview and then they brought me into their office for an in-person interview. When I arrived, they took me into a room and gave me one piece of paper (seen below) and another one with the following information and 4 questions. They gave me a laptop with the same 4 questions on it and then told me to type out the correct SQL code below each question. I had 45 minutes to answer all the questions. When I thought I had them all correct, I was to tell them “I am finished”.

NOTE: I was not allowed to have access to any internet resources like Stack Overflow, because they expected me to understand SQL enough to “know” what syntax to use to answer the questions.

sql_interview

(note: this is just a sample of some records within each table. assume each table has thousands of records)

  1. Get the names of all salespeople and the total sales revenue they have made. For users who did not make any sales, please display this as 0.

  2. Get the names of all salespeople with total order amounts above $700

  3. Get the names of all the salespeople who have made a sale to someone within industry B

  4. Which date did each salesperson make their highest sale and what was the amount? Result should be salesperson, date, amount.

My personal business is built around being able to create complex SQL queries, so I am fairly knowledgeable using various relational DB engines (mySQL, sqlLite, MS SQL, PostgreSQL, etc…). Since joining Free Code Camp, I have expanded my learning to include Mongo and a couple other non-relational databases.


#6

a friend of mine told me I also need to know the diff between mySQL, SQL lite, MS SQL etc etc, and when to use them or not to use them
is that correct?


#7

They all have very similar syntax, but SQL lite should never be used in a production database. mySQL and MS SQL can be used in production, but MS SQL can probably scale better than mySQL.


#8

ahhhh thank you so much


#9
CREATE TABLE persons (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    age INTEGER);
    
INSERT INTO persons (name, age) VALUES ("Bobby McBobbyFace", 12);
INSERT INTO persons (name, age) VALUES ("Lucy BoBucie", 25);
INSERT INTO persons (name, age) VALUES ("Banana FoFanna", 14);
INSERT INTO persons (name, age) VALUES ("Shish Kabob", 20);
INSERT INTO persons (name, age) VALUES ("Fluffy Sparkles", 8);
INSERT INTO persons (name, age) VALUES ("MrChow", 18);
CREATE table hobbies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    person_id INTEGER,
    name TEXT);
    
INSERT INTO hobbies (person_id, name) VALUES (1, "drawing");
INSERT INTO hobbies (person_id, name) VALUES (1, "coding");
INSERT INTO hobbies (person_id, name) VALUES (2, "dancing");
INSERT INTO hobbies (person_id, name) VALUES (2, "coding");
INSERT INTO hobbies (person_id, name) VALUES (3, "skating");
INSERT INTO hobbies (person_id, name) VALUES (3, "rowing");
INSERT INTO hobbies (person_id, name) VALUES (3, "drawing");
INSERT INTO hobbies (person_id, name) VALUES (4, "coding");
INSERT INTO hobbies (person_id, name) VALUES (4, "dilly-dallying");
INSERT INTO hobbies (person_id, name) VALUES (4, "meowing");
INSERT INTO hobbies (person_id, name) VALUES (6, "skateboarding");
SELECT persons.name,hobbies.name FROM persons
    JOIN hobbies
    ON persons.id=hobbies.person_id;
    
SELECT persons.name,hobbies.name FROM persons
    JOIN hobbies
    ON persons.id=hobbies.person_id;
    WHERE persons.name = "Bobby McBobbyFace";

I want to use the last statement to show only the name and hobbies of ‘Bobby McBobbyFace’,
why is it not working??


#10

The answer is:

select persons.name, hobbies.name from persons
  join hobbies
  on persons.id = hobbies.person_id
  where persons.name = 'Bobby McBobbyFace';

#11

I think your answer is the same as mine.


#12

The last part was the same, but you had this above it which was causing a syntax error.


#13
/*question one: Get the names of all salespeople and the total sales revenue they have made. For users who did not make any sales, please display this as 0.
*/

SELECT Salesperson.name,
Orders.Number * Orders.Amount

FROM Salesperson
JOIN Orders
ON Salesperson.ID = Orders.Salesperson_id 
GROUP BY Salesperson.name;   /*default is 0 did not do*/

/* 2:Get the names of all salespeople with total order amounts above $700*/

SELECT Salesperson.name
FROM Salesperson
JOIN Orders
ON Salesperson.id = Orders.salesperson_id
WHERE min(Orders.Number*Orders.Amount)>700;  /*inside min cannot put math operation??*/


/* 3:Get the names of all the salespeople who have made a sale to someone within industry B*/
SELECT Salesperson.name 
JOIN Orders
ON Salesperson.ID = Orders.salesperson_id
	JOIN Customer
	ON Customer.ID = Salesperson.ID
	WHERE Industry_Type = B
	GROUP BY NAME;


/* 4 Which date did each salesperson make their highest sale and what was the amount? Result should be salesperson, date, amount.*/
SELECT Salesperson.Name,
			 Orders.order_date,
			 max(Orders.Number*Orders.Amount)
FROM Salesperson
JOIN Orders
ON Salesperson.ID = Orders.salesperson_id
GROUP BY Salesperson.Name;

I believe that some of them I got it, some of them I did not.
Where can I improve/correct?


#14

Orders.Number is just an ID of the order and does not represent a quantity to be multiplied by Orders.Amount. Even if it was a quantity, your solution does not work, because you are using GROUP BY but not using an aggregate function like SUM on this multiplied quantity. Also, your simple JOIN will not capture Salesperson names which do not have a corresponding salesperon_id in the Orders table.

To solve this question, you need to first think about the appropriate JOIN you should be using to capture all the Names in the Salesperson table and the matching Amounts regardless if there is a corresponding salesperson_id in the Orders table. Once you get the correct join and use an aggregate function for Orders.Amount to get the total Amount for each Name.

Once you figure out the above, you will find that some names will have a NULL value for the aggregate function result. You will have to research how to convert a NULL value to 0, so any salespersons who did not have an order in the Orders table, will have 0 for the total.

Once you get the above query correct, then I suggest you rework your others solutions which are also not correct.


#15

thanks! I will work on these later !


#16

Below I have ranked the question’s difficulty level in order from least difficult to most difficult.

#2 and #3 are the easiest
#1 is slightly more difficult than #2
#4 is most difficult


#17

here is the answer of 1st question.
I think I got it right this time.

SELECT Salesperson.Name,IFNULL(SUM(Amount),0)
FROM Salesperson
LEFT OUTER JOIN Orders
ON Salesperson.ID=Orders.salesperson_id
GROUP BY Name;

2nd:

SELECT Salesperson.Name
FROM Salesperson
JOIN Orders
ON Salesperson.ID=Orders.salesperson_id
GROUP BY name
HAVING sum(Amount)>700;

#18

Yes, you got both of those right. You don’t actually need the Salesperson. in front of the Name in your first line. Also, since you are using MySQL, you are not required to use the keyword OUTER. You can simply use LEFT JOIN. The OUTER is implied.


#19

I see. Thanks. I think that i need to research on the different syntax for mySQL, SQL server , SQLite etc, just now I was having trouble with ISNULL(expression,replacement value) then I realised that it is IFNULL()


#20

ISNULL is used in MSSQL. NVL is used in Oracle.

Also, in you can use COALESCE in MySQL and MSSQL like:

SELECT Name, COALESCE(SUM(Amount),0)

What is interesting is in POSTGRESQL, any null values are automatically coerced to 0, so you can just write:

SELECT Name, SUM(Amount)