PostgreSQL Tutorial

SELECT
    COUNT("id") AS jami,
    SUM(CASE WHEN lang = 'en' THEN 1 ELSE 0 END) AS en_foiz,
    SUM(CASE WHEN lang = 'uz' THEN 1 ELSE 0 END) AS uz_foiz,
    SUM(CASE WHEN lang = 'ru' THEN 1 ELSE 0 END) AS ru_foiz,
    (SUM(CASE WHEN lang = 'en' THEN 1 ELSE 0 END) / COUNT("id")) * 100 AS en_foiz_percent,
    (SUM(CASE WHEN lang = 'uz' THEN 1 ELSE 0 END) / COUNT("id")) * 100 AS uz_foiz_percent,
    (SUM(CASE WHEN lang = 'ru' THEN 1 ELSE 0 END) / COUNT("id")) * 100 AS ru_foiz_percent
FROM users;

Ushbu query orqali siz telegram botingizda uzbek, english, rus foydalanuvchilarini foizda ola olasiz. Statistikada juda ham kerakli bo'lgan query larda bittasi bu. Ushbu query ishlashi uchun sizda quyidagi table bo'lishi kerak 👇

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  user_id VARCHAR(100) NOT NULL,
  status VARCHAR(200) NOT NULL,
  lang VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ushbu query orqali esa siz oxirgi bir soatda, bugun, shu haftada, shu oyda va hamma foydalanuvchilar sonini olishingiz mumkin va bu query ham yuqoridagi table uchun qilingan o'zingizga moslashtirib olasiz degan umiddaman 👇

SELECT 
        SUM(CASE WHEN created_at >= NOW() - INTERVAL '12 hours' THEN 1 ELSE 0 END) AS last_12_hours,
        SUM(CASE WHEN DATE(created_at) = CURRENT_DATE THEN 1 ELSE 0 END) AS today,
        SUM(CASE WHEN created_at >= CURRENT_DATE - INTERVAL '1 week' AND created_at < CURRENT_DATE + INTERVAL '1 day' THEN 1 ELSE 0 END) AS last_week,
        SUM(CASE WHEN created_at >= CURRENT_DATE - INTERVAL '1 month' AND created_at < CURRENT_DATE + INTERVAL '1 day' THEN 1 ELSE 0 END) AS last_month
    FROM users;

Hamma kerakli querylarni pastda sql faylda qoldiraman! 😉
Foydasi tekkan bo'lsa reaksiyani bosib ketamiz 🥹

Please open Telegram to view this post

VIEW IN TELEGRAM

2.66K viewsFirdavs Yorkulov, edited 03:48

PostgreSQL Tutorial

statistics_queries.sql

1.2 KB

Tepadagi postda yozilgan querylar jamlanmasi! DataGrip da ochib bitta run qilib natijani korishingiz mumkin!

2.78K viewsFirdavs Yorkulov, 03:53

PostgreSQL Tutorial

🖥 DataGrip database diagrammalarini ham chiqarib beradi 😉

Please open Telegram to view this post

VIEW IN TELEGRAM

527 viewsFirdavs Yorkulov, 06:03

PostgreSQL Tutorial

query.sql

3.6 KB

🖥 Bugun qilgan loyihamda ishlatilgan querylar jamlanmasi kimgadir kerak bo'lib qolar!
🖥 Koproq JOIN lardan foydalanganman.

Tez orada JOIN lar haqida POST
bo'ladi😊

Please open Telegram to view this post

VIEW IN TELEGRAM

544 viewsFirdavs Yorkulov, 18:29

PostgreSQL Tutorial

SELECT t.title AS test, jsonb_build_object('questions', (SELECT jsonb_agg(jsonb_build_object('question', row_to_json(q), 'variants', (SELECT jsonb_agg(v) FROM variants v WHERE v.question_id = q.id))) FROM questions q INNER JOIN test_questions tq ON q.id = tq.question_id)) AS question
FROM tests t;

Hayotim davomida yozgan eng uzun querylarimdan biri. Nima vazifa bajaradi deb o'ylaysiz? 🤔

444 viewsFirdavs Yorkulov, 07:02

PostgreSQL Tutorial

Bugun sizlar bilan JOIN lar haqida gaplashamiz.
JOIN lar orqali biz bir vaqtning o'zida bir nechta table dan malutmolarni olib kelishimiz mumkin. Ya'ni bizda user_files table bo'lsa va unda user_id , file_id columnlari dagi malumotlar kelsa biz JOIN lar orqali bitta queryning o'zida users jadvalida bizdagi user_id ga mos user ni, files table dan esa file_id ga mos file olib kelib qaytarib berishimiz mumkin. Va nafaqat ikkita balki bir nechta table lardan malumotlarni olib kelishingiz mumkin. Bu o'zingizning proyektingizga va logikangizga bog'liq!

