6174

Creation altering and deletion a table in SQL Oracle

Лабораторная работа

Информатика, кибернетика и программирование

Creation altering and deletion a table in SQL Oracle Purpose of the lab To study SQL Oracle possibilities in table creation, altering and deletion. To acquire practical skills in table creation, altering and deletion by using SQL*P...

Английский

2012-12-30

206.5 KB

11 чел.

Creation altering and deletion a table in SQL Oracle

  1.  Purpose of the lab
  •  To study SQL Oracle possibilities in table creation, altering and deletion.
  •  To acquire practical skills in table creation, altering and deletion by using SQL*Plus.
  1.  Theoretical backgrounds
    1.  Table creation
      1.  Syntax

  1.  Purpose

To create a relational table, the basic structure to hold user data. Tables are created with no data. You can add rows to a table with the INSERT statement. After creating a table, you can define additional columns and integrity constraints with the ADD clause of the ALTER TABLE statement. You can change the definition of an existing column with the MODIFY clause of the ALTER TABLE statement.

  1.  Prerequisites

To create a relational table in your own schema, you must have CREATE TABLE system privilege. To create a table in another user's schema, you must have CREATE ANY TABLE system privilege.

To enable a UNIQUE or PRIMARY KEY constraint, you must have the privileges necessary to create an index on the table. You need these privileges because Oracle creates an index on the columns of the unique or primary key in the schema containing the table.

  1.  Keywords and Parameters 

schema  

is the schema to contain the table. If you omit schema, Oracle creates the table in your own schema.  

table  

is the name of the table to be created.

column  

specifies the name of a column of the table.

If you also specify AS subquery, you can omit column and datatype.  

datatype  

is the datatype of a column. Oracle-supplied datatypes are defined in "Oracle Built-In datatypes".

You can specify a column of type ROWID, but Oracle does not guarantee that the values in such columns are valid rowids.  You can omit datatype:

- If you also specify AS subquery. (If you are creating an index-organized table and you specify AS subquery, you must omit the datatype.)

- If the statement also designates the column as part of a foreign key in a referential integrity constraint. (Oracle automatically assigns to the column the datatype of the corresponding column of the referenced key of the referential integrity constraint.)

DEFAULT  expr

specifies a value to be assigned to the column if a subsequent INSERT statement omits a value for the column. The datatype of the expression must match the datatype of the column. The column must also be long enough to hold this expression.

column_constraint 

defines an integrity constraint as part of the column definition. Column constraints will be discussed in Lab 2

table_constraint  

defines an integrity constraint as part of the table definition. Table constraints will be discussed in Lab 2

AS subquery  

inserts the rows returned by the subquery into the table upon its creation.

Restrictions: 

The number of columns in the table must equal the number of expressions in the subquery.

The column definitions can specify only column names, default values, and integrity constraints, not datatypes.

You cannot define a referential integrity constraint (See Lab 2) in a CREATE TABLE statement that contains AS subquery. Instead, you must create the table without the constraint and then add it later with an ALTER TABLE statement.

  1.  Oracle Built-In Datatypes 

A datatype associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a procedure or function. These properties cause Oracle to treat values of one datatype differently from values of another datatype. For example, Oracle can add values of NUMBER datatype, but not values of RAW datatype.

Table bellow summarizes the information about some Oracle built-in datatype that may be important to define lab tables.

Datatype

Description

Column Length and Default

CHAR (size)  

Fixed-length character data of length size bytes  

Fixed for every row in the table (with trailing blanks); maximum size is 2000 bytes per row, default size is 1 byte per row.  

VARCHAR2 (size)  

Variable-length character data  

Variable for each row, up to 4000 bytes per row. A maximum size must be specified.  

NUMBER (p,s)  

Variable-length numeric data.: Maximum precision p and/or scale s is 38  

Variable for each row. The maximum space required for a given column is 21 bytes per row.  

DATE  

Fixed-length date and time data, ranging from Jan. 1, 4712 B.C.E. to Dec. 31, 4712 C.E.  

Fixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-YY) specified by NLS_DATE_FORMAT parameter.  

BLOB  

