SQL serveri käsklusi

CREATE PROCEDURE VanimadLapsed_proc
	@LasteArv int
AS
	IF (@LasteArv >0)
SELECT TOP (@LasteArv) lapsenimi, synniaasta 
FROM lapsed
ORDER BY synniaasta
	ELSE
		PRINT 'Lähteandmed päringu tegemiseks on vigased!'

exec VanimadLapsed_proc -3 ;

SELECT ROW_NUMBER() OVER (ORDER BY synniaasta) as nr, * FROM lapsed
  ORDER BY lapsenimi;

SELECT nr, lapsenimi FROM
(SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY synniaasta) as nr, *
   FROM lapsed
  ORDER BY lapsenimi) as tabel1
   WHERE nr % 2 =0;
-- vanuse järgi lapsed üle ühe

SELECT COUNT(*), synniaasta FROM lapsed GROUP BY synniaasta WITH ROLLUP;

SELECT COUNT(*), GROUPING(synniaasta), synniaasta 
  FROM lapsed GROUP BY synniaasta WITH ROLLUP;

SELECT COUNT(*), GROUPING(synniaasta), synniaasta 
  FROM lapsed GROUP BY synniaasta WITH CUBE;

SELECT * FROM autod

-- margi keskmisest vanemad
SELECT * FROM autod AS t1 WHERE aasta<
  (SELECT AVG(aasta) FROM autod AS t2 WHERE t2.mark=t1.mark)



SELECT * FROM autod WHERE aasta=(SELECT MIN(aasta) FROM autod);

SELECT * FROM autod AS a1 WHERE aasta=
   (SELECT MIN(aasta) FROM autod AS a2 WHERE a2.linnanr=a1.linnanr);

SELECT mark, linnanr, aasta FROM autod AS a1 WHERE aasta=
   (SELECT MIN(aasta) FROM autod AS a2 WHERE a2.linnanr=a1.linnanr)
  GROUP BY mark, linnanr, aasta;


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

CREATE NONCLUSTERED INDEX ix_auto_aasta
 ON autod(aasta);

SELECT MAX(aasta) FROM autod;




CREATE TRIGGER autolisamine
ON autod FOR INSERT AS
INSERT INTO autologi(regnr, toiming, mark, aasta, linnanr)
 SELECT regnr, 'lisamine', mark, aasta, linnanr 
 FROM inserted;

 CREATE TRIGGER automuutmine
 ON autod FOR UPDATE AS
INSERT INTO autologi(regnr, toiming, mark, aasta, linnanr)
 SELECT regnr, 'muutmine', mark, aasta, linnanr 
 FROM inserted;

  CREATE TRIGGER autokustutamine
 ON autod FOR DELETE AS
INSERT INTO autologi(regnr, toiming, mark, aasta, linnanr)
 SELECT regnr, 'kustutamine', mark, aasta, linnanr 
 FROM deleted;

 SELECT * FROM autologi WHERE regnr='923ABC';


 CREATE OR REPLACE PROCEDURE autoajalugu 
     @registrinr VARCHAR(50) AS
  SELECT * FROM autologi WHERE regnr=@registrinr
  ORDER BY aeg DESC;

EXEC autoajalugu '923ABC'
DROP PROCEDURE autoajalugu;
CREATE PROCEDURE autoajalugu @registrinr VARCHAR(50) AS
  SELECT * FROM autologi WHERE regnr=@registrinr
   ORDER BY aeg DESC;

CREATE PROCEDURE autoseisund @registrinr VARCHAR(50), 
   @uuritavaeg DATETIME AS
SELECT TOP 1 * from autologi WHERE aeg<@uuritavaeg 
  ORDER BY aeg DESC;

EXEC autoseisund '923ABC', '2016-02-17 13:10'

CREATE PROCEDURE autoajalugu2 @registrinr VARCHAR(50) AS
DECLARE @uregnr VARCHAR(50)
DECLARE @uaeg DATETIME
DECLARE @getid CURSOR
SET @getid = CURSOR FOR
  SELECT regnr, aeg FROM autologi WHERE regnr=@registrinr
   ORDER BY aeg DESC

OPEN @getid
FETCH NEXT
FROM @getid  INTO @uregnr, @uaeg
WHILE @@FETCH_STATUS = 0
BEGIN
  print @uregn
  FETCH NEXT
   FROM @getid  INTO @uregnr, @uaeg
END

EXEC autoajalugu2 '923ABC'

CREATE TABLE [dbo].[failipuu]
(
	[id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [nimetus] NVARCHAR(50) NOT NULL, 
    [ylemkataloogi_id] INT NULL,
	FOREIGN KEY(ylemkataloogi_id) REFERENCES failipuu(id)
)

WITH allikas AS (
  SELECT nimetus, ylemkataloogi_id FROM failipuu 
    WHERE id=7
)
SELECT * FROM allikas;


WITH allikas AS (
  SELECT nimetus, ylemkataloogi_id FROM failipuu  WHERE id=7
  UNION ALL
  SELECT failipuu.nimetus, failipuu.ylemkataloogi_id FROM failipuu 
      JOIN allikas ON failipuu.id=allikas.ylemkataloogi_id
)
SELECT * FROM allikas;

nimetus	ylemkataloogi_id
kooliasjad	4
mati	3
kasutajad	1
C:	NULL

-- Koostage salvestatud protseduur, mis kuvaks etteantud kataloogi pika otsingutee:
Exec otsingutee 7
c:\kasutajad\mati\kooliasjad


CREATE FUNCTION dbo.StripWWWandCom (@input VARCHAR(250))
RETURNS VARCHAR(250)
AS BEGIN
    DECLARE @Work VARCHAR(250)

    SET @Work = @Input

    SET @Work = REPLACE(@Work, 'www.', '')
    SET @Work = REPLACE(@Work, '.com', '')

    RETURN @work
END

SELECT dbo.StripWWWandCom('www.tlu.ee') 


DROP FUNCTION pikktee
CREATE FUNCTION pikktee (@algid INT)
RETURNS VARCHAR(250)
AS BEGIN
 DECLARE @getid CURSOR;
 DECLARE @vastus VARCHAR(250)
 DECLARE @abi VARCHAR(250)
 SET @getid = CURSOR FOR
  WITH allikas AS (
    SELECT nimetus, ylemkataloogi_id FROM failipuu  WHERE id=@algid
    UNION ALL
    SELECT failipuu.nimetus, failipuu.ylemkataloogi_id FROM failipuu 
      JOIN allikas ON failipuu.id=allikas.ylemkataloogi_id
    )
	SELECT nimetus FROM allikas;
    OPEN @getid
	SET @vastus=''
    FETCH NEXT FROM @getid INTO @abi
    WHILE @@FETCH_STATUS = 0
    BEGIN 
	   SET @vastus=@abi+'\'+@vastus
       FETCH NEXT FROM @getid INTO @abi
    END
    RETURN @vastus
END

SELECT dbo.pikktee(7)
C:\kasutajad\mati\kooliasjad\

SELECT id, nimetus, dbo.pikktee(id) FROM failipuu;
1	C:	C:\
2	temp	C:\temp\
3	kasutajad	C:\kasutajad\
4	mati	C:\kasutajad\mati\
5	kati	C:\kasutajad\kati\
7	kooliasjad	C:\kasutajad\mati\kooliasjad\

-- Kuva etteantud kataloogi kõik alluvad 
-- Kuva etteantud kataloogi alluvate arv

WITH allikas AS (
  SELECT id, nimetus FROM failipuu  WHERE id=3
  UNION ALL
  SELECT failipuu.id, failipuu.nimetus FROM failipuu 
      JOIN allikas ON failipuu.ylemkataloogi_id=allikas.id
)
SELECT * FROM allikas;



WITH allikas AS (
  SELECT id, nimetus FROM failipuu  WHERE id=3
  UNION ALL
  SELECT failipuu.id, failipuu.nimetus FROM failipuu 
      JOIN allikas ON failipuu.ylemkataloogi_id=allikas.id
)
SELECT COUNT(*) FROM allikas;