Question

In SQL we are working with functions

1. Write a SELECT statement that returns these columns from the Instructors table:

a. The AnnualSalary column

b. A column named MonthlySalary that is the result of dividing the AnnualSalary column by 12

c. A column named MonthlySalaryRounded that calculates the monthly salary and then uses the ROUND function to round the result to 2 decimal places

2. Write a SELECT statement that returns these columns from the Students table:

a. The EnrollmentDate column

b. A column that returns the four-digit year that’s stored in the EnrollmentDate column

c. A column that returns only the day of the month that’s stored in the EnrollmentDate column

d. A column that returns the result from adding four years to the EnrollmentDate column; use the CAST function so only the year is returned

3. Write a SELECT statement that returns these columns:

a. The DepartmentName column from the Departments table

b. The CourseNumber column from the Courses table

c. The FirstName column from the Instructors table

d. The LastName column from the Instructors table

Add a column that includes the first three characters from the DepartmentName column in uppercase, concatenated with the CourseNumber column, the first character of the FirstName column if this column isn’t null or an empty string otherwise, and the LastName column. For this to work, you will need to cast the CourseNumber column to a character column.

4. Write a SELECT statement that returns these columns from the Students table:

a. The FirstName column

b. The LastName column

c. The EnrollmentDate column

d. The GraduationDate column

e. A column that shows the number of months between the EnrollmentDate and GraduationDate columns

Return one row for each student who has graduated.

5. Write a CTE with a SELECT statement that returns one row for each student that has courses with these columns:

a. The StudentID column from the Students table

b. The sum of the course units in the Courses table

6. Write a SELECT statement that uses this CTE to return these columns for each student:

a.The StudentID column from the CTE

b.The sum of course units from the CTE

An indication of whether the student is fulltime or parttime (Hint: To determine whether a student is fulltime, use the IIF function to test if the sum of course units is greater than 9.) The total tuition (Hint: To calculate the tuition, use the IIF function to determine whether a student is fulltime or partime. Then, multiply the sum of course units by the PerUnitCost column in the Tuition table and add that to either the FullTimeCost or PartTimeCost column in the Tuition table. To do that, use a cross join to join the CTE and the Tution tables. This makes the columns from the Tuition table available to the SELECT statement.)

Answer 1

(1)	SELECT AnnualSalary ,((Annual_Salary / 12) AS MonthlySalary, ROUND(Annual_Salary / 12.0, 2) AS 'MonthlySalaryRounded ' FROM Instructors ;
(2)	SELECT EnrollmentDate,YEAR(EnrollmentDate) AS OrderYear, DAY(EnrollmentDate) AS EnrollmentDate,DATEADD(YEAR,4,EnrollmentDate) AS '4yearEnrollmentDate' FROM Students ;
(3)	SELECT Departments.DepartmentName , Courses.CourseNumber , Instructors.FirstName, Instructors.LastName FROM Departments , Courses , Instructors
(4)	SELECT FirstName ,LastName ,EnrollmentDate,GraduationDate , (((Date.Year([GraduationDate ])-Date.Year([EnrollmentDate ]))*12) + Date.Month([GraduationDate ]) - Date.Month([EnrollmentDate ]) ) AS NumberofMonths FROM Students ;
(5)	WITH UnitsSummary AS ( SELECT Students.StudentID,SUM(CourseUnits) AS TotalUnits FROM Students JOIN StudentCourses ON Students.StudentID = StudentCourses.StudentID JOIN Courses ON StudentCourses.CourseID = Courses.CourseID GROUP BY Students.StudentID,CourseUnits )

(6)	SELECT StudentID, TotalUnits, IIF(TotalUnits > 9,'FUlltime','Parttime'), FullTimeCost + (TotalUnits * PerUnitCost) AS Tuition FROM UnitsSummary CROSS JOIN Tuition