2-laboratoriya ishi birinchi test ma`lumotlar bazasni yaratish

2-LABORATORIYA ISHI

1. 
   Birinchi test ma`lumotlar bazasni 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 barcha ma`lumotlarini tanlab olish so`rovini yozing.
1.2.-Talabalarni faqat name, class, gender bo`yicha ma`lumotlarini tanlash so`rovini yozing.
1.3.- Talabalar jadval nomini o`zbekcha qayta taxallus qo`ying
1.4.- Talabalar jadvali maydonlarini o`zbekcha qayta taxallus qo`ying
1.5.- student_sum jadvalidagi `social`, `science`, `math` maydonlarni o`rtachasini chiqaring
2-topshiriq. Saralash. ORBER BY
2.1.-Talabalar ma`lumotlarini name maydoni bo`yicha o`sish tartibida saralab olish so`rovini yozing.
2.2.- Talabalar ma`lumotlarini name maydoni bo`yicha kamayish tartibida saralab olish so`rovini yozing.
2.3- student_sum jadvalidagi `social`, `science`, `math` maydonlarni o`rtachasi bo`yicha o`sish bo`yicha saralashni amalga oshiring
2.4- student_sum jadvalidagi `social`, `science`, `math` maydonlarni o`rtachasi bo`yicha kamayish bo`yicha saralashni amalga oshiring.
2.5- Talabalar ma`lumotlarini mark va name maydoni bo`yicha kamayish tartibida saralab olish so`rovini yozing.
2.6- Talabalar ma`lumotlarini mark bo`yicha o`sish va name maydoni bo`yicha kamayish tartibida saralab olish so`rovini yozing.
2.7- student_sum jadvalining `social`, `science`, `math` maydonlari bo`yicha kamayish tartibida saralash so`rovini yozing.
3.-topshiriq. Mantiqiy operatorlar.