View difference between Paste ID: yWhGqC0J and Fxhwxsfx
SHOW: | | - or go back to the newest paste.
1
--                           CREARE BAZA DE DATE                           --  
2
3
Create database Lab2R
4
go
5
use Lab2R
6
go
7
CREATE TABLE Plafaruri(
8
	Pid INT PRIMARY KEY,
9
	Denumire varchar(50),
10
	Oras varchar(50) NOT NULL
11
)
12
CREATE TABLE Ingrediente(
13
	Iid INT PRIMARY KEY IDENTITY,
14
	Nume varchar(50),
15
	Cantitate int DEFAULT 1,
16
	Pret int
17
)
18
CREATE TABLE Ceaiuri(
19
	Cid INT PRIMARY KEY,
20
	Denumire varchar(50),
21
	Cantitate int,
22
	Pret int,
23
	Iid int FOREIGN KEY REFERENCES Ingrediente(Iid)
24
)
25
CREATE TABLE PlafaruriCeaiuri(
26
	Cid INT FOREIGN KEY REFERENCES Ceaiuri(Cid),
27
	Pid INT FOREIGN KEY REFERENCES Plafaruri(Pid),
28
	CONSTRAINT pk_PlafaruriCeaiuri PRIMARY KEY (Cid, Pid)
29
)
30
31
--                           SELECT                           --   
32
33
SELECT * FROM Plafaruri --AFISEAZA TOTUL DIN TABELA PLAFARURI
34
SELECT * FROM Ingrediente --AFISAREA TUTTROR ELEMENTELOR DIN TABELA INGREDIENTE
35
SELECT * FROM Ceaiuri --AFISAREA TUTUROR ELEMENTELOR DIN TABELA CEAIURI
36
SELECT * FROM PLafaruriCeaiuri --AFISAREA TUTUROR ELEMENTELOR DIN TABELA PLAFARURI_CEAIURI
37
SELECT * --AFISAREA TUTUROR ELEMENTELOR LEGATE
38
	FROM  Plafaruri P INNER JOIN PLafaruriCeaiuri PC ON P.Pid = PC.Pid
39
	INNER JOIN Ceaiuri C ON PC.CID = C.CID
40
41
--                           INSERT                           --   
42
43
INSERT INTO Plafaruri(Pid,Denumire,Oras)
44
	VALUES(1,'Plafar1','Cluj')
45
46
INSERT INTO Plafaruri 
47
	VALUES(2,'Plafar2','Brasov')
48
49
INSERT INTO Plafaruri(Pid,Oras)
50
	VALUES(3,'Bucuresti')
51
52
INSERT INTO Plafaruri --GRESIT NU RESPECTA PRIMARY KEY 
53
	VALUES(2,'Plafar4','Viena')
54
55
INSERT INTO Plafaruri(Pid,Denumire) --NU SE POATE INSERA IN TABEL DECAT DACA SE INSEREAZA SI IN TABELELE CE SUNT NOT NULL
56
	VALUES (4,'P4')
57
58
INSERT INTO Plafaruri --NU CONVIN TIPURILE
59
	VALUES ('Plafar4',4,8)
60
61
INSERT INTO Plafaruri(Denumire,Pid,Oras) --POATE FI REPARAT ASTFEL-ISH
62
	VALUES ('Plafar4',4,8)
63
64
INSERT INTO Ingrediente(Iid,Nume,Cantitate,Pret) --NU SE POATE INSERA PE Iid PENTRU CA E AUTOINCREMENT
65
	VALUES (4,'Scortisoara',50,4)
66
67
INSERT INTO Ingrediente(Nume,Cantitate,Pret) --ASA SE FACE CORECT (SE GENEREAZA AUTOMAT UN ID)
68
	VALUES ('Scortisoara',50,4)
69
70
INSERT INTO Ingrediente(Nume,Pret)
71
	VALUES('Musetel',1)
72
73
INSERT INTO Ceaiuri --NU SE POATE ADAUGA CEVA CE NU ESTE CA FK
74
	VALUES (1,'Ceai de menta',1,2,4)
75
76
INSERT INTO Ceaiuri
77
	VALUES (1,'Ceai de menta',4,50,2)
78
79
INSERT INTO Ceaiuri (Cid,Denumire,Iid)
80
	VALUES (2,'Ceai de musetel',1)
81
82
INSERT INTO PlafaruriCeaiuri (Cid,Pid)
83
	VALUES(1,1),(1,2),(1,3),(2,1),(2,2),(2,3)
84
85
--                           UPDATE                           --                         
86
87
UPDATE Ingrediente 
88
	SET Cantitate = 5, Nume = 'Ghimbir'
89
	WHERE Iid >= 5 AND Nume LIKE 'A%'  --FARA FACE UPDATE LA ABSOLUT TOATE
90
91
UPDATE Ceaiuri 
92
	SET Cantitate = 10
93
	WHERE Pret < 12 OR Cantitate = 1
94
95
UPDATE Plafaruri
96
	SET Oras = 'Botosani' 
97
	WHERE Denumire LIKE '_a_' OR Denumire IS NULL
98
99
UPDATE Ingrediente 
100
	SET Pret = 100
101
	WHERE Cantitate IN(20,30,40)
102
103
UPDATE Ceaiuri
104
	SET Cantitate = 50
105
	WHERE Pret BETWEEN 10 AND 40
106
107
UPDATE Ceaiuri
108
	SET Pret = 2
109
	WHERE Denumire LIKE '%s'
110
111
--                           DELETE                           --   
112
113
DELETE 
114
	FROM Ingrediente
115
	WHERE Nume = 'Menta' OR Cantitate = NULL
116
117
DELETE 
118
	FROM Ceaiuri
119
	WHERE Pret = 1 AND Denumire LIKE '___%'
120
121
DELETE 
122
	FROM Plafaruri --STERGE TOT! ORASUL NU POATE FI NULL
123
	WHERE Oras IS NOT NULL
124
125
DELETE
126
	FROM Ceaiuri
127
	WHERE Pret >= 12 AND Denumire = 'Menta'