:
SQL.
. 㳿 . , . SQL , .
7
.
() .
.
ճ :
3.1., 5.2.6. - 5.3. , . 6.1.
³ ;
.
SQL ( , <C>, , <>, , SQL);
ϳ
.
CREATE ADLE. , , . SQL.
.
ϳ 7. :
a) ;
b) ;
c) ;
d) CRETE TABLE;
e) ;
f) .
³ .
7 2 .
7
1. , ?
2. 璺, .
3. .?
: SQL.
1. SELECT [ ]., Count([ ].[_#]) AS ʳ, Avg([ ].[ֳ ]) AS [Avg_ֳ ]
FROM [ ] INNER JOIN [ ] ON [ ].[_#] = [ ].[_#]
GROUP BY [ ].
HAVING (((Avg([ ]![ֳ ]))<50))
ORDER BY [ ].;
2. SELECT [ ]._, [ ]., [ ]._, Count([ ]._) AS Count__, [ ].[ֳ ]
FROM [ ] INNER JOIN ([ ] INNER JOIN [ ] ON [ ].[_#] = [ ].[_#]) ON [ ].[__#] = [ ].[__#]
|
|
|
GROUP BY [ ]._, [ ]., [ ]._, [ ].[ֳ ];
3. SELECT [ ]._, Count([ ].[_#]) AS ʳ, [ ].[_#], [ ]., [ ].[ֳ ], [ ].[_#], Count([ ]![_#])*([ ]![ֳ ]) AS [ ]
FROM [ ] LEFT JOIN [ ] ON [ ].[_#] = [ ].[_#]
GROUP BY [ ]._, [ ].[_#], [ ]., [ ].[ֳ ], [ ].[_#]
ORDER BY [ ].[_#];
4. ( 1.) SELECT TOP 4 [ ].[_#], [ ].[ֳ ], [14]![ʳ]*[ ]![ֳ ] AS
FROM [ ] RIGHT JOIN 14 ON [ ].[_#] = 14.[_#]
ORDER BY [14]![ʳ]*[ ]![ֳ ] DESC;
( )
SELECT DISTINCTROW [ ].[_#], Count(*) AS ʳ
FROM [ ]
GROUP BY [ ].[_#]
ORDER BY Count(*) DESC;
5. ( 10) SELECT DISTINCTROW [ ].[_#], Count(*) AS ʳ
FROM [ ]
GROUP BY [ ].[_#]
ORDER BY Count(*) DESC;
6. ( 11)
SELECT [ ]._, [ ]., [ ].[ֳ ]
FROM [ ]
WHERE ((([ ]._) Like "*"));
7.( 12)
SELECT [ ]._, [ ]., [ ].[ֳ ]
FROM [ ]
WHERE ((([ ]._) Like "*"));
8. ( 13)
SELECT [ ]., Avg([ ].[ֳ ]) AS __, Min([ ].[ֳ ]) AS [Min_ֳ ]
FROM [ ]
GROUP BY [ ].
HAVING (((Avg([ ].[ֳ ]))>50)) OR (((Min([ ].[ֳ ]))>50));
9 ( 14)
SELECT [ ].[_#], Count([ ].[_#]) AS ʳ
FROM [ ]
GROUP BY [ ].[_#];
10. ( 15)
SELECT [ ]._, [ ].[_#], IIf([ ]![_#]=[14]![_#],[14]![ʳ],0) AS ʳ, [ ].[ֳ ], IIf([ ]![_#]=[14]![_#],[14]![ʳ]*[ ]![ֳ ],0) AS
|
|
|
FROM [ ] LEFT JOIN 14 ON [ ].[_#] = 14.[_#]
ORDER BY IIf([ ]![_#]=[14]![_#],[14]![ʳ]*[ ]![ֳ ],0) DESC;
11. ( 16)
SELECT [ ].[_#], Count([ ].[_#]) AS ʳ
FROM [ ]
GROUP BY [ ].[_#];
12 ( 17)
SELECT [ ]._, [ ].[__#], [ ].[ֳ ], [ ].
FROM [ ] INNER JOIN [ ] ON [ ].[_#] = [ ].[_#]
ORDER BY [ ].;
13. ( 19)
SELECT [ ]._, [ ].[ֳ ]
FROM [ ]
WHERE ((([ ].[ֳ ]) In (30,50,34)));
14. ( 2)
SELECT *
FROM [ ]
WHERE ((([ ].[_#])=3)) OR ((([ ].[__#])=4));
15. ( 21)
SELECT [ ]._, Count([ ].[_#]) AS ʳ, [ ].[_#], [ ]., [ ].[ֳ ], [ ].[_#], Count([ ]![_#])*([ ]![ֳ ]) AS [ ]
FROM [ ] LEFT JOIN [ ] ON [ ].[_#] = [ ].[_#]
GROUP BY [ ]._, [ ].[_#], [ ]., [ ].[ֳ ], [ ].[_#]
ORDER BY [ ].[_#];
16. ( 3)
SELECT Max([ ].[ֳ ]) AS , Avg([ ].[ֳ ]) AS _
FROM [ ]
WHERE ((([ ].) Not Like "*"));
17. ( 4)
SELECT *
FROM [ ]
WHERE ((([ ]._)=5));
18. ( 5)
SELECT [ ].[_#], Count(*) AS ʳ
FROM [ ]
GROUP BY [ ].[_#]
ORDER BY Count(*) DESC;
19. ( 6)
SELECT [ ]._, Count([ ].[_#]) AS [ʳ #]
FROM [ ]
GROUP BY [ ]._
HAVING ((([ ]._)=[ ]));
20. ( 7)
SELECT [ ]._, [ ].[__#], [ ].[ֳ ], [ ].
FROM [ ] INNER JOIN [ ] ON [ ].[_#] = [ ].[_#]
ORDER BY [ ].;
( 8)
SELECT [ ].*
FROM [ ];
21. ( 9)
SELECT [ ]._, [ ]._
FROM [ ] RIGHT JOIN [ ] ON [ ].[_#] = [ ].[_#];
|
|
|
22. ( 21)
SELECT Count([ ].[ֳ ]) AS [ʳ ], Int([ ]![ֳ ]/25)*25 AS , Int([ ]![ֳ ]/25)*25+25 AS _
FROM [ ]
GROUP BY Int([ ]![ֳ ]/25)*25;
23. (ϳ)
SELECT [ ].[_#], [ ]._, [ ]., [ ].[ֳ ], 15.ʳ, [15]![ʳ]*[ ]![ֳ ] AS
FROM [ ] INNER JOIN 15 ON [ ].[_#] = 15.[_#]
ORDER BY [ ].[_#];
24. (1_5_1_2)
SELECT [ ]._, [ ].[_#], [ ]._, ., [ ].[__#], [ ].[ֳ ], [ ].
FROM INNER JOIN ([ ] INNER JOIN [ ] ON [ ].[_#] = [ ].[_#]) ON .[ ] = [ ].
WHERE ((([ ]._)<=3) AND ((.)<"*") AND (([ ].[__#])<4));
25. ( )
SELECT [ ].[_#], IIf([ ]![_#]=[14]![_#],[14]![ʳ],0) AS ʳ
FROM [ ] LEFT JOIN 14 ON [ ].[_#] = 14.[_#]
ORDER BY [ ].[_#];
