USE ITI;

SELECT COUNT(*)
FROM Student
WHERE St_Age IS NOT NULL;


SELECT DISTINCT Ins_Name
FROM Instructor;


SELECT St_Id AS Student_ID, ISNULL(St_Fname, '') + ' ' + ISNULL(St_Lname, '') AS Student_Full_Name,
    ISNULL(Dept_Name, 'No Department') AS Department_Name
FROM Student
LEFT JOIN Department D
    ON Student.Dept_Id = D.Dept_Id;


SELECT I.Ins_Name, ISNULL(D.Dept_Name, 'No Department') 
FROM Instructor I
LEFT JOIN Department D
    ON I.Dept_Id = D.Dept_Id;


SELECT S.St_Fname + ' ' + S.St_Lname, C.Crs_Name
FROM Student S
JOIN Stud_Course SC ON S.St_Id = SC.St_Id
JOIN Course C ON SC.Crs_Id = C.Crs_Id
WHERE SC.Grade IS NOT NULL;


SELECT T.Top_Name, COUNT(C.Crs_Id) 
FROM Topic T
LEFT JOIN Course C ON T.Top_Id = C.Top_Id
GROUP BY T.Top_Name;


SELECT MAX(Salary) ,MIN(Salary)
FROM Instructor;


SELECT *
FROM Instructor
WHERE Salary <(SELECT AVG(Salary) FROM Instructor);

SELECT Dept_Name
FROM Department
WHERE Dept_Id = (
    SELECT Dept_Id
    FROM Instructor
    WHERE Salary = (SELECT MIN(Salary) FROM Instructor)
);


SELECT DISTINCT TOP 2 Salary
FROM Instructor;


SELECT Ins_Name,COALESCE(Salary, 0) 
FROM Instructor;


SELECT AVG(Salary) 
FROM Instructor;


SELECT S.St_Fname, I.Ins_Name , I.Salary 
FROM Student S
JOIN Instructor I ON S.St_super = I.Ins_Id;


SELECT Dept_Id, St_Fname, St_Lname
FROM (
    SELECT 
        Dept_Id, St_Fname, St_Lname,
        ROW_NUMBER() OVER (PARTITION BY Dept_Id ORDER BY NEWID()) AS rn
    FROM Student
) AS RandomStudents
WHERE rn = 1;
