Baza amaliy javoblar. “apteka”

Baza amaliy javoblar.

“apteka” bazasini shakillantirish.

Create database apteka;

Ushbu apteka bazasiga 3 ta jadval yaratamiz.

Jadval nomlari: Haridor, mahsulot va savdo.

• 
  Hodim jadvali yaratilishi.
  

Create table hodim(id_h int(5) not null primary key auto_increment, Ism varchar(20), Familiya varchar(20), tel_raqam int(10), manzil varchar(20));

• 
  Savdo jadvali yaratilishi.
  

Create table savdo(id_s int(5) not null primary key auto_increment, id_h int(5), id_d int(5), sotilgan_sana date, foreign key (id_h) references hodim(id_h) on update cascade on delete cascade, foreign key (id_d) references dori(id_d) on update cascade on delete cascade);

• 
  dori jadvali yaratilishi.
  

Create table dori(id_d int(5) not null primary key auto_increment, nomi varchar(20), firma varchar(30), narxi int(30));

Jadvallarga ma’lumot kiritish.

Savdo jadvaliga ma’lumot kiritish.

Insert into hodim values(null, ‘Behzod’, ‘Jumaboyev’, 999926507, ‘Tinchlik’);

Indexlar yaratish.

• 
  Oddiy indeks yaratish.
  

Create index hodimlar on hodim (nomi);

• 
  Yaratilgan indeksni ko’rish.
  

Show index from hodim;

• 
  Murakkab indeks yaratish.
  

Create unique index firmalar on dori (firma);

• 
  Indeksni jadvaldan o’chirish.
  

Alter table Mahsulot drop index firmalar;

Viewlar foydalanish.

• 
  view yaratish.
  

Create view darmon as select id_d, firma, ism, familiya, sotilgan_sana from dori, savdo, hodim order by firma asc;

• 
  Yaratilgan view ni ko’rish.
  

Show create view darmon \G;

Joinlardan foydalanish.

• 
  Inner join.
  

Select dori.id_d, dori.firma, savdo.Id_s, savdo.sotilgan_sana from dori inner join savdo on dori.id_d =savdo.id_s;

• 
  Left join.
  

Select dori.id_d, dori.firma, savdo.Id_s, savdo.sotilgan_sana from dori left join savdo on dori.id_d =savdo.id_s;

• 
  Right join.
  

Select dori.id_d, dori.firma, savdo.Id_s, savdo.sotilgan_sana from dori right join savdo on dori.id_d =savdo.id_s;

Where and or not dan foydalanish.

dori jadvalidan narxi ustunidagi malumotlarni 1000000dan baland 2000000dan kichik malumotlarni jadvalga chop etadi.

Select *from dori where(narxi>1000000 and narxi<2000000);

dori jadvalidan narxi ustunidagi malumotlarni 1000000dan baland va 2000000dan kichik malumotlarni jadvalga chop etadi.

Select *from dori where(narxi>1000000 or narxi<2000000);

dori jadvalidan narxi ustunidagi malumotlarni 1000000dan baland 2000000dan kichik malumotlardan tashqari ma’lumotlarni jadvalga chop etadi.

Select *from dori where not(narxi>1000000 and narxi<2000000);

hodim jadvalidagi id_h si 2dan 5gacha bo’lgan malumotlar oralig’idagi ma’lumotlarni jadvalga chop etadi.

Select *from hodim where id_h between 2 and 5;

Hodim jadvalidagi ism ustunidagi “Ali” malumoti bo’lgan satrni jadvalga chop etadi.

Select *from hodim where ism in(‘Ali’);