In order to meet all the Assessment Criteria:1. Complete task A27 Parameter Query2. Provide at least...

Question

In order to meet all the Assessment Criteria:1. Complete task A27 Parameter Query2. Provide at least 5 citations and a reference list (e.g. your excellent research on LEFT JOIN).3. Provide explanations of your choice of SQL for at least 5 more queries.Accountancy – An Introduction to Accounts CBA890 – SQL Assignment Using SQL Assignment SQL Assignment CBA890 There are three tasks. Task A is compulsory and must be answered in full in order to pass. Task B is optional and for those who wish to impress with their SQL skills. For the whole assignment, you must provide at least 5 citations and references to a resource such as W3Schools in relation to the SQL you use. You can use this  Online SQL Interpreter:  https://sql-js.github.io/sql.js/examples/GUI/ Or you can use one of the other Online SQL Lite Interpreters that use the SQL Lite database engine that have been mentioned in the course. Task A – Venables Limited Customer Contact System You must complete successfully every sub-task of Task A to pass this unit.  Background You work as a consultant with Goldstar Consulting. Goldstar Consulting have recently started a project with the customer services department of Venables Limited. Venables Ltd sells light fittings products to consumers (i.e. not businesses). The customer services department has been using a spreadsheet to record client details (the consumers), interactions between staff and clients (called Activities) and the staff members. Your colleague, Angie Smith, started the engagement but has fallen sick. You pick up the engagement from Angie. Resources TAVenables-Assignmt.db – the database file you start with. TA4-Client.csv – a text file of client data that you will need for Task 4. TA15-Activity.csv – a text file of activity data that you will need for Task 15. First Steps The database contains the following tables: · User · Product · Client You might like to do the following before starting: 1. Use this command to see the table structures: SELECT "name", "sql"   FROM "sqlite_master"   WHERE type="table"; 2. Use a SELECT statement to view the contents of each table. Important You must answer all questions successfully in order to pass (A1 to A27). When you answer the questions, you must provide: 1. An explanation of your choice of SQL; 2. The SQL that you run; and 3. A screenshot of the output from the database (if you are inserting or deleting data, also use a SELECT statement to show evidence of the loaded or deleted data). TASK A1: Remove The ‘Product’ Table AC11.1 Angie tells you that she has started the database in a file called TAVenables-Assignment.db. She says that there is a table called ‘Product’ in the database that the client no longer needs. You must remove it. TASK A2: Remove All Data from the ‘Client’ Table AC10.1 Angie tells you that she loaded all the wrong data into the ‘Client’ table. You need to remove all the existing data from the ‘Client’ table. TASK A3: Remove Some Data from the ‘User’ Table AC10.2 Angie tells you that the client no longer wants users from the Finance department to be in the User table. Please remove all users who are in the Finance department. TASK A4: Load Data Into The ‘Client’ Table AC8.1 Angie thanks you for deleting the wrong Client data. She has found the correct text file that contains all the client data that needs loading.  The file is named: TA4-Client.csv. You can download it from Moodle. Your task is to use the data in the file to make a SQL statement to load the Client data into the Client table. TASK A5: View The Data In The ‘Client’ Table AC1.1 You want to check that you have correctly loaded the Client data into the Client table. Use a SQL statement to show all records and all fields in the Client table. TASK A6: View all the Dentists in the ‘Client’ Table AC1.2 The Customer Services Manager (CSM) at Venables wants to see full details of all the dentists in the Client table. Write a SQL statement to return all details of Dentists. TASK A7: Use a Condition of Your Choice to Impress the CSM AC1.2 – use of imagination You decide to provide another SQL statement to view records from the Client table but this time using a different condition. Use your imagination to create a SQL statement that might be useful to the CSM. Explain why the SQL Statement might be useful to the CSM. TASK A8: Use Two Conditions - AND AC1.3 – use of AND The CSM asks to see all Clients who are ‘Dentists’ and ‘Silver’ level. TASK A9: Use Two Conditions - OR AC1.3 – use of OR The CSM asks to see all Clients who are ‘Dentists’ or are of ‘Silver’ level. TASK A10: Use Comparison Operator ‘>’ AC1.3 – Comparison Operator The CSM asks to see all Clients who onboarded in 2020 or later. TASK A11: Use Comparison Operator ‘.db containing the