Unstructured binary data  

Up to 232 - 1 bytes, or 4 gigabytes.  

RAW (size)  

Variable-length raw binary data  

Variable for each row in the table, up to 2000 bytes per row. A maximum size must be specified. Provided for backward compatibility.  

LONG RAW  

Variable-length raw binary data  

Variable for each row in the table, up to 231 - 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.  

ROWID  

Binary data representing row addresses  

Fixed at 10 bytes (extended ROWID) or 6 bytes (restricted ROWID) for each row in the table.  

  1.  Comparison Semantics 

Oracle compares CHAR values using blank-padded comparison semantics. If two values have different lengths, then Oracle adds blanks at the end of the shorter value, until the two values are the same length. Oracle then compares the values character-by-character up to the first character that differs. The value with the greater character in the first differing position is considered greater. Two values that differ only in the number of trailing blanks are considered equal.

Oracle compares VARCHAR2 values using non-padded comparison semantics. Two values are considered equal only if they have the same characters and are of equal length. Oracle compares the values character-by-character up to the first character that differs. The value with the greater character in that position is considered greater.

  1.  Using the NUMBER Datatype 

Use the NUMBER datatype to store real numbers in a fixed-point or floating-point format. Numbers using this datatype are guaranteed to be portable among different Oracle platforms, and offer up to 38 decimal digits of precision. You can store positive and negative numbers of magnitude 1 x 10-130 to 9.99...x10125, as well as zero, in a NUMBER column.

For numeric columns you can specify the column as a floating-point number:

Column_name NUMBER

You can specify a precision (total number of digits) and scale (number of digits to right of decimal point):

Column_name NUMBER (<precision>, <scale>)

For example, the number 123.45 has precision 5 and scale - 2

Although not required, specifying the precision and scale for numeric fields provides extra integrity checking on input. If a precision is not specified, then the column stores values as given. Table bellow  shows examples of how data would be stored using different scale factors.

Input Data

Stored As

Specified As

7,456,123.89  

NUMBER  

7456123.89  

7,456,123.89  

NUMBER (9)  

7456124  

7,456,123.89  

NUMBER (9,2)  

7456123.89  

7,456,123.89  

NUMBER (9,1)  

7456123.9  

7,456,123.89  

NUMBER (6)  

(not accepted, exceeds precision)  

7,456,123.89  

NUMBER (7, -2)  

7456100  

  1.  Using the DATE Datatype 

Use the DATE datatype to store point-in-time values (dates and times) in a table. The DATE datatype stores the century, year, month, day, hours, minutes, and seconds.

Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.

For input and output of dates, the standard Oracle default date format is DD-MON-YY. For example:

'13-NOV-92'

To change this default date format on an instance-wide basis, use the NLS_DATE_FORMAT parameter. To change the format during a session, use the ALTER SESSION statement. To enter dates that are not in the current default date format, use the TO_DATE function with a format mask. For example:

TO_DATE ('November 13, 1992', 'MONTH DD, YYYY')

If the date format DD-MON-YY is used, then YY indicates the year in the 20th century (for example, 31-DEC-92 is December 31, 1992). If you want to indicate years in any century other than the 20th century, then use a different format mask, as shown above.

Time is stored in 24-hour format #HH:MM:SS. By default, the time in a date field is 12:00:00 A.M. if no time portion is entered. In a time-only entry, the date portion defaults to the first day of the current month. To enter the time portion of a date, use the TO_DATE function with a format mask indicating the time portion, as in:

INSERT INTO Birthdays_tab (bname, bday) VALUES

    ('ANNIE',TO_DATE('13-NOV-92 10:56 A.M.','DD-MON-YY HH:MI A.M.'));

SQl standard propose addition form of date literal (that is date without time). That is also supported in Oracle. This form is the following:

DATE ‘YYYY-VV-DD’

Where

DATE – is a keyword

YYYY-MM-DD - date in the format

- YYYY – four digits of year

-  MM – two digits of month$

- DD – two digits of day.

Examples: DATE ‘1968-01-17’, DATE ‘2010-01-28’

  1.  ANSI/ISO Datatypes 

