-- Aggregation Queries
-- 1. Count the number of employees
SELECT COUNT(*) AS EmployeeCount FROM EMP;
-- 2. Calculate the average salary of employees
SELECT AVG(SAL) AS AverageSalary FROM EMP;
-- 3. Find the maximum salary of employees
SELECT MAX(SAL) AS MaximumSalary FROM EMP;
-- 4. Find the minimum salary of employees
SELECT MIN(SAL) AS MinimumSalary FROM EMP;
-- 5. Calculate the total salary paid to employees
SELECT SUM(SAL) AS TotalSalary FROM EMP;
-- Grouping Data Queries
-- 6. Count the number of employees in each job category
SELECT JOB, COUNT(*) AS JobCount
FROM EMP
GROUP BY JOB;
-- 7. Calculate the average salary for each job category
SELECT JOB, AVG(SAL) AS AverageSalary
FROM EMP
GROUP BY JOB;
-- 8. Calculate the total salary for each department
SELECT DEPTNO, SUM(SAL) AS TotalSalary
FROM EMP
GROUP BY DEPTNO;
-- 9. Calculate the average commission for salespeople
SELECT AVG(COMM) AS AverageCommission
FROM EMP
WHERE JOB = 'SALESMAN';
-- Joins Queries
-- 10. Join the EMP and DEPT tables to display employee names along with their department names
SELECT [Link], [Link]
FROM EMP
JOIN DEPT ON [Link] = [Link];
-- 11. Join the EMP and SALGRADE tables to display employee names along with their salary grade
SELECT [Link], [Link]
FROM EMP
JOIN SALGRADE ON [Link] BETWEEN [Link] AND [Link];
-- 12. Join the EMP and DEPT tables and display all employee information along with department names
and locations
SELECT EMP.*, [Link], [Link]
FROM EMP
JOIN DEPT ON [Link] = [Link];
-- Subqueries
-- 13. Select employees who have the highest salary
SELECT * FROM EMP
WHERE SAL = (SELECT MAX(SAL) FROM EMP);
-- 14. Select employees who earn more than the average salary
SELECT * FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP);
-- 15. Select employees who are in departments located in 'DALLAS'
SELECT * FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS');
-- 16. Select the names of employees who do not have a manager
SELECT ENAME FROM EMP
WHERE MGR IS NULL;
-- Advanced Queries
-- 17. Select the name and salary of employees who earn the highest salary in their department
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE (DEPTNO, SAL) IN (
SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO
);
-- 18. Select the departments with no employees
SELECT DEPT.*
FROM DEPT
LEFT JOIN EMP ON [Link] = [Link]
WHERE [Link] IS NULL;
-- 19. Select the names of employees along with their manager's name
SELECT [Link] AS Employee, [Link] AS Manager
FROM EMP E1
LEFT JOIN EMP E2 ON [Link] = [Link];
-- 20. Select the departments along with the number of employees in each department
SELECT [Link], COUNT([Link]) AS EmployeeCount
FROM DEPT
LEFT JOIN EMP ON [Link] = [Link]
GROUP BY [Link];
-- 21. Select the names of employees who joined before 1981
SELECT ENAME FROM EMP
WHERE HIREDATE < '1981-01-01';
-- 22. Select the highest salary in each job category
SELECT JOB, MAX(SAL) AS HighestSalary
FROM EMP
GROUP BY JOB;
-- 23. Select the employees who are earning the highest salary in each job category
SELECT ENAME, JOB, SAL
FROM EMP E1
WHERE SAL = (
SELECT MAX(SAL)
FROM EMP E2
WHERE [Link] = [Link]
);
-- Filtering Data
-- 1. Select faculty members who are professors
SELECT * FROM Faculty WHERE rank = 'Professor';
-- 2. Select faculty members with a salary greater than 25000
SELECT * FROM Faculty WHERE salary > 25000;
-- 3. Select courses taught by faculty member with facId 'F2345'
SELECT * FROM Courses WHERE facId = 'F2345';
-- Joins
-- 4. Join the Faculty and Courses tables to display faculty names along with the courses they teach
SELECT [Link], [Link]
FROM Faculty
JOIN Courses ON [Link] = [Link];
-- Aggregation
-- 5. Count the number of faculty members
SELECT COUNT(*) AS FacultyCount FROM Faculty;
-- 6. Calculate the average salary of faculty members
SELECT AVG(salary) AS AverageSalary FROM Faculty;
-- 7. Find the maximum salary of faculty members
SELECT MAX(salary) AS MaximumSalary FROM Faculty;
-- Grouping Data
-- 8. Count the number of faculty members in each department
SELECT dept, COUNT(*) AS FacultyCount
FROM Faculty
GROUP BY dept;
-- 9. Calculate the average salary for each rank
SELECT rank, AVG(salary) AS AverageSalary
FROM Faculty
GROUP BY rank;
-- Subqueries
-- 10. Select faculty members who have the highest salary
SELECT * FROM Faculty
WHERE salary = (SELECT MAX(salary) FROM Faculty);
-- 11. Select faculty members who earn more than the average salary
SELECT * FROM Faculty
WHERE salary > (SELECT AVG(salary) FROM Faculty);
-- Advanced Queries
-- 12. Select the name and salary of faculty members who earn the highest salary in their department
SELECT facName, salary, dept
FROM Faculty
WHERE (dept, salary) IN (
SELECT dept, MAX(salary)
FROM Faculty
GROUP BY dept
);
-- 13. Select the departments with the total number of courses they offer
SELECT [Link], COUNT([Link]) AS CourseCount
FROM Faculty
JOIN Courses ON [Link] = [Link]
GROUP BY [Link];
-- 14. Select the names of faculty members along with the number of courses they teach
SELECT [Link], COUNT([Link]) AS CourseCount
FROM Faculty
LEFT JOIN Courses ON [Link] = [Link]
GROUP BY [Link];
-- 15. Select the courses that are taught by faculty members who are 'Asso Prof'
SELECT [Link]
FROM Courses
JOIN Faculty ON [Link] = [Link]
WHERE [Link] = 'Asso Prof';
-- 16. Select faculty members who teach more than one course
SELECT [Link], COUNT([Link]) AS CourseCount
FROM Faculty
JOIN Courses ON [Link] = [Link]
GROUP BY [Link]
HAVING COUNT([Link]) > 1;
-- Given Queries
-- 1. Display the names of faculty members who are teaching the 'Database Systems' course and are
'Asso Prof'
SELECT [Link]
FROM Faculty
JOIN Courses ON [Link] = [Link]
WHERE [Link] = 'Database Systems' AND [Link] = 'Asso Prof';
-- 2. Which 'Asso Prof' is/are teaching courses? Display their names
SELECT DISTINCT [Link]
FROM Faculty
JOIN Courses ON [Link] = [Link]
WHERE [Link] = 'Asso Prof';
-- Additional Practice Queries
-- 3. Display the names and departments of faculty members who teach more than one course
SELECT [Link], [Link], COUNT([Link]) AS CourseCount
FROM Faculty
JOIN Courses ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING COUNT([Link]) > 1;
-- 4. Display the names of faculty members along with the titles of the courses they teach in the 'CSE'
department
SELECT [Link], [Link]
FROM Faculty
JOIN Courses ON [Link] = [Link]
WHERE [Link] = 'CSE';
-- 5. Display the total number of courses offered by each department
SELECT [Link], COUNT([Link]) AS TotalCourses
FROM Faculty
JOIN Courses ON [Link] = [Link]
GROUP BY [Link];
-- 6. Display the names of faculty members who do not teach any courses
SELECT facName
FROM Faculty
WHERE facId NOT IN (SELECT DISTINCT facId FROM Courses);
-- 7. Display the course titles and the names of faculty members who teach them, sorted by faculty name
SELECT [Link], [Link]
FROM Courses
JOIN Faculty ON [Link] = [Link]
ORDER BY [Link];
-- 8. Display the faculty names, their ranks, and the number of courses they teach, ordered by the
number of courses they teach in descending order
SELECT [Link], [Link], COUNT([Link]) AS CourseCount
FROM Faculty
LEFT JOIN Courses ON [Link] = [Link]
GROUP BY [Link], [Link]
ORDER BY CourseCount DESC;
-- 9. Find the faculty member with the highest salary and display their name, department, and salary
SELECT facName, dept, salary
FROM Faculty
WHERE salary = (SELECT MAX(salary) FROM Faculty);
-- 10. List the course titles along with the names of the faculty members who teach them, for courses
that have more than one word in their title
SELECT [Link], [Link]
FROM Courses
JOIN Faculty ON [Link] = [Link]
WHERE INSTR([Link], ' ') > 0;
-- 11. Display faculty members who are teaching courses that are worth more than 3 credits
SELECT [Link], [Link]
FROM Faculty
JOIN Courses ON [Link] = [Link]
WHERE [Link] > 3;
-- 12. Display the course titles and the number of faculty members teaching them
SELECT [Link], COUNT([Link]) AS FacultyCount
FROM Courses
JOIN Faculty ON [Link] = [Link]
GROUP BY [Link];
-- 13. List faculty members who teach a course with a specific keyword in its title, e.g., 'Algorithms'
SELECT [Link], [Link]
FROM Faculty
JOIN Courses ON [Link] = [Link]
WHERE [Link] LIKE '%Algorithms%';
-- 14. Display the names of faculty members and the total number of courses they teach, including those
who teach no courses
SELECT [Link], COALESCE(COUNT([Link]), 0) AS CourseCount
FROM Faculty
LEFT JOIN Courses ON [Link] = [Link]
GROUP BY [Link];
-- 15. Find the faculty members who are teaching the course with the highest number of credits
SELECT [Link], [Link], [Link]
FROM Faculty
JOIN Courses ON [Link] = [Link]
WHERE [Link] = (SELECT MAX(credits) FROM Courses);
-- 1. Show all the programs offered by the university
SELECT prName FROM PROGRAM;
-- 2. Show the total semesters and credits in each program
SELECT prName, totSem, prCredits FROM PROGRAM;
-- 3. Show the faculty name, salary, and rank
SELECT fName, fSal, rank FROM FACULTY;
-- 4. Show the records of students who do not have a telephone
SELECT * FROM STUDENT WHERE stPhone IS NULL OR stPhone = '';
-- 5. Show the mid-term marks, sessional, and final marks of each student
SELECT stId, crCode, semName, mTerm, sMrks, fMrks FROM ENROLL;
-- 6. Show the semester result of all students for the current semester
SELECT [Link], SEM_RES.semName, SEM_RES.gpa
FROM STUDENT
JOIN SEM_RES ON [Link] = SEM_RES.stId
WHERE [Link] = (SELECT MAX(curSem) FROM STUDENT);
-- 7. Show the name, current semester result, and the cgpa of all students
SELECT [Link], SEM_RES.gpa, [Link]
FROM STUDENT
JOIN SEM_RES ON [Link] = SEM_RES.stId
WHERE [Link] = (SELECT MAX(curSem) FROM STUDENT);
-- 8. Show the course names of all courses where course credits are 4
SELECT crName FROM COURSE WHERE crCredits = 4;
-- 9. List the names of the courses, names of faculty who are teaching those courses in the current
semester
SELECT [Link], [Link]
FROM COURSE
JOIN CROFRD ON [Link] = [Link]
JOIN FACULTY ON [Link] = [Link]
WHERE [Link] = (SELECT MAX(semName) FROM SEMESTER);
-- 10. Show the name of the student and names of the courses in which he has registered so far
SELECT [Link], [Link]
FROM STUDENT
JOIN ENROLL ON [Link] = [Link]
JOIN COURSE ON [Link] = [Link];
-- 11. Show the name of the student and names of the courses in which he has registered in the current
semester
SELECT [Link], [Link]
FROM STUDENT
JOIN ENROLL ON [Link] = [Link]
JOIN COURSE ON [Link] = [Link]
WHERE [Link] = (SELECT MAX(semName) FROM SEMESTER);