Untitled

USE Magazin_de_Antichitati
GO

--                          INSERT                          --

INSERT INTO Perioada_Istorica (CodP, Denumire, INTERVAL)
    VALUES  (1, 'Preistorie' , '10000 i.en.') ,
            (2, 'Antichitate' , '8000 i.en.') ,
            (3, 'Epoca Clasica' , '400 i.en.') ,
            (4, 'Evul Mediu' , '500 en.') ,
            (5, 'Renasterea' , '1400 en.') ,
            (6, 'Epoca industriala' , '1700 en.'),
            (7, 'Epoca moderna', '1900 en.')
GO

INSERT INTO Categorie
    VALUES (1, 'Bani obiecte de valoare'),
           (2, 'Mobilier'),
           (3, 'Bijuterii si accesorii'),
           (4, 'Ceasuri'),
           (5, 'Obiecte decorative'),
           (6, 'Colectii'),
           (7, 'Arta'),
           (8, 'Masinarii'),
           (9, 'Vesela'),
           (10, 'Colectii'),
           (11, 'Altele'),
           (11, 'Oale') -- Nu poate fi adaugat, cheia nu este unicat
GO

INSERT INTO Antichitate
    VALUES (1,'Lant ceremonial din pene',10000,1,3),
           (2,'Vas cupru',200,2,9),
           (3,'Farfurie de aur',8000,2,9),
           (4,'Bancnota de 2 lei',350,7,1),
           (5,'Parfum Christian Dior Miss Dior Paris',1750,7,2),
           (6,'Pat Neo Baroc',2650,6,2),
           (7,'Ceas De Perete Cu Pendula Hamburg',643,7,4),
           (8,'Hunga Munga',1500,4,11),
           (11,'Ceakovski Bizet',55,7,11),
           (12,'adwa',1231,111,11), -- Nu xista cheia CodP = 111
           (13,'adwa',1231,2,111231), -- Nu xista cheia CodCA = 111231
           (1,'afawfwa',1231,1,11) -- Cheia primara CodA este deja inserata
GO

INSERT INTO Antichitate(CodA, Pret, CodP, CodCa)
    VALUES (9,5000,3,11), (10,23,2,1)
GO

INSERT INTO Antichitate(Pret, CodP, CodCa) -- Nu se poat adauga elemente fara
    VALUES (5000,3,11)                     -- cheia primara
GO

INSERT INTO Achizitie
    VALUES (1,1,'01-08-2016',6000),
           (2,3,'01-02-2001',10),
           (3,2,'30-05-2011',2000),
           (4,4,'20-01-2010',120),
           (5,1,'13-08-1999',500),
           (6,10,'01-10-2016',1650),
           (7,10,'05-06-2017',300),
           (8,9,'08-03-2016',500),
           (9,5,'01-02-2012',3000),
           (10,7,'01-05-2016',5),
           (11,7,'01-02-2010',32),
           (22,7,'01-05-2016',-5), -- pretul nu poate fi negativ
           (5,33,'01-05-2016',5) -- nu exista distribuitorul
GO

--                          UPDATE                          --

----Achzitie----
UPDATE  Achizitie
    SET Suma = Suma * 5
    WHERE Suma <= 10

UPDATE Achizitie
    SET CodS = 3
    WHERE CodS BETWEEN 5 AND 7 OR CodS = 1

UPDATE Achizitie
    SET DATA = '02-03-2017'
    WHERE DATA LIKE '01%2016'

UPDATE Achizitie
    SET Suma = 222.54
    WHERE Suma IN (120,50,10) OR Suma IS NULL

----Antichitate----
UPDATE Antichitate
    SET Nume = 'in curs de identificare' , Pret = 0
    WHERE Nume IS NULL

UPDATE Antichitate
    SET Pret = Pret * 2
    WHERE CodP = 1 OR CodP = 2

UPDATE Antichitate
    SET Nume = 'in curs de identificare', Pret = 0
    WHERE Nume IS NULL OR Nume LIKE 'Bacnot'

UPDATE Antichitate
    SET CodCA = 3
    WHERE CodP IN (1,2,4) OR Pret BETWEEN 8000 AND 10000

---Perioada_Istorica----
UPDATE Perioada_Istorica
    SET INTERVAL = 'b.c.'
    WHERE INTERVAL LIKE '$i.en.$' OR INTERVAL IN ('Preistorie','Antichitate','Epoca Clasica')

