Download Table Creation Data Manipulation - Buisness Management - Lecture Notes and more Study notes Business Administration in PDF only on Docsity!
Page 1
TABLE CREATION & POPULATION
Data Definition Language (DDL):
DDL is the subset of SQL commands used to create, modify or remove Oracle database structures, including tables. These commands have an immediate effect on the database, and also record information in the Data Dictionary (this was covered in an earlier tutorial).
User Privileges: In order to be able to create a table a user must have been given: CREATE TABLE privilege A TABLESPACE area in which to create objects.
Oracle Table Structures: Tables can be created at any time, even with users still online and using the database. There is no need to specify the size of any table – however it is important to estimate how much space a table will use over time. We will be creating tables with storage options.
SQL User Matrix: Going back to the SQL user matrix, the commands discussed in this section can be used by everyone except users. Users would strictly be restricted to querying the database using SELECT.
Querying Data
Inserting Updating Deleting Rows
Creating, Replacing, Altering, Dropping Objects
Controlling Access
Guarantee Consistency and Integrity
DEVELOPER X XX X X X
USER X
DBA X X X XX X
DESIGNER X X XX X XX
Table Naming Conventions: The name you choose for a table must follow these standard rules: The name must begin with a letter A-Z or a-z Can contain numbers and underscores Can be in UPPER of lower case Can be up to 30 characters in length Cannot use the same name of another existing object in your schema Must not be a SQL reserved word
Examples of Object Names:
Name Valid?
EMP85 Yes
85EMP No – does not begin with a letter
FIXED_assets Yes
FIXED ASSETS No – contains a blank space
UPDATE No – is a SQL reserved word
You should use:
Page 2
Descriptive names for the tables and other database objects The same name to describe the same entity across different tables. E.g., the department number column is called DEPTNO in both EMP and DEPT table.
Creating a Table: You create a new table using the CREATE TABLE command.
One of the simplest forms of the CREATE TABLE command is where the basic information for each column is defined, together with their datatype and sizes, and with no integrity constraints.
When you create a table you need to specify each columns datatype (these were discussed in an earlier Practical).
CREATE TABLE table_name (column_name type(size), column_name type(size), ….);
Here is an example:
CREATE TABLE CARS (REG_NO VARCHAR2(20) NOT NULL, MAKE VARCHAR2(20) NOT NULL, MODEL VARCHAR2(20), OWNER VARCHAR2(20), LAST_SERVICE DATE);
If the table is successfully created you will receive a message “Table Created”. If you have an object of that name already you will not be allowed to create that object.
Creating a Table with Storage Options:
When creating a table in the Oracle database you can indicate where the data from the table is to be stored by specifying a tablespace for the table. If no tablespace is specified, then the table is stored in the users default tablespace. The users default tablespace is assigned when the Oracle DBA initially created your Oracle account. To find out where this is enter the following:
SELECT * FROM USER_USERS;
You will see five columns of data: username user_id – is the Oracle assigned internal id default_tablespace – where any objects we create without specifying a location will be stored temporary_tablespace – where Oracle will temporarily store the object created. created – the date the Oracle account was created
Page 4
There is an alternative method of creating a table and that is to use the format and data of an existing table. This method is useful when you want to select the data out of a table for temporary modification or if you want to create a table similar to the existing table using similar data. The syntax is as follows:
CREATE TABLE new_table(column1, column2, column3) AS (SELECT column1, column2, column FROM old_table ….)
CREATE TABLE new_cars(reg_no, make, model) AS (SELECT reg_no, make, model FROM cars);
You could add a WHERE statement to the above so as to select only a few rows from the existing cars table. You can then alter the new_cars table, using the ALTER statement to add more columns etc. This saves the user from recreating the table from scratch.
Altering a Table:
Once a table definition has been created, it can be changed at any time using the ALTER TABLE command. It is possible to modify the type of a column and also add further columns.
Syntax:
ALTER TABLE table_name MODIFY (column_name type (size));
Example:
ALTER TABLE CARS
MODIFY (OWNER VARCHAR2(30));
The above example extends the length of the owner column to cope with more than 20 characters, which it was originally defined as. The column width can be increased whether there is data in the column or not. Column width can only be decreased or the datatype changed of the column is empty (contains only nulls).
The next example adds a new column to a table. The column is defined in the same way as in CREATE TABLE. The NOT NULL parameter can only be used if the table is empty.
Example:
ALTER TABLE CARS
ADD (COLOUR VARCHAR2(10));
Notes: Columns cannot be dropped from a table – would need to drop the table and recreate it. The datatype of a column can be changed only if the column does not contain any data. Can use the RENAME command to rename a table e.g. RENAME table_name TO new_table_name;
Altering Storage Parameters You can use the ALTER TABLE command to control allocation of space for future blocks.
Page 5
Example : ALTER TABLE CARS STORAGE ( MAXEXTENTS 121 PCTINCREASE 100);
Dropping a Table:
To delete a table from the database, use the DROP TABLE command. When a table is dropped all the data it contains is also lost. In addition, if any indexes existed on the table, or if any privileges were made on the table then these would also be lost.
For example:
DROP TABLE CARS;
However the following would have to be used if referential integrity constraints that refer to primary and unique keys in the table to be dropped existed.
DROP TABLE cars CASCADE CONSTRAINTS;
If you omit this option, and such referential integrity constraints exist, the Oracle Server returns an error message and does not drop the table. We will cover Constraints in a later Tutorial.
Data Manipulation:
Up to this point we have just retrieved data from tables. There are three statements used to manipulate date: INSERT UPDATE DELTE
Inserting Data into a Table:
The INSERT statement inserts one or more rows into a table. There are three ways that we can use to insert data into tables using the INSERT statement.
Insert the data one row at a time – specifying the column headings Insert the data one row at a time – without specifying the column headings Insert the data several rows at a time
We will create a small table for these examples:
CREATE TABLE JACK(
A NUMBER,
B NUMBER,
C NUMBER);
Now use the describe command to list the columns within the table:
DESC jack;
The first way we can add data into this table is using an INSERT statement with the following syntax: INSERT INTO table_name
Page 7
SELECT column1, column FROM tablename WHERE search_condition;
Example:
INSERT INTO JILL(A,B)
SELECT A, B
FROM JACK;
Now check the contents of the JILL table. We can also include WHERE clauses in the above statement;
INSERT INTO JILL(A,B) SELECT A, B FROM JACK WHERE C > 10;
Once all inserts are complete, you should commit i.e. save all data to the database. To do this type commit;
Updating Data in a Table:
The update statement modifies the data values in one or more columns of a table. It must have at least two clauses, UPDATE and SET. The syntax for the UPDATE statement is as follows: UPDATE table_name SET column1 = value [,column2 = value2].. WHERE search_condition;
Example:
UPDATE CARS
SET COLOUR = ‘NAVY’
WHERE COLOUR = ‘RED’;
Note: You can have more than one SET command per UPDATE statement. Also if you omit the WHERE clause, then every record in the table is changed.
UPDATE CARS
SET COLOUR = ‘NAVY’,
OWNER = ‘PAUL’
WHERE COLOUR = ‘RED’;
Again, you should commit all desired updates to the database.
Deleting Data from a Table:
Delete removes rows from a table. As with UPDATE and SELECT, a DELETE without a WHERE clause will act on all rows in a table, deleting them all!
For example, the code below will delete all rows from the cars table
DELETE FROM CARS;
Page 8
When we delete data from an Oracle table we have the equivalent of an undo statement available. This ‘undo’ statement is called the ‘rollback’ statement and will undo any changes that have been made in your current SQL*PLUS session since you used the last commit statement.
So for example, having typed in the above statement we have cleared out the CARS table. However, we can type in the following to undo the change:
ROLLBACK;
This will undo the delete. However, if you typed in the above delete statement and then typed in COMMIT, then the relevant rows are deleted and cannot be undone.
The following should be used to remove just some rows from the table:
DELETE FROM CARS
WHERE OWNER = ‘MARK’;
It is safer to use a commit at this point.
