Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- CREARE BAZA DE DATE --
- CREATE DATABASE Lab2R
- GO
- USE Lab2R
- GO
- CREATE TABLE Plafaruri(
- Pid INT PRIMARY KEY,
- Denumire VARCHAR(50),
- Oras VARCHAR(50) NOT NULL
- )
- CREATE TABLE Ingrediente(
- Iid INT PRIMARY KEY IDENTITY,
- Nume VARCHAR(50),
- Cantitate INT DEFAULT 1,
- Pret INT
- )
- CREATE TABLE Ceaiuri(
- Cid INT PRIMARY KEY,
- Denumire VARCHAR(50),
- Cantitate INT,
- Pret INT,
- Iid INT FOREIGN KEY REFERENCES Ingrediente(Iid)
- )
- CREATE TABLE PlafaruriCeaiuri(
- Cid INT FOREIGN KEY REFERENCES Ceaiuri(Cid),
- Pid INT FOREIGN KEY REFERENCES Plafaruri(Pid),
- CONSTRAINT pk_PlafaruriCeaiuri PRIMARY KEY (Cid, Pid)
- )
- -- SELECT --
- SELECT * FROM Plafaruri --AFISEAZA TOTUL DIN TABELA PLAFARURI
- SELECT * FROM Ingrediente --AFISAREA TUTTROR ELEMENTELOR DIN TABELA INGREDIENTE
- SELECT * FROM Ceaiuri --AFISAREA TUTUROR ELEMENTELOR DIN TABELA CEAIURI
- SELECT * FROM PLafaruriCeaiuri --AFISAREA TUTUROR ELEMENTELOR DIN TABELA PLAFARURI_CEAIURI
- SELECT * --AFISAREA TUTUROR ELEMENTELOR LEGATE
- FROM Plafaruri P INNER JOIN PLafaruriCeaiuri PC ON P.Pid = PC.Pid
- INNER JOIN Ceaiuri C ON PC.CID = C.CID
- -- INSERT --
- INSERT INTO Plafaruri(Pid,Denumire,Oras)
- VALUES(1,'Plafar1','Cluj')
- INSERT INTO Plafaruri
- VALUES(2,'Plafar2','Brasov')
- INSERT INTO Plafaruri(Pid,Oras)
- VALUES(3,'Bucuresti')
- INSERT INTO Plafaruri --GRESIT NU RESPECTA PRIMARY KEY
- VALUES(2,'Plafar4','Viena')
- INSERT INTO Plafaruri(Pid,Denumire) --NU SE POATE INSERA IN TABEL DECAT DACA SE INSEREAZA SI IN TABELELE CE SUNT NOT NULL
- VALUES (4,'P4')
- INSERT INTO Plafaruri --NU CONVIN TIPURILE
- VALUES ('Plafar4',4,8)
- INSERT INTO Plafaruri(Denumire,Pid,Oras) --POATE FI REPARAT ASTFEL-ISH
- VALUES ('Plafar4',4,8)
- INSERT INTO Ingrediente(Iid,Nume,Cantitate,Pret) --NU SE POATE INSERA PE Iid PENTRU CA E AUTOINCREMENT
- VALUES (4,'Scortisoara',50,4)
- INSERT INTO Ingrediente(Nume,Cantitate,Pret) --ASA SE FACE CORECT (SE GENEREAZA AUTOMAT UN ID)
- VALUES ('Scortisoara',50,4)
- INSERT INTO Ingrediente(Nume,Pret)
- VALUES('Musetel',1)
- INSERT INTO Ceaiuri --NU SE POATE ADAUGA CEVA CE NU ESTE CA FK
- VALUES (1,'Ceai de menta',1,2,4)
- INSERT INTO Ceaiuri
- VALUES (1,'Ceai de menta',4,50,2)
- INSERT INTO Ceaiuri (Cid,Denumire,Iid)
- VALUES (2,'Ceai de musetel',1)
- INSERT INTO PlafaruriCeaiuri (Cid,Pid)
- VALUES(1,1),(1,2),(1,3),(2,1),(2,2),(2,3)
- -- UPDATE --
- UPDATE Ingrediente
- SET Cantitate = 5, Nume = 'Ghimbir'
- WHERE Iid >= 5 AND Nume LIKE 'A%' --FARA FACE UPDATE LA ABSOLUT TOATE
- UPDATE Ceaiuri
- SET Cantitate = 10
- WHERE Pret < 12 OR Cantitate = 1
- UPDATE Plafaruri
- SET Oras = 'Botosani'
- WHERE Denumire LIKE '_a_' OR Denumire IS NULL
- UPDATE Ingrediente
- SET Pret = 100
- WHERE Cantitate IN(20,30,40)
- UPDATE Ceaiuri
- SET Cantitate = 50
- WHERE Pret BETWEEN 10 AND 40
- UPDATE Ceaiuri
- SET Pret = 2
- WHERE Denumire LIKE '%s'
- -- DELETE --
- DELETE
- FROM Ingrediente
- WHERE Nume = 'Menta' OR Cantitate = NULL
- DELETE
- FROM Ceaiuri
- WHERE Pret = 1 AND Denumire LIKE '___%'
- DELETE
- FROM Plafaruri --STERGE TOT! ORASUL NU POATE FI NULL
- WHERE Oras IS NOT NULL
- DELETE
- FROM Ceaiuri
- WHERE Pret >= 12 AND Denumire = 'Menta'
Add Comment
Please, Sign In to add comment