YySbeqih

1
2SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
3SET time_zone = "+00:00";
4
5
6/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
7/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
8/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
9/*!40101 SET NAMES utf8 */;
10
11
12CREATE TABLE IF NOT EXISTS `beneficiar` (
13  `Cod_B` int(11) NOT NULL,
14  `Nume` varchar(255) NOT NULL,
15  `Oras` varchar(255) NOT NULL
16) ENGINE=InnoDB DEFAULT CHARSET=latin1;
17
18
19INSERT INTO `beneficiar` (`Cod_B`, `Nume`, `Oras`) VALUES
20(1, 'Serv SIM', 'Sibiu'),
21(2, 'ARIPA Light', 'Covasna'),
22(3, 'E Agricultura', 'Caras-Severin'),
23(4, 'NET IT', 'Olt'),
24(5, 'Fier CTC', 'Galati'),
25(6, 'RAF ALI', 'Neamt'),
26(7, 'FIRENZE', 'Bacau'),
27(8, 'Quinta Essenta', 'Arad'),
28(9, 'Swietelsky Construct', 'Braila'),
29(10, 'IRIDEX GROUP SI', 'Harghita');
30
31
32
33CREATE TABLE IF NOT EXISTS `cereri` (
34  `Cod_B` int(11) NOT NULL,
35  `Cod_P` int(11) NOT NULL,
36  `Pret` int(11) NOT NULL,
37  `Cantitate` int(11) NOT NULL
38) ENGINE=InnoDB DEFAULT CHARSET=latin1;
39
40
41INSERT INTO `cereri` (`Cod_B`, `Cod_P`, `Pret`, `Cantitate`) VALUES
42(3, 3, 300, 12),
43(3, 5, 300, 20),
44(6, 2, 300, 12);
45
46
47
48CREATE TABLE IF NOT EXISTS `furnizor` (
49  `Cod_F` int(11) NOT NULL,
50  `Nume` varchar(255) NOT NULL,
51  `Oras` varchar(255) NOT NULL
52) ENGINE=InnoDB DEFAULT CHARSET=latin1;
53
54
55
56INSERT INTO `furnizor` (`Cod_F`, `Nume`, `Oras`) VALUES
57(1, 'Dacia Service', 'Cluj'),
58(2, 'Mercury Lighting', 'Suceava'),
59(3, 'Agromec AG', 'Targu Mures'),
60(4, 'Computer Sharing', 'Bucuresti'),
61(5, 'Metal Brasov', 'Brasov'),
62(6, 'Interfrig', 'Prahova'),
63(7, 'Leoni Wiring', 'Vaslui'),
64(8, 'Remedia', 'Alba Iulia'),
65(9, 'Constructii Feroviare', 'Dambovita'),
66(10, 'Sidor Construct', 'Iasi');
67
68
69
70CREATE TABLE IF NOT EXISTS `oferte` (
71  `Cod_F` int(11) NOT NULL,
72  `Cod_P` int(11) NOT NULL,
73  `Pret` int(11) NOT NULL,
74  `Cantitate` int(11) NOT NULL
75) ENGINE=InnoDB DEFAULT CHARSET=latin1;
76
77
78
79INSERT INTO `oferte` (`Cod_F`, `Cod_P`, `Pret`, `Cantitate`) VALUES
80(3, 2, 250, 10),
81(8, 5, 400, 15);
82
83
84
85CREATE TABLE IF NOT EXISTS `produs` (
86  `Cod_P` int(11) NOT NULL,
87  `uM` varchar(40) NOT NULL,
88  `Nume` varchar(255) NOT NULL
89) ENGINE=InnoDB DEFAULT CHARSET=latin1;
90
91
92
93INSERT INTO `produs` (`Cod_P`, `uM`, `Nume`) VALUES
94(1, 'bucata', 'Bujie'),
95(2, 'bucata', 'Placa video'),
96(3, 'kg', 'Fertilizator'),
97(4, 'kg', 'Cuie'),
98(5, 'kg', 'Cauciuc'),
99(6, 'kg', 'Sulf'),
100(7, 'bucata', 'Electrozi'),
101(8, 'kg', 'Cupru'),
102(9, 'bucata', 'Procesor Intel'),
103(10, 'litri', 'Freon');
104
105
106
107CREATE TABLE IF NOT EXISTS `tranzactii` (
108  `Cod_T` int(11) NOT NULL,
109  `Cod_F` int(11) NOT NULL,
110  `Cod_P` int(11) NOT NULL,
111  `Cod_B` int(11) NOT NULL,
112  `Pret` int(11) NOT NULL,
113  `Cantitate` int(11) NOT NULL
114) ENGINE=InnoDB DEFAULT CHARSET=latin1;
115
116
117
118INSERT INTO `tranzactii` (`Cod_T`, `Cod_F`, `Cod_P`, `Cod_B`, `Pret`, `Cantitate`) VALUES
119(1, 5, 8, 1, 250, 15),
120(2, 1, 1, 6, 700, 25),
121(3, 2, 6, 8, 2200, 10),
122(4, 7, 7, 10, 500, 10),
123(5, 5, 4, 9, 1800, 35),
124(6, 4, 2, 4, 3, 1900),
125(7, 9, 8, 5, 4500, 16),
126(8, 10, 10, 7, 1000, 50),
127(9, 3, 5, 2, 2700, 5);
128
129
130ALTER TABLE `beneficiar`
131 ADD PRIMARY KEY (`Cod_B`);
132
133
134ALTER TABLE `cereri`
135 ADD PRIMARY KEY (`Cod_B`,`Cod_P`), ADD KEY `Cod_P` (`Cod_P`);
136
137
138ALTER TABLE `furnizor`
139 ADD PRIMARY KEY (`Cod_F`);
140
141
142ALTER TABLE `oferte`
143 ADD PRIMARY KEY (`Cod_F`,`Cod_P`), ADD KEY `Cod_P` (`Cod_P`);
144
145
146ALTER TABLE `produs`
147 ADD PRIMARY KEY (`Cod_P`);
148
149
150ALTER TABLE `tranzactii`
151 ADD PRIMARY KEY (`Cod_T`,`Cod_F`,`Cod_P`,`Cod_B`), ADD KEY `Cod_F` (`Cod_F`), ADD KEY `Cod_P` (`Cod_P`), ADD KEY `Cod_B` (`Cod_B`);
152
153
154ALTER TABLE `cereri`
155ADD CONSTRAINT `cereri_ibfk_1` FOREIGN KEY (`Cod_B`) REFERENCES `beneficiar` (`Cod_B`) ON DELETE CASCADE ON UPDATE CASCADE,
156ADD CONSTRAINT `cereri_ibfk_2` FOREIGN KEY (`Cod_P`) REFERENCES `produs` (`Cod_P`) ON DELETE CASCADE ON UPDATE CASCADE;
157
158
159ALTER TABLE `oferte`
160ADD CONSTRAINT `oferte_ibfk_1` FOREIGN KEY (`Cod_F`) REFERENCES `furnizor` (`Cod_F`) ON DELETE CASCADE ON UPDATE CASCADE,
161ADD CONSTRAINT `oferte_ibfk_2` FOREIGN KEY (`Cod_P`) REFERENCES `cereri` (`Cod_P`) ON DELETE CASCADE ON UPDATE CASCADE;
162
163
164ALTER TABLE `tranzactii`
165ADD CONSTRAINT `tranzactii_ibfk_1` FOREIGN KEY (`Cod_F`) REFERENCES `furnizor` (`Cod_F`) ON DELETE CASCADE ON UPDATE CASCADE,
166ADD CONSTRAINT `tranzactii_ibfk_2` FOREIGN KEY (`Cod_P`) REFERENCES `produs` (`Cod_P`) ON DELETE CASCADE ON UPDATE CASCADE,
167ADD CONSTRAINT `tranzactii_ibfk_3` FOREIGN KEY (`Cod_B`) REFERENCES `beneficiar` (`Cod_B`) ON DELETE CASCADE ON UPDATE CASCADE;
168
169/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
170/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
171/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
172
173
174select count(*)
175from furnizor;
176
177select count(distinct oras)
178from furnizor;
179
180SELECT Nume
181FROM furnizor
182WHERE Oras='Cluj'
183
184SELECT Nume
185FROM furnizor
186WHERE Oras='Cluj' or Oras='Bucuresti'
187
188SELECT Nume
189FROM furnizor
190order by nume asc;
191
192select nume, oras
193from beneficiar 
194where oras!='Bucuresti';
195
196select nume
197from furnizor, oferte
198where furnizor.cod_f=oferte.cod_f;
199
200select furnizor.nume as 'Nume'
201from furnizor, beneficiar, tranzactii
202where furnizor.oras='Cluj'
203and beneficiar.oras='Bucuresti'
204and furnizor.cod_f=tranzactii.cod_f
205and tranzactii.cod_b=beneficiar.cod_b;
206
207select nume
208from furnizor
209where cod_f not in
210(select cod_f from oferte);
211
212select furnizor.nume as 'Nume'
213from furnizor, beneficiar, tranzactii
214where furnizor.oras='Cluj'
215and beneficiar.oras!='Bucuresti'
216and furnizor.cod_f=tranzactii.cod_f
217and tranzactii.cod_b=beneficiar.cod_b;
218
219select furnizor.nume as 'Nume', count(cod_p) as 'Numar oferte' 
220from furnizor, oferte
221where furnizor.cod_f=oferte.cod_f;