The tblEmploy table contains seven fields. The EmpNum, Rate, and Code fields are numeric. The LastName, FirstName, Hired, and Status fields contain text. The Status field contains either the letter F (for full time) or the letter P (for part time). The Code field identifies the employee’s department: 1 for Accounting, 2 for Advertising, 3 for Personnel, and 4 for Inventory.
a. Write a SQL SELECT statement that selects all of the fields and records in the table and then sorts the records in ascending order by the Code field.
b. Write a SQL SELECT statement that selects only the EmpNum, LastName, and FirstName fields from all of the records.
c. Write a SQL SELECT statement that selects only the records for full-time employees.
d. Write a SQL SELECT statement that selects the EmpNum and Rate fields for employees in the Personnel department.
e. Write a SQL SELECT statement that selects the EmpNum and LastName fields for employees having the last name Smith.
f. Write a SQL SELECT statement that selects the EmpNum and LastName fields for employees having a last name that begins with the letter S.
g. Write a SQL SELECT statement that selects only the first and last names for part-time employees and then sorts the records in descending order by the LastName field.
h. Open the Morgan Industries Solution (Morgan Industries Solution.sln) file contained in the VB2015\Chap14\Morgan Industries Solution-SQL folder. The application is connected to the Employees database. Start the application to view the records contained in the dataset, and then stop the application. Open the DataSet Designer window and then start the TableAdapter Query Configuration Wizard. Open the Query Builder dialog box. Which field in the table is the primary key? How can you tell that it is the primary key?
i. Use the Query Builder dialog box to test your SELECT statements from Steps a through g.