JOIN lar ning bir nechta turi mavjud. Ularga INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, SELF JOIN va CROSS JOIN lar kiradi!

Har bittasini alohida ko'rib chiqamiz keyingi postlarda! 😉

577 viewsFirdavs Yorkulov, 05:30

PostgreSQL Tutorial

INNER JOIN ga misol: 👇

SELECT 
            upt.id, 
            jsonb_build_object('id', u.id, 'login', u.login, 'password', u.password, 'full_name', u.full_name, 
                                'birthdate', u.birthdate, 'role', u.role, 'file', 
                                row_to_json(f.*)) AS users,
            row_to_json(t.*) as test, 
            upt.total_questions, upt.passed_questions, upt.created_at
        FROM user_passed_tests upt 
        INNER JOIN users u ON upt.user_id = u.id 
        INNER JOIN tests t ON upt.test_id = t.id 
        INNER JOIN files f ON f.id = u.file_id
        WHERE upt.user_id = 1;

Please open Telegram to view this post

VIEW IN TELEGRAM

534 viewsFirdavs Yorkulov, 05:33

PostgreSQL Tutorial

CREATE OR REPLACE FUNCTION my_count(table_name VARCHAR)
RETURNS TABLE (
    row_count INTEGER
) AS $$
DECLARE
    count_value INTEGER;
BEGIN
    EXECUTE 'SELECT COUNT(*) FROM ' || table_name INTO count_value;
    row_count := count_value;
    RETURN NEXT;
END;
$$ LANGUAGE plpgsql;


SELECT * from my_count('files');

my_count() nomli function yaratilib unga table name berilganda o'sha table dagi hamma malumotlar sonini keltirib chiqarib beruvchi function.

413 viewsFirdavs Yorkulov, edited 14:59

PostgreSQL Tutorial


CREATE TABLE IF NOT EXISTS phones(
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS phones_history (
    id INT,
    name varchar(50) NOT NULL ,
    status varchar(10) NOT NULL ,
    created_at timestamp default current_timestamp
);

CREATE OR REPLACE FUNCTION pf_insert_historyyy()
        RETURNS TRIGGER
        LANGUAGE plpgsql
AS $$
    BEGIN
        INSERT INTO phones_history(id, name, status) VALUES (NEW.id,NEW.name, 'insert');
        RETURN new;
    end;
    $$;

CREATE OR REPLACE TRIGGER insert_phone_trigger
    AFTER INSERT
    ON phones
    FOR EACH ROW
    EXECUTE PROCEDURE pf_insert_historyyy();


INSERT INTO phones(name) VALUES ('Redmi');

phones table yaratiladi, phones_history table yaratiladi, pf_insert_historyyy function yaratiladi, insert_phone_trigger trigger yaratiladi. phones jadvaliga yangi malumot qo'shilganda trigger tinglab turib phones_historyga qo'shish uchun function (procedure) ni chaqirib yuboradi!

👍1

463 viewsFirdavs Yorkulov, 15:32

PostgreSQL Tutorial

Bugun sizlar bilan JOIN lar haqida gaplashamiz. JOIN lar orqali biz bir vaqtning o'zida bir nechta table dan malutmolarni olib kelishimiz mumkin. Ya'ni bizda user_files table bo'lsa va unda user_id , file_id columnlari dagi malumotlar kelsa biz JOIN lar orqali…

📝 INNER JOIN

SQL so'rovida ikki (yoki undan ko'p) jadvalni birlashtirish uchun ishlatiladi. Bu jadvaldagi ma'lum bir shartning qanoatlantirilgan ma'lumotlarini olish uchun foydalaniladi.

Bu so'rovda, birlashgan jadvaldagi har bir qator, qo'shimcha shart bajarilgan holda, boshqa jadvaldagi mos keladigan qatorlar bilan birlashtiriladi.

Ko'proq tushunchalarni tushuntirish maqsadida, quyidagi misolni ko'ramiz:


SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Bu misolda, orders jadvalidagi customer_id ustuniga qarab customers jadvalidagi customer_id ustuni bilan INNER JOIN amalga oshirilgan. Natijada, orders va customers jadvallarining mos keladigan qatorlari birlashadi.

🖥 INNER JOIN so'rovining sintaksisi quyidagicha:

sql 

