تمرین SQL: شش تمرین عالی برای تقویت مهارت های شما

SQL یک زبان پرس و جو ساختار یافته است که برای بازیابی و دستکاری داده های ذخیره شده در پایگاه های داده رابطه ای استفاده می شود. کسب مهارت کار با SQL یک پیش نیاز مهم برای بسیاری از مشاغل فناوری است و نیاز به کمی تمرین دارد. (منبع مطلب)

برای تکمیل منابع آموزشی SQL موجود در وب (مثل PGExercises،LeetCode ،HackerRank ،Mode )، فهرستی از تمرین را گردآوری کرده ام که می توانید آن ها را انجام بدهید یا با یک نمونه PostgreSQL به جواب برسید.

حتما دانلود کنید: آموزش پایگاه داده از صفر تا صد با 23 درس

این تمرینات مفاهیم انتقادی زیر را پوشش می دهند:

• بازیابی اولیه (SELECT، FROM)
• ساخت و ایجاد نام مستعار ( WITH-AS -GENERATE_SERIES )
• فیلتر کردن (Distinct- Where – Having- And- Or، In، Not In)
• تجمع( GROUP BY باCOUNT،SUM ،AVERAGE )
• پیوند و ترکیب (INNER JOIN – LEFT JOIN – FULL OUTER JOIN در یک یا چند معادله، CROSS JOIN- UNION وUNION ALL )
• عبارات شرطی (CASE – WHEN – THEN – ELSE – END)
• توابع پنجره ای (RANK – DENSE_RANK- ROW_NUMBER – SUM با PARTITION BY – ORDER BY)
• قالب بندی (LIMIT-ORDER BY- تبدیل عدد به صورت صحیح ، اعشاری یا تاریخ، CONCAT، COALESCE)
• عملیات حسابی و مقایسه ای (+،- ، *، /، //، ^،< ، >، =،!= )
• عملیات تاریخ (EXTRACT، ماه / روز / سال)

خودتان امتحان کنید

شما می ‌توانید این موارد را خودتان با دانلود PostgreSQL و PSequel امتحان کنید و سپس کوئری های نشان داده شده در کادر های خاکستری در متن زیر را اجرا کنید. اگر از کامپیوتر شخصی استفاده می کنید، باید بدانید که PSequel فقط در Mac در دسترس است.

این کوئری ها را خودتان با استفاده از PSequel و جداول ورودی ارائه شده در زیر امتحان کنید.

اولین بلوک متن در هر کوئری که در زیر نشان داده شده است، جدول ورودی را مشخص می کند و این فرمت را دنبال می کند:

WITH input_table (column_1, column_2)
AS (VALUES
(1, ‘A’), (2, ‘B’))

شما می توانید با استفاده از PSequel (نشان داده شده در بالا) جدول ورودی را به صورت کوئری در بیاورید و با استفاده از این الگو به راحتی جداول جدیدی بسازید.

منابع آموزشی SQL مبتنی بر وب از چند نظر ناقص هستند. به طور مثال LeetCode از استفاده از توابع پنجره پشتیبانی نمی کند. علاوه بر این، اجرای پرس ‌و ‌جو های SQL در مرورگر شما می ‌تواند بسیار کند باشد، چرا که مجموعه داده ‌ها بزرگ هستند و سرعت بازیابی اغلب برای کاربران غیر پریمیوم محدود می ‌شود. از سوی دیگر اجرای محلی یک کوئری، آنی است و امکان تکرار سریع از طریق باگ های سینتکسی و نحوی و جداول میانی را فراهم می کند.

در کنار این مطلب حتما دانلود کنید: دانلود رایگان آموزش کامل sql (فیلم+جزوه pdf)

سوالاتی که در زیر به آن ها پاسخ داده شده، شامل نمونه راه حل های تایید شده برای کار در PostgreSQL است. یادتان باشد که معمولا برای رسیدن به پاسخ درست برای یک مشکل SQL بیش از یک راه وجود دارد. ترجیح من استفاده از عبارات جدول رایج (CTEs) به جای کوئری های فرعی تو در تو است.CTE ها امکان نمایش تصویر خطی تر توالی آماده سازی داده ها را فراهم می کنند. با این حال، هر دو رویکرد می توانند راه حل های یکسانی را ارائه بدهند. از طرفی من مایلم از قرارداد نوشتن عملگر ها SQL با حروف بزرگ (مثلSELECT،FROM ، WHEREو غیره) و نام ستون ها با حروف کوچک (مثل user_id، تاریخ، و غیره) و نام مستعار جدول ساده (مثل t1، t2 و غیره) پیروی کنم.

قطعه کد نشان داده شده در زیر را می توان در PSequel همان طور که هست؛ اجرا کرد تا نتیجه نمایش داده شده را به دست آورد. به ویژگی Postgres توجه کنید: کسر ها باید در 1.0 ضرب شوند تا از فرمت عدد صحیح به فرمت اعشاری تبدیل شوند . این مورد در سایر پیاده سازی های SQL مورد نیاز نیست و در مصاحبه ها لازم نمی شود.

تمرینات

1-نرخ لغو

به کمک جدول شناسه‌ های کاربر، اقدامات و تاریخ ‌های زیر، یک کوئری بنویسید تا نرخ انتشار و لغو برای هر کاربر را برگرداند.

WITH users (user_id, action, date)
AS (VALUES
(1,’start’, CAST(’01-01-20′ AS date)),
(1,’cancel’, CAST(’01-02-20′ AS date)),
(2,’start’, CAST(’01-03-20′ AS date)),
(2,’publish’, CAST(’01-04-20′ AS date)),
(3,’start’, CAST(’01-05-20′ AS date)),
(3,’cancel’, CAST(’01-06-20′ AS date)),
(1,’start’, CAST(’01-07-20′ AS date)),
(1,’publish’, CAST(’01-08-20′ AS date))),
— retrieve count of starts, cancels, and publishes for each usert1 AS (
SELECT
user_id,
SUM(CASE WHEN action = ‘start’ THEN 1 ELSE 0 END) AS starts,
SUM(CASE WHEN action = ‘cancel’ THEN 1 ELSE 0 END) AS cancels,
SUM(CASE WHEN action = ‘publish’ THEN 1 ELSE 0 END) AS publishes
FROM users
GROUP BY 1
ORDER BY 1)— calculate publication, cancelation rate for each user by dividing by number of starts, casting as float by multiplying by 1.0 (default floor division is a quirk of some SQL tools, not always needed)SELECT
user_id,
1.0*publishes/starts AS publish_rate,
1.0*cancels/starts AS cancel_rate
FROM t1

2- تغییرات در دارایی خالص

به کمک جدول تراکنش ‌های بین دو کاربر زیر ، یک کوئری بنویسید تا تغییر در ارزش خالص هر کاربر را با کاهش تغییر نرخ خالص برگرداند.

WITH transactions (sender, receiver, amount, transaction_date)
AS (VALUES
(5, 2, 10, CAST(‘2-12-20’ AS date)),
(1, 3, 15, CAST(‘2-13-20’ AS date)),
(2, 1, 20, CAST(‘2-13-20’ AS date)),
(2, 3, 25, CAST(‘2-14-20’ AS date)),
(3, 1, 20, CAST(‘2-15-20’ AS date)),
(3, 2, 15, CAST(‘2-15-20’ AS date)),
(1, 4, 5, CAST(‘2-16-20’ AS date))),
— sum amounts for each sender (debits) and receiver (credits)debits AS (
SELECT
sender,
SUM(amount) AS debited
FROM transactions
GROUP BY 1 ),credits AS (
SELECT
receiver,
SUM(amount) AS credited
FROM transactions
GROUP BY 1 )– full (outer) join debits and credits tables on user id, taking net change as difference between credits and debits, coercing nulls to zeros with coalesce()SELECT
COALESCE(sender, receiver) AS user,
COALESCE(credited, 0) – COALESCE(debited, 0) AS net_change
FROM debits d
FULL JOIN credits c
ON d.sender = c.receiver
ORDER BY 2 DESC

3- متداول ترین اقلام

با استفاده از جدول زیر که حاوی لیستی از تاریخ ها و اقلام سفارش شده است، یک کوئری بنویسید تا بیشترین اقلام سفارش داده شده در هر تاریخ را برگرداند . در صورت تساوی چندین قلم را برگرداند.

WITH items (date, item)
AS (VALUES
(CAST(’01-01-20′ AS date),’apple’),
(CAST(’01-01-20′ AS date),’apple’),
(CAST(’01-01-20′ AS date),’pear’),
(CAST(’01-01-20′ AS date),’pear’),
(CAST(’01-02-20′ AS date),’pear’),
(CAST(’01-02-20′ AS date),’pear’),
(CAST(’01-02-20′ AS date),’pear’),
(CAST(’01-02-20′ AS date),’orange’)),– add an item count column to existing table, grouping by date and item columnst1 AS (
SELECT
date,
item,
COUNT(*) AS item_count
FROM items
GROUP BY 1, 2
ORDER BY 1),– add a rank column in descending order, partitioning by datet2 AS (
SELECT
*,
RANK() OVER (PARTITION BY date ORDER BY item_count DESC) AS date_rank
FROM t1)– return all dates and items where rank = 1SELECT
date,
item
FROM t2
WHERE date_rank = 1

4- تفاوت زمانی بین آخرین اقدامات

از جدول اقدامات کاربر زیر، یک کوئری بنویسید تا برای هر کاربر زمان سپری شده بین آخرین اقدام و اقدام یکی مانده به آخر را به ترتیب صعودی بر اساس شناسه کاربر بازگرداند.

WITH users (user_id, action, action_date)
AS (VALUES
(1, ‘start’, CAST(‘2-12-20’ AS date)),
(1, ‘cancel’, CAST(‘2-13-20’ AS date)),
(2, ‘start’, CAST(‘2-11-20’ AS date)),
(2, ‘publish’, CAST(‘2-14-20’ AS date)),
(3, ‘start’, CAST(‘2-15-20’ AS date)),
(3, ‘cancel’, CAST(‘2-15-20’ AS date)),
(4, ‘start’, CAST(‘2-18-20’ AS date)),
(1, ‘publish’, CAST(‘2-19-20’ AS date))),
— create a date rank column, partitioned by user ID, using the ROW_NUMBER() window function t1 AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY action_date DESC) AS date_rank
FROM users ),– filter on date rank column to pull latest and next latest actions from this tablelatest AS (
SELECT *
FROM t1
WHERE date_rank = 1 ),next_latest AS (
SELECT *
FROM t1
WHERE date_rank = 2 )– left join these two tables, subtracting latest from second latest to get time elapsed SELECT
l1.user_id,
l1.action_date – l2.action_date AS days_elapsed
FROM latest l1
LEFT JOIN next_latest l2
ON l1.user_id = l2.user_id
ORDER BY 1

