Author |
Topic |
whitepear
Starting Member
24 Posts |
Posted - 2014-04-11 : 08:24:09
|
Hello, All!I have 3 entities:course(#Id_coursename_course(music, dance, film, technick, sport)topic#id_topicname_topic(classsical, modern, tango, ballet, kids, drama, cars, ships, trains, tennis, fotball, swimming)FK_courselecture#id_lecture(20 of them)FK_topicdateprice#id_pricetype (A, B C)cipher(10€, 20€, 30€) // A= 10 €, B020€, c=30€...The course has price devided to lectures. Sum of all lecture's prices is the price of the course.Query is , which course is the most expensive?Please, who can help?MANY THANKS!!! |
|
whitepear
Starting Member
24 Posts |
Posted - 2014-04-11 : 08:27:49
|
p.S. One course has obviously more topics, and one topic has more lectures.One lecture belongs to just one topic. One topic belongs to just one course.One lecture has just one price.More lectures can have the same price(one price can belong to more lectures).Please, help!many thanks!!! |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-11 : 08:39:21
|
This looks like homework. Many readers of this forum could solve this with a few lines of SQL. If we did that, what would you learn from you assignment? |
|
|
whitepear
Starting Member
24 Posts |
Posted - 2014-04-11 : 10:47:46
|
Hello!How about this?WHat is wrong in sql?CREATE TABLE TOPIC(ID_TOPIC,NAME TEXT);CREATE TABLE LECTURE(ID_LECTURE,TK_ID_TOPIC,TK_ID_PRICE);CREATE TABLE PRICE(ID_PRICE,RANG TEXT,SUM_PRICE);INSERT INTO TOPIC VALUES(1,'PIANNO');INSERT INTO TOPIC VALUES(2,'VIOLA');INSERT INTO TOPIC VALUES(3,'BMW');INSERT INTO TOPIC VALUES(4,'AUDI');INSERT INTO TOPIC VALUES(5,'DOGS');INSERT INTO TOPIC VALUES(6,'CATS');;INSERT INTO LECTURE VALUES(1,1,1);INSERT INTO LECTURE VALUES(2,2,2);INSERT INTO LECTURE VALUES(3,3,3);INSERT INTO LECTURE VALUES(4,4,4);INSERT INTO LECTURE VALUES(5,5,1);INSERT INTO LECTURE VALUES(6,6,2);INSERT INTO LECTURE VALUES(7,1,3);INSERT INTO LECTURE VALUES(8,12,4);INSERT INTO LECTURE VALUES(9,3,1);INSERT INTO LECTURE VALUES(10,4,2);INSERT INTO PRICE VALUES(1,'A',10);INSERT INTO PRICE VALUES(1,'B',10);INSERT INTO PRICE VALUES(1,'C',10);INSERT INTO PRICE VALUES(1,'D',10);INSERT INTO PRICE VALUES(1,'E',10);SELECT SUM(SUM_PRICE) FROM TOPIC, LECTURE, PRICE WHERE TOPIC.ID_TOPIC=LECTURE.TK_ID_TOPIC AND PRICE.ID_PRICE=LECTURE.TK_ID_PRICE; |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-11 : 10:52:31
|
Well, for starters, you haven't declared the types of all your columns |
|
|
whitepear
Starting Member
24 Posts |
Posted - 2014-04-11 : 11:14:08
|
It is ok, but I just wonder what is wrong with the select clause!There are more lectures on each topic and I each lecture has its price, and I jsut try to find out how to count which topic has which price!!! |
|
|
whitepear
Starting Member
24 Posts |
Posted - 2014-04-11 : 11:15:17
|
This worked, but what withthe sum?DROP TABLE TOPIC;DROP TABLE LECTURE;DROP TABLE PRICE;CREATE TABLE TOPIC(ID_TOPIC PRIMARY KEY,NAME TEXT);CREATE TABLE LECTURE(ID_LECTURE primary key,TK_ID_TOPIC,TK_ID_PRICE);CREATE TABLE PRICE(ID_PRICE primary key,RANG TEXT,SUM_PRICE);INSERT INTO TOPIC VALUES(1,'PIANNO');INSERT INTO TOPIC VALUES(2,'VIOLA');INSERT INTO TOPIC VALUES(3,'BMW');INSERT INTO TOPIC VALUES(4,'AUDI');INSERT INTO TOPIC VALUES(5,'DOGS');INSERT INTO TOPIC VALUES(6,'CATS');COMMIT;INSERT INTO LECTURE VALUES(1,1,1);INSERT INTO LECTURE VALUES(2,2,2);INSERT INTO LECTURE VALUES(3,3,3);INSERT INTO LECTURE VALUES(4,4,4);INSERT INTO LECTURE VALUES(5,5,1);INSERT INTO LECTURE VALUES(6,6,2);INSERT INTO LECTURE VALUES(7,1,3);INSERT INTO LECTURE VALUES(8,2,4);INSERT INTO LECTURE VALUES(9,3,1);INSERT INTO LECTURE VALUES(10,4,2);COMMIT;INSERT INTO PRICE VALUES(1,'A',10);INSERT INTO PRICE VALUES(2,'B',10);INSERT INTO PRICE VALUES(3,'C',10);INSERT INTO PRICE VALUES(4,'D',10);COMMIT;SELECT * FROM TOPIC, LECTURE, PRICE WHERE TOPIC.ID_TOPIC=LECTURE.TK_ID_TOPIC AND LECTURE.TK_ID_PRICE=PRICE.ID_PRICE; |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-11 : 11:19:24
|
quote: Originally posted by whitepear It is ok, but I just wonder what is wrong with the select clause!There are more lectures on each topic and I each lecture has its price, and I jsut try to find out how to count which topic has which price!!!
Your query only selects SUM(SUM_Price), nothing else. If you want to see the topics as well, you'll need to select the topic name and add some grouping |
|
|
whitepear
Starting Member
24 Posts |
Posted - 2014-04-11 : 13:10:26
|
Hello!This works:BEGIN TRANSACTION;DROP TABLE TOPIC;DROP TABLE LECTURE;DROP TABLE PRICE;DROP TABLE COURSE;CREATE TABLE TOPIC(ID_TOPIC INTEGER PRIMARY KEY,NAME TEXT,TK_ID_COURSE);CREATE TABLE LECTURE(ID_LECTURE INTEGER PRIMARY KEY,TK_ID_TOPIC INTEGER,TK_ID_PRICE INTEGER);CREATE TABLE PRICE(ID_PRICE INTEGER primary key,RANG TEXT TEXT,SUM_PRICE INTEGER);CREATE TABLE COURSE(ID_COURSE INTEGER PRIMARY KEY,NAME_COURSE TEXT);INSERT INTO TOPIC VALUES(1,'PIANNO',1);INSERT INTO TOPIC VALUES(2,'VIOLA',1);INSERT INTO TOPIC VALUES(3,'BMW',2);INSERT INTO TOPIC VALUES(4,'AUDI',2);INSERT INTO TOPIC VALUES(5,'DOGS',3);INSERT INTO TOPIC VALUES(6,'CATS',3);COMMIT;INSERT INTO LECTURE VALUES(1,1,1);INSERT INTO LECTURE VALUES(2,2,2);INSERT INTO LECTURE VALUES(3,3,3);INSERT INTO LECTURE VALUES(4,4,4);INSERT INTO LECTURE VALUES(5,5,1);INSERT INTO LECTURE VALUES(6,6,2);INSERT INTO LECTURE VALUES(7,1,3);INSERT INTO LECTURE VALUES(8,2,4);INSERT INTO LECTURE VALUES(9,3,1);INSERT INTO LECTURE VALUES(10,4,2);COMMIT;INSERT INTO PRICE VALUES(1,'A',10);INSERT INTO PRICE VALUES(2,'B',20);INSERT INTO PRICE VALUES(3,'C',30);INSERT INTO PRICE VALUES(4,'D',40);COMMIT;INSERT INTO COURSE VALUES(1,'MUSIC');INSERT INTO COURSE VALUES(2,'CARS');INSERT INTO COURSE VALUES(3,'ANIMALS');SELECT NAME_COURSE, SUM(SUM_PRICE) FROM TOPIC, LECTURE, PRICE, COURSE WHERE TOPIC.ID_TOPIC=LECTURE.TK_ID_TOPIC AND LECTURE.TK_ID_PRICE=PRICE.ID_PRICE AND COURSE.ID_COURSE=TOPIC.TK_ID_COURSE GROUP BY NAME_COURSE ORDER BY NAME_COURSE DESC;...and I get 2 maximal results:I get:music 100cars 100animals 30now, I want the program to write out (both) maximal values, of course, using function max.Please, do I have to create a view table?I tried, but didnto work...Please, any suggestions???Many thanks!!! |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-11 : 13:21:01
|
try adding a Having clause (look it up!) |
|
|
whitepear
Starting Member
24 Posts |
Posted - 2014-04-11 : 13:35:50
|
Hello, gbritton!!!What should I write in that having clause?Can I use having for max function too?MANY THANKS!!!(SEEMS THE EXAMPLE IS NOT AS EASY FOR THE FIRST BEGINNING AS IT SEEMS!!!) |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-11 : 14:17:00
|
quote: Originally posted by whitepear Hello, gbritton!!!What should I write in that having clause?Can I use having for max function too?MANY THANKS!!!(SEEMS THE EXAMPLE IS NOT AS EASY FOR THE FIRST BEGINNING AS IT SEEMS!!!)
Yes this is tricky. You'll might need a csubquery or two or a CTE expression.You've already got a query that adds up the price per course.Now, think about how you would get the max price per NAME_COURSE. That will take a main query that uses the query you already have as a subquery.Then, figure out how to combine these with a main query calling one or more subqueries to only return course with the highest price. (Hint: WHERE/HAVING can compare to a subquery result). |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-11 : 14:21:22
|
quote: Originally posted by gbritton
quote: Originally posted by gbritton
quote: Originally posted by whitepear Hello, gbritton!!!What should I write in that having clause?Can I use having for max function too?MANY THANKS!!!(SEEMS THE EXAMPLE IS NOT AS EASY FOR THE FIRST BEGINNING AS IT SEEMS!!!)
Yes this is tricky. You might need a subquery or two or a CTE expression.You've already got a query that adds up the price per course.Now, think about how you would get the max price over all courses. That will take a main query that uses the query you already have as a subquery.Then, figure out how to combine these with a main query calling one or more subqueries to only return courses with the highest price. (Hint: WHERE/HAVING can compare to a subquery result). |
|
|
whitepear
Starting Member
24 Posts |
Posted - 2014-04-12 : 03:09:07
|
This one does nto work./Anyway, it should be done at once...using just one execution...)BEGIN TRANSACTION;DROP TABLE TOPIC;DROP TABLE LECTURE;DROP TABLE PRICE;DROP TABLE COURSE;CREATE TABLE TOPIC(ID_TOPIC INTEGER PRIMARY KEY,NAME TEXT,TK_ID_COURSE);CREATE TABLE LECTURE(ID_LECTURE INTEGER PRIMARY KEY,TK_ID_TOPIC INTEGER,TK_ID_PRICE INTEGER);CREATE TABLE PRICE(ID_PRICE INTEGER primary key,RANG TEXT TEXT,SUM_PRICE INTEGER);CREATE TABLE COURSE(ID_COURSE INTEGER PRIMARY KEY,NAME_COURSE TEXT);INSERT INTO TOPIC VALUES(1,'PIANNO',1);INSERT INTO TOPIC VALUES(2,'VIOLA',1);INSERT INTO TOPIC VALUES(3,'BMW',2);INSERT INTO TOPIC VALUES(4,'AUDI',2);INSERT INTO TOPIC VALUES(5,'DOGS',3);INSERT INTO TOPIC VALUES(6,'CATS',3);COMMIT;INSERT INTO LECTURE VALUES(1,1,1);INSERT INTO LECTURE VALUES(2,2,2);INSERT INTO LECTURE VALUES(3,3,3);INSERT INTO LECTURE VALUES(4,4,4);INSERT INTO LECTURE VALUES(5,5,1);INSERT INTO LECTURE VALUES(6,6,2);INSERT INTO LECTURE VALUES(7,1,3);INSERT INTO LECTURE VALUES(8,2,4);INSERT INTO LECTURE VALUES(9,3,1);INSERT INTO LECTURE VALUES(10,4,2);COMMIT;INSERT INTO PRICE VALUES(1,'A',10);INSERT INTO PRICE VALUES(2,'B',20);INSERT INTO PRICE VALUES(3,'C',30);INSERT INTO PRICE VALUES(4,'D',40);COMMIT;INSERT INTO COURSE VALUES(1,'MUSIC');INSERT INTO COURSE VALUES(2,'CARS');INSERT INTO COURSE VALUES(3,'ANIMALS');SELECT COURSE,SUM(SUM_PRICE) FROM TOPIC, LECTURE, PRICE, COURSE WHERE TOPIC.ID_TOPIC=LECTURE.TK_ID_TOPIC AND LECTURE.TK_ID_PRICE=PRICE.ID_PRICE AND COURSE.ID_COURSE=TOPIC.TK_ID_COURSE HAVING SUM(SUM_PRICE) > 30 ORDER BY NAME_COURSE DESC; |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2014-04-14 : 05:36:16
|
What do you mean by "This one does nto work."?MadhivananFailing to plan is Planning to fail |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-04-14 : 07:55:58
|
Here are three different approaches (I'm sure there are many more!)-- Method 1: use HAVING with original query as a subquerySELECT NAME_COURSE, SUM(SUM_PRICE) AS SUM_PRICEFROM TOPIC, LECTURE, PRICE, COURSE WHERE TOPIC.ID_TOPIC=LECTURE.TK_ID_TOPIC AND LECTURE.TK_ID_PRICE=PRICE.ID_PRICE AND COURSE.ID_COURSE=TOPIC.TK_ID_COURSE GROUP BY NAME_COURSEHAVING SUM(SUM_PRICE) = ( SELECT MAX(SUM_PRICE) FROM (SELECT SUM(SUM_PRICE) AS SUM_PRICE FROM TOPIC, LECTURE, PRICE, COURSE WHERE TOPIC.ID_TOPIC=LECTURE.TK_ID_TOPIC AND LECTURE.TK_ID_PRICE=PRICE.ID_PRICE AND COURSE.ID_COURSE=TOPIC.TK_ID_COURSE GROUP BY NAME_COURSE) AS SubQ) -- Method 2: Use a CTE to follow DRY (Don't Repeat Yourself) principle;WITH SUMMED_AND_GROUPED AS ( SELECT NAME_COURSE, SUM(SUM_PRICE) AS SUM_PRICE FROM TOPIC, LECTURE, PRICE, COURSE WHERE TOPIC.ID_TOPIC=LECTURE.TK_ID_TOPIC AND LECTURE.TK_ID_PRICE=PRICE.ID_PRICE AND COURSE.ID_COURSE=TOPIC.TK_ID_COURSE GROUP BY NAME_COURSE)SELECT * FROM SUMMED_AND_GROUPED WHERE SUM_PRICE = ( SELECT MAX(SUM_PRICE) FROM SUMMED_AND_GROUPED )-- Method 3: Use a temporary table instead of a CTEDROP TABLE #SUMMED_AND_GROUPEDSELECT NAME_COURSE, SUM(SUM_PRICE) AS SUM_PRICEINTO #SUMMED_AND_GROUPEDFROM TOPIC, LECTURE, PRICE, COURSE WHERE TOPIC.ID_TOPIC=LECTURE.TK_ID_TOPIC AND LECTURE.TK_ID_PRICE=PRICE.ID_PRICE AND COURSE.ID_COURSE=TOPIC.TK_ID_COURSE GROUP BY NAME_COURSESELECT * FROM #SUMMED_AND_GROUPEDWHERE SUM_PRICE = (SELECT MAX(SUM_PRICE) FROM #SUMMED_AND_GROUPED) |
|
|
|
|
|