· 7 years ago · Dec 14, 2018, 11:30 AM
1-- schema objects are owned by a user account.
2-- a synonymn is a schema object, however a public schema is a non-schema object.
3
4-- db object naming
5-- -- start with a letter,[A-Za-z0-9_#$]. 1<= length<=30
6 -- name is not case sensitive, but stored in UPPER in data dictionary tables.
7 -- if name is quoted, then all references should be quoted as well.
8 -- reserved words can be used in quoted names, they can start with any char or include spaces.
9-- Name Spaces
10 -- names have to be unique in their name spaces
11 -- Users & Roles
12 -- Public Synonymns
13 -- Schema Objects
14 -- NS for indexes
15 -- Indexes
16 -- NS for contraints
17 -- Constraints
18 -- NS for DB objects
19 -- Tables
20 -- views
21 -- synonymns
22 -- sequences
23 -- user defined types
24-- CREATE TABLE
25 -- need to ensure the brackets for column definitions, the commas and a final semicolon
26 -- Constraints can be added in-line or at the end. EXCEPT for NOT NULL. thats possible only with INLINE
27 -- Inline constraints can be named using the CONSTRAINT keyword.
28 -- the inline INVISIBLE keyworkd makes a column invisible.
29 -- it disappears from the DESC, and select *
30 -- can be selected/inserted by specifying the name in a select query.
31 -- For insert, the columns have to be specified in the form INSERT INTO tablename(col,col,inviscol) Values (val1,val2,val3).
32 -- can be made visible in a view by the view's select statement naming the column
33 -- If a view is using the select * (without naming the invisible cols), then the a select from the view that names the inviscol does not work
34 -- For a view to have acceess to the invis col, it has to be named in the view's select.
35
36-- DATA TYPES
37 -- CHAR(n). Fixed, shorter entries are RPADed with spaces. n is optional and assumed to be one. n<=2000
38 -- VARCHAR2(n). 1<= n <= 4000. n is mandatory
39 -- NUMBER(p,s). p,s optional defaults to the largerst values.
40 -- p is the total number of digits, s is the number of digits to the right of the decimal
41 -- NUMBER(4,1) -> 101.55 => 101.6 == total 4 digits, 1 to the right of the decimal and values are rounded.
42 -- DATE - stores time up to seconds as well
43 -- RR - 00-49 21at century, 50-99 20th century
44
45 -- TIMEASTAMP(n=6) - 0<=n<=9 . Stores franctional seconds as well
46 -- TIMESTAMP WITH TIMEZONE (n=6) - 0<=n<=9. Stores a timezone offset or timezone region name
47 -- TIMESTAMP WITH LOCAL TIMEZONE (n=6) - 0<=n<=9. Timezone offset or region code is not actually stored,
48 -- rather the DB converts to the session tine and retutrns to the user.
49
50 -- BLOB - Binary upto 4GB. No unique indexes possible
51 -- CLOB - Char LOB upto 4GB. No unique indexes possible.
52 -- NCLOB - CLOB in unicode.
53-- TRUNCATE TABLE
54 -- removes all rows and indexes, does not fire triggers
55 -- Leaves all child tables intact,
56 -- unless the child tables' FK constraint specifies ON DELETE CASCADE
57 -- In which case, a normal TRUNCATE would fail, a TRUNCATE TABLE <name> CASCADE is required.
58 -- Does not use UNDO space, does not work with FLASHBACK and ROLLBACK
59-- Transaction control
60 -- explit commit is => COMMIT and COMMIT WORK are the same, they commit the changes in the current transaction
61 -- Implicit commit occurs when a DDL is fired, even if the DDL fails. commits are done before and after a DDL
62 -- A normal exit from the session performs a commit as well
63 -- an abnormal termination of a session will issue a rollback
64 -- SAVEPOINT. Syntax is SAVEPOINT <name>. Name follws DB object naming conventions.
65 -- ROLLBACK TO <name> or ROLLBACK WORK TO <name> are the same. If Rollback names an invalid savepoint,
66 -- its a warning, and changes are still pending in the transaction.
67 -- Duplicate name for the save point will overwrite the savepoint
68 -- Once a commit is done, savepoints are erased and using them is an error.
69 --
70
71-- VIEWS
72-- Any select statement can be used
73-- Should have valid column aliases
74-- use the OR REPLACE keywords to replace an exisitng view with the same name without warning
75-- the view should contain enough columns to satisfy the tables' constraints to be updatable.
76-- if a view does not have a NOT NULL column from an underlying table, then you still maybe able to UPDATE or DELETE, but not insert
77-- A view's select statement cannot be updated with ALTER VIEW. ALTER VIEW can be used to recompile a view explicitly.
78-- You cannot INSERT update or delete from a view that uses
79 -- aggregate functions or group by or analytic queries with analyticFn() over()
80 -- distinct keyword
81 -- mostly all joins and subsqueries
82-- For example : the following is a view on the employee table alone, and selects all the non null columns
83-- but also adds a rank column. But even if you dont want to insert in to this, updating via this view is not allowed
84create or replace view HR_TEST as
85select employee_id,
86 first_name,
87 last_name,email,
88 hire_date,
89 job_id,
90 department_id,
91 rank() over(order by department_id) avg_sal
92 from employees;
93-- This query tries to insert only the non null columns, but FAILS !
94insert into hr_test(employee_id,first_name,last_name, email, hire_date, job_id)
95 values(EMPLOYEES_SEQ.nextval,'JOE','SMITH','JSMITH001',SYSDATE,'IT_PROG');
96
97-- Change the view to not have the analytic function like so :
98create or replace view HR_TEST as
99select employee_id,
100 first_name,
101 last_name,email,
102 hire_date,
103 job_id,
104 department_id
105 from employees;
106-- Now the same query above will work.
107
108-- LPAD and RPAD
109-- Syntax: LPAD(s1, n, s2)
110-- RPAD(s1, n, s2).
111-- Parameters: s1 (character string—required); n (number—required); s2 (character string—optional; s2 defaults to a single blank space if omitted).
112-- Process: Pad the left of character string s1 (LPAD) or right of character string s1 (RPAD) with character string s2 so that s1 is n characters long.
113-- Output: Character string.
114-- Example: Take a string literal 'Chapter One—I Am Born' and pad it to the right so that the resulting string is 40 characters in length.
115
116select rpad('0123456789',12,'.') from dual; -- 01234567890.. RPads 2 . chars to make the total length 12
117select rpad('0123456789',12,'LONG_STRING') from dual; -- 01234567890LO Rpads the chars in the string, till the total length is reached. can be LONG_STRINGLONGSTRING
118select rpad('0123456789',10,'.') from dual; -- The input stringis 10 chars, no Rpad needed.
119select rpad('0123456789',5,'.') from dual; -- 01234, truncates the string to meet the length requirement.
120select rpad('0123456789',0,'.') from dual; -- NULL desired lenght is 0, all of the input is truncated
121select rpad('0123456789',-1,'.') from dual; -- NULL desired length is negative, but no error.
122select rpad('0123456789',15) from dual; -- uses the default "single space" char as padding
123
124select lpad('0123456789',5,'.') from dual; -- Truncates the string to 5 chars
125select lpad('0123456789',10,'.') from dual; -- No LPad needed desired string length is already met.
126select lpad('0123456789',12,'.') from dual; -- Lpads 2 '.' chars to make the toatal length 12 chars.
127
128-- CONCAT & ||
129-- Syntax: CONCAT(s1, s2)
130-- s1 || s2
131-- Parameters: s1, s2. Both are character strings; both are required.
132-- Process: Concatenates s1 and s2 into one string. a single ' char is the escape char.
133-- Output: Character string.
134
135select concat('Hello',' there') || concat('! How''s it going','?') from dual;
136
137
138-- !!! TRICKY ONE !!!!
139-- LTRIM and RTRIM
140-- Syntax: LTRIM(s1, s2)
141-- RTRIM(s1, s2)
142-- Parameters: s1, s2—both are character strings. s1 is required, and s2 is optional—if omitted, it defaults to a single blank space.
143-- s2 is a 'strip list', after application s1 cannot begin(ltrim) or end(rtrim) with any of the chars in s2.
144-- Process: Removes occurrences of the s2 characters from the s1 string, from either the left side of s1 (LTRIM)
145 -- or the right side of s1 (RTRIM) exclusively.
146-- Output: Character string.
147-- Notes: Ideal for stripping out unnecessary blanks, periods, ellipses, and so on.
148-- basically ensures that the input string does not begin with or end with any of the characters in the S2.
149-- S2 is treated as "any char in this string" and not "match this literal string as-is"
150
151select LTRIM('AAABBAAABBBCC','A') from dual; -- Strips the leftside A's. all of them.
152select LTRIM('AAABBAAABBBCC','C') from dual; -- Nothing stripped, s1 does not have any leading C's
153select LTRIM('AAACCCBBAAABBBCC','ABC') from dual; -- NULL. 'ABC' is a strip list, All A's are stripped, then C's and then B's, then A's, B's and C's
154select LTRIM('AAABBAAABBBCC','ACB') from dual; -- NULL. Stripping the A's leaves a string with B, but B is also in the strip list...
155select LTRIM('AAABBAAABBBDCC','ACB') from dual; -- DCC. Stripping stops when the string begins with D, which is not on the strip list
156
157select RTRIM('AAABBAAABBBCC','A') from dual;
158select RTRIM('AAABBAAABBBCC','C') from dual;
159select RTRIM('AAABBAAABBBCC','BC') from dual;
160select RTRIM('AAABBAAABBBCC','ABC') from dual;
161select RTRIM('AAABBAAABBBCC','ACB') from dual;
162select RTRIM('AAABBAAABDBBCC','ACB') from dual;
163
164
165-- TRIM
166-- Syntax: TRIM(trim_info trim_char FROM trim_source)
167-- Parameters:
168
169-- trim_info is one of these keywords: LEADING, TRAILING, BOTH—if omitted, defaults to BOTH.
170-- trim_char is a single character to be trimmed—if omitted, assumed to be a blank.
171-- trim_source is the source string—if omitted, the TRIM function will return a NULL.
172
173-- Process: Same as LTRIM and RTRIM, with a slightly different syntax. ONLY CHAR IS ALLOWED IN THE TRIMSET!!!jgj
174-- Output: Character string.
175-- If multiple chars are specified in the trim_char, then an error is thrown
176select TRIM(LEADING '0' FROM '000ABC123000') from dual;
177select TRIM(TRAILING '0' FROM '000ABC123000') from dual;
178select TRIM('0' FROM '000ABC123000') from dual;
179select TRIM(LEADING '0' FROM (SELECT '000111' FROM DUAL)) from dual;
180
181select TRIM(LEADING '00' FROM '000ABC123000') from dual;
182
183
184-- INSTR
185-- Syntax: INSTR(s1, s2, pos, n)
186-- Parameters: s1 is the source string (required); s2 is the substring (required);
187 -- pos is the starting position in s1 to start looking for occurrences of s2 (optional, default is 1);
188 -- n is the occurrence of s2 to locate (optional, default is 1).
189 -- If pos is negative, the search in s1 for occurrences of s2 starts at the end of the string and moves backward.
190-- Process: Locates a string within another string (thus the name of the function: IN STRing).
191-- Output: Numeric.
192-- Indexes (position) are 1 based. looking for a string from the 0th position will yield nothing.
193-- Occurence - looking for the 0th or negative occurence will throw an error.
194-- The entire search string is located.
195-- Looking fo
196
197-- first occurence of A = 5
198select INSTR('1234AA7A9','A',1,1) FROM DUAL;
199-- third occurence of A = 8
200select INSTR('1234AA7A9','A',1,3) FROM DUAL;
201-- first occurence of A on or after the 7th char= 8
202select INSTR('1234AA7A9','A',7,1) FROM DUAL;
203-- first occurence of AA = 4, this is the index of the first matched char in the search string
204select INSTR('123AAA7A9','AA',1,1) FROM DUAL;
205-- second occurence of AA = 5, and the first occurence is 4
206select INSTR('123AAA7A9','AA',1,2) FROM DUAL;
207
208-- second occurence of AA = 0 i.e. does not exist in the input string
209select INSTR('1234AA7A9','AA',1,7) FROM DUAL;
210
211-- first occurence of A on or after the 0th position = 0. Startting from 0 will always fail to find the substring.
212select INSTR('1234AA7A9','A',0,1) FROM DUAL;
213
214-- 0th occurence of A on or after the 1st position = ERROR
215select INSTR('1234AA7A9','A',1,0) FROM DUAL;
216
217-- first occurence of AA on or after the 1st char = 5
218select INSTR('1234AA7A9','AA',1,1) FROM DUAL;
219-- 2nd occurence of AA on or after the 1st char = 0 NOT FOUND (there is only one occurence)
220select INSTR('1234AA7A9','AA',1,2) FROM DUAL;
221-- 1st occurence of AA on or after the last char (reverse search) = 10. although the search is in reverse, the indexes are from left to right
222select INSTR('1234AA7A9AA','AA',-1,1) FROM DUAL;
223-- 2nd occurence of AA on or after the last char (reverse search) = 5. although the search is reverse, the index/positions are from left to right.
224select INSTR('1234AA7A9AA','AA',-1,2) FROM DUAL;
225
226
227-- SUBSTR
228-- Syntax: SUBSTR(s, pos, len)
229-- Parameters: s = a character string, required; pos = a number, required; len = a number, optional.
230-- Process: Extracts a substring from s, starting at the pos character of s and continuing for len number of characters.
231 -- If len is omitted, then the substring starts as pos and runs through the end of s.
232 -- If pos is negative, then the function starts at the end of the string and moves backward.
233-- Output: Character string.
234
235-- take from 5th char to the end. No length
236select substr('1234567890',5) from dual;
237-- take from the 5th char, two chars (5th included)
238select substr('1234567890',5,2) from dual;
239-- zero based indexes work here, returns the first char
240select substr('1234567890',0),substr('1234567890',1,1),substr('1234567890',0,1) from dual;
241
242-- negative pos will start from the end
243-- yeilds 7, picks the fourth char from the end, and 1 char long
244select substr('1234567890',-4,1) from dual;
245-- yields 7890, picks the 4th char from the end and ties to pick 8 chars, but the input string runs out before that.
246select substr('1234567890',-4,8) from dual;
247
248-- negaive or zero lengths will give a NULL. No errors
249select substr('1234567890',5,0), substr('1234567890',5,-1) from dual;
250-- Just to prove that its actually a NULL thats returned
251select 'Hello' from Dual where substr('1234567890',5,0) IS null and substr('1234567890',5,-1) is null;
252
253-- CEIL
254-- Syntax: CEIL(n)
255-- Parameters: n is required and is any numeric data type.
256-- Process: CEIL returns the smallest integer that is greater than or equal to n.
257
258-- FLOOR
259-- Syntax: FLOOR(n)
260-- Parameters: n is required and is any numeric data type.
261-- Process: FLOOR returns the largest integer that is less than or equal to n.
262
263-- ROUND—Number
264-- Syntax: ROUND (n, i)
265-- Parameters: n is required, is any number, and can include decimal points. i is an integer and is optional—if omitted, it will default to 0.
266-- Process: n is rounded depending on the value of i.
267 -- If i is zero, n is rounded off to the nearest whole number, in other words, zero decimal points.
268 -- If i is a positive number, n is rounded to i places to the right of the decimal point.
269 -- If i is a negative number, n is rounded to i places to the left of the decimal point. The number 5 is rounded away from zero.
270-- Output: If i is omitted, ROUND returns a value in the same numeric data type as n. If i is specified, ROUND returns a data type of NUMBER.
271
272-- Rounding works with the number of decimals desired. if desired decimals are more than available, nothing is done.
273-- ~Negative decimal points will round on the other side of the decimal.
274select CEIL(5.5), FLOOR(5.5), ROUND(5.5),ROUND(5.49), ROUND(5.49,1),ROUND(5.49,3), ROUND(5.49e3,3),ROUND(555.49,-1), ROUND(555.49,-2)from Dual;
275-- Automatic type conversion works when numbers are quoted, including scientific 'e to the power' notation
276select CEIL('5.5'), FLOOR('5.5'), ROUND('5.5'),ROUND('5.49'), ROUND('5.49',1),ROUND('5.49',3), ROUND('5.49e2',2) from Dual;
277-- Auto type conversion cannot work with nin number strings ?
278select CEIL('21-NOV-18'),CEIL('A') from Dual;
279
280
281
282-- TRUNC—Number
283-- Syntax: TRUNC(n, i)
284-- Parameters: n is required, is any number, and can include decimal points. i is an integer and is optional—if omitted, it will default to 0.
285-- Process: TRUNC "rounds" toward zero; in other words, it truncates the numbers.
286-- Output: If i is omitted, TRUNC returns a value in the same numeric data type as n. If i is specified, TRUNC returns a data type of NUMBER.
287-- One way to think about this is that its like a FLOOR with a way to spceficy how many decimals to keep.
288
289-- without the decimal, it pretty much works like a FLOOR, with the decimal, ir takes away the number of significant digits, and rounds then to 0.
290select TRUNC(5.5),TRUNC(5.49), TRUNC(5.49,1),TRUNC(5.49,3), TRUNC(5.49e3,3),TRUNC(555.49,-1), TRUNC(555.49,-2)from Dual;
291
292-- !!! NEGATIVE NUMBERS !!!
293-- TRUNC, FLOOR AND ROUND work diffrently - this is where they are different.
294-- TRUNC does not care for the sign. to do this imagine that the sign is removed. TRUNC(-5.5) = -5 ; TRUNC(-x) = -TRUNC(x)
295-- FLOOR/CEIL goes to the smaller/higher number, and for negative numbers too. the sign is important. FLOOR (-5.5) = -6, and CEIL(-5.5) = -5
296-- ROUND on the other hand behaves like TRUNC. ROUND(-5.5) = -6 (although -6 is rounding down), and ROUND(5.5) = 6 (rounding up). ROUND(-x) = -ROUND(x)
297
298-- ROUND—Date
299-- Syntax: ROUND(d, i)
300-- Parameters: d is a date (required); i is a format model (optional).
301-- Process: d is rounded off to the nearest date value, at a level of detail specified by i.
302-- d is required and specifies a value of the DATE data type.
303-- i is a format model and specifies the level of detail to which the DATE value will be rounded,
304-- in other words, to the nearest day, nearest hour, nearest year, and so on. i is optional;
305-- if i is omitted, ROUND will default to a format model that returns a value of d rounded to the nearest whole day.
306-- Values are biased toward rounding up. For example, when rounding off time, 12 noon rounds up to the next day.
307SELECT SYSDATE TODAY, ROUND(SYSDATE,'MM') ROUNDED_MONTH, ROUND(SYSDATE,'RR') ROUNDED_YEAR FROM DUAL;
308
309-- rounding without a format, rounds to the nearest day, 12:00 noon rounds to the next day and 11:59:59 rounds to the 00:00 of the current day
310select to_char(round(to_date('16-DEC-2018 11:30:00','DD-MON-YYYY HH24:MI:SS')), 'DD-MON-YYYY HH24:MI:SS') "11:30:00",
311 to_char(round(to_date('16-DEC-2018 12:00:00','DD-MON-YYYY HH24:MI:SS')), 'DD-MON-YYYY HH24:MI:SS') "12:00:00" from Dual ;
312-- HH, rounds to the nearest hour, 30th minute rounds up to the next hour
313select to_char(round(to_date('16-DEC-2018 11:30:00','DD-MON-YYYY HH24:MI:SS'),'HH'), 'DD-MON-YYYY HH24:MI:SS') "11:30:00",
314 to_char(round(to_date('16-DEC-2018 11:29:59','DD-MON-YYYY HH24:MI:SS'),'HH'), 'DD-MON-YYYY HH24:MI:SS') " 11:29:59" from Dual ;
315-- DD rounds a date to the nearest Day. 12 noon rounds up to the next day.
316select to_char(round(to_date('16-DEC-2018 11:59:59','DD-MON-YYYY HH24:MI:SS'),'DD'), 'DD-MON-YYYY HH24:MI:SS') "11:59:59",
317 to_char(round(to_date('16-DEC-2018 12:00:00','DD-MON-YYYY HH24:MI:SS'),'DD'), 'DD-MON-YYYY HH24:MI:SS') "12:00:00" from Dual ;
318-- MM Round a date to the 'nearest' month. 15th is rounded down, and 16th is rounded up
319select to_char(round(to_date('16-DEC-2018 11:29:00','DD-MON-YYYY HH24:MI:SS'),'MM'), 'DD-MON-YYYY HH24:MI:SS') "16th",
320 to_char(round(to_date('15-DEC-2018 11:29:00','DD-MON-YYYY HH24:MI:SS'),'MM'), 'DD-MON-YYYY HH24:MI:SS') "15th" from Dual ;
321
322
323-- TRUNC—Date
324-- Syntax: TRUNC(d, i)
325-- Parameters: d is a date (required); i is a format model (optional).
326-- Process: Performs the same task as ROUND for dates, except TRUNC always rounds down.
327
328-- trunc without a format, rounds to the 00:00 of the current day
329select to_char(trunc(to_date('16-DEC-2018 11:30:00','DD-MON-YYYY HH24:MI:SS')), 'DD-MON-YYYY HH24:MI:SS') "11:30:00",
330 to_char(trunc(to_date('16-DEC-2018 12:00:00','DD-MON-YYYY HH24:MI:SS')), 'DD-MON-YYYY HH24:MI:SS') " 12:00:00" from Dual ;
331-- HH, truncates to the start of the current hour
332select to_char(trunc(to_date('16-DEC-2018 11:30:00','DD-MON-YYYY HH24:MI:SS'),'HH'), 'DD-MON-YYYY HH24:MI:SS') "11:30:00",
333 to_char(trunc(to_date('16-DEC-2018 11:29:59','DD-MON-YYYY HH24:MI:SS'),'HH'), 'DD-MON-YYYY HH24:MI:SS') " 11:29:59" from Dual ;
334-- DD truncates a date to the nearest Day. 12 noon rounds up to the next day.
335select to_char(trunc(to_date('16-DEC-2018 11:59:59','DD-MON-YYYY HH24:MI:SS'),'DD'), 'DD-MON-YYYY HH24:MI:SS') "11:59:59",
336 to_char(trunc(to_date('16-DEC-2018 12:00:00','DD-MON-YYYY HH24:MI:SS'),'DD'), 'DD-MON-YYYY HH24:MI:SS') "12:00:00" from Dual ;
337-- MM Round a date to the 'nearest' month. 15th is rounded down, and 16th is rounded up
338select to_char(trunc(to_date('16-DEC-2018 11:29:00','DD-MON-YYYY HH24:MI:SS'),'MM'), 'DD-MON-YYYY HH24:MI:SS') "16th",
339 to_char(trunc(to_date('15-DEC-2018 11:29:00','DD-MON-YYYY HH24:MI:SS'),'MM'), 'DD-MON-YYYY HH24:MI:SS') "15th" from Dual ;
340
341-- LAST_DAY
342-- Syntax: LAST_DAY(d)
343-- Parameters: d is a date, required.
344-- Process: Returns the last day of the month in which d falls.
345-- Output: Date.
346-- Example: Show the last days of February in 2020 and 2021.
347-- auto conversion works
348SELECT LAST_DAY(to_date('14-FEB-20','DD-MON-YY')), LAST_DAY ('20-FEB-21') FROM DUAL;
349
350-- ADD_MONTHS
351-- Syntax: ADD_MONTHS(d, n)
352-- Parameters: d is a date, required; n is a whole number, required.
353-- Process: Adds n months to d and returns a valid date value for the result.
354-- Output: Date.
355
356-- ading month to the last dat in a month, gives the last day of the next month. leap years included.
357select ADD_MONTHS('27-Feb-2018',1), ADD_MONTHS('28-Feb-2018',1),ADD_MONTHS('28-Feb-2020',1),ADD_MONTHS('29-Feb-2020',1) from dual ;
358-- subtracting months works the same way
359select ADD_MONTHS('27-Feb-2018',-1), ADD_MONTHS('28-Feb-2018',-1),ADD_MONTHS('28-Feb-2020',-1),ADD_MONTHS('29-Feb-2020',-1) from dual ;
360
361-- MONTHS BETWEEN
362-- returns the months between the two input dates .
363-- if the first month is smller than the second, a negative number is retrned.
364
365-- TO_NUMBER
366-- Syntax: TO_NUMBER(e1, format_model, nls_parms)
367 -- NLS_NUMERIC_CHARACTERS = 'dg' (always in that order - first char is decimal, second is group)
368 -- d = decimal character , g = group separator
369 -- NLS_CURRENCY = 'text' text = local currency symbol (see L in Table 6-1)
370 -- NLS_ISO_CURRENCY = 'currency' currency = international currency symbol
371-- G and D used in the format model. the nls chars param set the "redefined" vaues for G and D.
372SELECT TO_NUMBER('17.000,23', '999G999D99', 'nls_numeric_characters='',.'' ') REFORMATTED_NUMBER FROM DUAL;
373
374
375-- Date types
376-- DATE - Stores the century, year, month, date, hour, minute, and second.
377-- When a string is coerced, RR or YY Format model interpret the century. Once stored,
378-- the output format does not affect the stored century info.
379-- When a date is queried, it inlcudes the time stored. To compare dates without times, TRUNC can be used.
380-- TIMESTAMP - tracks franstional seconds in addition to date. default is to use 6 digits (percision) for storing the franctional seconds.
381-- TIMESTAMP WITH TIME ZONE - includes the TZ offset or th TZ region name.
382-- - Two TZ with timezones are equal if they represent the same instant in UTC .
383select 'they are the same', TO_TIMESTAMP_TZ ('1999-01-15 8:00:00 -8:00','YYYY-MM-DD HH24:MI:SS TZH:TZM'),
384TO_TIMESTAMP_TZ ('1999-01-15 11:00:00 -5:00','YYYY-MM-DD HH24:MI:SS TZH:TZM')
385from dual
386where
387 TO_TIMESTAMP_TZ ('1999-01-15 8:00:00 -8:00','YYYY-MM-DD HH24:MI:SS TZH:TZM') =
388 TO_TIMESTAMP_TZ ('1999-01-15 11:00:00 -5:00','YYYY-MM-DD HH24:MI:SS TZH:TZM') ;
389
390-- TIMESTAMP WITH LOCAL TIMEZONE - variarion on TS with TZ, but the date stored is in the DB tz, and the TZ offset is NOT stored.
391-- - when queried, the DB converts the date in to the user's local TZ as setup in the user session
392
393-- *** Date formatting ***
394-- FM clears up the extra spaces introduced by the formatting
395-- Day prints the day of the week in mixed case. DAY/day would have printed in upper/lower case. dAY = day, DaY=Day (only the first&second char matters).
396-- if the first char is lower, print all lower
397-- else // first char is upper
398-- if (second char is lower) => mixed case
399-- else => all upper case // second char is also upper.
400-- quoted string "the" passed through as is
401-- Ddth => DD TH. DD is day of month. TH adds the 1'ST', 'nd'. 'rd', 'th' appropriately. DD will cause TH, or RD, but Dd/dd will cause th,rd, etc.
402-- Month prints the complete month name in mixed case. MONTH = all upper case, month= all lower case. only the first two chars matter.
403-- RRRR - prints Year
404-- YYYY -> print current Year
405-- RRRR -> if the last two digits are 00-49, assume current century. 50-99 assumes previous century.
406-- The 24-hour format (HH24) shows midnight as 00, and the 12-hour format(HH12/HH) shows midnight as 12.
407select TO_CHAR(SYSDATE,'FMDaY, "the" Ddth "of" Month, RRRR') from dual;
408-- Shows how RR interprests date. once the DFATE object is created, its always put put the same way.
409-- RR and YY differ in interpretting a string represantatin as a DATE type.
410select TO_CHAR(TO_DATE('99','YY'), 'YYYY'), TO_CHAR(TO_DATE('99','RR'), 'YYYY'),TO_CHAR(TO_DATE('99','YY'), 'RRRR') from dual;
411-- Sample converting a number to a date.
412SELECT TO_DATE(11221999,'MMDDYYYY') "Time" FROM DUAL;
413
414
415-- CASE
416-- Kind of like an if then else.
417-- There are two kinds - simple and searched. when no options match , a null is returned.
418-- The evaluation stop at the first match.
419-- There can be a total of 255 exprs, inlcuding the expr for the CASE, the optional ELSE, and each WHEN and TEN exprs.
420-- Simple - CASE <EXPR> WHEN <CONDITION> THEN <VALUE> ELSE <DEFAULT> END
421-- EXPR is sommething like a name or a condition like AGE>10.
422-- CONDITIONS check the result of the expression.
423-- Select name, age, case name when 'angelina' then 'maleficent' from Table
424-- searched - CASE WHEN <EXPR> = <CONDITION> THEN <RESULT>
425-- Here we can have multiple expressions which is the big differnce.
426-- SELECT name, age , CASE WHEN age >= 18 THEN 'adult' WHEN age < 18 THEN 'child' from table.
427-- HANDLING NULLS
428-- Simple CASE, the NULL is compared to the WHEN clause, and results in NULL!=NULL as expected.
429Select CASE NULL when NULL THEN 'CASE : NULL == NULL' ELSE 'CASE : NULL !=NULL' END from Dual;
430
431-- Searched CASE, where a proper IS NULL check is employed, this returs NULL IS NULL as expected
432Select CASE WHEN NULL IS NULL THEN 'CASE : NULL IS NULL' ELSE 'CASE : NULL IS NOT NULL' END from Dual;
433
434-- the expressions in case can reference only existing columns or data.
435-- for example, to list the employees with thier salaries and compare thier salaries with the avg for the job,
436-- you could do this: (does not work)
437
438select first_name, last_name, job_id, salary, avg(salary) over (partition by job_id) avg_for_job,
439case
440 when salary < avg_for_job then 'underpaid'
441 when salary > avg_for_job then 'overpaid'
442 else 'on average'
443end pay_rate
444from employees;
445
446-- this does not work since the avg_for_job alias cannot be referenced from the case.
447-- This works :
448select first_name, last_name, job_id, salary, avg(salary) over (partition by job_id) avg_for_job,
449case
450 when salary < avg(salary) over (partition by job_id) then 'underpaid'
451 when salary > avg(salary) over (partition by job_id) then 'overpaid'
452 else 'on average'
453end pay_rate
454from employees;
455
456
457
458-- DECODE, where NULL values are compared -- IS NULL not used -- return true. This is out of the ordinary.
459Select DECODE(NULL,NULL,'DECODE: NULL==NULL','DECODE: NULL!=NULL') from Dual;
460
461-- Samples. Takes the EMP table, look at the job_id and deduce the job based on the first two letters of the code.
462SELECT EMP.*,
463CASE SUBSTR(EMP.JOB_ID,1,2)
464WHEN 'AD' THEN 'Admin'
465WHEN 'IT' THEN 'Tech'
466WHEN 'FI' THEN 'Finance'
467ELSE 'Other'
468END JOB
469from HR.EMPLOYEES EMP;
470-- Same thing in DECODE form.
471-- Both CASE and DECODE does not need to have a final "default". if there is no default and none of the exprs match, then a NULL is returned.
472
473SELECT EMP.*,
474DECODE( SUBSTR(EMP.JOB_ID,1,2),
475 'AD', 'Admin',
476 'IT', 'Tech',
477 'FI', 'Finance',
478 'Other') JOB
479from HR.EMPLOYEES EMP;
480
481
482-- COUNT
483
484-- Syntax: COUNT(e1)
485-- Parameters: e1 is an expression. e1 can be any data type.
486
487-- The aggregate function COUNT determines the number of occurrences of non-NULL values.
488-- It considers the value of an expression and determines whether that value is NOT NULL for each row it encounters.
489-- COUNT will never return NULL. If it encounters no values at all, it will at least return a value of 0 (zero).
490-- COUNT counts occurrences of data, ignoring NULL values. But when combined with the asterisk, as in SELECT COUNT(*) FROM VENDORS,
491-- it counts occurrences of rows—and will include rows with all NULL values in the results.
492--
493-- The DISTINCT and ALL operators can be used with aggregate functions. DISTINCT returns only unique values.
494-- ALL is the opposite of DISTINCT and is the default value. If you omit DISTINCT, the ALL is assumed.
495-- DISTINCT and ALL cannot be used with the asterisk
496--
497select COUNT(*), COUNT(COMMISSION_PCT), COUNT(DISTINCT COMMISSION_PCT) from HR.EMPLOYEES;
498
499
500-- SUM
501
502-- Syntax: SUM(e1)
503-- Parameters: e1 is an expression whose data type is numeric.
504
505-- The SUM function adds numeric values in a given column. It takes only numeric data as input.
506-- SUM adds all the values in all the rows and returns a single answer.
507Select to_char(SUM(salary),'L999,999,999.99') from hr.employees;
508
509
510-- MIN, MAX
511
512-- Syntax: MIN(e1); MAX(e1)
513-- Parameters: e1 is an expression with a data type of character, date, or number.
514-- they use the same basic logic that ORDER BY uses for the different data types, specifically:
515
516-- Numeric: Low numbers are MIN; high numbers are MAX.
517-- Date: Earlier dates are MIN; later dates are MAX. Earlier dates are less than later dates.
518-- Character: A is less than Z; Z is less than a. The string value 2 is greater than the string value 100.
519-- The character 1 is less than the characters 10.
520
521-- NULL values are ignored, unless all values are NULL, in which case MIN or MAX will return NULL.
522
523Select MIN(salary), MIN(email), MIN(hire_date), MAX(salary), MAX(email), MAX(hire_date) from hr.employees;
524
525-- AVG
526
527-- Syntax: AVG(e1)
528-- Parameters: e1 is an expression with a numeric data type.
529
530-- The AVG function computes the average value for a set of rows. AVG works only with numeric data. It ignores NULL values.
531-- a single aggregate function can be used within as many nested scalar functions as you want.
532-- The aggregate function need not be the innermost function
533
534-- MEDIAN
535
536-- Syntax: MEDIAN(e1)
537-- Parameters: e1 is an expression with a numeric or date data type.
538
539-- MEDIAN can operate on numeric or date data types. It ignores NULL values.
540Select median(hire_date), to_char(AVG(ROUND(nvl(salary,0),2)),'L999,999.99'), MAX(email), MAX(hire_date) from hr.employees;
541
542
543-- RANK: Analytic
544-- Syntax: RANK() OVER (PARTITION BY p1 ORDER BY ob1)
545-- Parameters: p1 is a partition. ob1 is an expression.
546
547-- The use of PARTITION BY is optional. All other elements are required.
548
549-- The RANK function calculates the rank of a value within a group of values.
550-- Ranks may not be consecutive numbers since SQL counts tied rows individually,
551-- so if three rows are tied for first, they will each be ranked 1, 1, and 1, and the next row will be ranked 4.
552
553
554-- SELECT SHIP_CABIN_ID, ROOM_STYLE, SQ_FT
555-- , RANK() OVER (PARTITION BY ROOM_STYLE ORDER BY SQ_FT) SQ_FT_RK
556-- FROM SHIP_CABINS
557-- WHERE SHIP_CABIN_ID <= 7
558-- ORDER BY SQ_FT;
559
560-- SHIP_
561-- CABIN_
562-- ID ROOM_STYLE SQ_FT SQ_FT_RK
563-- ------ ---------- ------ ----------
564-- 2 Stateroom 160 1
565-- 4 Stateroom 205 2
566-- 7 Stateroom 211 3
567-- 3 Suite 533 1
568-- 1 Suite 533 1
569-- 5 Suite 586 3
570-- 6 Suite 1524 4
571
572
573Select first_name, last_name, hire_date, salary,job_id,
574 RANK() over (partition by job_id order by salary,hire_date ) rnk
575from hr.employees
576order by job_id, rnk ;
577
578-- WHERE Clause
579-- not mandatory, but if present should follow the FROM clause.
580-- !! Aggregate functions are not permitted in a where clause.
581-- LIKE can only work with scalar functions - single valued things.
582-- Operator precedence - NOT > AND > OR
583
584-- Here all NOT is evaluated first , so all records where manager!= 100 is selected.
585-- Now AND is evaluated - from the selected manager, the dept_id should be 90
586-- Now OR is evaluated. The existing set, or the JOB_IDs that start with AD.
587select * from hr.employees
588where JOB_ID LIKE ('AD%') OR DEPARTMENT_ID=90 AND NOT MANAGER_ID=100
589
590-- BETWEEN
591-- The ranges are inclusive on both sides.
592-- !! Should be lower to higher. BETWEEN 5 AND 1 returns 0 rows. while BETWEEN 1 and 5 is valid.
593
594-- IS NULL
595-- only valid check to use to check if a value is null. the equality operator(=) does not work with NULL.
596-- value = NULL check always returns false.
597
598-- EXISTS, NOT EXISTS, IN and NOT IN
599-- You cannot chek a column with exists, you say select ... from ... where exists (query). Not where col_name exists (query)
600-- You are not looking for values retrned from the subquery. Just the fact if any rows are reurned.
601-- So EXISTS will evaluate to true when if select count(*) from (subquery) would be > 0
602-- For example :
603Select * from dept where exists (select null from dual);
604-- will return all rows in dept because ?:
605 -- select null from dual returns 1 row. the value in that one rw is null, but the value is immaterial.
606 -- select count(*) from (select null from dual) = 1, so a row is returned, and so exists evaluates to true
607 -- there are no other conditions, so the query is effectively select * from dept where true => select * from
608 -- Any subquery that is guaranteed to return a row - like a select count(), or select literal from dual will make exists evaluate to true.
609-- NOT EXISTS negates this.
610 -- it will evaluate to true only if the subquery returns no rows.
611 -- No rows DOES NOT MEAN the value 0 or null.
612 -- for example
613select * from hr.employees e where not exists (select null from dual where 1=2)
614 -- will return all rows, because the subquery returns no rows (due to the where clause of 1=2)
615 -- this makes the NOT EXISTS evaluate to true.
616 -- However the folowing query returns nothing
617select * from hr.employees e where not exists (select count(null) from dual where 1=2)
618 -- This is because even though we have an impossible where clause in the subquery (1=2), the subquery is doing a count
619 -- The count returns 0 - which is A ROW !!! - again the value is immaterial. the fact is that a row was returned.
620 -- this means EXISTS evaluates to true, and NOT exists evaluates to FALSE.
621 -- query reduced to : select * from hr.employees e where FALSE => no rows.
622 -- because selecting anything with a condition of 'where false' gives no rows.
623-- IN and NOT IN
624-- These are simialr to EXISTS/NOT exists, but not the same, especially when it comes to NULLS
625-- The first thing is that IN checks the value of a column with a set of values from the subquery. VALUES. not the presence or abcense of rows
626-- select .. from .. where col_name IN (subquery)
627-- IN also evaluates equality using the = operator. so col IN null => false.
628-- an IN query cn be reuced to a sequence of OR operations .
629-- for example :
630select * from hr.departments where department_id IN (10,20,NULL)
631 -- Here each candidate row from departments is evaluated aginst the list (could be a select query as well )
632 -- the IN clause expands to : where department_id = 10 OR department_id = 20 OR department_id = NULL
633 -- For candidate rows wwith dept id 10 , and 20 , the where caluse evaluates to TRUE. these are included.
634 -- The department_id = NULL will evaluate to false every single time.
635 -- but this is okay because we are doing an OR, and we just need any of the conditions to be true to pick the row.
636-- NOT IN works the same, except it flips the ORs to ANDs.
637select * from hr.departments where department_id NOT IN (10,20,NULL)
638 -- will return nothing. NOT IN (...,...,NULL,...) => if there is a null value in the IN list, NOT IN will never return anything.
639 -- THats because the query expands to :
640 -- department_id != 10 AND department_id!=20 AND department_id!=NULL
641 -- the same == or != is used for equality checks, but this time we have the AND operator.
642 -- Nothing is = NULL and nothing is != null (not even null ! thats why the IS NULL thig exists, but its not used here )
643 -- The expression will alwasy boil down to (true/false) AND (true/false) AND (false) -- tha last one is false for all rows because it does != NULL
644 -- Since the operator is AND, a single false makes the whole expression false.
645 -- query boils down to => where department_id != 10 AND department_id!=20 AND department_id!=NULL => where false => no rows returned.
646
647
648
649-- VARIABLE SUBSTITUTIONS
650-- & will prompt for a variable thats used in a query (value or column definition)
651-- Its like text substitution, a y part can be replaced with a variable substitution, and then evaluated.
652-- !! watch for quoting - the var can be quoted, or the user response to the prompt can be quoted.
653
654-- DEFINE - defines a value for a variable, if the value exists, the user is not prompted. lifetime : session
655-- DEFINE without prams to list currently defined vars
656-- UNDEFINE removes the defintion of a variable.
657-- SET DEFINE ON wil enable substitution,
658-- SET DEFINE <char> will change the var substitution char.
659-- SHOW DEFINE will print the char currently used for substitution.
660
661-- FETCH ... WITH TIES
662-- Syntax : FETCH [FIRST/NEXT] [num/default=1] [PERCENT/-] [ROW/ROWS] [ONLY/WITH TIES]
663-- the number is optional, defaults to 1. If percent is used, a number is required.
664-- ROW/ROWS are same and required.
665-- ONLY/ WITH TIES either is required.
666-- WITH TIES will bring logical groups even if the number pr percentage specified is exceeded
667
668-- !! ANALYTIC FUNCTIONS
669-- The analytic functions differ from AGG func becasue they return multiple rows per group, where agg funcs return one row per group
670-- They are evaluated just before the final ORDER BY, and after GROUP BY, WHERE, HAVING etc. so they can appear only in select or ORDER BY
671-- ORDER BY for the analytic functions's window is unrelated to the ORDER BY for the query
672-- ROW_NUMBER() is an analytic function and is sperate from ROWNUM, which is an internal oracle data type.
673-- PS : Top -n queries that use select * order by something where ROWNUM < 5 is trouble, becasue ROWRUM is assigned before orderby.
674-- Doing the same thing over a view is okay if the innner query does the order by nad outter does the ROWnum.
675
676-- In selet statements, you can run an analytic function without group by using the OVER clause.
677-- Syntax : <SUM/AVG/COUNT/other agg func> OVER ( PARTITION BY COL_NAME / ORDER BY COL NAME ROWS BETWEEN n PRECEEDING AND m FOLLOWING)
678-- The PARTITION BY or ORDER BY is required, both can be used (actully makes sense to always have an order by here to get predictable results)
679-- The default windowing clause is ROWS UNBOUNDED PRECEDING. => all preceding rows.
680-- This is an anchored window - anchored to the start of the rows.
681-- You can do a sliding window using the ROWS/RANGE BETWEEN ... AND ...
682-- Windows can be done using RANGE
683select First_name, last_name, Salary, JOB_ID, avg(salary) OVER ( PARTITION BY JOB_ID ORDER BY HIRE_DATE RANGE 90 PRECEDING) from hr.employees
684
685 -- The query above wil provide the name, and salary of the current row together with
686 --the avg salary of all previous rows in this group whose HIRE_DATE value falls within 90 days preceding the HIRE_DATE value of the current row.
687-- LAG and LEAD
688-- works with windows only
689
690
691-- GROUP BY identifies subsets of rows within the larger set of rows being considered by the SELECT statement.
692-- The GROUP BY can specify any number of valid expressions, including columns of the table.
693-- Generally the GROUP BY is used to specify columns in the table that will contain common data
694-- in order to group rows together for performing some sort of aggregate function on the set of rows.
695-- The following are the only items allowed in the select list of a SELECT that includes a GROUP BY clause:
696-- Expressions that are specified in the GROUP BY.
697-- Aggregate functions applied to those expressions.
698-- Expressions that are specified in the GROUP BY do not have to be included in the SELECT statement’s select list.
699
700-- The HAVING clause can exclude specific groups of rows defined in the GROUP BY clause.
701-- You could think of it as the WHERE clause for groups of rows specified by the GROUP BY clause.
702-- The HAVING clause is unique to the SELECT statement; it does not exist in other SQL statements.
703-- The HAVING clause does not define the groups of rows themselves; those groups must already be defined by the GROUP BY clause.
704-- HAVING defines the criteria upon which each of the GROUP BY groups will be either included or excluded.
705-- The HAVING clause can be invoked only in a SELECT statement where the GROUP BY clause is present.
706-- GROUP BY and HAVING may occur in any order.
707-- It can only compare expressions that reference groups as defined in the GROUP BY clause and aggregate functions.
708
709
710
711-- this query gets employees, their department names and manager names
712-- it ranks the employees by salary within thier department.
713-- displays the result in the order or salary earned.
714-- the left outer join for getting manager names is needed to capture the CEO(no manager)
715
716select emp.first_name||' '||emp.last_name as Employee,
717 emp.salary,
718 dept.department_name,
719 mgr.first_name||' '||mgr.last_name as Manager,
720 rank() over (partition by emp.department_id order by emp.salary desc)
721 from hr.employees emp
722 join hr.departments dept
723 on emp.department_id = dept.department_id
724 left outer join hr.employees mgr
725 on emp.manager_id = mgr.employee_id
726 order by emp.salary desc
727
728-- this query finds the total salary budget for each department with their managers.
729-- to get the salary budget, we need the employee table grouped at the department_id level
730-- emp.dept_id:dept.dept_name is 1:1, so group by department name to select that in the query
731-- its joined again with the emp table to find managers for each dept. this is also a 1:1
732-- since each dept has only one manager and that manager has only one name, we can group by the name.
733select dept.department_name, sum(emp.salary) total, dept.manager_id ,mgr.first_name
734from hr.employees emp
735 join hr.departments dept
736 on dept.department_id = emp.department_id
737 join hr.employees mgr
738 on mgr.employee_id = dept.manager_id
739-- this where clause would consider only employees with less than 10k in salary.
740-- all departments are considered, and the results with the filtered data is found
741--where emp.salary < 10000
742group by dept.department_name, dept.manager_id, mgr.first_name
743-- the having clause filters the groups that are already identified.
744-- it does not work at the individual row level but rather at the group level
745-- only aggregates or columns inthe group by can be used here.
746--having sum(emp.salary)>10000
747
748--when both the where clause and the having is used, the where caulse filters the
749-- rows and the group by processes the filtered rows. The having clause sees the
750-- filtered data from the where clause/
751order by total desc
752
753
754-- FLASHBACK
755-- Can restore a dropped table or an existing table.
756-- When restored, all indexes other than bitmap join indexes and all constriants other than FK constrinats are recovered.
757 -- other db objects like views or synomymns are not dropped or restored. obviously FLASHBACK TABLE, does not flashback a view/synonym
758-- privileges are restored when using FLASHBACK.
759-- If the user purges the recyclebin, then nothing can be restored.
760-- Objects can be restored to a timestamp, a SCN, a RESTORE POINT, or "Before Drop"
761 -- If the original name of teh object is in used at the time of restore, then rename to needs to be used, else error.
762 -- on successful restore, its removed from recylce bin
763
764-- SET OPERATORS
765-- There is no operator precendence. LHS op RHS in the order of occurence.
766-- A isect B union C minus D union all E
767-- Set A and B computed, isect'd -> result union'd with set C -> result disjointed with set D -> result union all'd with set E
768-- All set operators eliminate dupes, except UNION ALL
769-- (1,1,2,3) isect (1,2) => (1,2)
770-- (1,1,2,2) minus (1,2) => ()
771-- (1,1,3,3) union (1,1,3,3) => (1,3)
772-- !! (1,1,3,3) union ALL (1,1,3,3) => (1,1,1,1,3,3,3,3)
773
774
775
776-- Examples for testing queries as well as data dictionary.
777
778
779-- drop everything to make this idempotent
780drop table emp cascade constraints;
781drop table dept cascade constraints;
782drop table emp_managers ;
783drop table emp_department;
784drop table emp_department_mgr;
785drop sequence emp_seq;
786drop sequence dept_seq;
787drop view emp_details;
788drop view emp_detail2;
789
790-- table creation.
791-- named primary key using the constraint keyword.
792-- row movement enabled, so that this table can be used in a flashback table
793create table emp(id number(10),
794 name varchar2(30),
795 age number(3),
796 dept_id number(10),
797 CONSTRAINT emp_pk primary key (id)
798 ) enable row movement;
799
800-- un-named primary key, using inline syntax.
801-- named foreign key that references the manager_id column of the employee table.
802create table dept (id number(10) primary key,
803 name varchar2(30),
804 manager_id number(10),
805 constraint dept_mgr_fk foreign key(manager_id) references emp(id)
806 );
807
808-- adding an FK constraint on the emp table to reference the department id.
809-- this could not be done before becuase the dept table did not exist at that time
810alter table emp add constraint dept_fk foreign key (dept_id) references dept(id);
811
812-- sequence creations
813create sequence emp_seq start with 1000 increment by 5;
814create sequence dept_seq start with 10 increment by 10;
815
816
817-- some data being inserted, the sequence is used for key generation
818-- The FK cannot be populated initally since the dept row does not yet exist.
819insert into emp(id, name, age) values (emp_seq.nextval, 'Joe', 30);
820insert into dept values (dept_seq.nextval, 'keyboards', 1000);
821insert into dept values (dept_seq.nextval, 'mice', 1000);
822
823-- adding the reationship
824update emp set dept_id = (select max(id) from dept) where id = (select max(id) from emp);
825
826-- simple join
827select * from emp join dept on emp.dept_id = dept.id;
828
829-- Simple view - Insertable
830create view leadership_team as (select * from emp where dept_id = 10);
831select * from leadership_team;
832-- inserts a row into the underlying table, but the view sql will prevent this
833-- row from being selected in the view.
834-- the where condition on the view sql does not affect the insertability.
835insert into leadership_team values (emp_seq.nextval, 'alex', 25, 20);
836
837
838-- create a view . uses a subquery to get the department manager.
839-- can be done with a 3 way join as well
840create view emp_details as (
841 select emp.id, emp.name, age, dept_id, dept.name as dept_name,
842 (select emp.name from emp join dept on dept.manager_id = emp.id ) department_manager
843 from emp
844 join dept
845 on emp.dept_id = dept.id);
846
847-- using a 3 way join to get the employee details as well as the departmental manager.
848create view emp_detail2 as (select emp.id, emp.name, emp.age, emp.dept_id, dept.name as dept_name, mgr.name as department_manager
849from emp
850 join dept
851 on emp.dept_id = dept.id
852 join emp mgr on dept.manager_id = mgr.id);
853
854
855select * from emp_details;
856
857
858
859
860-- METADATA Tables and Views.
861
862-- all DDL (not DML) statements affect the data dictionary
863-- USER_ tables contain info on the objects that are owned by the current user.
864-- ALL_ tables conatain info on everything that the current user has access to (but not necesarily own)
865-- DBA_ tables have info on all objects in the DB, but requires the user to have permisssions to query the tables.
866
867-- query the data dictionary
868select * from dictionary where table_name like '%EMP%';
869select * from user_catalog;
870select * from user_objects;
871select * from user_tables;
872
873-- checking status of views and recompiling them.
874-- You cannot select all invalid views and recompile them in one command as
875-- the alter is a DDL and the select is a DML. PL/SQL can do this
876select * from user_objects where object_type='VIEW'
877alter view EMP_DETAILS compile
878
879-- check privileges
880select * from user_sys_privs;
881select * from DBA_SYS_PRIVS;
882
883-- See constraint details, filtered by the table name.
884-- note that only tables can have constraints.
885SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, R_CONSTRAINT_NAME, STATUS
886FROM USER_CONSTRAINTS
887WHERE TABLE_NAME = 'EMP'
888
889
890-- see table comments.
891-- users can add comments although the dictionary tables are owned by the SYS user
892select * from user_tab_comments;
893comment on table emp is 'simple table for testing';
894select * from user_tab_comments;
895
896-- Note that the dictonary stores everything in upper case. except for quoted table names.
897-- note that create table uses double qoutes to specify the table name, but the
898-- select from the user_tab_comments uses as single quoted string.
899create table "Really Bad Name" (key number(5) primary key,value varchar2(2000));
900select * from user_tab_comments where table_name = 'Really Bad Name'
901
902
903select * from user_col_comments where table_name = 'EMP'
904comment on column emp.id is 'The Employee ID. the primary key for the emp table'
905select * from user_col_comments where table_name = 'EMP'
906
907-- Multi Table Inserts
908
909
910-- setting up some dummy tables
911create table emp_managers (emp_name varchar2(30), manager_name varchar2(30));
912create table emp_department (emp_name varchar2(30), department_name varchar2(30));
913create table emp_department_mgr (emp_name varchar2(30), department_name varchar2(30),manager varchar2(30));
914
915-- Note that using a sequence in the select statement for the multitable insert is an error
916
917-- the first table omits the column names, so all columns are assumed in the table order.
918-- will fail if the values clause does not have data for all columns
919-- the second insert specifies the column list as well as the values list
920-- the final isert omits both the column list as well as the values list,
921-- but the select statement result can directly be inserted in to this table
922INSERT ALL
923 into emp_managers values (ename, manager)
924 into emp_department (emp_name, department_name) values (ename, dname)
925 into emp_department_mgr
926 Select emp.name ename, dept.name dname,
927 (select emp2.name from emp emp2 join dept dept2 on emp2.id= dept2.manager_id where dept.id = dept2.id) manager
928 from emp
929 join dept
930 on emp.dept_id = dept.id
931
932select * from emp_managers;
933select * from emp_department;
934select * from emp_department_mgr;
935
936
937
938-- SYSYEM PRIVILEGES
939-- are privileges on the DB as a whole, not a particular object.
940-- Creating a db object like a table is a system privilege.
941-- Creating an index is an object privilege since the index is created on the table's columns. Creating INDEXTYPE is a system privilege
942-- Syntax : GRANT [privilege, previlege / ALL PRIVILEGES] TO [user,user, role] (WITH ADMIN OPTION)
943-- ADMIN OPTION lets a user grant the same privilege to another user.
944-- REVOKE privilege works the same way, and uses the word FROM isntead of TO.
945-- Revocations do not cascade. Provileges granted with ADMIN OPTION has to be explicitly revoked.
946-- PUBLIC - is an special user, to whom when provileges are granted, all users get the privilege.
947-- To grant all provileges to some user - GRANT ALL PROVILEGES TO user (WITH ADMIN OPTION)
948-- To grant some privileges to all users - GRANT provilege to PUBLIC (WITH ADMIN OPTION)
949
950
951-- OBJECT PRivileges
952-- the owner of an object (TABLE, VIEW, SYNONYM) has all provileges to grant it to someone else
953-- users who have system provileges like SELECT_ANY_TABLE also has privileges.
954-- All DML - INSERT UPADTE DELETE SELECT has provileges. There is NO SPECIAL PROVILEGE FOR MERGE - Its covered by INSERT, UPDATE, DELETE
955-- Although a user owns a table, the user still needs the CREATE PUBLIC SYNONYM to create a public synonym
956-- even if a public synonym exists, a user needs provileges on the underlying object to use it .
957--
958-- Grant privilege on object to user with grant option.
959-- For object privileges, the keyword that gives the grantee the ability to propagate the privilege is WITH GRANT OPTION
960-- When a privilege granted with GRANT OPTION is revoked, then the revocation cascades. all further users that got the privilege will lose it.
961-- this is unlike system privileges, where once you got it, it has to be explicitly removed from the user.
962-- ALL PRIVILEGES
963 -- GRANT ALL PRIVILEGES ON object TO user
964 -- unlike the system privilege version, here the precense of 'ON object' tells us that this is all object provileges.
965 -- !! Note that for object provileges, the keyword provileges os not required. GRANT ALL ON obj to user will work
966 -- the PRIVILEGES keyword is mandatory in the system privilege case.
967-- if a table is dropped and recreated, its a new table, the privileges, indexes etc will need to be re-created.
968 -- however if the tabel is flashback'd then the privileges are restored.
969-- if a user has privilege on an object due to a direct privilege grant as well as a role, then
970-- the user still has access to the object if atleast one of the paths for access is still valid.