Sql_Lab_4

USE Magazin_de_Antichitati
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_RulariTesteTabele_Tabele]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [RulariTesteTabele] DROP CONSTRAINT [FK_RulariTesteTabele_Tabele]

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_TesteTabele_Tabele]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [TesteTabele] DROP CONSTRAINT [FK_TesteTabele_Tabele]

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_RulariTesteTabele_RulariTeste]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [RulariTesteTabele] DROP CONSTRAINT FK_RulariTesteTabele_RulariTeste

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_RulariTesteViewuri_RulariTeste]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [RulariTesteViewuri] DROP CONSTRAINT FK_RulariTesteViewuri_RulariTeste

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_TesteTabele_Teste]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [TesteTabele] DROP CONSTRAINT FK_TesteTabele_Teste

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_TesteViewuri_Teste]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [TesteViewuri] DROP CONSTRAINT FK_TesteViewuri_Teste

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_RulariTesteViewuri_Viewuri]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [RulariTesteViewuri] DROP CONSTRAINT FK_RulariTesteViewuri_Viewuri

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[FK_TesteViewuri_Viewuri]') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [TesteViewuri] DROP CONSTRAINT FK_TesteViewuri_Viewuri

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[Tabele]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [Tabele]

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[RulariTesteTabele]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [RulariTesteTabele]

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[RulariTesteViewuri]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [RulariTesteViewuri]

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[RulariTeste]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [RulariTeste]

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[TesteTabele]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [TesteTabele]

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[TesteViewuri]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [TesteViewuri]

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[Teste]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [Teste]

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[Viewuri]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [Viewuri]

GO

CREATE TABLE [Tabele] (

    [CodTabel] [INT] IDENTITY (1, 1) NOT NULL ,

    [Nume] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]

GO

CREATE TABLE [RulariTesteTabele] (

    [CodRulareTest] [INT] NOT NULL ,

    [CodTabel] [INT] NOT NULL ,

    [IncepeLa] [datetime] NOT NULL ,

    [SeIncheieLa] [datetime] NOT NULL

) ON [PRIMARY]

GO

CREATE TABLE [RulariTesteViewuri] (

    [CodRulareTest] [INT] NOT NULL ,

    [CodView] [INT] NOT NULL ,

    [IncepeLa] [datetime] NOT NULL ,

    [SeIncheieLa] [datetime] NOT NULL

) ON [PRIMARY]

GO

CREATE TABLE [RulariTeste] (

    [CodRulareTest] [INT] IDENTITY (1, 1) NOT NULL ,

    [Descriere] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [IncepeLa] [datetime] NULL ,

    [SeIncheieLa] [datetime] NULL

) ON [PRIMARY]

GO

CREATE TABLE [TesteTabele] (

    [CodTest] [INT] NOT NULL ,

    [CodTabel] [INT] NOT NULL ,

    [NrRanduri] [INT] NOT NULL ,

    [Pozitie] [INT] NOT NULL

) ON [PRIMARY]

GO

CREATE TABLE [TesteViewuri] (

    [CodTest] [INT] NOT NULL ,

    [CodView] [INT] NOT NULL

) ON [PRIMARY]

GO

CREATE TABLE [Teste] (

    [CodTest] [INT] IDENTITY (1, 1) NOT NULL ,

    [Nume] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]

GO

CREATE TABLE [Viewuri] (

    [CodView] [INT] IDENTITY (1, 1) NOT NULL ,

    [Nume] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY]

GO

ALTER TABLE [Tabele] WITH NOCHECK ADD

    CONSTRAINT [PK_Tabele] PRIMARY KEY  CLUSTERED

    (

        [CodTabel]

    )  ON [PRIMARY]

GO

ALTER TABLE [RulariTesteTabele] WITH NOCHECK ADD

    CONSTRAINT [PK_RulariTesteTabele] PRIMARY KEY  CLUSTERED

    (

        [CodRulareTest],

        [CodTabel]

    )  ON [PRIMARY]

GO

ALTER TABLE [RulariTesteViewuri] WITH NOCHECK ADD

    CONSTRAINT [PK_RulariTesteViewuri] PRIMARY KEY  CLUSTERED

    (

        [CodRulareTest],

        [CodView]

    )  ON [PRIMARY]

GO

ALTER TABLE [RulariTeste] WITH NOCHECK ADD

    CONSTRAINT [PK_RulariTeste] PRIMARY KEY  CLUSTERED

    (

        [CodRulareTest]

    )  ON [PRIMARY]

