Category: MySQL

(I’ll email the fileMaker file since it doesn’t support to attach it here, download FileMaker from https://www.claris.com/trial/resources.html, sign in as guest)***
Assignment Objectives
The idea of a “self-join,” (and how you write a self-join in SQL), which in FileMaker is a relationship in which a single table is linked to itself.
An understanding of how to implement (some) three-table joins in FileMaker.
Be aware, however, that more complicated select statements than we have considered can be far harder to work out than the simple cases we have considered.
The notion of a global field in FileMaker. In many respects, global fields serve as variables in FileMaker. As we’ll see when we discuss scripting in FileMaker, you can create a layout with which users give global fields values, with buttons that execute scripts that use those values while manipulating data.
The notion of creating a value list from the values stored in a given field of some table, and requiring the user to select from that list while entering a value.
———————————————————————————————————————————————————————
———————————————————————————————————————————————————————
Question: (details in attached document)
This question has you create a 3-table join of the Students, Marks and Assignments
tables you will find in the University Starter file. These are slightly simplified versions
of the tables by the same name that you met in the SQL assignment. They contain the
following fields (fields comprising the primary key for each table are shown in bold):
• Download the file UniversityStarter.fmp12 from Learn. Then create a layout in the Students table that looks like the following and behaves like the sample solution to
this question.
Read all instructions before starting.
For a given student, the portal lists the marks for the student in the class selected by
the List Marks for Which Course? popup in the upper right corner. It also shows the
student’s mark in that course, which is computed by multiplying the student’s mark for
each assignment times the weight for that assignment.
3
There are lots of interesting things about this layout.
• How do we specify which course we’re interested in? Which is to say, what is the
List Marks for Which Course? popup connected to?
• One possibility is to create an auxiliary text field (call it WhichCourse), and require
the user to enter a value into it by selecting from a popup menu connected to a
value list whose values are taken from the Course field of the Assignments table.
a) Instead, WhichCourse should be created as a “Global” field. These are “pseudofields” whose values are shared by all records. Only one value is stored in the
table, separate from the records in a table, and that value is seen and shared by all
records in the table. You can still format this as a pop-up. (Be sure to declare that
WhichCourse is of type Text, then click the Options button for WhichCourse and find
the checkbox that makes it a (shared) Global field..)
It would be easy to list ALL the marks for a student in the portal — you’d just define a relationship using the IDnumber field as the link. But we want to showin the portal records from the Marks table for a particular IDnumber and Course,so you want to match the values in two fields as you create a one-to-manyrelationship from Students to Marks.
b) The course, assignment number, and mark columns of the portal are just fields
from the Marks table, which are readily accessible once you have connected the
Students and Marks table. Place these fields into your portal.
c) What about the portal columns containing a textual description of each
assignment and the assignment weight? Those textual descriptions are stored in
the Assignments table.
• It seems we need to connect the Marks table to the Assignments table. What is
the primary key for the Assignments table? The Course and AssignNum fields.
Once again you need to link two tables by matching the values of two fields.
d) Finally, the course grade shown bottom right. See if you can figure out how that’s
done. You’ll need to create a calculated field in the Marks table, and a calculated
field in the Students table, but nothing more. Details left to the reader…
Password-protected solutions to these questions are in Learn. (Use a blank password.)

(I’ll email the fileMaker file since it doesn’t support to attach it here, download FileMaker from https://www.claris.com/trial/resources.html, sign in as guest)***
Assignment Objectives The idea of a “self-join,” (and how you write a self-join in SQL), which in FileMaker is a relationship in which a single table is linked to itself.
An understanding of how to implement (some) three-table joins in FileMaker.
Be aware, however, that more complicated select statements than we have considered can be far harder to work out than the simple cases we have considered.
The notion of a global field in FileMaker. In many respects, global fields serve as variables in FileMaker. As we’ll see when we discuss scripting in FileMaker, you can create a layout with which users give global fields values, with buttons that execute scripts that use those values while manipulating data.
The notion of creating a value list from the values stored in a given field of some table, and requiring the user to select from that list while entering a value.
———————————————————————————————————————————————————————
———————————————————————————————————————————————————————
Question: (details in attached document)
This question has you create a 3-table join of the Students, Marks and Assignments
tables you will find in the University Starter file. These are slightly simplified versions
of the tables by the same name that you met in the SQL assignment. They contain the
following fields (fields comprising the primary key for each table are shown in bold):
• Download the file UniversityStarter.fmp12 from Learn. Then create a layout in the Students table that looks like the following and behaves like the sample solution to
this question.
Read all instructions before starting.
For a given student, the portal lists the marks for the student in the class selected by
the List Marks for Which Course? popup in the upper right corner. It also shows the
student’s mark in that course, which is computed by multiplying the student’s mark for
each assignment times the weight for that assignment.
3
There are lots of interesting things about this layout.
• How do we specify which course we’re interested in? Which is to say, what is the
List Marks for Which Course? popup connected to?
• One possibility is to create an auxiliary text field (call it WhichCourse), and require
the user to enter a value into it by selecting from a popup menu connected to a
value list whose values are taken from the Course field of the Assignments table.
a) Instead, WhichCourse should be created as a “Global” field. These are “pseudofields” whose values are shared by all records. Only one value is stored in the
table, separate from the records in a table, and that value is seen and shared by all
records in the table. You can still format this as a pop-up. (Be sure to declare that
WhichCourse is of type Text, then click the Options button for WhichCourse and find
the checkbox that makes it a (shared) Global field..) It would be easy to list ALL the marks for a student in the portal — you’d just define a relationship using the IDnumber field as the link. But we want to showin the portal records from the Marks table for a particular IDnumber and Course,so you want to match the values in two fields as you create a one-to-manyrelationship from Students to Marks. b) The course, assignment number, and mark columns of the portal are just fields
from the Marks table, which are readily accessible once you have connected the
Students and Marks table. Place these fields into your portal. c) What about the portal columns containing a textual description of each
assignment and the assignment weight? Those textual descriptions are stored in
the Assignments table.
• It seems we need to connect the Marks table to the Assignments table. What is
the primary key for the Assignments table? The Course and AssignNum fields.
Once again you need to link two tables by matching the values of two fields. d) Finally, the course grade shown bottom right. See if you can figure out how that’s
done. You’ll need to create a calculated field in the Marks table, and a calculated
field in the Students table, but nothing more. Details left to the reader…
Password-protected solutions to these questions are in Learn. (Use a blank password.)

You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on Blackboard via the allocated folder. These files must not be in compressed format.
It is your responsibility to check and make sure that you have uploaded both the correct files.
Zero mark will be given if you try to bypass the SafeAssign (e.g. misspell words, remove spaces between words, hide characters, use different character sets, convert text into image or languages other than English or any kind of manipulation).
Email submission will not be accepted.
You are advised to make your work clear and well-presented. This includes filling your information on the cover page.
You must use this template, failing which will result in zero mark.
You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.
Late submission will result in ZERO mark.
The work should be your own, copying from students or other resources will result in ZERO mark.
Use Times New Roman font for all your answers.
2 Marks
Learning Outcome (1):
Explain the concepts and architectures involved in the database development.
Question One
List the three main types of constraints in the relational model and explain each in your own words. List the Basic operations for changing the database and explain why the DELETE operation may violate only the referential integrity and not the entity constraint.
Answer:
2 Marks
Learning Outcome(4):
Instructors: Create Entity-Relationship model, Relational model, and write SQL queries.
Question Two
Your client, a retail company, has approached you for assistance in optimizing their inventory management system. They need a database schema to track product inventory, suppliers, and orders efficiently. Design an Entity-Relationship (ER) diagram to represent this system, considering the following entities and their attributes:
Product: Attributes may include ProductID, Name, Description, Price, and Quantity.
Supplier: Attributes may include SupplierID, Name, Contact, Address.
Order: Attributes may include OrderID, Date, and TotalAmount.
OrderDetails: This is a relationship entity representing the details of each order, such as the quantity of each product ordered.
Consider the relationships between these entities and ensure that your ER diagram reflects these connections accurately. Your diagram should illustrate how products are supplied by suppliers, how orders are placed, and the details associated with each order.
Answer
2 Marks
Learning Outcome(1):
Instructors: Explain the concepts and architectures involved in the database development.
Question Three
Identify and explain the various types of database users and their roles in a database system. Choose one type of user (e.g., end-users, database administrators, application programmers) and elaborate on the specific tasks and responsibilities associated with that user role. Discuss how the interaction of different user types contributes to a database system’s overall functionality and efficiency. Finally, provide a real-world example or case study where the collaboration of different types of database users played a crucial role in the success or failure of a database project. Ensure your response reflects a deep understanding of the roles and responsibilities of database users.
Answer:
2 Marks
Learning Outcome(5):
Instructors: Design a database starting from the conceptual design to the implementation of database schemas.
Question Four
You are tasked with designing a conceptual database schema for the King Fahad General Hospital. The system needs to store information about Patients, Doctors, Departments, and Medicines. Explain in detail the fundamental database concepts you would consider during the design phase. Discuss the importance of entities, attributes, and relationships. Additionally, outline your proposed database architecture’s potential challenges and benefits, considering factors such as scalability and data integrity.
Answer:

