USE baas1

SELECT * FROM koerad

SELECT * FROM koerad ORDER BY toug DESC

SELECT toug FROM koerad WHERE kogus>7  --koerad, kelle tõust on kohal rohekm kui seitse

SELECT * FROM koerad WHERE toug LIKE 'T%'

SELECT * FROM koerad WHERE toug LIKE '%[uvk]%'

INSERT INTO koerad (toug, kogus) VALUES ('Kolli', 8)

UPDATE koerad SET kogus=6 WHERE id=5

UPDATE koerad SET kogus=kogus+1 WHERE id=5;

CREATE TABLE lapsed(
  id int identity NOT NULL PRIMARY KEY
  ,eesnimi VARCHAR(50)
  ,pikkus int
  ,synniaasta int
)


INSERT INTO lapsed (eesnimi, pikkus, synniaasta) VALUES
('Sass', 165, 1998)

SELECT DISTINCT synniaasta FROM lapsed

SELECT  synniaasta FROM lapsed

CREATE TABLE #abitabel2(kogus int);

insert into #abitabel2
SELECT count(distinct synniaasta) as kogus FROM lapsed WHERE synniaasta<1997

SELECT * FROM #abitabel2

SELECT * FROM lapsed

ALTER TABLE lapsed ADD isikukood CHAR(6)

UPDATE lapsed SET isikukood='abc' WHERE id=3

ALTER TABLE lapsed DROP COLUMN isikukood;

SELECT TOP 2 WITH TIES * FROM lapsed ORDER BY synniaasta 

select LEN('tere')

SELECT COUNT(*) as mitu, LEN(eesnimi) as nimepikkus FROM lapsed
GROUP BY LEN(eesnimi)

select (round(avg(pikkus*1.0), 1)) from lapsed

SELECT 
  AVG(aasta) as keskmine
  --,max(aasta)-min(aasta) as vahe
  ,mark 
from autod
GROUP BY mark


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

SELECT * FROM lapsed

INSERT INTO lemmikloomad(loomanimi, peremehe_id)
VALUES ('Miisu', 4)

SELECT * FROM lemmikloomad

SELECT [lapsed].[id], eesnimi, loomanimi FROM lapsed
INNER JOIN lemmikloomad
ON lemmikloomad.peremehe_id=lapsed.id

SELECT * FROM lapsed, lemmikloomad
WHERE lemmikloomad.peremehe_id=lapsed.id
ORDER BY eesnimi


SELECT eesnimi FROM lapsed WHERE
lapsed.id not in (
  SELECT peremehe_id FROM lemmikloomad
)

SELECT eesnimi FROM (
  SELECT eesnimi, loomanimi FROM lapsed
  LEFT JOIN lemmikloomad 
    ON lemmikloomad.peremehe_id=lapsed.id
  ) as tabel1 WHERE
loomanimi is NULL


declare @eesnimi AS VARCHAR(50)
set @eesnimi='Sass'
SELECT eesnimi FROM lapsed
WHERE synniaasta=(
  SELECT synniaasta FROM lapsed
  WHERE eesnimi=@eesnimi
) AND eesnimi<>@eesnimi


ALTER TABLE lapsed ADD isikukood CHAR(11)

ALTER TABLE lapsed DROP COLUMN isikukood

ALTER TABLE lapsed ADD isikukood CHAR(11)

EXEC sp_rename 'lapsed.isikukood', 'ikood'

ALTER TABLE autod ADD maakonna_id INT --lisab tulba

-- lisab seose
ALTER TABLE autod WITH CHECK ADD 
FOREIGN KEY (maakonna_id) REFERENCES maakonnad(id)

select * from lapsed where synniaasta is not null

delete from lapsed where id=7

ALTER TABLE lapsed WITH CHECK 
ADD UNIQUE(eesnimi)

SELECT * FROM lapsed;

SELECT synniaasta FROM lapsed
UNION ALL
SELECT synniaasta FROM lemmikloomad

SELECT * FROM autod JOIN maakonnad ON
autod.maakonna_id=maakonnad.id

SELECT * FROM autod, maakonnad WHERE
autod.maakonna_id=maakonnad.id

SELECT count(*), maakonnanimi FROM autod, maakonnad WHERE
autod.maakonna_id=maakonnad.id
group by maakonnanimi

