This repository has been archived by the owner on Oct 10, 2021. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
ViewsIndexesSequences&Synonyms.txt
163 lines (84 loc) · 2.9 KB
/
ViewsIndexesSequences&Synonyms.txt
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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
Note : Use the tables created for Joins,SubQueries & SetOperators
1) Create a view jview from journey table such that it contains day, time and route id with j_day, j_time and j_rid as column headings.
SQL> create view jview(j_day,j_time,j_rid) as select doj,time,rid from journey ;
View created.
2) Update the jview such that j_day is 20-jan-2018 and j_rid is 301.
SQL> update jview set j_date = '20-JAN-18' where j_rid = 301 ;
0 rows updated.
3) Select the contents of the corresponding table that jview is based and check whether the update has occurred.
SQL> select doj,time,rid from journey ;
DOJ TIME RID
---- ---- ---
29-AUG-19 5:00 1
05-JAN-19 14:00 2
14-FEB-19 10:00 3
10-MAR-19 22:00 4
4 rows selected.
4) Create a synonym passenger for ticket_detail table.
SQL> create synonym passenger for ticket_detail ;
Synonym created.
5) Display the contents of passenger.
SQL> select * from passenger ;
TNO NAME S AGE FARE
---- ----- -- --- ----
1 Haley F 18 850
2 Phil M 45 600
3 John M 19 650
4 Dylan M 21 400
4 rows selected.
6) Create a synonym bus details for busroute1 table.
SQL> create synonym route_detail for bus_route ;
Synonym created.
7) Drop the passenger synonym created previously.
SQL> drop synonym passenger ;
Synonym dropped.
8) Create an index on route_id column on busroute table.
SQL> create index dis_index on bus_route(dis) ;
Index created.
9) Drop the index what you had created.
SQL> drop index dis_index ;
Index dropped.
10) Create a sequence ticket where minimum value is 1 and maximum value is 20 and increment it with 2 starting with 1.
SQL> create sequence ticseq minvalue 1 maxvalue 20 increment by 2 start with 1 ;
Sequence created.
11) Insert the sequence ticket into the ticket column of ticket table.
SQL> alter table ticket(t_seq number) ;
Table altered.
SQL> update ticket set t_seq = ticseq.nextval ;
4 rows updated.
12) Alter the sequence such that the maximum value is 25.
SQL> alter sequence ticseq maxvalue 25 ;
Sequence altered ;
13) List all the sequences created by you.
SQL> select sequence_name from user_sequences ;
SEQUENCE_NAME
------------
TICSEQ
14) List all the views created by you.
SQL> selec view_name from user_views ;
VIEW_NAME
--------
JVIEW
15) List all the indexes created by you.
SQL> select index_name from user_indexes ;
INDEX_NAME
---------
SYS_C005423
SYS_C005424
SYS_C005425
SYS_C005429
16) Drop all the database objects created by you.
SQL> drop sequence ticseq ;
Sequence dropped.
SQL> drop view jview ;
View dropped.
SQL> drop table busdetails ;
Table dropped.
SQL> drop table bus_routes;
Table dropped.
SQL> drop table journey ;
Table dropped.
SQL> drop table ticket ;
Table dropped.
SQL> drop table ticketdetail ;
Table dropped.