UPDATE Perioada_Istorica
    SET Denumire = 'in curs de identificare'
    WHERE Denumire IS NULL

UPDATE Perioada_Istorica
    SET Denumire = 'Evul mediu'
    WHERE CodP BETWEEN 1 AND 5 OR CodP > 9

--                          DELETE                          --

----Antichitate----
DELETE
    FROM Antichitate
    WHERE Nume IS NULL

DELETE
    FROM Antichitate
    WHERE CodP = 1 OR CodP = 2

DELETE
    FROM Antichitate
    WHERE Nume IS NULL OR Nume LIKE 'Bacnot'

DELETE
    FROM Antichitate
    WHERE CodP IN (1,2,4) OR Pret BETWEEN 8000 AND 10000

----Achzitie----
DELETE
    FROM Achizitie
    WHERE Suma <= 10

DELETE
    FROM Achizitie
    WHERE CodS BETWEEN 5 AND 7 OR CodS = 1

DELETE
    FROM Achizitie
    WHERE DATA LIKE '01%2016'

DELETE
    FROM Achizitie
    WHERE Suma IN (120,50,10) OR Suma IS NULL

--                          INTEROGARI                          --

-- Lista cumparatorilor ce au atestate de colectionar generale
-- sau nu au atestate

SELECT C.CodCu,C.Nume, C.Prenume
    FROM Cumparator C
    WHERE C.CodCu IN (SELECT AC.CodAt
                        FROM Atestat_de_Colectionar AC
                        WHERE AC.Tip = 'General')
UNION
SELECT C.CodCu,C.Nume, C.Prenume
    FROM Cumparator C
    WHERE C.CodCu NOT IN (SELECT AC.CodAt
                            FROM Atestat_de_Colectionar AC
                            WHERE AC.Tip = 'General' OR Ac.Tip = 'Arme')
ORDER BY C.Nume, C.Prenume


-- Lista antichitatilor achizitionate + lista antichitatilor ce nu au un certificat de
-- autenticitate sau sunt prea vechi pentru a putea fi identificate fara a fi afectate
-- (inaintea epocii clasice)

SELECT A.CodA, A.Nume, A.Pret
    FROM Antichitate A
    WHERE A.CodA IN (SELECT V.CodA
                        FROM Vanzare V)
UNION
SELECT A.CodA, A.Nume, A.Pret
    FROM Antichitate A
    WHERE A.CodA NOT IN (SELECT C.CodC FROM Certificat_de_Autenticitate C)
          OR A.CodP IN (1,2)
ORDER BY A.Pret

-- Lista cumparatorilor ce au macar atestate de colectionar 'arme' si au cumparat
-- macar o antichitate

SELECT DISTINCT C.CodCu, C.Nume, C.Prenume
    FROM Cumparator C
    WHERE C.CodCu IN (SELECT CodAt FROM Atestat_de_Colectionar)
INTERSECT
SELECT DISTINCT C.CodCu, C.Nume, C.Prenume
    FROM Cumparator C
    WHERE C.CodCu IN (SELECT CodCu FROM Vanzare)

-- Lista produselor din categoria mobilier achizitionat de la un colectionar din Cluj

SELECT A.Nume, A.Pret
    FROM Antichitate A
    WHERE A.CodCA = 2
INTERSECT
SELECT A.Nume, A.Pret
    FROM Antichitate A
    WHERE A.CodA IN (SELECT AC.CodA
                        FROM Achizitie AC
                        WHERE AC.CodS IN (SELECT AD.CodAd
                                            FROM Adresa AD
                                            WHERE Oras = 'Cluj'))
ORDER BY A.Pret

-- Lista clientilor ce nu au Atestat de colectionar

SELECT C.Nume, C.Prenume
    FROM Cumparator C
EXCEPT
SELECT C.Nume, C.Prenume
    FROM Cumparator C
    WHERE C.CodCu IN (SELECT AC.CodAt
                            FROM Atestat_de_Colectionar AC
                            WHERE Tip IN ('Arme','Arne','Generic'))

-- Lista clientilor ce au Atestat de colectionar

SELECT C.Nume, C.Prenume
    FROM Cumparator C
EXCEPT
SELECT C.Nume, C.Prenume
    FROM Cumparator C
    WHERE C.CodCu NOT IN (SELECT AC.CodAt
                            FROM Atestat_de_Colectionar AC
                            WHERE Tip IN ('Arme','Arne','Generic'))

-- Antichitatile cumparate si vandute

