DROP DATABASE a11nikek;
CREATE DATABASE a11nikek;
USE a11nikek;

CREATE TABLE videobutik(
    namn VARCHAR(20),
    telnr VARCHAR (10),
    adress VARCHAR(50),
    PRIMARY KEY(namn)
) Engine = INNODB;

CREATE TABLE anstalld(
    anstallnr VARCHAR(20),
    lon INT,
    namn VARCHAR (20),
    chef VARCHAR (20),
    FOREIGN KEY (chef) REFERENCES anstalld(anstallnr),
    PRIMARY KEY(anstallnr)
) Engine = INNODB;


CREATE TABLE kampanj (
    namn VARCHAR(20),
    veckonr VARCHAR (20),
    ansvarig VARCHAR (20),
    FOREIGN KEY (ansvarig) REFERENCES anstalld(anstallnr),
    PRIMARY KEY (namn)
) Engine = INNODB;

CREATE TABLE hylla (
    hyllnr VARCHAR (20),
    butik VARCHAR (20),
    FOREIGN KEY (butik) REFERENCES videobutik(namn),
    PRIMARY KEY (hyllnr,butik)
) Engine = INNODB;

CREATE TABLE utgivare (
    namn VARCHAR (20),
    telnr VARCHAR (20),
    PRIMARY KEY (namn)
) Engine = INNODB;


CREATE TABLE film (
    titel VARCHAR (20),
    kategori VARCHAR (20),
    langd VARCHAR (20),
    utgivare VARCHAR (20),
    FOREIGN KEY (utgivare) REFERENCES utgivare(namn),
    PRIMARY KEY (titel)
) Engine = INNODB;

CREATE TABLE inkasso(
    telnr VARCHAR (20),
    stad VARCHAR (20),
    namn VARCHAR (20),
    PRIMARY KEY (telnr)
) Engine = INNODB;

CREATE TABLE kund(
    pnr VARCHAR (11),
    namn VARCHAR (20),
    omdome VARCHAR (255),
    telnr VARCHAR (20),
    PRIMARY KEY (pnr)
) Engine = INNODB;

CREATE TABLE filmex(
    exnr VARCHAR (20),
    film VARCHAR (20),
    butik VARCHAR (20),
    pris VARCHAR (20),
    kategori VARCHAR (20),
    FOREIGN KEY (film) REFERENCES film(titel),
    FOREIGN KEY (butik) REFERENCES videobutik(namn),
    PRIMARY KEY (exnr, film, butik)
) Engine = INNODB;

CREATE TABLE uthyrning(
    tidendenuthyrdes datetime,
    film VARCHAR (20),
    butik VARCHAR (20),
    exnr VARCHAR (20),
    kund VARCHAR (20),
    inkasso VARCHAR (20),
    uthyrningstid datetime,
    faktisktaterlamningstid datetime,
    FOREIGN KEY (exnr, film, butik) REFERENCES filmex(exnr, film, butik),
    FOREIGN KEY (inkasso) REFERENCES inkasso(telnr),
    FOREIGN KEY (kund) REFERENCES kund(pnr),
    PRIMARY KEY (tidendenuthyrdes, film, butik, exnr, kund)
) Engine = INNODB;

CREATE TABLE finnsihylla(
    exnr VARCHAR (20),
    film VARCHAR (20),
    butik VARCHAR (20),
    butikhylla VARCHAR (20),
    hyllnr VARCHAR (20),
    FOREIGN KEY (exnr, film, butik) REFERENCES filmex(exnr, film, butik),
    FOREIGN KEY (butikhylla, hyllnr) REFERENCES hylla (butik, hyllnr),
    PRIMARY KEY (exnr, film, butik, hyllnr, butikhylla)
) Engine = INNODB;

CREATE TABLE arbetar(
    butik VARCHAR (20),
    anstalld VARCHAR (20),
    FOREIGN KEY (anstalld) REFERENCES anstalld(anstallnr),
    FOREIGN KEY (butik) REFERENCES videobutik(namn),
    PRIMARY KEY (anstalld, butik)
) Engine = INNODB;

CREATE TABLE kampanjfilm (
    kampanjnamn VARCHAR (20),
    filmtitel VARCHAR (20),
    FOREIGN KEY (kampanjnamn) REFERENCES kampanj(namn),
    FOREIGN KEY (filmtitel) REFERENCES film(titel),
    PRIMARY KEY (filmtitel, kampanjnamn)
) Engine = INNODB;

