Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

back to top Research and SQL Queries Value: 20% Due Date: 26-May-2019 Return Date: 19-Jun-2019 Submission method options: Alternative submission method Task back to top This Assessment comprises two...

1 answer below »

back to top

Research and SQL Queries

Value:20%Due Date:26-May-2019Return Date:19-Jun-2019Submission method options:Alternative submission method

Task

back to top

This Assessment comprises two parts. Please refer to the submission instructions section for submission of each part.


Assessment Part A: (10 MARKS)


From Resources under Main Menu in Interact 2 go to Assignment 5 Folder


NOTES


  • This assignment requires you to use SQL Workbench to complete the tasks.
    Instruction sheet on how to install SQL Workbench will be placed under ‘student resources’ on Interact2.
  • You will need to download and use the file ‘Assignment5’, from Interact2 Student Resources in the ‘Assignment 5 - SQL DB’ folder.
  • Queries need to have the correct syntax and must show the results after execution:
    Typing the SQL statements or the results is NOT acceptable.



Write and run SQL statements to complete the following tasks


  1. List the names and codes of vendors and the number of products each vendor has supplied, i.e. vendor AA has supplied xxx products, and vendor BB has supplied yyy products etc.
  2. Show the total value of invoices for each customer with the customer the Last Name, and a column heading showing ‘Total_invoice_value’ in the results.
  3. List the names of the customers and how many times a customer generated an invoice: make sure the new column heading reads “Num_Of_Invoices”.
  4. Through a calculation, show the Total unit price (aliased column) for the units and line price.
  5. Show the details of the invoice that has the minimum subtotal.
  6. List all the purchases made by customer Orlando. (use a natural join).
  7. Add a new vendor row/record in the vendor table and use your teacher’s name and your choice of other details. (1m)
  8. Add a new row/record to the `customer` table to include your actual student_ID in the CUS_CODE, your last_name as the CUS_LNAME` and your first name as CUS_FNAME. Add any other details of your choice in all the other fields.
  9. Add a new attribute (field) ‘Customer_rating’ with data type and length ‘VARCHAR(14) to the vendor table.
  10. Update ‘Customer_rating’ for the Customer that has ‘your student ID and name’ from point 2, to show ‘GREAT’.

Assessment Part B (approx XXXXXXXXXXwords): 10 MARKS


Note: you are expected to conduct research about current and future Database technologies by answering the below question:


Research Question:List and discuss thefour basic categories of NoSQL databases: outline the differences between these categories and give examples where these are used.


Note: Use proper references in the APA style.


Submission Instructions:


You are required to submit the following file:


AWord documentwith 2 parts –


Part A:the SQL statements for each query (should be copied from your Workbench software into your Word assignment file): The screenshots of the results of the SQL statements copied and pasted into your submission file immediately after you execute the SQL query and under the SQL statements for the question.


Part B:Your researched answer in about XXXXXXXXXXwordson NoSQL databasetechnologies.

Rationale

back to top

This assessment task will assess the following learning outcome/s:

  • be able to use complex SQL commands to query a database.
  • be able to research a recent development in the field of database technology.

Marking criteria and standards

back to top

The marking criteria for this assignment is:

Criteria

HD

DI

CR

PS

PART A: be able

to use complex

SQL commands

to query a

database.


PART B: be able

to research a

recent

development in

the field of

database

technology.

PART A:SQL

syntax returns

all attributes

that meet the

business

requirement

from the tables

that hold the

data and applies

the correct

restrictions to

retrieve the

required

information.


PART B:

Demonstrates

an excellent

ability at

researching

recent

developments

in the field of

database

technology with

all the

responses

supported by

examples and

use correct

terminology.

PART A:SQL

syntax returns

all attributes

that meet the

business

requirement

from the tables

that hold the

data with minor

omissions and

applies

appropriate

restrictions to

retrieve the

required

information.


PART B:

Demonstrates a

good ability at

researching

recent

developments

in the field of

database

technology with

most responses

supported by

examples and

use correct

terminology.

PART A:SQL

syntax returns

key attributes

that meet the

business

requirement

from the tables

that hold the

data and applies

relevant

restrictions.


PART B: Makes a

genuine attempt

at researching

recent

developments

in the field of

database

technology with

some responses

supported by

examples and

use correct

terminology.

PART A:SQL

syntax returns

key attributes

that meet the

business

requirement

from the tables

that hold the

data. However,

the restrictions

applied

to retrieve the

required

information

contain minor

errors.


PART B: Limited

knowledge of

the

implementation

considerations

and a basic

explanation of

the security

issues.

Requirements

back to top

Either submit your assignmentasa PDFora Word document.

Don't use any other file formats.

Please include your student ID, name and subject code in your submission.

Answered Same Day May 24, 2021

Solution

Shikha answered on May 26 2021
149 Votes
Student Name
Student ID        7
Database Implementation
Submitted By
Course
Professo
Date
PART – A
SQL Statements
1. List the names and codes of vendors and the number of products each vendor has supplied, i.e. vendor AA has supplied xxx products, and vendor BB has supplied yyy products etc.
Select vendor.v_code, vendor.v_name, count(Product.v_code) from vendor, Product
Where Product.v_code=Vendor.v_code
Group by Product.v_code, vendor.v_name;
2. Show the total value of invoices for each customer with the customer the Last Name, and a column heading showing ‘Total_invoice_value’ in the results.
Select Customer.Cus_lname, Sum(Invoice.inv_total) As Total_invoice_value
From invoice, customer where
Invoice.Cus_code=Customer.Cus_code
Group by Invoice.Cus_code, Customer.Cus_lname;
3. List the names of the customers and how many times a customer generated an invoice: make sure the new column heading reads “Num_Of_Invoices”.
Select Customer.Cus_FName, Customer.Cus_lname, Count(Invoice.inv_Number) As Num_of_Invoices
From invoice, customer where
Invoice.Cus_code=Customer.Cus_code
Group by Invoice.Cus_code, Customer.Cus_Fname, Customer.Cus_lname;
4. Through a calculation, show the Total unit price (aliased column) for the units and line price.
Select (Sum(Line.Line_Price)/ Sum(Line.Line_units)) as Total_Unit_Price, Sum(Line_Price) as Total_Line_Price from Line;
5. Show the details of the invoice that has the minimum subtotal.
Select Inv_number, Cus_code, Inv_date, Min(Inv_subtotal), Inv_tax,Inv_total from invoice;
6. List all the purchases made by customer Orlando. (use a natural join).
Select Invoice.Cus_code, Customer.Cus_Lname, Customer.Cus_Fname, Invoice.Inv_number,
Invoice.Inv_date, Invoice.Inv_Subtotal, Invoice.Inv_tax, Invoice.Inv_total
From Customer Natural Join Invoice where Customer.Cus_Lname='Orlando';
7. Add a new vendor row
ecord in the vendor table and use your teacher’s name and your choice of other details. (1m)
INSERT INTO VENDOR VALUES('25600','Sanskriti','Park...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Attach File