آموزش دستور inner join در sql (به زبان ساده)

5- کاربران فوق العاده

یک شرکت کسانی را به عنوان کاربران فوق العاده اش تعریف می کند که حداقل دو تراکنش انجام داده باشند. از جدول زیر، یک کوئری بنویسید تا برای هر کاربر، تاریخ تبدیل شدن آن ها به یک کاربر فوق ‌العاده را که ابتدا توسط قدیمی ‌ترین سوپر کاربران مرتب شده است، برگرداند . کاربرانی که سوپرکاربر نیستند هم باید در جدول حضور داشته باشند.

WITH users (user_id, product_id, transaction_date)
AS (VALUES
(1, 101, CAST(‘2-12-20’ AS date)),
(2, 105, CAST(‘2-13-20’ AS date)),
(1, 111, CAST(‘2-14-20’ AS date)),
(3, 121, CAST(‘2-15-20’ AS date)),
(1, 101, CAST(‘2-16-20’ AS date)),
(2, 105, CAST(‘2-17-20’ AS date)),
(4, 101, CAST(‘2-16-20’ AS date)),
(3, 105, CAST(‘2-15-20’ AS date))),
— create a transaction number column using ROW_NUMBER(), partitioning by user IDt1 AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date) AS transaction_number
FROM users),– filter resulting table on transaction_number = 2t2 AS (
SELECT
user_id,
transaction_date
FROM t1
WHERE transaction_number = 2 ),– left join super users onto full user table, order by date t3 AS (
SELECT DISTINCT user_id
FROM users )SELECT
t3.user_id,
transaction_date AS superuser_date
FROM t3
LEFT JOIN t2
ON t3.user_id = t2.user_id
ORDER BY 2

