Different examples for training to exam Oracle Database SQL 1Z0-071. Part 2.
CHAPTER 11. "Reporting Aggregated Data Using Group Functions"
CHAPTER 12. "Using Subqueries to Solve Queries"
CHAPTER 13. "Managing Tables using DML statements"
CHAPTER 14. "Use DDL to manage tables and their relationships"
CHAPTER 15. "Controlling User Access"
CHAPTER 16. "Managing Data in Different Time Zones"
CHAPTER 11. "Reporting Aggregated Data Using Group Functions"
11.1. Restricting Group Results
11.2. Creating Groups of Data
11.3. Using Group Functions
Task 11.3.1. Using Group Functions.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER);
INSERT INTO TAB1 VALUES(1);
INSERT INTO TAB1 VALUES(3);
INSERT INTO TAB1 VALUES(NULL);
COMMIT;
Then do next operations:
1. Write what happen with NULL values in group functions?
2. Retrieve minimum value for COL1.
3. Retrieve maximum value for COL1.
4. Retrieve average for COL1.
5. Retrieve addition of all values for COL1.
6. Retrieve quantity of rows for COL1.
7. Display all values of COL1 through comma and suppose that result has size over
4000 symbols and you want ignore error, also remove duplicates.
8. How much group functions can be nested?
Solution:
--1 In group functions NULLs ignored.
SELECT /* 2 */ MIN(COL1),
/* 3 */ MAX(COL1),
/* 4 */ AVG(COL1),
/* 5 */ SUM(COL1),
/* 6 */ COUNT(COL1),
/* 7 */ LISTAGG(DISTINCT COL1,
', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT)
WITHIN GROUP (ORDER BY COL1)
FROM TAB1
--8 Two functions can be nested, for example:
SELECT COUNT(max(COL1)) FROM TAB1 GROUP BY COL1
Task 11.3.2. Using Group Functions. COUNT.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER);
INSERT INTO TAB1 VALUES(NULL);
INSERT INTO TAB1 VALUES(NULL);
INSERT INTO TAB1 VALUES(1);
INSERT INTO TAB1 VALUES(1);
INSERT INTO TAB1 VALUES(2);
COMMIT;
Then run query:
SELECT COUNT(COL1),
COUNT(*),
COUNT(1),
COUNT(DISTINCT COL1)
FROM TAB1
1. Write result and explain what happens with NULLS.
Solution:
--1
COUNT(COL1) = 3 --Quantity of rows for COL1, but NULLs will be ignored.
COUNT(*) = 5 --Quantity of all rows in table (NULLs will not be ignored).
COUNT(1) = 5 --Quantity of all rows in table (NULLs will not be ignored).
COUNT(DISTINCT COL1) = 2 --Quantity of rows for COL1, but NULLs will be ignored.
--Duplicates will be removed.
CHAPTER 12. "Using Subqueries to Solve Queries"
12.1. Using Single Row Subqueries
12.2. Using Multiple Row Subqueries
12.3. Update and delete rows using correlated subqueries
Task 12.1.2. Using Multiple Row Subqueries.
Do the next operations:
DROP TABLE TAB;
CREATE TABLE TAB(PROD VARCHAR2(100),
PRICE NUMBER,
MARK NUMBER);
INSERT INTO TAB(PROD, PRICE, MARK) VALUES('prod1', 100, 1);
INSERT INTO TAB(PROD, PRICE, MARK) VALUES('prod2', 170, 3);
INSERT INTO TAB(PROD, PRICE, MARK) VALUES('prod3', NULL, 1);
INSERT INTO TAB(PROD, PRICE, MARK) VALUES('prod4', 150, NULL);
INSERT INTO TAB(PROD, PRICE, MARK) VALUES('prod5', 170, 2);
INSERT INTO TAB(PROD, PRICE, MARK) VALUES('prod6', 190, 2);
INSERT INTO TAB(PROD, PRICE, MARK) VALUES('prod7', NULL, 2);
COMMIT;
Then make SELECT with subqueries:
1. Show rows where price = price for prod with MARK = 2. Two ways.
2. Show rows where price <> price for prod with MARK = 1. Two ways.
3. Show rows where price > price for prod with MARK = 1 OR
> price for prod with MARK = 3. Two ways.
4. Show rows where price < price for prod with MARK = 2 (it is mean price must be
< 170 and < 190 simultaneously).
Solution:
--1.
SELECT t.*
FROM TAB t
WHERE t.PRICE IN (SELECT PRICE
FROM TAB
WHERE MARK = 2)
ORDER BY t.PROD;
SELECT t1.*
FROM TAB t1
WHERE EXISTS (SELECT NULL
FROM TAB t2
WHERE t1.PRICE = t2.PRICE AND
t2.MARK = 2)
ORDER BY t1.PROD;
--2.
SELECT t.*
FROM TAB t
WHERE COALESCE(t.PRICE, 1) NOT IN (SELECT COALESCE(PRICE, 0) PRICE
FROM TAB
WHERE MARK = 1)
ORDER BY t.PROD;
SELECT t1.*
FROM TAB t1
WHERE NOT EXISTS (SELECT NULL
FROM TAB t2
WHERE t1.PRICE = t2.PRICE AND
t2.MARK = 1)
ORDER BY t1.PROD;
--3.
--second way is using SOME instead of ANY
SELECT t.*
FROM TAB t
WHERE t.PRICE > ANY /* SOME */ (SELECT PRICE
FROM TAB
WHERE MARK IN (1, 3))
ORDER BY t.PROD;
--4.
SELECT t.*
FROM TAB t
WHERE t.PRICE < ALL (SELECT COALESCE(PRICE, 500)
FROM TAB
WHERE MARK = 2)
ORDER BY t.PROD;
CHAPTER 13. "Managing Tables using DML statements"
13.1. Managing Database Transactions
13.2. Controlling transactions
13.3. Perform Insert, Update and Delete operations
13.4. Performing multi table Inserts
13.5. Performing Merge statements
Task 13.2.1. Controlling transactions
Look on operations and answer the questions:
/* A */ CREATE TABLE TAB1 (COL1 NUMBER);
/* B */ INSERT INTO TAB1 (COL1) VALUES(100);
/* C */ INSERT INTO TAB1 (COL1) VALUES(200);
/* D */ CREATE TABLE TAB2 (COL1 NUMBER);
/* E */ INSERT INTO TAB2 (COL1) VALUES(300);
/* F */ INSERT INTO TAB2 (COL1) VALUES(400);
/* G */ ...
Answer the questions:
1. What tables and data we will see in another session after row C?
2. What tables and data we will see in another session after row D?
3. How to return data back in TAB2 before row E, when TAB2 was empty?
4. How to return data back in TAB2 before row F, when TAB2 had only row E?
5. What we will see in another session after action №4?
6. How to make see in another session all rows (except G) and tables in database?
Solution:
1. TAB1 without data, because didn`t write 'COMMIT';
2. TAB1 with rows B and C and empty TAB2. Because CREATE is DDL command, DDL
commands makes COMMIT;
3. Write 'ROLLBACK;' in row G.
4. Write, for example, 'SAVEPOINT A;' after row E. Then after row F write
'ROLLBACK TO SAVEPOINT A;'.
5. We will see TAB1 with rows B, C and TAB2 without rows because have not COMMIT;
6. Write 'COMMIT;' in row G.
Task 13.2.2. Controlling transactions. Different sessions.
Look on operations and answer the questions:
--at 00:30 in first session HR-user connect to database ORCLPDB and made actions
/* A */ DROP TABLE TAB1;
/* B */ CREATE TABLE TAB1 (COL1 NUMBER);
/* C */ INSERT INTO TAB1 (COL1) VALUES(100);
--at 00:40 in second session HR-user connect to database ORCLPDB and made actions
/* D */ INSERT INTO TAB1 (COL1) VALUES(200);
/* E */ DROP TABLE TAB1;
/* F */ INSERT INTO TAB1 (COL1) VALUES(300);
/* G */ ALTER TABLE TAB1 ADD COL2 NUMBER;
/* H */ INSERT INTO TAB1 (COL1) VALUES(400);
/* I */ COMMENT ON TABLE TAB1 IS 'Table TAB1';
/* J */ COMMENT ON COLUMN TAB1.COL1 IS 'Column COL1';
--at 00:50 in first session HR-user
/* K */ ALTER TABLE TAB1 ADD COL2 NUMBER;
What will be data in table TAB1 in first, second sessions and explain result of
operation:
1. After action C.
2. After action E.
3. After action G.
4. After action J.
5. After action K.
Solution:
--Oracle Database implicitly commits the current transaction before and after
--every DDL, DCL statement.
--The CREATE, ALTER, TRUNCATE and DROP commands require exclusive access to the
--specified object.
--The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require
--exclusive access to the specified object.
--1
Result = success.
In first session: 100, in second - empty table.
--2
Result = immediately will be error: ORA-00054: resource busy and acquire with
NOWAIT specified or timeout expired. --TAB1 not will be dropped.
In first session: 200, 100, in second: 200.
--3
Result = after time period will be error: ORA-00054: resource busy and acquire
with NOWAIT specified or timeout expired. --Column not will be added.
In first session: 200, 300, 100, in second: 200, 300.
--4
Result = success. --Comments will be added in TAB1.
In first session: 200, 300, 400, 100, in second - 200, 300, 400.
--5
Result = success. --Column COL2 will be added.
In first session: 200, 300, 400, 100, in second - 200, 300, 400, 100.
Task 13.3.1. Perform Insert, Update and Delete operations. INSERT.
Do the next operations:
DROP TABLE TAB1;
DROP TABLE TAB2;
CREATE TABLE TAB1 (COL1 NUMBER, COL2 VARCHAR2(100));
CREATE TABLE TAB2 (COL1 NUMBER, COL2 VARCHAR2(100));
Then:
1. Show 3 ways of INSERT in TAB1.
2. Show 3 ways of INSERT from TAB1 to TAB2.
Solution:
--1.
INSERT INTO TAB1 (COL1, COL2) VALUES (1, 'one');
INSERT INTO TAB1 (COL2, COL1) VALUES ('one', 1);
INSERT INTO TAB1 VALUES (1, 'one');
--2.
INSERT INTO TAB2 SELECT * FROM TAB1;
INSERT INTO TAB2 SELECT COL1, COL2 FROM TAB1;
INSERT INTO TAB2 (COL2, COL1) SELECT COL2, COL1 FROM TAB1;
Task 13.3.2. Perform Insert, Update and Delete operations. INSERT.
Do next operations:
DROP TABLE TAB1;
DROP TABLE TAB2;
CREATE TABLE TAB1 (COL1 NUMBER,
COL2 VARCHAR2(100) DEFAULT 'zero');
INSERT INTO TAB1 (COL1, COL2) VALUES (1, 'one');
INSERT INTO TAB1 (COL1, COL2) VALUES (2, 'two');
INSERT INTO TAB1 (COL1, COL2) VALUES (3, 'three');
COMMIT;
CREATE TABLE TAB2 (COL1 NUMBER,
COL2 VARCHAR2(100));
Then do next operations:
1. List types of data warehouse and explain it.
2. Show how to insert into TAB1.COL2 DEFAULT value (two ways).
3. Show how to insert into TAB1.COL2 NULL value.
4. Insert rows into TAB2 from TAB1. 2 ways.
5. Insert rows into TAB2 from subquery where TAB1 have union all with TAB1.
Solution:
--1
OLTP - database designed for transaction processing (DML, DDL, etc.);
OLAP - database designed for queries and analysis.
--2
INSERT INTO TAB1(COL1) VALUES (4);
INSERT INTO TAB1(COL1, COL2) VALUES (5, DEFAULT);
COMMIT;
--3
INSERT INTO TAB1(COL1, COL2) VALUES (6, NULL);
COMMIT;
--4
INSERT INTO TAB2
SELECT * FROM TAB1;
INSERT INTO TAB2 (COL2, COL1)
SELECT COL2, COL1 FROM TAB1;
COMMIT;
--5
INSERT INTO TAB2
--(
SELECT COL1, COL2 FROM TAB1
UNION ALL
SELECT COL1, COL2 FROM TAB1;
--);
COMMIT;
Task 13.3.3. Perform Insert, Update and Delete operations. FOR UPDATE.
Do the next operations:
DROP TABLE TAB1;
DROP TABLE TAB2;
CREATE TABLE TAB1 (COL1 NUMBER, COL2 VARCHAR2(100));
INSERT INTO TAB1 (COL1, COL2) VALUES (1, 'one');
INSERT INTO TAB1 (COL1, COL2) VALUES (2, 'two');
INSERT INTO TAB1 (COL1, COL2) VALUES (3, 'three');
INSERT INTO TAB1 (COL1, COL2) VALUES (4, 'four');
CREATE TABLE TAB2 (COL1 NUMBER, COL2 VARCHAR2(100));
INSERT INTO TAB2 (COL1, COL2) VALUES (2, 'two');
INSERT INTO TAB2 (COL1, COL2) VALUES (3, 'three');
COMMIT;
Answer the questions:
1. How try to update TAB1 with unlimiting waiting?
2. How try to update TAB1 table with waiting 1 min?
3. How try to update TAB1 table without waiting and with message on screen if
updating not available?
4. How lock only rows in TAB1 where TAB1.COL1 = TAB2.COL1? TAB2 must be
available for update. TAB1 with COL1 <> TAB2.COL1 must be available for update.
5. How to save FOR UPDATE actions for TAB1 in database?
6. How do not save FOR UPDATE actions for TAB1 in database?
Solution:
--1
SELECT COL1, COL2 FROM TAB1
FOR UPDATE;
--2
SELECT COL1, COL2 FROM TAB1
FOR UPDATE wait 60;
--3
SELECT COL1, COL2 FROM TAB1
FOR UPDATE nowait;
--4
SELECT t1.COL1,
t1.COL2
FROM TAB1 t1
JOIN TAB2 t2
ON t1.COL1 = t2.COL1
FOR UPDATE of t1.COL1;
--5
SELECT COL1, COL2 FROM TAB1
FOR UPDATE;
COMMIT;
--6
SELECT COL1, COL2 FROM TAB1
FOR UPDATE;
ROLLBACK;
Task 13.3.4. Perform Insert, Update and Delete operations. UPDATE.
Do the next operations:
DROP TABLE TAB1;
DROP TABLE TAB2;
CREATE TABLE TAB1 (COL1 NUMBER, COL2 VARCHAR2(100));
INSERT INTO TAB1 (COL1, COL2) VALUES (1, 'one');
INSERT INTO TAB1 (COL1, COL2) VALUES (2, 'two');
CREATE TABLE TAB2 (COL1 NUMBER, COL2 VARCHAR2(100));
INSERT INTO TAB2 (COL1, COL2) VALUES (NULL, 'two');
INSERT INTO TAB2 (COL1, COL2) VALUES (4, 'four');
COMMIT;
Then:
1. Update rows in TAB1.COL1 by value 111 only if TAB1.COL1 = 1 and
TAB1.COL2 = 'one'. Two ways.
2. Update rows in TAB2.COL1 by value from TAB1.COL1 if TAB2.COL2 = TAB1.COL2.
3. Update all rows in TAB2 for COL1 and COL2 simultaneously by values 5, 'five'.
4. After action №3 update simultaneously TAB1.COL2, TAB1.COL1 if TAB1.COL2 = 'one'
by values from TAB2.COL2, TAB2.COL1 after DISTINCT.
5. Update TAB1 by NULL values.
Solution:
--1
UPDATE TAB1
SET COL1 = 111
WHERE COL1 = 1 AND
COL2 = 'one';
UPDATE (SELECT COL1
FROM TAB1
WHERE COL1 = 1 AND
COL2 = 'one')
SET COL1 = 111;
--2
UPDATE TAB2 t2
SET t2.COL1 = (SELECT t1.COL1
FROM TAB1 t1
WHERE t1.COL2 = t2.COL2)
WHERE t2.COL2 = (SELECT t1.COL2
FROM TAB1 t1
WHERE t1.COL2 = t2.COL2);
--3
UPDATE TAB2
SET COL1 = 5,
COL2 = 'five';
--4
UPDATE TAB1
SET (COL2, COL1) = (SELECT DISTINCT COL2,
COL1
FROM TAB2)
WHERE COL2 = 'one';
--5
UPDATE TAB1
SET COL1 = NULL,
COL2 = NULL;
Task 13.3.5. Perform Insert, Update and Delete operations. DELETE.
Do the next operations:
DROP TABLE TAB1;
DROP TABLE TAB2;
CREATE TABLE TAB1 (COL1 NUMBER, COL2 VARCHAR2(100));
INSERT INTO TAB1 (COL1, COL2) VALUES (1, 'one');
INSERT INTO TAB1 (COL1, COL2) VALUES (2, 'two');
INSERT INTO TAB1 (COL1, COL2) VALUES (3, 'three');
INSERT INTO TAB1 (COL1, COL2) VALUES (4, 'four');
CREATE TABLE TAB2 (COL1 NUMBER, COL2 VARCHAR2(100));
INSERT INTO TAB2 (COL1, COL2) VALUES (2, 'two');
INSERT INTO TAB2 (COL1, COL2) VALUES (3, 'three');
INSERT INTO TAB2 (COL1, COL2) VALUES (4, NULL);
COMMIT;
Then:
1. Delete rows from TAB1 if TAB1.COL2 equal 'one'.
2. Delete rows from TAB1 if TAB1.COL2 not equal values from subquery to TAB2.COL2.
3. Delete rows from TAB2 if COL1 equal 4 and COL2 equal NULL.
4. Clean TAB2. Two ways (explain difference). What will happen to unused indexes?
Solution:
--1
DELETE FROM TAB1
WHERE COL2 = 'one';
--2
DELETE TAB1
WHERE COL2 NOT IN (SELECT COALESCE(COL2, 'zzz') FROM TAB2);
--3
DELETE TAB2
WHERE COL1 = 4 AND
COL2 IS NULL;
--4
--DELETE generates UNDO data. It allows use ROLLBACK instead COMMIT to back data
DELETE TAB2; COMMIT;
--TRUNCATE is faster because does not generate UNDO data. Can`t rollback data.
TRUNCATE TABLE TAB2;
--TRUNCATE makes unusable indexes usable again. DELETE - not makes.
Task 13.4.1. Performing multi table Inserts
Do next operations:
DROP TABLE TAB1;
DROP TABLE TAB2;
DROP TABLE TAB3;
CREATE TABLE TAB1 (COL1 NUMBER,
COL2 VARCHAR2(100),
COL3 DATE);
INSERT INTO TAB1 (COL1, COL2, COL3) VALUES (1, 'AA', SYSDATE);
INSERT INTO TAB1 (COL1, COL2, COL3) VALUES (2, 'BB', SYSDATE);
INSERT INTO TAB1 (COL1, COL2, COL3) VALUES (3, 'BB', SYSDATE);
COMMIT;
CREATE TABLE TAB2 (COLN NUMBER,
COLV VARCHAR2(100));
CREATE TABLE TAB3 (COLN NUMBER,
COLD DATE);
Then do next operations:
1. Insert data from TAB1 to TAB2 and TAB3 at the same time.
2. Insert data from TAB1 to TAB2(if TAB1.COL1 > 2) and TAB3(if TAB1.COL2 = 'BB').
Which rows will be inserted in TAB2 and in TAB3?
3. Insert data from TAB1 to TAB2(if TAB1.COL1 > 2) and TAB3(if TAB1.COL2 = 'BB'),
but if 'if TAB1.COL1 > 2' will be met, then skip insert to TAB3.
Which rows will be inserted in TAB2 and in TAB3?
4. Write restrictions on multitable INSERT.
Solution:
--1
INSERT ALL
INTO TAB2 (COLN, COLV) VALUES (COL1, COL2)
INTO TAB3 (COLN, COLD) VALUES (COL1, COL3)
SELECT COL1, COL2, COL3
FROM TAB1;
--2
INSERT ALL
WHEN COL1 > 2 THEN
INTO TAB2 (COLN, COLV) VALUES (COL1, COL2)
WHEN COL2 = 'BB' THEN
INTO TAB3 (COLN, COLD) VALUES (COL1, COL3)
SELECT COL1, COL2, COL3
FROM TAB1;
"TAB2"
COLN COLV
3 BB
"TAB3"
COLN COLD
2 15.05.2020 17:43:00
3 15.05.2020 17:43:00
--3
INSERT FIRST
WHEN COL1 > 2 THEN
INTO TAB2 (COLN, COLV) VALUES (COL1, COL2)
WHEN COL2 = 'BB' THEN
INTO TAB3 (COLN, COLD) VALUES (COL1, COL3)
SELECT COL1, COL2, COL3
FROM TAB1;
"TAB2"
COLN COLV
3 BB
"TAB3"
COLN COLD
2 15.05.2020 17:43:00
--4
- Multitable INSERT available only for tables;
- Can`t use Multitable INSERT on remote table (table in another database);
- Can`t specify a table collection expression;
- First reference to SEQUENCE.NEXTVAL produce single value for all SQL statement.
Task 13.5.1. Performing Merge statements
Do next operations:
DROP TABLE WAREHOUSE1;
DROP TABLE WAREHOUSE2;
DROP TABLE ALL_WRH;
CREATE TABLE WAREHOUSE1(ITEM VARCHAR2(100), QUANTITY NUMBER);
INSERT INTO WAREHOUSE1 (ITEM, QUANTITY) VALUES ('ITEM1',10);
INSERT INTO WAREHOUSE1 (ITEM, QUANTITY) VALUES ('ITEM2',20);
INSERT INTO WAREHOUSE1 (ITEM, QUANTITY) VALUES ('ITEM3',30);
CREATE TABLE WAREHOUSE2(ITEM VARCHAR2(100), QUANTITY NUMBER);
INSERT INTO WAREHOUSE2 (ITEM, QUANTITY) VALUES ('ITEM2',20);
INSERT INTO WAREHOUSE2 (ITEM, QUANTITY) VALUES ('ITEM3',30);
INSERT INTO WAREHOUSE2 (ITEM, QUANTITY) VALUES ('ITEM4',40);
CREATE TABLE ALL_WRH (ITEM VARCHAR2(100), QUANTITY NUMBER);
Then do next operations:
1. Merge data from WAREHOUSE1 into ALL_WRH:
when ALL_WRH.ITEM = WAREHOUSE1.ITEM then UPDATE ALL_WRH,
when ALL_WRH.ITEM <> WAREHOUSE1.ITEM then INSERT rows into ALL_WRH.
Write which data will be in ALL_WRH.
2. Merge data from WAREHOUSE2 into ALL_WRH:
when ALL_WRH.ITEM = WAREHOUSE2.ITEM then DELETE this rows from ALL_WRH,
when ALL_WRH.ITEM <> WAREHOUSE2.ITEM then INSERT rows into ALL_WRH.
Write which data will be in ALL_WRH.
Solution:
--1
--'ITEM1', 10
--'ITEM2', 20
--'ITEM3', 30
MERGE INTO ALL_WRH DEST
USING (SELECT ITEM,
QUANTITY
FROM WAREHOUSE1) SRC
ON (DEST.ITEM = SRC.ITEM)
WHEN MATCHED THEN
UPDATE SET DEST.QUANTITY = SRC.QUANTITY
WHEN NOT MATCHED THEN
INSERT (DEST.ITEM,
DEST.QUANTITY) VALUES (SRC.ITEM,
SRC.QUANTITY);
COMMIT;
--2
--'ITEM1', 10
--'ITEM4', 40
MERGE INTO ALL_WRH DEST
USING (SELECT ITEM,
QUANTITY
FROM WAREHOUSE2) SRC
ON (DEST.ITEM = SRC.ITEM)
WHEN MATCHED THEN
UPDATE SET DEST.QUANTITY = SRC.QUANTITY
DELETE WHERE DEST.ITEM = SRC.ITEM
WHEN NOT MATCHED THEN
INSERT (DEST.ITEM,
DEST.QUANTITY) VALUES (SRC.ITEM,
SRC.QUANTITY);
COMMIT;
CHAPTER 14. "Use DDL to manage tables and their relationships"
14.1. Describing and Working with Tables
14.2. Describing and Working with Columns and Data Types
14.3. Creating tables
14.4. Dropping columns and setting column UNUSED
14.5. Truncating tables
14.6. Creating and using Temporary Tables
14.7. Creating and using external tables
14.8. Managing Constraints
Task 14.1.1.-14.2.1 Naming rules
Write naming rules.
Solution:
1. Begin with letter,
2. Name long <= 30 symbols (in Oracle 12c Release 2 <= 128).
3. Contain only: A-Z; a-z; 0-9; _; $; #.
4. One user can`t create objects with same names.
5. For name not allowed using Oracle`s server-reserved words.
6. In double quotation marks can write any name, but always must use this name
with "".
Task 14.2.2. Describing and Working with Columns and Data Types
Explain data types:
1. LONG;
2. CLOB;
3. NCLOB;
4. RAW(size);
5. LONG RAW;
6. BLOB;
7. BFILE;
8. ROWID;
9. TIMESTAMP;
10. INTERVAL YEAR TO MONTH;
11. INTERVAL DAY TO SECOND;
Solution:
--1 LONG
Variable-length character data (up to 2 gb). A LONG column is not copied when
a table is created using a subquery. A LONG column cannot be included in a
GROUP BY or an ORDER BY clause. Only one LONG column can be used per table.
Constraints can`t be defined on a long column (but CHECK constraint allowed).
--2 CLOB
A character large object containing single-byte or multibyte characters. Maximum
size is (4gb - 1) * (DB_BLOCK_SIZE). Stores database character set data.
--3 NCLOB
A character large object containing Unicode characters. Both fixed-width and
variable-width character sets are supported, both using the database national
character set. Maximum size is (4 gb - 1) * (database block size); used only for
storing Unicode data.
--4 RAW (size)
Raw binary data of length size bytes. You must specify size for a RAW value.
Maximum size is: 32767 bytes if MAX_SQL_STRING_SIZE = EXTENDED, 4000 bytes if
MAX_SQL_STRING_SIZE = LEGACY
--5 LONG RAW
Raw binary data of variable length up to 2 gb.
--6 BLOB
A binary large object. Maximum size is (4 gb - 1) * (DB_BLOCK_SIZE initialization
parameter (8TB to 128 TB)).
--7 BFILE
Binary data stored in an external file (up to 4gb).
--8 ROWID
Base 64 string representing the unique address of a row in its table. This data
type is primarily for values returned by the ROWID pseudocolumn. Recommended to
use UROWID instead of ROWID.
--9 TIMESTAMP
Enables storage of time as a date with fractional seconds. It stores the year,
month, day, hour, minute, second and the fractional seconds value.
Variations: TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCALTIMEZONE.
--10 INTERVAL YEAR TO MONTH
Enables storage of time as an interval of years and months; used to represent the
difference between two datetime values in which the only significant portions are
the year and month.
--11 INTERVAL DAY TO SECOND
Enables storage of time as an interval of days, hours, minutes, and seconds; used
to represent the precise difference between two datetime values.
Task 14.3.1. Creating tables. CREATE + Constraints
1. Create table TAB with column 'COL1' NUMBER, 'COL2' NUMBER.
COL1 can`t have NULL values and will use for relation in table TAB1 (if delete
write null).
COL2 can have NULL values and will use for relation in table TAB2 (if delete
write condtiton other then null).
2. Create table TAB1 with constraints using column level syntax. For each column
set default value = 10. One column can`t have NULL values.
3. Create table TAB2 with constraints using table level syntax. For each column
set default value = 10. One column can`t have NULL values. Primary key must have
two columns. In tables must be two columns (except primary key), where not allow
duplicates for this columns together.
Solution:
--1
CREATE TABLE TAB(COL1 NUMBER PRIMARY KEY,
COL2 NUMBER UNIQUE);
--2
CREATE TABLE TAB1
(COL1 NUMBER DEFAULT 10 CONSTRAINT TAB1_C_PK PRIMARY KEY,
COL2 NUMBER DEFAULT 10 CONSTRAINT TAB1_C_FK REFERENCES TAB(COL1)
ON DELETE SET NULL,
COL3 NUMBER DEFAULT 10 CONSTRAINT TAB1_C_NN NOT NULL,
COL4 NUMBER DEFAULT 10 CONSTRAINT TAB1_C_UN UNIQUE,
COL5 NUMBER DEFAULT 10 CONSTRAINT TAB1_C_CH CHECK (COL5 BETWEEN 10 AND 20));
--3
CREATE TABLE TAB2
(COL1 NUMBER DEFAULT 10,
COL2 NUMBER DEFAULT 10,
COL3 NUMBER DEFAULT 10,
COL4 NUMBER DEFAULT 10,
COL5 NUMBER DEFAULT 10,
CONSTRAINT TAB2_C_PK PRIMARY KEY (COL1, COL2),
CONSTRAINT TAB2_C_FK FOREIGN KEY (COL2) REFERENCES TAB(COL2)
ON DELETE CASCADE,
CONSTRAINT TAB2_C_NN CHECK (COL3 IS NOT NULL),
CONSTRAINT TAB2_C_U1 UNIQUE (COL4, COL5),
CONSTRAINT TAB2_C_CH CHECK (COL5 BETWEEN 10 AND 20));
Task 14.3.2. Creating tables. FLASHBACK.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 NUMBER, COL2 NUMBER);
INSERT INTO TAB1 (COL1, COL2) VALUES(10, 111);
INSERT INTO TAB1 (COL1, COL2) VALUES(20, 222);
INSERT INTO TAB1 (COL1, COL2) VALUES(20, 333);
COMMIT;
Then do next operations:
1. Which object are stored info about dropped tables.
2. How to remove dropped tables?
3. What mean SCN.
4. Remove table TAB1 and then restore TAB1.
5. Update COL2 with value = 444, if COL1 = 20. Show history of updating for COL2,
where can see old values and new values.
Solution:
--1
SELECT *
FROM RECYCLEBIN;
--2
PURGE RECYCLEBIN;
--3
The SCN (system change number) is an integer value associated with each change
to the database. Is is unique incremental number in the database. Every time you
commit a transaction, a new SCN is recorded.
--4
DROP TABLE TAB1;
FLASHBACK TABLE TAB1
TO BEFORE DROP;
--5
UPDATE TAB1
SET COL2 = 444
WHERE COL1 = 20;
COMMIT; --without commit we can`t see VERSIONS_STARTTIME and VERSIONS_ENDTIME
SELECT VERSIONS_STARTTIME,
VERSIONS_ENDTIME,
COL2
FROM TAB1
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE COL1 = 20;
Task 14.4.1. Dropping columns and setting column UNUSED. ALTER.
Do the next operations:
DROP TABLE TAB;
CREATE TABLE TAB (COL1 NUMBER(5,2), COL2 VARCHAR2(4));
INSERT INTO TAB (COL1, COL2) VALUES (110.25, 'one');
Then do next operations:
1. Add column COL4 DATE with default value = SYSDATE and not null.
2. Change name COL4 on COL3.
3. Set COL2 default 'zzz'.
4. Set COL3 without duplicates (null allow). Two ways.
5. For COL2 remake to CHAR type with minimum long as possible.
6. For COL1 remake type for can insert 1111.999.
7. For COL3 make not use (two ways). Which object in dictionary contain info
about these not used columns and table, list columns.
8. Add in table again column with name COL3 DATE.
9. Delete not using columns.
10. Delete COL2, COL3 from table.
11. Disable for table allow change data.
12. Enable for table allow change data.
13. Change name for table TAB to TABTAB. Two ways.
14. Remove table TABTAB from database. Two ways. Explain it.
Solution:
--1
ALTER TABLE TAB ADD COL4 DATE
DEFAULT SYSDATE
NOT NULL;
--2
ALTER TABLE TAB RENAME COLUMN COL4 TO COL3;
--3
ALTER TABLE TAB MODIFY COL2 DEFAULT 'zzz';
--4
ALTER TABLE TAB ADD CONSTRAINT CON_C1 UNIQUE (COL3);
ALTER TABLE TAB MODIFY COL3 UNIQUE;
--5
ALTER TABLE TAB MODIFY COL2 CHAR(3);
--6
ALTER TABLE TAB MODIFY COL1 NUMBER(7, 3);
--7
ALTER TABLE TAB SET UNUSED (COL3) ONLINE;
--ALTER TABLE TAB SET UNUSED COLUMN COL3 ONLINE;
SELECT TABLE_NAME, COUNT
FROM USER_UNUSED_COL_TABS
WHERE TABLE_NAME = 'TAB';
--8
ALTER TABLE TAB ADD COL3 DATE;
--9
ALTER TABLE TAB DROP UNUSED COLUMNS;
--10
ALTER TABLE TAB DROP (COL2, COL3);
--11
ALTER TABLE TAB READ ONLY;
--12
ALTER TABLE TAB READ WRITE;
--13
RENAME TAB TO TABTAB;
--ALTER TABLE TAB RENAME TO TABTAB;
--14
DROP TABLE TABTAB; --drop in recycle bin, can be recovered
--DROP TABLE TABTAB PURGE; --drop forever
Task 14.6.1. Creating and using Temporary Tables. GLOBAL TEMPORARY TABLE
1. Create global table. Two options. Explain it.
2. Suppose that we inserted 2 rows in tables from point 1 in first session. What
are tables and data we can see from second session?
Solution:
--1
DROP TEMP1;
DROP TEMP2;
--global temporary tables save data only for your session. When session ended
--table will be clean.
CREATE GLOBAL TEMPORARY TABLE TEMP1 (COL1 NUMBER, COL2 VARCHAR2(100))
ON COMMIT DELETE ROWS; --this mean that after COMMIT rows will be removed
CREATE GLOBAL TEMPORARY TABLE TEMP2 (COL1 NUMBER, COL2 VARCHAR2(100))
ON COMMIT PRESERVE ROWS; --after COMMIT rows will be saved in session
--2
Can see empty tables.
Task 14.7.1. Creating and using external tables. SQL LOADER.
Do next operations:
DROP TABLE FOR_EXT_TAB;
CREATE TABLE FOR_EXT_TAB(COL1 NUMBER, COL2 VARCHAR2(500), COL3 DATE);
Then do next operations:
1. Load data from external table (one row in COL3 must have NULL) to TAB1.
Stop loading if at least 1 row failed. How to disable COMMIT at the SQL LOADER?
Solution:
--1
Create in directory (for example D:\) 'my_table.csv' file with three columns
delimited by ";".
With data:
1;one;01.01.2000
2;two;
3;three;03.01.2000
--2
--in CTL file you can write comments
Create 'my_table.ctl' file in the same directory with 'my_table.csv'.
Paste next text into 'my_table.ctl':
LOAD DATA
--CHARACTERSET UTF8 --if .csv have Unicode
INFILE 'D:\my_table.csv'
APPEND INTO TABLE FOR_EXT_TAB
FIELDS TERMINATED BY ';' TRAILING NULLCOLS
(
COL1,
COL2 CHAR(500), --SQLLOADER loads string with default length <= 255.
--For string > 255 write size, for example CHAR(500).
COL3 DATE "DD.MM.YYYY"
--for TIMESTAMP=2023-01-01 00:00:00.000 use:TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF3"
)
--3
--we can`t disable COMMIT at the SQL LOADER
Execute next command in CMD for Windows with Username hr/hr@orclpdb:
sqlldr userid=hr/hr@orclpdb control=D:\my_table.ctl log=D:\my_table.log errors=0
Task 14.7.2. Creating and using external tables. EXTERNAL TABLES.
Do next operations:
DROP TABLE FOR_EXT_TAB;
CREATE TABLE FOR_EXT_TAB(COL1 NUMBER, COL2 VARCHAR2(100));
INSERT INTO FOR_EXT_TAB(COL1, COL2) VALUES (111, 'AAA');
INSERT INTO FOR_EXT_TAB(COL1, COL2) VALUES (222, 'BBB');
COMMIT;
Then do next operations:
1. Explain what the external table means. How to create external table?
2. Access to external table with oracle_loader.
3. Access to external table with oracle_datapump.
Solution:
--1
External table is read only (DML, indexes not allowed), which metadata is stored
in the database.
Create 'ext_tab.csv' with two columns delimited by ";" in directory 'D:\external'.
Connect to database sys as sysdba.
Then do:
Alter session set container=orclpdb; --or another DB
grant create any DIRECTORY to my_schema;
CREATE OR REPLACE DIRECTORY MY_DIR AS 'D:\external';
--2
CREATE TABLE TAB_LOADER (COL1 NUMBER,
COL2 VARCHAR2(100))
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY MY_DIR
ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ';')
LOCATION ('ext_tab.csv')
)
REJECT LIMIT UNLIMITED; --but DEFAULT = 0
SELECT * FROM TAB_LOADER;
--3
--3.1.
CREATE TABLE TAB_PUMP (COL1,
COL2) --table will be created with data from
--ext_tab_pump.dmp
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY MY_DIR
LOCATION ('ext_tab_pump.dmp') --this table will be store data from
--FOR_EXT_TAB
)
AS SELECT COL1,
COL2
FROM FOR_EXT_TAB;
SELECT * FROM TAB_PUMP;
--3.2. create table for read data from created table 'ext_tab_pump.dmp'
CREATE TABLE TAB_PUMP_READ (COL1 NUMBER,
COL2 VARCHAR2(100))
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY MY_DIR
LOCATION ('ext_tab_pump.dmp')
);
SELECT * FROM TAB_PUMP_READ;
Task 14.8.1. Managing Constraints. USER_CONSTRAINTS.
Do next operations.
DROP TABLE TAB0;
DROP TABLE TAB1;
CREATE TABLE TAB0(COL1 NUMBER PRIMARY KEY,
COL2 NUMBER UNIQUE);
CREATE TABLE TAB1
(COL1 NUMBER DEFAULT 10 CONSTRAINT TAB1_C_PK PRIMARY KEY,
COL2 NUMBER DEFAULT 10 CONSTRAINT TAB1_C_FK REFERENCES TAB0(COL1)
ON DELETE CASCADE,
COL3 NUMBER DEFAULT 10 CONSTRAINT TAB1_C_NN NOT NULL,
COL4 NUMBER DEFAULT 10 CONSTRAINT TAB1_C_UN UNIQUE,
COL5 NUMBER DEFAULT 10 CONSTRAINT TAB1_C_CH CHECK (COL5 BETWEEN 10 AND 20));
Then do next operations:
1. Show name of table, name of column and all columns about constraints for TAB1.
Solution:
SELECT ucc.TABLE_NAME,
ucc.COLUMN_NAME,
uc.*
FROM USER_CONSTRAINTS uc
LEFT JOIN USER_CONS_COLUMNS ucc
ON uc.CONSTRAINT_NAME = ucc.CONSTRAINT_NAME
WHERE uc.TABLE_NAME = 'TAB1'
ORDER BY uc.CONSTRAINT_NAME
Task 14.8.2. Managing Constraints. ALTER CONSTRAINTS.
Do next operations:
DROP TABLE TAB1;
DROP TABLE TAB2;
CREATE TABLE TAB1(COL1 NUMBER PRIMARY KEY, COL2 NUMBER);
INSERT INTO TAB1(COL1, COL2) VALUES(1, 10);
CREATE TABLE TAB2(COL1 NUMBER REFERENCES TAB1(COL1), COL2 VARCHAR2(100));
INSERT INTO TAB2(COL1, COL2) VALUES(1, 'one');
COMMIT;
Then do next operations:
1. Drop PRIMARY KEY from TAB1.
2. Make TAB1.COL1 as PRIMARY KEY. Two syntax.
3. Make TAB2.COL1 as FOREIGN KEY with relation to TAB1.COL1. Two syntax.
4. Disable primary key in TAB1.COL1 and foreign key in TAB2.COL1. Two syntax.
Enable primary key TAB1.COL1 and foreign key in TAB2.COL1.
5. Remove column TAB1.COL1.
6. Create NOT NULL constraint with name 'CONS_TAB2_NN' for TAB2.COL2. Then rename
'CONS_TAB2_NN' to 'C_T2_NN'.
Solution:
--1
ALTER TABLE TAB1 DROP PRIMARY KEY CASCADE;
--2
ALTER TABLE TAB1 ADD CONSTRAINT TAB1_PK PRIMARY KEY (COL1);
--ALTER TABLE TAB1 MODIFY COL1 PRIMARY KEY;
--3
ALTER TABLE TAB2 ADD CONSTRAINT TAB2_FK FOREIGN KEY (COL1)
REFERENCES TAB1(COL1);
--ALTER TABLE TAB2 MODIFY COL1 REFERENCES TAB1(COL1);
--4
ALTER TABLE TAB1 DISABLE PRIMARY KEY CASCADE;
--ALTER TABLE TAB1 DISABLE CONSTRAINT TAB1_PK CASCADE;
ALTER TABLE TAB1 ENABLE PRIMARY KEY;
--ALTER TABLE TAB1 ENABLE CONSTRAINT TAB1_PK;
ALTER TABLE TAB2 ENABLE CONSTRAINT TAB2_FK;
--5
ALTER TABLE TAB1 DROP COLUMN COL1 CASCADE CONSTRAINTS;
--6
ALTER TABLE TAB2 ADD CONSTRAINT CONS_TAB2_NN CHECK(COL2 IS NOT NULL);
ALTER TABLE TAB2 RENAME CONSTRAINT CONS_TAB2_NN TO C_T2_NN;
Task 14.8.3. Managing Constraints. DEFERRABLE CONSTRAINTS.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1(COL1 NUMBER, COL2 NUMBER);
Then do next operations:
1. For COL1, COL2 create different deferrable constraints. Explain it.
2. Change behavior deferrable constraint for session.
Solution:
--1
ALTER TABLE TAB1
ADD CONSTRAINT C_CH_COL1 CHECK (COL1 > 6)
DEFERRABLE INITIALLY DEFERRED; --will be violated when you try to commit
ALTER TABLE TAB1
ADD CONSTRAINT C_CH_COL2 CHECK (COL2 < 5)
DEFERRABLE INITIALLY IMMEDIATE; --will be violated immediately
--2
SET CONSTRAINT C_CH_COL1 IMMEDIATE;
SET CONSTRAINT C_CH_COL2 DEFERRED;
CHAPTER 15. "Controlling User Access"
15.1. Differentiating system privileges from object privileges.
15.2. Granting privileges on tables.
15.3. Distinguishing between granting privileges and roles.
Task 15.1.1. - 15.3.1.
1. What mean system privileges, object privileges for users.
2. Show list of users in database.
3. Show all the system privileges available for the version release.
4. Create new user "U1" and "U2" with password 123 and connect for them.
5. User must be gain access to create: table, sequence, view, synonym.
6. User must be gain access to SELECT for TAB1.
7. User must be gain access insert values only into TAB1.COL2.
8. Create role "my_role" which must be gain access to all privileges TAB1. Attach
this role to "U1". Explain which privileges available in this role.
9. Grant SELECT from TAB1 to all users in the database.
10. Provide CREATE TABLE, then SELECT and DELETE for TAB1 to user U2, who can
assign this privilege to another user.
11. Get back SELECT and DELETE for TAB1 from user U2.
12. Provide SELECT for any tables to U2;
13. Show what privileges have U2 in session.
14. Change password for user U2 from 123 to 123456.
15. List dictionary`s views with data about privileges.
16. Create table U2.MY_TAB (COL1 NUMBER), then remove user U2.
17. How to remove authentification block on user?
18. After point 17 how to make user change password while logging?
19. How to make password fixed forever, without requirement change password after
period?
Solution:
--1
System privileges for gain access to the database and perfoming a particular
action within the database.
Object privileges for manipulate the content of the objects in the database.
--2
SELECT * FROM ALL_USERS
--3
SELECT * FROM SYSTEM_PRIVILEGE_MAP
--4
login sys as sysdba;
alter session set container=orclpdb;
CREATE USER U1 IDENTIFIED BY 123;
GRANT CREATE SESSION TO U1;
CREATE USER U2 IDENTIFIED BY 123;
GRANT CREATE SESSION TO U2;
--5
GRANT CREATE TABLE TO U1;
GRANT UNLIMITED TABLESPACE TO U1;
GRANT CREATE SEQUENCE,
CREATE VIEW,
CREATE SYNONYM TO U1;
--6
GRANT SELECT ON HR.TAB1 TO U1;
--7
GRANT INSERT(COL2) ON HR.TAB1 TO U1;
--8
--in this role will be available OBJECT privs, for SYSTEM privs need add new role
CREATE ROLE MY_ROLE;
GRANT ALL ON HR.TAB1
TO MY_ROLE;
GRANT MY_ROLE TO U1;
--9
GRANT SELECT ON HR.TAB1 TO PUBLIC;
--10
GRANT CREATE TABLE TO U2;
GRANT SELECT, DELETE ON HR.TAB1
TO U2
WITH GRANT OPTION;
--11
REVOKE SELECT, DELETE ON HR.TAB1
FROM U2;
--12
GRANT SELECT ANY TABLE TO U2;
--13
SELECT * FROM SESSION_PRIVS;
--14
ALTER USER U2 IDENTIFIED BY 123456;
--15
ROLE_SYS_PRIVS - system privileges granted to roles;
ROLE_TAB_PRIVS - table privileges granted to roles;
USER_ROLE_PRIVS - roles accessible by the user;
USER_SYS_PRIVS - system privileges granted to the user;
USER_TAB_PRIVS_MADE - object privileges granted on the user`s objects;
USER_TAB_PRIVS_RECD - object privileges granted to the user;
USER_COL_PRIVS_MADE - object privs granted on the columns of the user`s objects;
USER_COL_PRIVS_RECD - object privileges granted to the user on specific columns.
--16
CREATE TABLE MY_TAB(COL1 NUMBER);
DROP USER U2 CASCADE;
--17
ALTER USER U1 IDENTIFIED BY "123" ACCOUNT UNLOCK;
--18
ALTER USER U1 PASSWORD EXPIRE;
--19
--login by SYS AS SYSDBA
ALTER SESSION SET CONTAINER=ORCLPDB;
SELECT PROFILE FROM DBA_USERS WHERE USERNAME = 'HR';--for example profile=DEFAULT
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
--assign current password again to remove message "Password will expire ..."
ALTER USER HR IDENTIFIED BY "CURRENT_PASSWORD";
CHAPTER 16. "Managing Data in Different Time Zones"
16.1. Working with CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP.
16.2. Working with INTERVAL data types.
Task 16.1.1. Working with CURRENT_DATE, CURRENT_TIMESTAMP,and LOCALTIMESTAMP.
1. Create table with three columns: first not contain time zone, second contain
time zone, third contain local time zone.
2. Show the current date and time for user session (timestamp with time zone).
Show the current date and time for user session (timestamp).
3. Show system date at the timestamp type.
4. Select data from table with zones names. Explain zone names.
5. Show the database time zone.
6. Show the session time zone.
7. Show current date for user session.
8. Change session`s time zone to 'AMERICA/DETROIT'.
9. Show offset of timezone 'EUROPE/AMSTERDAM'.
10. Convert 25.11.2000 10:10:05 to timestamp. With and without fractional seconds.
11. Convert 25.11.2000 10:10:05 to timestamp with time zone 'EUROPE/AMSTERDAM'.
12. Which values accept fractional seconds precision? Default value?
Solution:
--1
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 TIMESTAMP,
COL2 TIMESTAMP WITH TIME ZONE,
COL3 TIMESTAMP WITH LOCAL TIME ZONE);
--2
SELECT CURRENT_TIMESTAMP
FROM DUAL;
SELECT LOCALTIMESTAMP
FROM DUAL;
--3
SELECT SYSTIMESTAMP
FROM DUAL;
--4
SELECT *
FROM V$TIMEZONE_NAMES;
--5
SELECT DBTIMEZONE
FROM DUAL;
--6
SELECT SESSIONTIMEZONE
FROM DUAL;
--7
SELECT CURRENT_DATE
FROM DUAL;
--8
ALTER SESSION SET TIME_ZONE = 'AMERICA/DETROIT';
--9
SELECT TZ_OFFSET('EUROPE/AMSTERDAM')
FROM DUAL;
--10
SELECT TO_TIMESTAMP('25.11.2000 10:10:05','DD.MM.YYYY HH:MI:SS')
FROM DUAL;
SELECT TO_TIMESTAMP('25-Nov-00 10:10:05.000000','DD-Mon-RR HH24:MI:SS.FF')
FROM DUAL
--11
SELECT FROM_TZ(TIMESTAMP '2000-11-25 10:10:05', 'EUROPE/AMSTERDAM')
FROM DUAL;
--12
0-9. Default = 6.
Task 16.2.1. Working with INTERVAL data types. Interval.
1. Show interval for 1 year and 2 months. Write result.
2. Show interval for 1 year and 12 months. Write result.
3. Show interval for 100 years and 120 months. Write result.
4. Show interval for 121 months (using only month). Write result.
5. Show interval for -100 years (using only year). Write result.
6. Show interval for 30 days and 20 hours and 20 min. Write result.
7. Show interval for 333 days and 10 hours and 10 sec. Write result.
8. Show interval for 240 hours (using only hour). Write result.
9. Show interval for 1200 minutes (using only minutes). Write result.
10. Show interval for 100 days (using only days). Write result.
11. Show interval for 606 seconds (using only seconds). Write result.
Solution:
SELECT
/* 1) +01-02 */ INTERVAL '1-2' YEAR TO MONTH,
/* 2) +02-00 */ INTERVAL '2-0' YEAR TO MONTH,
/* 3) +110-00 */ INTERVAL '110-0' YEAR(3) TO MONTH,
/* 4) +10-01 */ INTERVAL '121' MONTH,
/* 5) -100-00 */ INTERVAL '-100' YEAR(3),
/* 6) +30 20:20:00.000000 */ INTERVAL '30 20:20:00' DAY TO SECOND,
/* 7) +333 10:00:10.000000 */ INTERVAL '333 10:00:10' DAY(3) TO SECOND,
/* 8) +10 00:00:00.000000 */ INTERVAL '240' HOUR,
/* 9) +00 20:00:00.000000 */ INTERVAL '1200' MINUTE,
/* 10) +100 00:00:00.000000 */ INTERVAL '100' DAY(3),
/* 11) +00 00:10:06.000000 */ INTERVAL '606' SECOND
FROM DUAL;
Task 16.2.2. Working with INTERVAL data types. Interval. Add interval, to interval,
extract.
Do next operations:
DROP TABLE TAB1;
CREATE TABLE TAB1 (COL1 DATE);
INSERT INTO TAB1
VALUES(TO_DATE('01.01.2000 10:30:00', 'DD.MM.YYYY HH24:MI:SS'));
COMMIT;
Then do next operations:
1. To COL1 add 1 year and 10 months. Two ways.
2. To COL1 add 100 years and 120 months. Two ways.
3. To COL1 add 15 days and 12 hours. Two ways.
4. To COL1 add 120 days and 600 seconds. Two ways.
5. Select only year.
6. Select only month.
7. Select only day.
Solution:
SELECT
/* 1.1. */ COL1 + INTERVAL '1-10' YEAR TO MONTH,
/* 1.2. */ COL1 + TO_YMINTERVAL('1-10'),
/* 2.1. */ COL1 + INTERVAL '100' YEAR(3) + INTERVAL '120' MONTH,
/* 2.2. */ COL1 + TO_YMINTERVAL('100-00') + INTERVAL '120' MONTH,
/* 3.1. */ COL1 + INTERVAL '15 12:00:00' DAY TO SECOND,
/* 3.2. */ COL1 + TO_DSINTERVAL('15 12:00:00'),
/* 4.1. */ COL1 + INTERVAL '120' DAY(3) + INTERVAL '600' SECOND,
/* 4.2. */ COL1 + TO_DSINTERVAL('120 00:00:00') + INTERVAL '600' SECOND,
/* 5 */ EXTRACT(YEAR FROM COL1),
/* 6 */ EXTRACT(MONTH FROM COL1),
/* 7 */ EXTRACT(DAY FROM COL1)
FROM TAB1;