SQL , usage of case

zhouxiang19910319 · July 20, 2018, 11:33pm

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?

camperextraordinaire · July 21, 2018, 12:07am

Don’t forget to use the GROUP BY.

zhouxiang19910319 · July 21, 2018, 10:39pm

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?

zhouxiang19910319 · July 21, 2018, 10:59pm

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?

zhouxiang19910319 · July 21, 2018, 11:07pm

ahhhh thank you so much

zhouxiang19910319 · July 21, 2018, 11:11pm

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??

zhouxiang19910319 · July 23, 2018, 12:53am

I think your answer is the same as mine.

zhouxiang19910319 · August 26, 2018, 4:41pm

/*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?

zhouxiang19910319 · August 26, 2018, 8:08pm

thanks! I will work on these later !

zhouxiang19910319 · August 29, 2018, 7:55am

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;

zhouxiang19910319 · August 29, 2018, 8:02am

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()

zhouxiang19910319 · August 29, 2018, 8:12am

3rd question’s answer is here

SELECT Salesperson.Name
FROM Salesperson
JOIN Orders
ON Salesperson.ID=Orders.salesperson_id
JOIN Customer 
ON Orders.cust_id=Customer.ID
GROUP BY Salesperson.Name
HAVING Industry_Type = "B";

zhouxiang19910319 · August 29, 2018, 8:33am

so most of the time (or is it all the time???) GROUP BY is being used only when there is an aggregate function HAVING?? is that correct?

also I changed Industry_Type to “Industry Type”.

zhouxiang19910319 · August 29, 2018, 8:42am

i see… thanks!

I will have to go out now, later will revisit this as well as 4.
I’ve already have the answer for 4, but will check again just in case.
See ya in a bit!

zhouxiang19910319 · August 30, 2018, 7:16am

okay… about the 3rd one, I was using GROUP BY under the impression that it will sort out the row. and also I was using HAVING cos I thought WHERE only works on stuff that belongs to the same row. But now in the form we have 2 B rows.

I tried this in an online editor and it still gives me the correct result, I don’t know whether it is correct.

SELECT Salesperson.Name
FROM Salesperson
JOIN Orders
ON Salesperson.ID=Orders.salesperson_id
JOIN Customer 
ON Orders.cust_id=Customer.ID
WHERE [Industry Type] = "B";

Also This is number 4’s answer.

-- /*4*/
SELECT Name,order_date,Amount
FROM Salesperson
JOIN Orders
ON Salesperson.ID=Orders.salesperson_id
GROUP BY Name
HAVING MAX(amount);

Here I used GROUP BY as well as HAVING and plus that I used the aggregate function, I think this is fine right?

zhouxiang19910319 · August 30, 2018, 4:37pm

also about HAVING and GROUP BY…

I can use GROUP BY without HAVING, but I cannot use HAVING without GROUP BY.
Is that correct?
Because GROUP BY comes before HAVING and HAVING relies on GROUP BY to perform calculation.

Is this understanding correct?

zhouxiang19910319 · August 30, 2018, 9:36pm

another question:

I have learnt that this is how you delete duplicated rows in a table

-- how to delete duplicate rows


CREATE TABLE table_name (
	id integer primary key,
	student_id integer,
	name text,
	subject_id integer,
	subject_name text,
	number_grade integer
);

insert into table_name (student_id,name,subject_id,subject_name,number_grade) values (1,2005001,"zhangsan",0001,"shuxue",69);
insert into table_name (student_id,name,subject_id,subject_name,number_grade) values (2,2005002,"lisi",0002,"shuxue",89);
insert into table_name (student_id,name,subject_id,subject_name,number_grade) values (1,2005001,"zhangsan",0001,"shuxue",69);



delete from table_name
	where id NOT IN (
			select min(id) 
			from table_name
			group by student_id,name,subject_id,subject_name,number_grade
		);

From my understanding, it basically tells the engine to delete every row that does not belong to the minimum ID. so lets say some duplicates are like this:

1, something , something else
2,something, something else
3,other thing, something extra

then the engine compares 1 and 2 then only keep the 1 (because 1 is the minimum among all the rows), delete 2, therefore duplicates just got deleted.

Is my understanding correct?

zhouxiang19910319 · September 16, 2018, 12:19pm

	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;

this is for the 4th question.

zhouxiang19910319 · September 17, 2018, 2:16pm

I will take a look at sub queries, I learned it before but I forgot how to use it.
Meanwhile may I ask you what kind of app are you using to run mySQL?
I tried some online editor. Some says my code has syntax error, some do not.

zhouxiang19910319 · September 17, 2018, 3:28pm

I will try to download it on my PC later. And maybe just use PHPmyAdmin on my mac.