You can define columns of tables in an Oracle database using ANSI/ISO datatypes. Oracle internally converts such datatypes to Oracle datatypes.

The ANSI datatype conversions to Oracle datatypes are shown in table bellow. The ANSI/ISO datatypes NUMERIC, DECIMAL, and DEC can specify only fixed-point numbers. For these datatypes, s defaults to 0.

ANSI SQL Datatype 

Oracle Datatype 

CHARACTER (n), CHAR (n)  

CHAR (n)  

NUMERIC (p,s), DECIMAL (p,s), DEC (p,s)  

NUMBER (p,s)  

INTEGER, INT, SMALLINT  

NUMBER (38)  

FLOAT (p)  

FLOAT (p)  

REAL  

FLOAT (63)  

DOUBLE PRECISION  

FLOAT (126)  

CHARACTER VARYING(n), CHAR VARYING(n)  

VARCHAR2 (n)  

  1.  Altering Tables 
    1.  Syntax

  1.  Purpose

To alter the definition of a table. Alter a table in an Oracle database for any of the following reasons:

  •  To add one or more new columns to the table.
  •  To add one or more integrity constraints to a table.
  •  To modify an existing column's definition (datatype, length, default value, and NOT NULL integrity constraint) .
  •  To drop integrity constraints associated with the table.
  •  To drop column.
    1.  Prerequisites 

The table must be in your own schema, or you must have ALTER privilege on the table, or you must have ALTER ANY TABLE system privilege. For some operations you may also need the CREATE ANY INDEX privilege.

To enable a UNIQUE or PRIMARY KEY constraint, you must have the privileges necessary to create an index on the table. You need these privileges because Oracle creates an index on the columns of the unique or primary key in the schema containing the table.

  1.  Keywords and Parameters

The clauses described below have specialized meaning in the ALTER TABLE statement. For descriptions of the remaining keywords, see “Table creation”

schema  

is the schema containing the table. If you omit schema, Oracle assumes the table is in your own schema.  

table  

is the name of the table to be altered.  

ADD 

relational _properties  

 

adds a column or integrity constraint. If you add a column, the initial value of each row for the new column is null. For a description of the keywords and parameters of this clause, see “Table creation”.

If you previously created a view with a query that used the "SELECT *" syntax to select all columns from table, and you now add a column to table, Oracle does not automatically add the new column to the view. To add the new column to the view, re-create the view using the CREATE VIEW statement with the OR REPLACE clause.  

You cannot add a column with a NOT NULL constraint if table has any rows.

column_constraint  

adds or removes a NOT NULL constraint to or from an existing column. You cannot use this clause to modify any other type of constraint using ALTER TABLE. See Lab2 to consult with constraint clause.  

table_constraint  

adds or modifies an integrity constraint on the table. See Lab2 to consult with constraint clause .  

MODIFY 

modify_column_options  

modifies the definition of an existing column. If you omit any of the optional parts of the column definition (datatype, default value, or column constraint), these parts remain unchanged.

  •  You can change a CHAR column to VARCHAR2 (or VARCHAR) and a VARCHAR2 (or VARCHAR) to CHAR only if the column contains nulls in all rows or if you do not attempt to change the column size.
  •  You can change any column's datatype or decrease any column's size if all rows for the column contain nulls.
  •  You can always increase the size of a character or raw column or the precision of a numeric column, whether or not all the columns contain nulls.

column  

is the name of the column to be added or modified.

The only type of integrity constraint that you can add to an existing column using the MODIFY clause with the column constraint syntax is a NOT NULL constraint, and only if the column contains no nulls. To define other types of integrity constraints (UNIQUE, PRIMARY KEY, referential integrity, and CHECK constraints) on existing columns, using the ADD clause and the table constraint syntax.  

datatype  

specifies a new datatype for an existing column.

You can omit the datatype only if the statement also designates the column as part of the foreign key of a referential integrity constraint. Oracle automatically assigns the column the same datatype as the corresponding column of the referenced key of the referential integrity constraint.

DEFAULT  

