use the eer diagram to create the 4 queries ISYS224/ITEC624 2018-Assignmest Task

Question

use the eer diagram to create the 4 queries

ISYS224/ITEC624 2018-Assignmest Task 3: DDL Scripts (5 marks) Create the DDL seripts to implement the relevant tables from Task in MySQL. You can choose to do this either through MySQL Workbench or manually using a text editor. Task 4: DML Scripts (5 marks) Insert records into each of your tables (there should be a minimum of 3 records per table). When inserting records into the tables, make sure that the result of each SQL query (for Task 5 given below) will return at least one record. Take snapshots of tables populated with data and add it to the word document Task 5: DQL Scripts (20 marks) Provide SOL statements for the followingqueries. Note that these SQl. queries must be tested using the data you inserted in Task 4 Take snapshots of tables of the query execution (query and result) and add it to the word document a) Find the total number of accounts that are co-owned Dolnt bank accounts) b) List all accounts that have a Personal' loan linked to it (you need to a have a loan type called Personal' to test this query) c) List the name of every department at each of the branches, along with the manager-name for that department d) List the names of managers who make more than the average manager salary Report To present your answers to these tasks, please use the templates provided in the assignment folder. Fill out the details provided on the Erst page of the template. For each task, remove the comments and add your answer in Snapshots for each of the queries tasks 4&5 must be placed in your word documest Assumptions. If you have any assumptions, please list them down in the last page of the report .If your images lack clarity, you will not be given any marks. Tutors can zoom in to check the diagram But we will not be able to increase the quality of the image, Whatever is submitted is the final submission. So, please make sure your image is readable Pdf your document. File-> Save as-> Choose pdf format as file type Submission

Explanation / Answer

a.

select count(*)
from Account A, AccountType T
where A.AccountTypeID = T.AccountTypeID
and accTypeName = "Joint"
group by accTypeName;

b.

select accountID
from Account A, Loan L, LoanType T
where A.AccountID = L.AccountID
and L.LoanTypeID = T.LoanTypeID
and T.loanName = "Personal";

c.

select D.departmentName, E.employeeName
from Department D, Branch B, Employee E
where B.BranchID = Branch_BranchID
and E.BranchID = Branch_BranchID;

d.

select employeeName
from Employee
where annualSalary >
(select avg(annualSalary)
from Employee);

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.