SELECT DISTINCT S.Nume AS [Nume Sursa],
                S.Prenume AS [Prenume Sursa],
                C.Nume AS [Nume Client],
                C.Prenume AS [Prenume Client],
                A.Nume AS [Denumire Antichitate]
    FROM Sursa S
        INNER JOIN Achizitie Ac ON S.CodS = Ac.CodS
        INNER JOIN Antichitate A ON Ac.CodA = A.CodA
        INNER JOIN Vanzare V ON V.CodA = A.CodA
        INNER JOIN Cumparator C ON V.CodCu = C.CodCu
ORDER BY S.Nume, S.Prenume,C.Nume,C.Prenume,A.Nume

-- toate antichitatile vandute din stoc  + antichitatile vandute de la clienti

SELECT S.Nume AS [Nume Sursa],
                S.Prenume AS [Prenume Sursa],
                C.Nume AS [Nume Client],
                C.Prenume AS [Prenume Client],
                A.Nume AS [Denumire Antichitate]
    FROM Sursa S
        LEFT JOIN Achizitie Ac ON S.CodS = Ac.CodS
        LEFT JOIN Antichitate A ON Ac.CodA = A.CodA
        LEFT JOIN Vanzare V ON V.CodA = A.CodA
        LEFT JOIN Cumparator C ON V.CodCu = C.CodCu
ORDER BY C.Nume,C.Prenume

-- toate antichitatile cumparate + antichitatile vandute

SELECT S.Nume AS [Nume Sursa],
                S.Prenume AS [Prenume Sursa],
                C.Nume AS [Nume Client],
                C.Prenume AS [Prenume Client],
                A.Nume AS [Denumire Antichitate]
    FROM Sursa S
        RIGHT JOIN Achizitie Ac ON S.CodS = Ac.CodS
        RIGHT JOIN Antichitate A ON Ac.CodA = A.CodA
        RIGHT JOIN Vanzare V ON V.CodA = A.CodA
        RIGHT JOIN Cumparator C ON V.CodCu = C.CodCu
ORDER BY S.Nume, S.Prenume

-- toate produsele achizitionate si cu cat au fost vandute + cele vandute din stock
-- + cele ce inca nu au fost vandute

SELECT Ac.DATA AS [DATA Achizitiei],
       Ac.Suma AS [Suma Achizitiei],
       V.DATA AS [DATA Vanzarii],
       V.Suma AS [Suma Vanzarii]
    FROM Achizitie Ac
        FULL JOIN Antichitate A ON Ac.CodA = A.CodA
        FULL JOIN Vanzare V ON A.CodA = V.CodA
ORDER BY Ac.DATA,V.DATA

-- Vanzatorii ce au efectuat o vanzare

SELECT V.Nume, V.Prenume
    FROM Vanzator V
    WHERE EXISTS (SELECT*
                    FROM Vanzare Vz
                    WHERE Vz.CodV = V.CodV)

-- Vanzatorii ce nu au efectuat o vanzare

SELECT V.Nume, V.Prenume
    FROM Vanzator V
    WHERE EXISTS (SELECT*
                    FROM Vanzare Vz
                    WHERE Vz.CodV = V.CodV)

-- Top categorii de antichitati cu cea mai mare valoare de vanzare

SELECT TOP 3 CA.Nume, SUM(V.Suma) AS Suma
    FROM Categorie CA, Vanzare V, Antichitate A
    WHERE CA.CodCA = A.CodCA AND A.CodA = V.CodA
GROUP BY CA.Nume
HAVING SUM(V.Suma)> 3000
ORDER BY Suma DESC

-- Top categorii de antichitati cu cea mai mare valoare de cumparare

SELECT TOP 10 CA.Nume, SUM(Ac.Suma) AS Medie
    FROM Categorie CA, Achizitie Ac, Antichitate A
    WHERE Ca.CodCA = A.CodCA AND A.codA = Ac.CodA
GROUP BY CA.Nume
HAVING SUM(Ac.Suma) > AVG(Ac.Suma)
ORDER BY Medie DESC

-- Numarul de antichitati achizitionate organizat pe categorii, avand suma cheltuita peste medie

SELECT CA.Nume, COUNT(Ac.CodA) AS [Nr. Achizitii]
    FROM Categorie CA, Achizitie Ac, Antichitate A
    WHERE Ca.CodCA = A.CodCA AND A.codA = Ac.CodA
GROUP BY CA.Nume
HAVING SUM(Ac.Suma) > (SELECT AVG(Suma) FROM Achizitie)
ORDER BY [Nr. Achizitii]

