{"id":883,"date":"2025-12-26T08:16:14","date_gmt":"2025-12-26T08:16:14","guid":{"rendered":"https:\/\/forgefuse.wasmer.app\/?p=883"},"modified":"2025-12-26T08:16:14","modified_gmt":"2025-12-26T08:16:14","slug":"oracle-sql-certification-1z0-071-course-section-4-use-ddl-to-manage-tables-and-their-relationships","status":"publish","type":"post","link":"https:\/\/www.forgefuse.net\/index.php\/oracle-sql-certification-1z0-071-course-section-4-use-ddl-to-manage-tables-and-their-relationships\/","title":{"rendered":"Oracle SQL Certification 1Z0-071 Course | Section 4: Use DDL to Manage Tables and Their Relationships"},"content":{"rendered":"\n<p>Data Definition Language, or DDL, is used to define and modify database structures. In the Oracle SQL 1Z0-071 exam, DDL topics are tested frequently and often combined with constraint behavior, data types, and transaction rules.<\/p>\n\n\n\n<p>This section focuses on creating tables, defining columns and constraints, modifying table structures, and understanding special table types supported by Oracle.<\/p>\n\n\n\n<p><strong><span style=\"text-decoration: underline;\">Video 1: Creating a Table and Using DESCRIBE<\/span><\/strong><\/p>\n\n\n\n<p>Creating tables is a foundational skill for Oracle SQL and a core exam topic. A CREATE TABLE statement defines the table name, column names, data types, and constraints.<\/p>\n\n\n\n<p>The general structure of a CREATE TABLE statement includes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The CREATE keyword<\/li>\n\n\n\n<li>The object type TABLE<\/li>\n\n\n\n<li>The table name<\/li>\n\n\n\n<li>Column definitions<\/li>\n\n\n\n<li>Optional constraints<\/li>\n<\/ul>\n\n\n\n<p>Multiple columns are defined within parentheses and separated by commas. Each column requires a name and a data type.<\/p>\n\n\n\n<p>Common data types introduced include NUMBER for numeric values, VARCHAR2 for variable length text, and DATE for date and time values.<\/p>\n\n\n\n<p>Constraints are introduced directly inside the CREATE TABLE statement. Primary key constraints enforce uniqueness and automatically apply a NOT NULL restriction. Default values can be defined to automatically populate columns during insert operations, such as using SYSDATE.<\/p>\n\n\n\n<p>Oracle provides the DESCRIBE command to inspect a table\u2019s structure. DESCRIBE, or its short form DESC, displays column names, data types, and nullability. It is an Oracle specific command, does not require a semicolon, and works in both SQL Developer and SQLPlus.<\/p>\n\n\n\n<p>Understanding how to read table metadata using DESCRIBE and graphical tools in SQL Developer is important for both practice and exam questions.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Creating a table and use Describe - Oracle SQL Certification (1Z0-071) | Section 4, Video 1\" width=\"1290\" height=\"726\" src=\"https:\/\/www.youtube.com\/embed\/SPezYPT_1_Y?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<pre class=\"wp-block-code\"><code>-- CREATE objectType objectName attributes\n-- create table\nCREATE TABLE cars (\ncar_id NUMBER,\ncar_manufacturer VARCHAR2(20),\ncar_model VARCHAR2(20),\nowner_id NUMBER NOT NULL,\nmanufacture_date DATE DEFAULT SYSDATE,\ncar_color VARCHAR2(15),\nCONSTRAINT car_pk PRIMARY KEY ( car_id )\n);\n-- description of table\nDESC cars;\n-- remove table as we don't need it atm\nDROP TABLE cars;<\/code><\/pre>\n\n\n\n<p><strong><span style=\"text-decoration: underline;\">Video 2: Data Types for Columns<\/span><\/strong><\/p>\n\n\n\n<p>Choosing correct data types is one of the most heavily tested topics in the 1Z0-071 exam. Data types affect how data is stored, validated, and processed.<\/p>\n\n\n\n<p>Oracle data types are grouped into several categories:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Character<\/li>\n\n\n\n<li>Numeric<\/li>\n\n\n\n<li>Date and Time<\/li>\n\n\n\n<li>Large Objects<\/li>\n<\/ul>\n\n\n\n<p>Character data types include CHAR and VARCHAR2. CHAR stores fixed length data and pads unused space with blanks, while VARCHAR2 stores only the actual length of the value. For most use cases and exam questions, VARCHAR2 is preferred unless fixed length storage is explicitly required.<\/p>\n\n\n\n<p>The NUMBER data type is defined using precision and scale. Precision specifies the total number of digits, while scale defines how many digits appear to the right of the decimal point. Oracle applies rounding rules when inserting values and raises errors when precision limits are exceeded. Negative scale values cause rounding to the left of the decimal point.<\/p>\n\n\n\n<p>Date and time data types include DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. The DATE type stores both date and time components, which is a common exam trap.<\/p>\n\n\n\n<p>Large object data types such as BLOB, CLOB, and NCLOB are used to store large amounts of data. These types have important restrictions. They cannot be used as primary keys and cannot participate in DISTINCT, GROUP BY, ORDER BY, or join operations.<\/p>\n\n\n\n<p>Oracle relies on NLS_DATE_FORMAT to implicitly convert strings into dates, which affects how date literals are interpreted.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Data types for Columns - Oracle SQL Certification (1Z0-071) | Section 4, Video 2\" width=\"1290\" height=\"726\" src=\"https:\/\/www.youtube.com\/embed\/ObJ-Q8yzKBY?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<pre class=\"wp-block-code\"><code>--character data type example\nCREATE TABLE test_table_character (\ncharacter1 CHAR(5),\ncharacter2 varchar2(100) -- the size number has to be defined and larger than 0\n);\nINSERT INTO test_table_character VALUES (\n'A', -- character1\n'A' -- character2\n);\nSELECT\n*\nFROM\ntest_table_character;\n--DELETE test_table_character;\n-- drop table as we don't need it anymore\nDROP TABLE test_table_character;\n-- number data type example\nCREATE TABLE test_table (\nnumber1 NUMBER,\nnumber2 NUMBER(2),\nnumber3 NUMBER(5, 2),\nnumber4 NUMBER(5, 2),\nnumber5 NUMBER(4, 1),\nnumber6 NUMBER(3, 2),\nnumber7 NUMBER(5, -2)\n);\nINSERT INTO test_table VALUES (\n1.23, -- number1\n11.23, -- number2\n1.23, -- number3\n1.26, -- number4\n1.26, -- number5\n11.45, -- number6\n1265.34 -- number7\n);\nSELECT * FROM test_table;\n-- DELETE test_table;\n-- drop table as we don't need it anymoreDROP TABLE test_table;<\/code><\/pre>\n\n\n\n<p><strong><span style=\"text-decoration: underline;\">Video 3: Constraints<\/span><\/strong><\/p>\n\n\n\n<p>Constraints enforce data integrity rules and are one of the most critical exam topics. Oracle SQL supports five constraint types tested in the exam:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>PRIMARY KEY<\/li>\n\n\n\n<li>FOREIGN KEY<\/li>\n\n\n\n<li>UNIQUE<\/li>\n\n\n\n<li>NOT NULL<\/li>\n\n\n\n<li>CHECK<\/li>\n<\/ul>\n\n\n\n<p>Constraints can be defined inline with column definitions or out of line after all columns are declared. Oracle can generate constraint names automatically, or you can specify them explicitly.<\/p>\n\n\n\n<p>A table can have only one primary key, which may consist of one or multiple columns. UNIQUE constraints allow NULL values, while primary keys do not. NOT NULL constraints can only be defined inline.<\/p>\n\n\n\n<p>Foreign keys establish relationships between tables and enforce referential integrity. A referenced table cannot be dropped while dependent foreign keys exist.<\/p>\n\n\n\n<p>PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints automatically create indexes. Certain data types, such as LOBs and TIMESTAMP WITH TIME ZONE, cannot be used with some constraints.<\/p>\n\n\n\n<p>Understanding when constraints are validated and how they behave during insert and update operations is essential for exam success.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Constraints - Oracle SQL Certification (1Z0-071) | Section 4, Video 3\" width=\"1290\" height=\"726\" src=\"https:\/\/www.youtube.com\/embed\/JYgbWTmDQ84?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Primary key constraint\n-- inline constraint - system named\nCREATE TABLE constraint_table_1 (\nconstraint_id NUMBER PRIMARY KEY\n);\nSELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_1');\n-- inline constraint - Named\nCREATE TABLE constraint_table_2 (\nconstraint_id NUMBER CONSTRAINT constraint_2_pk PRIMARY KEY\n);\nSELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_2');\n-- Out of line constraint - Named\nCREATE TABLE constraint_table_3 (\nconstraint_id NUMBER,\nCONSTRAINT constraint_3_pk PRIMARY KEY ( constraint_id )\n);\nSELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_3');\n-- Out of line constraint - System named\nCREATE TABLE constraint_table_4 (\nconstraint_id NUMBER,PRIMARY KEY ( constraint_id )\n);\nSELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_4');\n\n-- add pk with alter table\nCREATE TABLE constraint_table_5 (\nconstraint_id NUMBER\n);\nALTER TABLE constraint_table_5\nMODIFY constraint_id PRIMARY KEY;\nALTER TABLE constraint_table_5\nADD PRIMARY KEY (constraint_id);\nALTER TABLE constraint_table_5\nADD CONSTRAINT alter_pk_named PRIMARY KEY (constraint_id);\nSELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_5');\nDROP TABLE constraint_table_1;\nDROP TABLE constraint_table_2;\nDROP TABLE constraint_table_3;\nDROP TABLE constraint_table_4;\nDROP TABLE constraint_table_5;\n\n-- Unique constraint\n-- inline constraint - system named\nCREATE TABLE constraint_table_1 (\nconstraint_id NUMBER UNIQUE\n);\nSELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_1');\n-- inline constraint - Named\nCREATE TABLE constraint_table_2 (\nconstraint_id NUMBER CONSTRAINT constraint_2_uniq UNIQUE\n);\nSELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_2');\n-- Out of line constraint - Named + composite\nCREATE TABLE constraint_table_3 (\nconstraint_id NUMBER,\nconstraint_name VARCHAR(20),\nCONSTRAINT constraint_3_uniq UNIQUE ( constraint_id,constraint_name )\n);\nSELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_3');\n-- Out of line constraint - System named\nCREATE TABLE constraint_table_4 (\nconstraint_id NUMBER,\nUNIQUE ( constraint_id )\n);\n\nSELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('constraint_table_4');\nDROP TABLE constraint_table_1;\nDROP TABLE constraint_table_2;\nDROP TABLE constraint_table_3;\nDROP TABLE constraint_table_4;<\/code><\/pre>\n\n\n\n<p><strong><span style=\"text-decoration: underline;\">Video 4: Dropping Columns and Setting Columns UNUSED<\/span><\/strong><\/p>\n\n\n\n<p>Oracle provides two ways to remove columns from a table. Dropping a column permanently removes both the column definition and its data. This operation can be time consuming on large tables.<\/p>\n\n\n\n<p>Setting a column as UNUSED is a faster, metadata only operation. The column becomes inaccessible, cannot be rolled back, and still counts toward the table\u2019s column limit. UNUSED columns must be explicitly dropped later to free storage.<\/p>\n\n\n\n<p>Key rules include:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A table must always contain at least one column<\/li>\n\n\n\n<li>Columns involved in constraints may require CASCADE CONSTRAINTS<\/li>\n\n\n\n<li>UNUSED columns cannot be recovered<\/li>\n\n\n\n<li>UNUSED columns can be identified using data dictionary views<\/li>\n<\/ul>\n\n\n\n<p>Understanding when to drop a column versus setting it UNUSED is important for both real world scenarios and exam questions.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Dropping and setting columns unused - Oracle SQL Certification (1Z0-071) | Section 4, Video 4\" width=\"1290\" height=\"726\" src=\"https:\/\/www.youtube.com\/embed\/cW7TzGQW0J0?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<pre class=\"wp-block-code\"><code>-- dropping columns\nCREATE TABLE multi_column_table (\nid_column NUMBER,\ntext_column_1 VARCHAR2(100),\ntext_column_2 VARCHAR2(100),\ntext_column_3 VARCHAR2(100)\n);\n-- drop column\nALTER TABLE multi_column_table DROP COLUMN text_column_1;\nSELECT * FROM multi_column_table;\n-- other way, this way we can drop multiple columns in one line\nALTER TABLE multi_column_table DROP (text_column_2, text_column_3);\nSELECT * FROM multi_column_table;\n-- drop table as we do not need it\nDROP TABLE multi_column_table;\n-- dropping columns with constraints\nCREATE TABLE primary_table (\nprim_id NUMBER PRIMARY KEY,\ntext_column_1 VARCHAR2(100)\n);\nCREATE TABLE reference_table (\nid_ref_column NUMBER PRIMARY KEY,\nprim_id NUMBER,\nCONSTRAINT fk_prim FOREIGN KEY ( prim_id )\nREFERENCES primary_table ( prim_id )\n);\nALTER TABLE primary_table DROP COLUMN prim_id; -- WON'T WORK!\nALTER TABLE primary_table DROP COLUMN prim_id CASCADE CONSTRAINTS; --\nconstraints are also dropped\nSELECT * FROM primary_table;\n-- tip: CONSTRAINT and COSTRAINTS keywords (singular and plural) are both valid!\n--drop tables as we do not need it atm\nDROP TABLE primary_table;\nDROP TABLE reference_table;\n\n-- setting columns unused\nCREATE TABLE multi_column_table (\nid_column NUMBER,\ntext_column_1 VARCHAR2(100),\ntext_column_2 VARCHAR2(100),\ntext_column_3 VARCHAR2(100)\n);\nSELECT * FROM multi_column_table;\nALTER TABLE multi_column_table SET UNUSED COLUMN text_column_1;\nALTER TABLE multi_column_table SET UNUSED (text_column_2, text_column_3);\n--we can check unused columns:\nSELECT * FROM USER_UNUSED_COL_TABS;\n--drop columns which are unused\nALTER TABLE multi_column_table DROP UNUSED COLUMNS;\n--drop table as we do not need it atm\nDROP TABLE multi_column_table;<\/code><\/pre>\n\n\n\n<p><strong><span style=\"text-decoration: underline;\">Video 5: External Tables<\/span><\/strong><\/p>\n\n\n\n<p>External tables allow Oracle to read data directly from files stored outside the database. They are defined as database objects, but the data itself remains external.<\/p>\n\n\n\n<p>External tables are created using ORGANIZATION EXTERNAL and ORACLE_LOADER. They rely on DIRECTORY objects, which must have appropriate read and write privileges granted.<\/p>\n\n\n\n<p>External tables support only SELECT operations. INSERT, UPDATE, and DELETE are not allowed. Constraints, indexes, and LOB data types are not supported. Columns cannot be set UNUSED, but they can be dropped.<\/p>\n\n\n\n<p>Dropping an external table removes only its metadata. The underlying file remains unchanged.<\/p>\n\n\n\n<p>External tables are commonly used for data integration, reporting, and querying system generated files without loading them into database tables.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"External Tables - Oracle SQL Certification (1Z0-071) | Section 4, Video 5\" width=\"1290\" height=\"726\" src=\"https:\/\/www.youtube.com\/embed\/_oIEh8HpRHk?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<pre class=\"wp-block-code\"><code>-- create directory -- from sys user on our pluggable database\nCREATE OR REPLACE DIRECTORY receipts_dir AS 'C:\\app\\';\n--grant read\/write to hr user -- from sys user on our pluggable database\nGRANT READ ON DIRECTORY receipts_dir TO HR;\nGRANT WRITE ON DIRECTORY receipts_dir TO HR;\n-- create text file in directory as specified\nRECEIPT_ID \/ DATE \/ BATCH\n======================\n555123 01-NOV-2020 ABCD1\n555124 02-NOV-2020 ABCD1\n555125 03-NOV-2020 ABCD2\n555126 03-NOV-2020 ABCD2\n-- create external table\nCREATE TABLE external_receipts (\nreceipt_id CHAR(6),\nreceipt_date CHAR(10),\nreceipt_batch_id CHAR(5)\n)\nORGANIZATION EXTERNAL ( TYPE oracle_loader\nDEFAULT DIRECTORY receipts_dir ACCESS PARAMETERS (\nRECORDS DELIMITED BY NEWLINE\nSKIP 4\nFIELDS (\nreceipt_id CHAR (6),\nreceipt_date CHAR (12),\nreceipt_batch_id CHAR (6)\n)\n) LOCATION ( 'RECEIPTS.TXT' )\n);\nSELECT * FROM external_receipts;<\/code><\/pre>\n\n\n\n<p><strong><span style=\"text-decoration: underline;\">Section Outcome<\/span><\/strong><\/p>\n\n\n\n<p>After completing this section, you should:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Be able to create and modify tables using DDL<\/li>\n\n\n\n<li>Understand how data types affect storage and behavior<\/li>\n\n\n\n<li>Apply and interpret constraints correctly<\/li>\n\n\n\n<li>Modify table structures safely and efficiently<\/li>\n\n\n\n<li>Understand the purpose and limitations of external tables<\/li>\n<\/ul>\n\n\n\n<p>These skills form the foundation for writing reliable SQL and are heavily tested in the Oracle SQL 1Z0-071 certification exam.<\/p>\n\n\n\n<p>Now already involved in this SQL journey, you can subscribe to the YouTube channel and maybe subscribe to newsletter below?<\/p>\n\n\n\n<div class=\"wp-block-stackable-button-group stk-block-button-group stk-block stk-88daaaf\" data-block-id=\"88daaaf\"><div class=\"stk-row stk-inner-blocks stk-block-content stk-button-group\">\n<div class=\"wp-block-stackable-button stk-block-button popmake-851 stk-block stk-0cfdac5 popmake-851\" data-block-id=\"0cfdac5\"><a class=\"stk-link stk-button stk--hover-effect-darken\" href=\"\"><span class=\"stk-button__inner-text\">Join ForgeFuse Newsletter<\/span><\/a><\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>In this section we&#8217;ll learn basics (but in depth) around creating tables and their properties<\/p>\n","protected":false},"author":1,"featured_media":884,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":41,"footnotes":""},"categories":[11],"tags":[7,13,12],"class_list":["post-883","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle-1z0-071-sql-course","tag-1z0-071","tag-certification","tag-sql"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/posts\/883","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/comments?post=883"}],"version-history":[{"count":0,"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/posts\/883\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/media\/884"}],"wp:attachment":[{"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/media?parent=883"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/categories?post=883"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.forgefuse.net\/index.php\/wp-json\/wp\/v2\/tags?post=883"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}