whitepear
Starting Member
24 Posts |
Posted - 2014-04-12 : 08:46:26
|
Hello!Please, why is this one working:(two colums: courses and its prices-> prices)-we get just one colun, containing pricesBEGIN 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');CREATE VIEW MyView AS SELECT SUM(SUM_PRICE) as RESULT_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 RESULT_PRICE DESC;SELECT NC,TOP_SUM_PRICE FROM MyView;...but this one not???(ment as to get 2 columns: one with names of courses and another with their prices)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');CREATE VIEW MyView AS SELECT NAME_COURSE as NC,SUM(SUM_PRICE) as RESULT_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 RESULT_PRICE DESC;SELECT NC,TOP_SUM_PRICE FROM MyView;MANY THANKS!!! |
|