سوالات مصاحبه SQL و دیتابیس سطح میدل (50 سوال)

Question 1

چرا `users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.status = 'paid'` userهای بدون سفارش را برنمی‌گرداند؟

Accepted Answer

شرط `WHERE` روی `NULL`ها `TRUE` نمی‌شود و عملاً join را به `INNER` تبدیل می‌کند

Answer

`LEFT JOIN` با `WHERE` پشتیبانی نمی‌شود

Answer

planner همیشه `LEFT JOIN` را به `RIGHT JOIN` تبدیل می‌کند

Answer

چون `status` index ندارد

Question 2

کوئری `SELECT u.id, SUM(o.total), COUNT(p.id) FROM users u JOIN orders o ON ... JOIN payments p ON p.user_id = u.id GROUP BY u.id` مجموع سفارش‌ها را بیش از حد نشان می‌دهد. علت چیست؟

Accepted Answer

join دو رابطه one-to-many مستقل باعث ضرب سطرها (fan-out) می‌شود

Answer

`SUM` مقادیر `NULL` را دو بار می‌شمارد

Answer

`GROUP BY` باید روی `o.id` باشد

Answer

`COUNT` و `SUM` نمی‌توانند در یک کوئری باشند

Question 3

correlated subquery چیست؟

Accepted Answer

زیرکوئری‌ای که به ستون‌های کوئری بیرونی ارجاع می‌دهد و منطقاً برای هر سطر ارزیابی می‌شود

Answer

زیرکوئری‌ای که فقط یک بار اجرا می‌شود

Answer

زیرکوئری‌ای که داخل `FROM` قرار دارد

Answer

زیرکوئری‌ای که از `UNION` استفاده می‌کند

Question 4

در PostgreSQL مدرن، `WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)` در مقایسه با `WHERE u.id IN (SELECT user_id FROM orders)` چگونه است؟

Accepted Answer

معمولاً هر دو به semi-join تبدیل می‌شوند و عملکرد مشابه دارند

Answer

`IN` همیشه کل زیرکوئری را برای هر سطر دوباره اجرا می‌کند

Answer

`EXISTS` همیشه سطرهای تکراری برمی‌گرداند

Answer

`EXISTS` نمی‌تواند از index استفاده کند

Question 5

برای گرفتن جدیدترین سفارش هر user کدام روش درست است؟

Accepted Answer

`ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)` و فیلتر `rn = 1`

Answer

`SELECT * FROM orders GROUP BY user_id`

Answer

`SELECT * FROM orders ORDER BY created_at DESC LIMIT 1`

Answer

`SELECT user_id, MAX(created_at), total FROM orders GROUP BY user_id`

Question 6

برای امتیازهای `100, 90, 90, 80`، خروجی `RANK()` و `DENSE_RANK()` (به ترتیب نزولی) چیست؟

Accepted Answer

`RANK`: `1,2,2,4` و `DENSE_RANK`: `1,2,2,3`

Answer

`RANK`: `1,2,2,3` و `DENSE_RANK`: `1,2,2,4`

Answer

هر دو `1,2,3,4`

Answer

هر دو `1,2,2,3`

Question 7

`revenue - LAG(revenue) OVER (ORDER BY month)` چه چیزی محاسبه می‌کند؟

Accepted Answer

تغییر درآمد نسبت به ماه قبل (برای ماه اول `NULL`)

Answer

تغییر درآمد نسبت به ماه بعد

Answer

مجموع تجمعی درآمد

Answer

اختلاف درآمد با میانگین کل

Question 8

کدام عبارت یک running total از `amount` به ترتیب `id` می‌سازد؟

Accepted Answer

`SUM(amount) OVER (ORDER BY id)`

Answer

`SUM(amount) OVER ()`

Answer

`SUM(amount) GROUP BY id`

Answer

`SUM(amount) OVER (PARTITION BY id)`

Question 9

چرا `SUM(amount) OVER (ORDER BY day)` برای روزهای تکراری running total یکسانی نشان می‌دهد؟

Accepted Answer

frame پیش‌فرض `RANGE` است و همه peerهای هم‌مقدار را شامل می‌شود

Answer

`SUM` با تاریخ‌ها درست کار نمی‌کند

Answer

window functionها مقادیر تکراری را حذف می‌کنند

Answer

باید `GROUP BY day` اضافه شود

Question 10

مزیت اصلی CTE (`WITH ... AS (...)`) چیست؟

Accepted Answer

خوانایی بیشتر با نام‌گذاری مراحل میانی کوئری

Answer

همیشه سریع‌تر از زیرکوئری است

Answer

