SELECT * FROM autod WHERE regnr LIKE '%A%';
SELECT mark, COUNT(*) FROM autod GROUP BY mark;
SELECT mark, aasta,  COUNT(*) FROM autod GROUP BY mark, aasta;
SELECT mark, AVG(aasta) FROM autod GROUP BY mark;
SELECT mark, MAX(aasta)-MIN(aasta) FROM autod GROUP BY mark;
SELECT mark, COUNT(*) FROM autod GROUP BY mark WITH ROLLUP;

SELECT synniaasta FROM lapsed
UNION ALL
SELECT aasta FROM autod;

ALTER TABLE autod ADD linn VARCHAR(50) DEFAULT 'Tallinn';
ALTER TABLE autod ADD FOREIGN KEY(linn) REFERENCES linnad(linnanimi);

SELECT linnanimi, COUNT(regnr) FROM linnad 
  LEFT JOIN autod ON autod.linn=linnad.linnanimi
  GROUP BY linnanimi;

SELECT * FROM autod WHERE aasta<(SELECT AVG(aasta) FROM autod);

SELECT * FROM autod as a1 WHERE aasta<=
  (SELECT AVG(aasta) FROM autod as a2
    WHERE a1.mark=a2.mark);

SELECT * FROM autod as a1 WHERE aasta=
  (SELECT MIN(aasta) FROM autod as a2
    WHERE a1.linn=a2.linn);

ALTER TABLE autod 
  ADD CONSTRAINT aastakontroll 
    CHECK( aasta BETWEEN 1900 AND YEAR(GETDATE()));

CREATE NONCLUSTERED INDEX eesnimeindeks ON lapsed(eesnimi);
DROP TRIGGER autolisamine;
CREATE TRIGGER autolisamine 
ON autod
FOR INSERT 
AS
INSERT INTO muudatused (regnr, toiming, mark, aasta)
SELECT regnr, 'lisati', mark, aasta
FROM inserted;

CREATE TRIGGER automuutmine 
ON autod
FOR UPDATE 
AS
INSERT INTO muudatused (regnr, toiming, mark, aasta)
SELECT regnr, 'muudeti', mark, aasta
FROM deleted;
DROP PROCEDURE arvujada;
CREATE PROCEDURE arvujada AS
DECLARE @i INT, @s as VARCHAR(max)
SET @i=1
SET @s=''
WHILE(@i<=10) BEGIN
  IF (@i>1) BEGIN
     SET @s=@s+','
  END
  SET @s=@s+str(@i)
  SET @i=@i+1
  --PRINT @i
END
SELECT @s as tulemus

exec arvujada;

DROP PROCEDURE autoandmed;
CREATE PROCEDURE autoandmed @rnr VARCHAR(50)
AS
SELECT * FROM muudatused WHERE regnr=@rnr;

exec autoandmed '889BBR' ;

CREATE PROCEDURE paringuanalyys AS
DECLARE @kid CURSOR
DECLARE @taeg DATETIME
DECLARE @taasta INT
SET @kid= CURSOR FOR
  SELECT aeg, aasta FROM muudatused WHERE regnr='884AAB';
OPEN @kid
FETCH NEXT
FROM @kid INTO @taeg, @taasta
WHILE @@FETCH_STATUS=0
BEGIN
    PRINT @taasta
	FETCH NEXT
    FROM @kid INTO @taeg, @taasta
END

exec paringuanalyys