===> SQL Queries: Creating a View
create view empvu80
as
select employee_id,last_name,salary
from employees
where department_id = 80;
describe empvu80
Results:
create view succeeded.
describe empvu80
Name Null Type
------------------------------ -------- ------------------------------------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NUMBER(8,2)
===> SQL Queries: Creating a View using Aliases
create view salvu80
as
select employee_id ID_NUMBER,
last_name NAME,
salary*12 ANN_SALARY
from employees
where department_id = 50;
describe salvu80;
Results:
describe salvu80
Name Null Type
------------------------------ --------------------------------------------------
ID_NUMBER NOT NULL NUMBER(6)
NAME NOT NULL VARCHAR2(25)
ANN_SALARY
Retrieve data from view:
select * from salvu80
Results:
ID_NUMBER NAME ANN_SALARY
---------------------- ------------------------- ----------------------
120 Weiss 96000
121 Fripp 98400
122 Kaufling 94800
123 Vollman 78000
124 Mourgos 69600
125 Nayer 38400
126 Mikkilineni 32400
127 Landry 28800
128 Markle 26400
129 Bissot 39600
130 Atkinson 33600
131 Marlow 30000
132 Olson 25200
133 Mallin 39600
134 Rogers 34800
135 Gee 28800
136 Philtanker 26400
137 Ladwig 43200
138 Stiles 38400
139 Seo 32400
140 Patel 30000
141 Rajs 42000
142 Davies 37200
143 Matos 31200
144 Vargas 30000
180 Taylor 38400
181 Fleaur 37200
182 Sullivan 30000
183 Geoni 33600
184 Sarchand 50400
185 Bull 49200
186 Dellinger 40800
187 Cabrio 36000
188 Chung 45600
189 Dilly 43200
190 Gates 34800
191 Perkins 30000
192 Bell 48000
193 Everett 46800
194 McCain 38400
195 Jones 33600
196 Walsh 37200
197 Feeney 36000
198 OConnell 31200
199 Grant 31200
45 rows selected
===> SQL Queries: Modifying A View
create or replace view empvu80
(id_number,name,sal,department_id)
as
select employee_id,
first_name || '' || last_name,
salary,
department_id
from employees
where department_id = 80;
select * from empvu80
Results:
ID_NUMBER NAME SAL DEPARTMENT_ID
---------------------- --------------------------------------------- ---------------------- ----------------------
145 JohnRussell 14000 80
146 KarenPartners 13500 80
147 AlbertoErrazuriz 12000 80
148 GeraldCambrault 11000 80
149 EleniZlotkey 10500 80
150 PeterTucker 10000 80
151 DavidBernstein 9500 80
152 PeterHall 9000 80
153 ChristopherOlsen 8000 80
154 NanetteCambrault 7500 80
155 OliverTuvault 7000 80
156 JanetteKing 10000 80
157 PatrickSully 9500 80
158 AllanMcEwen 9000 80
159 LindseySmith 8000 80
160 LouiseDoran 7500 80
161 SarathSewall 7000 80
162 ClaraVishney 10500 80
163 DanielleGreene 9500 80
164 MatteaMarvins 7200 80
165 DavidLee 6800 80
166 SundarAnde 6400 80
167 AmitBanda 6200 80
168 LisaOzer 11500 80
169 HarrisonBloom 10000 80
170 TaylerFox 9600 80
171 WilliamSmith 7400 80
172 ElizabethBates 7300 80
173 SunditaKumar 6100 80
174 EllenAbel 11000 80
175 AlyssaHutton 8800 80
176 JonathonTaylor 8600 80
177 JackLivingston 8400 80
179 CharlesJohnson 6200 80
34 rows selected
===> SQL Queries: Creating a Complex View
create or replace view dept_sum_vu
(name,minsal,maxsal,avgsal)
as
select d.department_name,
min(e.salary),
max(e.salary),
avg(e.salary)
from employees e join departments d
on (e.department_id = d.department_id)
group by d.department_name;
select * from dept_sum_vu;
Results:
create or replace view succeeded.
NAME MINSAL MAXSAL AVGSAL
------------------------------ ---------------------- ---------------------- ----------------------
Administration 4400 4400 4400
Accounting 8300 12000 10150
Executive 17000 24000 333333333333333333
IT 4200 9000 576
Purchasing 2500 11000 4150
Human Resources 6500 6500 6500
Public Relations 10000 10000 10000
Shipping 2100 8200 3475.55555555555556
Finance 6900 12000 8600
Sales 6100 14000 8955.882352941176
Marketing 6000 13000 9500
===> SQL Queries: Creating Removing a View
select * from empvu80;
drop view empvu80;
select * from empvu80;
Results:
ID_NUMBER NAME SAL DEPARTMENT_ID
---------------------- --------------------------------------------- ---------------------- ----------------------
145 JohnRussell 14000 80
146 KarenPartners 13500 80
147 AlbertoErrazuriz 12000 80
148 GeraldCambrault 11000 80
149 EleniZlotkey 10500 80
150 PeterTucker 10000 80
151 DavidBernstein 9500 80
152 PeterHall 9000 80
153 ChristopherOlsen 8000 80
154 NanetteCambrault 7500 80
155 OliverTuvault 7000 80
156 JanetteKing 10000 80
157 PatrickSully 9500 80
158 AllanMcEwen 9000 80
159 LindseySmith 8000 80
160 LouiseDoran 7500 80
161 SarathSewall 7000 80
162 ClaraVishney 10500 80
163 DanielleGreene 9500 80
164 MatteaMarvins 7200 80
165 DavidLee 6800 80
166 SundarAnde 6400 80
167 AmitBanda 6200 80
168 LisaOzer 11500 80
169 HarrisonBloom 10000 80
170 TaylerFox 9600 80
171 WilliamSmith 7400 80
172 ElizabethBates 7300 80
173 SunditaKumar 6100 80
174 EllenAbel 11000 80
175 AlyssaHutton 8800 80
176 JonathonTaylor 8600 80
177 JackLivingston 8400 80
179 CharlesJohnson 6200 80
34 rows selected
drop view empvu80 succeeded.
Error starting at line 5 in command:
select * from empvu80
Error at Command Line:5 Column:15
Error report:
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment