3-laboratoriya ishi. Standtart funksiyalar foydanalish. Agretat funksiyasiyalar

3-LABORATORIYA ISHI. Standtart funksiyalar foydanalish. Agretat funksiyasiyalar.

1. 
   Birinchi test ma`lumotlar bazasini yaratish
   

CREATE DATABASE test1;

2. 
   Students jadvalini yaratish
   

CREATE TABLE IF NOT EXISTS `student` (
`id` int(2) NOT NULL DEFAULT '0',
`name` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '',
`class` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '',
`mark` int(3) NOT NULL DEFAULT '0',
`gender` varchar(6) CHARACTER SET utf8 NOT NULL DEFAULT 'male'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `student_sum` (

`id` int(2) NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '',
`class` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '',
`social` int(3) NOT NULL,
`science` int(3) NOT NULL,
`math` int(3) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

3. 
   Students jadvaliga ma`lumotlar qo`shish
   

INSERT INTO `student` (`id`, `name`, `class`, `mark`, `gender`) VALUES
(1, 'John Deo', 'Four', 75, 'female'),
(2, 'Max Ruin', 'Three', 85, 'male'),
(3, 'Arnold', 'Three', 55, 'male'),
(4, 'Krish Star', 'Four', 60, 'female'),
(5, 'John Mike', 'Four', 60, 'female'),
(6, 'Alex John', 'Four', 55, 'male'),
(7, 'My John Rob', 'Five', 78, 'male'),
(8, 'Asruid', 'Five', 85, 'male'),
(9, 'Tes Qry', 'Six', 78, 'male'),
(10, 'Big John', 'Four', 55, 'female'),
(11, 'Ronald', 'Six', 89, 'female'),
(12, 'Recky', 'Six', 94, 'female'),
(13, 'Kty', 'Seven', 88, 'female'),
(14, 'Bigy', 'Seven', 88, 'female'),
(15, 'Tade Row', 'Four', 88, 'male'),
(16, 'Gimmy', 'Four', 88, 'male'),
(17, 'Tumyu', 'Six', 54, 'male'),
(18, 'Honny', 'Five', 75, 'male'),
(19, 'Tinny', 'Nine', 18, 'male'),
(20, 'Jackly', 'Nine', 65, 'female'),
(21, 'Babby John', 'Four', 69, 'female'),
(22, 'Reggid', 'Seven', 55, 'female'),
(23, 'Herod', 'Eight', 79, 'male'),
(24, 'Tiddy Now', 'Seven', 78, 'male'),
(25, 'Giff Tow', 'Seven', 88, 'male'),
(26, 'Crelea', 'Seven', 79, 'male'),
(27, 'Big Nose', 'Three', 81, 'female'),
(28, 'Rojj Base', 'Seven', 86, 'female'),
(29, 'Tess Played', 'Seven', 55, 'male'),
(30, 'Reppy Red', 'Six', 79, 'female'),
(31, 'Marry Toeey', 'Four', 88, 'male'),
(32, 'Binn Rott', 'Seven', 90, 'female'),
(33, 'Kenn Rein', 'Six', 96, 'female'),
(34, 'Gain Toe', 'Seven', 69, 'male'),
(35, 'Rows Noump', 'Six', 88, 'female');

INSERT INTO `student_sum` (`id`, `name`, `class`, `social`, `science`, `math`) VALUES

(1, 'John Deo', 'Four', 75, 84, 78),
(2, 'Max Ruin', 'Three', 54, 67, 85),
(3, 'Arnold', 'Three', 78, 65, 90),
(4, 'Krish Star', 'Four', 50, 51, 53),
(5, 'John Mike', 'Four', 80, 78, 89),
(6, 'Alex John', 'Four', 78, 60, 60),
(7, 'My John Rob', 'Five', 77, 68, 83),
(8, 'Asruid', 'Five', 55, 50, 55),
(9, 'Tes Qry', 'Six', 68, 60, 62),
(10, 'Big John', 'Four', 65, 66, 76);

2. QISM ASOSIY

1-topshiriq. SELECT
1.1.-Talabalar name, class maydonlarini bitta maydon qilib chiqarish so`rovini yozing.
1.2.-Talabalarni name maydonini qiymatini bosh harfini olish asosida ma`lumotlar chiqarish so`rovini yozing.
1.3.- Talabalarni name, class maydonlarinining qiymatlari KATTA harflarda chiqaring.
1.4.- Talabalarni name, class maydonlarinining qiymatlari KICHIK harflarda chiqaring.
1.5.- Talabalarni mark baldan bahoga o`tkazib chiqaring. Bunda 60 dan 73 gacha 3 baho, 74-86 gacha 4 baho, 87-100 gacha 5 baho.
1.6 student_sum jadvalidan ham `social`, `science`, `math baldan bahoga o`tkazib chiqaring. Bunda 60 dan 73 gacha 3 baho, 74-86 gacha 4 baho, 87-100 gacha 5 baho.

2-topshiriq. Agregat funksiyalar
2.1.-Talabalar sonini aniqlash so`rovini yozing.
2.2.- Talabalar ichida erkaklar sonini aniqlash so`rovini yozing.
2.3- Talabalar ichida bahosi 75dan katta ayollar sonini aniqlash so`rovini yozing.
2.4- student_sum jadvalidagi `social maydoni ichida eng katta qiymat ega bo`lgan talabani aniqlash so`rovini yozing.
2.5- student_sum jadvalidagi ` math maydoni ichida eng kichik qiymat ega bo`lgan talabani aniqlash so`rovini yozing.
2.6 O`gil bolalarni `social`, `science`, `math` maydonlari bo`yicha o`rtacha baholarini aniqlash so`rovini yozing.
2.6- O`gil bolalarni `social`, `science`, `math` maydonlari bo`yicha o`rtacha baholarini aniqlash so`rovini yozing.
2.7- student_sum jadvalining class bo`yicha guruhlashni amalga oshiring.
2.8- student_sum jadvalining class bo`yicha guruhlashni amal oshirib, `math` bo`yicha o`rtasi 70% katta bo`lganlarini chiqarish so`rovini yozing.
2.9- student_sum jadvalining class bo`yicha guruhlashni amal oshirib, class qiymatlari 3 dan katta bo`lganlari chiqarish so`rovini yozing.