6- پیشنهاد محتوا (سخت)

با استفاده از دو جدول زیر، یک کوئری بنویسید تا پیشنهاد کننده های صفحه را به کاربر رسانه های اجتماعی بر اساس صفحاتی که دوستانش دوست داشته اند، اما هنوز به عنوان ” پسندیده ” علامت گذاری نکرده اند، بازگرداند. نتیجه را با افزایش شناسه کاربری مرتب کنید.

WITH friends (user_id, friend)
AS (VALUES
(1, 2), (1, 3), (1, 4), (2, 1), (3, 1), (3, 4), (4, 1), (4, 3)),likes (user_id, page_likes)
AS (VALUES
(1, ‘A’), (1, ‘B’), (1, ‘C’), (2, ‘A’), (3, ‘B’), (3, ‘C’), (4, ‘B’)),
— inner join friends and page likes tables on user_idt1 AS (
SELECT
l.user_id,
l.page_likes,
f.friend
FROM likes l
JOIN friends f
ON l.user_id = f.user_id ),– left join likes on this, requiring user = friend and user likes = friend likes t2 AS (
SELECT
t1.user_id,
t1.page_likes,
t1.friend,
l.page_likes AS friend_likes
FROM t1
LEFT JOIN likes l
ON t1.friend = l.user_id
AND t1.page_likes = l.page_likes )– if a friend pair doesn’t share a common page like, friend likes column will be null – pull out these entries SELECT DISTINCT
friend AS user_id,
page_likes AS recommended_page
FROM t2
WHERE friend_likes IS NULL
ORDER BY 1