SELECT fields
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Bu yerda, fields - olish uchun tanlangan ustunlar ro'yxati, table1 va table2 - birlashtiriladigan jadvallar, va column_name - ularning birlashuvini amalga oshirish uchun foydalaniladigan ustun. Ustun nomlari bir xil bo'lishi zarur.

Please open Telegram to view this post

VIEW IN TELEGRAM

375 viewsFirdavs Yorkulov, 12:44

PostgreSQL Tutorial

📝

LEFT JOIN

SQL so'rovi, ikki (yoki undan ko'p) jadvallarni birlashtirish uchun ishlatiladi, ammo, bu so'rovda chap jadvalning barcha qatorlari (qo'shimcha shart bajarilsa) qaytariladi, va agar o'ng jadvalda mos keladigan qator topilmasa, undagi ustunlar NULL qiymatini olishadi.

🖥 Quyidagi misolni ko'ramiz:


SELECT orders.order_id, customers.customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

Bu misolda, orders jadvalidagi customer_id ustuniga qarab customers jadvalidagi customer_id ustuni bilan LEFT JOIN amalga oshirilgan. Natijada, barcha orders jadvalidagi qatorlar qaytariladi, va agar customers jadvalidagi customer_id ustuni bilan mos keladigan qator topilmasa, customer_name ustuni NULL qiymati bilan to'ldiriladi.

🖥 LEFT JOIN so'rovining sintaksisi quyidagicha:


SELECT fields
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

Please open Telegram to view this post

VIEW IN TELEGRAM

478 viewsFirdavs Yorkulov, 12:48

PostgreSQL Tutorial

📝 LEFT JOIN SQL so'rovi, ikki (yoki undan ko'p) jadvallarni birlashtirish uchun ishlatiladi, ammo, bu so'rovda chap jadvalning barcha qatorlari (qo'shimcha shart bajarilsa) qaytariladi, va agar o'ng jadvalda mos keladigan qator topilmasa, undagi ustunlar…

One to one left join

©️ From Stack Overflow

Please open Telegram to view this post

VIEW IN TELEGRAM

498 viewsFirdavs Yorkulov, 13:01

PostgreSQL Tutorial

📝 INNER JOIN SQL so'rovida ikki (yoki undan ko'p) jadvalni birlashtirish uchun ishlatiladi. Bu jadvaldagi ma'lum bir shartning qanoatlantirilgan ma'lumotlarini olish uchun foydalaniladi. Bu so'rovda, birlashgan jadvaldagi har bir qator, qo'shimcha shart…

SQL INNER JOIN

473 viewsFirdavs Yorkulov, edited 13:03

PostgreSQL Tutorial

📝

RIGHT JOIN

SQL so'rovi, ikki (yoki undan ko'p) jadvallarni birlashtirish uchun ishlatiladi, ammo, bu so'rovda o'ng jadvalning barcha qatorlari (qo'shimcha shart bajarilsa) qaytariladi, va agar chap jadvalda mos keladigan qator topilmagan bo'lsa, undagi ustunlar NULL qiymatini olishadi.

Quyidagi misolni ko'ramiz:


SELECT orders.order_id, customers.customer_name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

🖥 Bu misolda, orders jadvalidagi customer_id ustuniga qarab customers jadvalidagi customer_id ustuni bilan RIGHT JOIN amalga oshirilgan. Natijada, barcha customers jadvalidagi qatorlar qaytariladi, va agar orders jadvalidagi customer_id ustuni bilan mos keladigan qator topilmagan bo'lsa, order_id ustuni NULL qiymati bilan to'ldiriladi.

🖥 RIGHT JOIN so'rovining sintaksisi quyidagicha:


SELECT fields
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Please open Telegram to view this post

VIEW IN TELEGRAM

501 viewsFirdavs Yorkulov, edited 13:10

PostgreSQL Tutorial

📝

FULL OUTER JOIN

SQL so'rovi, ikki (yoki undan ko'p) jadvallarni birlashtirish uchun ishlatiladi. Bu so'rovda, barcha qatorlar qaytariladi va agar bir jadvalda mos keladigan qator bo'lmasa, undagi ustunlar NULL qiymatini olishadi. Boshqa so'z bilan aytganda, bu so'rov LEFT JOIN va RIGHT JOIN ni birlashtiradi.

Quyidagi misolni ko'ramiz:

SELECT orders.order_id, customers.customer_name
FROM orders
FULL OUTER JOIN customers ON orders.customer_id = customers.customer_id;