Your boss has a question for you. “We think this is about the right level of granularity for our data mart. What do you think? Should we extract more detailed information, and if so, what? Or would you recommend going to a coarser level of granularity, and if so, what fields would you recommend we drop?” Give your rationale. Think critically, and demonstrate a good understanding of data management.
Attached are the finished files for the assignment and the instruction. Please unzip and load the codes in MySql. My part is Question 4 in Memo Management. Thanks

You are now ready to write the Data Definition Language (DDL) that will be the physical implementation of your database on the Oracle SQL Developer. This includes writing CREATE TABLE statements for each entity of your ERD. Be sure to follow the familiar patterns below for data types, NULL values, and propagating foreign keys under the following rubric for this assignment:
Use variable character data type (“VARCHAR”) as opposed to character in most cases where alpha-numeric values are the norm. Character data type is only appropriate if each value throughout the column will be the same length (such as postal code).
Use INTEGER for all primary key and foreign key columns.
Any column that will have “math”—such as AVG( ) or SUM( ))—will most likely be a numeric or decimal data type. Avoid having INTEGER for any column that will need precise fractions.
Make most columns NOT NULL (we want to avoid allowing “blanks” or empty values into your database). Usually, only description columns are optional.
Code INSERT INTO statements (five rows) for each table.
What to deliver for this assignment?
A single word document with screen shots that captures:
1. the CREATE TABLE statements for all tables. (show all data types, PKs, and FKs). You might also include ALTER TABLE commands if any.
2. the INSERT INTO statements for data population. Entities and Attributes
Case study: General description
The Wellness Clinic is a facility providing medical care in a rural area of the country. Its professional staff consists of five medical doctors (physicians), two nurse-practitioners who provide non-acute care and can prescribe medication, two registered nurses, two midwives who provide pre-natal care and supervise delivery except in cases with complications, a pharmacist, and a medical technician. The non-professional staff members include an office administrator, a receptionist, and a bookkeeper who works part time. The clinic serves several thousand patients, each of whom may visit the clinic any number of times per year, both for preventative care such as checkups or immunizations, and for treatment of illness. Its facilities consist of a waiting room with a reception desk, an administrative office, a nurses’ station, ten examining rooms with adjoining consultation rooms, a small operating room, a birthing room, a recovery room, a pharmacy, and a small laboratory.
Basic Operations
The clinic has regular hours of operation weekdays, Saturday mornings, and two evenings per week. Normally two physicians, one nurse practitioner, one registered nurse, and one midwife are in the clinic during regular hours. In addition, the physicians and nurse-practitioners rotate responsibility for covering emergency calls 24 hours per day, 7 days a week. At the end of each day, the receptionist sets up call forwarding so that emergency calls are automatically directed to the telephone number of the person providing emergency coverage. When the clinic opens in the morning, the call forwarding is halted. Two of the physicians are surgeons who perform routine surgery not requiring general anesthesia at the clinic one morning a week, assisted by a registered nurse. Others have specialties in pediatrics and internal medicine. However, all of the physicians can provide general and acute care for any of the patients. Patients who require major surgery or other hospital care must go to a hospital located outside the immediate area served by the clinic. The clinic physicians do not normally visit their patients who are in the hospital, instead leaving their care to the hospital staff with whom they communicate during the hospitalization. However, the clinic provides both pre- and post-hospital care for the patients.
Hours of operation are divided into scheduled appointments and unscheduled hours, which are open for walk-ins. Patients usually schedule checkups and immunizations well in advance. Patients suffering from chronic or acute illness can usually schedule appointments promptly, or they may come in during the unscheduled hours. The administrator is responsible for setting up all schedules, and for keeping records updated. Prior to the beginning of each month, the administrator makes up complete coverage schedules for all professional and support staff. The bookkeeper is responsible for doing all billing and recording payments. The receptionist is responsible for making patient appointments, for handling traffic, and for making the patient’s medical records available during the visit. The nurse prepares the patient, takes medical history, performs some medical routines or tests, takes samples for lab tests, updates the medical record, and assists the practitioner (the physician, nurse practitioner, or midwife) during the visit. The practitioner examines the patient, administers medical treatment, can perform some tests, can also take samples for lab tests, and writes prescriptions for medications or orders for additional lab tests during a visit. Each visit results in one or more diagnoses, which the practitioner adds to the patient’s medical record, along with any comments or observations. Prescriptions can be filled at the clinic’s pharmacy or they can be sent to another pharmacy at the patient’s request. Some laboratory tests are performed at the clinic by the medical technician, using samples taken by one of the professionals. More specialized tests are performed at an outside medical laboratory. Whenever possible, specimens, such as blood samples, are taken at the clinic by one of the professionals there and sent to the laboratory. If the lab test requires the presence of the patient and equipment that is not available at the clinic, the patient is sent to the outside laboratory for the test, and results are sent back to the clinic.
Medical care is provided for all patients, regardless of their ability to pay. Bills are generated based on the services provided, not on the payment method. Private patients who can afford to pay out of pocket can do so at the time of service or be billed at the end of each month. Those who have medical insurance provide information about their insurance policies, and the insurance companies are billed. Usually in that case patients pay a small amount of co-insurance (co-pay), which is determined by the type of policy they hold, at the time of the visit. Those who cannot afford to pay normally have government-provided health care, for which they have a government-issued medical card. They pay nothing and the clinic is reimbursed by the government for the entire cost of the visit, including any lab tests performed and medications dispensed there. A small number of indigent patients who do not have health coverage are treated and the cost is absorbed by the clinic until they qualify for government-provided coverage.
Information Needs
Currently all information about patients and their care is kept manually, and billing information is kept on a spreadsheet. Physicians use computer or telephone communications to provide information to the hospital and receive information about patients who need hospital care. The clinic has computer access to hospital records for its patients, as well as on-line systems provided by insurance companies and the government for third-party billing. It needs a database that keeps track of all the patient-related activities of the clinic and to provide information about billing and payments. The database will not keep track of medical supplies, plant maintenance, or payroll information.
The following forms or reports are needed.
Patient Intake Form This form is filled out by patients prior to or during their initial visits. It contains contact information, medical history, list of current medications, and insurance information. The data is updated for each visit.
Weekly Coverage Schedule This schedule lists the daily hours and the professional and non-professional staff who are scheduled to be in the clinic at specific times each day of the week. It also lists the name and telephone number of the person who is covering for emergencies during all hours each week. (Recall that the administrator provides the coverage information each month.)
Daily Master Schedule This is a master schedule for all practitioners for each day. It should list each of the practitioners who are in that day, with all appointments scheduled for them. Most appointments are allocated 10 minutes each, so each hour has 6 timeslots. However, some appointments are given more than one timeslot, depending on the nature of the care needed. Each professional has hours dedicated to walk-ins during which no pre-scheduled appointments are made. As walk-ins sign in for care, the receptionist assigns them to a practitioner (a physician, nurse-practitioner, or midwife) and adds the patient’s name to the schedule. The registered nurses do not have appointments scheduled, and are available to assist the practitioners with visits, or to administer tests or take samples on an unscheduled basis. The lab technician also does not have an appointment schedule.
Individual Practitioner’s Daily Schedule Each of the practitioners should receive a printed copy of his or her own schedule for any day he or she is in the clinic. Appointments list the patient’s name and the reason given for the visit. The copy is updated manually by the receptionist as visits for walk-ins are conducted.
Provider’s Statement for Insurance Forms This is a pre-printed form that is used as a receipt primarily for insurance purposes. It lists the clinic name, address, and telephone number, along with the names and tax identification numbers of all the practitioners on the staff. It also lists all the types of visits, the procedures that can be performed, with a code for each, and some blank lines for “other”, along with a line for entering the fee for each. It also has a list of the common diagnoses and codes, with a few blank lines for “other”. At the bottom are lines for Total Charge, Amount Paid, and Balance Due. The provider uses this form during a visit, to record visit type, procedures performed, and diagnosis. When the patient checks out after the visit, the receptionist checks off a box showing which practitioner provided services, fills in the fee for each service using a fee schedule, calculates the total, and writes in the amount paid, if any, and the Balance Due. One copy is kept by the clinic and another is given to the patient. The same information is submitted electronically to the private or government insurer.
Patient Monthly Statement Any patient who has an unpaid balance receives a statement that is compiled at the end of each month, listing all the services provided that month, any payments received, and the balance due.
Precription Label and Receipt This form consists of two parts. The top part is gummed and used as a label for the container in which medication is dispensed. The label shows the Rx Number, Doctor Name, Patient Name, Patient Address, Directions, Drug Name, Form, Strength, Quantity, Pharmacist’s Name, Date Filled, Original Date, and Number of Refills Remaining. The bottom part repeats the information on the label, and also lists the total price of the medication, the amount covered by insurance or the government, and the balance due from the patient, as well as more information about the drug, complete directions for use, and warnings about possible side effects and drug interactions. The receipt can be used for submitting claims for insurance coverage. This information is also submitted electronically to the private or government insurer.
Daily Laboratory Log This log is used to record all lab tests performed each day.
Operating Room Schedule This schedule provides information about all scheduled surgeries for the day.
Operating Room Log This records information about the surgeries actually performed on a given day, including identification of the patient, surgeon, and nurse, and notations and observations about the surgery.
Daily Delivery Room Log This records information about all the deliveries performed each day.
Recovery Room Log This report records information about the use of the recovery room, including the patient’s name, attending practitioner, bed, date in, time in, date out, time out, and signature of the practitioner who signs the patient out. A nurse records the times and results of any medical checks performed while the patient is in recovery.
Monthly Activity Report This is an internal report summarizing the clinic’s activity each month. It shows such items as the number of visits conducted by each provider, the number of surgeries performed, the number of deliveries, the number of lab tests broken down by type, the number of prescriptions dispensed, the average time per visit, and so on
PatientPatientID (PK)
PractitionerPractitionerID (PK)
PractitionerType
PractitionerName
AdministratorAdminID (PK)
AdminDetails
ReceptionistReceptionistID (PK)
ReceptionistDetails
BookkeeperBookkeeperID (PK)
BookkeeperDetails
Medical TechnicianTechnicianID (PK)
TechnicianDetails
PharmacistPharmacistID (PK)
PharmacistDetails
Medical RecordRecordID (PK)
PatientID (FK)
AppointmentAppointmentID (PK)
PatientID (FK)
VisitVisitID (PK)
PatientID (FK)
PrescriptionPrescriptionID (PK)
VisitID (FK)
PatientID (FK)
Laboratory TestLabTestID (PK)
VisitID (FK)
SurgerySurgeryID (PK)
DeliveryDeliveryID (PK)
Recovery RoomRoomID (PK)
BillingBillingID (PK)
VisitID (FK)
PatientID (FK)
…
Insurance CompanyInsuranceID (PK)
GovernmentGovernmentID (PK)
Relationships:
Patient-Appointment: One-to-Many
Practitioner-Appointment: One-to-Many
Practitioner-Visit: One-to-Many
Patient-Visit: One-to-Many
Visit-Medical Record: One-to-One
Visit-Prescription: One-to-Many
Visit-Laboratory Test: One-to-Many
Visit-Billing: One-to-One
Patient-Billing: One-to-Many
Prescription-Billing: One-to-One
Pharmacy-Prescription: One-to-Many
Surgery-Practitioner: Many-to-Many
Surgery-Patient: Many-to-One
Delivery-Practitioner: Many-to-One
Delivery-Patient: Many-to-One
Relationships:
Staff – Coverage Schedule (One to Many).
Patient – Appointment (One to Many).
Patient – Patient Monthly Statement (One to Many).
Practitioner – Provider’s Statement (One to Many).
Professional Staff – Appointment (One to Many).
Operation of Visits:
1.Appointment Scheduling: The first step involves a patient calling in to schedule a visit.
2.Conducting Visits: The visits are conducted by professional staff and each visit is associated to a patient and a staff member.
3.Room Allocation: Visits may use one or more rooms for consultations, examinations, or procedures.
4.Referrals: Visits may result in referrals to other healthcare providers, recorded within the Visit table or a separate Referral table.
5.Charges and Payments: Visits incur charges for services provided, recorded in tables such as Billing or Payments, including details of services rendered, costs, and payment status.
6.Prescriptions and Diagnostics: A visit may result in prescriptions for drugs or lab work, which are recorded in the Prescription table together with any diagnostic procedures and tests carried out during the appointment.
Submission Type : Word document & oracle code no plagarism

