FbAsztQF

1CREATE DATABASE RecordedElections
2GO
3USE RecordedElections
4GO
5CREATE TABLE Politicians ------Ta(aid, a2, …)
6(
7    pid INT PRIMARY KEY IDENTITY,
8    cabinetNumber INT UNIQUE,
9    name VARCHAR(30) NOT NULL
10)
11GO
12CREATE TABLE Elections -----Tb(bid, b2, …)
13(
14    eid INT PRIMARY KEY IDENTITY,
15    yoe INT,
16    topic VARCHAR(60)
17)
18GO
19CREATE TABLE FullResults ---- Tc(cid, aid, bid, …)
20(
21    fr INT PRIMARY KEY IDENTITY,
22    eid INT FOREIGN KEY REFERENCES Elections(eid),
23    pid INT FOREIGN KEY REFERENCES Politicians(pid),
24    procentage INT
25)
26GO
27DELETE FROM FullResults
28GO
29INSERT INTO Politicians
30    (name,cabinetNumber)
31VALUES('Gheorghe Alexandrescu', 32),
32    ('Nicolae Alexandri', 34),
33    ('Radu F. Alexandru', 5435),
34    ('Gheorghe Alexianu', 5433),
35    ('Dumitru Alimănișteanu', 65),
36    ('Elena Alistar', 876),
37    ('Andrei Volosevici', 99),
38    ('Roberta Anastase', 534534),
39    ('Petre Andrei', 3213),
40    ('Constantin Anton', 543),
41    ('Crin Antonescu', 54354),
42    ('Ion Antonescu', 654),
43    ('Mihai Antonescu', 6546),
44    ('Sorin Apostu', 413),
45    ('Constantin Argetoianu', 2131)
46GO
47SELECT *
48FROM Politicians
49GO
50INSERT INTO Elections
51    (yoe,topic)
52VALUES(2014, 'European Parliament election in Romania'),
53    (2019, 'European Parliament election in Romania'),
54    (1985, 'Romanian legislative election'),
55    (1990, 'Romanian general election'),
56    (1992, 'Romanian general election'),
57    (1996, 'Romanian general election'),
58    (2000, 'Romanian general election'),
59    (2004, 'Romanian general election'),
60    (2008, 'Romanian legislative election'),
61    (2012, 'Romanian legislative election'),
62    (2016, 'Romanian legislative election')
63GO
64SELECT *
65FROM Elections
66GO
67INSERT INTO FullResults
68    (eid,pid,procentage)
69VALUES
70    ( 4 , 2 , 15 ),
71    ( 1 , 1 , 29 ),
72    ( 4 , 7 , 25 ),
73    ( 7 , 6 , 11 ),
74    ( 1 , 8 , 15 ),
75    ( 2 , 11 , 14 ),
76    ( 6 , 15 , 9 ),
77    ( 2 , 12 , 9 ),
78    ( 8 , 14 , 11 ),
79    ( 3 , 13 , 22 ),
80    ( 7 , 15 , 2 ),
81    ( 2 , 6 , 27 ),
82    ( 3 , 3 , 22 ),
83    ( 2 , 14 , 8 ),
84    ( 1 , 4 , 28 ),
85    ( 11 , 12 , 28 ),
86    ( 1 , 7 , 23 ),
87    ( 11 , 14 , 27 ),
88    ( 11 , 5 , 24 ),
89    ( 6 , 10 , 4 )
90SELECT *
91FROM FullResults
92GO
93---a)
94-----clustered index scan;
95SELECT *
96FROM Politicians
97GO
98-----clustered index seek;
99SELECT *
100FROM Politicians
101WHERE pid > 10 
102GO
103-----nonclustered index scan
104IF EXISTS (SELECT name
105FROM sys.indexes
106WHERE name = N'N_idx_Politicians_cabnr') DROP INDEX N_idx_Politicians_cabnr ON Politicians;
107GO
108CREATE NONCLUSTERED INDEX N_idx_Politicians_cabnr ON Politicians(cabinetNumber)
109GO
110SELECT *
111FROM Politicians
112WHERE cabinetNumber%10 < 5
113ORDER BY cabinetNumber DESC
114-----nonclustered index seek
115SELECT *
116FROM Politicians
117WHERE cabinetNumber > 110 AND cabinetNumber < 1110
118ORDER BY cabinetNumber DESC
119GO
120-----key lookup
121
122
123----b)Write a query on table Tb with a WHERE clause of the form WHERE b2 = value and analyze its execution plan. Create a nonclustered 
124----index that can speed up the query. Recheck the query’s execution plan (operators, SELECT’s estimated subtree cost).
125
126SELECT *
127FROM Elections
128WHERE eid = 10
129GO
130---creating the index
131IF EXISTS (SELECT name
132FROM sys.indexes
133WHERE name = N'N_idx_eid') DROP INDEX N_idx_eid ON Elections;
134GO
135-- Create a nonclustered index called N_idx_Price on the Presents table using the Price column.
136CREATE NONCLUSTERED INDEX N_idx_eid ON Elections(Eid)
137GO
138SELECT *
139FROM Elections
140WHERE eid BETWEEN 5 AND 15
141GO
142-----c. Create a view that joins at least 2 tables. 
143-----Check whether existing indexes are helpful; if not, reassess existing indexes / examine the cardinality of the tables.
144
145CREATE OR ALTER VIEW viewFrumi
146AS
147    SELECT Pol.name, El.topic, El.yoe, FR.procentage
148    FROM Politicians Pol INNER JOIN FullResults FR ON Pol.pid=FR.pid
149        INNER JOIN Elections El ON FR.eid = El.eid
150    WHERE FR.procentage > 15 AND El.yoe BETWEEN 2004 AND 2016
151GO
152SELECT *
153FROM viewFrumi
154
155IF EXISTS (SELECT name
156FROM sys.indexes
157WHERE name = N'N_idx_proc') DROP INDEX N_idx_proc ON FullResults
158GO
159-- Create a nonclustered index called N_idx_Price on the Presents table using the Price column.
160CREATE NONCLUSTERED INDEX N_idx_proc ON FullResults(procentage)
161GO
162IF EXISTS (SELECT name
163FROM sys.indexes
164WHERE name = N'N_idx_yoe') DROP INDEX N_idx_yoe ON Elections
165GO
166-- Create a nonclustered index called N_idx_Price on the Presents table using the Price column.
167CREATE NONCLUSTERED INDEX N_idx_yoe ON Elections(yoe)
168GO
169
170SELECT *
171FROM viewFrumi