Answer the following questions


-- 1. An example that rolls back a transaction
BEGIN;
BEGIN TRANSACTION;   -- the DBMS starts keeping a transaction log here

-- A) verify that the new records in invoice and line are NOT there while product and customer do exist
SELECT * FROM invoice  WHERE ; --use cus_code '1000006' for the invoice order
SELECT * FROM line WHERE ;  --use inv_number '4000006' for the new invoice
SELECT * FROM product WHERE ; --use p_code '3000007' for the product ordered
SELECT * FROM customer WHERE ; --use cus_code '1000006' for customer

-- B) add an invoice using inv_number 4000006, cus_code '1000006', and the current date with GETDATE()
INSERT INTO  
VALUES ;

-- C) add a new line item to the invoice using inv_number '4000006', p_code '3000007', costing 29.99
INSERT INTO
VALUES ;

-- D) update the product table to remove the amount of the product (p_qoh) that was sold for that line item using p_code '3000007' for the product ordered
UPDATE  
SET
WHERE ;

-- E) update the customer table to update customer cus_code '1000006' balance to go up 29.99
UPDATE  
SET
WHERE ;

-- F) verify that these new records and updates to invoice, line, product, and customer are there by copying your four verification SELECT statements here written in 1 A) here:
SELECT * FROM invoice  WHERE ;
SELECT * FROM line WHERE ;
SELECT * FROM product WHERE ;
SELECT * FROM customer WHERE ;

ROLLBACK;
--COMMIT;

-- G) verify that the new records in invoice and line are longer there (ROLLBACK) or there (COMMIT)
-- and the original amount of the product on hand and customer balance is the same (ROLLBACK) or updated (COMMIT) by again copying your four verification SELECT statements here written in 1 A) here:
SELECT * FROM invoice  WHERE ;
SELECT * FROM line WHERE ;
SELECT * FROM product WHERE ;
SELECT * FROM customer WHERE ;

-- H) highlight all the code from here up to BEGIN TRANSACTION and Execute to see the results
END;

-- 2. Now revise your above example by commenting out --ROLLBACK
--    and uncomment the COMMIT and repeat the highlighting above to commit the transaction to storage.

-- 3. Create an example that rolls back a different transaction
BEGIN;
BEGIN TRANSACTION;   -- the DBMS starts keeping a transaction log here

-- A) verify that your new records in invoice and line are NOT there and product and customer do exist
SELECT * FROM invoice  WHERE ;
SELECT * FROM line WHERE ;
SELECT * FROM product WHERE ;
SELECT * FROM customer WHERE ;

-- B) add an invoice using the current date
INSERT INTO  
VALUES ;

-- C) add a different line item to the invoice
INSERT INTO
VALUES ;

-- D) update the product table to remove the amount of the product that was sold for that line item
UPDATE  
SET
WHERE ;

-- E) update the customer table to update customer balance
UPDATE  
SET
WHERE ;

-- F) verify that these new records and updates to invoice, line, product, and customer are there by copying your four verification SELECT statements here written in 2 A) here:
SELECT * FROM invoice  WHERE ;
SELECT * FROM line WHERE ;
SELECT * FROM product WHERE ;
SELECT * FROM customer WHERE ;

ROLLBACK;
-- COMMIT;

-- G) verify that the new records in invoice and line are longer there (ROLLBACK) or there (COMMIT)
-- and the original amount of the product on hand and customer balance is the same (ROLLBACK) or updated (COMMIT) by again copying your four verification SELECT statements here written in 2 A) here:
SELECT * FROM invoice  WHERE ;
SELECT * FROM line WHERE ;
SELECT * FROM product WHERE ;
SELECT * FROM customer WHERE ;

-- H) highlight all the code from here up to BEGIN TRANSACTION and Execute to see the results
END;

-- 4. Now revise your example above to commit your transaction now that you have tested it

-- 5. Check what user roles to determine your priviledges in a database using stored system tables:
BEGIN;

SELECT rol.name
FROM sys.database_principals mem
 INNER JOIN sys.database_role_members drm
 ON drm.member_principal_id = mem.principal_id
 INNER JOIN sys.database_principals rol
 ON drm.role_principal_id = rol.principal_id
WHERE mem.principal_id = user_id();