Hello, I am working on a MySql lab code. There are 22 tasks on it. It is done on a website called GitHub. I will attach the link and how to access it once accepted. The instructions on what to do is also in the link I will send. Please make sure to read the instructions on the website in the companion. If possible, please provide a brief explanation as well. Below is an overview of what the tasks will be:
Task 1: List the item ID, description, and price for all items.
Task 2: List all rows and columns for the complete INVOICES table.
Task 3: List the first name, last name, and credit limit of customers with credit limits of $1,000 or more.
Task 4: List the invoice number for each order placed by customer number 125 on 2021-11-15.
If you need help, use the discussion of the DATE data type in Figure 3-16 in Module 3.
Task 5: List the customer ID, the first name, and the last name of each customer represented by sales rep 10 or sales rep 15.
Task 6: List the item ID and description of each item that is not in category HOR.
Task 7: List the item ID, description, and number of units on hand for each item that has between 10 and 30 units on hand, including both 10 and 30.
Task 8: List the item ID, description, and on-hand value (units on hand * unit price) of each item in category CAT. (On-hand value is technically units on hand * cost, but there is no COST column in the ITEM table). Assign the name ON_HAND_VALUE to the computed column.
Task 9: List the item ID, description, and on-hand value for each item where on-hand value is at least $1,500. Assign the name ON_HAND_VALUE to the computed column.
Task 10: Use the IN operator to list the item ID and description of each item in category FSH or BRD.
Task 11: Find the ID, first name, and last name of each customer whose first name begins with the letter S.
Task 12: List all details about all items. Order the output by description.
Task 13: List all details about all items. Order the output by item ID within location. (That is, order the output by location and then by item ID.)
Task 14: How many customers have balances that are more than their credit limits?
Task 15: Find the total of the balances for all customers represented by sales rep 10 with balances that are less than their credit limits
Task 16: List the item ID, description, and on-hand value of each item whose number of units on hand is more than the average number of units on hand for all items.
Use a sub-query.
Task 17: What is the price of the least expensive item in the database?
Task 18: What is the item ID, description, and price of the least expensive item in the database?
Use a sub-query.
Task 19: List the REP_ID and the corresponding sum of the balances, as BALANCE, of all customers for each sales rep. Order and group the results by sales rep ID.
Task 20: List the sum of the balances of all customers for each sales rep but restrict the output to those sales reps for which the sum is more than $150.
The results should display the REP_ID and BALANCE column headers. Order the results by sales REP_ID.
Task 21: List the item ID, description, and category of all items that are in the DOG or CAT category and contain the word Small in the description
Task 22: KimTay Pet Supplies is considering discounting the price of all items by 10 percent. List the item ID, description, price, and discounted price for all items. Use DISCOUNTED_PRICE as the name for the computed column.

