Different examples for training to exam Oracle Database SQL 1Z0-071. Part 1.
CHAPTER 1. "Relational Database concepts"
CHAPTER 2. "Restricting and Sorting Data"
CHAPTER 3. "Using Conversion Functions and Conditional Expressions"
CHAPTER 4. "Displaying Data from Multiple Tables"
CHAPTER 5. "Using SET Operators"
CHAPTER 6. "Managing Indexes Synonyms and Sequences"
CHAPTER 7. "Managing Views"
CHAPTER 8. "Managing Objects with Data Dictionary Views"
CHAPTER 9. "Retrieving Data using the SQL SELECT Statement"
CHAPTER 10. "Using Single-Row Functions to Customize Output"
CHAPTER 1. "Relational Database concepts":
1.1. Explaining the theoretical and physical aspects of a relational database
1.2. Relating clauses in SQL Select Statement to Components of an ERD
1.3. Explaining the relationship between a database and SQL
1.4. Sequence of query execution
Task 1.1.1. ERD
1. What mean ERD?
2. List relationships.
Solution:
--1
ERD - entity-relationship diagram is a logical representation of real business
system and displays the relationships of entities in a database.
--2
One to one. One to many. Many to one. Many to many.
Task 1.1.2. Normal forms.
Do the next operations.
DROP TABLE TAB1;
CREATE TABLE TAB1 (NAME VARCHAR2(100), PHONE NUMBER);
INSERT INTO TAB1 (NAME, PHONE) VALUES ('Adam', 111);
INSERT INTO TAB1 (NAME, PHONE) VALUES ('John', 222);
INSERT INTO TAB1 (NAME, PHONE) VALUES ('John', 333);
INSERT INTO TAB1 (NAME, PHONE) VALUES ('Mike', 333);
COMMIT;
Then do next:
1. List and explain first 3 normal forms.
2. For table TAB1 write type of relationship. Then normalize this table by
another relationships.
Solution:
--1
--First normal form
No repeating rows. All tables are two-dimensional. Tables are structured in a
one-to-many relationship.
Example:
Table with not 1NF:
John 1, 2 01.01.2000
John 1, 2 01.01.2000
Table with 1NF:
John 1 01.01.2000
John 2 01.01.2000
--Second normal form
Contain first normal form + second normal form exists when no nonkey attribute
(column) is dependent upon part of a composite key. It is mean create references.
Example:
Table with 1NF:
'Table_0'
John 1 01.01.2000
John 2 01.01.2000
Tables with 2NF:
'Table_1' 'Table_2'
0 1 0 John 01.01.2000
0 2
--Third normal form
Contain 1NF + 2NF and all tables have primary key and one value for this primary
key.
Tables with 2NF:
'Table_1' 'Table_2'
0 1 0 John 01.01.2000
0 2
Tables with 3NF:
'Table_1' 'Table_2' 'Table_3'
0 1 0 John 0 01.01.2000
0 2
--2
--Many to many.
--Need create 3 tables:
DROP TABLE NAMES;
DROP TABLE PHONES;
DROP TABLE CONNECTION_TABLE;
--table NAMES
CREATE TABLE NAMES(ID_NAME NUMBER PRIMARY KEY, NAME VARCHAR2(100));
INSERT INTO NAMES VALUES(1, 'Adam');
INSERT INTO NAMES VALUES(2, 'John');
INSERT INTO NAMES VALUES(3, 'Mike');
--table PHONES
CREATE TABLE PHONES(ID_PHONE NUMBER PRIMARY KEY, PHONE VARCHAR2(100));
INSERT INTO PHONES VALUES(10, 111);
INSERT INTO PHONES VALUES(20, 222);
INSERT INTO PHONES VALUES(30, 333);
--table CONNECTION_TABLE with primary key = all columns
CREATE TABLE CONNECTION_TABLE(ID_NAME NUMBER, ID_PHONE NUMBER);
ALTER TABLE CONNECTION_TABLE ADD PRIMARY KEY(ID_NAME, ID_PHONE);
INSERT INTO CONNECTION_TABLE VALUES (1, 10);
INSERT INTO CONNECTION_TABLE VALUES (2, 20);
INSERT INTO CONNECTION_TABLE VALUES (2, 30);
INSERT INTO CONNECTION_TABLE VALUES (3, 30);
COMMIT;
Task 1.3.1 Types of SQL statements.
1. Write types of SQL statements: DML, DDL, DCL, transaction control.
Solution:
--1
--DML
INSERT, UPDATE, DELETE, MERGE
--DDL
CREATE, ALTER, DROP, RENAME, TRUNCATE, FLASHBACK, PURGE, COMMENT
--DCL, as DDL operations produce COMMIT implicitly.
GRANT, REVOKE
--Transaction control
COMMIT, ROLLBACK, SAVEPOINT
Task 1.4.1 Sequence of query execution
1. Write sequence of query execution.
Solution:
--1
FROM
JOIN (+ JOINS FROM WHERE)
WHERE
GROUP BY --or HAVING
HAVING --or GROUP BY
SELECT
DISTINCT
ORDER BY
OFFSET
FETCH FIRST --FETCH NEXT
CHAPTER 2. "Restricting and Sorting Data"
2.1. Applying Rules of precedence for operators in an expression
2.2. Limiting Rows Returned in a SQL Statement
2.3. Using Substitution Variables
2.4. Using the DEFINE and VERIFY commands
2.5. Sorting Data
Task 2.1.1. Applying Rules of precedence for operators in an expression
1. List COMPARISON conditions;
2. List rules of precedence all operators (high to low).
Solution:
--1)
Equal to: =
Greater than: >
Greater than or equal to: >=
Less than: <
Less than or equal to: <=
Not equal to: <>
Between two values (inclusive): BEETWEN ... AND ...
Match any of a list values: [NOT] IN (set)
Match a character pattern: [NOT] LIKE
Is a null value: IS NULL
--2)
1 - Arithmetic operators
2 - Concatenation operators
3 - Comparison conditions
4 - IS [NOT] NULL, LIKE, [NOT] IN, EXISTS, IS OF type
5 - [NOT] BETWEEN
6 - Not equal to
7 - NOT logical operator
8 - AND logical operator
9 - OR logical operator
Task 2.1.2. Applying Rules of precedence for operators in an expression. NULL.
1. If X = NULL which result will be in expression: X > 1.
2. Display result for next comparisons:
TRUE AND TRUE -> ?
TRUE AND FALSE -> ?
FALSE AND FALSE -> ?
TRUE AND NULL -> ?
FALSE AND NULL -> ?
TRUE OR TRUE -> ?
TRUE OR FALSE -> ?
FALSE OR FALSE -> ?
TRUE OR NULL -> ?
FALSE OR NULL -> ?
Solution:
--1
NULL
--2
TRUE AND TRUE -> TRUE
TRUE AND FALSE -> FALSE
FALSE AND FALSE -> FALSE
TRUE AND NULL -> NULL
FALSE AND NULL -> FALSE
TRUE OR TRUE -> TRUE
TRUE OR FALSE -> TRUE
FALSE OR FALSE -> FALSE
TRUE OR NULL -> TRUE
FALSE OR NULL -> NULL
Task 2.2.1. Limiting Rows Returned in a SQL Statement. BETWEEN.
Do the next operations.
DROP TABLE TAB;
CREATE TABLE TAB (ID CHAR, COL1 NUMBER, COL2 VARCHAR2(100));
INSERT INTO TAB (ID, COL1, COL2) VALUES ('A', -1, 'aaa'); --A
INSERT INTO TAB (ID, COL1, COL2) VALUES ('B', NULL, 'bbb'); --B
INSERT INTO TAB (ID, COL1, COL2) VALUES ('C', -2, NULL); --C
INSERT INTO TAB (ID, COL1, COL2) VALUES ('D', -3, 'CCC'); --D
INSERT INTO TAB (ID, COL1, COL2) VALUES ('E', 1, 'fff'); --E
INSERT INTO TAB (ID, COL1, COL2) VALUES ('F', 2, 'eee'); --F
INSERT INTO TAB (ID, COL1, COL2) VALUES ('G', 3, 'ddd'); --G
COMMIT;
Then write queries using COL1 or COL2 in WHERE clause:
1. Show rows A, C, D.
2. Show rows A, B, D.
3. Show rows E, F, G. Two ways.
4. Show rows D, E, F, G.
Solution:
SELECT ID,
COL1,
COL2,
ASCII(SUBSTR(COL2,1,1))
FROM TAB
WHERE /* 1 */ COL1 BETWEEN -3 AND -1
/* 2 */ COL2 BETWEEN 'C' AND 'c'
/* 3.1. */ COL1 BETWEEN 1 AND 3
/* 3.2. */ COL2 BETWEEN 'd' AND 'g'
/* 4. */ COL1 NOT BETWEEN -2 AND -1
Task 2.2.2. Limiting Rows Returned in a SQL Statement. LIKE, NULL.
Do the next operations.
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 VARCHAR2(100));
INSERT INTO TAB1 (COL1) VALUES ('num_One');
INSERT INTO TAB1 (COL1) VALUES ('num%TwO');
INSERT INTO TAB1 (COL1) VALUES (NULL);
INSERT INTO TAB1 (COL1) VALUES ('_um__4');
COMMIT;
Then write queries where:
operator LIKE
1. COL1 include 4th symbol is '_';
2. COL1 include '__';
3. COL1 include symbols 'T', 'O' at the same time;
4. COL1 begin 'num';
5. COL1 not ended '4';
6. COL1 include 'um_';
7. COL1 include '%'.
operator NULL
8. COL1 with NULL values;
9. COL1 without NULL values.
Solution:
SELECT COL1
FROM TAB1
WHERE /* 1 */ COL1 LIKE '___/_%' ESCAPE '/'
/* 2 */ COL1 LIKE '%/_/_%' ESCAPE '/'
/* 3 */ COL1 LIKE '%T%O%'
/* 4 */ COL1 LIKE 'num%'
/* 5 */ COL1 NOT LIKE '%4'
/* 6 */ COL1 LIKE '%um/_%' ESCAPE '/'
/* 7 */ COL1 LIKE '%_%%' ESCAPE '_'
/* 8 */ COL1 IS NULL
/* 9 */ COL1 IS NOT NULL
Task 2.3.1. Using Substitution Variables. Using the DEFINE and VERIFY commands.
Answer on the questions:
1. Write the symbol of the SUBSTITUTION VARIABLE that will be discarded after use.
2. Write how to save string values of couple SUBSTITUTION VARIABLE for the
session. 2 ways. Write query that retrieve this values.
3. Write how to remove couple SUBSTITUTION VARIABLE.
4. Write how to change text of the message`s header for couple SUBSTITUTION
VARIABLE. Write how to SELECT these substitutions from DUAL.
5. Write how to see old and new values of SUBSTITUTION VARIABLE.
6. Write how to disable recognition symbol of SUBSTITUTION VARIABLE.
7. Write how to enable recognition symbol of SUBSTITUTION VARIABLE.
Solution:
/* 1 */ &my_var
/* 2.1. */ DEFINE my_var1 = 'my_value1'; DEFINE my_var2 = 'my_value2';
/* 2.2. */ &&my_var1, &&my_var2
SELECT '&my_var1', '&my_var2' FROM DUAL;
/* 3 */ UNDEFINE my_var1;
UNDEFINE my_var2;
/* 4 */ ACCEPT my_var1 PROMPT 'my_text_1';
ACCEPT my_var2 PROMPT 'my_text_2';
SELECT &my_var1,
&my_var2
FROM DUAL
/* 5 */ SET VERIFY ON
SELECT &my_var
FROM DUAL --then go to "SCRIPT_OUTPUT" and will see OLD and NEW values
/* 6 */ SET DEFINE OFF;
/* 7 */ SET DEFINE ON;
Task 2.5.1. Sorting Data. ORDER BY.
Do the next operations.
DROP TABLE TAB;
CREATE TABLE TAB (COL1 NUMBER, COL2 VARCHAR2(100));
INSERT INTO TAB (COL1, COL2) VALUES (1, 'one');
INSERT INTO TAB (COL1, COL2) VALUES (NULL, 'two');
INSERT INTO TAB (COL1, COL2) VALUES (3, NULL);
INSERT INTO TAB (COL1, COL2) VALUES (4, 'four');
COMMIT;
Then write SELECT COL1 c1, COL2 c2 FROM TAB
and make:
1. For COL2 show NULL at the end. Three ways.
2. For COL2 show NULL at the beginning. Two ways.
3. Sort COL1 and COL2 together, but in result for COL1 show NULL at the beginning.
4. Sort COL1 and COL2 together, but in result for COL2 show NULL at the end.
5. Sort COL1 and COL2, but do not write in ORDER BY words: "COL1, COL2". Two ways.
6. Sort COL1 multiply 10.
7. Sort COL2 where first row always must be 'one', second row - 'two', third row
- NULL.
8. Demonstrate BLANK-PADDED and NONPADDED comparison semantics for values:
'ac' ? 'ab'
'ab' ? 'a '
'ab' ? 'a'
'ab' ? 'ab'
'a ' ? 'a'
Solution:
SELECT COL1 c1,
COL2 c2
FROM TAB
ORDER BY /* 1.1. */ COL2
/* 1.2. */ COL2 ASC
/* 1.3. */ COL2 DESC NULLS LAST
/* 2.1. */ COL2 DESC
/* 2.2. */ COL2 NULLS FIRST
/* 3 */ COL1 || COL2 DESC
/* 4 */ COL2 || COL1 DESC
/* 5.1. */ 1, 2
/* 5.2. */ c1, c2
/* 6. */ COL1 * 10
/* 7. */ DECODE(COL2, 'one', 1,
'two', 2,
NULL, 3)
--8
BLANK-PADDED NONPADDED
'ac' > 'ab' 'ac' > 'ab'
'ab' > 'a ' 'ab' > 'a '
'ab' > 'a' 'ab' > 'a'
'ab' = 'ab' 'ab' = 'ab'
'a ' = 'a' 'a ' > 'a'
Task 2.5.2. Sorting Data. ORDER BY, FETCH.
Do the next operations.
DROP TABLE TAB1;
CREATE TABLE TAB1 (ID CHAR, COL1 NUMBER, COL2 VARCHAR2(100));
INSERT INTO TAB1 (ID, COL1, COL2) VALUES ('A', 1, 'one');
INSERT INTO TAB1 (ID, COL1, COL2) VALUES ('B', 2, 'two');
INSERT INTO TAB1 (ID, COL1, COL2) VALUES ('C', 3, NULL);
INSERT INTO TAB1 (ID, COL1, COL2) VALUES ('D', NULL, 'zzz');
INSERT INTO TAB1 (ID, COL1, COL2) VALUES ('E', 2, 'TWO');
COMMIT;
Then write SELECT for ID, COL1, COL2 and using only COL1 or COL2 make:
1. Show in result rows A, B, C, D. Two ways.
2. Show in result rows E, A, B. Two ways.
3. Show in result rows A, B, E. Four ways.
4. Show in result rows C, B, E or C, E, B. Four ways.
Solution:
SELECT ID,
COL1,
COL2
FROM TAB1
/* 1.1. */ FETCH NEXT 4 ROWS ONLY
/* 1.2. */ FETCH FIRST 75 PERCENT ROWS ONLY
/* 2.1. */ ORDER BY COL2 FETCH FIRST 3 ROWS ONLY
/* 2.2. */ ORDER BY COL2 FETCH FIRST 60 PERCENT ROWS ONLY
/* 3.1. */ ORDER BY COL1 FETCH FIRST 3 ROWS ONLY
/* 3.2. */ ORDER BY COL1 FETCH FIRST 60 PERCENT ROWS ONLY
/* 3.3. */ ORDER BY COL1 FETCH FIRST 2 ROWS WITH TIES
/* 3.4. */ ORDER BY COL1 FETCH FIRST 40 PERCENT ROWS WITH TIES
/* 4.1. */ ORDER BY COL1 DESC OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY
/* 4.2. */ ORDER BY COL1 DESC OFFSET 1 ROWS FETCH FIRST 60 PERCENT ROWS ONLY
/* 4.3. */ ORDER BY COL1 DESC OFFSET 1 ROWS FETCH FIRST 2 ROWS WITH TIES
/* 4.4. */ ORDER BY COL1 DESC OFFSET 1 ROWS FETCH NEXT 40 PERCENT ROWS WITH TIES
CHAPTER 3. "Using Conversion Functions and Conditional Expressions"
3.1. Applying the NVL, NULLIF, and COALESCE functions to data
3.2. Understanding implicit and explicit data type conversion
3.3. Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
3.4. Nesting multiple functions
Task 3.1.1. Applying the NVL, NULLIF, and COALESCE functions to data. CASE, DECODE.
Do the next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (C1 NUMBER, C2 NUMBER, C3 NUMBER, C4 NUMBER, C5 NUMBER);
INSERT INTO TAB1 (C1, C2, C3, C4, C5)
VALUES (NULL, NULL, 1, 2, 3);
COMMIT;
Then write query which display:
1. Display C3 if C1 is NULL.
2. Display NULL when C3 = C4, otherwise display C3.
3. In range C1, C2, C3, C4 display first not-NULL value.
4. Display first TRUE condition: if C1 = NULL then display C4, if C3 = 1 then
display C5, otherwise display 0.
5. Display first TRUE condition: if C3 = NULL then display C4, if C3 = 1 then
display C5, otherwise display 0.
6. What is important difference between NVL and COALESCE?
Solution:
SELECT /* 1 */ NVL(C1, C3),
/* 2 */ NULLIF(C3, C4),
/* 3 */ COALESCE(C1, C2, C3, C4),
/* 4 */
CASE
WHEN C1 IS NULL
THEN C4
WHEN C3 = 1
THEN C5
ELSE 0
END,
/* 5 */
DECODE(C3, NULL, C4,
1, C5,
0)
FROM TAB1;
--6
NVL returns result after reading all arguments. But COALESCE returns the first
NOT NULL value immediately (without reading all arguments). It is mean COALESCE
can return result faster. Use COALESCE instead of NVL.
Task 3.3.1. Date to text
Do the next operations.
DROP TABLE TAB;
CREATE TABLE TAB (COL DATE);
INSERT INTO TAB (COL) VALUES (TO_DATE('13.07.2099 23:55:59',
'DD.MM.YYYY HH24:MI:SS'));
COMMIT;
Then сonvert date to text:
1. Show '2099';
2. Show 'TWENTY NINETY-NINE';
3. Show '07';
4. Show 'JULY ';
5. Show 'JUL';
6. Show '13';
7. Show 'MONDAY';
8. Show 'MON';
9. Show '23' OR '11' (for 11 two ways);
10. Show '55';
11. Show '59';
12. Show '86159';
13. Show '13.07.2099 11:55:59 PM'. Two ways;
14. Show '13.07.2099 23:55:59 PM';
15. Show '13TH SEVEN 2099 23:55:59';
16. Show 'THIRTEENTH JUL 2099 23:55:59'. Two ways;
17. Show 'THIRTEENTH JULY TWENTY NINETY-NINE';
18. Show 'DAY: THIRTEENTH / MONTH: JULY / YEAR: 2099';
19. Show '13.7.2099 11:55:59'.
20. Show the ordinal number of week`s day.
21. Show the ordinal number of month`s full week.
22. Show the ordinal number of the year in the decade
Solution:
SELECT /* 1 */ TO_CHAR(COL, 'YYYY'),
/* 2 */ TO_CHAR(COL, 'YEAR'),
/* 3 */ TO_CHAR(COL, 'MM'),
/* 4 */ TO_CHAR(COL, 'MONTH'),
/* 5 */ TO_CHAR(COL, 'MON'),
/* 6 */ TO_CHAR(COL, 'DD'),
/* 7 */ TO_CHAR(COL, 'FMDAY'),
/* 8 */ TO_CHAR(COL, 'DY'),
/* 9 */ TO_CHAR(COL, 'HH24'),
TO_CHAR(COL, 'HH12'),
TO_CHAR(COL, 'HH'),
/* 10 */ TO_CHAR(COL, 'MI'),
/* 11 */ TO_CHAR(COL, 'SS'),
/* 12 */ TO_CHAR(COL, 'SSSSS'),
/* 13 */ TO_CHAR(COL, 'DD.MM.YYYY HH:MI:SS AM'), --or use PM
TO_CHAR(COL, 'DD.MM.YYYY HH12:MI:SS AM'), --or use PM
/* 14 */ TO_CHAR(COL, 'DD.MM.YYYY HH24:MI:SS AM'), --or use PM
/* 15 */ TO_CHAR(COL, 'DDTH MMSP YYYY HH24:MI:SS'),
/* 16 */ TO_CHAR(COL, 'DDSPTH MON YYYY HH24:MI:SS'),
TO_CHAR(COL, 'DDTHSP MON YYYY HH24:MI:SS'),
/* 17 */ TO_CHAR(COL, 'FMDDSPTH MONTH YEAR'),
/* 18 */ TO_CHAR(COL, 'FM"DAY:" DDSPTH / "MONTH:" MONTH / "YEAR:" YYYY'),
/* 19 */ TO_CHAR(COL, 'FMDD.MM.YYYY HH:MI:SS'),
/* 20 */ TO_CHAR(COL, 'D'), --we can set first day in week as Monday:
--ALTER SESSION SET NLS_TERRITORY='GERMANY';
/* 21 */ TO_CHAR(COL, 'W'),
/* 22 */ TO_CHAR(COL, 'Y')
FROM TAB;
Task 3.3.2. Number to text.
Number to text:
1. 2659.49 convert to ' 2,659.49'. Three ways.
2. 2659.49 convert to '0,002,659.490'. Two ways.
3. -10 convert to '$10-'. Two ways.
4. -10 convert to '-10'. Two ways.
5. -10 convert to '<10>'.
6. 10.69 convert to '+$10.7'.
7. For 120.9 and 1000333 write one function, that return '120.90' or
'1,000,333.00'.
Solution:
SELECT /* 1 */ TO_CHAR(2659.49, '9G999D99'),
TO_CHAR(2659.49, '9,999.99'),
TO_CHAR(2659.49, '0,000.00'),
/* 2 */ TO_CHAR(2659.49, 'FM0,000,000.000'),
TO_CHAR(2659.49, 'FM0,009,999.990'),
/* 3 */ TO_CHAR(-10, '$99MI'),
TO_CHAR(-10, 'L99S'), --if NLS_TERRITORY=AMERICA
/* 4 */ TO_CHAR(-10, 'S99'),
TO_CHAR(-10, '99'),
/* 5 */ TO_CHAR(-10, '99PR'),
/* 6 */ TO_CHAR(10.69, 'S$99.9'),
/* 7 */ TO_CHAR(120.9, 'FM999,999,999.00'),
TO_CHAR(1000333, 'FM999,999,999.00')
FROM DUAL;
Task 3.3.3. To date. Text to date.
1. Show date '01.01.2049'. Three ways.
2. Show date '01.01.1950'. Two ways.
3. Show date '01.01.2050'. Two ways.
4. For date '01.01.2000' add 5 days.
5. For date '01.01.2000' subtract 5 days.
6. For date '29.02.2024' (leap year) add 3 months.
7. For date '29.02.2024' (leap year) subtract 3 months.
8. Show quantity of days between '01.01.1999' and '01.01.2000'. Write result.
9. Show quantity of days between '01.01.2000' and '01.01.1999'. Write result.
10. Show negative quantity of months between '01.01.2000' and '01.01.1999'.
11. Show positive quantity of months between '01.01.2000' and '01.01.1999'.
12. For date '01.01.2000' show last day in month.
13. For date '01.01.2000' show next monday. Two ways.
14. Convert '17.07.2000 13:45:59' to '18.07.2000'.
15. Convert '17.07.2000 13:45:59' to '01.01.2001'.
16. Convert '17.07.2000 13:45:59' to '01.08.2000'.
17. Convert '17.08.2000 13:45:59' to '01.10.2000'.
18. Convert '17.07.2000 13:45:59' to '17.07.2000'.
19. Convert '17.07.2000 13:45:59' to '01.01.2000'.
20. Convert '17.07.2000 13:45:59' to '01.07.2000'.
21. Convert '17.08.2000 13:45:59' to '01.07.2000'.
Text to date:
22. '31.december.2000' convert to date 31.12.2000.
Solution:
SELECT
/* 1 */ TO_DATE('01.01.49', 'DD.MM.RR'),
TO_DATE('01.01.49', 'DD.MM.YY'),
TO_DATE('01.01.2049', 'DD.MM.YYYY'),
/* 2 */ TO_DATE('01.01.50', 'DD.MM.RR'),
TO_DATE('01.01.1950', 'DD.MM.YY'),
/* 3 */ TO_DATE('01.01.50', 'DD.MM.YY'),
TO_DATE('01.01.2050', 'DD.MM.YYYY'),
/* 4 */ TO_DATE('01.01.2000', 'DD.MM.YYYY') + 5,
/* 5 */ TO_DATE('01.01.2000', 'DD.MM.YYYY') - 5,
/* 6 */ ADD_MONTHS(TO_DATE('29.02.2024', 'DD.MM.YYYY'), 3),
/* 7 */ ADD_MONTHS(TO_DATE('29.02.2024', 'DD.MM.YYYY'), -3),
/* 8 */ TO_DATE('01.01.1999', 'DD.MM.YYYY') - TO_DATE('01.01.2000', 'DD.MM.YYYY'),
/* 9 */ TO_DATE('01.01.2000', 'DD.MM.YYYY') - TO_DATE('01.01.1999', 'DD.MM.YYYY'),
/* 10 */ MONTHS_BETWEEN(TO_DATE('01.01.1999', 'DD.MM.YYYY'),
TO_DATE('01.01.2000', 'DD.MM.YYYY')),
/* 11 */ MONTHS_BETWEEN(TO_DATE('01.01.2000', 'DD.MM.YYYY'),
TO_DATE('01.01.1999', 'DD.MM.YYYY')),
/* 12 */ LAST_DAY(TO_DATE('01.01.2000', 'DD.MM.YYYY')),
/* 13 */ NEXT_DAY(TO_DATE('01.01.2000', 'DD.MM.YYYY'), 'MONDAY'),
NEXT_DAY(TO_DATE('01.01.2000', 'DD.MM.YYYY'), 'MON'),
-- 2 = 'MONDAY' in my NLS_DATE_LANGUAGE
NEXT_DAY(TO_DATE('01.01.2000', 'DD.MM.YYYY'), 2),
/* 14 */ ROUND(TO_DATE('17.07.2000 13:45:59', 'DD.MM.YYYY HH24:MI:SS')),
/* 15 */ ROUND(TO_DATE('17.07.2000 13:45:59', 'DD.MM.YYYY HH24:MI:SS'), 'YEAR'),
/* 16 */ ROUND(TO_DATE('17.07.2000 13:45:59', 'DD.MM.YYYY HH24:MI:SS'), 'MONTH'),
/* 17 */ ROUND(TO_DATE('17.08.2000 13:45:59', 'DD.MM.YYYY HH24:MI:SS'), 'Q'),
/* 18 */ TRUNC(TO_DATE('17.07.2000 13:45:59', 'DD.MM.YYYY HH24:MI:SS')),
/* 19 */ TRUNC(TO_DATE('17.07.2000 13:45:59', 'DD.MM.YYYY HH24:MI:SS'), 'YEAR'),
/* 20 */ TRUNC(TO_DATE('17.07.2000 13:45:59', 'DD.MM.YYYY HH24:MI:SS'), 'MONTH'),
/* 21 */ TRUNC(TO_DATE('17.08.2000 13:45:59', 'DD.MM.YYYY HH24:MI:SS'), 'Q'),
/* 22 */ TO_DATE('31.december.2000', 'DD.MM.YYYY')
FROM DUAL;
CHAPTER 4. "Displaying Data from Multiple Tables"
4.1. Using Self-joins
4.2. Using Various Types of Joins
4.3. Using Non equijoins
4.4. Using OUTER joins
4.5. Understanding and Using Cartesian Products
Task 4.2.1. JOINS.
1. List Oracle proprietary joins.
2. List SQL:1999 joins.
Solution:
Oracle proprietary joins.
1. Cartesian product, Equijoin, Nonequijoin, Outer join, Self join.
2. Cross join, Natural join, Using clause, Join, Left join, Right join, Full join.
Task 4.2.2. Oracle proprietary joins.
Do the next operations:
DROP TABLE TAB1;
DROP TABLE TAB2;
DROP TABLE TAB3;
CREATE TABLE TAB1 (COL1 NUMBER,
COL2 VARCHAR2(100),
COL3 NUMBER);
CREATE TABLE TAB2 (COL1 NUMBER,
COL22 VARCHAR2(100),
COL33 NUMBER);
CREATE TABLE TAB3 (COL11 VARCHAR2(100),
COL22 NUMBER);
INSERT INTO TAB1(COL1, COL2, COL3) VALUES(11, 'group11', 22);
INSERT INTO TAB1(COL1, COL2, COL3) VALUES(22, 'group22', 22);
INSERT INTO TAB1(COL1, COL2, COL3) VALUES(33, 'group33', 11);
INSERT INTO TAB2(COL1, COL22, COL33) VALUES(11, '11', 20);
INSERT INTO TAB2(COL1, COL22, COL33) VALUES(22, '22', 30);
INSERT INTO TAB2(COL1, COL22, COL33) VALUES(44, '44', 50);
INSERT INTO TAB3(COL11, COL22) VALUES('new22', 22);
INSERT INTO TAB3(COL11, COL22) VALUES('new44', 44);
INSERT INTO TAB3(COL11, COL22) VALUES('new55', 55);
COMMIT;
Then make Oracle proprietary joins:
1. Cartesian product for TAB1 and TAB2;
2. Equijoin for TAB1 and TAB2 and TAB3;
Equijoin for TAB1 and TAB2 with additional condition TAB1.COL1 > 11;
3. Nonequijoin for TAB2 and TAB1. Two ways with same condition;
4. Outer join for TAB1 and TAB2;
5. Self join for TAB1;
Solution:
--1 Cartesian product
SELECT t1.*,
t2.*
FROM TAB1 t1,
TAB2 t2
--2 Equijoin
SELECT t1.*,
t2.*,
t3.*
FROM TAB1 t1,
TAB2 t2,
TAB3 t3
WHERE t1.COL1 = t2.COL1 AND
t2.COL1 = t3.COL22;
SELECT t1.*,
t2.*
FROM TAB1 t1,
TAB2 t2
WHERE t1.COL1 = t2.COL1 AND
t1.COL1 > 11
--3 Nonequijoin
SELECT t1.*,
t2.*
FROM TAB1 t1,
TAB2 t2
WHERE t1.COL1 BETWEEN t2.COL1 AND t2.COL33;
SELECT t1.*,
t2.*
FROM TAB1 t1,
TAB2 t2
WHERE t1.COL1 >= t2.COL1 AND
t1.COL1 <= t2.COL33;
--4 Outer join
SELECT t1.*,
t2.*
FROM TAB1 t1,
TAB2 t2
WHERE t1.COL1 = t2.COL1(+);
SELECT t1.*,
t2.*
FROM TAB1 t1,
TAB2 t2
WHERE t1.COL1(+) = t2.COL1;
--5 Self join
SELECT t1.*,
t2.*
FROM TAB1 t1,
TAB1 t2
WHERE t1.COL1 = t2.COL3
Task 4.2.3. SQL:1999 joins.
Do the next operations:
DROP TABLE TAB1;
DROP TABLE TAB2;
DROP TABLE TAB3;
CREATE TABLE TAB1 (COL1 NUMBER,
COL2 VARCHAR2(100),
COL3 NUMBER);
CREATE TABLE TAB2 (COL1 NUMBER,
COL22 VARCHAR2(100),
COL33 NUMBER);
CREATE TABLE TAB3 (COL11 VARCHAR2(100),
COL22 NUMBER);
INSERT INTO TAB1(COL1, COL2, COL3) VALUES(11, 'group11', 22);
INSERT INTO TAB1(COL1, COL2, COL3) VALUES(22, 'group22', 22);
INSERT INTO TAB1(COL1, COL2, COL3) VALUES(33, 'group33', 11);
INSERT INTO TAB2(COL1, COL22, COL33) VALUES(11, '11', 20);
INSERT INTO TAB2(COL1, COL22, COL33) VALUES(22, '22', 30);
INSERT INTO TAB2(COL1, COL22, COL33) VALUES(44, '44', 50);
INSERT INTO TAB3(COL11, COL22) VALUES('new22', 22);
INSERT INTO TAB3(COL11, COL22) VALUES('new44', 44);
INSERT INTO TAB3(COL11, COL22) VALUES('new55', 55);
COMMIT;
Then make SQL:1999 joins:
1. Cross join for TAB1, TAB2, TAB3 (2 ways);
2. Natural join (in SELECT clause list all columns correctly);
3. Using clause (in SELECT clause list all columns correctly);
4. Join for TAB1 and TAB2 and TAB3 together;
5. Nonequijoin for TAB1 and TAB2;
6. Left join for TAB1 and TAB2;
7. Right join for TAB1 and TAB2;
8. Full join for TAB1 and TAB2;
9. How to make inner join for TAB1 and TAB3 using TAB1.COL1 and TAB3.COL11? All
rows from tables must be displayed. First row from TAB1 must be join with first
row from TAB3, second row to second, third to third.
Solution:
--1 Cross join
SELECT t1.*,
t2.*,
t3.*
FROM TAB1 t1
CROSS JOIN TAB2 t2
CROSS JOIN TAB3 t3
SELECT t1.*,
t2.*,
t3.*
FROM TAB1 t1
CROSS JOIN TAB2 t2, TAB3 t3
--2 Natural join
SELECT COL1,
t1.COL2,
t1.COL3,
t2.COL22,
t2.COL33
FROM TAB1 t1
NATURAL JOIN TAB2 t2 --type of COL1 must be same
--3 Using clause
SELECT COL22,
t2.COL1,
t2.COL33,
t3.COL11
FROM TAB2 t2
JOIN TAB3 t3
USING (COL22) --type of COL22 must be compatible
--4 Join
SELECT t1.*,
t2.*,
t3.*
FROM TAB1 t1
JOIN TAB2 t2
ON t1.COL1 = t2.COL1
JOIN TAB3 t3
ON t2.COL22 = t3.COL22
--5 Nonequijoin with JOIN
SELECT t1.*,
t2.*
FROM TAB1 t1
JOIN TAB2 t2
ON t1.COL1 BETWEEN t2.COL1 AND t2.COL33
--6 Left join
SELECT t1.*,
t2.*
FROM TAB1 t1
LEFT JOIN TAB2 t2
ON t1.COL1 = t2.COL1
--7 Right join
SELECT t1.*,
t2.*
FROM TAB1 t1
RIGHT JOIN TAB2 t2
ON t1.COL1 = t2.COL1
--8 Full join
SELECT t1.*,
t2.*
FROM TAB1 t1
FULL JOIN TAB2 t2
ON t1.COL1 = t2.COL1
--9 Inner join with DECODE
SELECT t1.*,
t3.*
FROM TAB1 t1
INNER JOIN TAB3 t3
ON DECODE(t1.COL1, 11, 1,
22, 2,
33, 3) = DECODE(t3.COL11, 'new22', '1',
'new44', '2',
'new55', '3')
CHAPTER 5. "Using SET Operators"
5.1. Matching the SELECT statements
5.2. Using the ORDER BY clause in set operations
5.3. Using The INTERSECT operator
5.4. Using The MINUS operator
5.5. Using The UNION and UNION ALL operators
Task 5.2.1. Using the ORDER BY clause in set operations
Do the next operations:
DROP TABLE TAB1;
DROP TABLE TAB2;
CREATE TABLE TAB1 (COL1 NUMBER);
INSERT INTO TAB1(COL1) VALUES(4);
INSERT INTO TAB1(COL1) VALUES(3);
CREATE TABLE TAB2 (COL2 NUMBER);
INSERT INTO TAB2(COL2) VALUES(1);
INSERT INTO TAB2(COL2) VALUES(2);
COMMIT;
Then do next:
1. Explain precedence of set operators.
2. Merge TAB1 (write first), TAB2 (write second) and display result: 1 2 3 4.
3. Merge TAB1 (write first), TAB2 (write second) and display result: 4 3 2 1.
Solution:
--1
The set operators have equal precedence. Use parenthesis to override precedence.
--2
--UNION have sort, but does not garantee correct sorting, use ORDER BY to sort
SELECT COL1 FROM TAB1
UNION
SELECT COL2 FROM TAB2
--3
SELECT COL1 FROM TAB1
--ORDER BY COL1 DESC not allowed here
UNION
SELECT COL2 FROM TAB2
ORDER BY COL1 DESC --this ORDER will sort only result
Task 5.3.1-5.4.1 Using The INTERSECT operator, using The MINUS operator.
Do the next operations:
DROP TABLE MY_TAB1;
DROP TABLE MY_TAB2;
CREATE TABLE MY_TAB1 AS
SELECT NULL COL1 FROM DUAL
UNION ALL
SELECT 4 COL1 FROM DUAL
UNION ALL
SELECT 3 COL1 FROM DUAL
UNION ALL
SELECT 2 COL1 FROM DUAL
UNION ALL
SELECT 2 COL1 FROM DUAL;
CREATE TABLE MY_TAB2 AS
SELECT 2 COL1 FROM DUAL
UNION ALL
SELECT 2 COL1 FROM DUAL
UNION ALL
SELECT 1 COL1 FROM DUAL
UNION ALL
SELECT 1 COL1 FROM DUAL
UNION ALL
SELECT NULL COL1 FROM DUAL;
Then make operations (first table must be MY_TAB1 and second table must be
MY_TAB2) with SET operators, explain them and write result:
1. Intersection of sets.
2. Difference of sets.
Solution:
--1 Matching rows from both tables will be retrieved. Duplicates will be removed.
--Also result will be sort by ASC.
--INTERSECT have sort, but does not garantee correct sorting, use ORDER BY to sort
--Result: 2 NULL
SELECT COL1 FROM MY_TAB1
INTERSECT
SELECT COL1 FROM MY_TAB2
--2 Duplicates will be removed. Matching rows from both tables will be removed.
--We retrieve remaining rows only from first table (MY_TAB1) and result will be
--sort by ASC.
--MINUS have sort, but does not garantee correct sorting, use ORDER BY to sort
--Result: 3 4
SELECT COL1 FROM MY_TAB1
MINUS --since Oracle 20c can use EXCEPT or EXCEPT ALL (does not remove duplicates)
SELECT COL1 FROM MY_TAB2
Task 5.5.1. Using The UNION and UNION ALL operators.
Do the next operations:
DROP TABLE MY_TAB1;
DROP TABLE MY_TAB2;
CREATE TABLE MY_TAB1 AS
SELECT NULL COL1 FROM DUAL
UNION ALL
SELECT 3 COL1 FROM DUAL
UNION ALL
SELECT 3 COL1 FROM DUAL
UNION ALL
SELECT 2 COL1 FROM DUAL
UNION ALL
SELECT 2 COL1 FROM DUAL;
CREATE TABLE MY_TAB2 AS
SELECT 2 COL1 FROM DUAL
UNION ALL
SELECT 2 COL1 FROM DUAL
UNION ALL
SELECT 1 COL1 FROM DUAL
UNION ALL
SELECT 1 COL1 FROM DUAL
UNION ALL
SELECT NULL COL1 FROM DUAL;
Then make operations (first table must be MY_TAB1 and second table must be
MY_TAB2) with SET operators, explain them and write result:
1. Merge tables with the exclusion of duplicates;
2. Merge tables without the exclusion of duplicates;
Solution:
--1 Result will be sort by ASC. NULL-rows will be represented by 1 NULL-row.
--Result: 1 2 3 NULL
SELECT COL1 FROM MY_TAB1
UNION
SELECT COL1 FROM MY_TAB2
--2 All rows will be merged
--Result: NULL 3 3 2 2 2 2 1 1 NULL
SELECT COL1 FROM MY_TAB1
UNION ALL
SELECT COL1 FROM MY_TAB2
CHAPTER 6. "Managing Indexes Synonyms and Sequences"
6.1. Managing Indexes
6.2. Managing Synonyms
6.3. Managing Sequences
Task 6.1.1. Managing Indexes.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER,
COL2 VARCHAR2(100));
INSERT INTO TAB1(COL1, COL2) VALUES(111, 'one');
COMMIT;
Then do next operations:
1. Write when need create index;
2. Write when not need create index;
3. Explain when Oracle can create index for TAB1.
4. Write how user can create index for TAB1 and what indexes.
5. How to see indexes in dictionary for TAB1, for COL1, COL2?
6. How to remove index? Two ways.
7. How to change index?
Solution:
--1
- A column contains a wide range of distinct values;
- A column contains a large number of null values;
- One or more columns are frequently used together in a WHERE clause or a join
condition;
- The table is large and most queries are expected to retrieve less than 2% to 4%
of the rows in the table.
--2
- The columns are not often used as a condtiton in the query;
- The table is small or most queries are expected to retrieve more than 2% to 4%
of the rows in the table;
- The table is updated frequently;
- The indexed columns are referenced as part of an expression.
--3 Unique indexes creates by Oracle for constraints: PRIMARY KEY and UNIQUE.
--In column with unique index can`t be duplicates. INDEX_TYPE will be 'NORMAL'
--Name of indexes will be equal name of constraints, for example:
CREATE TABLE TAB1(COL1 NUMBER CONSTRAINT CONS_TAB1_PK PRIMARY KEY,
COL2 VARCHAR2(100) CONSTRAINT CONS_TAB1_U UNIQUE);
--4 Not unique index can create by user. INDEX_TYPE will be 'NORMAL'.
CREATE INDEX MY_IND ON TAB1(COL1, COL2);
--Unique index can create by user. Column will not have duplicates. And if we
--use function for index, that INDEX_TYPE will be 'FUNCTION-BASED NORMAL'
CREATE UNIQUE INDEX MY_IND_U ON TAB1(COL1, UPPER(COL2));
--Bitmap index can create in Enterprise edition. INDEX_TYPE will be 'BITMAP'.
--Bitmap index need create for group rows in columns.
CREATE BITMAP INDEX MY_IND_B ON TAB1(COL2);
--5
--view with all indexes by user
SELECT *
FROM USER_INDEXES
WHERE TABLE_NAME = 'TAB1';
--view with columns and their indexes
SELECT *
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'TAB1';
--view for 'FUNCTION-BASED NORMAL' indexes
SELECT *
FROM USER_IND_EXPRESSIONS
WHERE TABLE_NAME = 'TAB1';
--6
DROP INDEX MY_IND_U;
ALTER TABLE TAB1 DISABLE CONSTRAINT CONS_TAB1_U;
--7 need drop and create again
DROP INDEX MY_IND;
CREATE INDEX MY_IND ON TAB1(COL1, COL2);
Task 6.2.1. Managing Synonyms.
Do next operations:
DROP TABLE TAB1;
DROP TABLE TAB2;
CREATE TABLE TAB1(COL1 NUMBER, COL2 VARCHAR2(100));
INSERT INTO TAB1(COL1, COL2) VALUES(111, 'one');
CREATE TABLE TAB2(COL1 NUMBER, COL2 VARCHAR2(100));
INSERT INTO TAB2(COL1, COL2) VALUES(222, 'two');
COMMIT;
Then do next operations:
1. Create private synonym using TAB1 for your schema;
2. Create synonym (with same name from point 1) for TAB2 for all users.
3. What a precedence between synonyms from point 1 and 2. Explain it.
4. Find both synonyms in dictionary. Who owns these synonyms?
5. Remove both synonyms.
Solution:
--1
CREATE SYNONYM MY_SYN FOR TAB1;
--2
CREATE PUBLIC SYNONYM MY_SYN FOR TAB2;
--3
Private synonym have precedence over public.
If at HR-schema make SELECT * FROM MY_SYN in result we will see data from TAB1;
--4
--For private synonym owner will be user, which created this synonym.
--For public synonym owner will be PUBLIC
SELECT * FROM ALL_SYNONYMS;
--5
DROP SYNONYM MY_SYN;
DROP PUBLIC SYNONYM MY_SYN;
Task 6.3.1. Managing Sequences.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER,
COL2 VARCHAR2(100));
INSERT INTO TAB1(COL1, COL2) VALUES(111, 'one');
COMMIT;
Then do next operations:
1. Create sequence with name SEQ (write all parameters manually).
2. For TAB1 write all situations where we can use next and current values of SEQ.
3. For TAB1 write all situations where we can`t use next and current values of
SEQ.
4. Show dictionary info for SEQ.
5. Change maximum value to 1000.
6. Remove SEQ.
7. Create sequence in range [-1;-999] (write all parameters manually).
8. What is issue about INSERT and SEQUENCE?
Solution:
--1
CREATE SEQUENCE SEQ
START WITH 1
INCREMENT BY 1
MINVALUE 1 --NOMINVALUE, it is mean that MINVALUE = START WITH
MAXVALUE 999 --NOMAXVALUE
NOCYCLE --CYCLE
CACHE 20 --NOCACHE
ORDER --NOORDER;
--2
--2.1. The SELECT list of a SELECT statement that is not part of subquery
SELECT COL1,
SEQ.NEXTVAL
FROM TAB1;
--2.2. The VALUES clause of an INSERT statement;
INSERT INTO TAB1(COL1) VALUES(SEQ.NEXTVAL);
COMMIT;
--2.3. The SET clause of an UPDATE statement
UPDATE TAB1
SET COL1 = SEQ.CURRVAL
WHERE COL2 = 'one';
COMMIT;
--2.4. In DEFAULT value.
ALTER TABLE TAB1 MODIFY COL1 DEFAULT SEQ.NEXTVAL;
DROP TABLE TAB2;
CREATE TABLE TAB2 (COL1 NUMBER DEFAULT SEQ.NEXTVAL,
COL2 VARCHAR2(100));
--3
--3.1. Sequence is part of subquery in SELECT, DELETE or UPDATE
SELECT *
FROM (SELECT SEQ.NEXTVAL FROM TAB1);
SELECT *
FROM TAB1
WHERE COL1 = SEQ.NEXTVAL;
DELETE TAB1
WHERE COL1 = SEQ.NEXTVAL;
UPDATE TAB1
SET COL2 = 'two'
WHERE COL1 = SEQ.NEXTVAL;
--3.2. In SELECT with DISTINCT
SELECT DISTINCT SEQ.NEXTVAL
FROM TAB1;
--3.3. A SELECT statement with GROUP BY, HAVING or ORDER BY clauses.
SELECT SEQ.NEXTVAL
FROM TAB1
GROUP BY COL1; --GROUP BY SEQ.NEXTVAL;
SELECT SEQ.NEXTVAL
FROM TAB1
ORDER BY COL1; --ORDER BY SEQ.NEXTVAL;
--3.4. The SELECT list of a view
CREATE OR REPLACE VIEW VIEW_TAB1 AS
SELECT COL1,
SEQ.NEXTVAL COL_SEQ
FROM TAB1;
--4
SELECT *
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'SEQ';
--5
ALTER SEQUENCE SEQ MAXVALUE 1000;
--6
DROP SEQUENCE SEQ;
--7
CREATE SEQUENCE SEQ
START WITH -1
INCREMENT BY -1
MINVALUE -999 --NOMINVALUE
MAXVALUE -1 --NOMAXVALUE
CYCLE --NOCYCLE
CACHE 20 --NOCACHE
ORDER; --NOORDER
--8
--For insert to HR.MY_TABLE (with DEFAULT MY_SEQUENCE.NEXTVAL) from other schema
GRANT INSERT ON HR.MY_TABLE TO ANOTHER_SCHEMA;
GRANT SELECT ON HR.MY_SEQUENCE TO ANOTHER_SCHEMA;
CHAPTER 7. "Managing Views"
7.1. Managing Views
Task 7.1.1. Managing Views
Do next operations:
DROP TABLE TAB1;
DROP TABLE TAB2;
CREATE TABLE TAB1(COL1 NUMBER,
COL2 VARCHAR2(100));
INSERT INTO TAB1(COL1, COL2) VALUES(111, 'one');
INSERT INTO TAB1(COL1, COL2) VALUES(222, 'two');
CREATE TABLE TAB2(COL1 NUMBER,
COL2 VARCHAR2(100));
INSERT INTO TAB2(COL1, COL2) VALUES(111, 'ONE_ONE');
INSERT INTO TAB2(COL1, COL2) VALUES(333, 'three');
COMMIT;
Then do next operations:
1. Create view without tables in database;
2. Create simple view. Two ways (list constraint`s type). Explain them and find
in constraint`s dictionary.
3. Create complex view with all conditions of complex view. But CREATE clause
must be another that in point 1 or 2.
4. Explain INSERT, DELETE, UPDATE for view where it is allowed.
5. Find view in dictionary.
6. Remove view.
7. How much columns can have view?
Solution:
--1
DROP TABLE MY_TABLE;
CREATE OR REPLACE FORCE VIEW V_F AS
SELECT MY_COLUMN
FROM MY_TABLE;
--2
--DML-operations not allowed
CREATE OR REPLACE VIEW V_TAB1 AS
SELECT COL1, COL2
FROM TAB1
WITH READ ONLY; --constraint type is "O"
--DML operations allowed only for condition of view (for COL1 = 111)
CREATE OR REPLACE VIEW V_TAB2 AS
SELECT COL1, COL2
FROM TAB2
WHERE COL1 = 111
WITH CHECK OPTION CONSTRAINT CONS_V_TAB2_V; --constraint type is "V"
INSERT INTO V_TAB2 (COL1, COL2) VALUES (111, 'onlyCOL1 = 111');
COMMIT;
--this constraints in dictionary
SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'V_TAB1' OR
CONSTRAINT_NAME = 'CONS_V_TAB2_V';
--3
CREATE OR REPLACE VIEW V_COM (C1, C2) AS
SELECT t1.COL1,
MAX(t2.COL1)
FROM TAB1 t1
LEFT JOIN TAB2 t2
ON t1.COL1 = t2.COL1
GROUP BY t1.COl1;
--4
DML allowed in simple views and in complex views (not always).
DELETE not allowed if view:
- contain group functions;
- contain GROUP BY clause;
- contain DISTINCT;
- contain ROWNUM.
UPDATE not allowed if view:
- contain group functions;
- contain GROUP BY clause;
- contain DISTINCT;
- contain ROWNUM;
- columns defined by expressions (for example: COL1 * 5).
INSERT not allowed if view:
- contain group functions;
- contain GROUP BY clause;
- contain DISTINCT;
- contain ROWNUM;
- columns defined by expressions (for example: COL1 * 5);
- NOT NULL columns without default value in the base tables that are not
selected by the view.
--5
SELECT *
FROM USER_VIEWS
WHERE VIEW_NAME = 'V_F';
--6
DROP VIEW V_F;
--7
Maximum is 1000.
CHAPTER 8. "Managing Objects with Data Dictionary Views"
8.1. Using data dictionary views
Task 8.1.1. Using data dictionary views
1. What user own all base tables and user-accessible views of the data dictionary?
2. List prefixes for dictionary`s views and explain what there contain.
3. Explain USER_OBJECTS/ALL_OBJECTS and what contain columns: OBJECT_NAME,
OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, STATUS, GENERATED.
4. Explain view USER_CATALOG, columns and write synonym.
5. Explain view USER_TABLES. Write synonym.
6. Explain USER_TAB_COLUMNS.
7. Explain USER_CONSTRAINTS and columns: CONSTRAINT_TYPE, DELETE_RULE, STATUS.
What constraints not allowed and allowed for columns of LOB type?
8. Explain USER_CONS_COLUMNS.
9. Which objects can have comments? Create table with one column and create
comment for table and for this column. Display views containing info about this
comments. What comment length is allowed? How to drop comments?
Solution:
--1
SYS
--2
USER - contains information about objects that you own.
ALL - contains information about all objects to which you have access.
DBA - contains information about all objects by all users.
V$ - performance-related data.
--3
USER_OBJECTS - contain info about all objects in your schema.
ALL_OBJECTS - contain info about all objects to which you have access.
OBJECT_NAME - name of the object;
OBJECT_ID - dictionary object number of the object;
OBJECT_TYPE - type of object;
CREATED - timestamp about the creation of the object;
LAST_DDL_TIME - timestamp of last DDL modification of the object;
STATUS - status of the object (valid, invalid or N/A);
GENERATED - was the name of this object system-generated (Y/N).
--4
'USER_CATALOG' lists indexes, tables, views, clusters, synonyms, and sequences
owned by the current user.
Have columns: TABLE_NAME, TABLE_TYPE. Synonym is 'CAT'.
--5
'USER_TABLES' contain detailed info about all your tables. Synonym is 'TABS'.
--6
'USER_TAB_COLUMNS' contain info about columns of the tables, views, and clusters
owned by the current user. Have column names, column data types, length of
data_types, precision and scale for NUMBER columns, nullable, default value.
--7
'USER_CONSTRAINTS' contain info about constraints for your tables.
CONSTRAINT_TYPE - contain: 'C' - check constraint or not null,
'P' - primary key,
'U' - unique key,
'R' - referential integrity (foreign key),
'V' - with check option, on view,
'O' - with read-only, on a view;
DELETE_RULE - 'CASCADE', 'SET NULL', 'NO ACTION';
STATUS - 'ENABLED', 'DISABLED'.
UNIQUE KEY, PRIMARY KEY, FOREIGN KEY not allowed for LOB, but CHECK allowed.
--8
'USER_CONS_COLUMNS' contain info about constraints for columns.
--9
--Table, view, materialized view, or its columns
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER);
COMMENT ON TABLE TAB1 IS 'This is comment for TAB1';
COMMENT ON COLUMN TAB1.COL1 IS 'This is comment for COL1';
SELECT * FROM ALL_TAB_COMMENTS;
SELECT * FROM ALL_COL_COMMENTS;
Comment length <= 4000 bytes.
COMMENT ON TABLE TAB1 IS ''; COMMENT ON COLUMN TAB1.COL1 IS ''; --drop comments
CHAPTER 9. "Retrieving Data using the SQL SELECT Statement"
9.1. Using Column aliases.
9.2. Using The SQL SELECT statement.
9.3. Using concatenation operator, literal character strings, alternative quote
operator, and the DISTINCT keyword.
9.4. Using Arithmetic expressions and NULL values in the SELECT statement.
Task 9.2.1. Using The SQL SELECT statement. PIVOT. UNPIVOT.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER, COL2 VARCHAR2(100), CNT NUMBER);
INSERT INTO TAB1 VALUES(10, 'A', 1);
INSERT INTO TAB1 VALUES(10, 'A', 1);
INSERT INTO TAB1 VALUES(20, 'A', 1);
INSERT INTO TAB1 VALUES(20, 'B', 1);
INSERT INTO TAB1 VALUES(30, 'A', 1);
COMMIT;
Then do next operations:
1. Pivot table. Show in rows for COL1, count of rows for values from COL2.
2. Demonstrate unpivot COL2, CNT where COL1 equal 20 and 30. Write result.
Solution:
--1
SELECT COL1,
"A",
"B"
FROM(
SELECT COL1,
COL2,
CNT
FROM TAB1
)
PIVOT (
COUNT(CNT) FOR COL2 IN (
'A' AS "A",
'B' AS "B"
)
)
ORDER BY 1
--2
SELECT COL1,
COL_NAME,
COL_VALUE
FROM
(
SELECT TO_CHAR(COL1) COL1,
TO_CHAR(COL2) COL2,
TO_CHAR(CNT) CNT
FROM TAB1
WHERE COL1 IN (20, 30)
)
UNPIVOT
(
COL_VALUE FOR COL_NAME IN (COL2, CNT)
)
--
COL1 COL_NAME COL_VALUE
20 COL2 A
20 CNT 1
20 COL2 B
20 CNT 1
30 COL2 A
30 CNT 1
Task 9.3.1. Using Column aliases, SQL SELECT statement, concatenation operator,
literal character strings, alternative quote operator, and the DISTINCT keyword.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER,
COL2 VARCHAR2(100),
COL3 NUMBER);
INSERT INTO TAB1(COL1, COL2, COL3) VALUES (1, 'one', 2);
INSERT INTO TAB1(COL1, COL2, COL3) VALUES (1, 'one', 3);
INSERT INTO TAB1(COL1, COL2, COL3) VALUES (2, 'two', 4);
INSERT INTO TAB1(COL1, COL2, COL3) VALUES (NULL, 'nn', 5);
COMMIT;
Then do next operations:
1. Make query that retrieve unique rows for COL1 and write alias starting to
number with spaces.
2. Make query that retrieve unique rows for COL1 and COL2.
3. Make query that concatenating COL1 and COL2. But between this values must be
text (without double quotes) " it's COL1 + COL2_".
Solution:
--1
--DISTINCT removes duplicates and applies additional sorting.
--DISTINCT have sort, but does not garantee correct sorting, use ORDER BY to sort
SELECT DISTINCT COL1 "1 col1 "
FROM TAB1
--2
SELECT DISTINCT COL1, COL2
FROM TAB1
--3
SELECT COL1||q'{ it's COL1 + COL2_}'||COL2
FROM TAB1
Task 9.4.1. Using Arithmetic expressions and NULL values in the SELECT statement.
1. Explain result of arithmetic expressions with NULL.
2. Write result for query:
SELECT 1 + NULL,
1 - NULL,
1 * NULL,
1 / NULL
FROM DUAL
Solution:
--1
Arithmetic expressions with NULL have result equal NULL.
--2
NULL, NULL, NULL, NULL
CHAPTER 10. "Using Single-Row Functions to Customize Output"
10.1. Manipulating strings with character functions in SQL SELECT and WHERE clauses
10.2. Performing arithmetic with date data
10.3. Manipulating numbers with the ROUND, TRUNC and MOD functions
10.4. Manipulating dates with the date function
Task 10.1.1. U, L, I, C, S, L, I, C.
For next string 'One two':
1. Convert to text 'one two';
2. Convert to text 'ONE TWO';
3. Convert to text 'One Two'.
For SELECT 'One', 'Two', 1 FROM DUAL:
4. Convert to text 'OneTwo';
5. Convert to text 'OneTwo1'.
For SELECT 'abcde' FROM DUAL:
6. Convert to text 'abc';
7. Convert to text 'de'. Two ways.
8. Convert to text 'bcd';
For SELECT 'nameANn' FROM DUAL:
9. Show symbol`s quantity.
10. Show number`s position for 'a';
11. Show number`s position for 'A';
12. Show number`s position for second 'n' left to right. Two ways;
13. Show number`s position for second 'n' right to left. Two ways;
Show symbol:
14. For symbol`s code 98.
Solution:
SELECT /* 1 */ LOWER('One two'),
/* 2 */ UPPER('One two'),
/* 3 */ INITCAP('One two')
FROM DUAL;
SELECT /* 4 */ CONCAT('One', 'Two'),
/* 5 */ 'One' || 'Two' || 1
FROM DUAL;
SELECT /* 6 */ SUBSTR('abcde', 1, 3),
/* 7.1. */ SUBSTR('abcde', 4, 2),
/* 7.2. */ SUBSTR('abcde', -2),
/* 8 */ SUBSTR('abcde', 2, 3)
FROM DUAL;
SELECT /* 9 */ LENGTH('nameANn'),
/* 10 */ INSTR('nameANn', 'a'),
/* 11 */ INSTR('nameANn', 'A'),
/* 12.1. */ INSTR('nameANn', 'n', 2),
/* 12.2. */ INSTR('nameANn', 'n', 1, 2),
/* 13.1. */ INSTR('nameANn', 'n', -2),
/* 13.2. */ INSTR('nameANn', 'n', -1, 2)
FROM DUAL;
SELECT /* 14 */ CHR(98)
FROM DUAL;
Task 10.1.2. L, R, R, T, T, L, R.
For SELECT 'one' FROM DUAL:
1. Convert to text ' one'. Two ways.
2. Convert to text 'one '. Two ways.
3. Convert to text '111one'.
4. Convert to text 'oneZZZ'.
For SELECT 'myORAcleORA' FROM DUAL:
5. Convert to text 'my2cle2'.
6. Convert to text 'mycle'.
7. Convert to text 'zzORAzzzORA'.
8. Convert to text 'ORAORA'.
For SELECT ' ora ZZZ ' FROM DUAL:
9. Convert to text 'ora ZZZ'. Three ways.
10. Convert to text 'ora ZZZ '. Two ways.
11. Convert to text ' ora ZZZ'. Two ways.
For SELECT ' oraYYY' FROM DUAL:
12. Convert to text ' ora'. Three ways.
Solution:
SELECT /* 1.1. */ LPAD('one', 5),
/* 1.2. */ LPAD('one', 5, ' '),
/* 2.1. */ RPAD('one', 5),
/* 2.2. */ RPAD('one', 5, ' '),
/* 3 */ LPAD('one', 6, '1'),
/* 4 */ RPAD('one', 6, 'Z')
FROM DUAL;
SELECT /* 5 */ REPLACE('myORAcleORA', 'ORA', '2'),
/* 6 */ REPLACE('myORAcleORA', 'ORA'),
/* 7 */ TRANSLATE('myORAcleORA', 'mycle', 'zzzzz'),
/* 8 */ REPLACE(TRANSLATE('myORAcleORA', 'mycle', ' '), ' ')
FROM DUAL;
SELECT /* 9.1. */ TRIM(' ora ZZZ '),
/* 9.2. */ TRIM(' ' FROM ' ora ZZZ '),
/* 9.3. */ TRIM(BOTH ' ' FROM ' ora ZZZ '),
/* 10.1. */ TRIM(LEADING ' ' FROM ' ora ZZZ '),
/* 10.2. */ LTRIM(' ora ZZZ '),
/* 11.1. */ TRIM(TRAILING ' ' FROM ' ora ZZZ '),
/* 11.2. */ RTRIM(' ora ZZZ '),
/* 12.1. */ TRIM(TRAILING 'Y' FROM ' oraYYY'),
/* 12.2. */ TRIM('Y' FROM ' oraYYY'),
/* 12.3. */ RTRIM(' oraYYY', 'Y')
FROM DUAL;
Task 10.3.1. R, T, M.
Without cut off:
1. Convert 155.594 to 156;
2. Convert 155.594 to 155.6;
3. Convert 155.594 to 155.59;
4. Convert 155.594 to 160;
5. Convert 155.594 to 200;
6. Convert 155.594 to 0;
With cut off:
7. Convert 155.594 to 155;
8. Convert 155.594 to 155.59;
9. Convert 155.594 to 100;
10. Convert 155.594 to 0;
Get the remainder of the division:
11. 10/2;
12. 9/7.
Solution:
SELECT /* 1 */ ROUND(155.594),
/* 2 */ ROUND(155.594, 1),
/* 3 */ ROUND(155.594, 2),
/* 4 */ ROUND(155.594, -1),
/* 5 */ ROUND(155.594, -2),
/* 6 */ ROUND(155.594, -3),
/* 7 */ TRUNC(155.594),
/* 8 */ TRUNC(155.594, 2),
/* 9 */ TRUNC(155.594, -2),
/* 10 */ TRUNC(155.594, -3),
/* 11 */ MOD(10, 2),
/* 12 */ MOD(9, 7)
FROM DUAL;