mRdC88qU

1/* kreirati bazu ako ne postoji */
2create database if not exists kolokvijum;
3use kolokvijum;
4/* obrisati tabele ako već postoje */
5drop table if exists Movie;
6drop table if exists Reviewer;
7drop table if exists Rating;
8
9/* kreirati tabele */
10create table Movie(mID int, title text, year int, director text);
11create table Reviewer(rID int, name text);
12create table Rating(rID int, mID int, stars int, ratingDate date);
13
14/* popuniti tabele podacima */
15insert into Movie values(101, 'Gone with the Wind', 1939, 'Victor Fleming');
16insert into Movie values(102, 'Star Wars', 1977, 'George Lucas');
17insert into Movie values(103, 'The Sound of Music', 1965, 'Robert Wise');
18insert into Movie values(104, 'E.T.', 1982, 'Steven Spielberg');
19insert into Movie values(105, 'Titanic', 1997, 'James Cameron');
20insert into Movie values(106, 'Snow White', 1937, null);
21insert into Movie values(107, 'Avatar', 2009, 'James Cameron');
22insert into Movie values(108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg');
23
24insert into Reviewer values(201, 'Sarah Martinez');
25insert into Reviewer values(202, 'Daniel Lewis');
26insert into Reviewer values(203, 'Brittany Harris');
27insert into Reviewer values(204, 'Mike Anderson');
28insert into Reviewer values(205, 'Chris Jackson');
29insert into Reviewer values(206, 'Elizabeth Thomas');
30insert into Reviewer values(207, 'James Cameron');
31insert into Reviewer values(208, 'Ashley White');
32
33insert into Rating values(201, 101, 2, '2011-01-22');
34insert into Rating values(201, 101, 4, '2011-01-27');
35insert into Rating values(202, 106, 4, null);
36insert into Rating values(203, 103, 2, '2011-01-20');
37insert into Rating values(203, 108, 4, '2011-01-12');
38insert into Rating values(203, 108, 2, '2011-01-30');
39insert into Rating values(204, 101, 3, '2011-01-09');
40insert into Rating values(205, 103, 3, '2011-01-27');
41insert into Rating values(205, 104, 2, '2011-01-22');
42insert into Rating values(205, 108, 4, null);
43insert into Rating values(206, 107, 3, '2011-01-15');
44insert into Rating values(206, 106, 5, '2011-01-19');
45insert into Rating values(207, 107, 5, '2011-01-20');
46insert into Rating values(208, 104, 3, '2011-01-02');
47
48
49
50/* ukloniti tabele ako vec postoje */
51drop table if exists Highschooler;
52drop table if exists Friend;
53drop table if exists Likes;
54
55/* kreirati tabele */
56create table Highschooler(ID int, name text, grade int);
57create table Friend(ID1 int, ID2 int);
58create table Likes(ID1 int, ID2 int);
59
60/* popuniti tabele podacima */
61insert into Highschooler values (1510, 'Jordan', 1);
62insert into Highschooler values (1689, 'Gabriel', 1);
63insert into Highschooler values (1381, 'Tiffany', 1);
64insert into Highschooler values (1709, 'Cassandra', 1);
65insert into Highschooler values (1101, 'Haley', 2);
66insert into Highschooler values (1782, 'Andrew', 2);
67insert into Highschooler values (1468, 'Kris', 2);
68insert into Highschooler values (1641, 'Brittany', 2);
69insert into Highschooler values (1247, 'Alexis', 3);
70insert into Highschooler values (1316, 'Austin', 3);
71insert into Highschooler values (1911, 'Gabriel', 3);
72insert into Highschooler values (1501, 'Jessica', 3);
73insert into Highschooler values (1304, 'Jordan', 4);
74insert into Highschooler values (1025, 'John', 4);
75insert into Highschooler values (1934, 'Kyle', 4);
76insert into Highschooler values (1661, 'Logan', 4);
77
78insert into Friend values (1510, 1381);
79insert into Friend values (1510, 1689);
80insert into Friend values (1689, 1709);
81insert into Friend values (1381, 1247);
82insert into Friend values (1709, 1247);
83insert into Friend values (1689, 1782);
84insert into Friend values (1782, 1468);
85insert into Friend values (1782, 1316);
86insert into Friend values (1782, 1304);
87insert into Friend values (1468, 1101);
88insert into Friend values (1468, 1641);
89insert into Friend values (1101, 1641);
90insert into Friend values (1247, 1911);
91insert into Friend values (1247, 1501);
92insert into Friend values (1911, 1501);
93insert into Friend values (1501, 1934);
94insert into Friend values (1316, 1934);
95insert into Friend values (1934, 1304);
96insert into Friend values (1304, 1661);
97insert into Friend values (1661, 1025);
98insert into Friend select ID2, ID1 from Friend;
99
100insert into Likes values(1689, 1709);
101insert into Likes values(1709, 1689);
102insert into Likes values(1782, 1709);
103insert into Likes values(1911, 1247);
104insert into Likes values(1247, 1468);
105insert into Likes values(1641, 1468);
106insert into Likes values(1934, 1709);
107insert into Likes values(1316, 1304);
108insert into Likes values(1501, 1934);
109insert into Likes values(1934, 1501);
110insert into Likes values(1934, 1101);
111insert into Likes values(1025, 1101);
112