Need help with these questions to understand.

Chapter 3

How to retrieve data from a single table

Exercises

Enter and run your own SELECT statements

In these exercises, youll enter and run your own SELECT statements.

1. Write a SELECT statement that returns all of the columns from the Courses table. Then, run this statement to make sure it works correctly.

2. Write a SELECT statement that returns three columns from the Courses table: CourseNumber, CourseDescription, and CourseUnits. Then, run this statement to make sure it works correctly.

   Add an ORDER BY clause to this statement that sorts the result set by CourseNumber in ascending sequence. Then, run this statement again to make sure it works correctly. This is a good way to build and test a statement, one clause at a time.

3. Write a SELECT statement that returns one column from the Students table named FullName that joins the LastName and FirstName columns.

   Format this column with the last name, a comma, a space, and the first name like this:

   Doe, John

   Sort the result set by last name in ascending sequence.

   Return only the students whose last name begins with a letter from A to M.

4. Write a SELECT statement that returns these column names and data from the Instructors table:

LastName FirstName AnnualSalary

The LastName column The FirstName column The AnnualSalary column

Return only the rows with an annual salary thats greater than or equal to 60,000.

Sort the result set in descending sequence by the AnnualSalary column. 5. Write a SELECT statement that returns these column names and data from the Instructors table:

LastName FirstName HireDate

The LastName column The FirstName column The HireDate column

Return only the rows with a hire date thats in 2019. Sort the result set in ascending sequence by the HireDate column.

6. Write a SELECT statement that returns these column names and data from the Students table:

FirstName LastName EnrollmentDate CurrentDate MonthsAttended

The FirstName column

The LastName column

The EnrollmentDate column

The current date

A column thats calculated by getting the difference between the enrollment date and the current date

To get the value of the months attended, use the DATEDIFF function with the month argument.

Sort the result set in ascending sequence by the MonthsAttended column. 7. Write a SELECT statement that returns these column names and data from the Instructors table:

FirstName LastName AnnualSalary

The FirstName column The LastName column The AnnualSalary column

Return only the top 20 percent of instructors based on annual salary.

8. Write a SELECT statement that returns these column names and data from the Students table:

   LastName The LastName column

   FirstName The FirstName column

   Return only the rows where the LastName column starts with the letter 'G'. To do that, use the LIKE phrase.

   Sort the result set by last name in ascending sequence.

9. Write a SELECT statement that returns these column names and data from the Students table:

LastName FirstName EnrollmentDate GraduationDate

The LastName column The FirstName column The EnrollmentDate column The GraduationDate column

Return only the rows where the EnrollmentDate column is greater than 12-01-2019 and the GraduationDate column contains a null value.

10. Write a SELECT statement that returns these columns and data from the Tuition table, along with a constant value and two calculated values:

FullTimeCost PerUnitCost Units TotalPerUnitCost

TotalTuition

The FullTimeCost column

The PerUnitCost column

12

A column thats calculated by multiplying the per unit cost by the units

A column thats calculated by adding the full time cost to the total per unit cost

