Sql so’rovlar create database database name – ma’lumotlar bazasini yaratish create table kurslar(name type, …) – Ma’lumotlar bazasiga jadval qo’shish integer, int, int(30) – Butun turdagi ma’lumotlarni bildiradi

RENAME TABLE teacher TO myteacher

13. 
    DELETE
    

DELETE FROM courses
WHERE teacher_id=1

14. 
    ALTER ADD COLUMN
    

ALTER TABLE courses
ADD COLUMN during CHAR(10)

15. 
    DROP COLUMN
    

ALTER TABLE courses
DROP xona_raqami

16. 
    SUM – yig’indisini aniqlaydi
    

SELECT SUM(kurs_narxi)
FROM kurslar

17. 
    COUNT – qatorlari sonini aniqlaydi
    

SELECT COUNT(id)
FROM kurslar

18. 
    MIN – eng kichik qiymatini aniqlaydi
    

SELECT MIN(kurs_narxi) AS eng_kichik_narx
FROM kurslar

19. 
    MAX – eng katta qiymatini aniqlaydi
    

SELECT MAX(kurs_narxi) AS eng_katta_narx
FROM kurslar

20. 
    AVG – o’rtacha qiymatini aniqlaydi
    

SELECT AVG(kurs_narxi) AS urtacha_qiymat
FROM kurslar

21. 
    LIKE % - ma’lumotlar ichidan qidiruv berish
    

SELECT *
FROM kurslar
WHERE kurs_nomi LIKE "%on%"

22. 
    LIKE _ - ma’lumotlar ichidan belgini positsiyasi orqali qidiruv berish
    

SELECT *
FROM kurslar
WHERE kurs_nomi LIKE "____Cad"

23. 
    GROUP BY ustun ma’lumotlarni guruhlash (takrorlanadigan ma’lumotlarni)
    

SELECT uqituvchi_id, SUM(kurs_narxi)
FROM kurslar
GROUP BY uqituvchi_id

24. 
    ORDER BY Ma’lumotlarni tartib bo’yicha chiqarish
    

SELECT * FROM kurslar
ORDER BY uqituvchi_id ASC

25. 
    Jadvallarni bog’lash FOREIGN KEY
    

CONSTRAINT birinchi
FOREIGN KEY(uqituvchi_id) REFERENCE uqituvchilar(id),
CONSTRAINT ikkinchi
FOREIGN KEY(kurs_id) REFERENCE kurslar(id)

26. 
    Yaratilgan jadvallarni bog’lash
    

ALTER TABLE kurslar
CONSTRAINT uchinchi
ADD FOREIGN KEY(uqituvchi_id) REFERENCE uqituvchilar(id)

27. 
    Bog’langan jadvallarni bog’lovchisini o’chirish
    

ALTER TABLE kurslar
DROP CONSTRAINT birinchi


ALTER TABLE kurslar
DROP FOREIGN KEY birinchi

28. 
    INTERSECT – 2004 yildan boshlab tug’ilganlar bilan 2006 yildan oldin tug’ilganlarni KESISHMASI
    

SELECT *
FROM uquvchilar
WHERE T_sanasi>"2004-01-01"
INTERSECT
SELECT *
FROM uquvchilar
WHERE T_sanasi<"2006-12-31"

29. 
    UNION – 1997 yildan oldin tug’ilganlar bilan 2006 yildan keyin tug’ilganlarni BIRLASHTIRISH
    

SELECT *
FROM uquvchilar
WHERE T_sanasi<"1997-01-01"
UNION
SELECT *
FROM uquvchilar
WHERE T_sanasi>"2006-01-01"

30. 
    EXCEPT – beeline telefon raqam ishlatadigan o’quvchilardan tashqari barcha o’quvchilarni chiqarish (AYIRMA)
    

SELECT *
FROM uquvchilar
EXCEPT
SELECT *
FROM uquvchilar
WHERE tel LIKE "91%" OR tel LIKE "90%"

31. 
    UNION ALL – To’plamlarni birlashtiradi va ikkala to’plamni kesishgan qismini takrorlaydi
    

SELECT *
FROM uqituvchilar
WHERE oyligi>2000000
UNION ALL
SELECT *
FROM uqituvchilar
WHERE oyligi<4000000

32. 
    FISH ni uzunligini aniqlaydi
    

SELECT FISH, LENGTH(FISH)
FROM uqituvchilar

33. 
    FISH ni chapidan va o’ng tomonidan matnlarni qirqib olish
    

SELECT FISH, LEFT(FISH, 3), RIGHT(FISH, 5)
FROM uqituvchilar

34. 
    Matnni ixtiyoriy qismidan matnni qirqib olish
    