Bu misolda, orders jadvalidagi customer_id ustuniga qarab customers jadvalidagi customer_id ustuni bilan FULL OUTER JOIN amalga oshirilgan. Natijada, barcha orders va customers jadvallaridagi qatorlar qaytariladi, va agar qo'shimcha jadvalarda customer_id ustuni bilan mos keladigan qator topilmagan bo'lsa, customer_name ustuni NULL qiymati bilan to'ldiriladi.

🖥

FULL OUTER JOIN so'rovining sintaksisi quyidagicha:

sql 
SELECT fields
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

🖥 Bu yerda, fields - olish uchun tanlangan ustunlar ro'yxati, table1 va table2 - birlashtiriladigan jadvallar, va column_name - ularning birlashuvini amalga oshirish uchun foydalaniladigan ustun. Ustun nomlari bir xil bo'lishi zarur.

Please open Telegram to view this post

VIEW IN TELEGRAM

👍1

620 viewsFirdavs Yorkulov, 13:32

PostgreSQL Tutorial

📝

SELF JOIN

SQL so'rovi, bir jadvallarning bir necha qatorlarini o'z ichiga birlashtirish uchun ishlatiladi. Bunday so'rovda, bir jadvalning har bir qatori boshqa qatorlar bilan o'z ichiga birlashtiriladi, o'zini boshqa qatorlariga o'xshash bo'lgan ustunlari boyicha.

Quyidagi misolni ko'ramiz. Agar bizda employees deb nomlangan jadval bo'lsa:

SELECT e1.employee_id, e1.employee_name, e2.supervisor_id, e2.supervisor_name
FROM employees e1
JOIN employees e2 ON e1.supervisor_id = e2.employee_id;

Bu so'rovda employees jadvali o'zini supervisor_id ustuni employee_id ustuni bilan birlashtiradi. Natijada, har bir xodimning ma'mul xodimi bor. e1 va e2 tabelalariga jadvallar qo'llanilgan qatlamalar hisoblanadi.

SELF JOIN so'rovining sintaksisi quyidagicha:

SELECT fields
FROM table1 alias1
JOIN table1 alias2 ON alias1.column_name = alias2.column_name;

Bu yerda, fields - olish uchun tanlangan ustunlar ro'yxati, table1 - o'zini o'ziga birlashtirish kerak bo'lgan jadval, va alias1 va alias2 - jadvalni o'z ichiga birlashtirish uchun ishlatiladigan aliaslar. Ustun nomlari bir xil bo'lishi zarur.

Please open Telegram to view this post

VIEW IN TELEGRAM

👍1

818 viewsFirdavs Yorkulov, 13:32

PostgreSQL Tutorial

CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY ,
    name VARCHAR(50) UNIQUE NOT NULL ,
    price INT NOT NULL
);

CREATE TABLE IF NOT EXISTS products_history(
    id INT ,
    name VARCHAR(50) NOT NULL ,
    price INT NOT NULL ,
    status VARCHAR(15) NOT NULL ,
    created_at TIMESTAMP DEFAULT current_timestamp
);