SELECT count(autod.id), maakonnanimi FROM  maakonnad 
LEFT JOIN autod ON
autod.maakonna_id=maakonnad.id
group by maakonnanimi

SELECT distinct mark FROM autod WHERE aasta < 1993m 
UNION ALL
SELECT distinct maakonnanimi FROM maakonnad JOIN autod ON
   autod.maakonna_id=maakonnad.id


CREATE TABLE autod(
   id int identity not null primary key,
   mark varchar(30),
   aasta int
)


CREATE TABLE maakonnad(
  id int identity not null primary key,
  maakonnanimi varchar(30)
)


INSERT INTO lapsed (eesnimi) VALUES ('Juku')


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

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


SELECT * FROM autod


-- maakonnaid, vähim aasta selles maakonnas
use baas1


SELECT tabel2.aasta, tabel2.mark, maakonnanimi FROM
(SELECT min(aasta) as maasta, maakonna_id FROM autod
GROUP BY maakonna_id) as tabel1 JOIN
(SELECT aasta, maakonna_id, mark FROM autod) as tabel2
ON tabel1.maasta=tabel2.aasta AND 
   tabel1.maakonna_id=tabel2.maakonna_id JOIN maakonnad
ON tabel1.maakonna_id=maakonnad.id


SELECT aasta, mark, maakonnanimi FROM
(SELECT min(aasta) as maasta, maakonna_id FROM autod
GROUP BY maakonna_id) as tabel1 JOIN autod
ON tabel1.maasta=autod.aasta AND 
   tabel1.maakonna_id=autod.maakonna_id JOIN maakonnad
ON tabel1.maakonna_id=maakonnad.id



SELECT tabel2.aasta, tabel2.mark, maakonnad.maakonnanimi FROM
(SELECT min(aasta) as maasta, maakonna_id FROM autod
GROUP BY maakonna_id) as tabel1, 
(SELECT aasta, maakonna_id, mark FROM autod) as tabel2,
maakonnad WHERE
tabel1.maasta=tabel2.aasta AND 
tabel1.maakonna_id=tabel2.maakonna_id AND
tabel1.maakonna_id=maakonnad.id


SELECT aasta, mark, maakonnanimi FROM
(SELECT min(aasta) as maasta, maakonna_id FROM autod
GROUP BY maakonna_id) as tabel1, autod, maakonnad WHERE
tabel1.maasta=autod.aasta AND 
tabel1.maakonna_id=autod.maakonna_id AND
tabel1.maakonna_id=maakonnad.id

-- Tabel1 näitab iga maakonna vanima auto väljalaskeaasta

INSERT INTO autod (mark, aasta, maakonna_id) VALUES 
('Zil', 1967, 1)

select * from sys.columns order by name
select * from sys.tables
select * from sys.databases

CREATE VIEW pikadlapsed AS
SELECT TOP 100 PERCENT eesnimi, pikkus FROM lapsed 
WHERE pikkus > 160 
ORDER BY eesnimi

select * from pikadlapsed

drop view pikadlapsed

CREATE VIEW autodmaakonnas AS
SELECT mark, aasta, maakonnanimi FROM autod, maakonnad
WHERE autod.maakonna_id=maakonnad.id

SELECT * FROM autodmaakonnas

DROP VIEW autodmaakonnas


CREATE VIEW autodearvmaakonnas AS
SELECT maakonnanimi, COUNT(autod.id) as autodearv 
FROM maakonnad LEFT JOIN autod  
ON autod.maakonna_id=maakonnad.id
GROUP BY maakonnanimi


CREATE VIEW paljuautosid AS
SELECT maakonnanimi FROM autodearvmaakonnas
WHERE autodearv>1

SELECT * FROM paljuautosid


SELECT maakonnanimi, autod.id
FROM maakonnad LEFT JOIN autod  
ON autod.maakonna_id=maakonnad.id

SELECT * FROM maakonnad

INSERT INTO autod (mark, aasta, maakonna_id) VALUES
('Kamaz', 1987, 3)

CREATE PROCEDURE kysiLapsed(
  @algaasta decimal = 1997
)
AS
SELECT eesnimi, synniaasta FROM lapsed 
WHERE synniaasta>=@algaasta

EXEC kysiLapsed 1967

DROP PROCEDURE kysiLapsed

CREATE PROCEDURE loeArvud AS
DECLARE @i AS 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

EXEC LoeArvud

DROP PROCEDURE loeArvud