نتیجه را به‌صورت دائمی ذخیره می‌کند

Answer

نیاز به index را حذف می‌کند

Question 11

برای گرفتن همه زیرمجموعه‌های یک category در درختی با ستون `parent_id`، از چه استفاده می‌کنید؟

Accepted Answer

`WITH RECURSIVE` با بخش anchor و بخش recursive که با `UNION ALL` ترکیب می‌شوند

Answer

یک self join ساده

Answer

`GROUP BY parent_id`

Answer

window function `LAG`

Question 12

اگر داده سلسله‌مراتبی دور (cycle) داشته باشد، یک recursive CTE با `UNION ALL` چه می‌کند؟

Accepted Answer

بی‌نهایت ادامه می‌دهد مگر اینکه cycle را تشخیص دهید یا عمق را محدود کنید

Answer

خودکار cycle را تشخیص داده و متوقف می‌شود

Answer

بلافاصله خطای syntax می‌دهد

Answer

فقط سطح اول را برمی‌گرداند

Question 13

تفاوت اصلی materialized view با view معمولی چیست؟

Accepted Answer

نتیجه را فیزیکی ذخیره می‌کند و باید با `REFRESH` به‌روز شود

Answer

همیشه با جدول پایه هم‌زمان است

Answer

قابل index گذاری نیست

Answer

فقط در حافظه نگه داشته می‌شود

Question 14

جدول `order_items(order_id, product_id, quantity, product_name)` با primary key `(order_id, product_id)` کدام فرم نرمال را نقض می‌کند؟

Accepted Answer

2NF، چون `product_name` فقط به بخشی از key وابسته است

Answer

1NF، چون key ترکیبی است

Answer

3NF، چون `quantity` عدد است

Answer

هیچ‌کدام؛ جدول کاملاً نرمال است

Question 15

جدول `employees(id, dept_id, dept_name)` با key برابر `id` کدام فرم نرمال را نقض می‌کند؟

Accepted Answer

3NF، به دلیل وابستگی transitive `id → dept_id → dept_name`

Answer

1NF

Answer

2NF

Answer

هیچ‌کدام

Question 16

چه زمانی denormalization (مثلاً ذخیره `comments_count` در جدول `posts`) منطقی است؟

Accepted Answer

وقتی خواندن بسیار پرتکرار و محاسبه گران است و می‌توان هم‌زمانی را مدیریت کرد

Answer

همیشه، چون join همیشه کند است

Answer

وقتی داده زیاد تغییر می‌کند و consistency حیاتی است

Answer

فقط وقتی دیتابیس foreign key ندارد

Question 17

isolation level پیش‌فرض PostgreSQL کدام است؟

Accepted Answer

Read Committed

Answer

Repeatable Read

Answer

Serializable

Answer

Read Uncommitted

Question 18

dirty read چیست؟

Accepted Answer

خواندن تغییرات commit‌نشده transaction دیگر

Answer

خواندن دو مقدار متفاوت از یک سطر در یک transaction

Answer

ظاهر شدن سطرهای جدید در اجرای دوم یک کوئری

Answer

خواندن داده از یک index خراب

Question 19

در Read Committed، transaction ابتدا `SELECT balance FROM accounts WHERE id = 1` را می‌خواند و `100` می‌گیرد؛ transaction دیگر آن را به `50` تغییر و commit می‌کند. اجرای دوباره همان SELECT چه می‌دهد؟

Accepted Answer

`50` (non-repeatable read)

Answer

`100`، چون snapshot ثابت است

Answer

خطای serialization

Answer

SELECT تا پایان transaction دیگر block می‌شود

Question 20

phantom read چیست؟

Accepted Answer

اجرای دوباره یک کوئری با شرط، سطرهای جدید یا حذف‌شده را نشان دهد

Answer

خواندن داده‌ای که rollback شده است

Answer

از دست رفتن یک update به‌خاطر update هم‌زمان

Answer

خواندن سطری که هنوز در WAL است

Question 21

دو درخواست هم‌زمان هر کدام `stock` را می‌خوانند (`10`)، در اپلیکیشن یکی کم می‌کنند و `UPDATE products SET stock = 9` می‌نویسند. نتیجه چیست؟

Accepted Answer

`9` به‌جای `8` (lost update)

Answer

`8`، چون دیتابیس خودکار ادغام می‌کند

Answer

deadlock و rollback یکی از آن‌ها

Answer

خطای unique constraint

Question 22

`SELECT * FROM accounts WHERE id = 1 FOR UPDATE` داخل یک transaction چه می‌کند؟

Accepted Answer