SELECT tel, SUBSTRING(tel, 5, 4)
FROM uqituvchilar

35. 
    Matn ichidan biror so’zni boshqa so’zga o’zgartirish
    

SELECT FISH, REPLACE(FISH, "bek", "jon")
FROM uqituvchilar

36. 
    Matnlarni katta va kichik harflarga o’tkazish o’tkazish
    

SELECT FISH, UPPER(FISH), LOWER(FISH)
FROM uquvchilar

37. 
    Matnlarni qo’shish
    

SELECT CONCAT(FISH," - ", T_sanasi," - ", tel)
FROM uquvchilar

38. 
    Matnni orasidan so’zni turgan positsiyasini aniqlash
    

SELECT FISH, POSITION("ova" IN FISH)
FROM uqituvchilar

39. 
    LENGTH(FISH) -- uzunlikni aniqlaydi
    

SELECT FISH, LENGTH(FISH)
FROM uqituvchilar

40. 
    LEFT(FISH, 10) -- chapdan qirqib olish
    

SELECT FISH, LEFT(FISH, 2)
FROM uqituvchilar
Masalan: Abdullayeva Iroda Natija: Ab

1. 
   RIGHT(FISH, 2) -- o'ngdan qirqib olish
   
2. 
   SUBSTRING(FISH, 3, 5) -- matnni ixtiyoriy qismidan qirqish
   

SELECT FISH, SUBSTRING(FISH, 5, 3)
FROM uqituvchilar
Masalan: Abdullayeva Iroda Natija: lla
SELECT FISH, SUBSTRING(FISH, 3)
FROM uqituvchilar
Masalan: Abdullayeva Iroda Natija: ullayeva Iroda

3. 
   UPPER(FISH), LOWER(FISH) -- matnlarni katta va kichik harflarga o'tkazish
   

SELECT FISH, UPPER(FISH)
FROM uqituvchilar
Masalan: Abdullayeva Iroda Natija: ABDULLAYEVA IRODA

4. 
   CONCAT(FISH," - ", ish_staji," - ", tel) -- matnlarni birlashtirish
   

SELECT FISH, CONCAT("+998", tel)
FROM uqituvchilar
Masalan: 916547898 Natija: [phone]

5. 
   REPLACE(FISH, "bek", "jon") -- matn ichidan so'zni o'zgartirish
   

SELECT FISH, REPLACE(FISH, "bek", "jon")
FROM uquvchilar
Masalan: Madaminov Suhrobbek Natija: Madaminov Suhrobjon

6. 
   POSITION("a" IN FISH) -- matn ichida so'z nechanchi positsiyadaligini aniqlaydi
   

SELECT kurs_narxi, LEFT(kurs_narxi, POSITION("0" IN kurs_narxi)-1)
FROM kurslar
Masalan: 650000 Natija: 65
Masalan: 400000 Natija: 4

7. 
   INNER JOIN
   

SELECT kurslar.kurs_nomi, uqituvchilar.FISH
FROM kurslar
INNER JOIN uqituvchilar
ON kurslar.uqituvchi_id = uqituvchilar.id

8. 
   INNER JOIN qisqartma (AS) bilan yozilish
   

SELECT K.kurs_nomi, U.FISH
FROM kurslar AS K
INNER JOIN uqituvchilar AS U
ON K.uqituvchi_id = U.id

9. 
   LEFT JOIN
   

SELECT K.kurs_nomi, K.uqituvchi_id, U.id, U.FISH
FROM kurslar AS K
LEFT JOIN uqituvchilar AS U
ON K.uqituvchi_id = U.id

10. 
    RIGHT JOIN
    

SELECT K.kurs_nomi, K.uqituvchi_id, U.id, U.FISH
FROM kurslar AS K
RIGHT JOIN uqituvchilar AS U
ON K.uqituvchi_id = U.id

11. 
    FULL OUTER JOIN
    

SELECT K.kurs_nomi, K.uqituvchi_id, U.id, U.FISH
FROM kurslar AS K
LEFT OUTER JOIN uqituvchilar AS U
ON K.uqituvchi_id = U.id
UNION
SELECT K.kurs_nomi, K.uqituvchi_id, U.id, U.FISH
FROM kurslar AS K
RIGHT OUTER JOIN uqituvchilar AS U
ON K.uqituvchi_id = U.id

12. 
    Murakkab so’rov-1: eng ko’p oyliq oladigan o’qituvchi ma’lumotlarini chiqarish
    

SELECT *
FROM uqituvchilar
WHERE oyligi = (SELECT MAX(oyligi) FROM uqituvchilar)

13. 
    Murakkab so’rov-2: o’rtacha oylikdan kam oylik oladiga o’qituvchilar ro’yxati
    