DECLARE @crs CURSOR
SET @CRS = CURSOR FOR
SELECT eesnimi FROM lapsed

OPEN @crs
FETCH NEXT FROM @crs
FETCH NEXT FROM @crs
CLOSE @crs
DEALLOCATE @crs

DECLARE @kood as integer
DECLARE @nimetus as VARCHAR(20)
SELECT @kood=id, @nimetus=maakonnanimi FROM maakonnad 
WHERE maakonnanimi='Hiiumaa'
if @kood IS NOT NULL
  print str(@kood)+' '+@nimetus
else 
  raiserror('Maakond puudub', 1, 1);

select year(getdate())

CREATE PROCEDURE mituvana (@algaasta int) AS
SELECT COUNT(*) FROM autod WHERE
aasta<@algaasta


EXEC mituvana 1983

CREATE PROCEDURE LisaAuto1(@mark VARCHAR(20), @aasta int, 
       @maakonna_id int) AS
INSERT INTO autod (mark, aasta, maakonna_id) VALUES
 (@mark, @aasta, @maakonna_id)


Exec LisaAuto1 'John"s roller', 1988, 1


CREATE PROCEDURE LisaAuto2(@mark VARCHAR(20), @aasta int, 
       @maakonnanimi VARCHAR(20)) AS
DECLARE @kood as integer
SELECT @kood=id FROM maakonnad 
WHERE maakonnanimi=@maakonnanimi
INSERT INTO autod (mark, aasta, maakonna_id) VALUES
 (@mark, @aasta, @kood)

Exec LisaAuto2 'Moskvits', 1989, 'Hiiumaa'

select * from autod

CREATE PROCEDURE LisaAuto3(@mark VARCHAR(20), @aasta int, 
       @maakonnanimi VARCHAR(20)) AS
DECLARE @kood as integer
SELECT @kood=id FROM maakonnad 
WHERE maakonnanimi=@maakonnanimi
if @kood IS NOT NULL
 INSERT INTO autod (mark, aasta, maakonna_id) VALUES
    (@mark, @aasta, @kood)
else
 RAISERROR('Maakond puudub', 1, 1)


Exec LisaAuto3 'Moskvits', 1990, 'Kalamaa'

CREATE PROCEDURE LisaAuto4(@mark VARCHAR(20),
       @maakonnanimi VARCHAR(20), @aasta int=null) AS
DECLARE @kood as integer
IF @aasta IS NULL
 set @aasta=YEAR(GETDATE())
SELECT @kood=id FROM maakonnad 
WHERE maakonnanimi=@maakonnanimi
if @kood IS NOT NULL
 INSERT INTO autod (mark, aasta, maakonna_id) VALUES
    (@mark, @aasta, @kood)
else
 RAISERROR('Maakond puudub', 1, 1)

Exec LisaAuto4 'Moskvits', 'Hiiumaa', 2004


DECLARE @mkcur as CURSOR
DECLARE @acur as CURSOR
DECLARE @mkid as int
DECLARE @mknimi as VARCHAR(30)
DECLARE @margid as VARCHAR(255)
DECLARE @mark as VARCHAR(30)
SET @mkcur=CURSOR FOR
SELECT id, maakonnanimi FROM maakonnad
OPEN @mkcur
CREATE TABLE #abimargid(
   maakonnanimi VARCHAR(30),
   margiloetelu VARCHAR(255)
)
FETCH NEXT FROM @mkcur INTO @mkid, @mknimi
WHILE @@FETCH_STATUS<>-1 BEGIN
  SET @acur=CURSOR FOR
    SELECT DISTINCT mark FROM autod 
      WHERE maakonna_id=@mkid;
    OPEN @acur
    SET @margid=''
    FETCH NEXT FROM @acur INTO @mark
    WHILE @@FETCH_STATUS<>-1 BEGIN
       SET @margid=@margid+' '+@mark
       FETCH NEXT FROM @acur INTO @mark
    END
    CLOSE @acur
    INSERT INTO #abimargid 
      VALUES(@mknimi, @margid)
  FETCH NEXT FROM @mkcur INTO @mkid, @mknimi   
END
SELECT * FROM #abimargid
DROP TABLE #abimargid
CLOSE @mkcur
DEALLOCATE @mkcur
DEALLOCATE @acur

SELECT * FROM autodmaakonnas ORDER BY maakonnanimi