روی سطر قفل ردیفی می‌گذارد تا transactionهای دیگر نتوانند آن را تا پایان transaction تغییر دهند یا قفل کنند

Answer

کل جدول را برای خواندن و نوشتن قفل می‌کند

Answer

جلوی SELECTهای معمولی دیگران را هم می‌گیرد

Answer

سطر را بلافاصله update می‌کند

Question 23

برای ساخت یک job queue روی PostgreSQL که چند worker هم‌زمان jobهای متفاوت بردارند، کدام مناسب است؟

Accepted Answer

`SELECT ... FOR UPDATE SKIP LOCKED LIMIT 1`

Answer

`SELECT ... FOR UPDATE NOWAIT` بدون retry

Answer

`LOCK TABLE jobs` در هر worker

Answer

`SELECT ... LIMIT 1` بدون قفل

Question 24

transaction A سطر 1 و سپس سطر 2 را update می‌کند و transaction B هم‌زمان سطر 2 و سپس سطر 1 را. PostgreSQL چه می‌کند؟

Accepted Answer

deadlock را تشخیص می‌دهد و یکی از transactionها را با خطا abort می‌کند

Answer

هر دو برای همیشه منتظر می‌مانند

Answer

هر دو را خودکار پشت سر هم اجرا می‌کند

Answer

update دوم هر دو را بی‌صدا نادیده می‌گیرد

Question 25

optimistic locking با ستون `version` معمولاً چطور پیاده‌سازی می‌شود؟

Accepted Answer

`UPDATE ... SET version = version + 1 WHERE id = ? AND version = ?` و بررسی تعداد سطرهای تحت تأثیر

Answer

`SELECT ... FOR UPDATE` قبل از هر خواندن

Answer

قفل جدول در ابتدای هر request

Answer

استفاده از isolation level Read Uncommitted

Question 26

با B-tree index روی `(last_name, first_name)`، کدام کوئری نمی‌تواند به‌طور مؤثر از آن استفاده کند؟

Accepted Answer

`WHERE first_name = 'Sara'`

Answer

`WHERE last_name = 'Ahmadi'`

Answer

`WHERE last_name = 'Ahmadi' AND first_name = 'Sara'`

Answer

`WHERE last_name = 'Ahmadi' ORDER BY first_name`

Question 27

برای کوئری `WHERE status = 'active' AND created_at > now() - interval '7 days'`، کدام index ترکیبی بهتر است؟

Accepted Answer

`(status, created_at)`: ستون برابری اول، ستون بازه دوم

Answer

`(created_at, status)`: ستون بازه اول

Answer

دو index جدا همیشه بهتر از index ترکیبی است

Answer

ترتیب ستون‌ها در index ترکیبی اهمیتی ندارد

Question 28

covering index (مثلاً `CREATE INDEX ON orders(user_id) INCLUDE (total)`) چه مزیتی دارد؟

Accepted Answer

امکان index-only scan بدون مراجعه به جدول اصلی برای ستون‌های لازم

Answer

تضمین یکتایی `total`

Answer

امکان جستجو با `WHERE total > 100` با همان کارایی ستون کلیدی

Answer

کاهش هزینه نوشتن در جدول

Question 29

partial index مثل `CREATE INDEX ON orders(created_at) WHERE status = 'pending'` چه زمانی مفید است؟

Accepted Answer

وقتی کوئری‌ها اغلب فقط زیرمجموعه کوچکی از سطرها (مثلاً pending) را هدف می‌گیرند

Answer

وقتی بخواهید index فقط بخشی از ستون‌ها را پوشش دهد

Answer

برای همه کوئری‌های روی `orders` بدون توجه به شرط

Answer

فقط برای جدول‌های partitioned

Question 30

با index روی `email`، چرا `WHERE LOWER(email) = 'a@b.com'` از index استفاده نمی‌کند؟

Accepted Answer

index روی مقدار خام ساخته شده؛ برای این کوئری expression index روی `LOWER(email)` لازم است

Answer

`LOWER` در `WHERE` مجاز نیست

Answer

B-tree با رشته کار نمی‌کند

Answer

باید به‌جای آن `LIKE` نوشت

Question 31

چرا planner ممکن است index روی ستون `is_active` را نادیده بگیرد وقتی ۹۵٪ سطرها `true` هستند؟

Accepted Answer

selectivity پایین است و seq scan برای خواندن بیشتر جدول ارزان‌تر است

Answer

index روی boolean پشتیبانی نمی‌شود

Answer

index خراب شده و باید `REINDEX` شود

Answer

planner هرگز برای `WHERE` از index استفاده نمی‌کند

Question 32