Hello, I am working on a MySql lab assignment on GitHub. There are 22 tasks to it. It is a database assignment
Task 1: List the item ID, description, and price for all items.
Task 2: List all rows and columns for the complete INVOICES table.
Task 3: List the first name, last name, and credit limit of customers with credit limits of $1,000 or more.
Task 4: List the invoice number for each order placed by customer number 125 on 2021-11-15.
If you need help, use the discussion of the DATE data type in Figure 3-16 in Module 3.
Task 5: List the customer ID, the first name, and the last name of each customer represented by sales rep 10 or sales rep 15.
Task 6: List the item ID and description of each item that is not in category HOR.
Task 7: List the item ID, description, and number of units on hand for each item that has between 10 and 30 units on hand, including both 10 and 30.
Task 8: List the item ID, description, and on-hand value (units on hand * unit price) of each item in category CAT. (On-hand value is technically units on hand * cost, but there is no COST column in the ITEM table). Assign the name ON_HAND_VALUE to the computed column.
Task 9: List the item ID, description, and on-hand value for each item where on-hand value is at least $1,500. Assign the name ON_HAND_VALUE to the computed column.
Task 10: Use the IN operator to list the item ID and description of each item in category FSH or BRD.
Task 11: Find the ID, first name, and last name of each customer whose first name begins with the letter S.
Task 12: List all details about all items. Order the output by description.
Task 13: List all details about all items. Order the output by item ID within location. (That is, order the output by location and then by item ID.)
Task 14: How many customers have balances that are more than their credit limits?
Task 15: Find the total of the balances for all customers represented by sales rep 10 with balances that are less than their credit limits
Task 16: List the item ID, description, and on-hand value of each item whose number of units on hand is more than the average number of units on hand for all items.
Use a sub-query.
Task 17: What is the price of the least expensive item in the database?
Task 18: What is the item ID, description, and price of the least expensive item in the database?
Use a sub-query.
Task 19: List the REP_ID and the corresponding sum of the balances, as BALANCE, of all customers for each sales rep. Order and group the results by sales rep ID.
Task 20: List the sum of the balances of all customers for each sales rep but restrict the output to those sales reps for which the sum is more than $150.
The results should display the REP_ID and BALANCE column headers. Order the results by sales REP_ID.
Task 21: List the item ID, description, and category of all items that are in the DOG or CAT category and contain the word Small in the description