GO

ALTER TABLE [TesteTabele] WITH NOCHECK ADD

    CONSTRAINT [PK_TesteTabele] PRIMARY KEY  CLUSTERED

    (

        [CodTest],

        [CodTabel]

    )  ON [PRIMARY]

GO

ALTER TABLE [TesteViewuri] WITH NOCHECK ADD

    CONSTRAINT [PK_TesteViewuri] PRIMARY KEY  CLUSTERED

    (

        [CodTest],

        [CodView]

    )  ON [PRIMARY]

GO

ALTER TABLE [Teste] WITH NOCHECK ADD

    CONSTRAINT [PK_Teste] PRIMARY KEY  CLUSTERED

    (

        [CodTest]

    )  ON [PRIMARY]

GO

ALTER TABLE [Viewuri] WITH NOCHECK ADD

    CONSTRAINT [PK_Viewuri] PRIMARY KEY  CLUSTERED

    (

        [CodView]

    )  ON [PRIMARY]

GO

ALTER TABLE [RulariTesteTabele] ADD

    CONSTRAINT [FK_RulariTesteTabele_Tabele] FOREIGN KEY

    (

        [CodTabel]

    ) REFERENCES [Tabele] (

        [CodTabel]

    ) ON DELETE CASCADE  ON UPDATE CASCADE ,

    CONSTRAINT [FK_RulariTesteTabele_RulariTeste] FOREIGN KEY

    (

        [CodRulareTest]

    ) REFERENCES [RulariTeste] (

        [CodRulareTest]

    ) ON DELETE CASCADE  ON UPDATE CASCADE

GO

ALTER TABLE [RulariTesteViewuri] ADD

    CONSTRAINT [FK_RulariTesteViewuri_RulariTeste] FOREIGN KEY

    (

        [CodRulareTest]

    ) REFERENCES [RulariTeste] (

        [CodRulareTest]

    ) ON DELETE CASCADE  ON UPDATE CASCADE ,

    CONSTRAINT [FK_RulariTesteViewuri_Viewuri] FOREIGN KEY

    (

        [CodView]

    ) REFERENCES [Viewuri] (

        [CodView]

    ) ON DELETE CASCADE  ON UPDATE CASCADE

GO

ALTER TABLE [TesteTabele] ADD

    CONSTRAINT [FK_TesteTabele_Tabele] FOREIGN KEY

    (

        [CodTabel]

    ) REFERENCES [Tabele] (

        [CodTabel]

    ) ON DELETE CASCADE  ON UPDATE CASCADE ,

    CONSTRAINT [FK_TesteTabele_Teste] FOREIGN KEY

    (

        [CodTest]

    ) REFERENCES [Teste] (

        [CodTest]

    ) ON DELETE CASCADE  ON UPDATE CASCADE

GO

ALTER TABLE [TesteViewuri] ADD

    CONSTRAINT [FK_TesteViewuri_Teste] FOREIGN KEY

    (

        [CodTest]

    ) REFERENCES [Teste] (

        [CodTest]

    ),

    CONSTRAINT [FK_TesteViewuri_Viewuri] FOREIGN KEY

    (

        [CodView]

    ) REFERENCES [Viewuri] (

        [CodView]

    )

GO

INSERT Tabele
    VALUES ('Sursa'),('Achizitie'),('Antichitate')
GO

INSERT Viewuri
    VALUES ('View_1Tabel'),('View_2Tabele'),('View_2Tabele_GroupBy')
GO

INSERT Teste
    VALUES ('view_1'),('view_2'),('view_3'),('add_sursa'),('add_antichitate'),('add_achizitie'),('delete_achizitie'),('delete_antichitate'),('delete_sursa')
GO

INSERT TesteViewuri
    VALUES (1,1),(2,2),(3,3)
GO

INSERT TesteTabele
    VALUES (7,2,10000,1),(8,3,10000,2),(9,1,10000,3),(4,1,10000,4),(6,3,10000,5),(5,2,10000,6)
GO

--PROCEDURI
--CREATE PROCEDURE view_1 AS BEGIN
--  SELECT * FROM View_1Tabel
--END
--GO

--CREATE PROCEDURE view_2 AS BEGIN
--  SELECT * FROM View_2Tabele
--END
--GO

