SQL till dbtek-tentan 2022-08-22 -------------------------------- Uppgift 3 (6 p) --------------- DROP TABLE Tandborstningar; DROP TABLE Tandborstar; DROP TABLE Modeller; DROP TABLE Användare; CREATE TABLE Användare (Nummer INTEGER NOT NULL PRIMARY KEY, Namn NVARCHAR(10) NOT NULL UNIQUE, Epostadress NVARCHAR(15) NOT NULL UNIQUE, Lösenord NVARCHAR(10) NOT NULL); CREATE TABLE Modeller (Nummer INTEGER NOT NULL PRIMARY KEY, Namn NVARCHAR(15) NOT NULL UNIQUE); CREATE TABLE Tandborstar (Nummer INTEGER NOT NULL PRIMARY KEY, Modell INTEGER NOT NULL REFERENCES Modeller(Nummer), Aktiveringsdatum DATE NOT NULL, Ägare INTEGER NOT NULL REFERENCES Användare(Nummer)); CREATE TABLE Tandborstningar (Nummer INTEGER NOT NULL PRIMARY KEY, Datum DATE NOT NULL, Starttid TIME NOT NULL, Längd INTEGER NOT NULL, Tandborste INTEGER NOT NULL REFERENCES Tandborstar(Nummer)); INSERT INTO Användare (Nummer, Namn, Epostadress, Lösenord) VALUES (1, 'Padrone', 'tpy@oru.se', 'banan'); INSERT INTO Användare (Nummer, Namn, Epostadress, Lösenord) VALUES (2, 'Lars', 'lars@oru.se', 'banan'); INSERT INTO Användare (Nummer, Namn, Epostadress, Lösenord) VALUES (3, 'Kalle', 'kalle@anka.co', 'anka'); INSERT INTO Modeller (Nummer, Namn) VALUES (1, 'Dentomatic 1000'); INSERT INTO Modeller (Nummer, Namn) VALUES (2, 'Ultra-Denter'); INSERT INTO Modeller (Nummer, Namn) VALUES (3, 'Turbo-Hacker'); INSERT INTO Tandborstar (Nummer, Modell, Aktiveringsdatum, Ägare) VALUES (1, 1, DATE '2019-12-24', 1); INSERT INTO Tandborstar (Nummer, Modell, Aktiveringsdatum, Ägare) VALUES (2, 1, DATE '2019-12-24', 1); INSERT INTO Tandborstar (Nummer, Modell, Aktiveringsdatum, Ägare) VALUES (3, 2, DATE '2019-12-24', 1); INSERT INTO Tandborstar (Nummer, Modell, Aktiveringsdatum, Ägare) VALUES (4, 1, DATE '2019-12-24', 2); INSERT INTO Tandborstar (Nummer, Modell, Aktiveringsdatum, Ägare) VALUES (5, 2, DATE '2019-12-24', 3); INSERT INTO Tandborstningar (Nummer, Datum, Starttid, Längd, Tandborste) VALUES (1, DATE '2022-08-20', TIME '07:00:00', 120, 1); INSERT INTO Tandborstningar (Nummer, Datum, Starttid, Längd, Tandborste) VALUES (2, DATE '2022-08-20', TIME '22:00:00', 120, 2); INSERT INTO Tandborstningar (Nummer, Datum, Starttid, Längd, Tandborste) VALUES (3, DATE '2022-08-21', TIME '07:00:00', 17, 1); INSERT INTO Tandborstningar (Nummer, Datum, Starttid, Längd, Tandborste) VALUES (4, DATE '2022-08-20', TIME '07:00:00', 120, 4); INSERT INTO Tandborstningar (Nummer, Datum, Starttid, Längd, Tandborste) VALUES (5, DATE '2022-08-20', TIME '22:00:00', 120, 4); SELECT * FROM Användare; SELECT * FROM Modeller; SELECT * FROM Tandborstar; SELECT * FROM Tandborstningar; SELECT * FROM Användare, Tandborstningar, Tandborstar, Modeller WHERE Användare.Nummer = Tandborstar.Ägare AND Tandborstar.Modell = Modeller.Nummer AND Tandborstar.Nummer = Tandborstningar.Tandborste; -- Uppgift 3 (12 p) -- Formulera följande frågor i SQL. Definiera gärna vyer eller CTE:er om -- det underlättar, men skapa inte nya tabeller. -- a) (2p) Jag har ett användarkonto med användarnamnet Padrone. Jag har -- flera tandborstar av modellen Dentomatic 1000. Vad är serienumren på -- dessa tandborstar? SELECT Tandborstar.Nummer AS Serienummer FROM Användare, Tandborstar, Modeller WHERE Användare.Namn = 'Padrone' AND Användare.Nummer = Tandborstar.Ägare AND Tandborstar.Modell = Modeller.Nummer AND Modeller.Namn = 'Dentomatic 1000'; -- b) (2p) Igår (2022-08-21) borstade jag (som har användarnamnet -- Padrone) bara tänderna en gång. Hur länge borstade jag? SELECT Tandborstningar.Längd FROM Användare, Tandborstar, Tandborstningar WHERE Användare.Namn = 'Padrone' AND Användare.Nummer = Tandborstar.Ägare AND Tandborstar.Nummer = Tandborstningar.Tandborste AND Tandborstningar.Datum = DATE '2022-08-21'; -- c) (2p) Hur många användare med e-postadresser på Örebro universitet -- (dvs som slutar med @oru.se) finns det? SELECT COUNT(*) FROM Användare WHERE Epostadress LIKE '%@oru.se'; -- d) (3p) Vilken tandborstmodell är vanligast? Vi vill alltså veta -- namnet på den modell som det finns flest tandborstar av. WITH AntalPerModell AS (SELECT Modeller.Nummer, Modeller.Namn, COUNT(*) AS Antal FROM Tandborstar, Modeller WHERE Tandborstar.Modell = Modeller.Nummer GROUP BY Modeller.Nummer, Modeller.Namn) SELECT Namn FROM AntalPerModell WHERE Antal = (SELECT MAX(Antal) FROM AntalPerModell); -- e) (3p) Ibland glömmer någon att borsta tänderna. Vi ska skicka e-post -- och påminna dem. Vad är e-postadresserna till alla de användare som -- inte borstade tänderna igår (2022-08-21)? SELECT Epostadress FROM Användare WHERE Nummer NOT IN (SELECT Ägare FROM Tandborstar WHERE Nummer IN (SELECT Tandborste FROM Tandborstningar WHERE Datum = DATE '2022-08-21')); -- Uppgift 4 (4 p) -- -- Tabellen Modeller innehåller tjugo rader, och de andra tabellerna -- har många miljoner rader. Sökningen i delfråga 3a ovan, den om -- serienumren på mina Dentomatic 1000, körs ofta, men kanske med -- andra konstanter. Frågan tar för lång tid att köra. Vi ser att det inte -- finns några index i databasen, inte ens på nycklar. -- -- a) Vilka index bör man skapa för att den sökningen ska bli -- snabbare? Användare.Namn Användare.Nummer Modeller.Namn -- nja, väldigt liten tabell Modeller.Nummer -- nja, väldigt liten tabell Tandborstar.Modell -- kanske inte, pga låg selektivitet Tandborstar.Ägare -- b) Ge ett exempel på ett index som inte skulle få den sökningen att -- gå snabbare, och förklara varför det indexet inte hjälper. Tandborstar.Nummer -- används inte för att söka fram rader