Q1) Write a SQL query to fetch all the duplicate records from applicants table.
/**Tables Structure:**/
drop table applicants;
create table applicants
(
user_id int primary key,
user_name varchar(30) not null,
email varchar(50));
insert into users values
(1, ‘pearson’, ‘pearson@gmail.com’),
(2, ‘Reshma’, ‘reshma@gmail.com’),
(3, ‘Farhana’, ‘farhana@gmail.com’),
(4, ‘Robin’, ‘robin@gmail.com’),
(5, ‘Robin’, ‘robin@gmail.com’);
select * from applicants;
Q2) Create a SQL query to retrieve the employee table’s second-to-last record.
–Tables Structure:
drop table employee;
create table employee
( emp_ID int primary key
, emp_NAME varchar(50) not null
, DEPT_NAME varchar(50)
, SALARY int);
insert into employee values(101, ‘Mohan’, ‘Admin’, 4000);
insert into employee values(102, ‘Rajkumar’, ‘HR’, 3000);
insert into employee values(103, ‘Akbar’, ‘IT’, 4000);
insert into employee values(104, ‘Dorvin’, ‘Finance’, 6500);
insert into employee values(105, ‘Rohit’, ‘HR’, 3000);
insert into employee values(106, ‘Rajesh’,’Finance’, 5000);
insert into employee values(107, ‘Preet’, ‘HR’, 7000);
insert into employee values(108, ‘Maryam’, ‘Admin’, 4000);
insert into employee values(109, ‘Sanjay’, ‘IT’, 6500);
insert into employee values(110, ‘Vasudha’, ‘IT’, 7000);
insert into employee values(111, ‘Melinda’, ‘IT’, 8000);
insert into employee values(112, ‘Komal’, ‘IT’, 10000);
insert into employee values(113, ‘Gautham’, ‘Admin’, 2000);
insert into employee values(114, ‘Manisha’, ‘HR’, 3000);
insert into employee values(115, ‘Chandni’, ‘IT’, 4500);
insert into employee values(116, ‘Satya’, ‘Finance’, 6500);
insert into employee values(117, ‘Adarsh’, ‘HR’, 3500);
insert into employee values(118, ‘Tejaswi’, ‘Finance’, 5500);
insert into employee values(119, ‘Cory’, ‘HR’, 8000);
insert into employee values(120, ‘Monica’, ‘Admin’, 5000);
insert into employee values(121, ‘Rosalin’, ‘IT’, 6000);
insert into employee values(122, ‘Ibrahim’, ‘IT’, 8000);
insert into employee values(123, ‘Vikram’, ‘IT’, 8000);
insert into employee values(124, ‘Dheeraj’, ‘IT’, 11000);
select * from employee;
Required Output: Vikram
Create a SQL query to only show the employee table’s information for those with the only highest or lowest salaries across all departments.
–Tables Structure:
drop table employee;
create table employee
( emp_ID int primary key
, emp_NAME varchar(50) not null
, DEPT_NAME varchar(50)
, SALARY int);
insert into employee values(101, ‘Mohan’, ‘Admin’, 4000);
insert into employee values(102, ‘Rajkumar’, ‘HR’, 3000);
insert into employee values(103, ‘Akbar’, ‘IT’, 4000);
insert into employee values(104, ‘Dorvin’, ‘Finance’, 6500);
insert into employee values(105, ‘Rohit’, ‘HR’, 3000);
insert into employee values(106, ‘Rajesh’,’Finance’, 5000);
insert into employee values(107, ‘Preet’, ‘HR’, 7000);
insert into employee values(108, ‘Maryam’, ‘Admin’, 4000);
insert into employee values(109, ‘Sanjay’, ‘IT’, 6500);
insert into employee values(110, ‘Vasudha’, ‘IT’, 7000);
insert into employee values(111, ‘Melinda’, ‘IT’, 8000);
insert into employee values(112, ‘Komal’, ‘IT’, 10000);
insert into employee values(113, ‘Gautham’, ‘Admin’, 2000);
insert into employee values(114, ‘Manisha’, ‘HR’, 3000);
insert into employee values(115, ‘Chandni’, ‘IT’, 4500);
insert into employee values(116, ‘Satya’, ‘Finance’, 6500);
insert into employee values(117, ‘Adarsh’, ‘HR’, 3500);
insert into employee values(118, ‘Tejaswi’, ‘Finance’, 5500);
insert into employee values(119, ‘Cory’, ‘HR’, 8000);
insert into employee values(120, ‘Monica’, ‘Admin’, 5000);
insert into employee values(121, ‘Rosalin’, ‘IT’, 6000);
insert into employee values(122, ‘Ibrahim’, ‘IT’, 8000);
insert into employee values(123, ‘Vikram’, ‘IT’, 8000);
insert into employee values(124, ‘Dheeraj’, ‘IT’, 11000);
select * from employee;
Ex: Output: In Admit department
# emp_ID,emp_NAME,DEPT_NAME,SALARY ,max_salary,min_salary
113GauthamAdmin200050002000
120Monica Admin500050002000
Create a SQL query from the students table to swap the adjacent student names.
Note: The student name should remain the same if there are no adjacent students.
–Table Structure:
drop table students;
create table students
(
id int primary key,
student_name varchar(50) not null
);
insert into students values
(1, ‘James’),
(2, ‘Michael’),
(3, ‘George’),
(4, ‘Stewart’),
(5, ‘Robin’);
select * from students;
Q7) Get all the instances where Alaska experienced extremely low temperatures for three or more straight days from the weather table.
Note: When the weather is below zero, it is deemed to be extremely cold.
–Table Structure:
drop table weather;
create table weather
(
id int,
city varchar(50),
temperature int,
day date
);
delete from weather;
insert into weather values
(1, ‘Alaska’, -1, to_date(‘2021-01-01′,’yyyy-mm-dd’)),
(2, ‘Alaska’, -2, to_date(‘2021-01-02′,’yyyy-mm-dd’)),
(3, ‘Alaska’, 4, to_date(‘2021-01-03′,’yyyy-mm-dd’)),
(4, ‘Alaska’, 1, to_date(‘2021-01-04′,’yyyy-mm-dd’)),
(5, ‘Alaska’, -2, to_date(‘2021-01-05′,’yyyy-mm-dd’)),
(6, ‘Alaska’, -5, to_date(‘2021-01-06′,’yyyy-mm-dd’)),
(7, ‘Alaska’, -7, to_date(‘2021-01-07′,’yyyy-mm-dd’)),
(8, ‘Alaska’, 5, to_date(‘2021-01-08′,’yyyy-mm-dd’));
select * from weather;

Download a dataset of size 300 MB or more and then solve the following programming questions using
Spark ML library.
a. a classification problem using KNN algorithm.
b. a regression problem using KNN algorithm.
c. a clustering problem using K-means algorithm.
Deliverables
A WORD document which contains the following
o Your solution to the classification problem.
o Your solution to the regression problem.
o Your solution to the clustering problem.
All solutions should have screenshot of code with description of each step.

Using the table of transactions (table attached) generate all the frequent itemsets using Apriori algorithm and then generate the interesting association rules. Also show which of the interesting rules are positively correlated. Assume minsup >=
0.3 and minimum confidence >= 0.4