--CREATE PROCEDURE view_3 AS BEGIN
--  SELECT * FROM View_2Tabele_GroupBy
--END
--GO

--CREATE PROCEDURE add_sursa AS BEGIN
--  DECLARE @count INT = 10000
--  WHILE @count <= 110000 BEGIN
--      INSERT Sursa
--          VALUES (@count,'nume_' + CAST(@count AS VARCHAR(30)),'prenume_' + CAST(@count AS VARCHAR(30)))
--      SET @count = @count + 1
--  END
--END
--GO

--CREATE PROCEDURE add_antichitate AS BEGIN
--  DECLARE @count INT = 10000
--  WHILE @count <= 110000 BEGIN
--      INSERT Antichitate
--          VALUES (@count,'antichitatea_' + CAST(@count AS VARCHAR(30)),1,1,1)
--      SET @count = @count + 1
--  END
--END
--GO

--CREATE PROCEDURE add_achizitie AS BEGIN
--  DECLARE @count INT = 10000
--  WHILE @count <= 110000 BEGIN
--      INSERT Achizitie
--          VALUES (@count,@count,'2017-12-12',1)
--      SET @count = @count + 1
--  END
--END
--GO

--CREATE PROCEDURE delete_achizitie AS BEGIN
--  DELETE FROM Achizitie
--      WHERE CodA > 9999
--END
--GO

--CREATE PROCEDURE delete_antichitate AS BEGIN
--  DELETE FROM Antichitate
--      WHERE CodA > 9999
--END
--GO

--CREATE PROCEDURE delete_sursa AS BEGIN
--  DELETE FROM Sursa
--      WHERE CodS > 9999
--END
--GO

--CREATE PROCEDURE run_achizitie AS BEGIN
--  DECLARE @data1 DATETIME, @data2 DATETIME, @data3 DATETIME, @data4 DATETIME
--  SET @data1 = GETDATE()
--  EXEC delete_achizitie
--  EXEC add_achizitie
--  SET @data2 = GETDATE()

--  SET @data3 = GETDATE()
--  EXEC view_2
--  SET @data4 = GETDATE()

--  INSERT RulariTeste
--      VALUES ('tabel_2 + view_2',@data1,@data4)
--  INSERT RulariTesteTabele
--      VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),2,@data1,@data2)
--  INSERT RulariTesteViewuri
--      VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),2,@data3,@data4)
--END
--GO

--CREATE PROCEDURE run_antichitate AS BEGIN
--  EXEC delete_achizitie

--  DECLARE @data1 DATETIME, @data2 DATETIME, @data3 DATETIME, @data4 DATETIME
--  SET @data1 = GETDATE()
--  EXEC delete_antichitate
--  EXEC add_antichitate
--  SET @data2 = GETDATE()

--  SET @data3 = GETDATE()
--  EXEC view_3
--  SET @data4 = GETDATE()


--  INSERT RulariTeste
--      VALUES ('tabel_3 + view_3',@data1,@data4)
--  INSERT RulariTesteTabele
--      VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),3,@data1,@data2)
--  INSERT RulariTesteViewuri
--      VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),3,@data3,@data4)

--  EXEC add_achizitie
--END
--GO

--CREATE PROCEDURE run_sursa AS BEGIN
--  EXEC delete_achizitie

--  DECLARE @data1 DATETIME, @data2 DATETIME, @data3 DATETIME, @data4 DATETIME
--  SET @data1 = GETDATE()
--  EXEC delete_sursa
--  EXEC add_sursa
--  SET @data2 = GETDATE()


--  SET @data3 = GETDATE()
--  EXEC view_1
--  SET @data4 = GETDATE()


--  INSERT RulariTeste
--      VALUES ('tabel_3 + view_1',@data1,@data4)
--  INSERT RulariTesteTabele
--      VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),1,@data1,@data2)
--  INSERT RulariTesteViewuri
--      VALUES ((SELECT MAX(CodRulareTest) FROM RulariTeste),1,@data3,@data4)
--  EXEC add_achizitie
--END
--GO

--CREATE PROCEDURE run_all AS BEGIN
--  EXEC run_achizitie
--  EXEC run_antichitate
--  EXEC run_sursa

--  SELECT * FROM RulariTeste
--END
--GO
--EXEC add_sursa
--EXEC add_antichitate
--EXEC add_achizitie

--EXEC delete_achizitie
--EXEC delete_antichitate
--EXEC delete_sursa

EXEC run_all