Guruh 13 laboratoriya ishi mavzu: Murakkab so‘rovlar yaratish. Top, limit yoki rownum

Mazamqulov Sultonbek

714-20 guruh

13 - LABORATORIYA ISHI

Mavzu: Murakkab so‘rovlar yaratish. TOP, LIMIT yoki ROWNUM
Ishdan maqsad: Berilgan predmet soha ma`lumotlar bazasidan foydalanib TOP, LIMIT yoki ROWNUM lardan foydalanishni o`rganish.

Masalani qo`yilishi: Predmet soha ma`lumotlar bazasi shakllantirilgandan so`ng undan unumli foydalanishni tashkil etish maqsadida TOP, LIMIT yoki ROWNUM lar orqali so`rovlar yaratish.
Ishni bajarish tartibi

Avvalgi bajargan korpus nomli jadvalni chaqirib olamiz.

SELECT * FROM kollej_ustoz;

1. 
   So`rov: SELECT * FROM  kollej_ustoz LIMIT 3;
   

Buyrug`ini kiritamiz natijada birinchi uchta satr hosil bo`ladi

Natija:

2. 
   Select FISH FROM kollej_ustoz LIMIT  2,3;
   

3.So’rov:SELECT *  FISH FROM kollej_ustoz

ORDER BY kollej_ustoz DESC LIMIT  2,3;

Natija:

4.So’rov:SELECT Manzil FROM kollej_ustoz

ORDER BY Manzil DESC LIMIT 2,3;

Natija:

14 - LABORATORIYA ISHI

Mavzu: SQL tilida INDEX yaratish

Ishdan maqsad: MySQL maxsus ustun qiymatlariga ega qatorlarni tezda topish uchun indekslardan foydalanishni o`rganish.

So`rov: SHOW INDEX FROM kollej_ustoz;

Natija:


2.Select * from Ustoz;

Create index Lavozim on ustoz(ID,FISH);

3. Yaratgan indeksimizni ko’ramiz:

show indexes from Ustoz;

Natija:


4. Select * from kollej_ustoz;

Create index oylik on ustoz(ID,FISH,oylik);

Natija:


5.So`rov: Show indexes from Customers;

Natija: 

6.“customers” jadvalidan “city” nomli indeksni o’chiramiz:

So`rov: Alter table customers drop city;

Natija:

15 - LABORATORIYA ISHI

Mavzu: Tasavvur (VIEW) yaratish va undan foydalanish
Ishdan maqsad: Berilgan predmet soha ma`lumotlar bazasidan foydalanib tasavvurlar yaratish.

Ishni bajarish tartibi

1. 
   Customer jadvalidan Uzbekiston ga teng Viewni yaratamiz:
   

So`rov: Create view Customers_Suppliers_contactname as select CustomerID,address from Customers where country='Uzbekistan';

select * FROM Customers_Suppliers_contactname;

Natila;

16 LABORATORIYA ISHI

Mavzu: SQLda protseduralar yaratish
Ishdan maqsad: Berilgan predmet soha ma`lumotlar bazasi uchun protsedura yaratish.

Ishni bajarish tartibi

1. 
   “customers, suppliers” jadvalini birlashtirib chiqiramiz:
   

select * from customers,suppliers;

Delimiter //

Create procedure oylik()

Begin

Select * from customers;

End//

Call oylik(); //

Natija;