specifies a new default for an existing column. Oracle assigns this value to the column if a subsequent INSERT statement omits a value for the column. If you are adding a new column to the table and specify the default value, Oracle inserts the default column value into all rows of the table.  

The datatype of the default value must match the datatype specified for the column. The column must also be long enough to hold the default value. A DEFAULT expression cannot contain references to other columns.

drop_constraint_clause  

drops an integrity constraint from the database. Oracle stops enforcing the constraint and removes it from the data dictionary. You can specify only one constraint for each drop_constraint_clause, but you can specify multiple drop_constraint_clauses in one statement.

PRIMARY KEY drops the table's PRIMARY KEY constraint

UNIQUE   drops the UNIQUE constraint on the specified columns

CONSTRAINT   drops the integrity constraint named constraint

CASCADE   drops all other integrity constraints that depend on the

 dropped integrity constraint

You cannot drop a UNIQUE or PRIMARY KEY constraint that is part of a referential integrity constraint without also dropping the foreign key. To drop the referenced key and the foreign key together, use the CASCADE clause. If you omit CASCADE, Oracle does not drop the PRIMARY KEY or UNIQUE constraint if any foreign key references it

drop_column_clause 

lets you free space in the database by dropping columns you no longer need, or by marking them to be dropped at a future time when the demand on system resources is less.

SET UNUSED

marks one or more columns as unused. Specifying this clause does not actually remove the target columns from each row in the table (that is, it does not restore the disk space used by these columns). Therefore, the response time is faster than it would be if you execute the DROP clause.

Unused columns are treated as if they were dropped, even though their column data remains in the table's rows. After a column has been marked as unused, you have no access to that column. A "SELECT *" query will not retrieve data from unused columns. In addition, the names and types of columns marked unused will not be displayed during a DESCRIBE, and you can add to the table a new column with the same name as an unused column.

DROP

removes the column descriptor and the data associated with the target column from each row in the table. If you explicitly drop a particular column, all columns currently marked as unused in the target table are dropped at the same time.

When the column data is dropped:

  •  All indexes defined on any of the target columns are also dropped.
  •  All constraints that reference a target column are removed. 

Note: If a constraint also references a nontarget column, Oracle returns an error and does not drop the column unless you have specified the CASCADE CONSTRAINTS clause. If you have specified that clause, Oracle removes all constraints that reference any of the target columns

DROP UNUSED COLUMNS  

removes from the table all columns currently marked as unused. Use this statement when you want to reclaim the extra disk space from unused columns in the table. If the table contains no unused columns, the statement returns with no errors.

column  

specifies one or more columns to be set as unused or dropped. Use the COLUMN keyword only if you are specifying only one column. If you specify a column list, it cannot contain duplicates.

CASCADE CONSTRAINTS  

drops all referential integrity constraints that refer to the primary and unique keys defined on the dropped columns, and drops all multicolumn constraints defined on the dropped columns. If any constraint is referenced by columns from other tables or remaining columns in the target table, then you must specify CASCADE CONSTRAINTS. Otherwise, the statement aborts and an error is returned.

INVALIDATE

Note: Currently, Oracle executes this clause regardless of whether you specify the keyword INVALIDATE.

Oracle invalidates all dependent objects, such as views, triggers, and stored program units. Object invalidation is a recursive process. Therefore, all directly dependent and indirectly dependent objects are invalidated.

An object invalidated by this statement is automatically revalidated when next referenced. You must then correct any errors that exist in that object before referencing it.

DROP COLUMNS CONTINUE  

continues the drop column operation from the point at which it was interrupted. Submitting this statement while the table is in a valid state results in an error.


When altering the column definitions of a table, you can only increase the length of an existing column, unless the table has no records. You can also decrease the length of a column in an empty table. For columns of datatype
CHAR, increasing the length of a column might be a time consuming operation that requires substantial additional storage, especially if the table contains many rows. This is because the CHAR value in each row must be blank-padded to satisfy the new column length.

If you change the datatype (for example, from VARCHAR2 to CHAR), then the data in the column does not change. However, the length of new CHAR columns might change, due to blank-padding requirements.

Altering a table has the following implications:

If a new column is added to a table, then the column is initially null. You can add a column with a NOT NULL constraint to a table only if the table does not contain any rows.

If a view or PL/SQL program unit depends on a base table, then the alteration of the base table might affect the dependent object, and always invalidates the dependent object.

  1.  Dropping Tables 
    1.  Syntax

  1.  Purpose 

To remove a table and all its data from the database.

  1.  Prerequisites 

The table must be in your own schema or you must have the DROP ANY TABLE system privilege.

  1.  Keywords and Parameters 

schema  

is the schema containing the table. If you omit schema, Oracle assumes the table is in your own schema.  

table  

is the name of the table to be dropped. Oracle automatically performs the following operations:  

  •  Removes all rows from the table (as if the rows were deleted).
  •  Drops all the table's indexes and triggers regardless of who created them or whose schema contains them.
  •  If the table is a base table for a view or if it is referenced in a stored procedure, function, or package, Oracle invalidates these dependent objects but does not drop them. You cannot use these objects unless you re-create the table or drop and re-create the objects so that they no longer depend on the table.

CASCADE CONSTRAINTS  

drops all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this clause, and such referential integrity constraints exist, Oracle returns an error and does not drop the table.  

  1.  Lab tasks
    1.  Tables creation

Create the following tables

Table name

Column name

Column

data type

Length

(or precision)

Scale

FACULTY    

FacNo

integer

Name

char

20

Chief

varchar2

50

Building

char

5

DEPARTMENT

DepNo

integer

FacNo

integer

Name

varchar2

50

Head

varchar2

50

Building

number

2

Fund

number

7

2

TEACHER

TchNo

integer

DepNo

integer

Name

varchar2

50

Post

varchar2

30

Tel

char

7

Hiredate

date

SGROUP

GrpNo

integer

DepNo

integer

Course

char

1

Num

char

3

Quantity

number

2

Curator

integer

SUBJECT

SbjNo

integer

Name

varchar2

50

ROOM

RomNo

integer

Num

number

4

Seats

number

3

LECTURE

TchNo

integer

GrpNo

integer

SbjNo

integer

RomNo

integer

Type

varchar2

25

Day

char

10

week

number

1

  1.  Altering the tables

Do the following changes in the tables

Table name

Column name

Column

data type

Length

(or precision)

Scale

Action

FACULTY    

Name

varchar2

50

change type and length

Dean

varchar2

50

change column name

Fund

number

7

2

add new column

DEPARTMENT

Building

char

5

change type and length

TEACHER

Salary

number

6

2

add new column

Commission

number

6

2

add new column

SGROUP

Course

number

1

change type

Num

number

3

change type

Rating

integer

add new column

ROOM

Floor

integer

add new column

Building

char

5

add new column

LECTURE

Lesson

number

1

add new column

  1.  Deleting the tables

Delete all the created tables

  1.  Control questions

Please, give answers to the following questions:

PAGE  2


 

А также другие работы, которые могут Вас заинтересовать

24021. АВТОРСКОЕ ТВОРЧЕСТВО ЖУРНАЛИСТА 143.68 KB
  ОСОБЕННОСТИ ЖУРНАЛИСТСКОГО ТЕКСТА журналистский текст несет в себе особый вид информации – журналистскую информацию актуальность связь конкретной ситуации с назревшей жизненной проблемой Чем глубже понял эту связь журналист и чем новее знание которое он получил в итоге тем актуальнее будет его публикация и тем больший резонанс она вызовет. Лазутина предлагает следующие правила монтажа журналистского текста: 1 соблюдать четкость предъявления текстовых элементов в их собственных границах Каждый текстовый элемент – микросущность 2...
24022. Наружная сонная артерия, ее топография, ветви и области, кровоснабжаемые ими 227 KB
  carotis externa является одной из двух конечных ветвей общей сонной артерии. Она отделяется от общей сонной артерии в пределах сонного треугольника на уровне верхнего края щитовидного хряща. Вначале она расположена медиальнее внутренней сонной артерии а затем латеральнее ее. Начальная часть наружной сонной артерии снаружи покрыта грудиноключичнососцевидной мышцей а в области сонного треугольника поверхностной пластинкой шейной фасции и подкожной мышцей шеи.
24023. Поверхностные и глубокие вены нижней конечности, их анатомия, топография, анастомозы 263 KB
  К органам иммунной системы принадлежат костный мозг в котором лимфоидная ткань тесно связана с кроветворной тимус вилочковая железа лимфатические узлы селезенка скопления лимфоидной ткани в стенках полых органов пищеварительной дыхательной систем и. Многочисленные лимфатические узлы лежат на путях следования лимфы от органов и тканей в венозную систему. Лимфатические капилляры тимуса которых больше в корковом веществе образуют в паренхиме органа сети из которых формируются лимфатические сосуды впадающие в передние средостенные...
24024. Ядра серого вещества спинного мозга, их назначение. Локализация проводящих путей в белом веществе спинного мозг 239.5 KB
  Ядра серого вещества спинного мозга их назначение. Локализация проводящих путей в белом веществе спинного мозга. Задние рога спинного мозга представлены преимущественно более мелкими клетками. Серое вещество задних рогов спинного мозга неоднородно.
24025. Строение простой и сложной рефлекторных дуг. Классификация проводящих путей головного и спинного мозга 236 KB
  Рефлекторная дуга это цпь нервных клеток обязательно включающая первый чувствительный и последний двигательный или секреторный нейроны по которым импульс движется от места возникновения к месту приложения мышцы железы. В трехнейронной рефлекторной дуге первый нейрон представлен чувствительной клеткой по которой импульс от места возникновения в чувствительном нервном окончании рецепторе лежащем в коже слизистой оболочке или в органах движется вначале по периферическому отростку в составе нерва а затем по...
24026. Крестцовое сплетение, его топография, нервы, области иннервации 284.5 KB
  Крестцовое сплетение его топография нервы области иннервации. Крестцовое сплетение plexus sacralis образовано передними ветвями V поясничного Lv верхних четырех крестцовых Si Siv и части передней ветви IV поясничного Lfv спинномозговых нервов. Передняя ветвь V поясничного спинномозгового нерва а также присоединяющаяся к нему часть передней ветви IV поясничного нерва образует пояснйчнокрестцовый ствол truncus lumbosacralis. Он спускается в полость малого таза и на передней поверхности грушевидной мышцы соединяется с передними...
24027. Кость как орган; ее развитие, строение, рост. Классификация костей 251 KB
  Кости разнообразны по величине и форме занимают определенное положение в теле. имеет удлиненную цилиндрической или трехгранной формы среднюю часть тело кости диафиз diaphysis от греч. Участок кости где диафиз переходит в эпифиз выделяют как м е т а ф и з metaphysis. Трубчатые кости составляют скелет конечностей выполняют функции рычагов.
24028. Голеностопный сустав: строение, форма, движения; мышцы, действующие на этот сустав, их кровоснабжение и иннервация; рентгеновское изображение голеностопного сустава 211 KB
  Строение мышцы как органа. Скелетные мышцы прикрепляясь к костям приводят их в вижение участвуют в образовании стенок полостей тела: ротовой грудной брюшной таза входят в состав стенок некоторых внутренних органов глотка верхняя часть пищевода гортань находятся в числе вспомогательных органов глаза глазодвигательные мышцы оказывают действие на слуховые косточки в барабанной полости. Под воздействием импульсов поступающих по нервам из центргльной нервной системы скелетные мышцы действуют на костные рычаги активно изменяют...
24029. Паховый канал, его стенки, глубокое и поверхностное кольцо 212.5 KB
  Он проходит в толще передней стенки живота у нижней её границы от глубокого пахового кольца ббразованного поперечной фасцией над серединой паховои связки до поверхностногог пахового кольца находящегося над верхней ветвью лобковой кости между латеральной и медиальной ножками апоневроза наружной косой мышцы живота рис. Оно ограничено ножками апоневроза наружной косой мышцы живота: сверху_ медиальной crus mediate снизу латер а л ь н о й cms laterdle. reflexum состоящая из ответвления волокон паховой связки и латеральной ножки...