Hello, Please help with the following 3 steps: step 4,
Hello,
Please help with the following 3 steps: step 4, step 11 and step 12.I’m using
MySQL 5.7 and the following statements are not supported by MySQL, but they can be emulated.
I have attached the following 2 documents:
– DBM449_Lab1.docx (this is the lab document, I started working the lab, but what I did in step 4 is wrong – it’s not a full outer join).
– Lab1_5.6.16.sql (this is all of the statements I created in MySQL 5.7 workbench).
Steps 4: Simulating FULL OUTER JOIN operator in MySQL:
Although all modern relational Database Management Systems implement the JOIN operator, not all support the FULL OUTER JOIN clause. MySQL does not directly support FULL OUTER JOIN. However, it is possible to emulate this feature. First, determine EXACTLY what results should be returned by a logical FULL OUTER JOIN on the CLIENT and COURSE_ACTIVITY tables. Then, construct a logically equivalent SQL query to provide identical results:
- Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.
Will the equivalent of a FULL OUTER JOIN be helpful to Ann? Why or why not? Place your response in the lab report document for this step.
STEP 11: Simulating the INTERSECT Operator:
MySQL does not implement the INTERSECT relational set operator. However, it is possible to emulate this feature. First, determine EXACTLY what results should be returned by a logical INTERSECT between the CORP_EXTRACT1 table and tables which follow. Then, construct a logically equivalent SQL query to provide identical results.
- Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
- Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
- Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.
Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.
STEP 12: Simulating the MINUS Operator:
MySQL does not implement the MINUS relational set operator. However, it is possible to emulate this feature. First, determine EXACTLY what results should be returned by a logical MINUS between Ann’s tables and the CORP_EXTRACT1 table. Then, construct a logically equivalent SQL query to provide identical results:
- Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
- Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
- Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.
Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.
Thank you,
Justice12345
ATTACHMENT PREVIEW Download attachmentNote!Submit your assignment to the Dropbox located on the silver tab at the top of this page.(See the Syllabus section “Due Dates for Assignments & Exams” for due date information.)Remember ThisConnect to the iLab here.GENERAL OVERVIEWScenario and SummaryMy colleague, Ann Henry, operates a regional training center for a commercial software organization.She created a database to track client progress so she can analyze effectiveness of the certificationprogram. CLIENT, COURSE, and COURSE_ACTIVITY are three of the tables in her database. TheCLIENT table contains client name, company, client number, pre-test score, certification programand e-mail address. The COURSE_ACTIVITY table contains client number, course code, grade, andinstructor notes. The COURSE table contains the course code, course name, instructor, course date,and location. Although she and her instructors enter much of the data themselves, some of the dataare extracted from the corporate database and loaded into her tables.Loading the initial data was easy. For grade entry at the end of each course, a former employeecreated a data entry form for the instructors. Updating most client information and generatingstatistics on client progress is not easy because Ann does not know much SQL. For now, sheexports the three tables into three spreadsheets. To look up a grade in the COURSE_ACTIVITYspreadsheet, she first has to look up client number in the CLIENT spreadsheet. While this is doable,it is certainly not practical. For statistics, she sorts the data in the COURSE_ACTIVITY spreadsheetusing multiple methods to get the numbers she needs.Every month, Ann’s database tables need to be refreshed to reflect changes in the corporatedatabase. Ann describes this unpleasant task. She manually compares the contents of newlyextracted data from corporate to the data in her spreadsheets, copies in the new values, and thenreplaces the database contents with the new values.Ann needs our help. Let’s analyze her situation and determine what advanced SQL she could use tomake her tasks easier.iLAB OVERVIEWScenario and SummaryThe purpose of this lab is to explore join operators to determine which, if any, are appropriate forsolving Ann’s business problems, as described in this week’s lecture.Because Ann prefers to work from Excel spreadsheets, she wants her CLIENT andCOURSE_ACTIVITY tables exported into one spreadsheet rather than two, as she is currently using.We need to determine which, if any, of the join operators will provide the data she wants for thesingle spreadsheet. (Note: we will not perform the export, just determine how to retrieve thenecessary data.) Using the spreadsheet, she will be able to determine1.which course(s) a specific client has taken;
View the Answer2.what grade(s) a specific client has earned in a specific course;3.which clients did not take any courses; and4.which courses were not taken by any client.The CREATE TABLE statements in the SQL script for this lab (see Doc Sharing) contains commandsthat show structure (columns and their data types) of tables CLIENT and COURSE_ACTIVITY. Youmay refer to it while constructing your queries.For this lab you will be creating several documents. First, write your queries in Notepad to create ascript file that will contain all of the queries asked for in lab steps 4 through 13. You can (and should)test each query as you write it to make sure that it works and is returning the correct data. Once youhave all of your queries written then run your entire script file, and copy your results.Make sure thatboth the query and the output are visible in your results. This will give you two files for the lab. Thethird file will be this Lab1 Report document. You will record your responses to the questions asked inthe various lab steps in the DBM449 iLab Results section toward the end of this document.Now let’s begin.i L A B S T E P SSTEP 1: Connect to MySQL, and Create the SchemaBack to TopLog into the MySQL iLab environment (you may also choose to install and run MySQL on your owncomputer in order to complete the labs, if you wish). Create an appropriate schema (e.g.,DBM449LAB1), and add the user ID you will use, and permissions.
Looking for a Similar Assignment? Order now and Get 10% Discount! Use Coupon Code "Newclient"
