· 5 years ago · Feb 29, 2020, 11:16 AM
1_-_-__-_-_-_-_-_-_-DATA SCIENCE-_-_-_-__-_-_--_-_-_-_-_
2 Practical 1(data science)
3
4Aim: Practical of Simple/Multiple Linear Regression.
5Source Code:
6#initializing height vector
7height<-c(102,117,105,141,135,115,138,144,137,100,131,119,115,121,113)
8
9#initializing weight vector
10weight<-c(61,46,62,54,60,69,51,50,46,64,48,56,64,48,59)
11
12#generate regression where height act as an linear predictor for weight
13student<-lm(weight~height)
14student
15
16#making a prediction using above linear regression model
17predict(student,data.frame(height=199),interval="confidence")
18
19#plot regression model
20plot(student)
21
22 Practical-2(data science)
23
24Aim: Practical of Time-series forecasting.
25
26Source Code:
27
28#loading dataset "AirPassengers
29data(AirPassengers)
30
31class(AirPassengers)
32
33#returns starting interval
34start(AirPassengers)
35
36#returns end interval
37end(AirPassengers)
38
39#retunrs number of observations before pattern repeats
40frequency(AirPassengers)
41summary(AirPassengers)
42
43#plot AirPassengers
44plot(AirPassengers)
45
46#adds a linear model to above plot
47abline(reg=lm(AirPassengers~time(AirPassengers)))
48
49#cycle(AirPassengers)
50#computing mean for data subsets in AirPassengers and plotting it
51plot(aggregate(AirPassengers,FUN=mean))
52
53#generate a boxplot for AirPassengers data on monthly basis
54boxplot(AirPassengers~cycle(AirPassengers))
55
56 Practical 3(data science)
57
58Aim: Practical of Time Series Analysis.
59
60Source Code:
61
62# Get the data points in form of a R vector.
63rainfall <- c(799,1174.8,865.1,1334.6,635.4,918.5,685.5,998.6,784.2,985,882.8,1071)
64
65# Convert it to a time series object.
66rainfall.timeseries <- ts(rainfall,start = c(2012,1),frequency = 12)
67
68# Print the timeseries data.
69print(rainfall.timeseries)
70
71# Give the chart file a name.
72png(file = "rainfall.png")
73
74# Plot a graph of the time series.
75plot(rainfall.timeseries)
76
77# Save the file.
78dev.off()
79
80 Practical 4(data science)
81
82Aim: Practical of k-means Clustering.
83
84Source Code:
85
86#loading dataset "iris"
87data(iris)
88
89#returns the variables names in dataset iris
90names(iris)
91
92#stores the subset of iris dataset without Species variable in symbol new_data
93new_data<-subset(iris,select=c(-Species))
94
95#forming clusters in dataset new_data using kmeans() where 3 incates number cluster to be formed
96cl<-kmeans(new_data,3)
97
98#assigning new_data values to data
99data<-new_data
100
101#apply function to all the elements of data and store the resulting vector in wss
102wss<-sapply(1:15,function(k){kmeans(data,k)$tot.withinss})
103
104#plot values in wss vector
105plot(1:15,wss,type="b",pch=19,frame=FALSE,xlab="Number of clusters K",ylab="Total within-clusters sums of squares")
106
107#importing cluster package
108library(cluster)
109
110#plotting clusters computed by kmeans function
111clusplot(new_data,cl$cluster,color=TRUE,shade=TRUE,labels=2,lines=0)
112
113# cl$cluster
114# cl$centers
115#hierarchical cluster analysis for dissimilarity structure of 3rd and 4th variable of iris dataset
116clusters<-hclust(dist(iris[,3:4]))
117
118#plotting Cluster Dendrogram
119plot(clusters,cex=0.65)
120
121#Dividing Dendrogram into 3 grounps
122clusterCut<-cutree(clusters,3)
123
124#printing the values of dendrogram groups w.r.t Scpecies
125table(clusterCut,iris$Species)
126
127 Practical 5(data science)
128
129Aim: Practical of Logistics Regression.
130Source Code:
131
132#install ISLR package(Introduction to Statistical Learning with Applications in R)
133install.packages("ISLR")
134
135# Call the Library ISLR and data file smarket
136library(ISLR)
137
138# List the variable names
139names(Smarket)
140
141# Dimensions and summary stats for continuous variables
142dim(Smarket)
143summary(Smarket)
144
145# Scatterplot Matrix
146pairs(Smarket)
147
148# To know more info about the data:
149`?`(Smarket)
150
151# Correlation matrix
152cor(Smarket[, -9])
153
154# Attach column names
155attach(Smarket)
156
157# plot multiple graphs in single plot row wise
158par(mfrow=c(1,1))
159
160# plot Volume
161plot(Volume)
162
163# Fitting GLM model
164glm.fits=glm(Direction~Lag1+Lag2+Lag3+Lag4+Lag5+Volume,data=Smarket,family=binomial)
165summary(glm.fits)
166
167# Coefficients from the GLM fit
168coef(glm.fits)
169summary(glm.fits)$coef
170
171# Only The 4th column in Coefficients, prob using z test
172summary(glm.fits)$coef[,4]
173
174# Generating probabilities using predict function and Type=response.
175glm.probs=predict(glm.fits,type="response")
176# Probabilities 1 through 10 are printed and the direction of prediction is up=1.
177glm.probs[1:10]
178contrasts(Direction)
179
180# Using the same predict function on all 1250 observations, we are creating the direction of the
181# market as Up if the prob exceeds 0.5 and downotherwise.
182# The table of predicted vs. direction is printed too.
183glm.pred=rep("Down",1250)
184glm.pred[glm.probs>.5]="Up"
185glm.probs[1:10]
186glm.pred[1:10]
187table(glm.pred,Direction)
188(507+145)/1250
189
190# Following divides the total true prediction by total to get
191# the portion of correct predictions in this table.
192mean(glm.pred==Direction)
193
194# The vector train pertains to all observationd prior to year 2005.
195train=(Year<2005)
196Smarket.2005=Smarket[!train,]
197dim(Smarket.2005) # Contains 252 observations and 9 variables
198Direction.2005=Direction[!train]
199
200#GLM Logistic regression for the training dataset using all 6 predictors.
201glm.fits=glm(Direction~Lag1+Lag2+Lag3+Lag4+Lag5+Volume,data=Smarket,family=binomial,subset=train)
202summary(glm.fits)
203glm.probs=predict(glm.fits,Smarket.2005,type="response")
204
205#direction calculation and mean direction of training data
206glm.pred=rep("Down",252)
207glm.pred[glm.probs>.5]="Up"
208
209# Table of actual vs. predicted directions
210table(glm.pred,Direction.2005)
211mean(glm.pred==Direction.2005)
212mean(glm.pred!=Direction.2005)
213
214# GLM Logistic regression model using only lag1 & lag2 as predictors and the training dataset,
215# generating proabilities, direction and mean direction
216glm.fits=glm(Direction~Lag1+Lag2,data=Smarket,family=binomial,subset=train)
217glm.probs=predict(glm.fits,Smarket.2005,type="response")
218
219# Dividing correct predictions by total to get the correct portion predicted
220mean(glm.pred==Direction.2005)
221
222(106+35)/252
223106/(106+35)
22476/(36+76)
225
226 Practical 6
227
228Aim: Practical of Decision Tree.
229
230Source Code:
231
232# Load the party package. It will automatically load other
233# dependent packages.
234install.packages("party")
235library(party)
236
237# Create the input data frame.
238input.dat <- readingSkills[c(1:105),]
239
240# Give the chart file a name.
241png(file = "decision_tree.png")
242
243# Create the tree.
244output.tree <- ctree(
245 nativeSpeaker ~ age + shoeSize + score,
246 data = input.dat)
247
248# Plot the tree.
249plot(output.tree)
250
251# Save the file.
252dev.off()
253
254 Practical 7(data science)
255
256Aim: Practical of Hypothesis Testing.
257
258Source Code:
259
260# generate a sequence(1,2,3...19,20)
261dataf<-seq(1,20,by=1)
262dataf
263
264#Calculate mean
265mean(dataf)
266
267#Calculate Standard Deviation
268sd(dataf)
269
270# x = dataf
271#a (non-empty) numeric vector of data values.
272#alternative a character string specifying the alternative hypothesis, must be one of "two.sided" (default),
273#"greater" or "less". You can specify just the initial letter.
274#mu = 10
275#a number indicating the true value of the mean (or difference in means if you are
276#performing a two sample test).
277
278a<-t.test(dataf,alternate="two.sided",mu=10,conf.int=0.95)
279a
280
281# print p-value and statistics for test
282a$p.value
283a$statistic
284
285# t-test formula
286(10.5-10)/(sd(dataf)/sqrt(length(dataf)))
287
288length(dataf)=1
289length(dataf)
290dataf
291dataf<-seq(1,20,by=1)
292length(dataf)-1
293
294 practical 8(data science)
295
296Aim: Practical of Analysis of Variance.
297
298
299Source Code:
300
301# loading warpbreaks
302data("warpbreaks")
303head(warpbreaks)
304summary(warpbreaks)
305
306# Fit an analysis of variance model by a call to lm for each stratum.
307#formula = breaks~wool+tension
308#A formula specifying the model.
309#data = warpbreaks
310#A data frame in which the variables specified in the formula will be found.
311Model_1<-aov(breaks~wool+tension,data=warpbreaks)
312summary(Model_1)
313
314plot(Model_1)
315
316#formula = breaks~wool+tension+wool:tension
317Model_2<-aov(breaks~wool+tension+wool:tension,data=warpbreaks)
318summary(Model_2)
319plot(Model_2)
320
321 Practical 9(data science)
322
323Aim: Practical of Principal Component Analysis.
324
325Source Code:
326
327# loading dataset iris
328data("iris")
329head(iris)
330library()
331
332# to find principal component
333mypr<-prcomp(iris[,-5],scale=T)
334
335# to understand use of scale
336plot(iris$Sepal.Length,iris$Sepal.Width)
337plot(scale(iris$Sepal.Length),scale(iris$Sepal.Width))
338mypr
339summary(mypr)
340plot(mypr,type="l")
341
342# plot the biplot showing first two PC’s and the original feature vectors in this
343#2D space i.e original feature vectors as linear combination of first two PC’s
344biplot(mypr,scale=0)
345
346# extract pc scores
347str(mypr)
348mypr$x
349iris2<-cbind(mypr$x[,1:2])
350head(iris2)
351
352# find co-relations(x=iris[,5], y=iris[,1:2])
353cor(iris[,-5],iris2[,1:2])
354
355# install pls package
356install.packages("pls")
357library(pls)
358names(iris)
359
360_-_-_-_-_-_-_-_-_-_-_-_-_--_-_-_-_-_-_-_-_--_-_-_-_-_--_-_-_-_-__-_-_-_-_-_-_-_DBMS-_-_-_-_-_-_--_-_-_-_-_-_-
361
362 Practical-1,2,3(db)
363//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.
364
365// 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.
366
367// Place the replication of global conceptual schema on different nodes and execute queries that will demonstrate distributed databases environment.
368
369SQL>
370Enter user-name: system
371Enter password:
372Connected.
373SQL> CREATE USER user1 IDENTIFIED BY user1;
374User created.
375SQL> CREATE USER user2 IDENTIFIED BY user2;
376User created.
377SQL> GRANT CONNECT,RESOURCE TO user1;
378Grant succeeded.
379SQL> GRANT CONNECT,RESOURCE TO user2;
380Grant succeeded.
381SQL> CREATE PUBLIC DATABASE LINK linksys CONNECT TO system IDENTIFIED BY server USING 'XE';
382Database link created.
383SQL> CREATE PUBLIC DATABASE LINK linku1 CONNECT TO user1 IDENTIFIED BY user1 USING 'XE';
384Database link created.
385SQL> CREATE PUBLIC DATABASE LINK linku2 CONNECT TO user2 IDENTIFIED BY user2 USING 'XE';
386Database link created.
387SQL> CREATE TABLE Emp(Eno NUMBER PRIMARY KEY,Ename VARCHAR2(10),Address VARCHAR2(10),Email VARCHAR2(18),Salary NUMBER);
388Table created.
389SQL> INSERT INTO Emp VALUES(&Eno,'&Ename','&Address','&Email',&Salary);
390Enter value for eno: 01
391Enter value for ename: Rizwan
392Enter value for address: Kurla
393Enter value for email: rizwan@gmail.com
394Enter value for salary: 6500
395old 1: INSERT INTO Emp VALUES(&Eno,'&Ename','&Address','&Email',&Salary)
396new 1: INSERT INTO Emp VALUES(01,'Rizwan','Kurla','rizwan@gmail.com',6500)
3971 row created.
398SQL> /
399Enter value for eno: 02
400Enter value for ename: Pintu
401Enter value for address: Naupada
402Enter value for email: pintu@yahoo.com
403Enter value for salary: 6000
404old 1: INSERT INTO Emp VALUES(&Eno,'&Ename','&Address','&Email',&Salary)
405new 1: INSERT INTO Emp VALUES(02,'Pintu','Naupada','pintu@yahoo.com',6000)
4061 row created.
407SQL> /
408Enter value for eno: 03
409Enter value for ename: John
410Enter value for address: Dadar
411Enter value for email: john@outlook.com
412Enter value for salary: 5000
413old 1: INSERT INTO Emp VALUES(&Eno,'&Ename','&Address','&Email',&Salary)
414new 1: INSERT INTO Emp VALUES(03,'John','Dadar','john@outlook.com',5000)
4151 row created.
416SQL> /
417Enter value for eno: 04
418Enter value for ename: Abhishek
419Enter value for address: Ghatkopar
420Enter value for email: abhi@live.com
421Enter value for salary: 4800
422old 1: INSERT INTO Emp VALUES(&Eno,'&Ename','&Address','&Email',&Salary)
423new 1: INSERT INTO Emp VALUES(04,'Abhishek','Ghatkopar','abhi@live.com',4800)
4241 row created.
425SQL> /
426Enter value for eno: 05
427Enter value for ename: Vikas
428Enter value for address: Bandra
429Enter value for email: vikas@gmail.com
430Enter value for salary: 4700
431old 1: INSERT INTO Emp VALUES(&Eno,'&Ename','&Address','&Email',&Salary)
432new 1: INSERT INTO Emp VALUES(05,'Vikas','Bandra','vikas@gmail.com',4700)
4331 row created.
434SQL> SELECT * FROM Emp;
435 ENO ENAME ADDRESS EMAIL SALARY
436---------- ---------- ---------- ------------------ ----------
437 1 Rizwan Kurla rizwan@gmail.com 6500
438 2 PintuNaupada pintu@yahoo.com 6000
439 3 John Dadar john@outlook.com 5000
440 4 Abhishek Ghatkopar abhi@live.com 4800
441 5 Vikas Bandra vikas@gmail.com 4700
442SQL> COMMIT;
443Commit complete.
444User1
445SQL> CREATE TABLE Emp1 AS SELECT * FROM Emp@linksys WHERE Eno <=3;
446Table created.
447SQL> SELECT * FROM Emp1;
448 ENO ENAME ADDRESS EMAIL SALARY
449---------- ---------- ---------- ------------------ ----------
450 1 Rizwan Kurla rizwan@gmail.com 6500
451 2 PintuNaupada pintu@yahoo.com 6000
452 3 John Dadar john@outlook.com 5000
453User2
454SQL> CREATE TABLE Emp2 AS SELECT * FROM Emp@linksys WHERE Eno BETWEEN 4 AND 5;
455Table created.
456SQL> SELECT * FROM Emp2;
457 ENO ENAME ADDRESS EMAIL SALARY
458---------- ---------- ---------- ------------------ ----------
459 4 Abhishek Ghatkopar abhi@live.com 4800
460 5 Vikas Bandra vikas@gmail.com 4700
461Q. Find the salary of all the employees.
462SQL> SELECT Salary FROM Emp1 UNION SELECT Salary FROM Emp2;
463 SALARY
464----------
465 4700
466 4800
467 5000
468 6000
469 6500
470
471Q. Find the email of all the employees where salary is greater than 5000.
472SQL> SELECT Email FROM Emp1 WHERE Salary > 5000 UNION SELECT Email FROM Emp2 WHERE Salary > 5000;
473EMAIL
474------------------
475pintu@yahoo.com
476rizwan@gmail.com
477Q. Find the employee name and email where employee number is known.
478SQL> SELECT Ename, Email FROM Emp1 WHERE Eno = 4 UNION SELECT Ename, Email FROM Emp2 WHERE Eno = 04;
479ENAME EMAIL
480---------- ------------------
481Abhishek abhi@live.com
482Q. Find the employee name and address where employee number is known.
483SQL> SELECT Ename,Address FROM Emp1 WHERE Eno = 01 UNION SELECT Ename,Address FROM Emp2 WHERE Eno = 05;
484ENAME ADDRESS
485---------- ----------
486Rizwan Kurla
487
488SQL> CREATE OR REPLACE TRIGGER temp
4892 AFTER INSERT ON Emp FOR EACH ROW
4903 BEGIN
4914 INSERT INTO Emp1@linku1 VALUES(:new.Eno, :new.Ename, :new.Address);
4925 INSERT INTO Emp2@linku2 VALUES(:new.Eno, :new.Email, :new.Salary);
4936 END;
4947 /
495Trigger created.
496SQL> COMMIT;
497Commit complete.
498User1
499SQL> CREATE TABLE Emp1 AS SELECT Eno,Ename,Address FROM Emp@linksys;
500Table created.
501SQL> SELECT * FROM Emp1;
502 ENO ENAME ADDRESS
503---------- ---------- ----------
504 1 Rizwan Kurla
505 2 PintuNaupada
506 3 John Dadar
507 4 Abhishek Ghatkopar
508 5 Vikas Bandra
509
510User2
511SQL> CREATE TABLE Emp2 AS SELECT Eno,Email,Salary FROM Emp@linksys;
512Table created.
513SQL> SELECT * FROM Emp2;
514
515 Practical No: 4(db)
516
517Topic: Object Oriented Databases
518SQL> Create or replace type AddrType1 as object(
519 Pincode number(5),
520 Street char(20),
521 City varchar2(50),
522 state varchar2(40),
523 no number(4) );
524 /
525
526Type created.
527
528SQL> create or replace type BranchType as object(address AddrType1,phone1 integer,
529 phone2 integer );
530 /
531
532Type created.
533
534SQL> create or replace type BranchTableType as table of BranchType;
535 /
536
537Type created.
538
539SQL> create or replace type AuthorType as object( name varchar2(50), addr AddrType1 );
540 /
541
542Type created.
543
544SQL> create table authors of AuthorType;
545
546Table created.
547
548SQL> create or replace type AuthorListType as varray(10) of ref AuthorType;
549 /
550
551Type created.
552
553SQL> create or replace type PublisherType as object( name varchar2(50),
554addr AddrType1, branches BranchTableType);
555 /
556
557Type created.
558
559SQL> create table books( title varchar2(50), year date, published_by ref PublisherType,
560 authors AuthorListType);
561
562Table created.
563
564SQL> create table Publishers of PublisherType NESTED TABLE branches STORE as branchtable;
565
566Table created.
567
568
569Step 2: Insertion of different values in the tables
570
571Table Name:Authors
572Format : Authors(name, addr:<pincode,street,city,state,no>)
573
574SQL> insert into Authors values('stallings', AddrType1(5002,'sstreet','pune','mha',04));
575
5761 row created.
577
578SQL>
579SQL> insert into Authors values('stallings', AddrType1(7007,'sstreet','mumbai','mha',1007));
580
5811 row created.
582
583SQL> insert into Authors values('Navathe', AddrType1(7008,'nstreet','nasik','mha',08));
584
5851 row created.
586
587SQL> insert into Authors values('Dhumdhare', AddrType1(7003,'dstreet','mumbai','mha',1003));
588
5891 row created.
590
591
592Table name : Publishers
593Format : Publishers(name, addr:<pincode,street,city,state,no>, branches:set of<address:<pincode,street,city,state,no>,phone1,phone2>)
594
595/*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*/
596
597SQL> insert into Publishers values('tata',AddrType1(4002,'rstreet','mumbai','mha',03),
598BranchTableType(BranchType( AddrType1(5002,'fstreet','mumbai','mha',03),23406,69896)));
599
6001 row created.
601
602SQL> insert into Publishers values('seth',AddrType1(7007,'sstreet','mumbai','mha',1007),
603 BranchTableType(BranchType(AddrType1(7007,'sstreet','mumbai','mha',1007),4543545,8676775)));
604
6051 row created.
606
607SQL> insert into Publishers values('joshi',AddrType1(7008,'sstreet','mumbai','mha',1007),
608 BranchTableType(BranchType(AddrType1(1002,'sstreet','nasik','mha',1007),456767,7675757)));
609
6101 row created.
611SQL> select * from Authors;
612
613Table Name3: books
614Format : books(title,year,published_by: ref<Publishers>, authors:list of ref Author)
615
616SQL> insert into books select 'java','28-may-1983', ref(pub), AuthorListType(ref(aut)) from
617 Publishers pub, Authors aut where pub.name='joshi' and aut.name=' Dhumdhare ';
618
6190 rows created.
620
621SQL> insert into books
622 select 'java','28-may-1983',ref(pub),AuthorListType(ref(aut)) from
623 Publishers pub,Authors aut where pub.name='joshi' and aut.name='Dhumdhare';
624
6251 row created.
626
627SQL> insert into books
628 select 'java','28-may-1983',ref(pub),AuthorListType(ref(aut)) from
629 Publishers pub,Authors aut where pub.name='seth' and aut.name='Navathe';
630
6311 row created.
632
633SQL> insert into books
634 select 'adv java','28-may-1983',ref(pub), AuthorListType(ref(aut)) from
635 Publishers pub, Authors aut where pub.name='seth' and aut.name='stallings';
636
6372 rows created.
638
639SQL> insert into books
640 select 'DBMS','28-may-1983',ref(pub),AuthorListType(ref(aut)) from
641 Publishers pub,Authors aut where pub.name='tata' and aut.name='Navathe';
642
6431 row created.
644
645SQL> select * from books;
646
647
648Step 3: Firing queries on the created tables
649a) List all of the authors that have the same address as their publisher:
650
651SQL> select a.name from authors a, publishers p where a.addr = p.addr;
652
653NAME
654--------------------------------------------------
655stallings
656
657b) List all of the authors that have the same pin code as their publisher:
658
659
660SQL> select a.name from authors a, publishers p where a.addr.pincode = p.addr.pincode;
661
662NAME
663--------------------------------------------------
664stallings
665Navathe
666
667c) select List all books that have 2 or more authors:
668
669SQL> select * from books b where 1 <( select count(*)from table(b.authors));
670
671no rows selected
672d) List the title of the book that has the most authors:
673
674SQL> Select title from books b, table(b.authors) group by title having count(*) =
675 (select max(count(*)) from books b, table(b.authors) group by title);
676
677TITLE
678--------------------------------------------------
679adv java
680java
681
682Name of authors who have not published a book:
683SQL> select a.name from authors a where not exists( select b.title from books b, table(b.authors)
684 where a.name = name);
685
686no rows selected
687
688 +++_-_-Practical-5(db)++++++
689
690// 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.
691
692SQL> create or replace type ttype as object(tid number ,tname varchar2(20),tcost number);
6932 /
694Type created.
695SQL> create or replace type ctype as object(cid number ,cname varchar2(20),members number);
6962 /
697Type created.
698SQL> create or replace type bktype as object(tid number, cid number, bkdt date, frmdt date, todt date);
6992 /
700SQL> create table tour of ttype;
701Table created.
702SQL> create table customer of ctype;
703Table created.
704SQL> create table booking of bktype;
705Table created.
706SQL> insert into tour values (ttype(001,'kashmir',20000));
7071 row created.
708
709SQL> insert into tour values (ttype(002,'kanyakumari',25000));
7101 row created.
711SQL> insert into tour values (ttype(005,'assam',22000));
7121 row created.
713SQL> insert into tour values (ttype(007,'dombivli',23000));
7141 row created.
715 SQL> insert into tour values (ttype(008,'ghatkopar',24000));
7161 row created.
717SQL> insert into tour values (ttype(010,'nalasopara',26000));
7181 row created.
719SQL> insert into customer values (ctype(001,'aditya chachlani',3));
7201 row created.
721SQL> insert into customer values (ctype(003,'manish birla',2));
7221 row created.
723SQL> insert into customer values (ctype(004,'neeta singh',4));
7241 row created.
725SQL> insert into customer values (ctype(006,'kajal rathod',1));
7261 row created.
727SQL> insert into customer values (ctype(008,'vibhuti mishra',6));
7281 row created.
729SQL> insert into customer values (ctype(012,'happu singh',12));
7301 row created.
731SQL> select * from tour;
732 TID TNAME TCOST
733---------- -------------------- ----------
734 1 kashmir 20000
735 2 kanyakumari 25000
736 5 assam 22000
737 7 dombivli 23000
738 8 ghatkopar 24000
739 10 nalasopara 26000
7406 rows selected.
741SQL> select * from customer;
742 CID CNAME MEMBERS
743---------- -------------------- ----------
744 1 adityachachlani 3
745 3 manishbirla 2
746 4 neetasingh 4
747 6 kajal rathod 1
748 8 vibhutimishra 6
749 12 happusingh 12
7506 rows selected.
751
752SQL> 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')));
7531 row created.
754SQL> 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')));
7551 row created.
756SQL> 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')));
7571 row created.
758SQL> 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')));
7591 row created.
760SQL> 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')));
7611 row created.
762SQL> 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')));
7631 row created.
764
765
766SQL> select * from booking;
767 TID CID BKDT FRMDT TODT
768---------- ---------- --------- --------- ---------
769 1 12 25-JUL-18 28-JUL-18 30-JUL-18
770 2 6 26-JUL-18 01-AUG-18 03-AUG-18
771 5 3 25-JUL-18 02-AUG-18 05-AUG-18
772 7 8 01-AUG-18 06-AUG-18 12-AUG-18
773 8 4 08-AUG-18 10-AUG-18 15-AUG-18
774 10 1 05-AUG-18 12-AUG-18 16-AUG-18
7756 rows selected.
776Q. Find name of customer whose tour name is Kashmir.
777SQL> 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'));
778CNAME
779--------------------
780Happusingh
781
782Q. Find total number of members belongs to customer Manish Birla.
783SQL> select c.members from customer c where cname='manish birla' ;
784 MEMBERS
785----------
786 2
787
788
789Q. Find name of tour who have customer with maximum number of members.
790SQL> 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 )));
791 TID TNAME TCOST
792---------- -------------------- ----------
793 1 kashmir 20000
794
795SQL> select * from tour t where t.tid in (select b.tid from booking b where trunc(frmdt,'mm')=to_date('aug','mm'));
796 TID TNAME TCOST
797---------- -------------------- ----------
798 2 kanyakumari 25000
799 5 assam 22000
800 7 dombivli 23000
801 8 ghatkopar 24000
802 10 nalasopara 26000
803
804Practical-6(db)
805// Create a temporal database and issue queries on it.
806
807SQL> CREATE TABLE tb1Emp_appnt(acc_no NUMBER(3), name VARCHAR2(10), recruitment_date DATE, retirement_date DATE);
808Table created.
809SQL> INSERT INTO tb1Emp_appnt VALUES(101,'Aditya',TO_DATE('2-3-2001','DD-MM-YYYY'),TO_DATE('13-04-2005','DD-MM-YYYY'));
8101 row created.
811SQL> INSERT INTO tb1Emp_appnt VALUES(102,'Medha',TO_DATE('12-8-2002','DD-MM-YYYY'),TO_DATE('12-07-2004','DD-MM-YYYY'));
8121 row created.
813SQL> INSERT INTO tb1Emp_appnt VALUES(103,'Rahul',TO_DATE('11-3-2002','DD-MM-YYYY'),TO_DATE('13-07-2005','DD-MM-YYYY'));
8141 row created.
815SQL> INSERT INTO tb1Emp_appnt VALUES(104,'Karan',TO_DATE('2-3-2003','DD-MM-YYYY'),TO_DATE('13-09-2005','DD-MM-YYYY'));
8161 row created.
817SQL> INSERT INTO tb1Emp_appnt VALUES(105,'Rizwan',TO_DATE('8-7-2000','DD-MM-YYYY'),TO_DATE('2-03-2001','DD-MM-YYYY'));
8181 row created.
819SQL> INSERT INTO tb1Emp_appnt VALUES(106,'Alok',TO_DATE('17-9-1999','DD-MM-YYYY'),TO_DATE('13-12-2004','DD-MM-YYYY'));
8201 row created.
821SQL> INSERT INTO tb1Emp_appnt VALUES(107,'Ram',TO_DATE('12-11-2001','DD-MM-YYYY'),TO_DATE('13-02-2005','DD-MM-YYYY'));
8221 row created.
823SQL> INSERT INTO tb1Emp_appnt VALUES(108,'Ganesh',TO_DATE('16-1-2001','DD-MM-YYYY'),TO_DATE('2-03-2001','DD-MM-YYYY'));
8241 row created.
825SQL> INSERT INTO tb1Emp_appnt VALUES(109,'Amey',TO_DATE('21-4-2002','DD-MM-YYYY'),TO_DATE('13-04-2005','DD-MM-YYYY'));
8261 row created.
827SQL> INSERT INTO tb1Emp_appnt VALUES(110,'Pritish',TO_DATE('28-2-2003','DD-MM-YYYY'),TO_DATE('13-04-2005','DD-MM-YYYY'));
8281 row created.
829SQL> SELECT * FROM tb1Emp_appnt;
830 ACC_NO NAME RECRUITME RETIREMEN
831---------- ---------- --------- ---------
832 101 Aditya 02-MAR-01 13-APR-05
833 102 Medha 12-AUG-02 12-JUL-04
834 103 Rahul 11-MAR-02 13-JUL-05
835 104 Karan 02-MAR-03 13-SEP-05
836 105 Rizwan 08-JUL-00 02-MAR-01
837 106 Alok 17-SEP-99 13-DEC-04
838 107 Ram 12-NOV-01 13-FEB-05
839 108 Ganesh 16-JAN-01 02-MAR-01
840 109 Amey 21-APR-02 13-APR-05
841 110 Pritish 28-FEB-03 13-APR-05
84210 rows selected.
843SQL> SELECT * FROM tb1Emp_appnt WHERE recruitment_date = TO_DATE('2-03-2001','dd-mm-yyyy');
844 ACC_NO NAME RECRUITME RETIREMEN
845---------- ---------- --------- ---------
846 101 Aditya 02-MAR-01 13-APR-05
847
848SQL> SELECT * FROM tb1Emp_appnt WHERE retirement_date = TO_DATE('2-03-2001','dd-
849mm-yyyy');
850 ACC_NO NAME RECRUITME RETIREMEN
851---------- ---------- --------- ---------
852 105 Rizwan 08-JUL-00 02-MAR-01
853 108 Ganesh 16-JAN-01 02-MAR-01
854SQL> CREATE TABLE tbl_shares(company_name VARCHAR2(10),no_shares NUMBER(5),price NUMBER(5),transaction_time DATE);
855Table created.
856SQL> INSERT INTO tbl_shares VALUES('Infosys',300,10,TO_DATE('11:45','hh24:mi'));
8571 row created.
858SQL> INSERT INTO tbl_shares VALUES('Wipro',200,20,TO_DATE('06:45','hh24:mi'));
8591 row created.
860SQL> INSERT INTO tbl_shares VALUES('Himalaya',100,15,TO_DATE('17:45','hh24:mi'));
8611 row created.
862SQL> INSERT INTO tbl_shares VALUES('MBT',100,20,TO_DATE('18:45','hh24:mi'));
8631 row created.
864SQL> INSERT INTO tbl_shares VALUES('Patni',500,10,TO_DATE('11:45','hh24:mi'));
8651 row created.
866SQL> SELECT * FROM tbl_shares;
867COMPANY_NA NO_SHARES PRICE TRANSACTI
868---------- ---------- ---------- ---------
869Infosys 300 10 01-AUG-18
870Wipro 200 20 01-AUG-18
871Himalaya 100 15 01-AUG-18
872MBT 100 20 01-AUG-18
873Patni 500 10 01-AUG-18
874SQL> ALTER SESSION SET nls_date_format = 'HH24:MI';
875Session altered.
876(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.)
877SQL> SELECT * FROM tbl_shares;
878COMPANY_NA NO_SHARES PRICE TRANS
879---------- ---------- ---------- -----
880Infosys 300 10 11:45
881Wipro 200 20 06:45
882Himalaya 100 15 17:45
883MBT 100 20 18:45
884Patni 500 10 11:45
885Q: Find all the names of a company whose share price is more than Rs. 1000 at 11:45 AM.
886SQL> SELECT no_shares*price FROM tbl_shares WHERE no_shares*price > 1000 AND transaction_time = TO_DATE('11:45','hh24:mi');
887NO_SHARES*PRICE
888---------------
889 3000
890 5000
891*Q: Find the name of company which has highestshare price at 5:00 PM.
892SQL> SELECT MAX(no_shares*price) FROM tbl_shares WHERE transaction_time< TO_DATE('17:00','hh24:mi');
893MAX(NO_SHARES*PRICE)
894--------------------
895 5000
896
897 ++++_-_++++_-_Practical-7(db)_-_++++
898
899// Formulate a database using active rules with row and statement level.
900
901SQL> create table project(pno varchar2(2) primary key, pname varchar2(20),thrs number);
902Table created.
903SQL> create table emp(eno varchar2(2) primary key,ename varchar2(20),hrs number, pno varchar2(2) references project(pno));
904Table created.
905SQL> insert into project values('p1','java',0);
9061 row created.
907SQL> insert into project values('p2','xml',0);
9081 row created.
909SQL> insert into project values('p3','php',0);
9101 row created.
911(Create following triggers in notepad. Save within directory D with extension .sql)
912Trigger1
913create or replace trigger t1 after insert on emp for each row
914begin
915update project set thrs = thrs+ :new.hrs where pno = :new.pno;
916end;
917/
918Trigger2
919create or replace trigger t2 after update of hrs on emp for each row
920begin
921update project set thrs = (thrs- :old.hrs )+:new.hrs where pno = :old.pno;
922end;
923/
924Trigger3
925create or replace trigger t3 after update of pno on emp for each row
926begin
927update project set thrs = thrs+ :old.hrs where pno = :new.pno;
928update project set thrs = thrs- :old.hrs where pno = :old.pno;
929end;
930/
931Trigger4
932create or replace trigger t4 after delete on emp for each row
933begin
934update project set thrs = thrs- :old.hrs where pno = :old.pno;
935end;
936/
937
938
939SQL> @D:\msc\t1
940Trigger created.
941SQL> @D:\msc\t2
942Trigger created.
943SQL> @D:\msc\t3
944Trigger created.
945SQL> @D:\msc\t4
946Trigger created.
947SQL> insert into emp values ('&eno','&ename',&hrs,'&pno');
948Enter value for eno: 1
949Enter value for ename: vijay
950Enter value for hrs: 10
951Enter value for pno: p1
952old 1: insert into emp values ('&eno','&ename',&hrs,'&pno')
953new 1: insert into emp values ('1','vijay',10,'p1')
9541 row created.
955SQL> /
956Enter value for eno: 2
957Enter value for ename: akshay
958Enter value for hrs: 15
959Enter value for pno: p2
960old 1: insert into emp values ('&eno','&ename',&hrs,'&pno')
961new 1: insert into emp values ('2','akshay',15,'p2')
9621 row created.
963SQL> /
964Enter value for eno: 3
965Enter value for ename: tejas
966Enter value for hrs: 20
967Enter value for pno: p2
968old 1: insert into emp values ('&eno','&ename',&hrs,'&pno')
969new 1: insert into emp values ('3','tejas',20,'p2')
9701 row created.
971SQL> /
972Enter value for eno: 4
973Enter value for ename: komal
974Enter value for hrs: 15
975Enter value for pno: p3
976old 1: insert into emp values ('&eno','&ename',&hrs,'&pno')
977new 1: insert into emp values ('4','komal',15,'p3')
9781 row created.
979SQL> select * from project;
980
981PN PNAME THRS
982-- -------------------- ----------
983p1 java 10
984p2 xml 35
985p3 php 15
986SQL> select * from emp;
987EN ENAME HRS PN
988-- -------------------- ---------- --
9891 vijay 10 p1
9902 akshay 15 p2
9913 tejas 20 p2
9924 komal 15 p3
993SQL> update emp set hrs= 15 where eno=1;
9941 row updated.
995SQL> select * from project;
996PN PNAME THRS
997-- -------------------- ----------
998p1 java 15
999p2 xml 35
1000p3 php 15
1001SQL> update emp set pno='p1' where eno=4;
10021 row updated.
1003SQL> select * from project;
1004PN PNAME THRS
1005-- -------------------- ----------
1006p1 java 30
1007p2 xml 35
1008p3 php 0
1009
1010SQL> select * from project;
1011PN PNAME THRS
1012-- -------------------- ----------
1013p1 java 30
1014p2 xml 35
1015p3 php 0
1016SQL> delete from emp where eno=4;
10171 row deleted.
1018SQL> select * from project;
1019PN PNAME THRS
1020-- -------------------- ----------
1021p1 java 15
1022p2 xml 35
1023p3 php 0
1024
1025
1026 -_-_-_-Practical-8(db)_--_-_-
1027// Formulate a database using active rules with row and statement level.
1028SQL> CREATE TABLE EMPLOYEE(NAME VARCHAR2(10), SSN NUMBER(5), SALARY NUMBER(5), DNO NUMBER(3) NULL, SUPERVISOR_SSN NUMBER(3));
1029SQL> SELECT * FROM AEMPLOYEE;
1030
1031NAME SSN SALARY DNO SUPERVISOR_SSN
1032---------- ---------- ---------- ---------- --------------
1033anita 123 10000 213 345
1034sunita 124 12000 214 346
1035nilima 125 13000 215 347
1036vinita 126 14000 216 348
1037nita 127 14000 216 348
1038
1039SQL> CREATE TABLE ADEPARTMENT (DNAME VARCHAR2(10), DNO NUMBER(3), TOTAL_SAL NUMBER(10), MANAGER_SSN NUMBER(3));
1040
1041SQL> SELECT * FROM ADEPARTMENT;
1042
1043DNAME DNO TOTAL_SAL MANAGER_SSN
1044---------- ---------- ---------- -----------
1045sales 213 10000 421
1046manager 214 12000 422
1047director 215 13000 432
1048production 216 28000 433
1049
1050TRIGGER 1
1051
1052CREATE OR REPLACE TRIGGER TOTAL_SAL1
1053AFTER INSERT ON AEMPLOYEE
1054FOR EACH ROW
1055WHEN(NEW.DNO IS NOT NULL)
1056BEGIN
1057UPDATE ADEPARTMENT
1058SET TOTAL_SAL=TOTAL_SAL+:NEW.SALARY
1059WHERE DNO=:NEW.DNO;
1060END;
1061/
1062(Insert data into employee table and check changes in Department table)
1063TRIGGER2
1064CREATE OR REPLACE TRIGGER TOTAL_SAL2
1065AFTER UPDATE OF SALARY ON AEMPLOYEE
1066FOR EACH ROW
1067WHEN(NEW.DNO IS NOT NULL)
1068BEGIN
1069UPDATE ADEPARTMENT
1070SET TOTAL_SAL=TOTAL_SAL+:NEW.SALARY-:OLD.SALARY
1071WHERE DNO=:NEW.DNO;
1072END;
1073/
1074(Update salary into employee table and check changes in Department table)
1075
1076TRIGGER3
1077CREATE OR REPLACE TRIGGER TOTAL_SAL3
1078AFTER UPDATE OF DNO ON AEMPLOYEE
1079FOR EACH ROW
1080BEGIN
1081UPDATE ADEPARTMENT
1082SET TOTAL_SAL=TOTAL_SAL+:NEW.SALARY
1083WHERE DNO=:NEW.DNO;
1084UPDATE ADEPARTMENT
1085SET TOTAL_SAL=TOTAL_SAL-:OLD.SALARY
1086WHERE DNO=:OLD.DNO;
1087END;
1088/
1089(Update DNO into employee table and check changes in Department table)
1090
1091TRIGGER4
1092CREATE OR REPLACE TRIGGER TOTAL_SAL4
1093AFTER DELETE ON AEMPLOYEE
1094FOR EACH ROW
1095WHEN(OLD.DNO IS NOT NULL)
1096BEGIN
1097UPDATE ADEPARTMENT
1098SET TOTAL_SAL=TOTAL_SAL-:OLD.SALARY
1099WHERE DNO=:OLD.DNO;
1100END;
1101/
1102
1103 _-. _-_-_-_-Practical-9(db)_-_&_&__-_-_&
1104
1105// Create a XML data base and demonstrate insert, update and delete operations on these tables. Issue queries on it.
1106
1107SQL> CREATE TABLE employee(
1108 dept_id NUMBER(3),
1109 employee_spec XMLTYPE);
1110Table created.
1111Inserting values into employee table
1112SQL> INSERT INTO employee VALUES
1113 (100,XMLTYPE(
1114 '<employees>
1115 <emp id="1">
1116 <name>Rizwan Ansari</name>
1117 <email>rizwan@outlook.com</email>
1118 <acc_no>111</acc_no>
1119 <dateOfJoining>2015-11-16</dateOfJoining>
1120 </emp>
1121 </employees>')) ;
11221 row created.
1123
1124
1125
1126SQL> INSERT INTO employee VALUES
1127 (200,XMLTYPE(
1128 '<employees>
1129 <emp id="2">
1130 <name>Vijay Sangoi</name>
1131 <email>vijay@gmail.com.com</email>
1132 <acc_no>222</acc_no>
1133 <dateOfJoining>2016-10-28</dateOfJoining>
1134 </emp>
1135 </employees>')) ;
11361 row created.
1137SQL> INSERT INTO employee VALUES
1138 (300,XMLTYPE(
1139 '<employees>
1140 <emp id="3">
1141 <name>Abhishek Yadav</name>
1142 <email>abhishek@yahoo.com</email>
1143 <acc_no>333</acc_no>
1144 <dateOfJoining>2010-4-14</dateOfJoining>
1145 </emp>
1146 </employees>')) ;
11471 row created.
1148Retrieve all information from employee table
1149SQL> SELECT * FROM employee;
1150
1151 DEPT_ID
1152----------
1153EMPLOYEE_SPEC
1154--------------------------------------------------------------------------------
1155 100
1156<employees>
1157<emp id="1">
1158<name>Rizwan Ansari</name>
1159<email>rizwan@outl
1160 200
1161<employees>
1162<emp id="2">
1163 DEPT_ID
1164----------
1165EMPLOYEE_SPEC
1166--------------------------------------------------------------------------------
1167<name>Vijay Sangoi</name>
1168<email>vijay@gmail.
1169 300
1170<employees>
1171<emp id="3">
1172<name>Abhishek Yadav</name>
1173<email>abhishek@y
1174Retrieve the name of employee from employee table
1175SQL> SELECT w.employee_spec.extract
1176 ('/employees/emp/name/text()').getStringVal() "name" FROM employee w
1177 ;
1178
1179
1180name
1181--------------------------------------------------------------------------------
1182Rizwan Ansari
1183Vijay Sangoi
1184Abhishek Yadav
1185Retrieve the acc_no of employee from employee table
1186SQL> SELECT w.employee_spec.extract
1187 ('/employees/emp/acc_no/text()').getStringVal() "acc_no" FROM employee w
1188 ;
1189acc_no
1190--------------------------------------------------------------------------------
1191111
1192222
1193333
1194Retrieve the names, acc_no, email of employees from employee table
1195SQL> SELECT
1196 w.employee_spec.extract('/employees/emp/name/text()').getStringVal() "name",
1197 w.employee_spec.extract('/employees/emp/acc_no/text()').getStringVal() "acc_no",
1198 w.employee_spec.extract('/employees/emp/email/text()').getStringVal() "email"
1199 FROM employee w ;
1200name
1201--------------------------------------------------------------------------------
1202acc_no
1203--------------------------------------------------------------------------------
1204email
1205--------------------------------------------------------------------------------
1206Rizwan Ansari
1207111
1208rizwan@outlook.com
1209Vijay Sangoi
1210222
1211vijay@gmail.com.com
1212name
1213--------------------------------------------------------------------------------
1214acc_no
1215--------------------------------------------------------------------------------
1216email
1217--------------------------------------------------------------------------------
1218Abhishek Yadav
1219333
1220abhishek@yahoo.com
1221Updating XMLType
1222SQL> UPDATE employee w SET employee_spec = XMLTYPE
1223 ('<employees>
1224 <emp id="5">
1225 <name>Raheem Khan</name>
1226 </emp>
1227 </employees>')
1228 WHERE
1229 w.employee_spec.extract('//acc_no/text()').getStringVal() = '333'
1230 ;
12311 row updated.
1232SQL> SELECT
1233 w.employee_spec.extract('/employees/emp/name/text()').getStringVal() "name" FROM employee w
1234 ;
1235
1236name
1237--------------------------------------------------------------------------------
1238Rizwan Ansari
1239Vijay Sangoi
1240Raheem Khan
1241Deleting an XMLType Column Row
1242SQL> DELETE FROM employee w WHERE
1243 w.employee_spec.extract('/employees/emp/acc_no/text()').getStringVal() = '111'
1244 ;
12451 row deleted.
1246SQL> SELECT
1247 w.employee_spec.extract('/employees/emp/acc_no/text()').getStringVal() "acc_no" FROM employee w
1248 ;
1249acc_no
1250--------------------------------------------------------------------------------
1251222