Select * from courses Departments Instructorld 1 1 4 5 2 8 6 16 5 7 9 1 11 1 4 4 5 1 1 1 4 6 16 100 % Results i Messages CourselD CourseNumber Course Description CourseUnits 1 1 36598 Beginning Accounting 3 2 2 48926 Abstract Algebra 3 3 3 14862 Primary Education 3 4 4 54321 Anatomy 3 5 5 82754 Social Psychology 3 6 6 13524 Statistical Analysis 3 7 7 24653 Intro to Marketing 3 8 8 22679 Intro to Calculus 3 9 9 98765 Intermediate Accounting 3 10 10 96032 Social Media 3 11 11 58230 Physiology 3 12 12 81256 Intro to Management 3 13 13 64321 Secondary Education 3 14 14 32751 Business Writing 2 15 15 46972 Biology 4 16 16 15487 Music Theory 3 17 17 28177 Classic Literature 3 18 18 90908 Educational Theory 3 19 19 55783 Shakespeare 3 20 20 63284 Population and Demo... 3 21 21 74832 Creative Writing 3 22 22 33218 Marching Band 2 23 23 37645 Composition 3 24 24 84937 Microbiology 4 25 25 44386 Trigonometry 3 1 7 8 2 1 10 3 12 15 13 15 O W U W W NW U10 7 9 2 12 10 3 4 14 SQL Query 14.sql - ...J3Q8DS\smitt (51))* + X SQLQuery13.sql - L...J3Q8DS\smitt (52))* SELECT * FROM instructors | Status F DepartmentChairman 1 0 FirstName Billy William Rachel Gene NULL L 0 LL LL 1 0 F 1 0 100 % Results i Messages Instructorld LastName 1 1 Brown 2 2 Thomas 3 3 Amundsen 4 4 Green 5 5 McGregor 6 6 Paxton 7 7 Rogers 8 8 Smith 9 9 Connors 10 10 Jones 11 11 Vilma 12 12 Thomas 13 13 Black 14 14 Warren 15 15 Drew 16 16 Gallegos Arnold NULL 0 1 9 Hire Date AnnualSalary DepartmentID 2016-01-10 77500.00 1 2016-03-30 38500.00 3 2016-06-05 79000.00 6 2016-08-02 75000.00 1 2017-01-03 74000.00 4 2017-07-15 36000.00 5 2017-10-22 38000.00 1 2018-02-05 73000.00 2 2018-03-04 71500.00 7 2018-09-21 74000.00 3 2018-11-18 35500.00 1 2019-01-17 35500.00 5 2019-04-20 34000.00 2 2019-07-14 33000.00 4 2019-08-25 72000.00 3 2020-03-23 64000.00 6 1 1 John Daniel Sally Jonathan Derrick Bill Angela Daniel Tomas 0 0 0 O O 0 Results StudentID Graduation Date 2019-12-14 1 1 2 2 3 2019-12-14 2019-05-07 3 4 4 2019-12-14 5 5 6 NULL 2019-12-14 2019-05-07 7 8 NULL 9 10 10 NULL NULL NULL 11 11 12 12 13 13 14 14 15 15 16 16 17 NULL 2019-12-14 NULL NULL NULL NULL NULL NULL NULL NULL 17 18 18 19 Messages LastName FirstName Howard Amber White George MacNamara Tony Welch Jonathan Taylor Donna Price Rose Rodriguez Jesse Williams Bonnie Kent Thomas Kramer Maggie DeLorean Cameron Sanchez Frank Smith Roberta Hoffman Wilma Bonwell Brian Clement Cal Patrick Charles Landry William Morrisey Monica Butler George Yount Anderson Rincon Anthony Hallowell Jimmy Flores Jesus Camden James Easton Barney Sommers Tanya Jones Andrew Jackson Floyd Geary Annette Osborne Letitia Manning Vincent Goodell Conner Griffin Gerald Gardner Faye Franks Karen Johnson Timothy Walker Andrew Cramsden Walter 19 EnrollmentDate 2015-12-18 16:44:26 2015-12-20 11:12:26 2015-12-21 09:21:55 2015-12-21 13:23:10 2015-12-28 10:32:16 2016-01-02 12:37:31 2016-01-03 13:08:37 2016-01-03 15:44:56 2016-07-15 11:14:23 2016-07-15 17:02:45 2016-07-18 12:48:43 2016-07-20 09:37:53 2016-07-22 11:18:25 2016-12-10 15:31:28 2016-12-12 14:22:53 2016-12-14 16:42:11 2016-12-22 08:43:48 2017-01-02 11:28:49 2017-01-04 10:42:06 2017-07-12 13:05:41 2017-07-18 14:21:07 2017-12-08 09:55:15 2017-12-19 13:44:25 2018-01-03 16:23:47 2018-01-04 11:12:31 2018-01-04 14:14:02 2018-07-22 15:41:12 2018-07-24 10:53:26 2018-07-25 09:27:53 2018-07-12 09:33:47 2018-12-12 17:14:22 2018-12-14 15:37:43 2019-01-02 14:21:58 2019-01-02 16:04:04 2019-07-22 08:15:57 2019-07-23 10:42:03 2019-08-04 09:01:04 2019-08-05 13:48:26 2019-12-15 10:18:37 20 20 21 21 22 22 NULL NULL 23 23 24 24 NULL NULL 25 25 26 26 NULL NULL 27 27 28 28 29 29 30 30 31 31 NULL NULL NULL NULL NULL NULL NULL 32 32 33 33 34 34 35 35 NULL NULL 36 36 37 37 NULL NULL 38 38 39 39 NULL Select * from Tuition 100 % Results Bi Messages PartTimeCost FullTimeCost PerUnitCost 1 750.00 1250.00 62.50