The Relational Model: SQL
Order Instructions
Assignment
M3 Your Turn Exercise: 3-1 – 3-24
Instructions
This Exercise includes answering questions that may involve screenshots of actions you have performed using the database file from module 3. You have six files listed below to complete this assignment.
Files for this exercise are:
o M3 – 24 Exercise.docx
o M3 JCConsulting.accdb
o M3 JCConsulting.sql
o M3 JCConsulting.sql.bak
o M3 MSSQL Server Data_JCConsulting.sql
o Data_JCConsulting.sql
o M3 MySQL_JCConsulting.sql
• All tasks will require the access files, BUT not all tasks will need the extra files included for each exercise.
• You will have to read the document titled, “M3 Steps for completing M3 Your Turn Exercise 3-1 through 3-24” for the step-by-step instructions to see what is required and follow the directions to complete exercises 3-1 through 3-24, listed below:
3-1 Using the JCConsulting database, select the ClientID and ClientName for all clients from the Clients table.
3-2 Select all fields from the Clients table where the Zip field value is 01431.
3-3 Select all fields from the Projects table with the ProjectStartDate field value of greater than or equal to 1/1/2021.
3-4 List all of the fields in the Employees table where the Title field contains Programmer and the Salary field (a monthly value) contains a value less than $6,000.
3-5 List all of the fields in the Employees table where the Title field contains Programmer or the Salary field (a monthly value) contains a value less than $6,000.
3-6 List all of the fields in the Employees table where the HireDate field is in the year 2020.
3-7 List the Description and CategoryID fields for of all records in the TaskMasterList table that do not contain the word Codin in the CategoryID field.
3-8 Select the LastName, Title, and annual salary for all employees with a Title of Programmer. The annual salary is calculated as 12 times the Salary field given the Salary field contains a monthly value.
3-9 List the first and last names of all employees, separated by a space.
3-10 List all the fields from the Employees table where the Title field includes the work “Developer” anywhere in the Title value.
3-11 List all the fields from the Employees table where the Title field is Database Developer, Front End Developer, or Programmer.
3-12 Select all of the fields in the Clients table. Sort the selected records in the ascending order by ClientName.
3-13 Select all of the fields in the Clients table. Sort the selected records by ClientName within Zip.
3-14 Calculate the total of the (monthly) Salary field for all employees in the Employees table, naming the field TotalSalary. Also calculate the count of all employees in the Employees table, naming the field CountOfEmployees.
3-15 For each Zip value in the Clients table, count the number of clients that have the same Zip value, naming the field CountOfZips. Order the records from the greatest count to the least. Add a secondary sort to order the records by the Zip value within each CountOfZips group.
3-16 Limit the number of records selected in the previous SQL statement to only those that have greater than 1 record in the group.
3-17 Using the Employees table, find the last name and salaries of the employees who have a Salary field value greater than Hector Garcia, EmployeeID 65.
3-18 List the ClientID and the ClientName for each client in the Clients table as well as the ProjectID and ProjectNotes for each related project in the Projects table. Order the records by ClientID, then by ProjectID.
3-19 Select the ClientID and ClientName fields from the Clients table, the ProjectID and the ProjectNotes fields from the Projects table, and the TaskID and the TaskDate fields from the ProjectLineItems table. As you may recall from Module 2, the Clients and the Projects tables are connected in the one-to-many relationship based on the common ClientID field and the Projects and ProjectLineItems tables are joined in a one-to-many relationship based on the common ProjectID field.
3-20 List the LastName and Salary of all Employees that are earning over $7,000 a month or who are assigned to a project.
3-21 Update the values in the Salary field of the Employees table to display annual data versus monthly data by multiplying the existing field value by 12.
3-22 Insert a new record into the Employees table with the following information for each column of the table:
EmployeeID: ‘88’,
LastName: ‘Zhao’,
FirstName: ‘Eric’,
HireDate: #01/01/2022#,
Title: ‘Programmer’,
Salary: 59500
3-23 Create an SQL command to delete all records in the ProjectLineItems table for ProjectID 11.
3-24 Create a new table named DatabaseEmployees consisting of all fields from the Employees table in which the Title field value is Database Developer.
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
