· 5 years ago · Mar 02, 2020, 03:16 PM
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35Name: Sachin Vishwakarma
36Roll No.: 48
37Subject: Data Science
38
39Index
40Sr.No.
41Date
42Title
43Page.
44No.
451
4615/10/2019
47Practical of Simple/Multiple Linear Regression.
483
492
5021/11/2019
51Practical of Time-series forecasting
527
533
5428/11/2019
55Practical of Time Series Analysis.
5611
574
5805/12/2019
59Practical of k-means Clustering.
6014
615
6212/12/2019
63Practical of Logistics Regression.
6418
656
6603/01/2020
67Practical of Decision Tree.
6825
697
7009/01/2020
71Practical of Hypothesis Testing.
7228
738
7416/01/2020
75Practical of Analysis of Variance.
7631
779
7823/01/2020
79Practical of Principle Component Analysis.
8035
81
82
83Practical 1
84
85Aim: Practical of Simple/Multiple Linear Regression.
86
87Description:
88
89A Supervised technique used for predicting the value when the data is continuous or real valued is known as regression. Regression consists variable know as Y or output variable and Independent variable known as X or input variable.
90Examples of the task in which the regression can be used are:
91Predicting the house price.
92Predicting age of person.
93Predicting nationality of person.
94Predicting stock price of the company etc.
95Some of the Important features of linear regression are as below:
96Linear regression is a fast and easy to model technique and is mainly useful when relationship is modelled and is not extremely complex.
97Linear regression is not feasible for fewer amounts of data.
98Linear regression is very sensitive to outliers.
99Regression is based on hypothesis and it can be linear, quadratic, polynomial, non-linear etc.
100Regression is divided mainly into two types which are:
101Simple Regression : It has only one feature and it is simple to use and develop. Simple regression is further classified into simple linear regression and simple non-linear regression.
102Multiple Regression : It has two or more than features and it is little complex to develop with respect to simple regression.
103
104Source Code:
105#initializing height vector
106height<-c(102,117,105,141,135,115,138,144,137,100,131,119,115,121,113)
107
108#initializing weight vector
109weight<-c(61,46,62,54,60,69,51,50,46,64,48,56,64,48,59)
110
111#generate regression where height act as an linear predictor for weight
112student<-lm(weight~height)
113student
114
115#making a prediction using above linear regression model
116predict(student,data.frame(height=199),interval="confidence")
117
118#plot regression model
119plot(student)
120
121Output:
122
123Console Output:
124student<-lm(weight~height)
125> student
126Call:
127lm(formula = weight ~ height)
128Coefficients:
129(Intercept) height
130 93.5530 -0.3084
131> #making a prediction using above linear regression model
132> predict(student,data.frame(height=199),interval="confidence")
133 fit lwr upr
1341 32.18165 13.07863 51.28468
135
136Plots:
137
138
139
140
141
142
143
144Practical 2
145
146Aim: Practical of Time-series forecasting.
147
148Description:
149
150Time series analysis consists of set of methods used to analyze various data facts or statistis from various characteristics of the data.
151Time series analysis is uscd for continuous data for example cconomic growth of an organization, share prices, sales temperature, weather etc.
152Time series analysis model has time factor "t" as an independent variable and the target is a dependent variable denoted by Y The output from the time scries model is a predicted value of Y at the given titne t. Time series is the process of recording of the data at regular interval of time.
153Time Series Components: There are various time series components which are as follows:
154Trend:
155It is considered to behavior of the feature at a particular amount of time, it can be categorized as increasing trend, decreasing trend or constant trend.
156When the particular feature value increases in particular amount of time it is increasing trend, similarly if it decreases it is decreasing trend and when it does not change over the period of time then it is constant trend.
157Seasonality:
158Seasonality a pattern which repcats at the constant frcquency. For example here the demand for the umbrellas will be in rainy season only.
159Cycles:
160Cycles are type of seasonality pattern but is does not repeat at regular frequency. Cycle can be generally considered as the task completion time.
161For example, in iterative model of software engincering every iteration can have different time requirement, but the crvery task has to undergo all stages in a single iteration.
162Forecasting:
163It is the processes of making prediction of the future based on the present and the past data most commonly by analysis of trends. Prediction is similar term to the forecasting but not cxactly the same.
164
165Source Code:
166
167#loading dataset "AirPassengers
168data(AirPassengers)
169
170class(AirPassengers)
171
172#returns starting interval
173start(AirPassengers)
174
175#returns end interval
176end(AirPassengers)
177
178#retunrs number of observations before pattern repeats
179frequency(AirPassengers)
180summary(AirPassengers)
181
182#plot AirPassengers
183plot(AirPassengers)
184
185#adds a linear model to above plot
186abline(reg=lm(AirPassengers~time(AirPassengers)))
187
188#cycle(AirPassengers)
189#computing mean for data subsets in AirPassengers and plotting it
190plot(aggregate(AirPassengers,FUN=mean))
191
192#generate a boxplot for AirPassengers data on monthly basis
193boxplot(AirPassengers~cycle(AirPassengers))
194
195Output:
196
197Console Output:
198> start(AirPassengers)
199[1] 1949 1
200>
201> #returns end interval
202> end(AirPassengers)
203[1] 1960 12
204>
205> #retunrs number of observations before pattern repeats
206> frequency(AirPassengers)
207[1] 12
208> summary(AirPassengers)
209 Min. 1st Qu. Median Mean 3rd Qu. Max.
210 104.0 180.0 265.5 280.3 360.5 622.0
211
212Plots:
213
214
215
216
217Practical 3
218
219Aim: Practical of Time Series Analysis.
220
221Description:
222
223Time series analysis consists of set of methods used to analyze various data facts or statistis from various characteristics of the data.
224Time series analysis is uscd for continuous data for example cconomic growth of an organization, share prices, sales temperature, weather etc.
225Time series analysis model has time factor "t" as an independent variable and the target is a dependent variable denoted by Y The output from the time scries model is a predicted value of Y at the given titne t. Time series is the process of recording of the data at regular interval of time.
226Time Series Components: There are various time series components which are as follows:
227Trend:
228It is considered to behavior of the feature at a particular amount of time, it can be categorized as increasing trend, decreasing trend or constant trend.
229When the particular feature value increases in particular amount of time it is increasing trend, similarly if it decreases it is decreasing trend and when it does not change over the period of time then it is constant trend.
230Seasonality:
231Seasonality a pattern which repcats at the constant frcquency. For example here the demand for the umbrellas will be in rainy season only.
232Cycles:
233Cycles are type of seasonality pattern but is does not repeat at regular frequency. Cycle can be generally considered as the task completion time.
234For example, in iterative model of software engincering every iteration can have different time requirement, but the crvery task has to undergo all stages in a single iteration.
235Forecasting:
236It is the processes of making prediction of the future based on the present and the past data most commonly by analysis of trends. Prediction is similar term to the forecasting but not cxactly the same.
237
238Source Code:
239
240# Get the data points in form of a R vector.
241rainfall <- c(799,1174.8,865.1,1334.6,635.4,918.5,685.5,998.6,784.2,985,882.8,1071)
242
243# Convert it to a time series object.
244rainfall.timeseries <- ts(rainfall,start = c(2012,1),frequency = 12)
245
246# Print the timeseries data.
247print(rainfall.timeseries)
248
249# Give the chart file a name.
250png(file = "rainfall.png")
251
252# Plot a graph of the time series.
253plot(rainfall.timeseries)
254
255# Save the file.
256dev.off()
257
258Output:
259
260Console Output:
261
262
263Plots:
264
265rainfall.png
266
267
268Practical 4
269
270Aim: Practical of k-means Clustering.
271
272Description:
273
274Some of the Key features of K-mneans are as follow:
275K-means is an exploratory data analysis technique.
276Implements nonhierarchical method of grouping objects logether.
277K-means delermines the centroid using Euclidean method for distance calculation.
278After calculating minimum distances groups of objects are created by considering the minimum distance.
279K-means clustering is a type of unsupervised learning, which is used when you have unlabeled data (i.e., data without defined categories or groups).
280The goal of this algonthm is to find groups in the data, with the number of groups represented by the variable K. The algorithm works iteratively to assign each data point to one of K groups based on the features that are provided.
281Data points are clustered based on feature similarity. The results of the K-means clustering algorithm are:
282The centroids of the K clusters, which can be used to label new data
283Labels for the training data (each data point is assigned to a single cluster) Rather than defining groups before looking at the data, clustering allows you to find and analyze the groups that have formed organically. The "Choosing K" section below describes how the number of groups can be determined.
284Each centroid of a cluster is a collection of feature values which define the resulting groups. Examining the centroid feature weights can be used to qualitatively interpret what kind of group each cluster represents.
285This introduction to the K-means clustering algorithm covers:
286Common busincss cases where K-means is used.
287The steps involved in running the algorithm.
288A Python example using delivery fleet data.
289
290Source Code:
291
292#loading dataset "iris"
293data(iris)
294
295#returns the variables names in dataset iris
296names(iris)
297
298#stores the subset of iris dataset without Species variable in symbol new_data
299new_data<-subset(iris,select=c(-Species))
300
301#forming clusters in dataset new_data using kmeans() where 3 incates number cluster to be formed
302cl<-kmeans(new_data,3)
303
304#assigning new_data values to data
305data<-new_data
306
307#apply function to all the elements of data and store the resulting vector in wss
308wss<-sapply(1:15,function(k){kmeans(data,k)$tot.withinss})
309
310#plot values in wss vector
311plot(1:15,wss,type="b",pch=19,frame=FALSE,xlab="Number of clusters K",ylab="Total within-clusters sums of squares")
312
313#importing cluster package
314library(cluster)
315
316#plotting clusters computed by kmeans function
317clusplot(new_data,cl$cluster,color=TRUE,shade=TRUE,labels=2,lines=0)
318
319# cl$cluster
320# cl$centers
321#hierarchical cluster analysis for dissimilarity structure of 3rd and 4th variable of iris dataset
322clusters<-hclust(dist(iris[,3:4]))
323
324#plotting Cluster Dendrogram
325plot(clusters,cex=0.65)
326
327#Dividing Dendrogram into 3 grounps
328clusterCut<-cutree(clusters,3)
329
330#printing the values of dendrogram groups w.r.t Scpecies
331table(clusterCut,iris$Species)
332
333Output:
334
335Console Output:
336names(iris)
337[1] "Sepal.Length" "Sepal.Width" "Petal.Length" "Petal.Width" "Species"
338table(clusterCut,iris$Species)
339
340clusterCut setosa versicolor virginica
341 1 50 0 0
342 2 0 21 50
343 3 0 29 0
344
345Plots:
346
347
348
349
350
351Practical 5
352
353Aim: Practical of Logistics Regression.
354
355Description:
356
357Logistic Regression was used in the biological sciences in early twentieth century. It was then used in many social science applications. Logistic Regression is used when the dependent variable(target) is categorical.
358For example,
359To predict whether an email is spam (1) or (0)
360Whether the tumor is malignant (1) or not (0)
361Consider a scenario where we need to classify whether an email is spam or not. If we use linear regression for this problem, there is a need for setting up a threshold based on which classification can be done.
362Say if the actual class is malignant, predicted continuous value 0.4 and the threshold value is 0.5, the data point will be classified as not malignant which can lead to serious consequence in real time.
363From this example, it can be inferred that linear regression is not suitable for classification problem. Linear regression is unbounded, and this brings logistic regression into picture. Their value strictly ranges from 0 to 1.
364
365Source Code:
366
367#install ISLR package(Introduction to Statistical Learning with Applications in R)
368install.packages("ISLR")
369
370# Call the Library ISLR and data file smarket
371library(ISLR)
372
373# List the variable names
374names(Smarket)
375
376# Dimensions and summary stats for continuous variables
377dim(Smarket)
378summary(Smarket)
379
380# Scatterplot Matrix
381pairs(Smarket)
382
383# To know more info about the data:
384`?`(Smarket)
385
386# Correlation matrix
387cor(Smarket[, -9])
388
389# Attach column names
390attach(Smarket)
391
392# plot multiple graphs in single plot row wise
393par(mfrow=c(1,1))
394
395# plot Volume
396plot(Volume)
397
398# Fitting GLM model
399glm.fits=glm(Direction~Lag1+Lag2+Lag3+Lag4+Lag5+Volume,data=Smarket,family=binomial)
400summary(glm.fits)
401
402# Coefficients from the GLM fit
403coef(glm.fits)
404summary(glm.fits)$coef
405
406# Only The 4th column in Coefficients, prob using z test
407summary(glm.fits)$coef[,4]
408
409# Generating probabilities using predict function and Type=response.
410glm.probs=predict(glm.fits,type="response")
411# Probabilities 1 through 10 are printed and the direction of prediction is up=1.
412glm.probs[1:10]
413contrasts(Direction)
414
415# Using the same predict function on all 1250 observations, we are creating the direction of the
416# market as Up if the prob exceeds 0.5 and downotherwise.
417# The table of predicted vs. direction is printed too.
418glm.pred=rep("Down",1250)
419glm.pred[glm.probs>.5]="Up"
420glm.probs[1:10]
421glm.pred[1:10]
422table(glm.pred,Direction)
423(507+145)/1250
424
425# Following divides the total true prediction by total to get
426# the portion of correct predictions in this table.
427mean(glm.pred==Direction)
428
429# The vector train pertains to all observationd prior to year 2005.
430train=(Year<2005)
431Smarket.2005=Smarket[!train,]
432dim(Smarket.2005) # Contains 252 observations and 9 variables
433Direction.2005=Direction[!train]
434
435#GLM Logistic regression for the training dataset using all 6 predictors.
436glm.fits=glm(Direction~Lag1+Lag2+Lag3+Lag4+Lag5+Volume,data=Smarket,family=binomial,subset=train)
437summary(glm.fits)
438glm.probs=predict(glm.fits,Smarket.2005,type="response")
439
440#direction calculation and mean direction of training data
441glm.pred=rep("Down",252)
442glm.pred[glm.probs>.5]="Up"
443
444# Table of actual vs. predicted directions
445table(glm.pred,Direction.2005)
446mean(glm.pred==Direction.2005)
447mean(glm.pred!=Direction.2005)
448
449# GLM Logistic regression model using only lag1 & lag2 as predictors and the training dataset,
450# generating proabilities, direction and mean direction
451glm.fits=glm(Direction~Lag1+Lag2,data=Smarket,family=binomial,subset=train)
452glm.probs=predict(glm.fits,Smarket.2005,type="response")
453
454# Dividing correct predictions by total to get the correct portion predicted
455mean(glm.pred==Direction.2005)
456
457(106+35)/252
458106/(106+35)
45976/(36+76)
460
461Output:
462
463Console Output:
464> names(Smarket)
465[1] "Year" "Lag1" "Lag2" "Lag3" "Lag4" "Lag5" "Volume" "Today"
466[9] "Direction"
467> dim(Smarket)
468[1] 1250 9
469> summary(Smarket)
470 Year Lag1 Lag2 Lag3 Lag4
471 Min. :2001 Min. :-4.922000 Min. :-4.922000 Min. :-4.922000 Min. :-4.922000
472 1st Qu.:2002 1st Qu.:-0.639500 1st Qu.:-0.639500 1st Qu.:-0.640000 1st Qu.:-0.640000
473 Median :2003 Median : 0.039000 Median : 0.039000 Median : 0.038500 Median : 0.038500
474 Mean :2003 Mean : 0.003834 Mean : 0.003919 Mean : 0.001716 Mean : 0.001636
475 3rd Qu.:2004 3rd Qu.: 0.596750 3rd Qu.: 0.596750 3rd Qu.: 0.596750 3rd Qu.: 0.596750
476 Max. :2005 Max. : 5.733000 Max. : 5.733000 Max. : 5.733000 Max. : 5.733000
477 Lag5 Volume Today Direction
478 Min. :-4.92200 Min. :0.3561 Min. :-4.922000 Down:602
479 1st Qu.:-0.64000 1st Qu.:1.2574 1st Qu.:-0.639500 Up :648
480 Median : 0.03850 Median :1.4229 Median : 0.038500
481 Mean : 0.00561 Mean :1.4783 Mean : 0.003138
482 3rd Qu.: 0.59700 3rd Qu.:1.6417 3rd Qu.: 0.596750
483 Max. : 5.73300 Max. :3.1525 Max. : 5.733000
484> # Correlation matrix
485> cor(Smarket[, -9])
486 Year Lag1 Lag2 Lag3 Lag4 Lag5 Volume Today
487Year 1.00000000 0.029699649 0.030596422 0.033194581 0.035688718 0.029787995 0.53900647 0.030095229
488Lag1 0.02969965 1.000000000 -0.026294328 -0.010803402 -0.002985911 -0.005674606 0.04090991 -0.026155045
489Lag2 0.03059642 -0.026294328 1.000000000 -0.025896670 -0.010853533 -0.003557949 -0.04338321 -0.010250033
490Lag3 0.03319458 -0.010803402 -0.025896670 1.000000000 -0.024051036 -0.018808338 -0.04182369 -0.002447647
491Lag4 0.03568872 -0.002985911 -0.010853533 -0.024051036 1.000000000 -0.027083641 -0.04841425 -0.006899527
492Lag5 0.02978799 -0.005674606 -0.003557949 -0.018808338 -0.027083641 1.000000000 -0.02200231 -0.034860083
493Volume 0.53900647 0.040909908 -0.043383215 -0.041823686 -0.048414246 -0.022002315 1.00000000 0.014591823
494Today 0.03009523 -0.026155045 -0.010250033 -0.002447647 -0.006899527 -0.034860083 0.01459182 1.000000000
495> glm.fits=glm(Direction~Lag1+Lag2+Lag3+Lag4+Lag5+Volume,data=Smarket,family=binomial)
496> summary(glm.fits)
497
498Call:
499glm(formula = Direction ~ Lag1 + Lag2 + Lag3 + Lag4 + Lag5 +
500 Volume, family = binomial, data = Smarket)
501
502Deviance Residuals:
503 Min 1Q Median 3Q Max
504-1.446 -1.203 1.065 1.145 1.326
505
506Coefficients:
507 Estimate Std. Error z value Pr(>|z|)
508(Intercept) -0.126000 0.240736 -0.523 0.601
509Lag1 -0.073074 0.050167 -1.457 0.145
510Lag2 -0.042301 0.050086 -0.845 0.398
511Lag3 0.011085 0.049939 0.222 0.824
512Lag4 0.009359 0.049974 0.187 0.851
513Lag5 0.010313 0.049511 0.208 0.835
514Volume 0.135441 0.158360 0.855 0.392
515
516(Dispersion parameter for binomial family taken to be 1)
517
518 Null deviance: 1731.2 on 1249 degrees of freedom
519Residual deviance: 1727.6 on 1243 degrees of freedom
520AIC: 1741.6
521
522Number of Fisher Scoring iterations: 3
523> coef(glm.fits)
524 (Intercept) Lag1 Lag2 Lag3 Lag4 Lag5 Volume
525-0.126000257 -0.073073746 -0.042301344 0.011085108 0.009358938 0.010313068 0.135440659
526> summary(glm.fits)$coef
527 Estimate Std. Error z value Pr(>|z|)
528(Intercept) -0.126000257 0.24073574 -0.5233966 0.6006983
529Lag1 -0.073073746 0.05016739 -1.4565986 0.1452272
530Lag2 -0.042301344 0.05008605 -0.8445733 0.3983491
531Lag3 0.011085108 0.04993854 0.2219750 0.8243333
532Lag4 0.009358938 0.04997413 0.1872757 0.8514445
533Lag5 0.010313068 0.04951146 0.2082966 0.8349974
534Volume 0.135440659 0.15835970 0.8552723 0.3924004
535> summary(glm.fits)$coef[,4]
536(Intercept) Lag1 Lag2 Lag3 Lag4 Lag5 Volume
537 0.6006983 0.1452272 0.3983491 0.8243333 0.8514445 0.8349974 0.3924004
538> glm.probs=predict(glm.fits,type="response")
539> glm.probs[1:10]
540 1 2 3 4 5 6 7 8 9 10
5410.5070841 0.4814679 0.4811388 0.5152224 0.5107812 0.5069565 0.4926509 0.5092292 0.5176135 0.4888378
542> contrasts(Direction)
543 Up
544Down 0
545Up 1
546> glm.pred=rep("Down",1250)
547> glm.pred[glm.probs>.5]="Up"
548> glm.probs[1:10]
549 1 2 3 4 5 6 7 8 9 10
5500.5070841 0.4814679 0.4811388 0.5152224 0.5107812 0.5069565 0.4926509 0.5092292 0.5176135 0.4888378
551> glm.pred[1:10]
552 [1] "Up" "Down" "Down" "Up" "Up" "Up" "Down" "Up" "Up" "Down"
553> table(glm.pred,Direction)
554 Direction
555glm.pred Down Up
556 Down 145 141
557 Up 457 507
558> (507+145)/1250
559[1] 0.5216
560> mean(glm.pred==Direction)
561[1] 0.5216
562> train=(Year<2005)
563> Smarket.2005=Smarket[!train,]
564> dim(Smarket.2005) # Contains 252 observations and 9 variables
565[1] 252 9
566> Direction.2005=Direction[!train]
567> glm.fits=glm(Direction~Lag1+Lag2+Lag3+Lag4+Lag5+Volume,data=Smarket,family=binomial,subset=train)
568> summary(glm.fits)
569
570Call:
571glm(formula = Direction ~ Lag1 + Lag2 + Lag3 + Lag4 + Lag5 +
572 Volume, family = binomial, data = Smarket, subset = train)
573
574Deviance Residuals:
575 Min 1Q Median 3Q Max
576-1.302 -1.190 1.079 1.160 1.350
577
578Coefficients:
579 Estimate Std. Error z value Pr(>|z|)
580(Intercept) 0.191213 0.333690 0.573 0.567
581Lag1 -0.054178 0.051785 -1.046 0.295
582Lag2 -0.045805 0.051797 -0.884 0.377
583Lag3 0.007200 0.051644 0.139 0.889
584Lag4 0.006441 0.051706 0.125 0.901
585Lag5 -0.004223 0.051138 -0.083 0.934
586Volume -0.116257 0.239618 -0.485 0.628
587
588(Dispersion parameter for binomial family taken to be 1)
589 Null deviance: 1383.3 on 997 degrees of freedom
590Residual deviance: 1381.1 on 991 degrees of freedom
591AIC: 1395.1
592Number of Fisher Scoring iterations: 3
593> glm.probs=predict(glm.fits,Smarket.2005,type="response")
594> glm.pred=rep("Down",252)
595> glm.pred[glm.probs>.5]="Up"
596> table(glm.pred,Direction.2005)
597 Direction.2005
598glm.pred Down Up
599 Down 77 97
600 Up 34 44
601> mean(glm.pred==Direction.2005)
602[1] 0.4801587
603> mean(glm.pred!=Direction.2005)
604[1] 0.5198413
605> glm.fits=glm(Direction~Lag1+Lag2,data=Smarket,family=binomial,subset=train)
606> glm.probs=predict(glm.fits,Smarket.2005,type="response")
607> mean(glm.pred==Direction.2005)
608[1] 0.4801587
609> (106+35)/252
610[1] 0.5595238
611> 106/(106+35)
612[1] 0.751773
613> 76/(36+76)
614[1] 0.6785714
615
616
617Plots:
618
619
620
621Practical 6
622
623Aim: Practical of Decision Tree.
624
625Description:
626
627A Decision Tree is an algorithm used for supervised learning problems such as classification or regression. A decision tree or a classification tree is a tree in which cach internal (nonleaf) node is labeled with an input feature.
628The arcs coming from a node labeled with a feature are labeled with cach of the possible values of the feature. Each leaf of the tree is labeled with a class or a probability distribution over the classes.
629A tree can be "learned" by splitting the source set into subsets based on attribute value test. This process is repeated on cach derived subset in a recursive manner called recursive partitioning.
630The recursion is completed when the subset at a node has all the same value of the target variable, or when splitting no longer adds value to the predictions.
631This process of top-down induction of decision trees is an example of a greedy algorithm, and it is the most common strategy for learning decision trees. Decision trees used in data mining are of two main types
632Classification tree : when the response is a nominal variable, for example if an email is spam or not.
633Regression tree : when the predicted outcome can be considered a real number (e.g. the salary of a worker).
634Decision trees are a simple method, and as such has some problems. One of this issues is the high variance in the resulting models that decision trees produce. In order to alleviate this problem, ensemble methods of decision trees were developed. There are two groups of ensemble methods currently used extensively
635Bagging decision trees : These trees are used to build multiple decision trees by repeatedly resampling training data with replacement, and voting the trees for a consensus prediction. This algorithm has been called random forest.
636Boosting decision trees : Gradient boosting combines weak learners; in this case, decision trees into a single strong learner, in an iterative fashion. It fits a weak tree to the data and iteratively keeps fitting weak learners in order to correct the error of the previous model.
637
638Source Code:
639
640# Load the party package. It will automatically load other
641# dependent packages.
642install.packages("party")
643library(party)
644
645# Create the input data frame.
646input.dat <- readingSkills[c(1:105),]
647
648# Give the chart file a name.
649png(file = "decision_tree.png")
650
651# Create the tree.
652output.tree <- ctree(
653 nativeSpeaker ~ age + shoeSize + score,
654 data = input.dat)
655
656# Plot the tree.
657plot(output.tree)
658
659# Save the file.
660dev.off()
661
662Output:
663
664Console Output:
665> # Load the party package. It will automatically load other
666> # dependent packages.
667> install.packages("party")
668WARNING: Rtools is required to build R packages but is not currently installed. Please download and install the appropriate version of Rtools before proceeding:
669
670https://cran.rstudio.com/bin/windows/Rtools/
671Installing package into ‘C:/Users/VRUTIKA/Documents/R/win-library/3.6’
672(as ‘lib’ is unspecified)
673trying URL 'https://cran.rstudio.com/bin/windows/contrib/3.6/party_1.3-3.zip'
674Content type 'application/zip' length 901670 bytes (880 KB)
675downloaded 880 KB
676
677package ‘party’ successfully unpacked and MD5 sums checked
678
679The downloaded binary packages are in
680 C:\Users\VRUTIKA\AppData\Local\Temp\Rtmp88ZbXh\downloaded_packages
681> library(party)
682Loading required package: grid
683Loading required package: mvtnorm
684Loading required package: modeltools
685Loading required package: stats4
686Loading required package: strucchange
687Loading required package: zoo
688
689Attaching package: ‘zoo’
690
691The following objects are masked from ‘package:base’:
692
693 as.Date, as.Date.numeric
694
695Loading required package: sandwich
696Warning messages:
6971: package ‘party’ was built under R version 3.6.2
6982: package ‘strucchange’ was built under R version 3.6.2
6993: package ‘zoo’ was built under R version 3.6.2
7004: package ‘sandwich’ was built under R version 3.6.2
701>
702> # Create the input data frame.
703> input.dat <- readingSkills[c(1:105),]
704>
705> # Give the chart file a name.
706> png(file = "decision_tree.png")
707>
708> # Create the tree.
709> output.tree <- ctree(nativeSpeaker ~ age + shoeSize + score, data = input.dat)
710>
711> # Plot the tree.
712> plot(output.tree)
713>
714> # Save the file.
715> dev.off()
716null device
717 1
718
719Plots:
720decision_tree.png
721
722
723
724
725
726
727
728Practical 7
729
730Aim: Practical of Hypothesis Testing.
731
732Description:
733
734Hypothesis Tests, or Statistical Hypothesis Testing, is a technique used to compare two datasets, or a sample from a dataset. It is a statistical inference method so, in the end of the test, you'll draw a conclusion - you'll infer something- about the characteristics of what you're comparing.
735A statistical hypothesis is an assumption made by the researcher about the population of data collected for any experiment. It is not mandatory for this assumption to be true every time. Hypothesis testing is, in a way, the formal way of validating the hypothesis made by the researcher.
736In order to validate a hypothesis, it will consider the entire population into account. However, this is not possible practically. Thus, to validate a hypothesis, it will use random samples from a population. On the basis of the result from testing over the sample data, it either selects or rejects the hypothesis
737Statistical Hypothesis can be categorized into 2 types as below:
738Null Hypothesis : Hypothesis lests are used to test the validity of a claim that is made about a population. This claim that's on trial, in essence, is called the null hypothesis. The null hypothesis testing is denoted by H0.
739Alternative Hypothesis : The alternative hypothesis is the one you would believe if the null hypothesis is concluded to be untrue. The evidence in the trial is your data and the statistics that go along with it. The alternative hypothesis testing is denoted by H1 or Ha.
740
741Source Code:
742
743# generate a sequence(1,2,3...19,20)
744dataf<-seq(1,20,by=1)
745dataf
746
747#Calculate mean
748mean(dataf)
749
750#Calculate Standard Deviation
751sd(dataf)
752
753# x = dataf
754#a (non-empty) numeric vector of data values.
755#alternative a character string specifying the alternative hypothesis, must be one of "two.sided" (default),
756#"greater" or "less". You can specify just the initial letter.
757#mu = 10
758#a number indicating the true value of the mean (or difference in means if you are
759#performing a two sample test).
760
761a<-t.test(dataf,alternate="two.sided",mu=10,conf.int=0.95)
762a
763
764# print p-value and statistics for test
765a$p.value
766a$statistic
767
768# t-test formula
769(10.5-10)/(sd(dataf)/sqrt(length(dataf)))
770
771length(dataf)=1
772length(dataf)
773dataf
774dataf<-seq(1,20,by=1)
775length(dataf)-1
776
777Output:
778
779Console Output:
780> data("warpbreaks")
781> head(warpbreaks)
782 breaks wool tension
7831 26 A L
7842 30 A L
7853 54 A L
7864 25 A L
7875 70 A L
7886 52 A L
789> summary(warpbreaks)
790 breaks wool tension
791 Min. :10.00 A:27 L:18
792 1st Qu.:18.25 B:27 M:18
793 Median :26.00 H:18
794 Mean :28.15
795 3rd Qu.:34.00
796 Max. :70.00
797>
798> # Fit an analysis of variance model by a call to lm for each stratum.
799> #formula = breaks~wool+tension
800> #A formula specifying the model.
801> #data = warpbreaks
802> #A data frame in which the variables specified in the formula will be found.
803> Model_1<-aov(breaks~wool+tension,data=warpbreaks)
804> summary(Model_1)
805 Df Sum Sq Mean Sq F value Pr(>F)
806wool 1 451 450.7 3.339 0.07361 .
807tension 2 2034 1017.1 7.537 0.00138 **
808Residuals 50 6748 135.0
809---
810Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1
811>
812> plot(Model_1)
813Hit <Return> to see next plot: Model_2<-aov(breaks~wool+tension+wool:tension,data=warpbreaks)
814Hit <Return> to see next plot: summary(Model_2)
815Hit <Return> to see next plot: plot(Model_2)
816Hit <Return> to see next plot: train=(Year<2005)
817> # generate a sequence(1,2,3...19,20)
818> dataf<-seq(1,20,by=1)
819> dataf
820 [1] 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
821>
822> #Calculate mean
823> mean(dataf)
824[1] 10.5
825>
826> #Calculate Standard Deviation
827> sd(dataf)
828[1] 5.91608
829>
830> # x = dataf
831> #a (non-empty) numeric vector of data values.
832> #alternative a character string specifying the alternative hypothesis, must be one of "two.sided" (default),
833> #"greater" or "less". You can specify just the initial letter.
834> #mu = 10
835> #a number indicating the true value of the mean (or difference in means if you are
836> #performing a two sample test).
837>
838> a<-t.test(dataf,alternate="two.sided",mu=10,conf.int=0.95)
839> a
840
841 One Sample t-test
842
843data: dataf
844t = 0.37796, df = 19, p-value = 0.7096
845alternative hypothesis: true mean is not equal to 10
84695 percent confidence interval:
847 7.731189 13.268811
848sample estimates:
849mean of x
850 10.5
851
852>
853> # print p-value and statistics for test
854> a$p.value
855[1] 0.7096465
856> a$statistic
857 t
8580.3779645
859>
860> # t-test formula
861> (10.5-10)/(sd(dataf)/sqrt(length(dataf)))
862[1] 0.3779645
863>
864> length(dataf)=1
865> length(dataf)
866[1] 1
867> dataf
868[1] 1
869> dataf<-seq(1,20,by=1)
870> length(dataf)-1
871[1] 19
872
873Practical 8
874
875Aim: Practical of Analysis of Variance.
876
877Description:
878
879It is property of different predictive models with a lower bias for parameter estimation have a higher variance for given dataset and vice versa.
880Bias: It is an error from the erroneous assumptions made during the learning of an algorithm. Higher bias can lead to missing of the relevant data of feature needed for the targeted value 1 other words it leads to underfitting.
881Variance : It is an crror form the sensitivity of an algorithm where small fluctuation of samples in the training set can lead to an error. High variance in an algorithm can lead to generation of random noise in the training data and can deviate the output.
882In other words it leads to overfitting.
883Bias-Variance trade off is generally faced in supervised nlgorithms due to which the accuracy and generalization both cannot be ndopted in the model.
884Any model can be bad or not optimal because of two main reasons:
885It is not accurate.
886It does not match the data well.
887The reason for the first is bias and other is variance. If models are made complex then it leads to Improvement in the bias but such models are very costly which leads to higher variance, whercas when the model is made more specific to the data then the variance will be reduced but on the other hand it leads to higher bias.
888Variance is the amount that the estimate of the target functions which will change if different training data was used. Algorithm should have some variance.
889Low variance provides small changes to tbe estimate of the target functions with the changes to thc training dataset. High variance provides large changcs to the estimale of the target function with changes to the training set. Whenever the model is choosing with low complexity and low variance automatically the high variance is introduced.
890
891Source Code:
892
893# loading warpbreaks
894data("warpbreaks")
895head(warpbreaks)
896summary(warpbreaks)
897
898# Fit an analysis of variance model by a call to lm for each stratum.
899#formula = breaks~wool+tension
900#A formula specifying the model.
901#data = warpbreaks
902#A data frame in which the variables specified in the formula will be found.
903Model_1<-aov(breaks~wool+tension,data=warpbreaks)
904summary(Model_1)
905
906plot(Model_1)
907
908#formula = breaks~wool+tension+wool:tension
909Model_2<-aov(breaks~wool+tension+wool:tension,data=warpbreaks)
910summary(Model_2)
911plot(Model_2)
912
913Output:
914
915Console Output:
916> # loading warpbreaks
917> data("warpbreaks")
918> head(warpbreaks)
919 breaks wool tension
9201 26 A L
9212 30 A L
9223 54 A L
9234 25 A L
9245 70 A L
9256 52 A L
926> summary(warpbreaks)
927 breaks wool tension
928 Min. :10.00 A:27 L:18
929 1st Qu.:18.25 B:27 M:18
930 Median :26.00 H:18
931 Mean :28.15
932 3rd Qu.:34.00
933 Max. :70.00
934>
935> # Fit an analysis of variance model by a call to lm for each stratum.
936> #formula = breaks~wool+tension
937> #A formula specifying the model.
938> #data = warpbreaks
939> #A data frame in which the variables specified in the formula will be found.
940> Model_1<-aov(breaks~wool+tension,data=warpbreaks)
941> summary(Model_1)
942 Df Sum Sq Mean Sq F value Pr(>F)
943wool 1 451 450.7 3.339 0.07361 .
944tension 2 2034 1017.1 7.537 0.00138 **
945Residuals 50 6748 135.0
946---
947Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1
948>
949> plot(Model_1)
950Hit <Return> to see next plot: Model_2<-aov(breaks~wool+tension+wool:tension,data=warpbreaks)
951Hit <Return> to see next plot: summary(Model_2)
952Hit <Return> to see next plot: plot(Model_2)
953Hit <Return> to see next plot: train=(Year<2005)
954
955
956Plots:
957
958
959
960
961
962
963Practical 9
964
965Aim: Practical of Principal Component Analysis.
966
967Description:
968
969A Principal Component Analysis (PCA) can be considered as a rotation of the axes of the original variable coordinate system to new orthogonal axes, called as the principal axes, such that the new axes coincide with directions of maximum variation of the original observations.
970PCA is also called as Karhunen-Loeve or K-L Method. This is method of dimensionality reduction searches for k n-dimensional orthogonal vectors that can best used to represent the dala. The original data are thus projected onto a much smaller space which results to dimensionality reduction.
971Basic Procedure Followed in PCA
972Firstly the input data is normalized so that all the features fall into the similar range. This step is performed in order to normalize the large and small domain values.
973PCA then computes K ortho normal vectors which provide a basis for normalized input data. These are unit vectors and are perpendicular lo each other's. These vectors are also referred as the Principal Components.
974The Principal Components are then stored in order of decreasing strength.
975Principal components provide new set of axes.
976As the components are stored in the decreasing order of strength, the size of the data can be reduced by climinating the weaker components.
977Source Code:
978
979# loading dataset iris
980data("iris")
981head(iris)
982library()
983
984# to find principal component
985mypr<-prcomp(iris[,-5],scale=T)
986
987# to understand use of scale
988plot(iris$Sepal.Length,iris$Sepal.Width)
989plot(scale(iris$Sepal.Length),scale(iris$Sepal.Width))
990mypr
991summary(mypr)
992plot(mypr,type="l")
993
994# plot the biplot showing first two PC’s and the original feature vectors in this
995#2D space i.e original feature vectors as linear combination of first two PC’s
996biplot(mypr,scale=0)
997
998# extract pc scores
999str(mypr)
1000mypr$x
1001iris2<-cbind(mypr$x[,1:2])
1002head(iris2)
1003
1004# find co-relations(x=iris[,5], y=iris[,1:2])
1005cor(iris[,-5],iris2[,1:2])
1006
1007# install pls package
1008install.packages("pls")
1009library(pls)
1010names(iris)
1011
1012Output:
1013
1014Console Output:
1015> data("iris")
1016> head(iris)
1017 Sepal.Length Sepal.Width Petal.Length Petal.Width Species
10181 5.1 3.5 1.4 0.2 setosa
10192 4.9 3.0 1.4 0.2 setosa
10203 4.7 3.2 1.3 0.2 setosa
10214 4.6 3.1 1.5 0.2 setosa
10225 5.0 3.6 1.4 0.2 setosa
10236 5.4 3.9 1.7 0.4 setosa
1024> mypr<-prcomp(iris[,-5],scale=T)
1025>
1026> # to understand use of scale
1027> plot(iris$Sepal.Length,iris$Sepal.Width)
1028> plot(scale(iris$Sepal.Length),scale(iris$Sepal.Width))
1029> mypr
1030Standard deviations (1, .., p=4):
1031[1] 1.7083611 0.9560494 0.3830886 0.1439265
1032
1033Rotation (n x k) = (4 x 4):
1034 PC1 PC2 PC3 PC4
1035Sepal.Length 0.5210659 -0.37741762 0.7195664 0.2612863
1036Sepal.Width -0.2693474 -0.92329566 -0.2443818 -0.1235096
1037Petal.Length 0.5804131 -0.02449161 -0.1421264 -0.8014492
1038Petal.Width 0.5648565 -0.06694199 -0.6342727 0.5235971
1039> summary(mypr)
1040Importance of components:
1041 PC1 PC2 PC3 PC4
1042Standard deviation 1.7084 0.9560 0.38309 0.14393
1043Proportion of Variance 0.7296 0.2285 0.03669 0.00518
1044Cumulative Proportion 0.7296 0.9581 0.99482 1.00000
1045> plot(mypr,type="l")
1046> str(mypr)
1047List of 5
1048 $ sdev : num [1:4] 1.708 0.956 0.383 0.144
1049 $ rotation: num [1:4, 1:4] 0.521 -0.269 0.58 0.565 -0.377 ...
1050 ..- attr(*, "dimnames")=List of 2
1051 .. ..$ : chr [1:4] "Sepal.Length" "Sepal.Width" "Petal.Length" "Petal.Width"
1052 .. ..$ : chr [1:4] "PC1" "PC2" "PC3" "PC4"
1053 $ center : Named num [1:4] 5.84 3.06 3.76 1.2
1054 ..- attr(*, "names")= chr [1:4] "Sepal.Length" "Sepal.Width" "Petal.Length" "Petal.Width"
1055 $ scale : Named num [1:4] 0.828 0.436 1.765 0.762
1056 ..- attr(*, "names")= chr [1:4] "Sepal.Length" "Sepal.Width" "Petal.Length" "Petal.Width"
1057 $ x : num [1:150, 1:4] -2.26 -2.07 -2.36 -2.29 -2.38 ...
1058 ..- attr(*, "dimnames")=List of 2
1059 .. ..$ : NULL
1060 .. ..$ : chr [1:4] "PC1" "PC2" "PC3" "PC4"
1061 - attr(*, "class")= chr "prcomp"
1062> iris2<-cbind(mypr$x[,1:2])
1063> head(iris2)
1064 PC1 PC2
1065[1,] -2.257141 -0.4784238
1066[2,] -2.074013 0.6718827
1067[3,] -2.356335 0.3407664
1068[4,] -2.291707 0.5953999
1069[5,] -2.381863 -0.6446757
1070[6,] -2.068701 -1.4842053
1071>
1072> # find co-relations(x=iris[,5], y=iris[,1:2])
1073> cor(iris[,-5],iris2[,1:2])
1074 PC1 PC2
1075Sepal.Length 0.8901688 -0.36082989
1076Sepal.Width -0.4601427 -0.88271627
1077Petal.Length 0.9915552 -0.02341519
1078Petal.Width 0.9649790 -0.06399985
1079>
1080> # install pls package
1081> install.packages("pls")
1082WARNING: Rtools is required to build R packages but is not currently installed. Please download and install the appropriate version of Rtools before proceeding:
1083
1084https://cran.rstudio.com/bin/windows/Rtools/
1085Installing package into ‘C:/Users/VRUTIKA/Documents/R/win-library/3.6’
1086(as ‘lib’ is unspecified)
1087trying URL 'https://cran.rstudio.com/bin/windows/contrib/3.6/pls_2.7-2.zip'
1088Content type 'application/zip' length 1230513 bytes (1.2 MB)
1089downloaded 1.2 MB
1090
1091package ‘pls’ successfully unpacked and MD5 sums checked
1092
1093The downloaded binary packages are in
1094 C:\Users\VRUTIKA\AppData\Local\Temp\RtmpwLgCMC\downloaded_packages
1095> library(pls)
1096
1097Attaching package: ‘pls’
1098
1099The following object is masked from ‘package:stats’:
1100
1101 loadings
1102
1103Warning message:
1104package ‘pls’ was built under R version 3.6.2
1105> names(iris)
1106[1] "Sepal.Length" "Sepal.Width" "Petal.Length" "Petal.Width" "Species"
1107
1108Plots:
1109
1110
1111
1112
1113
1114Name: Sachin Vishwakarma Subject: ADBMS
1115
1116Class: T.Y.BSC (COMPUTER SCIENCE) Roll No: 48
1117 Paper III
1118 INDEX
1119NO
1120DATE
1121TITLE
1122PAGE NO
1123SIGN
1124
1125
11261
112716/11/2019
1128Demonstrate distributed databases environment based on vertical fragments.
1129
1130
1131
1132
1133
1134
11352
113623/11/2019
1137Demonstrate distributed databases environment based on horizontal fragments.
1138
1139
1140
1141
1142
1143
11443
114501/12/2019
1146Demonstrate distributed databases environment based on replication of global conceptual schema.
1147
1148
1149
1150
1151
1152
11534
115406/01/2020
1155Object Oriented Databases
1156
1157
1158
1159
1160
1161
1162
1163
11645
116513/01/2020
1166Create different types that include attributes and methods. Demonstrate insert, update and delete operations on these tables.
1167
1168
1169
1170
1171
1172
1173
1174
11756
117620/01/2020
1177Create a temporal database and issue queries on it.
1178
1179
1180
1181
1182
1183
1184
1185
11867
118727/01/2020
1188Formulate a database using active rules with row and statement level.
1189
1190
1191
1192
1193
1194
11958
119603/02/2020
1197Formulate a database using active rules with row and statement level.
1198
1199
1200
1201
1202
1203
1204
1205
12069
120710/02/2020
1208Create a XML data base and demonstrate insert, update and delete operations on these tables. Issue queries on it.
1209
1210
1211
1212
1213
1214
1215
1216
1217Practical-1,2,3
1218//For a given a global conceptual schema, divide the schema into vertical fragments and place them on different nodes. Execute queries on these fragments that will demonstrate distributed databases environment.
1219
1220// For a given a global conceptual schema, divide the schema into horizontal fragments and place them on different nodes. Execute queries on these fragments that will demonstrate distributed databases environment.
1221
1222// Place the replication of global conceptual schema on different nodes and execute queries that will demonstrate distributed databases environment.
1223
1224System
1225SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 30 08:19:52 2018
1226Copyright (c) 1982, 2010, Oracle. All rights reserved.
1227SQL>
1228Enter user-name: system
1229Enter password:
1230Connected.
1231SQL> CREATE USER user1 IDENTIFIED BY user1;
1232User created.
1233SQL> CREATE USER user2 IDENTIFIED BY user2;
1234User created.
1235SQL> GRANT CONNECT,RESOURCE TO user1;
1236Grant succeeded.
1237SQL> GRANT CONNECT,RESOURCE TO user2;
1238Grant succeeded.
1239SQL> CREATE PUBLIC DATABASE LINK linksys CONNECT TO system IDENTIFIED BY server USING 'XE';
1240Database link created.
1241SQL> CREATE PUBLIC DATABASE LINK linku1 CONNECT TO user1 IDENTIFIED BY user1 USING 'XE';
1242Database link created.
1243SQL> CREATE PUBLIC DATABASE LINK linku2 CONNECT TO user2 IDENTIFIED BY user2 USING 'XE';
1244Database link created.
1245SQL> CREATE TABLE Emp(Eno NUMBER PRIMARY KEY,Ename VARCHAR2(10),Address VARCHAR2(10),Email VARCHAR2(18),Salary NUMBER);
1246Table created.
1247SQL> INSERT INTO Emp VALUES(&Eno,'&Ename','&Address','&Email',&Salary);
1248Enter value for eno: 01
1249Enter value for ename: Rizwan
1250Enter value for address: Kurla
1251Enter value for email: rizwan@gmail.com
1252Enter value for salary: 6500
1253old 1: INSERT INTO Emp VALUES(&Eno,'&Ename','&Address','&Email',&Salary)
1254new 1: INSERT INTO Emp VALUES(01,'Rizwan','Kurla','rizwan@gmail.com',6500)
12551 row created.
1256SQL> /
1257Enter value for eno: 02
1258Enter value for ename: Pintu
1259Enter value for address: Naupada
1260Enter value for email: pintu@yahoo.com
1261Enter value for salary: 6000
1262old 1: INSERT INTO Emp VALUES(&Eno,'&Ename','&Address','&Email',&Salary)
1263new 1: INSERT INTO Emp VALUES(02,'Pintu','Naupada','pintu@yahoo.com',6000)
12641 row created.
1265SQL> /
1266Enter value for eno: 03
1267Enter value for ename: John
1268Enter value for address: Dadar
1269Enter value for email: john@outlook.com
1270Enter value for salary: 5000
1271old 1: INSERT INTO Emp VALUES(&Eno,'&Ename','&Address','&Email',&Salary)
1272new 1: INSERT INTO Emp VALUES(03,'John','Dadar','john@outlook.com',5000)
12731 row created.
1274SQL> /
1275Enter value for eno: 04
1276Enter value for ename: Abhishek
1277Enter value for address: Ghatkopar
1278Enter value for email: abhi@live.com
1279Enter value for salary: 4800
1280old 1: INSERT INTO Emp VALUES(&Eno,'&Ename','&Address','&Email',&Salary)
1281new 1: INSERT INTO Emp VALUES(04,'Abhishek','Ghatkopar','abhi@live.com',4800)
12821 row created.
1283SQL> /
1284Enter value for eno: 05
1285Enter value for ename: Vikas
1286Enter value for address: Bandra
1287Enter value for email: vikas@gmail.com
1288Enter value for salary: 4700
1289old 1: INSERT INTO Emp VALUES(&Eno,'&Ename','&Address','&Email',&Salary)
1290new 1: INSERT INTO Emp VALUES(05,'Vikas','Bandra','vikas@gmail.com',4700)
12911 row created.
1292SQL> SELECT * FROM Emp;
1293 ENO ENAME ADDRESS EMAIL SALARY
1294---------- ---------- ---------- ------------------ ----------
1295 1 Rizwan Kurla rizwan@gmail.com 6500
1296 2 PintuNaupada pintu@yahoo.com 6000
1297 3 John Dadar john@outlook.com 5000
1298 4 Abhishek Ghatkopar abhi@live.com 4800
1299 5 Vikas Bandra vikas@gmail.com 4700
1300SQL> COMMIT;
1301Commit complete.
1302User1
1303SQL> CREATE TABLE Emp1 AS SELECT * FROM Emp@linksys WHERE Eno <=3;
1304Table created.
1305SQL> SELECT * FROM Emp1;
1306 ENO ENAME ADDRESS EMAIL SALARY
1307---------- ---------- ---------- ------------------ ----------
1308 1 Rizwan Kurla rizwan@gmail.com 6500
1309 2 PintuNaupada pintu@yahoo.com 6000
1310 3 John Dadar john@outlook.com 5000
1311User2
1312SQL> CREATE TABLE Emp2 AS SELECT * FROM Emp@linksys WHERE Eno BETWEEN 4 AND 5;
1313Table created.
1314SQL> SELECT * FROM Emp2;
1315 ENO ENAME ADDRESS EMAIL SALARY
1316---------- ---------- ---------- ------------------ ----------
1317 4 Abhishek Ghatkopar abhi@live.com 4800
1318 5 Vikas Bandra vikas@gmail.com 4700
1319Q. Find the salary of all the employees.
1320SQL> SELECT Salary FROM Emp1 UNION SELECT Salary FROM Emp2;
1321 SALARY
1322----------
1323 4700
1324 4800
1325 5000
1326 6000
1327 6500
1328
1329Q. Find the email of all the employees where salary is greater than 5000.
1330SQL> SELECT Email FROM Emp1 WHERE Salary > 5000 UNION SELECT Email FROM Emp2 WHERE Salary > 5000;
1331EMAIL
1332------------------
1333pintu@yahoo.com
1334rizwan@gmail.com
1335Q. Find the employee name and email where employee number is known.
1336SQL> SELECT Ename, Email FROM Emp1 WHERE Eno = 4 UNION SELECT Ename, Email FROM Emp2 WHERE Eno = 04;
1337ENAME EMAIL
1338---------- ------------------
1339Abhishek abhi@live.com
1340Q. Find the employee name and address where employee number is known.
1341SQL> SELECT Ename,Address FROM Emp1 WHERE Eno = 01 UNION SELECT Ename,Address FROM Emp2 WHERE Eno = 05;
1342ENAME ADDRESS
1343---------- ----------
1344Rizwan Kurla
1345
1346SQL> CREATE OR REPLACE TRIGGER temp
13472 AFTER INSERT ON Emp FOR EACH ROW
13483 BEGIN
13494 INSERT INTO Emp1@linku1 VALUES(:new.Eno, :new.Ename, :new.Address);
13505 INSERT INTO Emp2@linku2 VALUES(:new.Eno, :new.Email, :new.Salary);
13516 END;
13527 /
1353Trigger created.
1354SQL> COMMIT;
1355Commit complete.
1356User1
1357SQL> CREATE TABLE Emp1 AS SELECT Eno,Ename,Address FROM Emp@linksys;
1358Table created.
1359SQL> SELECT * FROM Emp1;
1360 ENO ENAME ADDRESS
1361---------- ---------- ----------
1362 1 Rizwan Kurla
1363 2 PintuNaupada
1364 3 John Dadar
1365 4 Abhishek Ghatkopar
1366 5 Vikas Bandra
1367
1368User2
1369SQL> CREATE TABLE Emp2 AS SELECT Eno,Email,Salary FROM Emp@linksys;
1370Table created.
1371SQL> SELECT * FROM Emp2;
1372 ENO EMAIL SALARY
1373---------- ------------------ ----------
1374 1 rizwan@gmail.com 6500
1375 2 pintu@yahoo.com 6000
1376 3 john@outlook.com 5000
1377 4 abhi@live.com 4800
1378 5 vikas@gmail.com 4700
1379SQL>
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389Practical No: 4
1390
1391Topic: Object Oriented Databases
1392
1393Problem Statement:
1394Using Object Oriented databases create the following types:
1395
1396AddrType (Pincode: number, Street :char, City : char, state :char)
1397BranchType (address: AddrType, phone1: integer,phone2: integer )
1398AuthorType (name:char,addr AddrType)
1399PublisherType (name: char, addr: AddrType, branches: BranchTableType
1400AuthorListType as varray, which is a reference to AuthorType
1401
1402Next create the following tables:
1403
1404 f) BranchTableType of BranchType
1405 g) authors of AuthorType
1406 h) books(title: varchar, year : date, published_by ref PublisherType,authors AuthorListType)
1407 i) Publishers of PublisherType
1408
1409Insert 5 records into the above tables and fire the following queries:
1410
1411a) List all of the authors that have the same pin code as their publisher:
1412b) List all books that have 2 or more authors:
1413c) List the name of the publisher that has the most branches
1414d) Name of authors who have not published a book
1415e) Name of authors who have not published a book.
1416
1417Overview: An object oriented database management system (OODBMS) is the coupling of database and object oriented concepts. OODBMS concepts have their origin in object oriented programming language that contains abstract data types, encapsulation, information hiding methods, class/type hierarchies, inheritance etc.
1418
1419An object has state (value) and behaviour (operation).
1420
1421For Example- An Employee can be an object with state as fields eno, ename, bdate, salary and behavior may be finding age operation to find the age of an employee using bdate.
1422
1423Step1: Create different Types and Tables
1424
1425SQL> Create or replace type AddrType1 as object(
1426 Pincode number(5),
1427 Street char(20),
1428 City varchar2(50),
1429 state varchar2(40),
1430 no number(4) );
1431 /
1432
1433Type created.
1434
1435SQL> create or replace type BranchType as object(address AddrType1,phone1 integer,
1436 phone2 integer );
1437 /
1438
1439Type created.
1440
1441SQL> create or replace type BranchTableType as table of BranchType;
1442 /
1443
1444Type created.
1445
1446SQL> create or replace type AuthorType as object( name varchar2(50), addr AddrType1 );
1447 /
1448
1449Type created.
1450
1451SQL> create table authors of AuthorType;
1452
1453Table created.
1454
1455SQL> create or replace type AuthorListType as varray(10) of ref AuthorType;
1456 /
1457
1458Type created.
1459
1460SQL> create or replace type PublisherType as object( name varchar2(50),
1461addr AddrType1, branches BranchTableType);
1462 /
1463
1464Type created.
1465
1466SQL> create table books( title varchar2(50), year date, published_by ref PublisherType,
1467 authors AuthorListType);
1468
1469Table created.
1470
1471SQL> create table Publishers of PublisherType NESTED TABLE branches STORE as branchtable;
1472
1473Table created.
1474
1475
1476Step 2: Insertion of different values in the tables
1477
1478Table Name:Authors
1479Format : Authors(name, addr:<pincode,street,city,state,no>)
1480
1481SQL> insert into Authors values('stallings', AddrType1(5002,'sstreet','pune','mha',04));
1482
14831 row created.
1484
1485SQL>
1486SQL> insert into Authors values('stallings', AddrType1(7007,'sstreet','mumbai','mha',1007));
1487
14881 row created.
1489
1490SQL> insert into Authors values('Navathe', AddrType1(7008,'nstreet','nasik','mha',08));
1491
14921 row created.
1493
1494SQL> insert into Authors values('Dhumdhare', AddrType1(7003,'dstreet','mumbai','mha',1003));
1495
14961 row created.
1497
1498
1499Table name : Publishers
1500Format : Publishers(name, addr:<pincode,street,city,state,no>, branches:set of<address:<pincode,street,city,state,no>,phone1,phone2>)
1501
1502/*where addr is an object-valued attribute. Branches is a complex-valued attribute, in this case a nested table where each element in the table has 3 parts: an address and two phones*/
1503
1504SQL> insert into Publishers values('tata',AddrType1(4002,'rstreet','mumbai','mha',03),
1505BranchTableType(BranchType( AddrType1(5002,'fstreet','mumbai','mha',03),23406,69896)));
1506
15071 row created.
1508
1509SQL> insert into Publishers values('seth',AddrType1(7007,'sstreet','mumbai','mha',1007),
1510 BranchTableType(BranchType(AddrType1(7007,'sstreet','mumbai','mha',1007),4543545,8676775)));
1511
15121 row created.
1513
1514SQL> insert into Publishers values('joshi',AddrType1(7008,'sstreet','mumbai','mha',1007),
1515 BranchTableType(BranchType(AddrType1(1002,'sstreet','nasik','mha',1007),456767,7675757)));
1516
15171 row created.
1518SQL> select * from Authors;
1519
1520Table Name3: books
1521Format : books(title,year,published_by: ref<Publishers>, authors:list of ref Author)
1522
1523SQL> insert into books select 'java','28-may-1983', ref(pub), AuthorListType(ref(aut)) from
1524 Publishers pub, Authors aut where pub.name='joshi' and aut.name=' Dhumdhare ';
1525
15260 rows created.
1527
1528SQL> insert into books
1529 select 'java','28-may-1983',ref(pub),AuthorListType(ref(aut)) from
1530 Publishers pub,Authors aut where pub.name='joshi' and aut.name='Dhumdhare';
1531
15321 row created.
1533
1534SQL> insert into books
1535 select 'java','28-may-1983',ref(pub),AuthorListType(ref(aut)) from
1536 Publishers pub,Authors aut where pub.name='seth' and aut.name='Navathe';
1537
15381 row created.
1539
1540SQL> insert into books
1541 select 'adv java','28-may-1983',ref(pub), AuthorListType(ref(aut)) from
1542 Publishers pub, Authors aut where pub.name='seth' and aut.name='stallings';
1543
15442 rows created.
1545
1546SQL> insert into books
1547 select 'DBMS','28-may-1983',ref(pub),AuthorListType(ref(aut)) from
1548 Publishers pub,Authors aut where pub.name='tata' and aut.name='Navathe';
1549
15501 row created.
1551
1552SQL> select * from books;
1553
1554
1555Step 3: Firing queries on the created tables
1556a) List all of the authors that have the same address as their publisher:
1557
1558SQL> select a.name from authors a, publishers p where a.addr = p.addr;
1559
1560NAME
1561--------------------------------------------------
1562stallings
1563
1564b) List all of the authors that have the same pin code as their publisher:
1565
1566
1567SQL> select a.name from authors a, publishers p where a.addr.pincode = p.addr.pincode;
1568
1569NAME
1570--------------------------------------------------
1571stallings
1572Navathe
1573
1574c) select List all books that have 2 or more authors:
1575
1576SQL> select * from books b where 1 <( select count(*)from table(b.authors));
1577
1578no rows selected
1579d) List the title of the book that has the most authors:
1580
1581SQL> Select title from books b, table(b.authors) group by title having count(*) =
1582 (select max(count(*)) from books b, table(b.authors) group by title);
1583
1584TITLE
1585--------------------------------------------------
1586adv java
1587java
1588
1589Name of authors who have not published a book:
1590SQL> select a.name from authors a where not exists( select b.title from books b, table(b.authors)
1591 where a.name = name);
1592
1593no rows selected
1594
1595
1596Practical-5
1597// Create different types that include attributes and methods. Define tables for these types by adding sufficient number of tuples. Demonstrate insert, update and delete operations on these tables. Execute queries on them.
1598
1599SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 30 08:19:52 2018
1600Copyright (c) 1982, 2010, Oracle. All rights reserved.
1601SQL> connect
1602Enter user-name: system
1603Enter password:
1604Connected.
1605SQL> create or replace type ttype as object(tid number ,tname varchar2(20),tcost number);
16062 /
1607Type created.
1608SQL> create or replace type ctype as object(cid number ,cname varchar2(20),members number);
16092 /
1610Type created.
1611SQL> create or replace type bktype as object(tid number, cid number, bkdt date, frmdt date, todt date);
16122 /
1613SQL> create table tour of ttype;
1614Table created.
1615SQL> create table customer of ctype;
1616Table created.
1617SQL> create table booking of bktype;
1618Table created.
1619SQL> insert into tour values (ttype(001,'kashmir',20000));
16201 row created.
1621
1622SQL> insert into tour values (ttype(002,'kanyakumari',25000));
16231 row created.
1624SQL> insert into tour values (ttype(005,'assam',22000));
16251 row created.
1626SQL> insert into tour values (ttype(007,'dombivli',23000));
16271 row created.
1628 SQL> insert into tour values (ttype(008,'ghatkopar',24000));
16291 row created.
1630SQL> insert into tour values (ttype(010,'nalasopara',26000));
16311 row created.
1632SQL> insert into customer values (ctype(001,'aditya chachlani',3));
16331 row created.
1634SQL> insert into customer values (ctype(003,'manish birla',2));
16351 row created.
1636SQL> insert into customer values (ctype(004,'neeta singh',4));
16371 row created.
1638SQL> insert into customer values (ctype(006,'kajal rathod',1));
16391 row created.
1640SQL> insert into customer values (ctype(008,'vibhuti mishra',6));
16411 row created.
1642SQL> insert into customer values (ctype(012,'happu singh',12));
16431 row created.
1644SQL> select * from tour;
1645 TID TNAME TCOST
1646---------- -------------------- ----------
1647 1 kashmir 20000
1648 2 kanyakumari 25000
1649 5 assam 22000
1650 7 dombivli 23000
1651 8 ghatkopar 24000
1652 10 nalasopara 26000
16536 rows selected.
1654SQL> select * from customer;
1655 CID CNAME MEMBERS
1656---------- -------------------- ----------
1657 1 adityachachlani 3
1658 3 manishbirla 2
1659 4 neetasingh 4
1660 6 kajal rathod 1
1661 8 vibhutimishra 6
1662 12 happusingh 12
16636 rows selected.
1664
1665SQL> insert into booking values (bktype(001, 012, to_date('25-07-2018 5:45','dd-mm-yyyy hh24:mi'), to_date('28-07-2018 7:45','dd-mm-yyyy hh24:mi'), to_date('30-07-2018 7:45','dd-mm-yyyy hh24:mi')));
16661 row created.
1667SQL> insert into booking values (bktype(002,006,to_date('26-07-2018 5:45','dd-mm-yyyy hh24:mi'),to_date('1-08-2018 7:45','dd-mm-yyyy hh24:mi'),to_date('3-08-2018 7:45','dd-mm-yyyy hh24:mi')));
16681 row created.
1669SQL> insert into booking values (bktype(005,003,to_date('25-07-2018 5:45','dd-mm-yyyy hh24:mi'),to_date('2-08-2018 7:45','dd-mm-yyyy hh24:mi'),to_date('5-08-2018 7:45','dd-mm-yyyy hh24:mi')));
16701 row created.
1671SQL> insert into booking values (bktype(007,008,to_date('1-08-2018 5:45','dd-mm-yyyy hh24:mi'),to_date('6-08-2018 7:45','dd-mm-yyyy hh24:mi'),to_date('12-08-2018 7:45','dd-mm-yyyy hh24:mi')));
16721 row created.
1673SQL> insert into booking values (bktype(008,004,to_date('8-08-2018 5:45','dd-mm-yyyy hh24:mi'),to_date('10-08-2018 7:45','dd-mm-yyyy hh24:mi'),to_date('15-08-2018 7:45','dd-mm-yyyy hh24:mi')));
16741 row created.
1675SQL> insert into booking values (bktype(010,001,to_date('5-08-2018 5:45','dd-mm-yyyy hh24:mi'),to_date('12-08-2018 7:45','dd-mm-yyyy hh24:mi'),to_date('16-08-2018 7:45','dd-mm-yyyy hh24:mi')));
16761 row created.
1677
1678
1679SQL> select * from booking;
1680 TID CID BKDT FRMDT TODT
1681---------- ---------- --------- --------- ---------
1682 1 12 25-JUL-18 28-JUL-18 30-JUL-18
1683 2 6 26-JUL-18 01-AUG-18 03-AUG-18
1684 5 3 25-JUL-18 02-AUG-18 05-AUG-18
1685 7 8 01-AUG-18 06-AUG-18 12-AUG-18
1686 8 4 08-AUG-18 10-AUG-18 15-AUG-18
1687 10 1 05-AUG-18 12-AUG-18 16-AUG-18
16886 rows selected.
1689Q. Find name of customer whose tour name is Kashmir.
1690SQL> select c.cname from customer c where c.cid=(select b.cid from booking b where b.tid=(select t.tid from tour t where tname='kashmir'));
1691CNAME
1692--------------------
1693Happusingh
1694
1695Q. Find total number of members belongs to customer Manish Birla.
1696SQL> select c.members from customer c where cname='manish birla' ;
1697 MEMBERS
1698----------
1699 2
1700
1701
1702Q. Find name of tour who have customer with maximum number of members.
1703SQL> select * from tour t where t.tid=(select b.tid from booking b where b.cid=(select c.cid from customer c where c.members=(select max(c.members) from customer c )));
1704 TID TNAME TCOST
1705---------- -------------------- ----------
1706 1 kashmir 20000
1707
1708SQL> select * from tour t where t.tid in (select b.tid from booking b where trunc(frmdt,'mm')=to_date('aug','mm'));
1709 TID TNAME TCOST
1710---------- -------------------- ----------
1711 2 kanyakumari 25000
1712 5 assam 22000
1713 7 dombivli 23000
1714 8 ghatkopar 24000
1715 10 nalasopara 26000
1716
1717
1718
1719
1720
1721
1722Practical-6
1723// Create a temporal database and issue queries on it.
1724
1725SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 18 11:35:48 2018
1726Copyright (c) 1982, 2010, Oracle. All rights reserved.
1727SQL> connect
1728Enter user-name: system
1729Enter password:
1730Connected.
1731SQL> CREATE TABLE tb1Emp_appnt(acc_no NUMBER(3), name VARCHAR2(10), recruitment_date DATE, retirement_date DATE);
1732Table created.
1733SQL> INSERT INTO tb1Emp_appnt VALUES(101,'Aditya',TO_DATE('2-3-2001','DD-MM-YYYY'),TO_DATE('13-04-2005','DD-MM-YYYY'));
17341 row created.
1735SQL> INSERT INTO tb1Emp_appnt VALUES(102,'Medha',TO_DATE('12-8-2002','DD-MM-YYYY'),TO_DATE('12-07-2004','DD-MM-YYYY'));
17361 row created.
1737SQL> INSERT INTO tb1Emp_appnt VALUES(103,'Rahul',TO_DATE('11-3-2002','DD-MM-YYYY'),TO_DATE('13-07-2005','DD-MM-YYYY'));
17381 row created.
1739SQL> INSERT INTO tb1Emp_appnt VALUES(104,'Karan',TO_DATE('2-3-2003','DD-MM-YYYY'),TO_DATE('13-09-2005','DD-MM-YYYY'));
17401 row created.
1741SQL> INSERT INTO tb1Emp_appnt VALUES(105,'Rizwan',TO_DATE('8-7-2000','DD-MM-YYYY'),TO_DATE('2-03-2001','DD-MM-YYYY'));
17421 row created.
1743SQL> INSERT INTO tb1Emp_appnt VALUES(106,'Alok',TO_DATE('17-9-1999','DD-MM-YYYY'),TO_DATE('13-12-2004','DD-MM-YYYY'));
17441 row created.
1745SQL> INSERT INTO tb1Emp_appnt VALUES(107,'Ram',TO_DATE('12-11-2001','DD-MM-YYYY'),TO_DATE('13-02-2005','DD-MM-YYYY'));
17461 row created.
1747SQL> INSERT INTO tb1Emp_appnt VALUES(108,'Ganesh',TO_DATE('16-1-2001','DD-MM-YYYY'),TO_DATE('2-03-2001','DD-MM-YYYY'));
17481 row created.
1749SQL> INSERT INTO tb1Emp_appnt VALUES(109,'Amey',TO_DATE('21-4-2002','DD-MM-YYYY'),TO_DATE('13-04-2005','DD-MM-YYYY'));
17501 row created.
1751SQL> INSERT INTO tb1Emp_appnt VALUES(110,'Pritish',TO_DATE('28-2-2003','DD-MM-YYYY'),TO_DATE('13-04-2005','DD-MM-YYYY'));
17521 row created.
1753SQL> SELECT * FROM tb1Emp_appnt;
1754 ACC_NO NAME RECRUITME RETIREMEN
1755---------- ---------- --------- ---------
1756 101 Aditya 02-MAR-01 13-APR-05
1757 102 Medha 12-AUG-02 12-JUL-04
1758 103 Rahul 11-MAR-02 13-JUL-05
1759 104 Karan 02-MAR-03 13-SEP-05
1760 105 Rizwan 08-JUL-00 02-MAR-01
1761 106 Alok 17-SEP-99 13-DEC-04
1762 107 Ram 12-NOV-01 13-FEB-05
1763 108 Ganesh 16-JAN-01 02-MAR-01
1764 109 Amey 21-APR-02 13-APR-05
1765 110 Pritish 28-FEB-03 13-APR-05
176610 rows selected.
1767SQL> SELECT * FROM tb1Emp_appnt WHERE recruitment_date = TO_DATE('2-03-2001','dd-mm-yyyy');
1768 ACC_NO NAME RECRUITME RETIREMEN
1769---------- ---------- --------- ---------
1770 101 Aditya 02-MAR-01 13-APR-05
1771
1772SQL> SELECT * FROM tb1Emp_appnt WHERE retirement_date = TO_DATE('2-03-2001','dd-
1773mm-yyyy');
1774 ACC_NO NAME RECRUITME RETIREMEN
1775---------- ---------- --------- ---------
1776 105 Rizwan 08-JUL-00 02-MAR-01
1777 108 Ganesh 16-JAN-01 02-MAR-01
1778SQL> CREATE TABLE tbl_shares(company_name VARCHAR2(10),no_shares NUMBER(5),price NUMBER(5),transaction_time DATE);
1779Table created.
1780SQL> INSERT INTO tbl_shares VALUES('Infosys',300,10,TO_DATE('11:45','hh24:mi'));
17811 row created.
1782SQL> INSERT INTO tbl_shares VALUES('Wipro',200,20,TO_DATE('06:45','hh24:mi'));
17831 row created.
1784SQL> INSERT INTO tbl_shares VALUES('Himalaya',100,15,TO_DATE('17:45','hh24:mi'));
17851 row created.
1786SQL> INSERT INTO tbl_shares VALUES('MBT',100,20,TO_DATE('18:45','hh24:mi'));
17871 row created.
1788SQL> INSERT INTO tbl_shares VALUES('Patni',500,10,TO_DATE('11:45','hh24:mi'));
17891 row created.
1790SQL> SELECT * FROM tbl_shares;
1791COMPANY_NA NO_SHARES PRICE TRANSACTI
1792---------- ---------- ---------- ---------
1793Infosys 300 10 01-AUG-18
1794Wipro 200 20 01-AUG-18
1795Himalaya 100 15 01-AUG-18
1796MBT 100 20 01-AUG-18
1797Patni 500 10 01-AUG-18
1798SQL> ALTER SESSION SET nls_date_format = 'HH24:MI';
1799Session altered.
1800(Use the ALTER SESSION statement to set or modify any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database.)
1801SQL> SELECT * FROM tbl_shares;
1802COMPANY_NA NO_SHARES PRICE TRANS
1803---------- ---------- ---------- -----
1804Infosys 300 10 11:45
1805Wipro 200 20 06:45
1806Himalaya 100 15 17:45
1807MBT 100 20 18:45
1808Patni 500 10 11:45
1809Q: Find all the names of a company whose share price is more than Rs. 1000 at 11:45 AM.
1810SQL> SELECT no_shares*price FROM tbl_shares WHERE no_shares*price > 1000 AND transaction_time = TO_DATE('11:45','hh24:mi');
1811NO_SHARES*PRICE
1812---------------
1813 3000
1814 5000
1815*Q: Find the name of company which has highestshare price at 5:00 PM.
1816SQL> SELECT MAX(no_shares*price) FROM tbl_shares WHERE transaction_time< TO_DATE('17:00','hh24:mi');
1817MAX(NO_SHARES*PRICE)
1818--------------------
1819 5000
1820SQL>
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830Practical-7
1831// Formulate a database using active rules with row and statement level.
1832
1833Copyright (c) 1982, 2010, Oracle. All rights reserved.
1834SQL> connect
1835Enter user-name: system
1836Enter password:
1837Connected.
1838SQL> create table project(pno varchar2(2) primary key, pname varchar2(20),thrs number);
1839Table created.
1840SQL> create table emp(eno varchar2(2) primary key,ename varchar2(20),hrs number, pno varchar2(2) references project(pno));
1841Table created.
1842SQL> insert into project values('p1','java',0);
18431 row created.
1844SQL> insert into project values('p2','xml',0);
18451 row created.
1846SQL> insert into project values('p3','php',0);
18471 row created.
1848(Create following triggers in notepad. Save within directory D with extension .sql)
1849Trigger1
1850create or replace trigger t1 after insert on emp for each row
1851begin
1852update project set thrs = thrs+ :new.hrs where pno = :new.pno;
1853end;
1854/
1855Trigger2
1856create or replace trigger t2 after update of hrs on emp for each row
1857begin
1858update project set thrs = (thrs- :old.hrs )+:new.hrs where pno = :old.pno;
1859end;
1860/
1861Trigger3
1862create or replace trigger t3 after update of pno on emp for each row
1863begin
1864update project set thrs = thrs+ :old.hrs where pno = :new.pno;
1865update project set thrs = thrs- :old.hrs where pno = :old.pno;
1866end;
1867/
1868Trigger4
1869create or replace trigger t4 after delete on emp for each row
1870begin
1871update project set thrs = thrs- :old.hrs where pno = :old.pno;
1872end;
1873/
1874
1875
1876SQL> @D:\msc\t1
1877Trigger created.
1878SQL> @D:\msc\t2
1879Trigger created.
1880SQL> @D:\msc\t3
1881Trigger created.
1882SQL> @D:\msc\t4
1883Trigger created.
1884SQL> insert into emp values ('&eno','&ename',&hrs,'&pno');
1885Enter value for eno: 1
1886Enter value for ename: vijay
1887Enter value for hrs: 10
1888Enter value for pno: p1
1889old 1: insert into emp values ('&eno','&ename',&hrs,'&pno')
1890new 1: insert into emp values ('1','vijay',10,'p1')
18911 row created.
1892SQL> /
1893Enter value for eno: 2
1894Enter value for ename: akshay
1895Enter value for hrs: 15
1896Enter value for pno: p2
1897old 1: insert into emp values ('&eno','&ename',&hrs,'&pno')
1898new 1: insert into emp values ('2','akshay',15,'p2')
18991 row created.
1900SQL> /
1901Enter value for eno: 3
1902Enter value for ename: tejas
1903Enter value for hrs: 20
1904Enter value for pno: p2
1905old 1: insert into emp values ('&eno','&ename',&hrs,'&pno')
1906new 1: insert into emp values ('3','tejas',20,'p2')
19071 row created.
1908SQL> /
1909Enter value for eno: 4
1910Enter value for ename: komal
1911Enter value for hrs: 15
1912Enter value for pno: p3
1913old 1: insert into emp values ('&eno','&ename',&hrs,'&pno')
1914new 1: insert into emp values ('4','komal',15,'p3')
19151 row created.
1916SQL> select * from project;
1917
1918PN PNAME THRS
1919-- -------------------- ----------
1920p1 java 10
1921p2 xml 35
1922p3 php 15
1923SQL> select * from emp;
1924EN ENAME HRS PN
1925-- -------------------- ---------- --
19261 vijay 10 p1
19272 akshay 15 p2
19283 tejas 20 p2
19294 komal 15 p3
1930SQL> update emp set hrs= 15 where eno=1;
19311 row updated.
1932SQL> select * from project;
1933PN PNAME THRS
1934-- -------------------- ----------
1935p1 java 15
1936p2 xml 35
1937p3 php 15
1938SQL> update emp set pno='p1' where eno=4;
19391 row updated.
1940SQL> select * from project;
1941PN PNAME THRS
1942-- -------------------- ----------
1943p1 java 30
1944p2 xml 35
1945p3 php 0
1946
1947SQL> select * from project;
1948PN PNAME THRS
1949-- -------------------- ----------
1950p1 java 30
1951p2 xml 35
1952p3 php 0
1953SQL> delete from emp where eno=4;
19541 row deleted.
1955SQL> select * from project;
1956PN PNAME THRS
1957-- -------------------- ----------
1958p1 java 15
1959p2 xml 35
1960p3 php 0
1961
1962SQL>
1963
1964Practical-8
1965// Formulate a database using active rules with row and statement level.
1966
1967SQL> CREATE TABLE EMPLOYEE(NAME VARCHAR2(10), SSN NUMBER(5), SALARY NUMBER(5), DNO NUMBER(3) NULL, SUPERVISOR_SSN NUMBER(3));
1968SQL> SELECT * FROM AEMPLOYEE;
1969
1970NAME SSN SALARY DNO SUPERVISOR_SSN
1971---------- ---------- ---------- ---------- --------------
1972anita 123 10000 213 345
1973sunita 124 12000 214 346
1974nilima 125 13000 215 347
1975vinita 126 14000 216 348
1976nita 127 14000 216 348
1977
1978SQL> CREATE TABLE ADEPARTMENT (DNAME VARCHAR2(10), DNO NUMBER(3), TOTAL_SAL NUMBER(10), MANAGER_SSN NUMBER(3));
1979
1980SQL> SELECT * FROM ADEPARTMENT;
1981
1982DNAME DNO TOTAL_SAL MANAGER_SSN
1983---------- ---------- ---------- -----------
1984sales 213 10000 421
1985manager 214 12000 422
1986director 215 13000 432
1987production 216 28000 433
1988
1989TRIGGER 1
1990
1991CREATE OR REPLACE TRIGGER TOTAL_SAL1
1992AFTER INSERT ON AEMPLOYEE
1993FOR EACH ROW
1994WHEN(NEW.DNO IS NOT NULL)
1995BEGIN
1996UPDATE ADEPARTMENT
1997SET TOTAL_SAL=TOTAL_SAL+:NEW.SALARY
1998WHERE DNO=:NEW.DNO;
1999END;
2000/
2001(Insert data into employee table and check changes in Department table)
2002TRIGGER2
2003CREATE OR REPLACE TRIGGER TOTAL_SAL2
2004AFTER UPDATE OF SALARY ON AEMPLOYEE
2005FOR EACH ROW
2006WHEN(NEW.DNO IS NOT NULL)
2007BEGIN
2008UPDATE ADEPARTMENT
2009SET TOTAL_SAL=TOTAL_SAL+:NEW.SALARY-:OLD.SALARY
2010WHERE DNO=:NEW.DNO;
2011END;
2012/
2013(Update salary into employee table and check changes in Department table)
2014
2015TRIGGER3
2016CREATE OR REPLACE TRIGGER TOTAL_SAL3
2017AFTER UPDATE OF DNO ON AEMPLOYEE
2018FOR EACH ROW
2019BEGIN
2020UPDATE ADEPARTMENT
2021SET TOTAL_SAL=TOTAL_SAL+:NEW.SALARY
2022WHERE DNO=:NEW.DNO;
2023UPDATE ADEPARTMENT
2024SET TOTAL_SAL=TOTAL_SAL-:OLD.SALARY
2025WHERE DNO=:OLD.DNO;
2026END;
2027/
2028(Update DNO into employee table and check changes in Department table)
2029
2030TRIGGER4
2031CREATE OR REPLACE TRIGGER TOTAL_SAL4
2032AFTER DELETE ON AEMPLOYEE
2033FOR EACH ROW
2034WHEN(OLD.DNO IS NOT NULL)
2035BEGIN
2036UPDATE ADEPARTMENT
2037SET TOTAL_SAL=TOTAL_SAL-:OLD.SALARY
2038WHERE DNO=:OLD.DNO;
2039END;
2040/
2041(Delete any employee from employee table and check changes in Department table)
2042
2043
2044Practical-9
2045// Create a XML data base and demonstrate insert, update and delete operations on these tables. Issue queries on it.
2046
2047SQL*Plus: Release 11.2.0.2.0 Production on Sat Oct 27 09:06:59 2018
2048Copyright (c) 1982, 2010, Oracle. All rights reserved.
2049SQL> connect
2050Enter user-name: system
2051Enter password:
2052Connected.
2053Creating table with XMLType
2054SQL> CREATE TABLE employee(
2055 dept_id NUMBER(3),
2056 employee_spec XMLTYPE);
2057Table created.
2058Inserting values into employee table
2059SQL> INSERT INTO employee VALUES
2060 (100,XMLTYPE(
2061 '<employees>
2062 <emp id="1">
2063 <name>Rizwan Ansari</name>
2064 <email>rizwan@outlook.com</email>
2065 <acc_no>111</acc_no>
2066 <dateOfJoining>2015-11-16</dateOfJoining>
2067 </emp>
2068 </employees>')) ;
20691 row created.
2070
2071
2072
2073SQL> INSERT INTO employee VALUES
2074 (200,XMLTYPE(
2075 '<employees>
2076 <emp id="2">
2077 <name>Vijay Sangoi</name>
2078 <email>vijay@gmail.com.com</email>
2079 <acc_no>222</acc_no>
2080 <dateOfJoining>2016-10-28</dateOfJoining>
2081 </emp>
2082 </employees>')) ;
20831 row created.
2084SQL> INSERT INTO employee VALUES
2085 (300,XMLTYPE(
2086 '<employees>
2087 <emp id="3">
2088 <name>Abhishek Yadav</name>
2089 <email>abhishek@yahoo.com</email>
2090 <acc_no>333</acc_no>
2091 <dateOfJoining>2010-4-14</dateOfJoining>
2092 </emp>
2093 </employees>')) ;
20941 row created.
2095Retrieve all information from employee table
2096SQL> SELECT * FROM employee;
2097
2098 DEPT_ID
2099----------
2100EMPLOYEE_SPEC
2101--------------------------------------------------------------------------------
2102 100
2103<employees>
2104<emp id="1">
2105<name>Rizwan Ansari</name>
2106<email>rizwan@outl
2107 200
2108<employees>
2109<emp id="2">
2110 DEPT_ID
2111----------
2112EMPLOYEE_SPEC
2113--------------------------------------------------------------------------------
2114<name>Vijay Sangoi</name>
2115<email>vijay@gmail.
2116 300
2117<employees>
2118<emp id="3">
2119<name>Abhishek Yadav</name>
2120<email>abhishek@y
2121Retrieve the name of employee from employee table
2122SQL> SELECT w.employee_spec.extract
2123 ('/employees/emp/name/text()').getStringVal() "name" FROM employee w
2124 ;
2125
2126
2127name
2128--------------------------------------------------------------------------------
2129Rizwan Ansari
2130Vijay Sangoi
2131Abhishek Yadav
2132Retrieve the acc_no of employee from employee table
2133SQL> SELECT w.employee_spec.extract
2134 ('/employees/emp/acc_no/text()').getStringVal() "acc_no" FROM employee w
2135 ;
2136acc_no
2137--------------------------------------------------------------------------------
2138111
2139222
2140333
2141Retrieve the names, acc_no, email of employees from employee table
2142SQL> SELECT
2143 w.employee_spec.extract('/employees/emp/name/text()').getStringVal() "name",
2144 w.employee_spec.extract('/employees/emp/acc_no/text()').getStringVal() "acc_no",
2145 w.employee_spec.extract('/employees/emp/email/text()').getStringVal() "email"
2146 FROM employee w ;
2147name
2148--------------------------------------------------------------------------------
2149acc_no
2150--------------------------------------------------------------------------------
2151email
2152--------------------------------------------------------------------------------
2153Rizwan Ansari
2154111
2155rizwan@outlook.com
2156Vijay Sangoi
2157222
2158vijay@gmail.com.com
2159name
2160--------------------------------------------------------------------------------
2161acc_no
2162--------------------------------------------------------------------------------
2163email
2164--------------------------------------------------------------------------------
2165Abhishek Yadav
2166333
2167abhishek@yahoo.com
2168Updating XMLType
2169SQL> UPDATE employee w SET employee_spec = XMLTYPE
2170 ('<employees>
2171 <emp id="5">
2172 <name>Raheem Khan</name>
2173 </emp>
2174 </employees>')
2175 WHERE
2176 w.employee_spec.extract('//acc_no/text()').getStringVal() = '333'
2177 ;
21781 row updated.
2179SQL> SELECT
2180 w.employee_spec.extract('/employees/emp/name/text()').getStringVal() "name" FROM employee w
2181 ;
2182
2183name
2184--------------------------------------------------------------------------------
2185Rizwan Ansari
2186Vijay Sangoi
2187Raheem Khan
2188Deleting an XMLType Column Row
2189SQL> DELETE FROM employee w WHERE
2190 w.employee_spec.extract('/employees/emp/acc_no/text()').getStringVal() = '111'
2191 ;
21921 row deleted.
2193SQL> SELECT
2194 w.employee_spec.extract('/employees/emp/acc_no/text()').getStringVal() "acc_no" FROM employee w
2195 ;
2196acc_no
2197--------------------------------------------------------------------------------
2198222
2199SQL>