-- populera 1.0
INSERT INTO videobutik (namn, telnr, adress) VALUES('vipp-video', '03112345', 'Göteborg123');
INSERT INTO videobutik (namn, telnr, adress) VALUES('Axvallsgrillen', '031123456', 'Göteborg231');


-- populera 1.1
INSERT INTO anstalld (anstallnr, lon, namn, chef) VALUES ('31366', '36000', 'Bosse Lindqvist', '31366');
INSERT INTO arbetar (butik, anstalld) VALUES ('Vipp-video', '31366');

-- Populera 1.2
INSERT INTO anstalld (anstallnr, lon, namn, chef) VALUES ('87231', '8420', 'Biff Lindström', '31366');
INSERT INTO arbetar (butik, anstalld) VALUES ('Vipp-video', '87231');

-- Populera 1.3
INSERT INTO anstalld (anstallnr, lon, namn, chef) VALUES ('38065', '23000', 'Angela Bly', '38065');
INSERT INTO arbetar (butik, anstalld) VALUES ('Axvallsgrillen', '38065');

-- Populera 1.4
INSERT INTO anstalld (anstallnr, lon, namn, chef) VALUES ('66633', '10000', 'Ulf Bertilsson', '38065');
INSERT INTO arbetar (butik, anstalld) VALUES ('Axvallsgrillen', '66633');

-- Populera 1.5
INSERT INTO kampanj (namn, veckonr, ansvarig) VALUES ('Vinterruset', '48', '66633');

-- Populera 1.6
INSERT INTO kampanj (namn, veckonr, ansvarig) VALUES ('Sommarrullen', '26', '87231');

-- Populera 1.7
INSERT INTO utgivare (namn, telnr) VALUES ('Östproduktion', '097854321');
INSERT INTO utgivare (namn, telnr) VALUES ('Vaniljrelease', '068723472');
-- Populera 1.8
INSERT INTO film (titel, kategori, langd, utgivare) VALUES ('Sigges Sommar', 'Romantik', '97', 'Östproduktion');
INSERT INTO filmex (exnr, film, butik, pris, kategori) VALUES ('111', 'Sigges Sommar', 'Axvallsgrillen', '48', 'DVD');
INSERT INTO filmex (exnr, film, butik, pris, kategori) VALUES ('122', 'Sigges Sommar', 'Axvallsgrillen', '48', 'DVD');
INSERT INTO filmex (exnr, film, butik, pris, kategori) VALUES ('133', 'Sigges Sommar', 'Axvallsgrillen', '48', 'DVD');
INSERT INTO filmex (exnr, film, butik, pris, kategori) VALUES ('442', 'Sigges Sommar', 'Axvallsgrillen', '78', 'Bluray');
INSERT INTO filmex (exnr, film, butik, pris, kategori) VALUES ('443', 'Sigges Sommar', 'Axvallsgrillen', '78', 'Bluray');

-- Populera 1.9
INSERT INTO film (titel, kategori, langd, utgivare) VALUES ('Hårda Puckar', 'Sport', '62', 'Östproduktion');
INSERT INTO filmex (exnr, film, butik, pris, kategori) VALUES ('671', 'Hårda Puckar', 'Axvallsgrillen', '29', 'VHS');

-- Populera 1.10
INSERT INTO hylla (hyllnr, butik) VALUES ('114', 'Axvallsgrillen');
INSERT INTO finnsihylla (exnr, film, butik, butikhylla, hyllnr) VALUES ('111', 'Sigges Sommar', 'Axvallsgrillen', 'Axvallsgrillen', '114');
INSERT INTO finnsihylla (exnr, film, butik, butikhylla, hyllnr) VALUES ('122', 'Sigges Sommar', 'Axvallsgrillen', 'Axvallsgrillen', '114');
INSERT INTO finnsihylla (exnr, film, butik, butikhylla, hyllnr) VALUES ('133', 'Sigges Sommar', 'Axvallsgrillen', 'Axvallsgrillen', '114' );

-- Populera 1.11
INSERT INTO hylla (hyllnr, butik) VALUES ('115', 'AxvallsGrillen');
INSERT INTO finnsihylla (exnr, film, butik, butikhylla, hyllnr) VALUES ('442', 'Sigges Sommar', 'Axvallsgrillen', 'Axvallsgrillen', '114');
INSERT INTO finnsihylla (exnr, film, butik, butikhylla, hyllnr) VALUES ('443', 'Sigges Sommar', 'Axvallsgrillen', 'Axvallsgrillen', '114');