تفاوت `EXPLAIN` و `EXPLAIN ANALYZE` چیست؟

Accepted Answer

`EXPLAIN` فقط plan تخمینی را نشان می‌دهد؛ `EXPLAIN ANALYZE` کوئری را واقعاً اجرا و زمان و تعداد سطر واقعی را گزارش می‌کند

Answer

`EXPLAIN ANALYZE` فقط آمار جدول را به‌روز می‌کند

Answer

`EXPLAIN` کوئری را اجرا می‌کند ولی نتیجه را نشان نمی‌دهد

Answer

تفاوتی ندارند؛ فقط فرمت خروجی متفاوت است

Question 33

در `EXPLAIN ANALYZE` یک گره `rows=10` تخمینی ولی `rows=500000` واقعی دارد. محتمل‌ترین اقدام اول چیست؟

Accepted Answer

اجرای `ANALYZE` روی جدول برای به‌روز کردن آمار

Answer

حذف همه indexها

Answer

افزایش `max_connections`

Answer

تبدیل جدول به unlogged

Question 34

Bitmap Heap Scan در plan PostgreSQL چه زمانی معمولاً انتخاب می‌شود؟

Accepted Answer

وقتی تعداد سطرها برای index scan زیاد ولی برای seq scan کم است، یا چند index با هم ترکیب می‌شوند

Answer

فقط برای جدول‌هایی با کمتر از ۱۰۰ سطر

Answer

وقتی هیچ indexی وجود ندارد

Answer

فقط برای ستون‌های boolean

Question 35

در یک ORM، حلقه روی ۵۰ پست و دسترسی به `post.author` در هر تکرار ۵۱ کوئری تولید می‌کند. راه حل استاندارد چیست؟

Accepted Answer

eager loading (مثل `include`/`joinedload`/`with`) برای بارگذاری نویسنده‌ها در یک یا دو کوئری

Answer

افزایش اندازه connection pool

Answer

کش کردن هر پست جداگانه در Redis

Answer

اضافه کردن index روی `posts.title`

Question 36

کدام یک از مشکلات رایج ORMها در عملکرد است؟

Accepted Answer

بارگذاری همه ستون‌ها و سطرها در حافظه وقتی فقط یک شمارش یا چند فیلد لازم است

Answer

استفاده خودکار از parameterized query

Answer

پشتیبانی از transaction

Answer

تولید migration از روی مدل‌ها

Question 37

چرا باز کردن یک connection جدید PostgreSQL برای هر request HTTP ایده بدی است؟

Accepted Answer

هر connection یک process جدا با هزینه راه‌اندازی و حافظه است و تعدادشان محدود است

Answer

PostgreSQL فقط یک connection هم‌زمان را می‌پذیرد

Answer

connectionهای جدید داده‌های commit‌نشده را می‌بینند

Answer

این کار باعث SQL injection می‌شود

Question 38

در PgBouncer با `pool_mode = transaction`، کدام قابلیت session-level به‌طور سنتی مشکل‌ساز است؟

Accepted Answer

`SET` در سطح session، advisory lockهای session و `LISTEN`

Answer

transactionهای چند statement با `BEGIN`/`COMMIT`

Answer

کوئری‌های `SELECT` ساده

Answer

foreign key constraintها

Question 39

سرویسی ۲۰ instance دارد و هر کدام pool با اندازه ۵۰. دیتابیس `max_connections = 200` دارد. مشکل چیست؟

Accepted Answer

تا ۱۰۰۰ connection درخواست می‌شود و از سقف دیتابیس عبور می‌کند

Answer

هیچ مشکلی ندارد چون poolها خودکار هماهنگ می‌شوند

Answer

poolهای کوچک‌تر همیشه کندترند

Answer

`max_connections` فقط روی replicaها اعمال می‌شود

Question 40

چرا `ORDER BY id LIMIT 20 OFFSET 1000000` کند است؟

Accepted Answer

دیتابیس باید یک میلیون سطر را بخواند و دور بریزد تا به ۲۰ سطر برسد

Answer

`OFFSET` index را غیرفعال می‌کند و کل جدول sort می‌شود

Answer

`LIMIT` بیش از ۱۰ پشتیبانی نمی‌شود

Answer

عدد بزرگ OFFSET باعث overflow می‌شود

Question 41

در keyset pagination بر اساس `created_at`، چرا باید `id` را هم به‌عنوان tiebreaker اضافه کرد؟

Accepted Answer

چون چند سطر می‌توانند `created_at` یکسان داشته باشند و بدون آن سطرها تکرار یا جا می‌افتند

Answer