-- Valoarile peste medie a vanzarilor efectuate de un vanzator

SELECT V.Nume,V.Prenume, COUNT(Va.CodV) AS [Nr. Vanzari], AVG(Va.Suma) AS [Media]
    FROM Vanzator V, Vanzare Va
    WHERE Va.CodV = V.CodV
GROUP BY V.Nume, V.Prenume
HAVING SUM(Va.Suma) > (SELECT AVG(Suma) FROM Vanzare)
ORDER BY [Media] DESC

--Produsele pretul mai mare decat cele vandute inainte de 01.01.2017

SELECT A.Nume
    FROM Antichitate A, Vanzare V
    WHERE A.CodA = V.CodA AND A.Pret > ALL(SELECT Suma
                                            FROM Vanzare
                                            WHERE DATA < '2017-01-01')

SELECT A.Nume
    FROM Antichitate A, Vanzare V
    WHERE A.CodA = V.CodA AND A.Pret > (SELECT AVG(Suma)
                                            FROM Vanzare
                                            WHERE DATA < '2017-01-01')

--Produsele pretul mai mic decat cele vandute in data de 01.01.2017

SELECT A.Nume
    FROM Antichitate A, Vanzare V
    WHERE A.CodA = V.CodA AND A.Pret < ANY(SELECT Suma
                                            FROM Vanzare
                                            WHERE DATA = '2017-01-01')

SELECT A.Nume
    FROM Antichitate A, Vanzare V
    WHERE A.CodA = V.CodA AND A.Pret < (SELECT AVG(Suma)
                                            FROM Vanzare
                                            WHERE DATA = '2017-01-01')

-- Produsele vandute in data de 01-01-2017

SELECT A.Nume
    FROM Antichitate A, Vanzare V
    WHERE A.CodA = V.CodA AND V.DATA = ALL(SELECT DATA
                                            FROM Vanzare
                                            WHERE DATA = '2017-01-01')

SELECT A.Nume
    FROM Antichitate A, Vanzare V
    WHERE A.CodA = V.CodA AND V.DATA NOT IN (SELECT DATA
                                                FROM Vanzare
                                                WHERE DATA < '2017-01-01' OR DATA >'2017-01-01' )

-- Produsele vandute inainte de 01-01-2017

SELECT A.Nume
    FROM Antichitate A, Vanzare V
    WHERE A.CodA = V.CodA AND V.DATA = ANY(SELECT DATA
                                            FROM Vanzare
                                            WHERE DATA < '2017-01-01')

SELECT A.Nume
    FROM Antichitate A, Vanzare V
    WHERE A.CodA = V.CodA AND V.DATA NOT IN (SELECT DATA
                                                FROM Vanzare
                                                WHERE DATA >= '2017-01-01')

-- Antichitatile cumparate si vandute
SELECT A.[Nume Sursa],A.[Nume Sursa],A.[Nume Client],A.[Prenume Client],A.[Denumire Antichitate]
    FROM (SELECT DISTINCT S.Nume AS [Nume Sursa],
                          S.Prenume AS [Prenume Sursa],
                          C.Nume AS [Nume Client],
                          C.Prenume AS [Prenume Client],
                          A.Nume AS [Denumire Antichitate]
          FROM Sursa S
            INNER JOIN Achizitie Ac ON S.CodS = Ac.CodS
            INNER JOIN Antichitate A ON Ac.CodA = A.CodA
            INNER JOIN Vanzare V ON V.CodA = A.CodA
            INNER JOIN Cumparator C ON V.CodCu = C.CodCu) A


-- toate antichitatile vandute din stoc  + antichitatile vandute de la clienti

SELECT A.[Nume Sursa],A.[Nume Sursa],A.[Nume Client],A.[Prenume Client],A.[Denumire Antichitate]
    FROM (SELECT DISTINCT S.Nume AS [Nume Sursa],
                          S.Prenume AS [Prenume Sursa],
                          C.Nume AS [Nume Client],
                          C.Prenume AS [Prenume Client],
                          A.Nume AS [Denumire Antichitate]
          FROM Sursa S
            LEFT JOIN Achizitie Ac ON S.CodS = Ac.CodS
            LEFT JOIN Antichitate A ON Ac.CodA = A.CodA
            LEFT JOIN Vanzare V ON V.CodA = A.CodA
            LEFT JOIN Cumparator C ON V.CodCu = C.CodCu) A