, . :
-
- ,
-
- ,
-
-
2.1:
| 1.1 |
:
| PTS | , |
| PTS_type | |
| Charasteristics | , |
| PTS-Characteristics | , , PTS Characteristics. |
| TO | , |
| Numbers | |
| Serial | |
| Regions | |
| Streers | |
| PTS_users | , , PTS Users |
| Users | , , . |
| Organisations | |
| Stealing | , |
| Reasons | |
| DTP_Type | |
| Reason_DTP | |
| DTP | |
| Number_DTP | , , PTS DTP. |
| 1.1 |
:
PTS:
ID -
SignalingID ( NULL)
Type
NumberID
Firm
Model
Year
Color
Engine_number ( NULL )
Chassis_number
Stand_number
| 1.2 PTS |
PTS_Types:
ID
Name
| 1.3 PTS_Types |
Charasteristics:
ID
Name
Str
Chisl
| 1.4 Charasteristics |
|
|
|
PTS-Characteristics:
PTSID
CharacteristicID
| 1.5 PTS - Charasteristics |
TO:
ID
Date
PTS_ID
Passed
Cost -
Period
| 1.6 TO |
Numbers:
Avto_type
Number
ID
Date
SeriallD
RegionID
| 1.7 Charasteristics |
Serial:
ID
Name
| 1.8 Serial |
Regions:
ID
Name
| 1.9 Regions |
Streers:
ID
Name
| 1.10 Streets |
PTS_users:
PTS_ID
UserID
| 1.11 PTS_users |
Users
ID
First_name
Second_name
Father_name
Birthday
Organisation ( NULL )
| 1.11 Users |
Organisations:
ID
Name
StreetID
Manager
| 1.12 Organisations |
Stealing:
ID
PTS_ID
ReasonID
Date
StreetID
Found
| 1.13 Stealing |
Reasons
ID
Name
| 1.14 Reasons |
DTP_Type:
ID
Name
| 1.15 DTP_Type |
Reason_DTP:
ID
Name
| 1.16 Reason_DTP |
DTP:
ID
ReasonID
Date
Type
Arial
StreetID
| 1.17 DTP |
Number_DTP:
PTSID
DTP_ID
Leave
| 1.18 Number_DTP |
SQL- , , :
1. , , .
SELECT Organisations.Name, Organisations.Arial, Organisations.Addres, Organisations.Manager
FROM Serial, Numbers, PTS, Users, Organisations, PTS_users
WHERE Serial.ID = Numbers.SerialID
and Numbers.ID = PTS.NumberID
and PTS.ID = PTS_users.PTS_ID
and PTS_users.UserID = Users.ID
and Users.Organisation = Organisations.ID
and Serial.Name = 'CTO'
SELECT COUNT (Organisations.Name) as [ ]
|
|
|
FROM Serial, Numbers, PTS, Users, Organisations, PTS_users
WHERE Serial.ID = Numbers.SerialID
and Numbers.ID = PTS.NumberID
and PTS.ID = PTS_users.PTS_ID
and PTS_users.UserID = Users.ID
and Users.Organisation = Organisations.ID
and Serial.Name = 'CTO'
SELECT Organisations.Name, Organisations.Arial, Organisations.Addres, Organisations.Manager
FROM Serial, Numbers, PTS, Users, Organisations, PTS_users
WHERE Serial.ID = Numbers.SerialID
and Numbers.ID = PTS.NumberID
and PTS.ID = PTS_users.PTS_ID
and PTS_users.UserID = Users.ID
and Users.Organisation = Organisations.ID
and Numbers.[Date] BETWEEN '07.10.2007' AND '14.08.2013'
:
1.19
2. .
SELECT Users.Second_name, Users.First_name, Users.Father_name, Users.Birthdate
FROM Numbers, PTS, USERS, PTS_users
WHERE Numbers.ID = PTS.NumberID
and PTS.ID = PTS_users.PTS_ID
and PTS_users.UserID = Users.ID
and Users.Organisation is NULL
and Numbers.Number = '125'
SELECT Users.Second_name, Users.First_name, Users.Father_name, Users.Birthdate, Organisations.Name, Organisations.Addres, Organisations.Arial, Organisations.Manager
FROM Numbers, PTS, USERS, PTS_users, Organisations
WHERE Numbers.ID = PTS.NumberID
and PTS.ID = PTS_users.PTS_ID
and PTS_users.UserID = Users.ID
and Users.Organisation = Organisations.ID
and Numbers.Number = '323'
:
1.20
3. "" - , , , .
SELECT PTS.Firm, PTS.Engine_number, PTS.Chassis_number, PTS.Stand_number, DTP.Data as [ ], [To].Passed as []
FROM Numbers, PTS, Number_DTP, DTP, [To]
WHERE Numbers.ID = PTS.NumberID
and Number_DTP.PtsID = PTS.ID
and DTP.ID = Number_DTP.DTP_ID
and Numbers.ID = Pts.NumberID
and Numbers.Number = '72'
and [To].PTS_ID = PTS.ID
:
1.21
4. , .
SELECT Users.Second_name, Users.First_name, Users.Father_name, Users.Birthdate
FROM [TO], PTS, Users, PTS_users
WHERE Users.ID = PTS_users.UserID
and PTS_users.PTS_ID = PTS.ID
and [TO].PTS_ID = PTS.ID
and [TO].Passed = 'false'
SELECT COUNT (Users.Second_name) as [ ]
FROM [TO], PTS, Users, PTS_users
WHERE Users.ID = PTS_users.UserID
and PTS_users.PTS_ID = PTS.ID
and [TO].PTS_ID = PTS.ID
and [TO].Passed = 'false'
:
1.22
5. .
SELECT COUNT(DTP.ID) as [ ], COUNT(Number_DTP.PtsID)
as [ ], COUNT(Number_DTP.Leave) as[ ]
FROM DTP, DTP_Types, Number_DTP
WHERE DTP_Types.Name = ' '
and DTP.[Type] = DTP_Types.ID
and DTP.ID = Number_DTP.DTP_ID
and DTP.Data BETWEEN '06.07.2013' and '02.10.2015'
:
1.23
6. : ,
SELECT TOP 5 .Name,
FROM (SELECT count(DTP.ReasonID) AS , Reason_DTP.Name
FROM Reason_DTP, DTP
WHERE Reason_DTP.ID = DTP.ReasonID
GROUP BY Reason_DTP.Name) AS
ORDER BY desc
SELECT TOP 5 .Name,
FROM (SELECT count(DTP.StreetID) AS , Streets.Name
FROM Streets, DTP
WHERE Streets.ID = DTP.StreetID
GROUP BY Streets.Name) AS
ORDER BY desc
:
1.24
|
|
|
7. , .
SELECT B_DTP.[ ], A_DTP.[ ], A_DTP.[ ]*100/B_DTP.[ ] as [ (%)]
FROM (SELECT COUNT(DTP.ID) as [ ] FROM DTP, Reason_DTP WHERE DTP.ReasonID = Reason_DTP.ID and Reason_DTP.ID = '2') AS A_DTP,
(SELECT COUNT(DTP.ID) as [ ] FROM DTP, Reason_DTP WHERE DTP.ReasonID = Reason_DTP.ID) AS B_DTP
:
1.25
8. , , .
SELECT PTS_type.name as [Type],PTS.Firm, PTS.Color, Numbers.Number, Serial.Name as [Serial], Regions.Name as [Region], PTS.Engine_number, PTS.Engine_number, PTS.Stand_number, Stealing.[Date] as [Stealing date], Reasosns.Name as []
FROM Stealing, PTS, Numbers, Regions, PTS_type, Serial, Reasosns
WHERE Stealing.PTS_ID = PTS.ID
and PTS.NumberID = Numbers.ID
and Serial.ID = Numbers.SerialID
and Regions.ID = Numbers.RegionID
and PTS_type.id = PTS.[Type]
and Stealing.ReasonID = Reasosns.ID
and Stealing.Found = 'false'
:
1.26
9. : .
SELECT steel_table.steel as [ ], nsteel_table.nsteel as [ ], steel_table.steel*100/nsteel_table.nsteel as[ ]
FROM (SELECT COUNT(Stealing.ID) as steel FROM Stealing WHERE Stealing.Found = 'true') as steel_table,
(SELECT COUNT(Stealing.ID) as nsteel FROM Stealing) as nsteel_table
:
1.27
10. .
SELECT PTS.Firm, PTS.Color, Numbers.Number, Serial.Name, Stealing.[Date], Stealing.Citi, Stealing.Street
FROM Stealing, PTS, Numbers, Serial, Reasosns
WHERE Serial.ID = Numbers.SerialID
and Numbers.ID = PTS.NumberID
and PTS.ID = Stealing.PTS_ID
and Reasosns.ID = '2'
and Stealing.[Date] BETWEEN '03.09.2013'AND'21.12.2015'
:
2.28
11. : , ..
SELECT TOP 5 .Firm,
FROM (SELECT COUNT(PTS.ID) AS , PTS.Firm
FROM PTS, Stealing
WHERE Stealing.PTS_ID = PTS.ID
and Stealing.ReasonID = '2'
GROUP BY PTS.Firm) as
ORDER BY desc
SELECT TOP 5 .Name,
FROM (SELECT COUNT(PTS.ID) AS , Signalling.Name
FROM PTS, Stealing, Signalling
WHERE Stealing.PTS_ID = PTS.ID
and Signalling.ID = PTS.SignallingID
and Stealing.ReasonID = '2'
GROUP BY Signalling.Name) as
ORDER BY desc
:
1.29
SQL- :
1. :
CREATE TRIGGER ___
ON Numbers
AFTER INSERT, UPDATE
AS
IF EXISTS (
SELECT *
FROM Numbers, Users
WHERE Users.Birthdate > Numbers.[Date])
BEGIN
ROLLBACK
PRINT ' '
END
:
|
|
|
UPDATE Numbers
SET Date = '1970-06-03'
WHERE Date = '2006-06-03'
:
2. :
CREATE TRIGGER __
ON DTP
AFTER INSERT, UPDATE
AS
IF EXISTS (
SELECT *
FROM DTP, PTS
WHERE PTS.[Year] > DTP.Data)
BEGIN
ROLLBACK
PRINT ' '
END
:
UPDATE DTP
SET Data = '2015-12-12'
WHERE Data = '1999-08-09'
:
3. (, ):
CREATE TRIGGER __
ON Stealing
AFTER INSERT, UPDATE
AS
IF EXISTS (
SELECT *
FROM Stealing, PTS
WHERE PTS.[Year] > Stealing.[Date])
BEGIN
ROLLBACK
PRINT ' '
END
:
UPDATE Stealing
SET Date = '2015-08-09'
WHERE Date = '1999-08-09'
:
