6811
Data manipulation in SQL Oracle
Лабораторная работа
Информатика, кибернетика и программирование
Data manipulation in SQL Oracle Purpose of the lab To study SQL Oracle possibilities in inserting, updating and deleting rows in a tables. To acquire practical skills in inserting, updating and deleting rows in a tables by using SQ...
Английский
2013-01-08
110 KB
10 чел.
Data manipulation in SQL Oracle
There are three SQL statements that enable you to manipulate the data within a database's table. These statements are the following:
To add rows to a table.
For you to insert rows into a table, the table must be in your own schema or you must have INSERT privilege on the table.
If you have the INSERT ANY TABLE system privilege, you can also insert rows into any table.
schema |
is the schema containing the table. If you omit schema, Oracle assumes the table or view is in your own schema. |
table |
is the name of the table into which rows are to be inserted. |
column
|
is a column of the table. In the inserted row, each column in this list is assigned a value from the values_clause or the subquery. If you omit one of the table's columns from this list, the column's value for the inserted row is the column's default value as specified when the table was created. For more information on default column values, see Lab1. If any of these columns has a NOT NULL constraint and it does not have a default value, then Oracle returns an error indicating that the constraint has been violated and rolls back the INSERT statement. If you omit the column list altogether, the values_clause or query must specify values for all columns in the table. |
values_clause |
specifies a row of values to be inserted into the table or view. You must specify a value in the values_clause for each column in the column list. If you omit the column list, then the values_clause must provide values for every column in the table. |
subquery
|
is a subquery that returns rows that are inserted into the table. If the subquery selects no rows, Oracle inserts no rows into the table. When specified with VALUES, the subquery returns values to be inserted into one row. When specified without VALUES, the subquery can return values to be inserted into more than one row. The subquery can refer to any table, including the target table of the INSERT statement. The select list of this subquery must have the same number of columns as the column list of the INSERT statement. If you omit the column list, then the subquery must values for all columns in a table. |
t_alias |
provides a correlation name for the table to be referenced elsewhere in the statement. |
VALUES Examples
The following statement inserts a row into the FACULTY table:
INSERT INTO FACULTY
VALUES (1, 'informatics', 'Sidorov', '5', 93517);
The following statement inserts a row with six columns into the TEACHER table. One of these columns is assigned NULL: Values of two columns (Tel and Commission) are not defined. So Tel column the value NULL is assigned and Commission column takes the value 0 (default value):
INSERT INTO TEACHER (TchNo, DepNo, name, post, hiredate, salary)
VALUES (15, 7, 'Ivanov', NULL, NULL, TO_DATE('25.07.2001', 'DD.MM.YYYY'), 350.00);
Subquery Example
The following statement copies professors whose Commission exceeds 25% of salary into the BONUS table:
INSERT INTO bonus
SELECT *
FROM TEACHER
WHERE Commission > 0.25 * salary AND post 'proessor';
To change existing values in a table.
For you to update values in a table, the table must be in your own schema or you must have UPDATE privilege on the table.
The UPDATE ANY TABLE system privilege also allows you to update values in any table or any view's base table.
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 updated. |
|
set_clause
|
column |
is the name of a column of the table that is to be updated. If you omit a column of the table from the set_clause, that column's value remains unchanged. |
subquery |
is a subquery that returns exactly one row for each row updated. If you specify only one column in the set_clause, the subquery can return only one value. If you specify multiple columns in the set_clause, the subquery must return as many values as you have specified columns. If the subquery returns no rows, then the column is assigned a null. |
|
expr |
is the new value assigned to the corresponding column. |
|
where_clause |
restricts the rows updated to those for which the specified condition is TRUE. If you omit this clause, Oracle updates all rows in the table or view. See the syntax description of conditions in Lab2 . The where_clause determines the rows in which values are updated. If you do not specify the where_clause, all rows are updated. For each row that satisfies the where_clause, the columns to the left of the equals (=) operator in the set_clause are set to the values of the corresponding expressions on the right. The expressions are evaluated as the row is updated. |
Simple Examples
The following statement gives null Commission to all teaches with the post assistant:
UPDATE TEACHER
SET Commission = NULL
WHERE post = 'assistant';
The following statement moves Jones Department 20 with a 100 salary raise (assuming there is only one Jones):
UPDATE TEACHER
SET DepNo =20, salary = salary + 100
WHERE name = 'jones';
Complex Example
This example shows the following syntactic constructs of the UPDATE statement:
UPDATE TEACHER a
SET DepNo =
(SELECT DepNo
FROM deptartment
WHERE name = 'data bases and information systems'),
(salary, Commission) =
(SELECT 1.1*AVG(salary), 1.5*AVG(Commission)
FROM TEACHER b
WHERE a.DepNo = b.DepNo)
WHERE DepNo IN
(SELECT DepNo
FROM DEPARTMENT
WHERE FacNo = 1);
The above UPDATE statement performs the following operations:
To remove rows from a table.
For you to delete rows from a table, the table must be in your own schema or you must have DELETE privilege on the table.
The DELETE ANY TABLE system privilege also allows you to delete rows from any table.
schema |
is the schema containing the table. If you omit schema, Oracle assumes the table or view is in your own schema. |
table |
is the name of a table from which the rows are to be deleted. |
where_clause
|
deletes only rows that satisfy the condition. The condition can reference the table and can contain a subquery.. If you omit the where_clause, Oracle deletes all rows of the table or view. |
t_alias |
provides a correlation name for the table to be referenced elsewhere in the statement. Table aliases are generally used in DELETE statements with correlated queries. |
Basic Examples
The following statement deletes all rows from a table named DEPARTMENT.
DELETE FROM DEPARTMENT;
The following statement deletes from the TEACHER table all professors who have less than 100 Commission:
DELETE FROM TEACHER
WHERE post = 'professor' AND Commission < 100;
Insert the following rows in tables.
FACULTY |
FacNo |
Name |
Dean |
Building |
Fund |
1 |
informatics |
Sidorov |
5 |
57398.00 |
|
2 |
economy |
Petrov |
3 |
NULL |
|
3 |
linguistics |
Popov |
4 |
NULL |
DEPARTMENT |
DepNo |
FacNo |
Name |
Head |
Building |
Fund |
1 |
1 |
SE |
Sidorov |
5 |
14378.00 |
|
2 |
1 |
CAD |
Perov |
5 |
15000.00 |
|
3 |
1 |
DBMS |
Ivanov |
4 |
22000.00 |
|
4 |
2 |
Accounts |
John |
3 |
NULL |
TEACHER |
TchNo |
DepNo |
Name |
Post |
Tel |
Hiredate |
Salary |
Commission |
1 |
1 |
Andrew |
assistant |
2281319 |
01.02.2001 |
250 |
80 |
|
2 |
1 |
John |
professor |
2281550 |
01.07.2001 |
400 |
150 |
|
3 |
2 |
Bill |
assistant |
NULL |
17.11.2002 |
240 |
80 |
|
4 |
2 |
Albert |
assistant |
NULL |
11.11.2001 |
260 |
100 |
SGROUP |
GrpNo |
DepNo |
Course |
Num |
Quantity |
Curator |
Rating |
1 |
1 |
1 |
101 |
33 |
4 |
20 |
|
2 |
1 |
1 |
102 |
35 |
4 |
22 |
|
3 |
3 |
2 |
205 |
20 |
1 |
15 |
|
4 |
3 |
3 |
305 |
25 |
NULL |
40 |
|
5 |
3 |
4 |
405 |
25 |
2 |
37 |
SUBJECT |
SbjNo |
Name |
1 |
pascal |
|
2 |
C |
|
3 |
OS |
|
4 |
inernet |
|
5 |
dbms |
ROOM |
RomNo |
Num |
Seate |
Floor |
Building |
1 |
101 |
20 |
1 |
5 |
|
2 |
316 |
150 |
3 |
5 |
|
3 |
201 |
150 |
2 |
2 |
|
4 |
202 |
30 |
2 |
5 |
LECTURE |
TchNo |
GrpNo |
SbjNo |
RomNo |
Type |
Day |
Week |
Lesson |
1 |
1 |
1 |
1 |
lecture |
mon |
1 |
1 |
|
1 |
2 |
2 |
1 |
lab |
mon |
1 |
1 |
|
2 |
3 |
3 |
1 |
lecture |
tue |
1 |
3 |
|
2 |
4 |
4 |
2 |
practice |
wed |
1 |
3 |
|
4 |
4 |
5 |
2 |
practice |
thu |
2 |
4 |
|
4 |
4 |
5 |
3 |
lab |
fri |
2 |
1 |
Insert in the FACULTY table information about your faculty/ Because of at that time information about teachers dies not exists, use NULL as a value of foreign key that references to the dean of the faculty.
Insert into DEPARTMENT table information about your department and all departments of teachers that teach you. Because of at that time information about teachers dies not exists, use NULL as a value of foreign key that references to the head of the department
Insert into TEACHER table information about all teaches that teach you, including dean of the faculty and head of the departments. . Teacher subordination is set in such a way:
- Dean of the faculty does not subordinate anybody
- Chief of the department subordinates to the dean
- Teachers that has lectures subordinates to the head of the department
- Teachers that has labs subordinates to the teacher that has lectures in the same subject.
Update FACULTY table by changing NULL in DeanFK column to the references to corresponding teacher
Update DEPARTMENT table by changing NULL in HeadFK column to the references to corresponding teacher.
Insert into SGROUP table information about your group.
Insert into SUBJECT table all disciplines that you study.
Insert into ROOM table information about all room where you have lectures.
Insert into LECTURE table information about all lectures that you have.
Do the following changes in tables data:
Please, give answers to the following questions:
PAGE 7
А также другие работы, которые могут Вас заинтересовать | |||
62955. | Злочини і покарання. Вбивство. Герундій | 38.38 KB | |
The old English rule extended this concept to include not only intentional or deliberate killings but also accidental killings in the course of some other serious crime (such as robbery or rape). | |||
62956. | Типи злочинів. Інші види злочинів | 164.24 KB | |
The European Court of Human Rights was established by the European Convention (домовленість, угода) for the Protection (захист) of Human Rights and Fundamental Freedoms and was set up in Strasbourg in 1959. | |||
62957. | Види покарання | 52.17 KB | |
here are several kinds of punishment available to the courts. In civil cases, the most common punishment is a fine, but specific performance and injunctions may also be ordered. | |||