Reference no: EM131066575
Assessment: Query and update databases using SQL
Task
NOTES
• This assignment encourages you to use SQLite to complete the tasks listed below.
• The instructions listed below relate to SQLite.
• For guidance on how to install SQLite, watch this short YouTube video: https://www.youtube.com/watch?v=YZQuQ6jael8
• For guidance on how to create tables using SQLite, watch this short YouTube video:https://www.youtube.com/watch?v=Ev2JnjavvmE
• You are welcome to use MySQL. The instructions for using MySQL will be placed in the Students Resources Folder.
• To use other packages, please seek the approval of your subject coordinator.
• Typing the SQL statements or the results is NOT acceptable.
Instructions
From Resources under Main Menu in Interact 2 go to Students Resources Folder
Copy sqlite3.exe and cmd.exe files from the Students Resources Folder into a folder in your machine
Copy a5.txt from the Students Resources Folder into the folder in your machine where your SQLite file is located
Open a5.txt in Notepad
Replace yourstudentid in line .output yourstudentid.txt in a5.txt with your actual studentid i.e. for example 45446667.txt
From the Command Prompt type sqlite3.exe yourstudentid.db
Note: From the Command Prompt call the folder in your machine where your SQLite file is located before you type sqlite3.exe yourstudentid.db
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 .quit
Using WordPad open yourstudentid.txt [to check the tables you created exist]
From the Command Prompt type sqlite3
At the SQLite Command Line type .open yourstudentid.db
At the SQLite Command Line type .headers on
At the SQLite Command Line type .mode column
Write and run SQL statements to complete the following tasks
1. Show all the details of the products by v_code 21344
2. Locate the record in the vendor table that does not have a value for the attribute V_STATE
3. Find the customers whose balance is greater than 200 but less than 300.
4. What is the max balance?
5. Show the names of the customers for whom we have invoices.
6. Show the CUS_CODE of the customers for whom we DO NOT have invoices.
7. Show the names of the customers for whom we DO NOT have invoices.
8. Find out how many times a customer generated an invoice.
9. List the names of the customers and how many times a customer generated an invoice.
10. Add a new attribute (field) credithistory varchar(6) to the customer table.
11. Update credithistory for customer '10011' to 'Bad'.
12. Delete the employee Leighla Genkazi from the EMP table.
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
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
|
Implicit borrowing rate being paid by customers
: A store will give you a 3% discount on the cost of your purchase if you pay cash today. Otherwise, you will be billed the full price with payment due in 1 month. What is the implicit borrowing rate being paid by customers who choose to defer payme..
|
|
Closest to the present worth of the machine
: Machine C has an initial cost of $40,000, maintenance costs of $5,000 per year and a salvage value of $12,000 after its 3-year useful life. Assuming an interest rate of 10% per year, which of the following would be closest to the present worth of the..
|
|
What are some long-term options of financing
: What are some long-term options of financing? Pick one option and defend it above the rest. Be sure to comment on short-term and long-term implications to the company’s earning and balance sheet.
|
|
Implementing the new coding structure
: Why do you think the USA has been slow in implementing the new coding structure? What are some of the costs that have been encountered by facilities as they upgrade?
|
|
Find out how many times a customer generated an invoice
: Find out how many times a customer generated an invoice. List the names of the customers and how many times a customer generated an invoice.
|
|
Portfolio of the two assets
: What is the expected return on a portfolio that is equally invested in the two assets? If a portfolio of the two assets has a beta of 0.8, what are the portfolio weights?
|
|
Show that the best linear unbiased estimator of
: Suppose that X1, X2, . . .is a sequence of random variables with E(Xt2) 8 and E(Xt)=µ. a. If X1, X2, . . .is iid with E(Xi2) 8 and EXi=µ, where µis known, whatis the minimum mean squared error predictor of Xn+1in terms of X1, . . . , Xn?
|
|
Write a summary for cost management
: Write a summary for Cost Management.
|
|
A positive integer valued random variable
: A positive integer valued random variable N is a stopping time with respect to the discrete time stochastic process {X1, X2, · · · } if for every n, the event {N = n} is determined by the values of {X1, X2, · · · , Xn}.
|