دستور SELECT

[bita_s]

سلام
من چند تا جدول دارم که میخوام یه دستور select برای انتخاب یه سری عناصر اون بنویسم. جدول های من به صورت زیر است:

1- رشته ـ درس ـ پیش نیاز
2- شماره دانشجویی ـ درس ـ ترم ـ نمره

البته تعداد جداولم زیاد است ولی میخوام یه select بین این 2 تا جدول داشته باشم.(ضمنا attributeهای جدول همان نام جدول است یعنی برای جدول اول 3تا و برای دومی 4تا attribute)

میخوام برای یه دانشجوی خاص درسهایی را select کنم که دانشجو پیش نیاز اونها را گذرانده باشه(یعنی برای پیش نیاز اونها نمره بالاتر از 10 گرفته باشه)

اگه جواب بدین کمک بزرگی به من کردین
ممنون

[AminSobati]

سلام دوست عزیز،
من در مورد ساختار این جداول یک مقدار هنوز ذهنم نا آشناست. لطفا در این دو مورد کمی راهنمایی بفرمایید:
1) نمره ای که دانشجو برای پیش نیاز اون درس گرفته در کدوم تیبل ذخیره میشه؟ اولی یا دومی؟
2) اگر منظورتون این هست که دو تیبل رو با هم Join کنین، فیلد مشترک بین اونها کدومه؟ حدس میزنم باید شماره دانشجویی در تیبل اول هم باشه.

موفق باشین،
امین ثباتی MCSD

[phantasm]

سلام٬

حدس میزنم باید شماره دانشجویی در تیبل اول هم باشه.
من فکر میکنم در اثر نرمال کردن زیاد ٬ ساختار جداول این شکلی شده.
ببین این مشکلت رو حل میکنه؟ :roll:


select DISTINCT dars from table1
where NOT EXISTS(select * from table1
where (SN='شماره مورد نظر')
AND (NOT EXISTS(select * from table2
where (table1.dars=table2.dars)
AND (table2.pishniaz>= 10) ))

SN همون شماره دانشجوییه

[AminSobati]

در نرمال سازی زمانی که فیلدهای مرتبط به فیلد اصلی رو به تیبل جدید انتقال میدیم، فیلد اصلی در تیبل اولیه هم به عنوان Foreign Key باقی میمونه و ارتباط حفظ میشه.
البته اگر دوستمون بیشتر راهنمایی کنن، ما کمتر مجبور میشیم از قوه تخیل خودمون برای تکمیل تیبلها استفاده کنیم! :)

[rsalimian]

شما مهمترین کاری که باید در ابتدا انجام بدهید اینه است که٬‌
1- طراحی جداول بهینه
2- مشخص کردن کلید اصلی
3- ارتباط و relation بین جداول
حداقل این سه مورد را باید دقیق انجام داد تا توانست به راحتی query های دلخواه را ایجاد کرد.
پس با مفروضات زیر:
- جدول درسtblDars شامل فیلدهای darsID, darsName,.....
- جدول رشته tblReshte شامل فیلدهای reshteID, fk_darsID,rshPsihniaz
- جدول نمرات دانشجوtblDaneshjoo شامل فیلدهای stdID, fk_darsID,term,grade
میتوان نوشت:


SELECT tblDars.darsID
FROM tblDaneshjoo INNER JOIN
tblDars ON tblDaneshjoo.fk_darsID = tblDars.darsID INNER JOIN
tblReshte ON dbo.tblDars.darsID = tblReshte.fk_darsID
WHERE (tblDars.darsID IN
(SELECT tblDaneshjoo.fk_darsID
FROM tblDaneshjoo
WHERE grade > 10 AND stdID = 300))
البته بهترین راه برای تولید query فوق و تست آن استفاده از برنامه هایی مانند access یا sql server می باشد . جستجوی فوق را من با sql server تولید کردم و کاملا جواب داد.
موفق باشید

[bita_s]

