OCA Oracle Database
SQL Expert
Exam Guide
(Exam 1Z0-047)
-
SQL Scripts
Reference and Workbook
Author: Steve O'Hearn
NOTE: This document is intended to be published as an electronic document.
How to Use This Document
This document is intended to be published as an electronic document.
This document is a supplement to the book OCA Oracle Database SQL Expert Exam Guide: Exam 1Z0-047, by Steve O'Hearn. It is designed to assist those readers wishing to perform hands-on work using the SQL scripts in the book.
The chapters in this document correspond to the chapters in the book OCA Oracle Database SQL Expert Exam Guide: Exam 1Z0-047. For each chapter, this document contains the following:
-Instructions for installing database objects for that chapter, if required
-Electronic copies of each SQL script specified in the book, by chapter and page number
-Instructions and notes pertaining to unusual situations, such as SQL scripts that are deliberately written to create an error message, or interim setup scripts required to continue through the scripts in the given chapter.
Using this document, a reader may choose to do any of the following:
-Pick any random chapter of the book, look up the corresponding chapter in this document, and starting at the beginning of that selected chapter in this document, perform the specified "SETUP" instructions, then continue through the scripts in the chapter.
-Start at beginning of the book and work through each chapter to the very end.
-Play what-if games using any scripts found herein, as desired.
This document is certainly not needed to get the most out of the book. But I'm making it available to you as one of the many tools you may wish to use in your journey toward earning and maintaining your status as an Oracle certified SQL Expert.
If you have any questions or comments, please do not hesitate to contact me at - I love hearing from readers and look forward to hearing from you! In the meantime, good luck and best wishes for great success.
All my best,
Steve O'Hearn
Email:
A Note About Database Objects
I recommend that you use a standalone Oracle instance to test the scripts in this database. Keep in mind that you'll need to create the following user accounts and public synonyms:
USER ACCOUNTS:
-CODD
-EFCODD
-HARDY
-HAROLD
-HENRY
-JOAN
-LARRY
-LAUREL
-LISA
-NEWOWNER
PUBLIC SYNONYMS:
-ACCT_01
-CO
-PORTS
-WEBINARS
-WH
If you choose to run these scripts in an Oracle database that is home to other database objects, be sure to confirm that your existing objects do not have the same names as are shown above. If so, you'll need to edit these scripts to avoid inadvertently dropping any of the above objects you already have and wish to retain.
Table of Contents
1.Introduction to SQL
Setup
Page 19
2.Using DDL Statements to Create and Manage Table
Setup
Page 50
Page 54
Page 55
Page 58
Page 59
Page 66
Page 67
Page 68
Page 69
Page 70
Page 71
Page 72
Page 74
Page 75
Page 77
Page 78
Page 79
3.Manipulating Data
Setup
Page 99
Page 101
Page 102
Page 103
Page 104
Page 105
Page 106
Page 108
Page 109
Page 111
Page 113
Page 114
Page 117
Page 118
Page 119
Page 120
Page 121
Page 122
4.Retrieving Data Using the SQL SELECT Statement
Setup
Page 139
Page 141
Page 142
Page 143
Page 144
Page 145
Page 146
Page 147
Page 149
Page 150
Page 153
Page 154
5.Restricting and Sorting Data
Setup
Page 171
Page 174
Page 175
Page 176
Page 177
Page 179
Page 180
Page 181
Page 182
Page 183
Page 186
Page 188
Page 190
Page 191
Page 192
Page 193
6.Using Single-Row Functions to Customize Output
Setup
Page 210
Page 215
Page 216
Page 217
Page 218
Page 219
Page 220
Page 221
Page 222
Page 223
Page 224
Page 225
Page 226
Page 227
Page 228
Page 229
Page 230
Page 231
Page 232
Page 233
Page 235
Page 236
Page 237
Page 239
Page 240
Page 241
Page 242
Page 244
Page 245
Page 246
Page 247
Page 249
Page 250
Page 252
Page 253
Page 254
Page 255
Page 256
Page 257
Page 258
Page 259
Page 260
Page 261
Page 262
7.Reporting Aggregated Data Using the Group Functions
Setup
Page 278
Page 279
Page 280
Page 281
Page 282
Page 283
Page 284
Page 286
Page 287
Page 288
Page 289
Page 290
Page 291
Page 292
Page 293
Page 294
Page 295
Page 297
8.Displaying Data from Multiple Tables
Setup
Page 317
Page 318
Page 319
Page 320
Page 321
Page 322
Page 323
Page 324
Page 325
Page 327
Page 329
Page 330
Page 331
9.Retrieving Data Using Subqueries
Setup
Page 350
Page 351
Page 352
Page 353
Page 354
Page 356
Page 357
Page 358
Page 359
Page 361
Page 363
Page 364
Page 365
Page 366
10.Creating Other Schema Objects
Setup
Page 383
Page 384
Page 385
Page 386
Page 387
Page 388
Page 389
Page 390
Page 392
Page 394
Page 395
Page 397
Page 398
Page 399
Page 402
Page 403
Page 404
Page 405
Page 406
Page 407
Page 408
Page 409
Page 410
11.Managing Schema Objects
Setup
Page 424
Page 425
Page 426
Page 427
Page 428
Page 430
Page 431
Page 432
Page 433
Page 435
Page 436
Page 437
Page 438
Page 439
Page 443
Page 444
Page 445
Page 446
Page 447
Page 448
Page 449
Page 450
Page 451
Page 452
Page 453
Page 454
Page 455
Page 456
Page 457
Page 458
Page 460
Page 461
Page 462
Page 463
Page 464
Page 465
Page 466
Page 467
Page 468
Page 470
Page 471
Page 472
Page 473
Page 474
12.Using the Set Operators
Setup
Page 491
Page 492
Page 493
Page 494
Page 495
Page 497
Page 498
Page 499
13.Generating Reports by Grouping Related Data
Setup
Page 513
Page 514
Page 516
Page 517
Page 518
Page 519
Page 520
14.Managing Objects with Data Dictionary Views
Setup
Page 540
Page 541
Page 542
Page 543
Page 544
Page 545
Page 546
Page 547
Page 548
Page 549
15.Manipulating Large Data Sets
Setup
Page 561
Page 562
Page 563
Page 564
Page 565
Page 566
Page 572
Page 572
Page 575
Page 576
Page 577
Page 578
Page 579
Page 581
Page 582
Page 584
Page 586
Page 589
Page 590
Page 591
Page 593
Page 595
Page 596
Page 597
Page 598
16.Hierarchical Retrieval
Setup
Page 617
Page 619
Page 621
Page 622
Page 623
Page 624
Page 625
Page 626
Page 627
17.Regular Expression Support
Setup
Page 645
Page 646
Page 647
Page 648
Page 649
Page 650
Page 651
Page 652
Page 653
Page 654
Page 655
Page 656
Page 657
Page 658
Page 659
Page 661
18.Controlling User Access
Setup
Page 678
Page 679
Page 680
Page 682
Page 683
Page 685
Page 687
Page 688
Page 689
Page 690
Page 692
Page 693
Page 694
Page 695
Page 697
1.Introduction to SQL
Setup
If you are starting Chapter 1 for the first time, then log into a DBA-qualified Oracle schema and issue the following two statements.
CREATE USER CODD IDENTIFIED BY EF;
GRANT DBA TO CODD;
Note: if you have already issue the above statements earlier, you must first drop the user account, as follows:
DROP USER CODD CASCADE;
Then go back and issue the first two SQL statements to CREATE and GRANT your schema.
Finally, log into the newly created account:
CONNECT CODD/EF
You are ready to begin.
Page 19
CREATE TABLE SHIPS
( SHIP_ID NUMBER
,SHIP_NAME VARCHAR2(20)
,CAPACITY NUMBER
,LENGTH NUMBER
);
INSERT INTO SHIPS
( SHIP_ID
, SHIP_NAME
, CAPACITY
, LENGTH
)
VALUES
( 1
,'Codd Crystal'
, 2052
, 855
);
SELECT SHIP_NAME
, CAPACITY
, LENGTH
FROM SHIPS
;
2.Using DDL Statements to Create and Manage Table
Setup
If you are continuing from Chapter 1 into Chapter 2, no setup is required.
If you are starting with Chapter 2, perform the Chapter 1 setup.
Page 50
CREATE TABLE work_schedule
( work_schedule_id NUMBER
,start_date DATE
,end_date DATE
);
Page 54
CREATE TABLE ports
( port_id NUMBER
,port_name VARCHAR2(20)
);
SELECT *
FROM ports
;
SELECT *
FROM PORTS
;
CREATE TABLE "ports"
( port_id NUMBER
,port_name VARCHAR2(20)
);
SELECT *
FROM "ports"
;
Page 55
SELECT *
FROM "PORTS"
;
(Author's note: when I said in the book that the above query would not work, what I meant is that a reference to the table "PORTS" is not the same as a reference to a table called "ports". To confirm this, try the following:
INSERT INTO "ports" VALUES (1, 'Sydney');
SELECT * FROM "ports";
SELECT * FROM "PORTS";
You'll see that the first SELECT statement returns the newly inserted rows, but the second does not. The table "ports" is not the same as the table "PORTS".)
SELECT *
FROM ports
;
CREATE TABLE "Company Employees"
( employee_id NUMBER
,name VARCHAR2(35)
);
SELECT *
FROM "Company Employees"
;
Page 58
CREATE TABLE cruises
( cruise_id NUMBER
,cruise_type_id NUMBER
,cruise_name VARCHAR2(20)
,captain_id NUMBER NOT NULL
,start_date DATE
,end_date DATE
,status VARCHAR2(5) DEFAULT 'DOCK'
,CONSTRAINT cruise_pk PRIMARY KEY (cruise_id)
);
Page 59
(Author's note: you'll need to drop the CRUISES table created on Page 58 before running the next script. To drop it, use this:
DROP TABLE CRUISES;
The above script allow you to run the next script.)
CREATE TABLE cruises
( cruise_id NUMBER
, cruise_type_id NUMBER
, cruise_name VARCHAR2(20)
, captain_id NUMBER NOT NULL
, start_date DATE
, end_date DATE
, status VARCHAR2(5) DEFAULT 'DOCK'
, CONSTRAINT cruise_pk PRIMARY KEY (cruise_id)
);
DESC cruises
Page 66
CREATE TABLE CRUISE_NOTES
( CRUISE_NOTES_ID NUMBER
,CRUISE_NOTES CLOB
);
Page 67
CREATE TABLE positions
( position_id NUMBER
, position VARCHAR2(20)
, exempt CHAR(1)
, CONSTRAINT positions_pk PRIMARY KEY (position_id)
);
(Author's note: you'll need to drop the PORTS table created on Page 54 before running the next script. To drop it, use this:
DROP TABLE PORTS;
The above script allow you to run the next script.)
CREATE TABLE PORTS
( PORT_ID NUMBER PRIMARY KEY
,PORT_NAME VARCHAR2(20)
);
Page 68
(Author's note: if you've been running the scripts prior to this one, you may need to first drop the PORTS table created on Page 67 before running the next script. To drop it, use this:
DROP TABLE PORTS;
The above script allow you to run the next script.)
CREATE TABLE PORTS
( PORT_ID NUMBER CONSTRAINT PORT_ID_PK PRIMARY KEY
, PORT_NAME VARCHAR2(20)
);
CREATE TABLE VENDORS
( VENDOR_ID NUMBER
, VENDOR_NAME VARCHAR2(20)
, STATUS NUMBER(1) NOT NULL
, CATEGORY VARCHAR2(5)
);
(Author's note: to test the next script, be sure to DROP the vendors table created in the prior script:
DROP TABLE VENDORS;
The above script allow you to run the next script.)
CREATE TABLE VENDORS
( VENDOR_ID NUMBER
, VENDOR_NAME VARCHAR2(20)
, STATUS NUMBER(1) CONSTRAINT STATUS_NN NOT NULL
, CATEGORY VARCHAR2(5)
);
(Author's note: to test the next script, be sure to DROP the vendors table created in the prior script:
DROP TABLE VENDORS;
The above script allow you to run the next script.)
CREATE TABLE VENDORS
( VENDOR_ID NUMBER PRIMARY KEY
, VENDOR_NAME VARCHAR2(20)
, STATUS NUMBER(1) CONSTRAINT STATUS_NN NOT NULL
, CATEGORY VARCHAR2(5)
);
Page 69
(Author's note: you may need to first drop the PORTS table created earlier before running the next script:
DROP TABLE PORTS;
The above script allow you to run the next script.)
CREATE TABLE PORTS
( PORT_ID NUMBER
, PORT_NAME VARCHAR2(20)
, PRIMARY KEY (PORT_ID)
);
(Author's note: you may need to first drop the PORTS table created earlier before running the next script:
DROP TABLE PORTS;
The above script allow you to run the next script.)
CREATE TABLE PORTS
( PORT_ID NUMBER
,PORT_NAME VARCHAR2(20)
,CONSTRAINT PORT_ID_PK PRIMARY KEY (PORT_ID)
);
(Author's note: you may need to first drop the PORTS table created earlier before running the next script:
DROP TABLE PORTS;
The above script allow you to run the next script.)
CREATE TABLE PORTS
( PORT_ID NUMBER
, PORT_NAME VARCHAR2(20)
);
ALTER TABLE PORTS
MODIFY PORT_ID PRIMARY KEY
;
Page 70
(Author's note: you may need to first drop the PORTS table created earlier before running the next script:
DROP TABLE PORTS;
The above script allow you to run the next script.)
CREATE TABLE PORTS
( PORT_ID NUMBER PRIMARY KEY
,PORT_NAME VARCHAR2(20)
);
(Author's note: Here's a setup for the next script:
ALTER TABLE PORTS DROP PRIMARY KEY;
The above script allow you to run the next script.)
ALTER TABLE PORTS
MODIFY PORT_ID CONSTRAINT PORT_ID_PK PRIMARY KEY;
(Author's note: Here's a setup for the next script:
ALTER TABLE PORTS DROP PRIMARY KEY;
The above script allow you to run the next script.)
ALTER TABLE PORTS
ADD PRIMARY KEY (PORT_ID)
;
(Author's note: Here's a setup for the next script:
ALTER TABLE PORTS DROP PRIMARY KEY;
The above script allow you to run the next script.)
ALTER TABLE PORTS
ADD CONSTRAINT PORT_ID_PK PRIMARY KEY (PORT_ID)
;
(Author's note: Here's a setup for the next script:
DROP TABLE PORTS;
The above script allow you to run the next script, which, as specified in the book, is an invalid script and will trigger an error message.)
CREATE TABLE PORTS
( PORT_ID NUMBER
,PORT_NAME VARCHAR2(20)
,NOT NULL (PORT_ID)
);
(Author's note: Since the last script resulted in a failed attempt to create a PORTS table, there shouldn't be one. However, in case you tried to fix it and created the PORTS table, you'll now have to drop it for the next statement to perform as intended:
DROP TABLE PORTS;
Remember, the next script is specified in the book as an invalid script and will trigger an error message. See the book for more information.)
CREATE TABLE PORTS
( PORT_ID NUMBER
,PORT_NAME VARCHAR2(20)
,CONSTRAINT PORT_ID_NN NOT NULL (PORT_ID)
);
Page 71
(Author's note: Once again, you shouldn't need this, but here it is just in case:
DROP TABLE PORTS;
You're ready to run the next script.)
CREATE TABLE PORTS
( PORT_ID NUMBER
,PORT_NAME VARCHAR2(20)
,CONSTRAINT PORT_ID_UN UNIQUE (PORT_ID)
);
(Author's note: Here's a setup for the next script:
DROP TABLE PORTS;
The above script allow you to run the next script.)
CREATE TABLE PORTS
( PORT_ID NUMBER
,PORT_NAME VARCHAR2(20)
, CONSTRAINT PORT_ID_PK PRIMARY KEY (PORT_ID)
);
(Author's note: The next statement is an invalid script and will trigger an error message. See the book for more information.)
ALTER TABLE PORTS
ADD NOT NULL (PORT_NAME)
;
(Author's note: The next statement is an invalid script and will trigger an error message. See the book for more information.)
ALTER TABLE PORTS
ADD CONSTRAINT PORT_NAME_NN NOT NULL (PORT_NAME)
;
(Authors note: We're back to scripts that will execute correctly.)
ALTER TABLE PORTS
MODIFY PORT_NAME NOT NULL
;
(Author's note: Here's a setup for the next script:
DROP TABLE PORTS;
CREATE TABLE PORTS
( PORT_ID NUMBER
, PORT_NAME VARCHAR2(20)
, CONSTRAINT PORT_ID_PK PRIMARY KEY (PORT_ID)
);
The above script allow you to run the next script.)
ALTER TABLE PORTS
MODIFY PORT_NAME CONSTRAINT PORT_NAME_NN NOT NULL
;
Page 72
CREATE TABLE CUSTOMERS
( FIRST_NAME VARCHAR2(20)
,MIDDLE_NAME VARCHAR2(20)
, LAST_NAME VARCHAR2(30)
);
INSERT INTO CUSTOMERS
( FIRST_NAME
, LAST_NAME
)
VALUES
( 'Angelina'
, 'Ellison')
;
Page 74
CREATE TABLE employees
( employee_id NUMBER
, ship_id NUMBER
, first_name VARCHAR2(20)
, last_name VARCHAR2(30)
, position_id NUMBER
, CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);
Page 75
CREATE TABLE HelpDesk
( HD_Category NUMBER
,HD_Year NUMBER
, HD_Ticket_No NUMBER
, HD_Title VARCHAR2(30)
, CONSTRAINT HelpDesk_PK PRIMARY KEY (HD_Category
, HD_Year
, HD_Ticket_No)
);
Page 77
(Author's note: Here's a setup for the next script:
DROP TABLE PORTS;
The above script allow you to run the next script.)
CREATE TABLE PORTS
( PORT_ID NUMBER
,PORT_NAME VARCHAR2(20)
,COUNTRY VARCHAR2(40)
,CAPACITY NUMBER
,CONSTRAINT PORT_PK PRIMARY KEY (PORT_ID)
);
(Author's note: Here's a setup for the next script:
DROP TABLE SHIPS;
The above script allow you to run the next script.)
CREATE TABLE SHIPS
( SHIP_ID NUMBER
, SHIP_NAME VARCHAR2(20)
,HOME_PORT_ID NUMBER
,CONSTRAINT SHIPS_PORTS_FK FOREIGN KEY (HOME_PORT_ID)
REFERENCES PORTS (PORT_ID)
);
Page 78
(Author's note: Here's a setup for the next script:
DROP TABLE SHIPS;
The above script allow you to run the next script.)
CREATE TABLE SHIPS
( SHIP_ID NUMBER
,SHIP_NAME VARCHAR2(20)
,HOME_PORT_ID NUMBER NOT NULL
,CONSTRAINT SHIPS_PORTS_FK FOREIGN KEY (HOME_PORT_ID)
REFERENCES PORTS (PORT_ID)
);
Page 79
(Author's note: Here's a setup for the next script:
DROP TABLE VENDORS;
The above script allow you to run the next script.)
CREATE TABLE VENDORS
( VENDOR_ID NUMBER
,VENDOR_NAME VARCHAR2(20)
,STATUS NUMBER(1) CHECK (STATUS IN (4,5))
,CATEGORY VARCHAR2(5)
);
(Author's note: Here's a setup for the next script:
DROP TABLE VENDORS;
The above script allow you to run the next script.)
CREATE TABLE VENDORS
( VENDOR_ID NUMBER CONSTRAINT VENDOR_ID_PK PRIMARY KEY
,VENDOR_NAME VARCHAR2(20) NOT NULL
,STATUS NUMBER(1) CONSTRAINT STATUS_NN NOT NULL
,CATEGORY VARCHAR2(20)
,CONSTRAINT STATUS_CK CHECK (STATUS IN (4, 5))
,CONSTRAINT CATEGORY_CK CHECK
(CATEGORY IN ('Active','Suspended','Inactive'))
);
3.Manipulating Data
Setup
Perform the Chapter 1 setup. Next, execute the following:
CREATE TABLE cruises
( cruise_id NUMBER
, cruise_type_id NUMBER
, cruise_name VARCHAR2(20)
, captain_id NUMBER NOT NULL
, start_date DATE
, end_date DATE
, status VARCHAR2(5) DEFAULT 'DOCK'
, CONSTRAINT cruise_pk PRIMARY KEY (cruise_id)
);
You are ready to begin.
Page 99
INSERT INTO CRUISES
( CRUISE_ID
, CRUISE_TYPE_ID
, CRUISE_NAME
,CAPTAIN_ID
, START_DATE
, END_DATE
,STATUS
)
VALUES
( 1
, 1
, 'Day At Sea'
, 101
, '02-JAN-10'
, '09-JAN-10'
,'Sched'
);
DESC CRUISES
Page 101
(Author's note: Here's a setup for the next script:
DELETE FROM CRUISES;
The above script allow you to run the next script.)
INSERT INTO CRUISES
VALUES
( 1
, 1
, 'Day At Sea'
, 101
, '02-JAN-10'
, '09-JAN-10'
, 'Sched'
);
Page 102
(Author's note: Here's a setup for the next script:
CREATE TABLE TEST_SCORES
( TEST_SCORE_ID NUMBER
, SCORE NUMBER
);
The above script allow you to run the next script.)
DESC TEST_SCORES
INSERT INTO TEST_SCORES VALUES (100,85);
(Author's note: Here's a setup for the next script:
DROP TABLE TEST_SCORES;
CREATE TABLE TEST_SCORES
( SCORE NUMBER
, TEST_SCORE_ID NUMBER
);
The above script allow you to run the next script.)
DESC TEST_SCORES
INSERT INTO TEST_SCORES VALUES (100,85);
Page 103
INSERT INTO CRUISES
( CRUISE_ID
, CRUISE_NAME
,STATUS
, CAPTAIN_ID
, START_DATE
, END_DATE
)
VALUES
( 2
, 'Bermuda and Back'
, 'Done'
, 101
, '07-APR-08'
, '14-APR-08'
);
(Author's note: Here's a setup for the next script:
DELETE FROM CRUISES;
The above script allow you to run the next script.)
INSERT INTO CRUISES
( CRUISE_ID
, CAPTAIN_ID
)
VALUES
( 2
, '101'
);
Page 104
(Author's note: Here's a setup for the next script:
DROP TABLE CRUISES;
The above script allow you to run the next script.)
CREATE TABLE CRUISES
( CRUISE_ID NUMBER
, CRUISE_NAME VARCHAR2(30)
, START_DATE DATE
, END_DATE DATE
, CONSTRAINT CRUISE_ID_PK PRIMARY KEY (CRUISE_ID)
, CONSTRAINT CRUISE_NAME_CK CHECK
(CRUISE_NAME IN ('Hawaii','Bahamas','Bermuda',
'Mexico','Day at Sea')
)
);
CREATE SEQUENCE SEQ_CRUISE_ID;
Page 105
INSERT INTO CRUISES
( CRUISE_ID
, CRUISE_NAME
)
VALUES
( SEQ_CRUISE_ID.NEXTVAL
, 'Hawaii'
);
(Author's note: The next statement is an invalid script and will trigger an error message. See the book for more information.)
INSERT INTO CRUISES
( CRUISE_ID
, CRUISE_NAME
)
VALUES
( SEQ_CRUISE_ID.NEXTVAL
, 'Hawaii and Back'
);
Page 106
UPDATE CRUISES
SET CRUISE_NAME = 'Bahamas'
,START_DATE = '01-DEC-11'
WHERE CRUISE_ID = 1;
Page 108
(Author's note: Here's a setup for the next script:
CREATE TABLE COMPENSATION
( COMPENSATION_ID NUMBER
, EMPLOYEE_NUMBER NUMBER
, SALARY NUMBER
, LAST_CHANGED_DATE DATE
);
INSERT INTO COMPENSATION VALUES (1, 83, 50000, SYSDATE);