rT2hWkgz

1drop database if exists Finals;
2create database Finals;
3use Finals;
4CREATE TABLE `Groups`(
5    id_group INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
6    stud_number INT(50),
7   year YEAR(4) NOT NULL,
8   specialty VARCHAR(50),
9   faculty VARCHAR(50)
10);
11insert into `Groups`(id_group,stud_number,year,specialty, faculty)
12values('01','30', '2017', 'E-Marketing', 'IEF');
13insert into `Groups`(id_group,stud_number,year,specialty, faculty)
14values('02','30', '2018', 'Economics of E-Business', 'IEF');
15insert into `Groups`(id_group,stud_number,year,specialty, faculty)
16values('03','29', '2016', 'ISaT in logistics', 'IEF');
17insert into `Groups`(id_group,stud_number,year,specialty, faculty)
18values('04','30', '2017', 'IT Software', 'FKSaN');
19insert into `Groups`(id_group,stud_number,year,specialty, faculty)
20values('05','28', '2016', 'Artificial Intelligence', 'FITaM');
21SELECT
22    *
23FROM
24    `Groups`;
25  use Finals;
26CREATE TABLE RecordBooks (
27    id_rb INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
28    date_given DATE NOT NULL
29);
30 insert into RecordBooks(id_rb,date_given)
31 values('7740005','2017-07-25');
32insert into RecordBooks (id_rb,date_given)
33 values('6780004','2016-08-05');
34insert into RecordBooks (id_rb,date_given)
35 values('8830006','2018-07-25');
36insert into RecordBooks (id_rb,date_given)
37 values('7300078','2017-08-05');
38insert into RecordBooks (id_rb,date_given)
39 values('6230007','2016-08-05');
40SELECT
41    *
42FROM
43    RecordBooks;
44 
45use Finals;
46CREATE TABLE Students (
47    id_stud INT(10) AUTO_INCREMENT PRIMARY KEY,
48   id_rb INT(10) UNIQUE,
49    FOREIGN KEY (id_rb)
50        REFERENCES RecordBooks (id_rb),
51    fio VARCHAR(40) NOT NULL,
52    birth_date DATE NOT NULL,
53    address VARCHAR(40) NOT NULL,
54    id_group INT(10) UNSIGNED,
55    FOREIGN KEY (id_group)
56        REFERENCES `Groups`(id_group)
57);
58insert into Students(id_stud,id_rb, fio, birth_date, address, id_group)
59values('1', '7740005','Baranovskaya Elizaveta Andreevna','2000-03-10','Odintsova 111-25 Minsk', '01');
60insert into Students(id_stud,id_rb, fio, birth_date, address, id_group)
61values('2','6780004','Ivanov Sergey Alexandrovich','1999-07-16','Lobanka 9-134 Minsk', '03');
62insert into Students(id_stud,id_rb, fio, birth_date, address, id_group)
63values('3','8830006', 'Bokov Ilya Dmitrievich', '2000-08-09', 'Krasnoarmeyskaya 5-67 Brest','02');
64insert into Students(id_stud,id_rb, fio, birth_date, address, id_group)
65values('4','7300078', 'Petrova Anna Igorevna', '2000-05-23', 'Kosmonavtov 10-12 Vitebsk','04');
66insert into Students(id_stud,id_rb, fio, birth_date, address, id_group)
67values('5','6230007', 'Ovsyankina Olga Petrovna', '1999-09-13' , 'Nekrasova 6-90 Minsk','05');
68 
69SELECT
70    *
71FROM
72    Students;
73 
74use Finals;
75CREATE TABLE Subject (
76    id_subject INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
77    subject_name VARCHAR(20) NOT NULL,
78    hours INT(5) UNSIGNED NOT NULL
79);
80SELECT
81    *
82FROM
83    Subject;
84 
85use Finals;
86CREATE TABLE Tests (
87    id_rb INT(10) UNSIGNED,
88    id_subject INT(10) UNSIGNED,
89   test_form VARCHAR(20) NOT NULL,
90   mark VARCHAR(20) NOT NULL,
91   test_date DATE NOT NULL,
92PRIMARY KEY (id_rb, id_subject)
93);
94insert into Tests(id_rb,id_subject,test_form,mark, test_date)
95values('7740005','1', 'Credit', 'Passed', '2018-12-26');
96insert into Tests(id_rb,id_subject,test_form,mark, test_date)
97values('6780004','2', 'Exam', '9', '2019-01-12');
98insert into Tests(id_rb,id_subject,test_form,mark, test_date)
99values('8830006','1', 'Credit', 'Passed', '2018-12-28');
100insert into Tests(id_rb,id_subject,test_form,mark, test_date)
101values('7300078','3', 'Exam', '8', '2019-01-10');
102insert into Tests(id_rb,id_subject,test_form,mark, test_date)
103values('6230007','4', 'Exam', '9', '2019-01-17');
104SELECT
105    *
106FROM
107    Tests;
108 
109use Finals;
110CREATE TABLE Teachers (
111    id_teacher INT(10) AUTO_INCREMENT PRIMARY KEY,
112   id_post INT(10) UNSIGNED,
113    FOREIGN KEY (id_post)
114        REFERENCES Post (id_post),
115    fio VARCHAR(40) NOT NULL,
116    work_date DATE NOT NULL
117);
118insert into Teachers(id_teacher,id_post, fio, work_date)
119values('1', '1','Ivanov Petr Nikolaevich','2000-03-10');
120insert into Teachers(id_teacher,id_post, fio, work_date)
121values('2', '2','Abramskaya Nina Vasilyevna','2005-09-25');
122insert into Teachers(id_teacher,id_post, fio, work_date)
123values('3', '3','Osipova Kristina Petrovna','1995-09-01');
124insert into Teachers(id_teacher,id_post, fio, work_date)
125values('4', '4','Iosifova Polina Vyacheslavovna','2003-09-15');
126insert into Teachers(id_teacher,id_post, fio, work_date)
127values('5', '5','Sidorov Leonid Olegovich','2017-09-20');
128 
129SELECT
130    *
131FROM
132    Teachers;
133 
134use Finals;
135CREATE TABLE Posts (
136    id_post INT(10) AUTO_INCREMENT PRIMARY KEY,
137    post_name VARCHAR(40) NOT NULL
138);
139insert into Posts(id_post,post_name)
140values('1', 'docent');
141insert into Posts(id_post, post_name)
142values('2', 'assistant professor');
143insert into Posts(id_post,post_name)
144values('3', 'docent');
145insert into Posts(id_post,post_name)
146values('4', 'docent');
147insert into Posts(id_post,post_name)
148values('5', 'assistant');
149SELECT
150    *
151FROM
152    Posts;
153 
154use Finals;
155CREATE TABLE Classes (
156    id_teacher INT(10) UNSIGNED,
157   id_subject INT(10) UNSIGNED,
158    id_group INT(10) UNSIGNED,
159    class VARCHAR(30) NOT NULL,
160    PRIMARY KEY (id_teacher, id_subject, id_group)
161);
162insert into Classes (id_teacher,id_subject, id_group, class)
163values('1', '1','1','Lectures');
164insert into Classes (id_teacher,id_subject, id_group, class)
165values('2', '2','2','Lectures');
166insert into Classes (id_teacher,id_subject, id_group, class)
167values('3', '1','3','Lectures');
168insert into Classes (id_teacher,id_subject, id_group, class)
169values('4', '3','4','Lectures and Seminars');
170insert into Classes (id_teacher,id_subject, id_group, class)
171values('5', '4','2','Labwork');
172SELECT
173    *
174FROM
175    Classes;
176use Finals;
177CREATE TABLE Test(
178id_tsk int(10) unsigned auto_increment primary key,
179information varchar(20) not null,
180bdata date not null
181);
182insert into Test(id_tsk,information,bdata)
183values('1','Вторая лаба','2019-04-02');
184insert into Test(id_tsk,information,bdata)
185values('3','Третья лаба ','2019-04-05');
186insert into Test(id_tsk,information,bdata)
187values('2','Заключительная лаба','2019-04-22');
188select * from Test;
189 
190alter table Test
191change column information infa
192varchar(20) not null;
193alter table Test
194add new_column varchar(20) not null
195after infa;
196alter table Test
197rename to NewTable;
198 
199alter table  NewTable
200drop column bdata;
201alter table  NewTable
202modify infa char(20) not null;
203 
204select * from NewTable;
205drop table NewTable;
206drop database Finals;