· 8 years ago · Mar 18, 2018, 02:12 PM
1DROP SCHEMA IF EXISTS Bildedeling;
2CREATE SCHEMA Bildedeling;
3USE Bildedeling;
4CREATE TABLE Bruker (
5 BrukerID VARCHAR(32) NOT NULL,
6 Fornavn VARCHAR(32) NOT NULL,
7 Etternavn VARCHAR(32) NOT NULL,
8 Epost VARCHAR(64) NOT NULL,
9 PRIMARY KEY(BrukerID)
10);
11CREATE TABLE Bilde (
12 BildeID VARCHAR(32) NOT NULL,
13 Beskrivelse VARCHAR(255),
14 OpplastetDato DATE NOT NULL,
15 Fotograf VARCHAR(32) NOT NULL,
16 PRIMARY KEY (BildeId),
17 FOREIGN KEY (Fotograf) REFERENCES Bruker(BrukerID)
18);
19CREATE TABLE Likes (
20 BildeID VARCHAR(32) NOT NULL,
21 BrukerID VARCHAR(32) NOT NULL,
22 CONSTRAINT LikesPN PRIMARY KEY(BildeID, BrukerID),
23 CONSTRAINT LikesBildeFN FOREIGN KEY(BildeID) REFERENCES Bilde(BildeID),
24 CONSTRAINT LikesBrukerFN FOREIGN KEY(BrukerID) REFERENCES Bruker(BrukerID)
25);
26CREATE TABLE Kommentarer (
27 BildeID VARCHAR(32) NOT NULL,
28 BrukerID VARCHAR(32) NOT NULL,
29 Kommentaren TEXT NOT NULL,
30 CONSTRAINT KommentarPN PRIMARY KEY(BildeID, BrukerID),
31 CONSTRAINT KommentarBildeFN FOREIGN KEY(BildeID) REFERENCES Bilde(BildeID),
32 CONSTRAINT KommentarBrukerFN FOREIGN KEY(BrukerID) REFERENCES Bruker(BrukerID)
33);
34
35CREATE TABLE Emneknagg (
36 EmneknaggID INT NOT NULL AUTO_INCREMENT,
37 Emneknaggen VARCHAR(64) NOT NULL,
38 PRIMARY KEY(EmneknaggID)
39);
40
41CREATE TABLE TagForBilde (
42 BildeID VARCHAR(32) NOT NULL,
43 EmneknaggID INT NOT NULL,
44 CONSTRAINT TagForBildePN PRIMARY KEY(BildeID, EmneknaggID),
45 CONSTRAINT TagForBildeBildeFN FOREIGN KEY(BildeID) REFERENCES Bilde(BildeID),
46 CONSTRAINT TagForBildeEmneknaggFN FOREIGN KEY(EmneknaggID) REFERENCES Emneknagg(EmneknaggID)
47);
48
49
50
51INSERT INTO Bruker
52VALUES ('ant100', 'Eirik', 'Aanstad', 'Eirik.Aanstad@mail.com'),
53 ('ant101', 'Jean-Luc', 'Picard', 'Picard@mail.com'),
54 ('ant102', 'James', 'Kirk', 'Captain.Kirk@mail.com'),
55 ('ant103', 'Wesley', 'Crusher', 'Wes@mail.com');
56
57
58INSERT INTO Bilde
59VALUES ('pic100', 'Hyttekos', 20180218, 'ant100'),
60 ('pic101', 'The Starship Enterprise', 19870928, 'ant101'),
61 ('pic102', 'Live Long and Prosper', 20150227, 'ant102'),
62 ('pic103', 'Nye albummet til Trump og Kanye!
63Watch the wall', 20180201, 'ant100');
64
65
66INSERT INTO Likes
67VALUES ('pic102', 'ant100'),
68 ('pic102', 'ant101'),
69 ('pic102', 'ant102'),
70 ('pic103', 'ant101'),
71 ('pic101', 'ant102'),
72 ('pic100', 'ant102');
73
74
75
76INSERT INTO Kommentarer
77VALUES ('pic102', 'ant102', 'Rest In Peace dear friend'),
78 ('pic102', 'ant101', '<3'),
79 ('pic103', 'ant102', 'Lol'),
80 ('pic103', 'ant101', 'Not Cool Man');
81
82
83INSERT INTO Emneknagg
84VALUES ('100', '#VakreMolde'),
85 ('101', '#StarTrek'),
86 ('102', '#DonaldDuck'),
87 ('103', '#Love');
88
89
90
91INSERT INTO TagForBilde
92VALUES ('pic100', '100'),
93 ('pic100', '103'),
94 ('pic103', '102'),
95 ('pic102', '103'),
96 ('pic102', '101'),
97 ('pic101', '103'),
98 ('pic101', '101'),
99 ('pic103', '103');
100
101
102SELECT * FROM Bilde;
103
104
105SELECT Etternavn, Fornavn, Epost
106FROM Bruker
107ORDER BY Etternavn ASC;
108
109
110
111SELECT *
112FROM Bilde
113WHERE OpplastetDato > 20180201;
114
115
116SELECT COUNT(BrukerID) As AntallBrukere
117FROM Bruker;
118
119
120
121SELECT Fornavn, Etternavn, Count(Fotograf) As AntallBilder
122FROM Bruker LEFT JOIN Bilde
123ON Bruker.BrukerId = Bilde.Fotograf
124GROUP BY Fotograf;
125
126
127SELECT Fornavn, Etternavn, Count(Fotograf) As AntallBilder
128FROM Bruker LEFT JOIN Bilde
129ON Bruker.BrukerId = Bilde.Fotograf
130GROUP BY Fotograf HAVING AntallBilder < 1;
131
132
133
134SELECT Bilde.BildeID, Fotograf, COUNT(Kommentaren) as AntallKommentarer
135FROM Bilde LEFT JOIN Kommentarer
136ON Bilde.BildeID = Kommentarer.BildeID
137GROUP BY Bilde.BildeID HAVING AntallKommentarer < 1;
138
139
140SELECT BrukerID, BildeID
141FROM Likes
142WHERE BildeID = 'pic100';
143
144
145SELECT Fornavn, BildeID
146FROM LIKES INNER JOIN
147
148SELECT *
149FROM Kommentarer;