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' |