Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --a
- create table Postoffices
- (
- postNbr char(5),
- postOffice varchar(20),
- constraint pk_Postoffices primary key(postNbr)
- );
- create table Newspapers
- (
- newsPaper varchar(20),
- nType varchar(20),
- monthPrice int,
- constraint ck_monthPrice check (monthPrice between 5 and 100),
- constraint pk_Newspapers primary key(newsPaper)
- );
- create table Customers
- (
- customerNbr int,
- lastname varchar(30),
- firstname varchar(20),
- street varchar(30),
- postNbr char(5),
- constraint pk_Customers primary key(customerNbr),
- constraint fk_customersPostNbr foreign key(postNbr) references Postoffices(postNbr)
- );
- create table Subscriptions
- (
- customerNbr int,
- newsPaper varchar(20),
- months int,
- constraint ck_Subscriptions check(months between 1 and 12),
- constraint fk_customerNbr foreign key(customerNbr) references Customers(customerNbr),
- constraint fk_newsPaper foreign key(Newspaper) references Newspapers(newsPaper),
- constraint pk_Subscriptions primary key(customerNbr, newsPaper)
- );
- --b
- insert into Customers(customerNbr, lastname, firstname, street, postNbr) values
- ('1', 'Anka', 'Kalle', 'Ankgränd 2', '66000'),
- ('2', 'Anka', 'Kajsa', 'Ankgränd 3', '66000'),
- ('3', 'Pan', 'Peter', 'Barngatan 11', '11000'),
- ('4', 'Puh', 'Nalle', 'Honungsgatan 1', '55000'),
- ('5', 'Åsna', 'Ior', 'Ekgränd 9', '550000'),
- ('6', 'Kanin', 'Kalle', 'Ekgatan 2', '55000'),
- ('7', 'Ling', 'Tinge', 'Barngatan 11', '11000'),
- ('8', 'Lång', 'Ben', 'Ankgatan 3', '66000');
- insert into Postoffices values
- ('11000', 'INGENSTANS'),
- ('55000', 'SJUMILASKOGEN'),
- ('66000', 'ANKEBORG');
- select * from Postoffices
- select * from customers
- insert into Newspapers values
- ('Tietokone', 'fakta', '15'),
- ('Apu', 'familje', '10'),
- ('Seura', 'familje', '12'),
- ('Suosikki', 'ungdoms', '20'),
- ('MikroPC', 'fakta', '15'),
- ('Vasabladet', 'dags', '20'),
- ('Pohjalainen', 'dags', '22');
- insert into Subscriptions values
- ('1', 'Suosikki', '3'),
- ('1', 'Apu', '12'),
- ('2', 'Seura', '4'),
- ('2', 'Tietokone', '12'),
- ('2', 'Vasabladet', '12'),
- ('4', 'Vasabladet', '3'),
- ('4', 'Pohjalainen', '6'),
- ('5', 'Suosikki', '3'),
- ('5', 'Pohjalainen', '10'),
- ('6', 'Tietokone', '12'),
- ('6', 'Vasabladet', '3'),
- ('6', 'Pohjalainen', '12'),
- ('7', 'Vasabladet', '12'),
- ('8', 'Apu', '12'),
- ('8', 'Seura', '3'),
- ('8', 'Suosikki', '9');
- --c
- alter table dbo.Customers
- add phone char(15),
- constraint df_phone default null for phone;
- --d
- create index newspaperindex on dbo.Newspapers(nType);
- create index subsIndex on dbo.Subscriptions(customerNbr, newsPaper);
- --e
- update Customers set street = 'åsnestigen 5',
- postNbr = 11000 where customerNbr = 5;
- update Newspapers set monthPrice = monthPrice + 2 where nType in('familje', 'fakta');
- update subscriptions set months = 12 where customerNbr = 5 and newsPaper = 'Pohjalainen'
- --går inte att ändra till 'help'
- --f
- delete from Subscriptions where newsPaper = 'Apu';
- delete from Newspapers where newsPaper = 'Apu';
- select newspaper, ntype, monthprice from Newspapers order by monthPrice desc, newsPaper asc;
- --g
- select distinct Newspapers as 'tidning', monthprice as 'pris',
- monthprice * 5.95 as 'markpris' from newspaper;
- --h -->groupby och statistiska funktioner
- select newspaper, count(*) as 'antal'
- from Subscriptions
- group by newsPaper;
- --antalet prenumerationer per tidning
- select Newspaper, sum(months) as 'månader'
- from Subscriptions
- group by newsPaper;
- --totala antalet prenumerationer
- select count(*) as 'totalantal'
- from Subscriptions;
- --medelmånadspriset för alla tidningar
- select avg(monthprice) as 'medelpris'
- from Newspapers;
- --i -->selektion med where
- --namnet på de tidningar som hör till typen fakta eller familje
- select *
- from Newspapers
- where nType = 'fakta' or newsPaper = 'familje';
- select *
- from Newspaper
- where ntype like 'f%';
- select *
- from Newspapers
- where nType in ('fakta', 'familje');
- --namnet på alla kunder som bor på en gata
- select firstname, lastname
- from Customers
- where street like '%gata%';
- --namnet på alla Ankor och Kallar
- select lastname, firstname
- from Customers
- where firstname = 'Kalle' or lastname = 'Anka';
- --antalet Ankor
- select count(*) as 'antalet ankor'
- from Customers
- where lastname = 'Anka';
- --namnet på de tidningar som har minst 20 prenumerationsmånader
- select Newspaper, sum(months) as 'månader'
- from Subscriptions
- group by newsPaper
- having sum(months) > 20;
- --j -->join
- --efternamn, förnamn, gatuaddress, postnummer och anstalt för alla kunder sorterade
- --i stigande sekvens enligt postanstalt, efternamn och förnamn
- select Customers.firstname, Customers.lastname, Customers.street, Customers.postNbr,
- Postoffices.postOffice
- from Customers inner join Postoffices
- on Customers.postNbr = Postoffices.postNbr
- order by Postoffices.postOffice asc,
- Customers.lastname asc,
- Customers.firstname;
- --Antalet prenumerationer per tidning. Även tidningar som saknar prenumerationer bör tas med .
- SELECT Newspapers.newsPaper, COUNT(Subscriptions.newsPaper) AS 'antalet prenumerationer'
- FROM Newspapers LEFT OUTER JOIN Subscriptions
- ON Newspapers.newsPaper = Subscriptions.newsPaper
- GROUP BY Newspapers.newsPaper;
- --Totalinkomsten för alla prenumerationer tillsammans.
- SELECT SUM(Subscriptions.months*Newspapers.monthprice) AS 'totalinkomst'
- FROM Newspapers INNER JOIN Subscriptions
- ON Newspapers.newsPaper= Subscriptions.newsPaper;
- --k
- --Namnet på alla kunder som har samma postanstalt som Nalle Puh.
- SELECT firstname, lastname FROM Customers WHERE postnbr =
- (SELECT postnbr FROM Customers WHERE firstname='Nalle' AND lastname='Puh');
- --Namnet på den tidning som har det högsta månadspriset.
- SELECT newspaper FROM Newspapers WHERE monthprice = (SELECT MAX(monthprice) FROM Newspapers);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement