1. .
2. .
3. .
, .
1. - , , . . .: . .: , 2003. 1088 .
2. . . , 8- : . . .: , 2005. 1328 .
3. .. : ; ; ; .: , 2002. 800 .
2
CASE- Oracle Designer CASE Studio.
Oracle Designer , .
Oracle Designer:
Entity Relationship Diagrammer ER-;
Database Desing Transformer ER- (Server Model)
Design Editor (Server Model)
Generate Database from Server Model SQL
CASE Studio ER- t IDEF1X. CASE Studio , , .. : ( ), ( ), , , (), . ( Database Convertion) . Generate script.
1. ER- Oracle Designer
| |
| () (, ) |
| (1,0):(N,1) |
| (1,0):(N,0) |
| (1,1):(N,1) |
| (1,1):(1,1) |
| (N,0):(N,0) |
| # | |
| * | (NOT NULL) |
| O | (NULL) |
2. ER- CASE Studio
| |
| |
| (0,N):(1,1) |
| (1,N):(1,1) |
| (1,1):(1,1) |
| (0,1):(0,1) |
| (N:N) |
| (1,1):(1,N) |
| (1,1):(0,N) |
| (PK) | |
| (FK) |
|
|
|
1. .. : ; ; ; .: , 2002. 800 .
2. . , 9- : . . .: , 2005. 864 .
3. , - . Oracle Desiner. , 2- .: Ȼ, 1999. 592 .
4. .., .., .. : , 5- .: -; .: , 2006. 736 .
5. http://www.idefinfo.ru ( )
6. http://www.intuit.ru/department/database/basedbw/3/3.html ( CASEStudio).
7. http://www.interface.ru/oracle/des2000x.html ( Oracle Designer).
3
3. SQL
| Data Definition Language (DDL) | CREATE TABLE | |
| DROP TABLE | ||
| ALTER TABLE | ||
| CREATE INDEX | ||
| DROP INDEX | ||
| CREATE VIEW | ||
| DROP VIEW | ||
| Data Manipulation Language (DML) | SELECT | |
| UPDATE | ||
| INSERT | ||
| DELETE |
4.
| SUM([DISTINCT] ) | [] | , NULL |
| AVG([DISTINCT] ) | [] | , NULL |
| COUNT([DISTINCT] ) | [] | , NULL |
| COUNT(*) | NULL | |
| MAX() | , NULL | |
| MIN() | , NULL |
SQL .
1.
, CREATE TABLE.
CREATE TABLE films (
film_id INTEGER NOT NULL,
film_name VARCHAR(100) NOT NULL,
film_time time,
film_director VARCHAR(50) NOT NULL,
film_actors VARCHAR(255),
film_year INTEGER NOT NULL,
PRIMARY KEY (film_id))
FILMS , .
2.
ALTER TABLE.
ALTER TABLE film_distributions ADD FOREIGN KEY (film_id) REFERENCES films(film_id) ON DELETE CASCADE
3.
DROP TABLE
DROP TABLE films
4.
SQL- SELECT.
|
|
|
SELECT * FROM films
WHERE films.film_year=1999
1999 :
| FILM_ID | FILM_NAME | FILM_TIME | FILM_DIRECTOR | FILM_ACTORS | FILM_YEAR |
| Ghost Dog: The Way of the Samurai | Jim Jarmusch | Forest Whitaker,John Tormey,Cliff Gorman,Henry Silva,Isaach de Bankole,Frank Minucci | |||
| Man on the moon | Milos Forman | Jim Carrey,Danny DeVito,Courtney Love,Paul Giamatti,Vincent Schiavelli |
:
1. WHERE;
2. LEFT (RIGHT, FULL) OUTER JOIN.
.
5.
SELECT clients.client_fio, rented_films.rent_start_date, returned_rented_films.rent_end_date
FROM rented_films LEFT OUTER JOIN returned_rented_films ON returned_rented_films.rent_id=rented_films.rent_id,
clients
WHERE clients.client_id=rented_films.client_id
| CLIENT_FIO | RENT_START_DATE | RENT_END_DATE |
| 28.06.2006 | 29.08.2006 | |
| 12.09.2006 | 13.09.2006 | |
| 11.10.2005 | 13.10.2005 | |
| 01.05.2006 | 02.05.2006 | |
| 05.06.2006 | 06.06.2006 | |
| 13.09.2006 | NULL |
SELECT clients.client_fio, rented_films.rent_start_date, returned_rented_films.rent_end_date
FROM rented_films,
returned_rented_films,
clients
WHERE clients.client_id=rented_films.client_id AND
returned_rented_films.rent_id=rented_films.rent_id
| CLIENT_FIO | RENT_START_DATE | RENT_END_DATE |
| 12.09.2006 | 13.09.2006 | |
| 28.06.2006 | 29.08.2006 | |
| 28.06.2006 | 29.08.2006 | |
| 01.05.2006 | 02.05.2006 | |
| 05.06.2006 | 06.06.2006 | |
| 11.10.2005 | 13.10.2005 |
. WHERE , . OUTER JOIN JOIN. , , , , NULL-.
6.
, - . , . CREATE VIEW.
client_list .
CREATE VIEW client_list AS
SELECT clients.client_fio, rented_films.rent_start_date, returned_rented_films.rent_end_date
FROM rented_films LEFT OUTER JOIN returned_rented_films ON returned_rented_films.rent_id=rented_films.rent_id, clients
WHERE clients.client_id=rented_films.client_id
7.
( ).
SELECT film_name,cnt_clients
FROM films,
(SELECT film_id, COUNT(DISTINCT client_id) AS cnt_clients
FROM rented_films GROUP BY film_id) cnt
WHERE films.film_id=cnt.film_id
ORDER BY film_name
, . , 1 .
8.
SQL INSERT.
INSERT INTO medium_type_directory (medium_type) VALUES ('dvd')
|
|
|
medium_type_directory .
9.
DELETE - .
DELETE FROM clients WHERE client_id=15
client_id 15.
9.
, UPDATE.
UPDATE clients
SET client_phone_number=795-55-78-48
WHERE client_fio=John N. Doe
John N. Doe .
1. ., , . SQL, 4- : . . .: , 2001. 352 .
2. ., . SQL. 3- : . . .: , 2003. 896 .
3. . , 9- : . . .: , 2005. 864 .
4. http://www.firststeps.ru/sql/oracle/oracle3.html (SQL, Oracle)
5. http://www.sql-ex.ru (SQL, Oracle)
6. http://megalib.com/items.php?idsubject=6 ( SQL)
