Advertisement
IvoB1n

Untitled

May 1st, 2021
1,471
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE Collectors CASCADE CONSTRAINTS;
  2. DROP TABLE Orders CASCADE CONSTRAINTS;
  3. DROP TABLE Magazin CASCADE CONSTRAINTS;
  4. DROP TABLE MangaVolume CASCADE CONSTRAINTS;
  5. DROP TABLE Episode CASCADE CONSTRAINTS;
  6. DROP TABLE Manga CASCADE CONSTRAINTS;
  7. DROP TABLE IndividualPages CASCADE CONSTRAINTS;
  8. DROP TABLE CharacterInManga CASCADE CONSTRAINTS;
  9. DROP TABLE Author CASCADE CONSTRAINTS;
  10. DROP TABLE Publisher CASCADE CONSTRAINTS;
  11. DROP TABLE Genre CASCADE CONSTRAINTS;
  12.  
  13. DROP TABLE OrderMagazin CASCADE CONSTRAINTS;
  14. DROP TABLE OrderMangaVolume CASCADE CONSTRAINTS;
  15. DROP TABLE MagazinEpisode CASCADE CONSTRAINTS;
  16. DROP TABLE CharacterInEpisode CASCADE CONSTRAINTS;
  17. DROP TABLE CollectorCharacter CASCADE CONSTRAINTS;
  18. DROP TABLE MangaCharacter CASCADE CONSTRAINTS;
  19. DROP TABLE CollectorManga CASCADE CONSTRAINTS;
  20. DROP TABLE MangaAuthor CASCADE CONSTRAINTS;
  21. DROP TABLE AuthorGenre CASCADE CONSTRAINTS;
  22. DROP TABLE MangaGenre CASCADE CONSTRAINTS;
  23.  
  24.  
  25. DROP SEQUENCE SEQ_Magazin;
  26.  
  27. CREATE TABLE Collectors
  28. (
  29.     CollectorId NUMBER GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
  30.     UserName VARCHAR(50) NOT NULL,
  31.     UserAddress VARCHAR(50) NOT NULL,
  32.     Email VARCHAR(50)  NOT NULL CHECK(REGEXP_LIKE(Email,'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$')),
  33.     Phone VARCHAR(20) UNIQUE NOT NULL CHECK(Phone != ''),
  34.     PRIMARY KEY(CollectorId)
  35. );
  36.  
  37. CREATE TABLE Orders
  38. (
  39.     OrderId NUMBER GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
  40.     OrderDate DATE NOT NULL,
  41.     OrderType VARCHAR(50) NOT NULL,
  42.     OrderStatus VARCHAR(50) NOT NULL,
  43.     CollectorsId NUMBER NOT NULL,
  44.     PRIMARY KEY (OrderId)
  45. );
  46.  
  47. CREATE TABLE Magazin
  48. (
  49.     MagazinId NUMBER NOT NULL,
  50.     MagazinName VARCHAR(50) NOT NULL,
  51.     PublicationDate DATE NOT NULL,
  52.     MagazinPrice NUMBER NOT NULL,
  53.     PRIMARY KEY (MagazinId)
  54. );
  55.  
  56. CREATE TABLE MangaVolume
  57. (
  58.     MangaVolumeId NUMBER GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
  59.     VolumeName VARCHAR(50) NOT NULL,
  60.     PublicationDate DATE NOT NULL,
  61.     VolumePrice NUMBER NOT NULL,
  62.     PRIMARY KEY (MangaVolumeId)
  63. );
  64.  
  65. CREATE TABLE Episode
  66. (
  67.     EpisodeId NUMBER GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
  68.     EpisodeName VARCHAR(50) NOT NULL,
  69.     EpisodeNumber NUMBER NOT NULL,
  70.     MangaVolId NUMBER NOT NULL,
  71.     InManga NUMBER NOT NULL,
  72.     PRIMARY KEY (EpisodeId)
  73. );
  74.  
  75. CREATE TABLE Manga
  76. (
  77.     MangaId NUMBER GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
  78.     MangaName VARCHAR(50) NOT NULL,
  79.     PublicationDateStart DATE NOT NULL,
  80.     PublicationDateEnd DATE NULL,
  81.     PublisherName VARCHAR(50) NOT NULL,
  82.     PRIMARY KEY (MangaId)
  83. );
  84.  
  85. CREATE TABLE IndividualPages
  86. (
  87.     IndividualPagesId NUMBER GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
  88.     EpisodeId NUMBER NOT NULL,
  89.     PageNumber NUMBER NOT NULL,
  90.     Content BLOB NULL,
  91.     PRIMARY KEY (IndividualPagesId)
  92. );
  93.  
  94. CREATE TABLE CharacterInManga
  95. (
  96.     CharacterInMangaId NUMBER GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
  97.     CharacterName VARCHAR(50) NOT NULL,
  98.     CharacterAge NUMBER,
  99.     Information VARCHAR(500) NOT NULL,
  100.     FirstOccurrence NUMBER NOT NULL,
  101.     LastOccurrence NUMBER NOT NULL,
  102.     Passed NUMBER NULL,
  103.     PRIMARY KEY (CharacterInMangaId)
  104. );
  105.  
  106. CREATE TABLE Author
  107. (
  108.     AuthorId NUMBER GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
  109.     AuthorName VARCHAR(50) NOT NULL,
  110.     AuthorAge NUMBER NULL,
  111.     AuthorAddress VARCHAR(100) NULL,
  112.     PRIMARY KEY (AuthorId)
  113. );
  114.  
  115. CREATE TABLE Publisher
  116. (
  117.     PublisherName VARCHAR(50) NOT NULL,
  118.     PublisherAddress VARCHAR(100) NOT NULL,
  119.     Email VARCHAR(50) UNIQUE NOT NULL CHECK(Email != ''),
  120.     Phone VARCHAR(20) UNIQUE NOT NULL CHECK(Phone != ''),
  121.     PRIMARY KEY (PublisherName)
  122. );
  123.  
  124. CREATE TABLE Genre
  125. (
  126.     GenreName VARCHAR(50) NOT NULL,
  127.     Information VARCHAR(1000) NOT NULL,
  128.     PRIMARY KEY (GenreName)
  129. );
  130.  
  131.  
  132. CREATE TABLE OrderMagazin
  133. (
  134.     OrderId NUMBER NOT NULL,
  135.     MagazinId NUMBER NOT NULL
  136. );
  137.  
  138. CREATE TABLE OrderMangaVolume
  139. (
  140.     OrderId NUMBER NOT NULL,
  141.     MangaVolumeId NUMBER NOT NULL
  142. );
  143.  
  144. CREATE TABLE MagazinEpisode
  145. (
  146.     MagazinId NUMBER NOT NULL,
  147.     EpisodeId NUMBER NOT NULL
  148. );
  149.  
  150. CREATE TABLE CharacterInEpisode
  151. (
  152.     EpisodeId NUMBER NOT NULL,
  153.     MangaCharacterId NUMBER NOT NULL
  154. );
  155.  
  156. CREATE TABLE CollectorCharacter
  157. (
  158.     CollectorId NUMBER NOT NULL,
  159.     MangaCharacterId NUMBER NOT NULL
  160. );
  161.  
  162. CREATE TABLE MangaCharacter
  163. (
  164.     Manga NUMBER NOT NULL,
  165.     MangaCharacter NUMBER NOT NULL
  166. );
  167.  
  168. CREATE TABLE CollectorManga
  169. (
  170.     CollectorId NUMBER NOT NULL,
  171.     MangaId NUMBER NOT NULL
  172. );
  173.  
  174. CREATE TABLE MangaAuthor
  175. (
  176.     MangaId NUMBER NOT NULL,
  177.     AuthorId NUMBER NOT NULL
  178. );
  179.  
  180. CREATE TABLE AuthorGenre
  181. (
  182.     AuthorId NUMBER NOT NULL,
  183.     GenreName VARCHAR(50) NOT NULL
  184. );
  185.  
  186. CREATE TABLE MangaGenre
  187. (
  188.     MangaId NUMBER NOT NULL,
  189.     GenreName VARCHAR(50) NOT NULL
  190. );
  191.  
  192. CREATE SEQUENCE SEQ_Magazin INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 0;
  193.  
  194. -- trigger for Id(PK) autocomplete
  195. CREATE OR REPLACE TRIGGER TR_Magazin BEFORE INSERT ON Magazin FOR EACH ROW
  196. BEGIN
  197.   IF :NEW.MagazinId IS NULL THEN
  198.     SELECT SEQ_Magazin.NEXTVAL INTO :NEW.MagazinId FROM DUAL;
  199.   END IF;
  200. END TR_Magazin;
  201. /
  202.  
  203. -- UPDATE system_info
  204. -- SET field_value = 'NewValue'
  205. -- WHERE field_desc IN (SELECT role_type
  206. --                      FROM system_users
  207. --                      WHERE user_name = 'uname')
  208.  
  209. -- define for how many episodes every character was in manga
  210.  
  211.  
  212. ALTER TABLE Orders ADD CONSTRAINT FK_Orders FOREIGN KEY (CollectorsId) REFERENCES Collectors (CollectorId);
  213. ALTER TABLE Episode ADD CONSTRAINT FK_Episode_1 FOREIGN KEY (MangaVolId) REFERENCES MangaVolume (MangaVolumeId);
  214. ALTER TABLE Episode ADD CONSTRAINT FK_Episode_2 FOREIGN KEY (InManga) REFERENCES Manga (MangaId);
  215. ALTER TABLE IndividualPages ADD CONSTRAINT FK_IndividualPages FOREIGN KEY (EpisodeId) REFERENCES Episode (EpisodeId);
  216. ALTER TABLE CharacterInManga ADD CONSTRAINT FK_CharacterInManga_1 FOREIGN KEY (FirstOccurrence) REFERENCES IndividualPages (IndividualPagesId);
  217. ALTER TABLE CharacterInManga ADD CONSTRAINT FK_CharacterInManga_2 FOREIGN KEY (LastOccurrence) REFERENCES IndividualPages (IndividualPagesId);
  218. ALTER TABLE Manga ADD CONSTRAINT FK_Publisher FOREIGN KEY (PublisherName) REFERENCES Publisher (PublisherName);
  219. ALTER TABLE OrderMagazin ADD CONSTRAINT FK_OrderMagazin_1 FOREIGN KEY (OrderId) REFERENCES Orders (OrderId);
  220. ALTER TABLE OrderMagazin ADD CONSTRAINT FK_OrderMagazin_2 FOREIGN KEY (MagazinId) REFERENCES Magazin (MagazinId);
  221. ALTER TABLE OrderMangaVolume ADD CONSTRAINT FK_OrderMangaVolume_1 FOREIGN KEY (OrderId) REFERENCES Orders (OrderId);
  222. ALTER TABLE OrderMangaVolume ADD CONSTRAINT FK_OrderMangaVolume_2 FOREIGN KEY (MangaVolumeId) REFERENCES MangaVolume (MangaVolumeId);
  223. ALTER TABLE MagazinEpisode ADD CONSTRAINT FK_MagazinEpisode_1 FOREIGN KEY (MagazinId) REFERENCES Magazin (MagazinId);
  224. ALTER TABLE MagazinEpisode ADD CONSTRAINT FK_MagazinEpisode_2 FOREIGN KEY (EpisodeId) REFERENCES Episode (EpisodeId);
  225. ALTER TABLE CharacterInEpisode ADD CONSTRAINT FK_CharacterInEpisode_1 FOREIGN KEY (EpisodeId) REFERENCES Episode (EpisodeId);
  226. ALTER TABLE CharacterInEpisode ADD CONSTRAINT FK_CharacterInEpisode_2 FOREIGN KEY (MangaCharacterId) REFERENCES CharacterInManga (CharacterInMangaId);
  227. ALTER TABLE CollectorCharacter ADD CONSTRAINT FK_CollectorCharacter_1 FOREIGN KEY (CollectorId) REFERENCES Collectors (CollectorId);
  228. ALTER TABLE CollectorCharacter ADD CONSTRAINT FK_CollectorCharacter_2 FOREIGN KEY (MangaCharacterId) REFERENCES CharacterInManga (CharacterInMangaId);
  229. ALTER TABLE MangaCharacter ADD CONSTRAINT FK_MangaCharacter_1 FOREIGN KEY (Manga) REFERENCES Manga (MangaId);
  230. ALTER TABLE MangaCharacter ADD CONSTRAINT FK_MangaCharacter_2 FOREIGN KEY (MangaCharacter) REFERENCES CharacterInManga (CharacterInMangaId);
  231. ALTER TABLE CollectorManga ADD CONSTRAINT FK_CollectorManga_1 FOREIGN KEY (CollectorId) REFERENCES Collectors (CollectorId);
  232. ALTER TABLE CollectorManga ADD CONSTRAINT FK_CollectorManga_2 FOREIGN KEY (MangaId) REFERENCES Manga (MangaId);
  233. ALTER TABLE MangaAuthor ADD CONSTRAINT FK_MangaAuthor_1 FOREIGN KEY (MangaId) REFERENCES Manga (MangaId);
  234. ALTER TABLE MangaAuthor ADD CONSTRAINT FK_MangaAuthor_2 FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId);
  235. ALTER TABLE AuthorGenre ADD CONSTRAINT FK_AuthorGenre_1 FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId);
  236. ALTER TABLE AuthorGenre ADD CONSTRAINT FK_AuthorGenre_2 FOREIGN KEY (GenreName) REFERENCES Genre (GenreName);
  237. ALTER TABLE MangaGenre ADD CONSTRAINT FK_MangaGenre_1 FOREIGN KEY (MangaId) REFERENCES Manga (MangaId);
  238. ALTER TABLE MangaGenre ADD CONSTRAINT FK_MangaGenre_2 FOREIGN KEY (GenreName) REFERENCES Genre (GenreName);
  239.  
  240. INSERT INTO Author (AuthorName, AuthorAge, AuthorAddress) VALUES ('Naoshi Arakawa', NULL, 'Japan');
  241. INSERT INTO Author (AuthorName, AuthorAge, AuthorAddress) VALUES ('Hadzime Isaiama', '34', 'Japan');
  242.  
  243. INSERT INTO Genre (GenreName, Information) VALUES ('Drama', 'Drama is the specific mode of fiction represented in performance: a play, opera, mime, ballet, etc., performed in a theatre, or on radio or television. Considered as a genre of poetry in general, the dramatic mode has been contrasted with the epic and the lyrical modes ever since Aristotles Poetics —the earliest work of dramatic theory. The term drama comes from a Greek word meaning action (Classical Greek: δρᾶμα, drama), which is derived from I do (Classical Greek: δράω, drao). The two masks associated with drama represent the traditional generic division between comedy and tragedy.');
  244. INSERT INTO Genre (GenreName, Information) VALUES ('Anti-utopia', 'A dystopia is a fictional community or society that is undesirable or frightening. It is often treated as an antonym of utopia, a term that was coined by Sir Thomas More and figures as the title of his best known work, published in 1516, which created a blueprint for an ideal society with minimal crime, violence and poverty. But the relationship between utopia and dystopia is more complex than this, as there exist utopian elements in many dystopias, and vice-versa.');
  245.  
  246. INSERT INTO Collectors (UserName, UserAddress, Email, Phone) VALUES ('Ivan', 'Jilkova, 61500, Brno', 'xbobro01@fit.vutbr.cz', '+4200000000000');
  247. INSERT INTO Collectors (UserName, UserAddress, Email, Phone) VALUES ('Dmitrii', 'Kastanova, 62000, Brno', 'xkozhe00@fit.vutbr.cz', '+420111111111');
  248. INSERT INTO Collectors (UserName, UserAddress, Email, Phone) VALUES ('Snezhana', 'Kastanova, 62000, Brno', 'xhunter01@fit.vutbr.cz', '+420111111222');
  249.  
  250. INSERT INTO Publisher (PublisherName, PublisherAddress, Email, Phone) VALUES ('Kodansha', 'Japan, Tokio', 'kodansha.co.jp', '+894256899865');
  251. INSERT INTO Publisher (PublisherName, PublisherAddress, Email, Phone) VALUES ('Lupus', 'Russia, Tokio', 'lupus.su', '+2552462466');
  252.  
  253. INSERT INTO Manga (MangaName, PublicationDateStart, PublicationDateEnd, PublisherName) VALUES ('Shigatsu wa Kimi no Uso (Your Lie in April)', DATE '2017-01-02', DATE '2020-01-22', 'Kodansha');
  254. INSERT INTO Manga (MangaName, PublicationDateStart, PublicationDateEnd, PublisherName) VALUES ('Shingeki no Kyojin (Attack on titan)', DATE '2017-01-02', DATE '2020-01-22', 'Kodansha');
  255. INSERT INTO Manga (MangaName, PublicationDateStart, PublicationDateEnd, PublisherName) VALUES ('Naruto', DATE '2010-01-02', DATE '2019-01-22', 'Kodansha');
  256. INSERT INTO Manga (MangaName, PublicationDateStart, PublicationDateEnd, PublisherName) VALUES ('Boruto', DATE '2017-01-02', DATE '2021-01-22', 'Kodansha');
  257.  
  258. INSERT INTO MangaVolume (VolumeName, PublicationDate, VolumePrice) VALUES ('Volume 1', DATE '2018-01-02', 10);
  259. INSERT INTO MangaVolume (VolumeName, PublicationDate, VolumePrice) VALUES ('Volume 2', DATE '2018-01-10', 15);
  260.  
  261. INSERT INTO Episode (EpisodeName, EpisodeNumber, MangaVolId, InManga) VALUES ('Monotone/Colorful', 1, 1, 1);
  262. INSERT INTO Episode (EpisodeName, EpisodeNumber, MangaVolId, InManga) VALUES ('Friend A', 2, 1, 1);
  263.  
  264. INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 1', DATE '2018-01-02', 10);
  265. INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 2', DATE '2018-01-05', 45);
  266. INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 3', DATE '2018-01-02', 60);
  267. INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 4', DATE '2018-01-05', 56);
  268. INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 4', DATE '2018-01-15', 42);
  269. INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 4', DATE '2018-01-16', 43);
  270. INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 5', DATE '2018-01-17', 29);
  271. INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 5', DATE '2018-01-18', 48);
  272. INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 6', DATE '2018-01-19', 71);
  273. INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 7', DATE '2018-01-20', 34);
  274. INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 1', DATE '2018-01-21', 52);
  275. INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 8', DATE '2018-01-22', 19);
  276. INSERT INTO Magazin (MagazinName, PublicationDate, MagazinPrice) VALUES ('Magazin 8', DATE '2018-01-23', 61);
  277.  
  278.  
  279. INSERT INTO IndividualPages (EpisodeId, PageNumber) VALUES (1, 12);
  280. INSERT INTO IndividualPages (EpisodeId, PageNumber) VALUES (1, 13);
  281.  
  282. INSERT INTO CharacterInManga (CharacterName, CharacterAge, Information, FirstOccurrence, LastOccurrence, Passed) VALUES ('Eren Yeger', 14, 'Information about Eren', 1, 2, NULL);
  283. INSERT INTO CharacterInManga (CharacterName, CharacterAge, Information, FirstOccurrence, LastOccurrence, Passed) VALUES ('Mikasa Akkerman', 14, 'Information about Mikasa', 1, 1, NULL);
  284. INSERT INTO CharacterInManga (CharacterName, CharacterAge, Information, FirstOccurrence, LastOccurrence, Passed) VALUES ('Naruto Uzumaki', 16, 'Jsem stanu HOKAGE', 1, 1, NULL);
  285. INSERT INTO CharacterInManga (CharacterName, CharacterAge, Information, FirstOccurrence, LastOccurrence, Passed) VALUES ('Naruto Uzumaki', 28, 'Jsem HOKAGE', 1, 1, NULL);
  286. INSERT INTO CharacterInManga (CharacterName, Information, FirstOccurrence, LastOccurrence, Passed) VALUES ('Ococuke Kaguja', 'Not Information', 1, 1, NULL);
  287.  
  288. INSERT INTO Orders (OrderDate, OrderType, OrderStatus, CollectorsId) VALUES (DATE '2018-01-11', 'On-line', 'Done', 1);
  289. INSERT INTO Orders (OrderDate, OrderType, OrderStatus, CollectorsId) VALUES (DATE '2018-03-12', 'On-line + book', 'In process', 2);
  290. INSERT INTO Orders (OrderDate, OrderType, OrderStatus, CollectorsId) VALUES (DATE '2018-03-13', 'On-line + book', 'In process', 2);
  291. INSERT INTO Orders (OrderDate, OrderType, OrderStatus, CollectorsId) VALUES (DATE '2018-03-14', 'On-line + book', 'Canceled', 2);
  292. INSERT INTO Orders (OrderDate, OrderType, OrderStatus, CollectorsId) VALUES (DATE '2018-03-15', 'On-line + book', 'In process', 3);
  293. INSERT INTO Orders (OrderDate, OrderType, OrderStatus, CollectorsId) VALUES (DATE '2018-03-15', 'On-line + book', 'Done', 2);
  294. INSERT INTO Orders (OrderDate, OrderType, OrderStatus, CollectorsId) VALUES (DATE '2018-03-15', 'On-line + book', 'In process', 1);
  295.  
  296.  
  297. -- SQL skript obsahující dotazy SELECT musí obsahovat konkrétně alespoň dva dotazy
  298. -- využívající spojení dvou tabulek
  299.  
  300. -- information to send notification about order status
  301. SELECT  Collectors.UserName, Collectors.Phone, Collectors.Email, Orders.OrderStatus
  302. FROM Orders JOIN Collectors ON Orders.OrderId = Collectors.CollectorID
  303. ORDER BY Orders.OrderStatus;
  304.  
  305. -- which publisher publish manga from certain author
  306. SELECT Manga.MangaName, Manga.PublisherName, AuthorName
  307. FROM Author JOIN Manga ON Author.AuthorId = Manga.MangaId
  308. ORDER BY Author.AuthorName;
  309.  
  310. -- jeden využívající spojení tří tabulek,
  311.  
  312. -- find information about charecter in certain episode and certain manga
  313. SELECT CharacterInManga.CharacterName, CharacterInManga.CharacterAge, CharacterInManga.Information, Manga.MangaName, Episode.EpisodeNumber
  314. FROM CharacterInManga
  315. JOIN Episode ON CharacterInManga.CharacterInMangaId = Episode.EpisodeId
  316. JOIN Manga ON CharacterInManga.CharacterInMangaId = Manga.MangaId
  317. ORDER BY CharacterInManga.CharacterName;
  318.  
  319. -- dva dotazy s klauzulí GROUP BY a agregační funkcí
  320.  
  321. -- return an oldest version of characters
  322. SELECT CharacterName, MAX( CharacterAge )
  323. FROM CharacterInManga GROUP BY CharacterName
  324. ORDER BY CharacterName;
  325.  
  326. -- returns the minimum price for each magazine in the given price range.
  327. SELECT MagazinName, MIN(MagazinPrice)
  328. FROM Magazin
  329. --WHERE MagazinPrice =(
  330.     --SELECT MAX(MagazinPrice)
  331.     --FROM Magazin
  332. --)
  333. GROUP BY MagazinName
  334. HAVING MIN(MagazinPrice) BETWEEN 40 AND 70
  335. ORDER BY MagazinName;
  336.  
  337. --jeden dotaz obsahující predikát EXISTS
  338.  
  339. -- search for chars if they're age is present
  340.  
  341. SELECT CharacterName, CharacterAge, Information
  342. FROM CharacterInManga
  343. WHERE EXISTS (
  344.         SELECT *
  345.         FROM Manga
  346.         WHERE CharacterInManga.CharacterInMangaId = Manga.MangaId
  347.     );
  348.  
  349.  
  350.  
  351. --jeden dotaz s predikátem IN s vnořeným selectem (nikoliv IN s množinou konstantních dat).
  352.  
  353. -- search for order status, where status is 'in progress'
  354.  
  355. SELECT OrderId, UserName, CollectorId, OrderStatus
  356. FROM Orders FULL OUTER JOIN Collectors ON Orders.CollectorsId = Collectors.CollectorID
  357. WHERE OrderStatus IN (
  358.     'In process'
  359. )
  360. ORDER BY OrderId;
  361.  
  362. SELECT * FROM ORDERS;
  363. SELECT * FROM COLLECTORS;
  364. SELECT * FROM MAGAZIN;
  365. SELECT * FROM CHARACTERINMANGA;
  366. SELECT * FROM Episode;
  367.  
  368. --SQL skript pro vytvoření pokročilých objektů schématu databáze – SQL skript, který nejprve vytvoří základní objekty schéma databáze a naplní tabulky ukázkovými daty (stejně jako skript v bodě 2),
  369. -- a poté zadefinuje či vytvoří pokročilá omezení či objekty databáze dle upřesňujících požadavků zadání.
  370. --Dále skript bude obsahovat ukázkové příkazy manipulace dat a dotazy demonstrující použití výše zmiňovaných omezení a objektů tohoto skriptu (např. pro demonstraci použití indexů zavolá nejprve skript EXPLAIN PLAN na dotaz bez indexu, poté vytvoří index, a nakonec zavolá EXPLAIN PLAN na dotaz s indexem; pro demostranci databázového triggeru se provede manipulace s daty, která vyvolá daný trigger; atp.).
  371. --Dokumentace popisující finální schéma databáze – Dokumentace popisující řešení ze skriptu v bodě 4 vč. jejich zdůvodnění (např. popisuje výstup příkazu EXPLAIN PLAN bez indexu, důvod vytvoření zvoleného indexu, a výstup EXPLAIN PLAN s indexem, atd.).
  372.  
  373. -- index and explain plan
  374.  
  375. EXPLAIN PLAN SET STATEMENT_ID = 'performanceAnalysisBefore' FOR
  376.   SELECT MagazinId, MagazinName, Magazin.MagazinPrice, COUNT(*)
  377.   FROM Magazin WHERE MagazinPrice < 50
  378.   GROUP BY (MagazinId, MagazinName, Magazin.MagazinPrice);
  379. SELECT * FROM TABLE(DBMS_XPLAN.display);
  380.  
  381. CREATE INDEX performanceIndex ON Magazin (MagazinPrice);
  382.  
  383. EXPLAIN PLAN SET STATEMENT_ID = 'performanceAnalysisAfter' FOR
  384.   SELECT MagazinId, MagazinName, MagazinPrice, COUNT(*)
  385.   FROM Magazin WHERE MagazinPrice < 50
  386.   GROUP BY (MagazinId, MagazinName, MagazinPrice);
  387. SELECT * FROM TABLE(DBMS_XPLAN.display);
  388.  
  389. DROP INDEX performanceIndex;
  390.  
  391.  
  392. GRANT ALL PRIVILEGES ON Collectors TO xkozhe00;
  393. GRANT ALL PRIVILEGES ON Orders TO xkozhe00;
  394. GRANT ALL PRIVILEGES ON Magazin TO xkozhe00;
  395. GRANT ALL PRIVILEGES ON MangaVolume TO xkozhe00;
  396. GRANT ALL PRIVILEGES ON Episode TO xkozhe00;
  397. GRANT ALL PRIVILEGES ON Manga TO xkozhe00;
  398. GRANT ALL PRIVILEGES ON IndividualPages TO xkozhe00;
  399. GRANT ALL PRIVILEGES ON CharacterInManga TO xkozhe00;
  400. GRANT ALL PRIVILEGES ON Author TO xkozhe00;
  401. GRANT ALL PRIVILEGES ON Publisher TO xkozhe00;
  402. GRANT ALL PRIVILEGES ON Genre TO xkozhe00;
  403. GRANT ALL PRIVILEGES ON OrderMagazin TO xkozhe00;
  404. GRANT ALL PRIVILEGES ON OrderMangaVolume TO xkozhe00;
  405. GRANT ALL PRIVILEGES ON MagazinEpisode TO xkozhe00;
  406. GRANT ALL PRIVILEGES ON CharacterInEpisode TO xkozhe00;
  407. GRANT ALL PRIVILEGES ON CollectorCharacter TO xkozhe00;
  408. GRANT ALL PRIVILEGES ON MangaCharacter TO xkozhe00;
  409. GRANT ALL PRIVILEGES ON CollectorManga TO xkozhe00;
  410. GRANT ALL PRIVILEGES ON MangaAuthor TO xkozhe00;
  411. GRANT ALL PRIVILEGES ON AuthorGenre TO xkozhe00;
  412. GRANT ALL PRIVILEGES ON MangaGenre TO xkozhe00;
  413.  
  414.  
  415. DROP MATERIALIZED VIEW MV_notify;
  416. -- DROP MATERIALIZED VIEW LOG ON Collectors;
  417.  
  418. CREATE MATERIALIZED VIEW LOG ON Collectors
  419.     WITH PRIMARY KEY
  420.     INCLUDING NEW VALUES;
  421.  
  422. CREATE MATERIALIZED VIEW MV_notify
  423.     BUILD IMMEDIATE        
  424.     REFRESH FAST ON COMMIT
  425.     AS SELECT CollectorId, Email  FROM Collectors;
  426.  
  427. SELECT * FROM MV_notify;
  428. GRANT SELECT ON MV_notify TO xkozhe00;
  429.  
  430.  
  431. CREATE OR REPLACE PROCEDURE UpdateMagazin
  432.    ( name_in IN VARCHAR )
  433.  IS
  434.    MPrice NUMBER;
  435.  
  436.    CURSOR c1 IS
  437.    SELECT MagazinPrice
  438.     FROM Magazin
  439.     WHERE MagazinName = name_in;
  440.  
  441. BEGIN
  442.  
  443.    OPEN c1;
  444.    FETCH c1 INTO MPrice;
  445.  
  446.    IF c1%notfound THEN
  447.       MPrice := -1;
  448.    ELSE MPrice := 50;
  449.    END IF;
  450.    
  451.    MagazinName := 'LALALA';
  452.    MPrice := 999;
  453.    INSERT INTO Magazin
  454.    ( MagazinName,
  455.      MagazinPrice )
  456.    VALUES
  457.    ( name_in,
  458.      MPrice );
  459.  
  460.    COMMIT;
  461.  
  462.    CLOSE c1;
  463.  
  464. EXCEPTION
  465. WHEN OTHERS THEN
  466.    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
  467. END;
  468. /
  469.  
  470. SELECT * FROM MAGAZIN;
  471. BEGIN
  472.   UpdateMagazin('Magazin 6');
  473. END;
  474. /
  475. SELECT * FROM MAGAZIN;
Advertisement
RAW Paste Data Copied
Advertisement