30 maj 2014 kl 17:42
USE sqlmaint
IF OBJECT_ID('sko_storlekar') IS NOT NULL DROP TABLE sko_storlekar
GO
CREATE TABLE sko_storlekar(
tillverkare varchar(20) NOT NULL
,modell varchar(30) NOT NULL
,typ varchar(15) NOT NULL
,passform tinyint NOT NULL -- 0 = normal, -1 = lite liten, +1 = lite stor
,eu decimal(3,1) NOT NULL
,us decimal(3,1) NOT NULL
,uk decimal(3,1) NOT NULL
,cm decimal(3,1) NULL
,CONSTRAINT PK_sko_storlekar PRIMARY KEY(tillverkare, modell)
,CONSTRAINT CK_sko_storlekar_typ CHECK (typ IN('Asfalt', 'Terräng', 'Metalldubbade'))
)
GO
INSERT INTO sko_storlekar(tillverkare, modell, typ, passform, eu, us, uk, cm)
VALUES
('Adidas', 'Hagio 2', 'Asfalt', 0, 43.3, 9.5, 9, 27.5)
--,('Asics', 'Piranha SP4', 'Asfalt', 0, 43, 9, 9, 27) --För stor, fimpar
,('Brooks', 'Green Silence', 'Asfalt', 0, 43, 9.5, 8.5, 27.5)
,('Brooks', 'PureConnect', 'Asfalt', 0, 43, 9.5, 8.5, 27.5)
,('Inov-8', 'Road-X 233', 'Asfalt', 0, 42.5, 9.5, 8.5, 26.5)
,('Merrell ', 'Road Glove', 'Asfalt', 0, 43, 9, 8.5, NULL)
,('Nike ', 'Hyperfeel', 'Asfalt', 0, 42.5, 9, 8, 27)
,('Nike ', 'Lunaracer', 'Asfalt', 0, 43, 9.5, 8.5, 27.5)
,('Saucony ', 'Kinvara 2', 'Asfalt', 0, 43, 9.5, 8.5, NULL)
,('Skechers ', 'GoMeb Speed 2', 'Asfalt', 0, 43, 9.5, 8.5, 27.5)
,('Brooks', 'PureGrit', 'Terräng', 0, 42.5, 9, 8, 27)
,('Icebug ', 'Acceleritas3', 'Terräng', 1, 42.5, 9.5, 8.5, 27.5)
,('Inov-8', 'X-Talon 212', 'Terräng', -1, 42, 9, 8, 26)
,('Inov-8', 'Terrafly 303', 'Terräng', 0, 42.5, 9.5, 8.5, 26.5)
,('Merrell', 'Trail Glove', 'Terräng', 0, 43, 9, 8.5, NULL)
,('New Balance', 'MT 110', 'Terräng', 0, 43, 9.5, 9, 27.5)
,('Salomon ', 'Sense Ultra', 'Terräng', 0, 42.7, 9, 8.5, 27)
,('Skechers ', 'GoBionic Trail', 'Terräng', 0, 42.5, 9, 8, 27)
,('Icebug ', 'Anima', 'Metalldubbade', 0, 42.5, 9.5, 8.5, 27.5)
,('Inov-8', 'Oroc 280', 'Metalldubbade', -1, 42, 9, 8, 26)
,('VJ ', 'Integrator', 'Metalldubbade', 0, 42.5, 9.5, 8.5, NULL)
SELECT * FROM sko_storlekar
SELECT
AVG(eu) AS eu
, AVG(us) AS us
, AVG(uk) AS uk
, AVG(cm) AS cm
, STDEVP(eu) AS eu_var
, STDEVP(us) AS us_var
, STDEVP(uk) AS uk_var
, STDEVP(cm) AS cm_var
FROM sko_storlekar