-- Populera 1.12
INSERT INTO hylla (hyllnr, butik) VALUES ('114', 'vipp-video');
INSERT INTO film (titel, kategori, langd, utgivare) VALUES ('Mopedjakten', 'Drama', '5', 'Vaniljrelease');
INSERT INTO filmex (exnr, film, butik, pris, kategori) VALUES('333', 'Mopedjakten', 'vipp-video', '29', 'VHS');
INSERT INTO filmex (exnr, film, butik, pris, kategori) VALUES('334', 'Mopedjakten', 'vipp-video', '29', 'VHS');
INSERT INTO filmex (exnr, film, butik, pris, kategori) VALUES('335', 'Mopedjakten', 'vipp-video', '29', 'VHS');
INSERT INTO finnsihylla (exnr, film, butik, butikhylla, hyllnr) VALUES('333', 'Mopedjakten', 'vipp-video', 'vipp-video', '114');
INSERT INTO finnsihylla (exnr, film, butik, butikhylla, hyllnr) VALUES('334', 'Mopedjakten', 'vipp-video', 'vipp-video', '114');
INSERT INTO finnsihylla (exnr, film, butik, butikhylla, hyllnr) VALUES('335', 'Mopedjakten', 'vipp-video', 'vipp-video', '114');

-- Populera 1.13
INSERT INTO kund (pnr, namn, omdome, telnr) VALUES ('920128-5884', 'Josse Björk', 'pålitlig', '070012345');

-- Populera 1.14
INSERT INTO kund (pnr, namn, omdome, telnr) VALUES ('670723-5835', 'Björn Bark', 'stamkund', '0788883212');

-- Populera 1.15
INSERT INTO inkasso (telnr, stad, namn) VALUES ('053334577', 'Stöpet', 'Inkasso AB');

-- Populera 1.16
INSERT INTO inkasso (telnr, stad, namn) VALUES ('00326573892', 'Varola', 'Grå&Trist HB');

-- Populera 1.17
INSERT INTO uthyrning (tidendenuthyrdes, film, butik, exnr, kund, inkasso, uthyrningstid, faktisktaterlamningstid) VALUES ('2012-01-12 18:01:00', 'Sigges Sommar', 'Axvallsgrillen', '442', '920128-5884', '00326573892', '2012-01-13 18:01:00', '2012-01-13 17:59:00');

-- Populera 1.18
INSERT INTO uthyrning (tidendenuthyrdes, film, butik, exnr, kund, inkasso, uthyrningstid, faktisktaterlamningstid) VALUES ('2012-02-16 12:34:00', 'Hårda Puckar' , 'Axvallsgrillen' , '671', '670723-5835', '053334577', '2012-02-20 18:00:00', NULL);

-- Populera 1.19
INSERT INTO uthyrning (tidendenuthyrdes, film, butik, exnr, kund, inkasso, uthyrningstid, faktisktaterlamningstid) VALUES ('2012-01-12 17:00:00', 'Sigges Sommar', 'Axvallsgrillen', '442', '670723-5835', '00326573892', '2012-01-13 17:00:00', '2012-01-25 17:59:00');
-- Populera 1.20
INSERT INTO kampanjfilm (kampanjnamn, filmtitel) VALUES ('Vinterruset', 'Hårda Puckar');

-- Populera inlaga för frågor
-- fråga 10
INSERT INTO kund (pnr, namn, omdome, telnr) VALUES ('910607-2312', 'Sebastian Olsson', 'Skurk', '0700519423');

INSERT INTO uthyrning (tidendenuthyrdes, film, butik, exnr, kund, inkasso, uthyrningstid, faktisktaterlamningstid) VALUES ('2012-02-13 17:00:00', 'Sigges Sommar', 'Axvallsgrillen', '442', '910607-2312', '053334577', '2012-02-13 17:00:00', '2012-02-14 17:59:00');

-- Frågespråk 1.1
SELECT stad FROM inkasso WHERE telnr='00326573892';

-- Frågespråk 1.2
SELECT butik FROM arbetar WHERE anstalld='31366';

-- FrågeSpråk 1.3
SELECT utgivare.namn, utgivare.telnr FROM film, utgivare WHERE film.utgivare=utgivare.namn AND titel ='sigges sommar';

-- FrågeSpråk 1.4
SELECT arbetar.butik FROM arbetar, kampanj WHERE kampanj.namn='Vinterruset' AND kampanj.ansvarig=arbetar.anstalld;

-- Frågespråk 1.5
SELECT kampanjfilm.kampanjnamn FROM kampanjfilm WHERE filmtitel='Hårda Puckar';

-- Frågespråk 1.6
SELECT * FROM filmex e1, filmex e2 WHERE e1.pris=e2.pris AND e1.film<>e2.film;

-- Frågespråk 1.7
SELECT kund.namn, kund.pnr FROM kund , uthyrning WHERE uthyrning.kund=kund.pnr AND faktisktaterlamningstid IS NULL;

-- Frågespråk 1.8
SELECT kund.namn, kund.pnr FROM kund , uthyrning WHERE uthyrning.kund=kund.pnr AND DATE_ADD(tidendenuthyrdes, INTERVAL 10 DAY)<faktisktaterlamningstid;

-- FrågeSpråk 1.9
SELECT kund.namn FROM kund WHERE NOT EXISTS (SELECT * FROM uthyrning WHERE kund.pnr=uthyrning.kund);

-- Frågespråk 1.10
SELECT kund.namn FROM kund, uthyrning WHERE kund.pnr=uthyrning.kund;

-- Frågespråk 1.11
SELECT kund.namn FROM kund WHERE kund.pnr IN (SELECT uthyrning.kund FROM uthyrning GROUP BY uthyrning.kund HAVING COUNT(*)=2);

-- FrågeSpråk 1.12
SELECT anstalld.namn FROM anstalld ORDER BY anstalld.namn DESC;

-- FrågeSpråk 1.13
SELECT AVG(filmex.pris) FROM filmex;

-- FrågeSpråk 1.14
SELECT AVG(anstalld.lon), arbetar.butik FROM anstalld, arbetar WHERE anstalld.anstallnr=arbetar.anstalld GROUP BY (arbetar.butik);

-- Frågespråk 1.15
-- Hämta medelkostnaden för varje kund som lämnat åter samtliga av sina filmer. (Tips: använd Not exists och is null.)
SELECT AVG(filmex.pris), kund.namn FROM kund, filmex, uthyrning WHERE kund.pnr=uthyrning.kund AND filmex.exnr=uthyrning.exnr AND NOT EXISTS (SELECT * FROM uthyrning WHERE kund.pnr=uthyrning.kund AND uthyrning.faktisktaterlamningstid IS NULL);

-- FrågeSpråk 1.16 inte klar
SELECT inkasso.telnr FROM inkasso WHERE (SELECT COUNT(*) FROM uthyrning WHERE uthyrning.inkasso=inkasso.telnr AND inkasso.telnr <> '00326573892')=(SELECT COUNT(*) FROM uthyrning WHERE uthyrning.inkasso = '00326573892');
-- Frågespråk 1.17
SELECT *  FROM uthyrning AS uthyrning1, uthyrning AS uthyrning2 WHERE uthyrning1.exnr=uthyrning2.exnr AND uthyrning1.uthyrningstid<uthyrning2.uthyrningstid AND uthyrning1.faktisktaterlamningstid<=uthyrning2.faktisktaterlamningstid;

-- Frågespråk 1.18
SELECT * FROM inkasso WHERE stad LIKE 'E%';

-- Frågespråk 1.19
SELECT kund.namn, kund.pnr FROM kund WHERE kund.pnr NOT RLIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';

-- Frågespårk 1.20
SELECT kund.namn, kund.telnr, filmex.pris FROM kund, uthyrning, filmex WHERE uthyrning.kund=kund.pnr AND uthyrning.exnr=filmex.exnr AND uthyrning.film=filmex.film AND uthyrning.butik=filmex.butik AND filmex.pris=(SELECT MAX(pris) FROM filmex);

-- Frågespråk 1.21
SELECT kund.namn FROM uthyrning, kund WHERE uthyrning.kund=kund.pnr AND uthyrning.film ='Sataythai' AND uthyrning.butik='Vipp-video' ORDER BY uthyrning.uthyrningstid DESC LIMIT 1;
-- Frågespråk 1.22
SELECT uthyrning.film FROM uthyrning WHERE DATEDIFF(CURDATE(),uthyrning.uthyrningstid)<7;

-- Frågespråk 1.23
UPDATE anstalld SET lon = 1.22 * lon WHERE lon BETWEEN 10000 AND 12000;

-- Frågespråk 1.24
DELETE FROM filmex WHERE exnr='442';

-- Frågespråk 1.25
DELETE FROM anstalld WHERE anstallnr='31366';