Voditeli
Voditeli
| . | . | - | . - | |||||
"Voditeli" ( ), ( ), ( ).
Bus
Bus
| ( ) | ||||
| 010 | Golden Drakon | 11.09.2013 | ||
| 113 | ZIL | 8.10.2011 | ||
| 158 | Golden Drakon | 3.09.2012 | ||
| 256 | Hern | 3.12.2013 | ||
| 782 | ZIL | 31.09.2010 |
"Bus" ( ), ( ), ( ).
Marshrut
Marshrut
| . | . | () | - | ||
| 782 | |||||
| 256 | |||||
| 158 | |||||
| 113 | |||||
| 010 |
"Marshrut" ( ), ( ), ( ).
, .
:
CREATE USER Administrator
IDENTIFIED BY password21;
GRANT all privilege
TO Administrator;
:
CREATE TABLE Voditeli
(. NUMBER(6) PRIMARY KEY,
VARCHAR2(50) NOT NULL,
. NUMBER (10) NOT NULL,
VARCHAR2(10) UNIQUE,
VARCHAR2(6) NOT NULL,
VARCHAR2(3) NOT NULL,
VARCHAR2(20) NOT NULL,
NUMBER(4) UNIQUE,
NUMBER(50) UNIQUE);
CREATE TABLE Bus
(. VARCHAR2 (6) PRIMARY KEY,
VARCHAR2(30) NOT NULL,
VARCHAR2(50) NOT NULL,
VARCHAR2(3) UNIQUE,
NUMBER(50) NOT NULL);
CREATE TABLE Marshrut
( . NUMBER(3) PRIMARY KEY,
. VARCHAR2(30) NOT NULL,
. VARCHAR2(50) NOT NULL,
() VARCHAR2(10) UNIQUE,
|
|
|
- . VARCHAR2(10) NOT NULL,
. . VARCHAR2(500) NOT NULL);
:
INSERT INTO Voditeli (., , . , , , , , , )
VALUES (100, , 123456, 2318217, 2007, 1, , 1960, 64261357);
INSERT INTO Voditeli
VALUES (200, , 654321, 8281917, 2001, 2, , 1976, 97123612);
INSERT INTO Pisateli
VALUES (300, , 645321, 9215368, 2008, 3, , 1988, 26243311);
INSERT INTO Voditeli
VALUES (400, , 601543, 5331279, 2006, 4, , 1969, 42565621);
INSERT INTO Voditeli
VALUES (500, , 302532, 6282366, 2012, 5, , 1973, 43254363);
INSERT INTO Bus (., , , , ( ))
VALUES (010, Golden Drakon, 11.09.2013, 3, 12);
INSERT INTO Bus
VALUES (113, ZIL, 8.10.2011, 2, 13);
INSERT INTO Bus
VALUES (158, Golden Drakon, 3.09.2012, 4, 87);
INSERT INTO Bus
VALUES (256, Hern, 3.12.2013, 1, 982);
INSERT INTO Bus
VALUES (782, ZIL, 31.09.2010, 9, 324);
INSERT INTO Marshrut ( , . , . , (), - , . )
VALUES (86, , , 120, 10, 782);
INSERT INTO Marshrut
VALUES (53, , , 180, 28, 256);
INSERT INTO Marshrut
VALUES (33, , , 120, 19, 158);
INSERT INTO Marshrut
VALUES (32, , , 90, 17, 113);
INSERT INTO Marshrut
VALUES (8, , , 60, 20, 010);
2
1. , , , . .
SELECT First_name, salary, commission_pct
FROM employees
Where commission_pct>0
ORDER BY salary, commission asc;
2. , , , , 15%.
SELECT employee_id, first_name, salary, salary+salary*0,15
FROM employees;
3. , , ID, , .
Select employees.first_name, departments.department_name, departments.location_id, locations.city
From employees, departments, locations
Where employees.department_id=departments.department_id, departments.location_id=locations.location_id;
4. ( ) , , Zlotkey.
SELECT firs_name, hire_date
FROM employees
WHERE departments_id =
(select departments_id
From employees
Where firs_name=Zlotkey);
5. , . , u.
Select employee_id, first_name
From employees
Where departments_id=
(select departments_id
From employees
Where firs_name in (*u*));
|
|
|
6. .
Select departments.department_name, count(employee_id)
From employees, departments
Where employees.department_id=departments.department_id
Group by department_id;
7. , , .
Select E1.department_id, count(E1.employee_id), AVG(E1.salary)
From employees E1, employees E2
Where E1 department_id IN
(Select E2.department_id,
From E2
Where E2.department_id = E1.department_id);
8.1 .
Create or replace view
As select ,
from voditeli
Where <2013;
8.2 .
Create or replace view marshrut33
As select marshut.=33, bus.
From marhrut, bus
Where marshrut.. .=bus..;
8.3 .
CREATE INDEX bus_idx
ON bus();
