use [master]
go
Create database [StrudentDB]
GO
use StrudentDB
GO
-- جدول Students
CREATE TABLE Students (
StudentID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
BirthDate DATE,
Email VARCHAR(100) UNIQUE
);
-- جدول Courses
CREATE TABLE Courses (
CourseID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
CourseName NVARCHAR(100) NOT NULL,
Credits INT NOT NULL
);
-- جدول Enrollments
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
StudentID INT,
CourseID INT,
EnrollmentDate DATE,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
-- ایجاد ایندکس برای بهبود عملکرد جستجو
CREATE INDEX idx_student_name ON Students(FirstName, LastName);
CREATE INDEX idx_course_name ON Courses(CourseName);
GO
-- درج داده‌ها در جدول Students
INSERT INTO Students (FirstName, LastName, BirthDate, Email) VALUES
(N'علی', N'رضایی', '2000-05-15', 'ali.rezaei@example.com'),
(N'زهرا', N'محمدی', '1999-08-22', 'zahra.mohammadi@example.com'),
(N'محمد', N'کریمی', '2001-03-10', 'mohammad.karimi@example.com'),
(N'فاطمه', N'حسینی', '2000-11-30', 'fateme.hosseini@example.com'),
(N'رضا', N'نوری', '1998-07-25', 'reza.nouri@example.com');
-- درج داده‌ها در جدول Courses
INSERT INTO Courses (CourseName, Credits) VALUES
(N'ریاضیات', 3),
(N'فیزیک', 4),
(N'شیمی', 3),
(N'برنامه‌نویسی', 5),
(N'تاریخ', 2);
-- درج داده‌ها در جدول Enrollments
INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate) VALUES
(1, 1, '2023-09-01'),
(2, 2, '2023-09-02'),
(3, 3, '2023-09-03'),
(4, 4, '2023-09-04'),
(5, 5, '2023-09-05');
--نمایش تمام دانشجویان
SELECT * FROM Students;
--نمایش تمام دوره‌ها
SELECT * FROM Courses;
--نمایش تمام ثبت‌نام‌ها
SELECT * FROM Enrollments;
--نمایش نام و نام خانوادگی دانشجویان
SELECT FirstName, LastName FROM Students;
--نمایش نام دوره‌ها و تعداد واحدها
SELECT CourseName, Credits FROM Courses;
--کوئری‌های با شرط
--نمایش دانشجویانی که بعد از سال 2000 متولد شده‌اند
SELECT * FROM Students WHERE BirthDate > '2000-01-01';
--نمایش دوره‌هایی که بیش از 3 واحد دارند
SELECT * FROM Courses WHERE Credits > 3;
--نمایش ثبت‌نام‌های انجام شده در تاریخ خاص
SELECT * FROM Enrollments WHERE EnrollmentDate = '2023-09-01';
--نمایش دانشجویانی که ایمیل خاصی دارند
SELECT * FROM Students WHERE Email = 'ali.rezaei@example.com';
--نمایش دوره‌هایی که نام آن‌ها با "ریاضی" شروع می‌شود
SELECT * FROM Courses WHERE CourseName LIKE 'ریاضی%';
--کوئری‌های JOIN
--نمایش نام دانشجویان و دوره‌هایی که در آن ثبت‌نام کرده‌اند
SELECT Students.FirstName, Students.LastName, Courses.CourseName
FROM Enrollments
JOIN Students ON Enrollments.StudentID = Students.StudentID
JOIN Courses ON Enrollments.CourseID = Courses.CourseID;
--نمایش نام دانشجویانی که در دوره "ریاضیات" ثبت‌نام کرده‌اند
SELECT Students.FirstName, Students.LastName
FROM Enrollments
JOIN Students ON Enrollments.StudentID = Students.StudentID
JOIN Courses ON Enrollments.CourseID = Courses.CourseID
WHERE Courses.CourseName = 'ریاضیات';
--نمایش تعداد دانشجویان ثبت‌نام شده در هر دوره
SELECT Courses.CourseName, COUNT(Enrollments.StudentID) AS StudentCount
FROM Enrollments
JOIN Courses ON Enrollments.CourseID = Courses.CourseID
GROUP BY Courses.CourseName;
--نمایش دانشجویانی که در هیچ دوره‌ای ثبت‌نام نکرده‌اند
SELECT Students.FirstName, Students.LastName
FROM Students
LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
WHERE Enrollments.EnrollmentID IS NULL;
--نمایش دوره‌هایی که هیچ دانشجویی در آن‌ها ثبت‌نام نکرده است
SELECT Courses.CourseName
FROM Courses
LEFT JOIN Enrollments ON Courses.CourseID = Enrollments.CourseID
WHERE Enrollments.EnrollmentID IS NULL;
--کوئری‌های با توابع تجمعی
--نمایش تعداد کل دانشجویان
SELECT COUNT(*) AS TotalStudents FROM Students;
--نمایش تعداد کل دوره‌ها
SELECT COUNT(*) AS TotalCourses FROM Courses;
--نمایش تعداد کل ثبت‌نام‌ها
SELECT COUNT(*) AS TotalEnrollments FROM Enrollments;
--نمایش میانگین تعداد واحدهای دوره‌ها
SELECT AVG(Credits) AS AverageCredits FROM Courses;
--نمایش مجموع واحدهای تمام دوره‌ها
SELECT SUM(Credits) AS TotalCredits FROM Courses;
--کوئری‌های با ORDER BY
--نمایش دانشجویان بر اساس نام به ترتیب صعودی
SELECT * FROM Students ORDER BY FirstName ASC;
--نمایش دوره‌ها بر اساس تعداد واحدها به ترتیب نزولی
SELECT * FROM Courses ORDER BY Credits DESC;
--نمایش ثبت‌نام‌ها بر اساس تاریخ ثبت‌نام به ترتیب صعودی
SELECT * FROM Enrollments ORDER BY EnrollmentDate ASC;
--نمایش دانشجویان بر اساس تاریخ تولد به ترتیب نزولی
SELECT * FROM Students ORDER BY BirthDate DESC;
--نمایش دوره‌ها بر اساس نام به ترتیب صعودی
SELECT * FROM Courses ORDER BY CourseName ASC;
--کوئری‌های با GROUP BY و HAVING
--نمایش دوره‌هایی که بیش از 2 دانشجو در آن‌ها ثبت‌نام کرده‌اند
SELECT Courses.CourseName, COUNT(Enrollments.StudentID) AS StudentCount
FROM Enrollments
JOIN Courses ON Enrollments.CourseID = Courses.CourseID
GROUP BY Courses.CourseName
HAVING COUNT(Enrollments.StudentID) > 2;
--نمایش دانشجویانی که در بیش از یک دوره ثبت‌نام کرده‌اند
SELECT Students.FirstName, Students.LastName, COUNT(Enrollments.CourseID) AS CourseCount
FROM Enrollments
JOIN Students ON Enrollments.StudentID = Students.StudentID
GROUP BY Students.StudentID, Students.FirstName, Students.LastName
HAVING COUNT(Enrollments.CourseID) >= 1;
--نمایش تعداد دانشجویان ثبت‌نام شده در هر دوره به ترتیب نزولی
SELECT Courses.CourseName, COUNT(Enrollments.StudentID) AS StudentCount
FROM Enrollments
JOIN Courses ON Enrollments.CourseID = Courses.CourseID
GROUP BY Courses.CourseName
ORDER BY StudentCount DESC;
--نمایش دوره‌هایی که میانگین تعداد واحدهای آن‌ها بیشتر از 3 است
SELECT CourseName, AVG(Credits) AS AverageCredits
FROM Courses
GROUP BY CourseName
HAVING AVG(Credits) > 3;
--نمایش دانشجویانی که در دوره‌هایی با بیش از 3 واحد ثبت‌نام کرده‌اند
SELECT Students.FirstName, Students.LastName
FROM Enrollments
JOIN Students ON Enrollments.StudentID = Students.StudentID
JOIN Courses ON Enrollments.CourseID = Courses.CourseID
WHERE Courses.Credits > 3;