Use DDL to manage tables and their relationships

DDL statements are used to build and modify the structure of your tables and other objects in the database. When you execute a DDL statement, it takes effect immediately.

• The create table statement does exactly that:

        CREATE TABLE <table name> ( 
        <attribute name 1> <data type 1>,
        ...
        <attribute name n> <data type n>);

The data types that you will use most frequently are character strings, which might be called VARCHAR or CHAR for variable or fixed length strings; numeric types such as NUMBER or INTEGER, which will usually specify a precision; and DATE or related types. Data type syntax is variable from system to system; the only way to be sure is to consult the documentation for your own software.

• The alter table statement may be used as you have seen to specify primary and foreign key constraints, as well as to make other modifications to the table structure. Key constraints may also be specified in the CREATE TABLE statement.

        ALTER TABLE <table name>
        ADD CONSTRAINT <constraint name> PRIMARY KEY (<attribute list>);

You get to specify the constraint name. Get used to following a convention of tablename_pk (for example, Customers_pk), so you can remember what you did later. The attribute list contains the one or more attributes that form this PK; if more than one, the names are separated by commas.

• The foreign key constraint is a bit more complicated, since we have to specify both the FK attributes in this (child) table, and the PK attributes that they link to in the parent table.

        ALTER TABLE <table name>
        ADD CONSTRAINT <constraint name> FOREIGN KEY (<attribute list>)
        REFERENCES <parent table name> (<attribute list>);

Name the constraint in the form childtable_parenttable_fk (for example, Orders_Customers_fk). If there is more than one attribute in the FK, all of them must be included (with commas between) in both the FK attribute list and the REFERENCES (parent table) attribute list.

You need a separate foreign key definition for each relationship in which this table is the child.

• If you totally mess things up and want to start over, you can always get rid of any object you’ve created with a drop statement. The syntax is different for tables and constraints.

        DROP TABLE <table name>;

        ALTER TABLE <table name>
        DROP CONSTRAINT <constraint name>;

This is where consistent constraint naming comes in handy, so you can just remember the PK or FK name rather than remembering the syntax for looking up the names in another table. The DROP TABLE statement gets rid of its own PK constraint, but won’t work until you separately drop any FK constraints (or child tables) that refer to this one. It also gets rid of all data that was contained in the table—and it doesn't even ask you if you really want to do this!

• All of the information about objects in your schema is contained, not surprisingly, in a set of tables that is called the data dictionary. There are hundreds of these tables most database systems, but all of them will allow you to see information about your own tables, in many cases with a graphical interface. How you do this is entirely system-dependent.

Describe the purpose of Data Definition Language (DDL)

Data Definition Language (DDL) is a standard for commands that define the different structures in a database. DDL statements create, modify, and remove database objects such as tables, indexes, and users.



Commonly used DDL in SQL querying are:

CREATE: This command builds a new table and has a predefined syntax. The CREATE statement syntax is CREATE TABLE [table name] ([column definitions]) [table parameters]. CREATE TABLE Employee (Employee Id INTEGER PRIMARY KEY, First name CHAR (50) NULL, Last name CHAR (75) NOT NULL).


ALTER: An alter command modifies an existing database table. This command can add up additional column, drop existing columns and even change the data type of columns involved in a database table. An alter command syntax is ALTER object type object name parameters. ALTER TABLE Employee ADD DOB Date.


DROP: A drop command deletes a table, index or view. Drop statement syntax is DROP object type object name. DROP TABLE Employee.

Relationship between a database and SQL

Database:

Databases are essentially collections of data that are designed to let the user retrieve and store data as efficiently as possible.  They are capable of storing millions of records, while at the same time able to locate a single unique file within seconds.   The computer language that these entities speak is the SQL side.

SQL:

SQL (Structured Query Language) is a programming language designed for managing items held in databases.  Most databases support the use of SQL to access and store data.

So, this is the relationship between databases and SQL.  SQL is the language that speaks to the database which makes it possible to store and retrieve information very rapidly.

Oracle Database SQL - 1Z0-071

Oracle Database SQL - 1Z0-071

Using Structured Query Language (SQL)    

•     Explain the relationship between a database and SQL