Ali Asgar · Accepted Answer

Accountancy – An Introduction to Accounts
CBA890 – SQL Assignment
Using SQL Assignment
SQL Assignment CBA890
There are three tasks. Task A is compulsory and must be answered in full in order to pass. Task B is optional and for those who wish to impress with their SQL skills. For the whole assignment, you must provide at least 5 citations and references to a resource such as W3Schools in relation to the SQL you use. You can use this  Online SQL Interpreter: 
https://sql-js.github.io/sql.js/examples/GUI/
Or you can use one of the other Online SQL Lite Interpreters that use the SQL Lite database engine that have been mentioned in the course.
Task A – Venables Limited Customer Contact System
You must complete successfully every sub-task of Task A to pass this unit. 
Background
You work as a consultant with Goldstar Consulting. Goldstar Consulting have recently started a project with the customer services department of Venables Limited. Venables Ltd sells light fittings products to consumers (i.e. not businesses). The customer services department has been using a spreadsheet to record client details (the consumers), interactions between staff and clients (called Activities) and the staff members. Your colleague, Angie Smith, started the engagement but has fallen sick. You pick up the engagement from Angie.
Resources
TAVenables-Assignmt.db – the database file you start with.
TA4-Client.csv – a text file of client data that you will need for Task 4.
TA15-Activity.csv – a text file of activity data that you will need for Task 15.
First Steps
The database contains the following tables:
· User
· Product
· Client
You might like to do the following before starting:
1. Use this command to see the table structures:
SELECT "name", "sql"
  FROM "sqlite_master"
  WHERE type="table";
2. Use a SELECT statement to view the contents of each table.
Important
You must answer all questions successfully in order to pass (A1 to A27). When you answer the questions, you must provide:
1. An explanation of your choice of SQL;
2. The SQL that you run; and
3. A screenshot of the output from the database (if you are inserting or deleting data, also use a SELECT statement to show evidence of the loaded or deleted data).
Task A
Firstly we used the below SQL to know the initial format of our database.
SELECT `name`, `sql`
  FROM `sqlite_master`
  WHERE type='table';
Task A1:
Explanation: We need to delete an entire table for this we use the DROP command.
SQL: DROP TABLE 'Product'
We then re-ran the initial script to find the status of database.
SELECT `name`, `sql`
  FROM `sqlite_master`
  WHERE type='table';
  
Task A2
We use a select statement to find the contents of the Client Table
SQL: SELECT * FROM Client
Explanation: To Delete all data from a table, we use the “Delete From ” command without the “Where” clause. This deletes all data from the table, but leaves the table structure as is.
SQL: DELETE FROM Client
SELECT * FROM Client
Task A3
SELECT * FROM User;
Explanation: When we need to Use “Delete From ”  command with a “where” clause to delete rows with specific conditions.
We use Where clause with User_Dept=”Finance” to select all users with Finance dept.
SQL: DELETE FROM User WHERE User_Dept="Finance";	
SELECT * FROM User;
Task A4
Explanation: We use “INSERT INTO  (Column1,Column2…) VALUES ((R1Value1, R1Value2…),(R2Value1,R2Value2…),…) command. This command lets us insert multiple rows in a table with a single INSERT INTO command.
SQL:
INSERT INTO Client
( Client_Id,Client_Fst_Name,Client_Last_Name,Client_Profession,Client_Level,Client_Onboard_Date,
Client_Street,Client_City,Client_Postcode,Client_Phone,Client_Email,

Accountancy – An Introduction to Accounts CBA890 – SQL Assignment Using SQL Assignment SQL Assignment CBA890 There are three tasks. Task A is compulsory and must be answered in full in order to pass....

Answer To: Accountancy – An Introduction to Accounts CBA890 – SQL Assignment Using SQL Assignment SQL...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment