Create the following SQL queries using the specified DML statement in the query window. Comment each step in the script. A. Write a SELECT statement that returns these columns:

A. The DepartmentName column from the Departments table The CourseNumber column from the Courses table The FirstName column from the Instructors table - (If null, display as Not Available) The LastName column from the Instructors table 

B. Add a column that includes 

1. the first three characters from the DepartmentName column in uppercase, concatenated with the CourseNumber column, 

2. the first character of the FirstName column if this column isn't null or an empty string otherwise, 

3. the LastName column. For this to work, you will need to cast the CourseNumber column to a character column. 

C. This query needs to join the Courses and Departments tables by DepartmentID and also join the Courses and Instructors tables by InstructorID.

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

A. The FirstName column The LastName column The EnrollmentDate column in the format MMM DD ,YYYY The GraduationDate column in the format MMM DD , YYYY

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

C. Return one row for each student who has graduated. 

C. Write a Select statement that returns data generated from the Instructors table: A. Column1 - Using Concat, combine the firstname, 1 space, and lastname as 'Name'. 1. Nest the isNull function in Concat - if the firstname is null return 'NA' B. Column 

2 - Use the Case command for Status. Name this column as 'Status' 1. Value 'F' returns 'Full Time' 2. Value 'P' returns 'Adjunct' 3. Otherwise return 'Unknown' 

C. Column 3 - Use the Case command for DepartmentChairman. Name this column as 'Role' 1. Value 1 the return 'Department Chair' 2. Otherwise return ' 

D. Column 4 - Using the Dense_Rank function using AnnualSalary descending. Name as 'SalaryRank'. 

E. Sort the results by Status and then Name. 

D. Run your created script

Answer rating: 100% (QA)

A Here is the SQL query that returns the specified columns sql SELECT DDepartmentName CCourseNumber ... View the full answer