CREATE OR REPLACE FUNCTION update_products_history()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO products_history (id, name, price, status)
    VALUES (
        coalesce(NEW.id, old.id),
        COALESCE(NEW.name, OLD.name),
        COALESCE(NEW.price, OLD.price),
        CASE
            WHEN TG_OP = 'INSERT' THEN 'INSERT'
            WHEN TG_OP = 'UPDATE' THEN 'UPDATE'
            WHEN TG_OP = 'DELETE' THEN 'DELETE'
        END

    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER products_history_trigger
AFTER INSERT OR UPDATE OR DELETE
ON products
FOR EACH ROW
EXECUTE FUNCTION update_products_history();



create or replace procedure insert_product(
   name varchar,
   price int
)
language plpgsql
as $$
begin

    INSERT INTO products(name, price) VALUES (name, price*100);

    commit;
end;$$;


call insert_product('Banana', 5);



INSERT INTO products(name, price) VALUES ('Phone23455', 5000);

DELETE FROM products WHERE id=3;

👍1

1.16K viewsFirdavs Yorkulov, 23:41

PostgreSQL Tutorial

CREATE TABLE IF NOT EXISTS products ( id SERIAL PRIMARY KEY , name VARCHAR(50) UNIQUE NOT NULL , price INT NOT NULL ); CREATE TABLE IF NOT EXISTS products_history( id INT , name VARCHAR(50) NOT NULL , price INT NOT NULL , status…

1. Products nomli table yaratiladi.
2. Products_history nomli table yaratiladi,
3. Update Product history nomli FUNCTION yaratiladi.
4. products_history_trigger nomli Trigger yaratiladi.
5. insert_procedure nomli protsedura yaratiladi.

Products table ga malumot qo'shilganda uning har bitta rowini tinglab turgan trigger update_products_history nomli functionni chiqirib yuboryapti. update_products_history function esa products_history table ga coalesce yordamida qo'shyapti. (colasce - update ga o'xshaydi. Unga agar yangi malumot berilsa o'sha malumotni yozyapti agar yangi malumot berilmasa eskini yozib qo'yayapti). CASE ni ichida esa actionga qarab statusni aniqlab olib yozib qo'yayapti.

Bu yerda protsedura ham bor u esa call qilinganda unga name va price berilyapti u esa products table ham name ni o'z holicha, price ni esa dollardan cent ga o'zgartirib yozib qo'yayapti!

Keyingi postlarimda TRIGGER, FUNCTION, INDEX, PROCEDURE, TRANSACTION lar haqida alohida to'xtalib example lar bilan to'liq malumot beraman........

👍8⚡1

1.37K viewsFirdavs Yorkulov, edited 23:47

PostgreSQL Tutorial

 sql

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
    REFERENCES customers(customer_id)
    ON DELETE SET NULL
);

⚡1

1.21K viewsFirdavs Yorkulov, 09:38

PostgreSQL Tutorial

sql CREATE TABLE customers( customer_id INT GENERATED ALWAYS AS IDENTITY, customer_name VARCHAR(255) NOT NULL, PRIMARY KEY(customer_id) ); CREATE TABLE contacts( contact_id INT GENERATED ALWAYS AS IDENTITY, customer_id INT, contact_name…

Foreign Key ga misol!

1.27K viewsFirdavs Yorkulov, 09:38

PostgreSQL Tutorial

Umidjon Jabborov

Trigger haqida ma'lumot bera olasizmi qandya hoaltlarda ishlatiladi

PostgreSQL-da triggerlar (triggers) ma'lum bir hodisalar sodir bo'lganda avtomatik tarzda ishga tushadigan maxsus protseduralar (functions) hisoblanadi. Odatda triggerlar jadvallarda (tables) ma'lumotlar qo'shilganda, yangilanganda yoki o'chirilganda (INSERT, UPDATE, DELETE) ishga tushadi. Triggerlar yordamida ma'lumotlar yaxlitligini ta'minlash, avtomatlashtirilgan tekshiruvlar yoki o'zgarishlar amalga oshirish mumkin.

Trigger yaratish
Trigger yaratish uchun CREATE TRIGGER buyrug'i ishlatiladi. Bu buyruq trigger nomini, qachon va qaysi jadvalda ishga tushishini aniqlab beradi.

CREATE TRIGGER trigger_name
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE | TRUNCATE }
ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name();

BEFORE yoki AFTER: Trigger operatsiyadan oldin (BEFORE) yoki keyin (AFTER) ishga tushishi kerakligini aniqlaydi.
INSERT, UPDATE, DELETE, TRUNCATE: Qaysi hodisa triggerni ishga tushirishini aniqlaydi.
table_name: Trigger bog'langan jadval nomi.
function_name: Trigger ishga tushiradigan funksiyaning nomi.

Misol
Keling, quyidagi misol orqali triggerni ko'rib chiqamiz. Biz yangi yozuv qo'shilganda avtomatik tarzda jadvaldagi boshqa ustunni yangilaydigan trigger yaratamiz.

1. Trigger funksiyasini yaratish:

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.modified_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Bu funksiya modified_at ustunini hozirgi vaqt (NOW()) bilan yangilaydi.

2. Triggerni yaratish:

CREATE TRIGGER update_modified_at
BEFORE INSERT OR UPDATE ON your_table_name
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

Bu trigger har safar your_table_name jadvaliga yangi yozuv qo'shilganda yoki mavjud yozuv yangilanganda modified_at ustunini hozirgi vaqt bilan yangilaydi.

Triggerlarni o'chirish
Agar triggerni o'chirmoqchi bo'lsangiz, quyidagi buyruqni ishlatishingiz mumkin:

DROP TRIGGER trigger_name ON table_name;

Qo'shimcha ma'lumot
Triggerlar juda kuchli vosita bo'lib, ularni noto'g'ri ishlatish jadvallar bilan bog'liq operatsiyalarni sekinlashtirishi yoki murakkabligi tufayli qiyinchiliklar keltirib chiqarishi mumkin. Shu sababli, triggerlarni ishlab chiqishda ehtiyotkorlik bilan yondashish tavsiya etiladi.

👍3

885 viewsFirdavs Yorkulov, edited 14:31

About

Blog

Apps

Platform