CREATE TABLE autod(
  id INT IDENTITY NOT NULL PRIMARY KEY,
  mark VARCHAR(20),
  regnr CHAR(6), 
  aasta INT
)

SELECT * FROM autod ORDER BY aasta DESC

SELECT DISTINCT mark FROM autod

SELECT regnr, SUBSTRING(regnr, 4, 3) FROM autod WHERE aasta < 1993
ORDER BY SUBSTRING(regnr, 4, 3),   regnr

SELECT MIN(aasta) FROM autod

UPDATE autod SET regnr='114GRA' 
WHERE id=1

DELETE FROM autod
WHERE id=2

SELECT * FROM autod

INSERT INTO autod (mark, regnr, aasta)
VALUES('Ziguli', '789JKJ', 1989)

SELECT RIGHT(regnr, 3) as Tähed FROM autod

SELECT *,newid() as uus FROM autod ORDER BY uus

SELECT TOP 1 regnr FROM autod ORDER BY newid()

SELECT COUNT(*) as hulk, synniaasta 
FROM lapsed
GROUP BY synniaasta
HAVING COUNT(*)>1

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
END
SELECT @s as tulemus


CREATE TABLE lemmikloomad(
  id INT identity PRIMARY KEY,
  loomanimi VARCHAR(50),
  peremehe_id INT,
  FOREIGN KEY (peremehe_id) REFERENCES lapsed(id)
)

ALTER TABLE lemmikloomad
ADD aasta INT

ALTER TABLE lemmikloomad
DROP COLUMN aasta

ALTER TABLE lemmikloomad
RENAME COLUMN aasta TO synniaasta

SELECT * FROM autod

CREATE TABLE maakonnad(
   id INT IDENTITY PRIMARY KEY,
   maakonnanimi VARCHAR(20)
)

INSERT INTO maakonnad(maakonnanimi)
VALUES ('Võrumaa')

ALTER TABLE autod
ADD maakonna_id INT REFERENCES maakonnad(id)


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

SELECT * FROM autod as tabel1
JOIN
(SELECT AVG(aasta) as keskmine, mark FROM autod
GROUP BY mark) as tabel2
ON tabel1.mark=tabel2.mark 
WHERE tabel1.aasta>tabel2.keskmine

SELECT * FROM autod as tabel1 
WHERE tabel1.aasta<
(SELECT AVG(aasta) as keskmine FROM autod as tabel2
 WHERE tabel1.mark=tabel2.mark)