سلام
من چند وقتی به اینترنت دسترسی نداشتم و نتونستم جوابها رو ببینم .
از دوستانی که لطف کردند و جواب دادند خیلی ممنونم. ولی متاسفانه هیچ کدوم از دستورات جواب نداد .
فکر کنم من سوالم رو درست مطرح نکردم. من چند تا جدول دارم که به صورت زیر است:
1-student(pk_studentID,name,lastName,email
2-field(pk_fieldID,name
3-course(pk_courseID,name,unit
4-term(pk_termID
5-field_course_pre(fk_fieldID,fk_courseID,fk_courseI D
6-field_course_type(fk_fieldID,fk_courseID,type
7-student_course_grade(fk_studentID,fk_courseID,fk_t erm,grade
8-course_term_exam(fk_courseID,fk_termID,ExamDate,ca pacity
اگه فکر می کنید در ساختار این جداول مشکلی وجود داره لطفا بگین.
لازم است در مورد –رشته_درس_نوع –این توضیح رو بدم که منظور از نوع دروس اختصاصی یا عمومی و یا غیره است که برای جلوگیری از افرونگی داده اون رو از جدول پیش نیازها جدا کردم.در ضمن انتخاب باید از جدول اولی انجام بشه که شامل لیست همه دروس است .(ممکن است درسی پیش نیاز نداشته باشه و در جدول پیش نیازها نام آن ذکر نشه)
حالا می خوام دستوری بنویسم که برای یک دانشجوی خاص درسهایی را انتخاب کنم که اولا دانشجو قبلا آنها را پاس نکرده باشد(دروسی که در جدول نمره ها نمره بیشتر از 10 داشته باشندپاس شده اند) و ثانیا دانشجو همه پیش نیازهای آنها را پاس کرده باشد(یعنی دروسی که دانشجو می تواند در ترم جدید انتخاب کند)
فکر می کنم که دیگه زیادی توضیح دادم.
اگه لطف کنید و جواب بدین خیلی ممنون میشم.

[AminSobati]

سلام،
من سوالی که در Post اول مطرح کردم رو مجددا میپرسم:
نمراتی که دانشجو در امتحان درس پیش نیاز گرفته در کدوم جدول ذخیره میشه؟ چرا جدول 5 دو تا fk_courseID داره؟

[bita_s]

نمرات دروس پیش نیاز هم در جدول 7 ذخیره میشه (یعنی جدول نمرات) برای چک کردن اینکه آیا پیش نیازهای یک درس گذرانده شده یا نه باید چک کنیم که آیا همه این پیش نیازها در جدول 7 نمره بالاتر از 10 دارند یا نه؟
امیدوارم سوالتان را درست فهمیده باشم . اگر شما فکر می کنید اشتباهی در ساختار جداول وجود دارد لطفا ساختار درست آنها را بگوئید .
در مورد جدول 5 ، هم دروس و هم پیش نیاز آنها نوعی درس هستند که باید از جدول 3 انتخاب شوند و هر دو از نوع درس هستند.

[AminSobati]

دوست عزیز در یک جدول دو فیلد نمیتونن هم اسم باشن. لطفا تکلیف جدول 5 رو روشن کنین!
ضمنا، تا جایی که بنده متوجه شدم، هم نمرات دروس پیش نیاز و هم دروس اصلی در جدول 7 ذخیره میشن. پس در جدول Course باید یک فیلد وجود داشته باشه تا معلوم بشه هر درس از چه نوع درسیه (اصلی یا پیش نیاز)

[bita_s]

اسم دو تا فیلد در جدول 5 یکی نیست من فقط خواستم نشان بدهم که هر دو تا فیلد از یک جنس و Foreign Key هستند(متوجه نشدم که اسم هر دو تا فیلد یکی شده) ولی در جداولم در SQL نام آنها متفاوت است :oops:
در مورد سوال دوم باید بگم که این مساله در جدول 5 بررسی می شود، این جدول نشان می دهد که در هر رشته پیش نیاز هر درس چه دروسی هستند.
فیلدهای این جدول(رشته-درس-پیش نیاز)است :roll:

[AminSobati]

از توضیحاتتون ممنونم،
زمانی که قصد داریم پیش نیازهای یک درس رو پیدا کنیم از جدول 5، لازم داریم که بدونیم برای کدوم رشته این کار رو انجام میدیم. لذا به نظر میرسه جدول اول باید رشته درسی دانشجو رو برای انجام query تامین کنه. یا شاید من درست متوجه طراحی نشدم؟
چند نکته دیگه:
1) آیا یک درس، همیشه پیش نیازهای ثابت داره یا اگر همون درس برای رشته دیگه ای هم باشه، پیش نیازهاش برای اون رشته تغییر میکنه؟
2) آیا یک درس پیش نیاز، میتونه برای بیشتر از یک درس نقش پیش نیاز داشته باشه یا همیشه برای یک درس خاص پیش نیاز محسوب میشه.

موفق باشین

[bita_s]

از توجهتون واقعا ممنونم.
در مورد سوالاتتون :
1-رشته دانشجو هم یکی از فیلدهای جدول 1 است
2- پیش نیازهای دروس در رشته های مختلف متفاوت است و فیلد رشته در جدول 5 هم به همین خاطر اضافه شده
3- دروس مختلف میتوانند برای بیشتر از یک درس نقش پیش نیاز داشته باشند، درست مثل دروسی که در دانشگاه می گذرانیم

[AminSobati]

خوب،
من با اجازتون این دو تغییر رو در جدول 1 و 5 انجام دادم تا بتونم query رو بنویسم:


1-student(pk_studentID,name,lastName,email,fk_FieldI D)
5-field_course_pre(fk_fieldID,fk_courseID,fk_courseI D_Pre)

در جدول 5، فیلد fk_courseID_Pre نشون دهنده پیش نیاز برای fk_courseID هستش. در جدول 1 هم که فقط fk_FieldID اضافه شده. حالا برای query مینویسیم:


DECLARE @StudentID int,@FieldID int
-- farz mikonim daneshjooye shomareye 12345 morde nazare mast,
-- shoma avazesh konid be ye IDie sahih:
SET @StudentID = 12345
-- reshteye in daneshjoo ro bayad bedast biarim:
SELECT @FieldID=fk_FieldID FROM Student WHERE pk_StudentID=@StudentID
IF @FieldID IS NULL
BEGIN
Print 'Invalid Field ID!!!'
RETURN
END

SELECT pk_CourseID FROM Course WHERE pk_CourseID IN
--**** list e course hayee ke ghablan pass nakarde ****
(SELECT DISTINCT fk_CourseID FROM Field_Course_Pre FCP WHERE
NOT EXISTS (SELECT fk_CourseID FROM Student_Course_Grade WHERE
FCP.fk_CourseID=fk_CourseID AND Grade >=10))
AND pk_CourseID NOT IN
--**** list e course hayee ke pish niazeshoon ro pass nakarde ****
(SELECT DISTINCT fk_CourseID FROM Field_Course_Pre
WHERE fk_CourseID_Pre NOT IN
(SELECT fk_CourseID FROM Student_Course_Grade WHERE fk_StudentID=@StudentID
AND Grade>=10)
AND fk_FieldID=@FieldID)


موفق باشید،
امین ثباتی MCSD

[bita_s]

خیلی ممنون از راهنمائی تون ، فقط این دستور یک مشکلی داره و اون اینه که چون همه select ها با توجه به جدول پیش نیازها انجام میشه ، اگه درسی پیش نیاز نداشته باشه SELECT نمیشه

[AminSobati]

اگر درسی پیش نیاز نداشته باشه، در جدول 5 برای fk_courseID_Pre مقدار NULL قرار میدین؟

[bita_s]

نه ، دروسی که پیش نیاز ندارند در جدول 5 ذکر نمی شوند و باید از جدول دروس انتخاب شوند

[AminSobati]

پس از جدول Field_Course_Type استفاده میکنیم و فقط Query رو کمی تغییر میدیم:


DECLARE @StudentID int,@FieldID int
-- farz mikonim daneshjooye shomareye 12345 morde nazare mast,
-- shoma avazesh konid be ye IDie sahih:
SET @StudentID = 12345
-- reshteye in daneshjoo ro bayad bedast biarim:
SELECT @FieldID=fk_FieldID FROM Student WHERE pk_StudentID=@StudentID
IF @FieldID IS NULL
BEGIN
Print 'Invalid Field ID!!!'
RETURN
END

SELECT pk_CourseID FROM Course WHERE pk_CourseID IN
--**** list e course hayee ke ghablan pass nakarde ****
(SELECT DISTINCT fk_CourseID FROM
(SELECT fk_CourseID FROM Field_Course_Type WHERE fk_FieldID=@FieldID) FCT
WHERE NOT EXISTS (SELECT fk_CourseID FROM Student_Course_Grade WHERE
FCT.fk_CourseID=fk_CourseID AND Grade >=10))
AND pk_CourseID NOT IN
--**** list e course hayee ke pish niazeshoon ro pass nakarde ****
(SELECT DISTINCT fk_CourseID FROM Field_Course_Pre
WHERE fk_CourseID_Pre NOT IN
(SELECT fk_CourseID FROM Student_Course_Grade WHERE fk_StudentID=@StudentID
AND Grade>=10)
AND fk_FieldID=@FieldID)

[bita_s]

سلام
از توجه و لطفتون خیلی ممنونم ،کمک بزرگی به من کردید.
امیدوارم همیشه موفق باشید :flower:

[AminSobati]

:)

[bita_s]

سلام

من دوباره یک مشکل در رابطه با Select در این Data base پیدا کردم :roll: :?
من می خوام دانشجوها یی رو Select کنم که همه ی دروس رو گذرونده باشند.(دانشجویان فارغ التحصیل)

اگه لطف کنید وجواب بدید ممنون می شم :flower:

[AminSobati]

امیدوارم این کمک بکنه:


SELECT pk_StudentID FROM Student WHERE NOT EXISTS
(SELECT Exams.fk_CourseID, SCG.Grade FROM
(SELECT fk_CourseID FROM Field_Course_Type WHERE fk_FieldID=Student.fk_FieldID
UNION
SELECT fk_CourseID_Pre FROM Field_Course_Pre WHERE fk_FieldID=Student.fk_FieldID) Exams
LEFT OUTER JOIN Student_Course_Grade SCG ON (Exams.fk_CourseID=SCG.fk_CourseID
AND Student.pk_StudentID=SCG.fk_StudentID)
WHERE SCG.Grade<10 OR SCG.Grade IS NULL)

[bita_s]

باز هم از لطفتون ممنونم
ولی این دستور یک مشکل کوچیک داره و اون اینه که اگر دانشجو درسی رو یک بار افتاده باشه و دوباره اونرو پاس کرده باشه ، اون درس برای او پاس شده به حساب نمی آید و این دانشجو فارغ التحصیل به حساب نمی یاد. :?:

ضمنا اگه ممکنه یک سوال دیگه هم داشتم :oops:
من برای کارهای آماری پروژه ام از قبیل مشخص کردن رتبه ی دانشجویان نیاز دارم که معدل دانشجو ها را داشته باشم .
به نظر شما آیا بهتره که یک فیلد در جدول به نام Average بذارم و هر بار که نمره ای برای دانشجو وارد می شه اون معدل دوباره حساب بشه یا اینکه موقع کارهای آماری این معدل برای دانشجو حساب بشه.

خیلی از کمکتون مچکرم. 8)

[AminSobati]

من Query رو بازنویسی کردم اما در این روش از تعداد امتحانات لازم برای هر رشته و تعداد امتحاناتی که دانشجو پاس کرده کمک گرفتم. در این حالت اگر دانشجو یک امتحان رو 3 بار افتاده باشه و لی دفعه چهارم قبول شده باشه، کلا یک بار براش شمرده میشه.
یک نکته: چون تعداد امتحانات پاس شده ملاک هستند، اگر دانشجویی یک امتحان رو دو بار داده باشه و هر دوبار پاس کرده باشه، این میتونه یکی از امتحانات پاس نشده رو جبران کنه. ولی حدس زدم سیستم نباید اجازه بده امتحانی رو که شخصی پاس کرده مجددا شرکت کنه.
امیدوارم این روش با نیازهای پروژه مغایرتی نداشته باشه.



SELECT pk_StudentID FROM Student WHERE
(SELECT COUNT(fk_CourseID) AS 'CCount' FROM
(SELECT fk_FieldID, fk_CourseID FROM Field_Course_Type WHERE fk_FieldID=Student.fk_FieldID
UNION
SELECT fk_FieldID, fk_CourseID_Pre FROM Field_Course_Pre WHERE fk_FieldID=Student.fk_FieldID) Exams
GROUP BY fk_FieldID)
=
(SELECT COUNT(fk_CourseID) AS 'CCount' FROM Student_Course_Grade WHERE fk_StudentID=Student.pk_StudentID AND Grade>=10
GROUP BY fk_StudentID)