CREATE TABLE students (
id serial PRIMARY KEY,
lastname varchar,
firstname varchar,
gpa float,
email varchar
);
CREATE TABLE disciplines (
id serial PRIMARY KEY,
course_name varchar,
credits int,
prereq varchar
);
CREATE TABLE teachers (
id serial PRIMARY KEY,
lastname varchar,
firstname varchar
);
CREATE TABLE audiences (
id serial PRIMARY KEY,
audience_num varchar,
capacity int
);
CREATE TABLE schedule (
id serial PRIMARY KEY,
audience_id int REFERENCES audiences(id),
disc_id int REFERENCES disciplines(id),
teacher_id int REFERENCES teachers(id),
time timestamp,
weekday varchar
);
CREATE TABLE dis_teach (
teacher_id int REFERENCES teachers(id),
discipline_id int REFERENCES disciplines(id),
PRIMARY KEY (teacher_id, discipline_id)
);
CREATE TABLE stud_dis (
stud_id int REFERENCES students(id),
discipline_id int REFERENCES disciplines(id),
teacher_id int REFERENCES teachers(id),
PRIMARY KEY (stud_id, discipline_id)
);
INSERT INTO students (lastname, firstname, gpa, email) VALUES
('Abdulin', 'Askar', 3.5, '[email protected]'),
('Nurmagambetov', 'Nurzhan', 3.6, '[email protected]'),
('Zhumadilova', 'Aidana', 3.7, '[email protected]'),
('Kulmukhanov', 'Yerzhan', 3.8, '[email protected]'),
('Yergaliyev', 'Aruzhan', 3.9, '[email protected]'),
('Mukhamedov', 'Adil', 3.3, '[email protected]'),
('Ibragimov', 'Gani', 3.4, '[email protected]'),
('Kurbanov', 'Rinat', 3.5, '[email protected]'),
('Saparov', 'Aibek', 3.6, '[email protected]'),
('Kenzhaliev', 'Murat', 3.7, '[email protected]'),
('Sadykova', 'Gulnara', 3.2, '[email protected]'),
('Tazhibayev', 'Dauren', 3.1, '[email protected]'),
('Muratova', 'Dana', 3.4, '[email protected]'),
('Kenzhebekov', 'Ruslan', 3.3, '[email protected]'),
('Bektemirov', 'Timur', 3.2, '[email protected]'),
('Zhunusov', 'Nurlan', 3.8, '[email protected]'),
('Kadyrov', 'Samat', 3.9, '[email protected]'),
('Abishev', 'Talgat', 4.0, '[email protected]'),
('Omarov', 'Yeldos', 3.1, '[email protected]'),
('Nigmatulin', 'Olzhas', 3.2, '[email protected]'),
('Ospanov', 'Omir', 3.5, '[email protected]'),
('Kasymov', 'Yerbol', 3.6, '[email protected]'),
('Nazarbayev', 'Nursultan', 3.7, '[email protected]'),
('Tokayev', 'Kassym-Jomart', 3.8, '[email protected]'),
('Rakhimov', 'Azamat', 3.9, '[email protected]'),
('Zhakypov', 'Zhandos', 3.3, '[email protected]'),
('Bekbolatov', 'Erkebulan', 3.4, '[email protected]'),
('Iskakov', 'Ayan', 3.5, '[email protected]'),
('Doskhanov', 'Dulat', 3.6, '[email protected]'),
('Seytkaliyev', 'Ruslan', 3.7, '[email protected]'),
('Sultanov', 'Rustam', 3.4, '[email protected]'),
('Ismailov', 'Eldar', 3.2, '[email protected]'),
('Karimov', 'Daniyar', 3.3, '[email protected]'),
('Nurpeisov', 'Arman', 3.4, '[email protected]'),
('Yessenov', 'Daulet', 3.5, '[email protected]'),
('Nurpeissov', 'Marat', 3.8, '[email protected]'),
('Kairatuly', 'Baurzhan', 3.9, '[email protected]'),
('Zhaxybekov', 'Dauren', 4.0, '[email protected]'),
('Sultanbekov', 'Nurzhan', 3.1, '[email protected]'),
('Akhmetov', 'Kanat', 3.2, '[email protected]'),
('Temirov', 'Aidar', 3.2, '[email protected]'),
('Kazhimov', 'Bolat', 3.3, '[email protected]'),
('Duisenov', 'Chingiz', 3.4, '[email protected]'),
('Erkinov', 'Dastan', 3.5, '[email protected]'),
('Fazilov', 'Erzhan', 3.6, '[email protected]'),
('Galiyev', 'Farkhad', 3.7, '[email protected]'),
('Hakimov', 'Galym', 3.8, '[email protected]'),
('Ismailov', 'Hakim', 3.9, '[email protected]'),
('Jumadilov', 'Igor', 4.0, '[email protected]'),
('Kambarov', 'Jasulan', 3.1, '[email protected]'),
('Lukpanov', 'Kairat', 3.2, '[email protected]'),
('Makhmutov', 'Linar', 3.3, '[email protected]'),
('Nurlanov', 'Madi', 3.4, '[email protected]'),
('Ospanov', 'Nurlan', 3.5, '[email protected]'),
('Petrov', 'Oleg', 3.6, '[email protected]'),
('Qadir', 'Pavel', 3.7, '[email protected]'),
('Ryskulov', 'Rinat', 3.8, '[email protected]'),
('Suleimenov', 'Sultan', 3.9, '[email protected]'),
('Tazhibayev', 'Timur', 4.0, '[email protected]'),
('Ualiev', 'Ulan', 3.1, '[email protected]'),
('Vassilenko', 'Vlad', 3.2, '[email protected]'),
('Yermekov', 'Yerbol', 3.3, '[email protected]'),
('Zhumabekov', 'Zhanbolat', 3.4, '[email protected]'),
('Abikenov', 'Zhassulan', 3.5, '[email protected]'),
('Bakytov', 'Azamat', 3.6, '[email protected]'),
('Dauletbayev', 'Yerlan', 3.2, '[email protected]'),
('Ermekov', 'Bekzat', 3.3, '[email protected]'),
('Fayzullin', 'Rustem', 3.4, '[email protected]'),
('Gabdullin', 'Nurtas', 3.5, '[email protected]'),
('Hakimzhanov', 'Daniyar', 3.6, '[email protected]'),
('Ismagulov', 'Serik', 3.7, '[email protected]'),
('Jumagulov', 'Azamat', 3.8, '[email protected]'),
('Kaliaskarov', 'Marat', 3.9, '[email protected]'),
('Lukpanov', 'Dinmukhamed', 4.0, '[email protected]'),
('Mukashev', 'Askhat', 3.1, '[email protected]'),
('Nugmanov', 'Talgat', 3.2, '[email protected]'),
('Omarov', 'Kanat', 3.3, '[email protected]'),
('Pazylov', 'Timur', 3.4, '[email protected]'),
('Quatov', 'Ruslan', 3.5, '[email protected]'),
('Rakhimov', 'Saken', 3.6, '[email protected]'),
('Saparov', 'Yerbolat', 3.7, '[email protected]'),
('Tazhibayev', 'Berik', 3.8, '[email protected]'),
('Ualikhanov', 'Miras', 3.9, '[email protected]'),
('Vassilenko', 'Viktor', 4.0, '[email protected]'),
('Yessenov', 'Abylay', 3.1, '[email protected]'),
('Zhakypov', 'Daulet', 3.2, '[email protected]'),
('Aibekov', 'Aibek', 3.3, '[email protected]'),
('Bektemirov', 'Nurbol', 3.4, '[email protected]'),
('Chernykh', 'Artem', 3.5, '[email protected]'),
('Dmitriev', 'Anton', 3.6, '[email protected]'),
('Emirov', 'Nurlan', 3.5, '[email protected]'),
('Fayzulin', 'Dauren', 3.6, '[email protected]'),
('Garin', 'Mikhail', 3.7, '[email protected]'),
('Hakim', 'Arman', 3.8, '[email protected]'),
('Iskakov', 'Baurzhan', 3.9, '[email protected]'),
('Kairat', 'Yerzhan', 3.2, '[email protected]'),
('Jumageldi', 'Rustam', 3.1, '[email protected]'),
('Lukin', 'Igor', 3.3, '[email protected]'),
('Madiyar', 'Samat', 3.4, '[email protected]'),
('Nurlanov', 'Daulet', 3.5, '[email protected]'),
('Ospan', 'Ruslan', 3.6, '[email protected]'),
('Petrov', 'Viktor', 3.7, '[email protected]'),
('Qadir', 'Nurzhan', 3.8, '[email protected]'),
('Rakhmetov', 'Timur', 3.9, '[email protected]'),
('Saparov', 'Azamat', 3.1, '[email protected]'),
('Tazhibay', 'Murat', 3.2, '[email protected]'),
('Umarov', 'Arman', 3.3, '[email protected]'),
('Vassilenko', 'Alexey', 3.4, '[email protected]'),
('Yermek', 'Daniyar', 3.5, '[email protected]'),
('Zhaksylyk', 'Nurlan', 3.6, '[email protected]'),
('Abayev', 'Yerbol', 3.7, '[email protected]'),
('Borisov', 'Denis', 3.8, '[email protected]'),
('Chernenko', 'Oleg', 3.9, '[email protected]'),
('Dmitriev', 'Nikita', 3.1, '[email protected]'),
('Esenov', 'Rustam', 3.2, '[email protected]'),
('Fedorov', 'Vladimir', 3.3, '[email protected]'),
('Gumarov', 'Adil', 3.4, '[email protected]'),
('Husainov', 'Murat', 3.5, '[email protected]'),
('Ivanov', 'Andrey', 3.6, '[email protected]'),
('Jumabek', 'Talgat', 3.7, '[email protected]'),
('Kambar', 'Marat', 3.8, '[email protected]'),
('Lomov', 'Sergey', 3.9, '[email protected]'),
('Mukhanov', 'Alexandr', 3.1, '[email protected]'),
('Nazarov', 'Roman', 3.2, '[email protected]'),
('Oblomov', 'Ilya', 3.3, '[email protected]'),
('Pavlov', 'Kirill', 3.4, '[email protected]'),
('Rasputin', 'Grigory', 3.5, '[email protected]'),
('Suleimenov', 'Dias', 3.6, '[email protected]'),
('Talgatov', 'Yerbol', 3.7, '[email protected]'),
('Ulyanov', 'Lenin', 3.8, '[email protected]'),
('Voronov', 'Anton', 3.9, '[email protected]'),
('Yakovlev', 'Petr', 3.1, '[email protected]'),
('Zhukov', 'Georgy', 3.2, '[email protected]'),
('Akimov', 'Dmitry', 3.3, '[email protected]'),
('Bykov', 'Evgeny', 3.4, '[email protected]'),
('Cherkasov', 'Nikolay', 3.5, '[email protected]'),
('Drozdov', 'Pavel', 3.6, '[email protected]'),
('Efremov', 'Oleg', 3.7, '[email protected]'),
('Golubev', 'Vasiliy', 3.8, '[email protected]'),
('Ishchenko', 'Fedor', 3.9, '[email protected]'),
('Kovalenko', 'Ivan', 3.2, '[email protected]'),
('Litvinov', 'Maxim', 3.3, '[email protected]'),
('Melnikov', 'Artyom', 3.4, '[email protected]'),
('Nikitin', 'Alexei', 3.5, '[email protected]'),
('Orlov', 'Petr', 3.6, '[email protected]'),
('Petrov', 'Sergei', 3.7, '[email protected]'),
('Romanov', 'Dmitri', 3.8, '[email protected]'),
('Sidorov', 'Nikita', 3.9, '[email protected]'),
('Titov', 'Oleg', 4.0, '[email protected]'),
('Uvarov', 'Vladimir', 3.1, '[email protected]'),
('Vinogradov', 'Boris', 3.2, '[email protected]'),
('Zaitsev', 'Yuri', 3.3, '[email protected]'),
('Baranov', 'Konstantin', 3.4, '[email protected]'),
('Voronin', 'Leonid', 3.5, '[email protected]'),
('Gusev', 'Roman', 3.6, '[email protected]'),
('Kuznetsov', 'Stanislav', 3.7, '[email protected]'),
('Lazarev', 'Mikhail', 3.8, '[email protected]'),
('Medvedev', 'Grigori', 3.9, '[email protected]'),
('Nosov', 'Viktor', 4.0, '[email protected]'),
('Ozerov', 'Igor', 3.1, '[email protected]'),
('Pavlov', 'Stepan', 3.2, '[email protected]'),
('Rogov', 'Daniil', 3.3, '[email protected]'),
('Sokolov', 'Maksim', 3.4, '[email protected]'),
('Terekhov', 'Andrei', 3.5, '[email protected]'),
('Filatov', 'Yaroslav', 3.6, '[email protected]'),
('Kharitonov', 'Egor', 3.7, '[email protected]'),
('Tsarev', 'Sergey', 3.8, '[email protected]'),
('Shapovalov', 'Denis', 3.9, '[email protected]'),
('Yudin', 'Timofey', 4.0, '[email protected]'),
('Zhuravlev', 'Artem', 3.1, '[email protected]'),
('Belyaev', 'Kirill', 3.2, '[email protected]'),
('Vlasov', 'Anton', 3.3, '[email protected]'),
('Golovin', 'Alexandr', 3.4, '[email protected]'),
('Isakov', 'Valeriy', 3.5, '[email protected]'),
('Kazakov', 'Nikolay', 3.6, '[email protected]'),
('Lobanov', 'Ilya', 3.7, '[email protected]'),
('Morozov', 'Vasiliy', 3.8, '[email protected]'),
('Novikov', 'Pavel', 3.9, '[email protected]'),
('Osipov', 'Evgeny', 4.0, '[email protected]'),
('Polyakov', 'Vyacheslav', 3.1, '[email protected]'),
('Semyonov', 'Ivan', 3.2, '[email protected]'),
('Tarasov', 'Bogdan', 3.3, '[email protected]'),
('Utkin', 'Vadim', 3.4, '[email protected]'),
('Fomin', 'Anatoliy', 3.5, '[email protected]'),
('Khokhlov', 'Yegor', 3.6, '[email protected]'),
('Tsiganov', 'Roman', 3.7, '[email protected]'),
('Shirokov', 'Maksim', 3.8, '[email protected]'),
('Yakovlev', 'Denis', 3.9, '[email protected]'),
('Zhukov', 'Andrey', 4.0, '[email protected]'),
('Nurmagambetov', 'Askar', 3.2, '[email protected]'),
('Ismailov', 'Nurzhan', 3.3, '[email protected]'),
('Kenzhegulov', 'Aidana', 3.4, '[email protected]'),
('Abishev', 'Daulet', 3.5, '[email protected]'),
('Zhumabekov', 'Aruzhan', 3.6, '[email protected]'),
('Tazhibayev', 'Samat', 3.7, '[email protected]'),
('Ospanov', 'Yerbol', 3.8, '[email protected]'),
('Mukashev', 'Timur', 3.9, '[email protected]'),
('Kairatuly', 'Murat', 3.1, '[email protected]'),
('Sultanov', 'Nurlan', 3.2, '[email protected]'),
('Zhakypov', 'Bolat', 3.3, '[email protected]'),
('Bektemirov', 'Ruslan', 3.4, '[email protected]'),
('Zhunusov', 'Marat', 3.5, '[email protected]'),
('Tazhibay', 'Azamat', 3.6, '[email protected]'),
('Nurgaliev', 'Erbol', 3.7, '[email protected]'),
('Kaliyev', 'Dauren', 3.8, '[email protected]'),
('Amanov', 'Aibek', 3.9, '[email protected]'),
('Omarov', 'Serik', 3.1, '[email protected]'),
('Madiyarov', 'Dulat', 3.2, '[email protected]'),
('Kenzhebayev', 'Galym', 3.3, '[email protected]'),
('Toktarov', 'Yerlan', 3.4, '[email protected]'),
('Ibrayev', 'Nursultan', 3.5, '[email protected]'),
('Ualiev', 'Baurzhan', 3.6, '[email protected]'),
('Yessenov', 'Daniyar', 3.7, '[email protected]'),
('Zhaxylykov', 'Ayan', 3.8, '[email protected]'),
('Sarsembayev', 'Zhassulan', 3.9, '[email protected]'),
('Abilov', 'Yerzhan', 3.1, '[email protected]'),
('Kenzhalin', 'Zhandos', 3.2, '[email protected]'),
('Zhaxybekov', 'Daulet', 3.3, '[email protected]'),
('Abdikarimov', 'Azamat', 3.4, '[email protected]'),
('Nurpeissov', 'Rustam', 3.5, '[email protected]'),
('Kazbekov', 'Kanat', 3.6, '[email protected]'),
('Yesbolatov', 'Eldar', 3.7, '[email protected]'),
('Bekbolatov', 'Arman', 3.8, '[email protected]'),
('Zhappasov', 'Rinat', 3.9, '[email protected]'),
('Yerbolatuly', 'Dauren', 3.1, '[email protected]'),
('Tursynov', 'Nurbol', 3.2, '[email protected]'),
('Zhailaubayev', 'Zhansaya', 3.3, '[email protected]'),
('Aidarkhanov', 'Talgat', 3.4, '[email protected]'),
('Mukhanov', 'Olzhas', 3.5, '[email protected]'),
('Zhumageldiev', 'Serik', 3.6, '[email protected]'),
('Nurlanuly', 'Bakhyt', 3.7, '[email protected]'),
('Yeskendir', 'Aruzhan', 3.8, '[email protected]'),
('Sadykov', 'Aslan', 3.9, '[email protected]'),
('Karimov', 'Rustem', 3.1, '[email protected]'),
('Zhunussov', 'Erbol', 3.2, '[email protected]'),
('Sagyndykov', 'Bekzat', 3.3, '[email protected]'),
('Suleimen', 'Bauyrzhan', 3.4, '[email protected]'),
('Nurmukhamedov', 'Chingiz', 3.5, '[email protected]'),
('Ismagul', 'Dastan', 3.6, '[email protected]'),
('Kairatov', 'Eldos', 3.7, '[email protected]'),
('Zhakanov', 'Farkhad', 3.8, '[email protected]'),
('Sapargaliev', 'Gani', 3.9, '[email protected]'),
('Kamalov', 'Hakim', 3.1, '[email protected]'),
('Bolatuly', 'Igor', 3.2, '[email protected]'),
('Duisenov', 'Jasulan', 3.3, '[email protected]'),
('Ermekov', 'Kairat', 3.4, '[email protected]'),
('Gabdullin', 'Linar', 3.5, '[email protected]'),
('Zhumadilov', 'Madi', 3.6, '[email protected]');
INSERT INTO disciplines (course_name,credits,prereq)VALUES
('WEB',5,''),
('JAVA',5,''),
('AKT',4,''),
('FZ',2,''),
('BACKEND',6,'DBMS'),
('DBMS',5,'WEB'),
('ENGLISH',4,''),
('MATH',6,''),
('C++',4,''),
('C#',5,'');
INSERT INTO teachers (lastname, firstname)
VALUES
('Байдулла', 'Аяулым'),
('Меирманова', 'Улжан'),
('Өмірәлі', 'Айкүміс'),
('Солтангельдинова','Мадина'),
('Батырхан', 'Серік'),
('Толегенов', ' Азамат '),
(' Шойынбек','Темірлан');
INSERT INTO audiences(audience_num, capacity) VALUES
(532, 28),
(515, 28),
(529, 28),
(505, 28),
(513, 28);
INSERT INTO schedule (audience_id, disc_id, teacher_id, time, weekday) VALUES
(3, 7, 2, '2023-12-06 09:00:00', 'Monday'),
(1, 5, 4, '2023-12-07 10:30:00', 'Tuesday'),
(2, 9, 3, '2023-12-08 13:00:00', 'Wednesday'),
(4, 2, 1, '2023-12-09 11:00:00', 'Thursday'),
(5, 10, 6, '2023-12-10 14:30:00', 'Friday'),
(3, 8, 5, '2023-12-13 09:30:00', 'Monday'),
(2, 3, 7, '2023-12-14 12:00:00', 'Tuesday'),
(1, 6, 4, '2023-12-15 15:00:00', 'Wednesday'),
(4, 1, 2, '2023-12-16 10:30:00', 'Thursday'),
(5, 4, 3, '2023-12-17 14:00:00', 'Friday'),
(3, 7, 6, '2023-12-20 08:30:00', 'Monday'),
(2, 5, 1, '2023-12-21 11:30:00', 'Tuesday'),
(1, 9, 2, '2023-12-22 13:45:00', 'Wednesday'),
(4, 8, 5, '2023-12-23 09:15:00', 'Thursday'),
(5, 2, 4, '2023-12-24 15:45:00', 'Friday'),
(3, 10, 3, '2023-12-27 10:00:00', 'Monday'),
(2, 6, 6, '2023-12-28 12:45:00', 'Tuesday'),
(1, 3, 1, '2023-12-29 14:15:00', 'Wednesday'),
(4, 7, 4, '2023-12-30 08:00:00', 'Thursday'),
(5, 5, 2, '2024-01-03 11:45:00', 'Friday');
INSERT INTO dis_teach (teacher_id, discipline_id) values
(1,1),
(1,2),
(2,9),
(3,3),
(5,5),
(5,8),
(6,6),
(7,7),
(7,10),
(4,4);
insert into stud_dis values
(1,1,1),
(2,2,1),
(3,9,2),
(4,3,3),
(5,5,5),
(6,8,5),
(7,6,6),
(8,7,7),
(9,10,7),
(10,4,2),
(11,1,1),
(12,2,1),
(13,9,2),
(14,3,3),
(15,5,5),
(16,8,5),
(17,6,6),
(18,7,7),
(19,10,7),
(20,4,2),
(21,1,1),
(22,2,1),
(23,9,2),
(24,3,3),
(25,5,5),
(26,8,5),
(27,6,6),
(28,7,7),
(29,10,7),
(30,4,2),
(31,1,1),
(32,2,1),
(33,9,2),
(34,3,3),
(35,5,5),
(36,8,5),
(37,6,6),
(38,7,7),
(39,10,7),
(40,4,2),
(41,1,1),
(42,2,1),
(43,9,2),
(44,3,3),
(45,5,5),
(46,8,5),
(47,6,6),
(48,7,7),
(49,10,7),
(50,4,2),
(51,1,1),
(52,2,1),
(53,9,2),
(54,3,3),
(55,5,5),
(56,8,5),
(57,6,6),
(58,7,7),
(59,10,7),
(60,4,2),
(61,1,1),
(62,2,1),
(63,9,2),
(64,3,3),
(65,5,5),
(66,8,5),
(67,6,6),
(68,7,7),
(69,10,7),
(70,4,2),
(71,1,1),
(72,2,1),
(73,9,2),
(74,3,3),
(75,5,5),
(76,8,5),
(77,6,6),
(78,7,7),
(79,10,7),
(80,4,2),
(81,1,1),
(82,2,1),
(83,9,2),
(84,3,3),
(85,5,5),
(86,8,5),
(87,6,6),
(88,7,7),
(89,10,7),
(90,4,2),
(91,1,1),
(92,2,1),
(93,9,2),
(94,3,3),
(95,5,5),
(96,8,5),
(97,6,6),
(98,7,7),
(99,10,7),
(100,4,2),
(101,1,1),
(102,2,1),
(103,9,2),
(104,3,3),
(105,5,5),
(106,8,5),
(107,6,6),
(108,7,7),
(109,10,7),
(110,4,2),
(111,1,1),
(112,2,1),
(113,9,2),
(114,3,3),
(115,5,5),
(116,8,5),
(117,6,6),
(118,7,7),
(119,10,7),
(120,4,2),
(121,1,1),
(122,2,1),
(123,9,2),
(124,3,3),
(125,5,5),
(126,8,5),
(127,6,6),
(128,7,7),
(129,10,7),
(130,4,2),
(131,1,1),
(132,2,1),
(133,9,2),
(134,3,3),
(135,5,5),
(136,8,5),
(137,6,6),
(138,7,7),
(139,10,7),
(140,4,2),
(141,1,1),
(142,2,1),
(143,9,2),
(144,3,3),
(145,5,5),
(146,8,5),
(147,6,6),
(148,7,7),
(149,10,7),
(150,4,2),
(151,1,1),
(152,2,1),
(153,9,2),
(154,3,3),
(155,5,5),
(156,8,5),
(157,6,6),
(158,7,7),
(159,10,7),
(160,4,2),
(161,1,1),
(162,2,1),
(163,9,2),
(164,3,3),
(165,5,5),
(166,8,5),
(167,6,6),
(168,7,7),
(169,10,7),
(170,4,2),
(171,1,1),
(172,2,1),
(173,9,2),
(174,3,3),
(175,5,5),
(176,8,5),
(177,6,6),
(178,7,7),
(179,10,7),
(180,4,2),
(181,1,1),
(182,2,1),
(183,9,2),
(184,3,3),
(185,5,5),
(186,8,5),
(187,2,1),
(188,9,2),
(189,2,2),
(190,5,5),
(191,7,3),
(192,5,6);
select students.lastname, students.firstname, disciplines.course_name
from students
left join stud_dis on students.id = stud_dis.stud_id
left join disciplines on disciplines.id = stud_dis.discipline_id;
select disciplines.course_name, teachers.lastname,teachers.firstname
from disciplines
left join dis_teach on disciplines.id = dis_teach.discipline_id
left join teachers on teachers.id = dis_teach.teacher_id;
select teachers.lastname , teachers.firstname, count(stud_id)
from teachers
left join stud_dis on teachers.id = stud_dis.teacher_id
left join students on stud_dis.stud_id = students.id
group by teachers.lastname, teachers.firstname;
select * from students order by gpa desc limit 10;
select disciplines.course_name, audiences.audience_num
from disciplines
inner join schedule on disciplines.id = schedule.disc_id
inner join audiences on audiences.id = schedule.audience_id
where weekday like '%Monday%';
select disciplines.course_name, avg(students.gpa)
from disciplines
inner join stud_dis on disciplines.id = stud_dis.discipline_id
inner join students on students.id = stud_dis.stud_id
where disciplines.course_name = 'DBMS'
group by disciplines.course_name;
select audiences.audience_num, disciplines.course_name, schedule.weekday
from audiences
inner join schedule on audiences.id = schedule.audience_id
inner join disciplines on disciplines.id = schedule.disc_id
where audiences.capacity > 28;
select * from students where firstname like 'А%';
select * from students where gpa>2.67;
select teachers.lastname,teachers.firstname, disciplines.course_name
from teachers
inner join dis_teach on teachers.id = dis_teach.teacher_id
inner join disciplines on disciplines.id = dis_teach.discipline_id
where disciplines.credits = (select max(disciplines.credits) from disciplines);
select disciplines.course_name, teachers.lastname, teachers.firstname, count(stud_dis.stud_id)
from disciplines
inner join stud_dis on disciplines.id=stud_dis.discipline_id
inner join teachers on teachers.id=stud_dis.teacher_id
group by disciplines.course_name, teachers.lastname, teachers.firstname
having count(stud_dis.stud_id)=(select min(cnt) from (select count(stud_dis) as cnt from stud_dis
group by discipline_id) as subquery);
create or replace procedure insert_stud(lastname1 varchar,firstname1 varchar,gpa1 float,email1 varchar)
language plpgsql as $$
begin
insert into students(lastname,firstname,gpa,email) values(lastname1,firstname1,gpa1,email1);
end; $$;
call insert_stud('Amangeldi','Aray',3.5,'[email protected]');
select * from students where firstname = 'Aray';
create or replace procedure update_schedule(sched_id int,audic varchar,times timestamp,weekdays varchar)
language plpgsql as $$
begin
update schedule set audience_id = (select id from audiences
where audiences.audience_num = audic), time = times , weekday = weekdays
where id = sched_id;
end; $$;
call update_schedule(1,'102','2023-12-06 07:45:00','Monday');
select * from schedule where id =1;
create or replace function get_student_schedule(student_id INT) returns table (
audience_id int,
disc_id int,
teacher_id int,
schedule_time timestamp,
schedule_weekday varchar
) language plpgsql as $$
begin
return query
select s.audience_id, s.disc_id, s.teacher_id, s.time, s.weekday
from schedule s
inner join stud_dis sd ON s.disc_id = sd.discipline_id AND s.teacher_id = sd.teacher_id
where sd.stud_id = student_id;
end;$$;
select * from get_student_schedule(1);
create or replace function is_audience_available(audience_id int, check_time timestamp)
returns boolean as $$
declare
is_available boolean;
begin
select not exists (
select 1 from schedule s
where s.audience_id = is_audience_available.audience_id
and s.time = check_time
) into is_available;
return is_available;
end;
$$ language plpgsql;
select * from is_audience_available('1','2023-12-06 09:00:00');
Select audiences.audience_num ,disciplines.course_name,teachers .lastname,teachers .firstname From schedule
join audiences on schedule.audience_id=audiences.id
join teachers on schedule.teacher_id=teachers.id
join disciplines on schedule.disc_id=disciplines.id
where schedule.weekday like '%Monday%'
group by audiences.audience_num ,disciplines.course_name,teachers.lastname,teachers.firstname;
Write, Run & Share PostgreSQL queries online using OneCompiler's PostgreSQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler's PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as 'PostgreSQL' and start writing queries to learn and test online without worrying about tedious process of installation.
PostgreSQL is a open source relational database system and is also knows as Postgres.
CREATE command is used to create a table, schema or an index.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);
ALTER command is used to add, modify or delete columns or constraints from the database table.
ALTER TABLE Table_name ADD column_name datatype;
TRUNCATE command is used to delete the data present in the table but this will not delete the table.
TRUNCATE table table_name;
DROP command is used to delete the table along with its data.
DROP TABLE table_name;
RENAME command is used to rename the table name.
ALTER TABLE table_name1 RENAME to new_table_name1;
INSERT Statement is used to insert new records into the database table.
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Select statement is used to select data from database tables.
SELECT column1, column2, ...
FROM table_name;
UPDATE statement is used to modify the existing values of records present in the database table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
DELETE statement is used to delete the existing records present in the database table.
DELETE FROM table_name where condition;