New Content Unit 3 AIS
Which SQL command is used to remove a table from a database? A)REMOVE TABLE B)DROP TABLE C)DELETE TABLE D)D) ERASE TABLE
B) DROP TABLE
What is the correct SQL syntax for inserting a new record into a table? A)ADD INTO B) INSERT INTO C) ADD RECORD D) ADD ROW
B) INSERT INTO
You need to write a command to retrieve all the records in the Products table. Which of the following syntax is the correct command? A) SELECT all FROM Products B) SELECT * FROM Products C) RETRIEVE all FROM Products D) RETRIEVE * FROM Products
B) SELECT * FROM Products
What is the purpose of the WHERE clause in an SQL query? A)To specify the columns to be retrieved B) To filter rows based on a condition C) To order the results in ascending or descending order D) To join two or more tables
B) To filter rows based on a condition
Updating Data (Update the email address of a customer)
UPDATE Customer SET City = Amherst WHERE CustomerNumber = 1
Top 2 reasons for fraud
-Lack on internal controls. -Overriding internal controls.
What is the correct SQL syntax for deleting a record from a table? A)REMOVE FROM B) DELETE FROM C) ERASE FROM D) DROP FROM
B) DELETE FROM
3 categories of occupational fraud
1. Corruption 2. Asset misappropriation 3. financial statement fraud
Billing Scheme Red Flags
1.An employee's home address matches a vendor's address. 2.An employee's initial match a vendor's name. 3.Checks are written to "cash." 4.A vendor's address contains a P.O. box. 5.Vendor data are missing. 6.Vendor data are formatted illogically.
Which SQL command is used to create a new table in a database? A)CREATE TABLE B)ADD TABLE C)INSERT TABLE D)NEW TABLE
A) CREATE TABLE
You need to write a query to retrieve all customer information (table is 'Customer') for customers who do NOT live in Texas ('TX'). Which of the following SQL statements will work for your query? A) SELECT * FROM Customer WHERE State <> 'TX' B) SELECT * FROM Customer WHERE State not = 'TX' C) SELECT all FROM Customer WHERE State <> 'TX' D) SELECT all FROM Customer WHERE State not = 'TX'
A) SELECT * FROM Customer WHERE State <> 'TX'
What is the correct syntax for adding a new column named "email" to an existing table named "users" with a datatype of VARCHAR(50)? A)ALTER TABLE users ADD COLUMN email VARCHAR(50); B)ADD COLUMN users.email VARCHAR(50); C) MODIFY TABLE users ADD email VARCHAR(50); D) CREATE COLUMN users.email VARCHAR(50);
A)ALTER TABLE users ADD COLUMN email VARCHAR(50);
Which of the following is the correct syntax for creating a new table in SQL? A)CREATE TABLE table_name (column1 datatype, column2 datatype); B) NEW TABLE table_name (column1 datatype, column2 datatype); C) ADD TABLE table_name (column1 datatype, column2 datatype); D) INSERT TABLE table_name (column1 datatype, column2 datatype);
A)CREATE TABLE table_name (column1 datatype, column2 datatype);
What is the correct syntax for deleting all records from a table named "orders"? A)DELETE FROM orders; B)REMOVE FROM orders; C)ERASE FROM orders; D)DROP FROM orders;
A)DELETE FROM orders;
Which SQL command is used to retrieve data from a database? A)SELECT B) UPDATE C) INSERT D) DELETE
A)SELECT
What is the correct syntax for a simple SELECT statement to retrieve all columns from a table named "customers"? A)SELECT * FROM customers; B) SELECT ALL FROM customers; C) SELECT ALL columns FROM customers; D) RETRIEVE * FROM customers;
A)SELECT * FROM customers;
What is the purpose of the JOIN clause in SQL? A)To combine rows from two or more tables based on a related column between them B)To group rows that have the same values C)To filter rows based on a condition D)To perform mathematical operations on columns
A)To combine rows from two or more tables based on a related column between them
What is the purpose of the HAVING clause in SQL? A)To filter rows based on a condition B) To group rows that have the same values C) To order the results in ascending or descending order D) To perform mathematical operations on columns
A)To filter rows based on a condition
What is the correct syntax for updating a column named "age" in a table named "students" to make all values 21? A)UPDATE students SET age = 21; B)B) MODIFY students SET age = 21; C)ALTER students UPDATE age = 21; D)D) SET students.age = 21;
A)UPDATE students SET age = 21;
Which SQL keyword is used to filter rows based on a condition in a SELECT statement? A)WHERE B) FILTER C) CONDITION D) IF
A)WHERE
What is the correct syntax for renaming a table named "old_table" to "new_table" in SQL? A)ALTER TABLE old_table RENAME TO new_table; B)RENAME TABLE old_table TO new_table; C)MODIFY TABLE old_table RENAME new_table; D)CHANGE TABLE old_table TO new_table;
B)RENAME TABLE old_table TO new_table;
What is the purpose of the WHERE clause in an SQL query? A)To specify the columns to be retrieved B)To filter rows based on a condition C)To order the results in ascending or descending order D)To join two or more tables
B)To filter rows based on a condition
Which SQL keyword is used to link two or more tables based on a related column? A)CONNECT B) MERGE C) JOIN D) LINK
C) JOIN
You need to write a query to retrieve all customer information (table is 'Customer') for customers who do NOT live in Texas ('TX') and the payment method (field is 'PaymentMethod') is American Express ('AMEX'). Which of the following SQL statements will work for your query? A) SELECT all FROM Customer WHERE State <> 'TX' and PaymentMethod = 'AMEX' B) SELECT all FROM Customer WHERE State not = 'TX' and PaymentMethod = 'AMEX' C) SELECT * FROM Customer WHERE State <> 'TX' and PaymentMethod = 'AMEX' D) SELECT * FROM Customer WHERE State not = 'TX' and PaymentMethod = 'AMEX'
C) SELECT * FROM Customer WHERE State <> 'TX' and PaymentMethod = 'AMEX'
You need to write an SQL query to retrieve CustomerFirstName, CustomerLastName, and CustomerEmailAddress from the Customer table for all customers who list "OK" as their state (field name for state is CustomerState). Which SQL statement will yield the results that you need? A) SELECT CustomerFirstName, CustomerLastName, CustomerEmailAddress FROM Customer WHEN CustomerState IS 'OK' B) SELECT CustomerFirstName, CustomerLastName, CustomerEmailAddress FROM Customer WHEN CustomerState = 'OK' C) SELECT CustomerFirstName, CustomerLastName, CustomerEmailAddress FROM Customer WHERE CustomerState = 'OK' D) SELECT CustomerFirstName, CustomerLastName, CustomerEmailAddress FROM Customer WHERE CustomerState IS 'OK'
C) SELECT CustomerFirstName, CustomerLastName, CustomerEmailAddress FROM Customer WHERE CustomerState = 'OK'
Which SQL statement is used to update existing records in a table? A)ALTER B)MODIFY C)UPDATE D)CHANGE
C)UPDATE
Creating a Table
CREATE TABLE table_name ( column1 datatype1 constraints, column2 datatype2 constraints,
Which SQL keyword is used to specify the values to be inserted into a table? A)SET B) VALUES C) INTO D) INSERT
D) INSERT
Deleting Data (Delete a Sale)
DELETE FROM Sales WHERE SalesOrderNumber = 2
Outer Join
Displays All records in one table and the Matching records in another table
what is fraud ethically?
Fraud is gaining an unfair advantage over another
Inserting Data (Insert a new Inventory Item)
INSERT INTO Inventory (Description, Retail_Price) VALUES ('Vintage Cowboy Hat', 200)
Inner Join
Only displays matches in the tables
The fraud triangle
Opportunity, pressure, rationalization
Selecting Data
SELECT column1, column2, ... FROM table_name WHERE condition
Corruption
There are four types of corruption fraud schemes oConflicts of interest oIllegal gratuities oCommercial bribery oEconomic extortion
What is fraud legally?
a false statement, a material fact which induces a person to act, an intent to deceive, misrepresentation, an injury is suffered
Accountant's Role Preventing Fraud
preventing fraud before losses occur should always be the priority over detecting it after it happens - prevention is the most cost-effective approach to fighting fraud
fraud prevention approach
strong system of internal controls and creates a culture of ethical behavior in an organization
Asset misappropriation
top two types -Billing Schemes -Travel Expense Reimbursement
Financial statement fraud
understating or overstating the company's performance
Behavioral Red Flags
•Financial Difficulties •Living beyond one's means •Close association with vendor or customer •Recent divorce or family problems •Control issues or unwillingness to share duties •Big-shot attitude
Detecting fraud
•Identifying non-behavioral red flags at a process or entity level •Setting up anonymous whistleblower hotlines •Using data analytics specifically designed to continuously monitor processes and systems for fraud trends or anomalies •Conducting independent checks of people's work •Reviewing adequate documentation that leaves an audit trail
vertical analysis
•This type of analysis involves calculating each line item in the same financial statement as a percentage of another line item in the same financial statement
Horizontal analysis:
•This type of analysis involves investigating the changes in financial statement items by comparing two or more financial statements from different periods
reasons management might want to understate the company's financial performance
•To decrease taxes owed •To reduce the amount of money distributed in dividends to shareholders •To defer earnings to subsequent periods if current goals have been met •To reduce investors' current expectations to create perception of growth in the future
When Querying data you may be getting an incomplete data set if you do not consider...
•join properties.