چون `created_at` قابل index نیست

Answer

برای اینکه `LIMIT` کار کند

Answer

چون مقایسه timestampها در SQL مجاز نیست

Question 42

با soft delete (ستون `deleted_at`)، کدام مشکل رایج پیش می‌آید؟

Accepted Answer

unique constraint روی `email` مانع ثبت‌نام دوباره با ایمیل یک کاربر حذف‌شده می‌شود

Answer

سطرها واقعاً از دیسک حذف می‌شوند

Answer

foreign keyها خودکار غیرفعال می‌شوند

Answer

کوئری‌ها دیگر نیاز به `WHERE` ندارند

Question 43

یک مزیت و یک عیب UUID v4 به‌عنوان primary key در مقایسه با auto-increment چیست؟

Accepted Answer

قابل تولید در client و غیرقابل حدس است، ولی تصادفی بودنش باعث درج پراکنده در B-tree و index بزرگ‌تر می‌شود

Answer

کوچک‌تر از `bigint` است، ولی قابل index نیست

Answer

ترتیب زمانی دارد، ولی ممکن است تکراری شود

Answer

سریع‌تر درج می‌شود، ولی در توزیع‌شده کار نمی‌کند

Question 44

در PostgreSQL 11 به بعد، `ALTER TABLE big ADD COLUMN flag boolean NOT NULL DEFAULT false` روی جدول بزرگ چگونه است؟

Accepted Answer

سریع است چون default ثابت در metadata ذخیره می‌شود و جدول بازنویسی نمی‌شود

Answer

کل جدول را بازنویسی می‌کند و ساعت‌ها قفل نگه می‌دارد

Answer

همیشه خطا می‌دهد چون `NOT NULL` با `DEFAULT` ناسازگار است

Answer

سطرهای موجود `NULL` می‌گیرند

Question 45

چرا در production از `CREATE INDEX CONCURRENTLY` استفاده می‌شود؟

Accepted Answer

تا index بدون block کردن نوشتن‌ها روی جدول ساخته شود

Answer

چون سریع‌تر از `CREATE INDEX` معمولی است

Answer

تا index روی همه replicaها هم‌زمان ساخته شود

Answer

چون داخل transaction اجرا می‌شود و قابل rollback است

Question 46

کاربر پروفایلش را update می‌کند و بلافاصله صفحه از read replica خوانده می‌شود و مقدار قدیمی نمایش داده می‌شود. علت چیست؟

Accepted Answer

replication lag در replication غیرهمگام

Answer

transaction روی primary rollback شده است

Answer

replica فقط داده‌های قدیمی‌تر از یک روز را دارد

Answer

کش مرورگر کاربر

Question 47

table partitioning بر اساس تاریخ (مثلاً یک partition در ماه) برای جدول `events` چه مزیتی دارد؟

Accepted Answer

partition pruning در کوئری‌های بازه زمانی و حذف سریع داده قدیمی با `DROP`/`DETACH`

Answer

توزیع خودکار داده روی چند سرور

Answer

حذف نیاز به index

Answer

تضمین یکتایی سراسری روی هر ستونی

Question 48

چرا PostgreSQL به `VACUUM` نیاز دارد؟

Accepted Answer

چون `UPDATE` و `DELETE` نسخه‌های قدیمی سطر (dead tuple) باقی می‌گذارند که باید بازیافت شوند

Answer

برای فشرده‌سازی فایل WAL

Answer

برای ساخت دوباره همه indexها

Answer

برای پاک کردن connectionهای بیکار

Question 49

چرا `SELECT COUNT(*) FROM big_table` در PostgreSQL روی جدول خیلی بزرگ کند است؟

Accepted Answer

به‌خاطر MVCC تعداد دقیق برای هر snapshot متفاوت است و باید سطرها (یا index) اسکن شوند

Answer

چون `COUNT(*)` همه ستون‌ها را از دیسک می‌خواند و decode می‌کند

Answer

چون PostgreSQL تعداد سطرها را هرگز تخمین نمی‌زند

Answer

چون `COUNT(*)` جدول را قفل می‌کند

Question 50

برای اضافه کردن foreign key به جدول بزرگ در production بدون قفل طولانی، روش رایج چیست؟

Accepted Answer

ابتدا با `NOT VALID` اضافه و سپس جداگانه `VALIDATE CONSTRAINT` اجرا شود

Answer

جدول را کپی، constraint را اضافه و سپس جایگزین کنید

Answer

constraint را فقط در کد اپلیکیشن چک کنید

Answer

`LOCK TABLE` و اضافه کردن مستقیم constraint در ساعت کم‌ترافیک