Assessment item 5 back to top Query and update databases using SQL Value: 15% Due Date: 02-Oct-2018 Return Date: 23-Oct-2018 Length: Submission method options: EASTS (online) Task back to top NOTES...

Assessment item 5

back to top

Query and update databases using SQL

Value:15%Due Date:02-Oct-2018Return Date:23-Oct-2018Length:
Submission method options:EASTS (online)

Task

back to top

NOTES

• This assignmentrequires you to use SQLite to complete the tasks listed below.


• For guidance on how to install SQLite, watch this short YouTube video:https://www.youtube.com/watch?v=9ynCDVg54H0
  


• For guidance on how to create tables using SQLite, watch this short YouTube video:https://www.youtube.com/watch?v=kij5lPw0F-o
  


• Typing the SQL statements or the results is NOT acceptable.

Instructions

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

Copy sqlite3.exe file from theAssignment 5 Folder into a folder in your machineCopy a5.txt from the Assignment 5 Folder into the folder in your machine where your SQLite file is locatedFrom the SQLite Command Promptexecute the a5.txt script. To do this,at the SQLite Command Line type .read a5.txt [to create the tables you will use to answer this assignment’s questions]At the SQLite Command Line type .headers onAt the SQLite Command Line type .mode columnWrite and run SQL statements to complete the following tasks

1. Show the details of the vendors who are located in area code 615.


2. Show the details of the products that do not have a value for the attribute v_code.


3. Show the details of the invoices whose subtotal is greater than 25 but less than 75.


4. Show the details of the invoice who has the minimum subtotal.


5. Show the codes and names of the vendors who supplied products.


6. Using EXCEPT show the codes of the vendors who did not supply any products.


7. Using ‘NOT IN’ show the codes and names of the vendors who did not supply any products.


8. List the codes of vendors and the number of products each vendor has supplied, i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc.


9. List the names andcodes of vendors and the number of products each vendor has supplied, i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc.


10. Add a new attribute (field) status varchar(6) to the EMP table.


11. Update status for employee '100' to 'Temporary'.


12. Using inner join, list the details of the products whose line price is greater than 100.

You are required to submit:1. The SQL statements for each query, which should be copied from your SQLite3 Command Line and pasted into your submission file. If you type .echo on at the SQLite3 Command Line, SQLite will output your SQL statement with the results of the query making it easy to copy and paste both.2. The screenshots of the results of the SQL statements which are to be copied from your SQLite3 Command Line and pasted into your submission file immediately after you execute the SQL query.If you used an output file please submit only the output file.

Rationale

back to top

This assessment task will assess the following learning outcomes:

• be able to implement a database design using Structured Query Language (SQL).


• be able to query a database using SQL.

Marking criteria and standards

back to top

Each task is worth 1.25 marks. The marking criteria for this assignment is:

Criteria	HD	DI	CR	PS
be able to implement a database design using Structured Query Language (SQL). be able to query a database using SQL.	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.	SQL syntax returns all attributes that meet the business requirementfrom the tables that hold the data with minor omissions and applies appropriate restrictions to retrieve the required information.	SQL syntax returnskey attributes that meet the business requirementfrom the tables that hold the data and applies relevant restrictionsto retrieve the required information.	SQL syntax returnskey attributes that meet the business requirement from the tables that hold the data butthe restrictions applied to retrieve the required information containminor errors.

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.