new in sql-please help!!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

new in sql-please help!!!

Author

Topic

whitepear
Starting Member

24 Posts

Posted - 2014-04-11 : 08:24:09

Hello, All!
I have 3 entities:

course
(#Id_course
name_course(music, dance, film, technick, sport)

topic
#id_topic
name_topic(classsical, modern, tango, ballet, kids, drama, cars, ships, trains, tennis, fotball, swimming)
FK_course

lecture
#id_lecture
(20 of them)
FK_topic
date

price
#id_price
type (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 100
cars 100
animals 30

now, 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."?

Madhivanan

Failing 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 subquery

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
HAVING 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 CTE

DROP TABLE #SUMMED_AND_GROUPED
SELECT NAME_COURSE, SUM(SUM_PRICE) AS SUM_PRICE
INTO #SUMMED_AND_GROUPED
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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources