· 5 years ago · Mar 22, 2020, 05:10 PM
1IF EXISTS (SELECT 'True' FROM sys.databases WHERE name = 'magazin_foto')
2 BEGIN
3 DROP DATABASE magazin_foto
4 END
5
6create database magazin_foto;
7
8use magazin_foto;
9
10------------------- crearea tabelelor --------------------
11
12CREATE TABLE categorii (
13 idCategorie int primary key NOT NULL,
14 denCategorie varchar(25) NOT NULL
15);
16
17----------------------------------------------------------
18
19CREATE TABLE producatori (
20 idProducator int primary key NOT NULL,
21 denProducator varchar(20) NOT NULL
22);
23
24----------------------------------------------------------
25
26CREATE TABLE tari_producatoare (
27 idTara int primary key NOT NULL,
28 denTara varchar(15) NOT NULL
29);
30
31----------------------------------------------------------
32
33CREATE TABLE produse(
34 idProdus int primary key NOT NULL,
35 denProdus varchar(50) NOT NULL,
36 pret float NOT NULL,
37 idCategorie int references categorii(idCategorie) NOT NULL,
38 idProducator int references producatori(idProducator) NOT NULL,
39 idTara int references tari_producatoare(idTara) NOT NULL,
40 foto char(40) NOT NULL,
41 rating int NOT NULL
42);
43
44----------------------------------------------------------
45
46CREATE TABLE aparatefoto (
47 idProdus int primary key references produse(idProdus) NOT NULL,
48 rezolutie float NOT NULL,
49 mount varchar(10) NOT NULL,
50 descriere text NOT NULL
51);
52
53----------------------------------------------------------
54
55
56CREATE TABLE lentile (
57 idProdus int primary key references produse(idProdus) NOT NULL,
58 mount varchar(10) NOT NULL,
59 diafragma float NOT NULL,
60 focalLenght int NOT NULL,
61 descriere text NOT NULL
62);
63
64----------------------------------------------------------
65
66
67CREATE TABLE pelicula (
68 idProdus int primary key references produse(idProdus) NOT NULL,
69 tip varchar(20) NOT NULL,
70 iso int NOT NULL,
71 formatul varchar(15) NOT NULL,
72 descriere text NOT NULL
73);
74
75----------------------------------------------------------
76
77--- Inserarea datelor ---
78
79INSERT INTO producatori VALUES
80(1, 'Ilford'),
81(2, 'Canon'),
82(3, 'Fujifilm'),
83(4, 'Kodak'),
84(5, 'Leica'),
85(6, 'Sony'),
86(7, 'Nikon'),
87(8, 'Sigma'),
88(9, 'Olympus');
89
90INSERT INTO tari_producatoare VALUES
91(1, 'China'),
92(2, 'Japonia'),
93(3, 'Taiwan'),
94(4, 'Germania'),
95(5, 'S.U.A.'),
96(6, 'Anglia');
97
98INSERT INTO categorii VALUES
99(1, 'Aparate Foto'),
100(2, 'Lentile Foto'),
101(3, 'Pelicula Foto');
102
103
104INSERT INTO produse VALUES
105(1, 'Ilford HP5 Plus 400/36', 6.89, 3, 1, 6, 'ilfordhp5plus.png', 5),
106(2, 'Ortho Plus 80/36', 10.99, 3, 1, 6, 'ilfordorthoplus.png', 4),
107(3, 'EF 50mm f/1.8 STM', 129, 2, 2, 3, 'canonef50mmf18stm.png', 5),
108(4, 'X-T3 Kit', 1899, 1, 3, 2, 'fujifilmx-t3.png', 5),
109(5, 'Colorplus 200/36', 3.69, 3, 4, 5, 'kodakcolorplus200.png', 5),
110(6, 'Q2', 4989, 1, 5, 4, 'leicaq2.png', 5),
111(7, '550D Kit', 299, 1, 2, 1, 'canon550d.png', 5),
112(8, 'Alpha A7R IV', 3259, 1, 6, 2, 'sonyalpha7r.png', 5),
113(9, 'NIKKOR 24-70mm AF-S F2.8 G ED', 1499, 2, 7, 2, 'nikon2470.png', 4),
114(10,'X-T4 Kit', 2299, 1, 3, 2, 'fujifilmx-t4.png', 5),
115(11,'X100V', 1099, 1, 3, 2, 'fujifilmx-x100v.png', 5),
116(12,'30mm f/2.8 DN E-Mount', 199, 2, 8, 1, 'sigma30mm.png', 4),
117(13,'OM-D Mark II Kit', 399, 1, 9, 1, 'olympusomd.png', 4),
118(14,'Ektachrome 100/36', 12.99, 3, 4, 5, 'ektachrome.png', 5);
119
120INSERT INTO pelicula VALUES
121(1, 'negativ/alb-negru', 400, '35mm', 'Cea mai popularaa pelicula engleza.'),
122(2, 'negativ/alb-negru', 80, '35mm', 'Pelicula cu sensibilitate sporita in gama infrarosie.'),
123(5, 'negativ/color', 200, '35mm', 'Cea mai ieftina si una din cele mai populare tipuri de pelicula din lume! '),
124(14, 'pozitiv/color', 100, '35mm', 'Legendara pelicula pozitiva Kodak, care a fost reinviata in 2018.');
125
126INSERT INTO lentile VALUES
127(3, 'EF', 1.8, 50, 'Lorem ipsum...'),
128(9, 'F', 2.8, 70, 'Lorem ipsum...'),
129(13, 'E', 2.8, 30, 'Lorem ipsum...');
130
131INSERT INTO aparatefoto VALUES
132(4, 26.1, 'X', 'Lorem ipsum...'),
133(6, 46.7, 'M', 'Lorem ipsum...'),
134(7, 18, 'EF-S', 'Lorem ipsum...'),
135(8, 60.1, 'E', 'Lorem ipsum...'),
136(10, 26.1, 'X', 'Lorem ipsum...'),
137(11, 26.1, 'X', 'Lorem ipsum...'),
138(13, 16, 'Micro 3/4', 'Lorem ipsum...');