SELECT COUNT(St_age)
FROM Student;

SELECT DISTINCT Ins_Name
FROM Instructor;

SELECT
    St_Id AS "Student ID",
    ISNULL(St_Fname + ' ' + St_Lname, 'No Name') AS "Student Full Name",
    Dept_Name AS "Department name"
FROM Student s
LEFT JOIN Department d ON s.Dept_Id = d.Dept_Id;

SELECT i.Ins_Name, d.Dept_Name
FROM Instructor i
LEFT JOIN Department d ON i.Dept_Id = d.Dept_Id;

SELECT (s.St_Fname + ' ' + s.St_Lname) AS "Student Full Name", 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) AS "Number of Courses"
FROM Topic t
JOIN Course c ON t.Top_Id = c.Top_Id
GROUP BY t.Top_Name;

SELECT MAX(Salary) AS "Max Salary", MIN(Salary) AS "Min Salary"
FROM Instructor;

SELECT Ins_Name, Salary
FROM Instructor
WHERE Salary < (SELECT AVG(Salary) FROM Instructor);

SELECT d.Dept_Name
FROM Department d
JOIN Instructor i ON d.Dept_Id = i.Dept_Id
WHERE i.Salary = (SELECT MIN(Salary) FROM Instructor);

SELECT TOP 2 Salary
FROM Instructor
ORDER BY Salary DESC;

SELECT Ins_Name, COALESCE(CAST(Salary AS varchar), 'bonus') AS Salary
FROM Instructor;

SELECT AVG(Salary) AS "Average Salary"
FROM Instructor;

SELECT s1.St_Fname AS "Student Name", s2.*
FROM Student s1
JOIN Student s2 ON s1.St_super = s2.St_Id;

WITH RankedSalaries AS (
    SELECT
        Ins_Name,
        Salary,
        Dept_Name,
        DENSE_RANK() OVER(PARTITION BY d.Dept_Id ORDER BY Salary DESC) as rn
    FROM Instructor i
    JOIN Department d ON i.Dept_Id = d.Dept_Id
    WHERE i.Salary IS NOT NULL
)
SELECT Ins_Name, Salary, Dept_Name
FROM RankedSalaries
WHERE rn <= 2;

WITH RandomStudent AS (
    SELECT
        St_Fname,
        Dept_Name,
        ROW_NUMBER() OVER(PARTITION BY d.Dept_Id ORDER BY NEWID()) as rn
    FROM Student s
    JOIN Department d ON s.Dept_Id = d.Dept_Id
)
SELECT St_Fname, Dept_Name
FROM RandomStudent
WHERE rn = 1;

