تمرین سری ۵ دو روز تمدید شد

مباحث میانترم فصل های ۲-۳-۴ـ۶ و بخش تریگر از فصل ۵ می باشد.

مثال به همراه جواب برای مباحث sql و جبر رابطه ای👆👆

نمونه سوال میانترم برای ترم های گذشته

میانترم ترم گذشته

✅ جوابیه تمرین سری 3

تمرین سری ۵ تا فرداشب تمدید شد.

https://meet.google.com/ehu-morh-pgo

لینک جلسه رفع اشکال

ویدئوی جلسه رفع اشکال

جواب سوالات sql مربوط به نمونه سوال به تاریخ 1400/9/17:
الف)
SELECT bname FROM Boats WHERE color = 'Red';
ب)
SELECT DISTINCT s.sname
FROM Sailors s
JOIN Reserves r1 ON s.sid = r1.sid
JOIN Boats b1 ON r1.bid = b1.bid
JOIN Reserves r2 ON s.sid = r2.sid
JOIN Boats b2 ON r2.bid = b2.bid
WHERE b1.color = 'Red' AND b2.color = 'Green';

ج)
SELECT s.sname
FROM Sailors s
JOIN Reserves r ON s.sid = r.sid
JOIN Boats b ON r.bid = b.bid
GROUP BY s.sid, s.sname
HAVING COUNT(DISTINCT r.bid) = (SELECT COUNT(*) FROM Boats);

د)
SELECT b.bname
FROM Boats b
JOIN Reserves r ON b.bid = r.bid
WHERE b.color = 'Red'
GROUP BY b.bname
HAVING COUNT(r.sid) > 2;
ه)
SELECT DISTINCT s.sname
FROM Sailors s
WHERE s.sid IN (
SELECT r.sid
FROM Reserves r
JOIN Boats b ON r.bid = b.bid
WHERE b.color = 'Green'
GROUP BY r.sid
HAVING COUNT(r.bid) > 1
);
و)
SELECT
s.sname,
b.bname,
b.color
FROM
Sailors s
JOIN Reserves r1 ON s.sid = r1.sid
JOIN Boats b ON r1.bid = b.bid
WHERE
r1.date = (
SELECT r2.date
FROM Reserves r2
WHERE r2.sid = r1.sid
ORDER BY r2.date
LIMIT 1 OFFSET 1
);
ز)
SELECT s.sname, s.age
FROM Sailors s
WHERE s.age > (SELECT AVG(age) FROM Sailors);

جواب سوالات sql مربوط به نمونه سوال به تاریخ 1403/02/19:
الف)
SELECT b.bname
FROM Boats b
JOIN Reserves r ON b.bid = r.bid
JOIN Sailors s ON r.sid = s.sid
WHERE s.age < 40;
ب)
SELECT DISTINCT s.sname
FROM Sailors s
JOIN Reserves r ON s.sid = r.sid
JOIN Boats b ON r.bid = b.bid
WHERE b.color = 'قرمز' AND r.date < '2014-02-24';

ج)
SELECT b.*
FROM Boats b
JOIN Reserves r ON b.bid = r.bid
WHERE r.sid = 1
ORDER BY r.date
LIMIT 1;

د)
SELECT b.bname
FROM Boats b
JOIN Reserves r ON b.bid = r.bid
GROUP BY b.bname, r.sid
HAVING COUNT(r.bid) > 2;
ه)
SELECT S.sname, COALESCE(COUNT(R.bid), 0) AS ReservationCount
FROM Sailors S
LEFT JOIN Reserves R ON S.sid = R.sid
GROUP BY S.sname;

و)
SELECT S.sname, B.bname, MIN(R.date) AS SecondReservationDate
FROM Sailors S
JOIN Reserves R ON S.sid = R.sid
JOIN Boats B ON R.bid = B.bid
WHERE R.date > (
SELECT MIN(R2.date)
FROM Reserves R2
WHERE R2.sid = R.sid AND R2.bid = R.bid
)
GROUP BY S.sname, B.bname
ORDER BY SecondReservationDate;

تمرین سری ۵ تا یکشنبه شب تمدید شد