SELECT *
FROM uqituvchilar
WHERE oyligi < (SELECT AVG(oyligi) FROM uqituvchilar)

14. 
    Murakkab so’rov-3: 2000-yilda tug’ilgan o’quvchilar qatnashadigan kurslar ro’yxati
    

SELECT *
FROM kurslar
WHERE id IN
(SELECT kurs_id FROM uquvchilar WHERE T_sanasi LIKE "2000%")

15. 
    Murakkab so’rov-4: eng katta kurs narxli kursni o’chirish
    

DELETE FROM kurslar
WHERE kurs_narxi = (SELECT MAX(kurs_narxi) FROM kurslar)

16. 
    Murakkab so’rov-5: eng kichik chegirma berilgan kursni narxini 1200000 deb o’zgartirish
    

UPDATE kurslar
SET kurs_narxi=1200000
WHERE chegirma_foizi =(SELECT MIN(chegirma_foizi) FROM kurslar)

17. 
    Murakkab so’rov-6: courses nomli jadvaldagi ma’lumotlarni kurslar nomli jadvalga kiritish
    

INSERT INTO kurslar(kurs_nomi, kurs_narxi)
SELECT course_name, course_price
FROM courses

18. 
    Kiritilgan matn oldiga hello so’zini qo’shib chiqaruvchi funksiya yaratish
    

CREATE FUNCTION valijon(a VARCHAR(50))
RETURNS VARCHAR(50) DETERMINISTIC
RETURN CONCAT("hello ", a)

41. 
    Kiritilgan matn ichida “va ” so’zi nechanchi positsiyadaligini aniqlovchi funksiya yaratish
    

CREATE FUNCTION male(a CHAR(50))
RETURNS INT(5) DETERMINISTIC
RETURN POSITION("va " IN a)

42. 
    Murakkab funksiya yaratish, qimmat yoki arzonligini aniqlovchi funksiya
    

delimiter $$
CREATE FUNCTION qimmat(a INT(20))
RETURNS CHAR(10)
BEGIN
DECLARE b CHAR(10);
if a>400000 then SET b="qimmat";
ELSE SET b="arzon";
END if;
RETURN(b);
END$$
delimiter;

43. 
    Trigger yaratish INSERT uchun, agar kurslar jadvaliga 0 dan kichik kurs narxi kiritilsa uni qiymatini musbat qiymatga avtomatik o’zgartirib beruvchi trigger
    

delimiter $$
CREATE TRIGGER kiritish
BEFORE INSERT
ON kurslar
FOR EACH ROW
if NEW.kurs_narxi < 0
then SET NEW.kurs_narxi=ABS(NEW.kurs_narxi);
END if; $$

Qo’llanilishi:

INSERT INTO kurslar(kurs_nomi, kurs_narxi)
VALUES ("Tarix", -500000)

44. 
    Trigger yaratish INSERT uchun, agar kurslar jadvalida chegirma_foizi ustuniga ma’lumot kiritilmasa avtamatik tarzda 0 qiymat deb oluvchi trigger.
    

delimiter $$
CREATE TRIGGER kiritish2
BEFORE INSERT
ON kurslar
FOR EACH ROW
if NEW.chegirma_foizi IS NULL
then SET NEW.chegirma_foizi=0;
END if; $$
Qo’llanilishi:
INSERT INTO kurslar(kurs_nomi, kurs_narxi, uqituvchi_id)
VALUES ("Ona tili", -300000, 19)

45. 
    Trigger yaratish UPDATE uchun, agar kurslar jadvalida chegirma_foizi 50 dan katta deb o’zgarish kiritilsa avtomatik tarzda uni qiymatini 49 deb belgilovchi trigger.
    

delimiter $$
CREATE TRIGGER yangilash
BEFORE UPDATE
ON kurslar
FOR EACH ROW
BEGIN
if NEW.chegirma_foizi >= 50
then SET NEW.chegirma_foizi=49;
ELSEIF NEW.chegirma_foizi < 0
then SET NEW.chegirma_foizi=0;
END if;
END $$
delimiter;

Qo’llanilishi:

UPDATE kurslar
SET chegirma_foizi=-30
WHERE id=5

46. 
    Trigger yaratish DELETE uchun, agar
    

delimiter $$
CREATE TRIGGER uchirish
BEFORE DELETE
ON kurslar
FOR EACH ROW
BEGIN
INSERT INTO uchirilgan_kurslar(kurs_nomi,kurs_narxi,chegirma_foizi,uqituvchi_id)
VALUE (OLD.kurs_nomi,OLD.kurs_narxi,OLD.chegirma_foizi,OLD.uqituvchi_id);
END$$
delimiter;

Qo’llanilishi:

DELETE FROM kurslar
WHERE id=11