Using Data Definition Language (DDL)    

•     Describe the purpose of DDL 
•     Use DDL to manage tables and their relationships
•     Explain the theoretical and physical aspects of a relational database

Using Data Manipulation Language (DML) and Transaction Control Language (TCL)    

•     Describe the purpose of  DML 
•     Use DML to manage data in tables
•     Use TCL to manage transactions

Defining SELECT Statements    

•     Identify the connection between an ERD and a database using SQL SELECT statements

Using Basic SELECT statements    

•     Build a SELECT statement to retrieve data from an Oracle Database table
•     Use the WHERE clause to the SELECT statement to filter query results

Restricting and Sorting Data    

•     Use the ORDER BY clause to sort SQL query results
•     Limit the rows that are retrieved by a query
•     Sort the rows that are retrieved by a query
•     Use ampersand substitution to restrict and sort output at runtime

Defining Table Joins    

•     Describe the different types of joins and their features
•     Use joins to retrieve data from multiple tables
•     Use self joins

Using Single-Row Functions to Customize Output    

•     Use various types of functions available in SQL
•     Use conversion functions
•     Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD) functions in SELECT statements

Using Conversion Functions and Conditional Expressions    

•     Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
•     Apply general functions and conditional expressions in a SELECT statement

Reporting Aggregated Data Using the Group Functions    

•     Describe the use of group functions
•     Group data by using the GROUP BY clause
•     Include or exclude grouped rows by using the HAVING clause

Displaying Data from Multiple Tables    

•     Use SELECT statements to access data from more than one table using equijoins and nonequijoins
•     Join a table to itself by using a self-join
•     View data that generally does not meet a join condition by using outer joins

Using Subqueries to Solve Queries    

•     Define subqueries
•     Describe the types of problems subqueries can solve
•     Describe the types of subqueries
•     Use correlated subqueries
•     Update and delete rows using correlated subqueries
•     Use the EXISTS and NOT EXISTS operators
•     Use the WITH clause
•     Use single-row and multiple-row subqueries

Using the Set Operators    

•     Use a set operator to combine multiple queries into a single query
•     Control the order of rows returned

Manipulating Data    

•     Insert rows into a table
•     Update rows in a table
•     Delete rows from a table
•     Control transactions

Using DDL Statements to Create and Manage Tables    

•     Describe data types that are available for columns
•     Create a simple table
•     Create constraints for tables
•     Describe how schema objects work
•     Execute a basic SELECT statement

Creating Other Schema Objects    

•     Create simple and complex views with visible/invisible columns
•     Create, maintain and use sequences

Managing Objects with Data Dictionary Views    

•     Use the data dictionary views to research data on objects
•     Query various data dictionary views

Controlling User Access    

•     Differentiate system privileges from object privileges
•     Grant privileges on tables and on a user
•     Distinguish between privileges and roles

Managing Schema Objects    

•     Manage constraints
•     Create and maintain indexes including invisible indexes and multiple indexes on the same columns
•     Drop columns and set column UNUSED
•     Perform flashback operations
•     Create and use external tables

Manipulating Large Data Sets    

•     Describe the features of multitable INSERTs
•     Merge rows in a table

Changing undo tablespace online without database shutdown.

SQL> select name from v$database;

NAME
---------
SESDB


SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
SEARCH_ESS
SEARCH_DATA
SEARCH_DATA
SEARCH_DATA
SEARCH_INDEX
SEARCH_MDS

10 rows selected.

SQL> create undo tablespace undotbs2 datafile 'E:\app\SES\oradata\sesdb\undotbs0
2.dbf' size 1000m;

Tablespace created.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
UNDOTBS2
SEARCH_ESS
SEARCH_DATA
SEARCH_DATA
SEARCH_DATA
SEARCH_INDEX
SEARCH_MDS

11 rows selected.

SQL> alter system set undo_tablespace= undotbs2;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
SQL>
SQL> drop tablespace undotbs1 including contents;

Tablespace dropped.

SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
USERS
UNDOTBS2
SEARCH_ESS
SEARCH_DATA
SEARCH_DATA
SEARCH_DATA
SEARCH_INDEX
SEARCH_MDS

10 rows selected.

SQL>