1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
| 0) CREATE DATABASE NIKITA;
1) SHOW DATABASES;
3) USE NIKITA;
1) CREATE TABLE EXAMIN(
SURNAME VARCHAR(100) NOT NULL,
NAME VARCHAR(100) NOT NULL,
MARK VARCHAR(40) NOT NULL,
EXAM_DATE DATE,
SELECT DISTINCT SURNAME, NAME, MARK, EXAM_DATE
FROM (STUDENT AS s INNER JOIN SESSONI_RESLTS AS SR S.STODENT_ID = SR.STODENT_ID)
INNER JOIN SSUBJIKT AS SU ON SR.SUBJIKT_ID = SU.SUBJIKT_ID
WHERE SU.SUBJIKT_NAME = 'Экспертные Системы' AND S.FACULTET = 'Системное Программное Обеспечения'
CHECK (MARK BETWEEN 6 AND 7 OR 8)
SELECT * FROM SURNAME ORDER BY SURNAME
SELECT * FROM EXAM_DATE ORDER BY EXAM_DATE DESC
);
2) CREATE TABLE BELARUSE(
SUBJEKT_NAME VARCHAR(100) NOT NULL,
LECTURER VARCHAR(100) NOT NULL,
ID_HOURS VARCHAR(40) NOT NULL,
SUBJECTS VARCHAR(40) NOT NULL,
SELECT DISTINCT SUBJEKT_NAME, LECTURER, ID_HOURS, SUBJECTS
FROM (STUDENT AS s INNER JOIN SESSONI_RESLTS AS SR S.STODENT_ID = SR.STODENT_ID)
INNER JOIN SUBJECTS AS SU ON SR.SUBJECTS_ID = SU.SUBJECTS_ID
WHERE SU.SUBJECTS_NAME='Ивановым' AND S.FACULTET ='Петровым'
CHECK (SUBJIKT_NAME BETWEEN 30 AND 70)
SELECT * FROM SUBJECTS ORDER BY SUBJECTS
SELECT * FROM ID_HOURS ORDER BY ID_HOURS DESC
);
3) CREATE TABLE PEPOLS(
SUBJEKT_NAME VARCHAR(100) NOT NULL,
LECTURER VARCHAR(100) NOT NULL,
ID_HOURS VARCHAR(40) NOT NULL,
SUBJECTS VARCHAR(40) NOT NULL,
SELECT DISTINCT SUBJEKT_NAME, LECTURER, ID_HOURS, SUBJECTS
FROM (STUDENT AS s INNER JOIN SESSONI_RESLTS AS SR S.STODENT_ID = SR.STODENT_ID)
INNER JOIN SUBJECTS AS SU ON SR.SUBJECTS_ID = SU.SUBJECTS_ID
WHERE SU.SUBJECTS_NAME = 'Котов' AND S.FACULTET = 'Сидоров' OR SU.SUBJECTS_ID = 'Петорв'
CHECK (SUBJIKT_NAME BETWEEN 1 AND 50)
SELECT IF (1 = 50 , 'ДА' , 'НЕТ') AS 'ПРЕДЕЛ ЧИСОВ ОТ ОДНОГО ДО ПЕДЕСЯТ ВЫБЕРИТЕ ДА ИЛИ НЕТ !';
SELECT * FROM SUBJECTS ORDER BY SUBJECTS
SELECT * FROM ID_HOURS ORDER BY ID_HOURS DESC
);
4) CREATE TABLE OTMETKA(
SURNAME VARCHAR(100) NOT NULL,
NAME VARCHAR(100) NOT NULL,
SUBJECTS_NAME VARCHAR(40) NOT NULL,
LECTURER VARCHAR(40) NOT NULL,
MARK VARCHAR(40) NOT NULL,
EXAM_DATE DATE,
CHECK (SUBJIKT_NAME BETWEEN 1 AND 10)
SELECT IF (1<>3 OR 3<>10 , 'ДА' , 'НЕТ') AS 'ВЫ ХОИТЕ ВЫВЕСТИ ОЦЕНКИ ЗА ЭКЗАМЕН В ЛЕТНУЮ СЕСИУЮ ?';
SELECT * FROM MARK ORDER BY MARK DESC
);
5) CREATE TABLE YHENIKI(
SURNAME VARCHAR(100) NOT NULL,
NAME VARCHAR(100) NOT NULL,
SUBJECTS_NAME VARCHAR(40) NOT NULL,
LECTURER VARCHAR(40) NOT NULL,
MARK VARCHAR(40) NOT NULL,
EXAM_DATE DATE,
CHECK (SUBJIKT_NAME BETWEEN 9 AND 10)
SELECT IF (9 = 9 OR 10 = 10 , 'ДА' , 'НЕТ') AS 'ВЫ ХОИТЕ ВЫВЕСТИ УЧЕНИКОВ КОЛИЧЕСВТО БАЛОВ У КОТРЫХ 9 И 10?';
SELECT * FROM MARK ORDER BY MARK DESC
);
6) CREATE TABLE DATAEXMAINA(
SURNAME VARCHAR(100) NOT NULL,
NAME VARCHAR(100) NOT NULL,
SUBJEKT_NAME VARCHAR(100) NOT NULL,
MARK VARCHAR(40) NOT NULL,
EXAM_DATE DATE,
FACULTY VARCHAR(100) NOT NULL,
SELECT DISTINCT SUBJEKT_NAME, LECTURER, ID_HOURS, SUBJECTS
FROM (STUDENT AS s INNER JOIN SESSONI_RESLTS AS SR S.STODENT_ID = SR.STODENT_ID)
INNER JOIN SUBJECTS AS SU ON SR.SUBJECTS_ID = SU.SUBJECTS_ID
WHERE SU.SUBJECTS_NAME = 'Минск' OR S.FACULTET = 'Москва'
CHECK (SUBJIKT_NAME BETWEEN 8 OR 9)
SELECT IF (8 = 8 OR 9 = 9 , 'ДА' , 'НЕТ') AS 'ПРЕДЕЛ ЧИСОВ ОТ ОДНОГО ДО ПЕДЕСЯТ ВЫБЕРИТЕ ДА ИЛИ НЕТ !';
SELECT * FROM FACULTET ORDER BY FACULTY
SELECT * FROM MARK ORDER BY MARK DESC
);
7) CREATE TABLE DATEQ(
SURNAME VARCHAR(100) NOT NULL,
NAME VARCHAR(100) NOT NULL,
AVERAGE_MARK VARCHAR(40) NOT NULL,
ID_YEAR INTEGER(10) NOT NULL,
SELECT avg(BALL) FROM AVERAGE_MARK WHERE ID_YEAR;
CHECK (AVERAGE_MARK BETWEEN 2,3 AND 4)
SELECT * FROM SURNAME ORDER BY SURNAME
SELECT * FROM NAME ORDER BY NAME
SELECT * FROM AVERAGE_MARK ORDER BY AVERAGE_MARK DESC
);
8) CREATE TABLE SORTSLIV(
SURNAME VARCHAR(100) NOT NULL,
NAME VARCHAR(100) NOT NULL,
AVERAGE_MARK VARCHAR(40) NOT NULL,
ID_YEAR INTEGER(10) NOT NULL,
SELECT avg(BALL) FROM AVERAGE_MARK WHERE ID_YEAR;
CHECK (AVERAGE_MARK BETWEEN 3 AND 4)
SELECT * FROM SURNAME ORDER BY SURNAME
SELECT * FROM NAME ORDER BY NAME
SELECT * FROM AVERAGE_MARK ORDER BY AVERAGE_MARK DESC
);
9) CREATE TABLE MARGSORT(
STUDENT_SURNAME VARCHAR(100) NOT NULL,
NAME VARCHAR(100) NOT NULL,
ID_YEAR INTEGER(10) NOT NULL,
ID_GROUP INTEGER(10) NOT NULL,
FACULTET VARCHAR(40) NOT NULL,
THE_HEAD VARCHAR(20) NOT NULL,
CHECK (FACULTET BETWEEN 4 AND 5)
SELECT * FROM STUDENT_SURNAME ORDER BY STUDENT_SURNAME
SELECT * FROM NAME ORDER BY NAME
SELECT * FROM ID_GROUP ORDER BY ID_GROUP DESC
);
10) CREATE TABLE HOMWORKS(
STUDENT_SURNAME VARCHAR(100) NOT NULL,
NAME VARCHAR(100) NOT NULL,
ID_YEAR INTEGER(10) NOT NULL,
ID_GROUP INTEGER(10) NOT NULL,
FACULTET VARCHAR(40) NOT NULL,
THE_HEAD VARCHAR(20) NOT NULL,
CHECK (FACULTET BETWEEN 3,4 AND 5)
SELECT * FROM STUDENT_SURNAME ORDER BY STUDENT_SURNAME
SELECT * FROM NAME ORDER BY NAME
SELECT * FROM ID_GROUP ORDER BY ID_GROUP DESC
);
11) CREATE TABLE HIGET(
SUBJEKT_NAME VARCHAR(100) NOT NULL,
LECTURER VARCHAR(100) NOT NULL,
ID_HOURS VARCHAR(40) NOT NULL,
CHECK (SUBJIKT_NAME BETWEEN 4);
CHECK (SUBJIKT_NAME BETWEEN 60 AND 300)
SELECT IF ( 60 < 300 AND 60 != 60 OR 70<>300 , 'ДА' , 'НЕТ') AS 'ПРЕДЕЛ ЧИСЕЛ КОТОРЫЕ ПРИВЫШАЮТ КОЛИЧЕСВТО БАЛОВ С ВЫШЕ 60!';
SELECT * FROM SUBJECTS_NAME ORDER BY SUBJIKT_NAME
SELECT * FROM ID_HOURS ORDER BY ID_HOURS DESC
);
12) CREATE TABLE ISORIA(
SUBJEKT_NAME VARCHAR(100) NOT NULL,
LECTURER VARCHAR(100) NOT NULL,
ID_HOURS VARCHAR(40) NOT NULL,
CHECK (SUBJIKT_NAME BETWEEN 4);
CHECK (SUBJIKT_NAME BETWEEN 60 AND 100)
SELECT IF ( 60 <> 100, 'ДА' , 'НЕТ') AS 'ВЫ ХОТИТЕ УЗНАТЬ КОЛИЧЕСВТО ЧАСОВ БОЛЛЕЕ 0 НО МЕНИЕ 100 ?';
SELECT * FROM SUBJEKT_NAME ORDER BY SUBJIKT_NAME
SELECT * FROM ID_HOURS ORDER BY ID_HOURS DESC
); |
(
