· 9 years ago · Apr 04, 2017, 07:12 PM
1drop table GroupPosition CASCADE CONSTRAINTS;
2drop table Editor CASCADE CONSTRAINTS;
3drop table Category CASCADE CONSTRAINTS;
4drop table Article CASCADE CONSTRAINTS;
5drop table Membership CASCADE CONSTRAINTS;
6drop table grants_access_to CASCADE CONSTRAINTS;
7drop table RegisteredUser CASCADE CONSTRAINTS;
8drop table ArticleComment CASCADE CONSTRAINTS;
9
10CREATE TABLE GroupPosition
11(
12 baseSalary FLOAT NOT NULL,
13 POSITIONNAME VARCHAR2(30) NOT NULL,
14 PRIMARY KEY (POSITIONNAME)
15);
16
17CREATE TABLE Editor
18(
19 email VARCHAR2(30) NOT NULL,
20 publishingName VARCHAR2(30) NOT NULL,
21 RegisteredUsername VARCHAR2(30) NOT NULL,
22 password VARCHAR2(30) NOT NULL,
23 POSITIONNAME VARCHAR2(30) NOT NULL,
24 PRIMARY KEY (email),
25 FOREIGN KEY (POSITIONNAME) REFERENCES GroupPosition(POSITIONNAME)
26);
27
28CREATE TABLE Category
29(
30 categoryName VARCHAR2(30) NOT NULL,
31 PRIMARY KEY (categoryName)
32);
33
34CREATE TABLE Article
35(
36 articleID INT NOT NULL,
37 title VARCHAR2(60) NOT NULL,
38 datePublished DATE NOT NULL,
39 email VARCHAR2(30) NOT NULL,
40 categoryName VARCHAR2(30) NOT NULL,
41 PRIMARY KEY (articleID),
42 FOREIGN KEY (email) REFERENCES Editor(email),
43 FOREIGN KEY (categoryName) REFERENCES Category(categoryName)
44);
45
46CREATE TABLE Membership
47(
48 type VARCHAR2(30) NOT NULL,
49 PRIMARY KEY (type)
50);
51
52CREATE TABLE grants_access_to
53(
54 type VARCHAR2(30) NOT NULL,
55 articleID INT NOT NULL,
56 PRIMARY KEY (type, articleID),
57 FOREIGN KEY (type) REFERENCES Membership(type),
58 FOREIGN KEY (articleID) REFERENCES Article(articleID)
59);
60
61CREATE TABLE RegisteredUser
62(
63 email VARCHAR2(30) NOT NULL,
64 RegisteredUsername VARCHAR2(30) NOT NULL,
65 password VARCHAR2(30) NOT NULL,
66 type VARCHAR2(30),
67 PRIMARY KEY (email),
68 FOREIGN KEY (type) REFERENCES Membership(type)
69);
70
71CREATE TABLE ArticleComment
72(
73 ArticleCommentID INT NOT NULL,
74 numberOfLikes INT NOT NULL,
75 email VARCHAR2(30) NOT NULL,
76 commentText varchar2(60) NOT NULL,
77 PRIMARY KEY (ArticleCommentID),
78 FOREIGN KEY (email) REFERENCES RegisteredUser(email)
79);
80
81drop sequence AutoIncSeq;
82
83CREATE SEQUENCE AutoIncSeq
84MINVALUE 0
85START WITH 0
86INCREMENT BY 1
87CACHE 10;
88
89drop sequence AutoIncSeq2;
90
91CREATE SEQUENCE AutoIncSeq2
92MINVALUE 0
93START WITH 0
94INCREMENT BY 1
95CACHE 10;
96
97INSERT INTO GroupPosition values (200,'part-time worker');
98INSERT INTO GroupPosition values (150,'new part-time worker');
99INSERT INTO GroupPosition values (500,'junior editor');
100INSERT INTO GroupPosition values (700,'senior editor');
101INSERT INTO GroupPosition values (1000,'veduci oddelenia');
102
103INSERT INTO EDITOR values ('KristopherDenzel@yahoo.com','Kristopher Denzel','KristopherDenzel','PASSWORD1','part-time worker');
104INSERT INTO EDITOR values ('BillPrudence@gmail.com','Bill Prudence','BillPrudence', 'PASSWORD2','new part-time worker');
105INSERT INTO EDITOR values ('LinfordMilford@gmail.com','Linford Milford','LinfordMilford', 'PASSWORD3','junior editor');
106INSERT INTO EDITOR values ('AydanTyson@gmail.com','Aydan Tyson','AydanTyson', 'PASSWORD32','junior editor');
107INSERT INTO EDITOR values ('MaryanneWat@yahoo.com','Maryanne Wat','MaryanneWat', 'PASSWORD4','junior editor');
108INSERT INTO EDITOR values ('RaynerBarnabas@gmail.com','Rayner Barnabas','RaynerBarnabas', 'PASSWORD42','senior editor');
109INSERT INTO EDITOR values ('UlricStevie@gmail.com','Ulric Stevie','UlricStevie', 'PASSWORD5','veduci oddelenia');
110
111INSERT INTO CATEGORY VALUES ('travel');
112INSERT INTO CATEGORY VALUES ('local news');
113INSERT INTO CATEGORY VALUES ('world news');
114INSERT INTO CATEGORY VALUES ('fashion');
115INSERT INTO CATEGORY VALUES ('music');
116INSERT INTO CATEGORY VALUES ('politics');
117INSERT INTO CATEGORY VALUES ('stars');
118INSERT INTO CATEGORY VALUES ('tech');
119INSERT INTO CATEGORY VALUES ('science');
120INSERT INTO CATEGORY VALUES ('finance');
121
122INSERT INTO ARTICLE VALUES (AutoIncSeq2.nextval,'Russia used fake news to manipulate election','18-03-2017','KristopherDenzel@yahoo.com','politics');
123INSERT INTO ARTICLE VALUES (AutoIncSeq2.nextval,'Colombian police seize more than six tonnes of cocaine','18-03-2017','KristopherDenzel@yahoo.com','world news');
124INSERT INTO ARTICLE VALUES (AutoIncSeq2.nextval,'Top Asian News 5:23 p.m. GMT','1-03-2017','RaynerBarnabas@gmail.com','world news');
125INSERT INTO ARTICLE VALUES (AutoIncSeq2.nextval,'Mammoth discovery: Six-foot tusk found on Essex beach','21-02-2017','RaynerBarnabas@gmail.com','science');
126INSERT INTO ARTICLE VALUES (AutoIncSeq2.nextval,'NASAs ice-world robots can reach, launch, and melt','15-03-2017','RaynerBarnabas@gmail.com','science');
127INSERT INTO ARTICLE VALUES (AutoIncSeq2.nextval,'Windows 10 Creators Update: Microsofts best just got better','3-01-2017','AydanTyson@gmail.com','tech');
128INSERT INTO ARTICLE VALUES (AutoIncSeq2.nextval,'This season new trends','12-02-2017','AydanTyson@gmail.com','fashion');
129INSERT INTO ARTICLE VALUES (AutoIncSeq2.nextval,'Best places to travel to in the summer','3-01-2017','AydanTyson@gmail.com','travel');
130INSERT INTO ARTICLE VALUES (AutoIncSeq2.nextval,'How to choose right savings account','3-01-2017','BillPrudence@gmail.com','finance');
131INSERT INTO ARTICLE VALUES (AutoIncSeq2.nextval,'What to wear this spring','30-01-2017','MaryanneWat@yahoo.com','fashion');
132INSERT INTO ARTICLE VALUES (AutoIncSeq2.nextval,'Hollywood stars that rocked this red carpet','14-02-2017','MaryanneWat@yahoo.com','stars');
133INSERT INTO ARTICLE VALUES (AutoIncSeq2.nextval,'Plastic makes up nearly 70% of all ocean litter','06-03-2017','MaryanneWat@yahoo.com','science');
134
135INSERT INTO MEMBERSHIP VALUES ('NoMembership');
136INSERT INTO MEMBERSHIP VALUES ('Basic');
137INSERT INTO MEMBERSHIP VALUES ('Silver');
138INSERT INTO MEMBERSHIP VALUES ('Gold');
139INSERT INTO MEMBERSHIP VALUES ('Diamond');
140
141INSERT INTO GRANTS_ACCESS_TO VALUES ('NoMembership',1);
142INSERT INTO GRANTS_ACCESS_TO VALUES ('Basic',1);
143INSERT INTO GRANTS_ACCESS_TO VALUES ('Basic',2);
144INSERT INTO GRANTS_ACCESS_TO VALUES ('Silver',1);
145INSERT INTO GRANTS_ACCESS_TO VALUES ('Silver',2);
146INSERT INTO GRANTS_ACCESS_TO VALUES ('Silver',3);
147INSERT INTO GRANTS_ACCESS_TO VALUES ('Gold',1);
148INSERT INTO GRANTS_ACCESS_TO VALUES ('Gold',2);
149INSERT INTO GRANTS_ACCESS_TO VALUES ('Gold',3);
150INSERT INTO GRANTS_ACCESS_TO VALUES ('Gold',4);
151INSERT INTO GRANTS_ACCESS_TO VALUES ('Gold',12);
152INSERT INTO GRANTS_ACCESS_TO VALUES ('Gold',11);
153INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',1);
154INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',2);
155INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',3);
156INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',4);
157INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',5);
158INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',6);
159INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',7);
160INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',8);
161INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',9);
162INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',10);
163INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',11);
164INSERT INTO GRANTS_ACCESS_TO VALUES ('Diamond',12);
165
166INSERT INTO REGISTEREDUSER VALUES ('DarceyRowanne@gmail.com','Darcey','PASSWORD1','Diamond');
167INSERT INTO REGISTEREDUSER VALUES ('DaynaYorick@gmail.com','Dayna','PASSWORD2','Diamond');
168INSERT INTO REGISTEREDUSER VALUES ('ErnieTria@gmail.com','Ernie','PASSWORD3','Diamond');
169INSERT INTO REGISTEREDUSER VALUES ('WestleyCandice@yahoo.com','Westley','PASSWORD4','Gold');
170INSERT INTO REGISTEREDUSER VALUES ('KierstenTabatha@gmail.com','Kiersten','PASSWORD5','Gold');
171INSERT INTO REGISTEREDUSER VALUES ('SteveSherry@gmail.com','Steve','PASSWORD6','Silver');
172INSERT INTO REGISTEREDUSER VALUES ('AuraRachelle@yahoo.com','Aura','PASSWORD7','Basic');
173INSERT INTO REGISTEREDUSER VALUES ('FelixParker@gmail.com','Felix','PASSWORD8','NoMembership');
174INSERT INTO REGISTEREDUSER VALUES ('IbbieMelicent@yahoo.com','Ibbie','PASSWORD9','NoMembership');
175
176INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.nextval,1,'DarceyRowanne@gmail.com','text komentara1');
177INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.nextval,0,'DaynaYorick@gmail.com','text komentara2');
178INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.nextval,0,'ErnieTria@gmail.com','text komentara3');
179INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.nextval,5,'ErnieTria@gmail.com','text komentara4');
180INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.nextval,7,'ErnieTria@gmail.com','text komentara5');
181INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.nextval,3,'ErnieTria@gmail.com','text komentara51');
182INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.nextval,12,'AuraRachelle@yahoo.com','text komentara6');
183INSERT INTO ARTICLECOMMENT VALUES (AutoIncSeq.nextval,3,'AuraRachelle@yahoo.com','text komentara7');
184
185------- JEDNODUCHE SELECTY
186/*Zobraz vsetkych registrovanych uzivatelov kt. maju gmail.*/
187CREATE OR REPLACE VIEW GmailUsers AS
188SELECT * FROM REGISTEREDUSER WHERE REGEXP_LIKE (EMAIL, '*.@gmail.com');
189
190/*Zobraz vÅ¡etky Älánky ktoré patria do kategórie fashion.*/
191CREATE OR REPLACE VIEW FashionCategory AS
192SELECT * FROM ARTICLE WHERE CATEGORYNAME='fashion';
193
194/*najdi vsetky clanky ktore patria do kategorie science a boli napisane senior editorom*/
195CREATE OR REPLACE VIEW ScienceArticlesBySeniorEditor AS
196SELECT * FROM ARTICLE
197WHERE ARTICLE.EMAIL=(SELECT EMAIL
198FROM EDITOR
199WHERE EDITOR.POSITIONNAME='senior editor')
200and ARTICLE.CATEGORYNAME='science';
201
202------- SPAJANIE TABULIEK
203/*Mená a emaily editorov, ktorà už napÃsali nejaký (aspoň 1) Älánok. ----------- outer JOIN */
204CREATE OR REPLACE VIEW OuterJOIN AS
205SELECT UNIQUE EDITOR.PUBLISHINGNAME, ARTICLE.EMAIL
206FROM EDITOR
207RIGHT OUTER JOIN ARTICLE
208ON EDITOR.EMAIL = ARTICLE.EMAIL;
209
210/*Vypise pouzivatelov, ktory napisali nejaký komentár, daný komentár a pocet likov. -------- spajanie 2 tabuliek */
211CREATE OR REPLACE VIEW TwoTablesJOIN AS
212SELECT u.REGISTEREDUSERNAME,u.EMAIL,a.COMMENTTEXT,a.NUMBEROFLIKES
213FROM ARTICLECOMMENT a
214JOIN REGISTEREDUSER u
215on a.EMAIL = u.EMAIL
216order by a.ARTICLECOMMENTID;
217
218/*vsetky clanky napisane serior editorom ku ktorym maju pristup uzivatelia s gold membershipom ---------- spajanie 3 tabuliek */
219CREATE OR REPLACE VIEW ThreeTablesJOIN AS
220SELECT a.TITLE, e.POSITIONNAME, gat.type
221FROM ARTICLE a
222JOIN EDITOR e on a.EMAIL=e.EMAIL
223JOIN GRANTS_ACCESS_TO gat on gat.ARTICLEID=a.ARTICLEID
224WHERE gat.TYPE='Gold' and e.POSITIONNAME='senior editor';
225
226------ AGREGACNE FUNKCIE
227/*Pocet najnovsich clankov - napisanych v posledny den publikacie na stranke.*/
228CREATE OR REPLACE VIEW NewestArticles AS
229SELECT count(ARTICLEID) AS "NUMBER OF NEWEST ARTICLES"
230FROM ARTICLE
231WHERE ARTICLE.DATEPUBLISHED=(SELECT max(DATEPUBLISHED) FROM ARTICLE);
232
233/*Najkratsie meno použÃvatela, ktorý neodberá ziaden membership.*/
234CREATE OR REPLACE VIEW ShortestNameWithNoMembership AS
235SELECT MIN(REGISTEREDUSERNAME) AS "SHORTEST NAME"
236FROM REGISTEREDUSER
237WHERE TYPE='NoMembership';
238
239/*Pocet clankov napisanych danym editorom.*/
240CREATE OR REPLACE VIEW NumberOfArticlesByEditor AS
241SELECT EMAIL, COUNT (EMAIL) AS "NUMBER OF ARTICLES"
242FROM ARTICLE
243GROUP BY EMAIL
244ORDER BY 2 DESC;