, :
1
| AngPROGRAMMA | ||
| nazvanie | VARCHAR2(20) | PRIMARY KEY |
| firma | VARCHAR2(20) | FOREIGN KEY AngPROIZVODITEL |
| tip | VARCHAR2(20) | |
| podderjka | VARCHAR2(20) |
1 . :
nazvanie - ;
firma ;
tip ( , ..);
podderjka .
:
VARCHAR2(L) - L .
2
| AngPROIZVODITEL | ||
| firma | VARCHAR2(20) | PRIMARY KEY |
| strana | VARCHAR2(20) | |
| svyaz | NUMBER |
2 . :
firma- - ;
strana -;
svyaz .
:
VARCHAR2(L) - L ;
NUMBER - .
3
| AngVERSIYA | ||
| nazvanie | VARCHAR2(20) | PRIMARY KEY FOREIGN KEY AngPROGRAMMA |
| versiya | NUMBER | PRIMARY KEY |
| naznachenie | VARCHAR2(20) | |
| dataV | NUMBER | |
| stoimost | NUMBER | stoimost>0 |
3 . :
nazvanie - ;
versiya - ;
naznachenie ;
dataV ;
stoimost- , .
:
VARCHAR2(L) - L ;
NUMBER - .
4
| AngTEXNXARAKT | ||
| nazvanie | VARCHAR2(20) | PRIMARY KEY FOREIGN KEY AngVERSIYA |
| versiya | NUMBER | PRIMARY KEY FOREIGN KEY AngVERSIYA |
| tipSistTreb | VARCHAR2(20) | PRIMARY KEY |
| znachenieSistTreb | NUMBER | |
| yazik | VARCHAR2(20) |
4 . :
nazvanie - ;
versiya - ;
tipSistTreb - ;
znachenieSistTreb ;
yazik - .
:
VARCHAR2(L) - L ;
NUMBER - .
|
|
|
.
.
SQL :
CREATE TABLE AngPROGRAMMA
(nazvanie VARCHAR2(20) CONSTRAINT pkluch_nazvanie PRIMARY KEY,
firma VARCHAR2(20) CONSTRAINT fkluch_firma REFERENCES AngPROIZVODITEL(firma),
tip VARCHAR2(20),
podderjka VARCHAR2(20))
CREATE TABLE AngPROGRAMMA - .
, :
nazvanie VARCHAR2(20), .. 20 ;
firma , .. 2, VARCHAR2(20), .. 20 ;
tip VARCHAR2(20), .. 20 ;
podderjka VARCHAR2(20), .. 20 .
CONSTRAINT:
PRIMARY KEY ;
CHECK (<>) , , ;
REFERENCES <> (<>) <> <>;
FOREIGN KEY , .. .
, CONSTRAINT . . 3 4.
:
CREATE TABLE AngPROIZVODITEL
(firma VARCHAR2(20) CONSTRAINT pkluch_firma PRIMARY KEY,
strana VARCHAR2(20),
svyaz NUMBER)
CREATE TABLE AngVERSIYA
(nazvanie VARCHAR2(20) CONSTRAINT fkluch_nazvanie REFERENCES AngPROGRAMMA(nazvanie),
versiya NUMBER,
naznachenie VARCHAR2(20),
dataV NUMBER,
stoimost NUMBER(5) CONSTRAINT ch_stoimost CHECK (stoimost>0),
CONSTRAINT pkluch_AnzVERSIYA PRIMARY KEY (nazvanie,versiya))
CREATE TABLE AngTEXNXARAKT
(nazvanie VARCHAR2(20),
versiya NUMBER,
tipSistTreb VARCHAR2(20),
znachenieSistTreb NUMBER,
yazik VARCHAR2(20),
CONSTRAINT pkluch_AngTEXNXARAKT PRIMARY KEY (nazvanie,versiya,tipSistTreb),
CONSTRAINT fkluch_nazvanie_versiya FOREIGN KEY(nazvanie,versiya)
REFERENCES AngVERSIYA(nazvanie,versiya))
:
TABLE CREATED .
SQL :
DROP TABLE AngTEXNXARAKT.
ALTER TABLE c MODIFY, : , , , NOT NULL. MODIFY .
:
.
ALTER TABLE AngVERSIYA
MODIFY (stoimost NUMBER(4))
ALTER TABLE c ADD.
:
.
ALTER TABLE AngVERSIYA
ADD (CONSTRAINT nn_Stoimost NOT NULL Stoimost)
|
|
|
.
SQL .
AngPROGRAMMA:
INSERT INTO, , . .
INSERT INTO AngPROGRAMMA (nazvanie,firma,tip,podderjka)
VALUES, , :
INSERT INTO AngPROGRAMMA (nazvanie,firma,tip,podderjka)
VALUES('WINDOWS','MICROSOFT','OC','MICROSOFT.RU')
INSERT INTO AngPROGRAMMA (nazvanie,firma,tip,podderjka)
VALUES('STUDENT','TRIADA','game','TRIADA.RU')
INSERT INTO AngPROGRAMMA (nazvanie,firma,tip,podderjka)
VALUES('MACINTOUCH','APPLE','OC','APPLE.RU')
INSERT INTO AngPROGRAMMA (nazvanie,firma,tip,podderjka)
VALUES('DELPHI','BORLAND','PL','BORLAND.RU')
INSERT INTO AngPROGRAMMA (nazvanie,firma,tip,podderjka)
VALUES('WEBCAMERA','TOSHIBA','camera','TOSHIBA.RU')
INSERT INTO AngPROGRAMMA (nazvanie,firma,tip,podderjka)
VALUES('NMAP','LINUX','SS','LINUX.RU')
AngPROIZVODITEL:
INSERT INTO AngPROIZVODITEL(firma,strana,svyaz)
VALUES('MICROSOFT','USA',1234567)
INSERT INTO AngPROIZVODITEL(firma,strana,svyaz)
VALUES('TRIADA','Germany',7654321)
INSERT INTO AngPROIZVODITEL(firma,strana,svyaz)
VALUES('APPLE','ENGLAND',9876543)
INSERT INTO AngPROIZVODITEL(firma,strana,svyaz)
VALUES('BORLAND','USA',3456789)
INSERT INTO AngPROIZVODITEL(firma,strana,svyaz)
VALUES('TOSHIBA','Japan',5678900)
INSERT INTO AngPROIZVODITEL(firma,strana,svyaz)
VALUES('LINUX','USA',9119119)
.
UPDATE, .
1:
2 , 10000 .
UPDATE AngVERSIYA SET dataV=dataV+2
WHERE stoimost<10000
2:
, 500.
UPDATE AngTEXNXARAKT SET yazik=german
WHERE znachenieSistTreb<500
DELETE, .
:
, : 5 8000.
DELETE FROM AngVERSIYA
WHERE versiya<5 AND stoimost<8000
SELECT.
1:
, .
SELECT * FROM AngPROGRAMMA
2:
, .
SELECT nazvanie FROM AngVERSIYA
ORDER BY dataV
3:
, 2005 .
SELECT SUM(stoimost) FROM AngVERSIYA
WHERE dataV>2005
4:
, "NT".
SELECT nazvanie FROM AngPROGRAMMA
WHERE nazvanie LIKE '%NT%'
5:
: , ; . , 10000 .
SELECT nazvanie SUM(stoimost) FROM AngPROGRAMMA, AngVERSIYA
WHERE AngPROGRAMMA. Nazvanie= AngVERSIYA. Nazvanie
GROUP BY nazvanie. AngPROGRAMMA
HAVING SUM(stoimost)>10000
