Implement a virtual database on DBSEC schema, for example, on CUSTOMER/CUSTOMERS table(depending on how your spelled it). Your mission is to develop a single SQL script that will perform all the following tasks(scripts are already attached here):

Please submit  a screenshot of the final output.

Total 5 steps: 

 

1. Drop Customer/Customers table and re-create it or alter the table and include the new column. Insert the new data(data file is attached) provided here.
2. Create a view called MY_VIEW. (Syntax and the data are attached)
3. Grant permissions as listed below to the user DBSEC_CLERK.
4. Login as DBSEC_CLERK. Insert the one record attached in the notepad here.
5. Query as DBSEC_CLERK.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

LAB STARTS HERE:

 

1. Connect to DBSEC/secc$1new, the same user you created in previous projects.
2. Createe a CUSTOMER/CUSTOMERS table with the following columns: (You need to drop/ alter the current table and re-create it to accommodate the new field "USER_NAME")

Column Name	Data Type
SALES_REP_ID	NUMBER(4)
CUSTOMER_ID	NUMBER(8) NOT NULL
CUSTOMER_SSN	VARCHAR2(9)
FIRST_NAME	VARCHAR2(20)
LAST_NAME	VARCHAR2(20)
ADDR_LINE	VARCHAR2(80)
CITY	VARCHAR2(30)
STATE	VARCHAR2(30)
ZIP_CODE	VARCHAR2(9)
PHONE	VARCHAR2(15)
EMAIL	VARCHAR2(80)
CC_NUMBER	VARCHAR2(20)
CREDIT_LIMIT	NUMBER
GENDER	CHAR(1)
STATUS	CHAR(1)
COMMENTS	VARCHAR2(1024)
USER_NAME	VARCHAR2(30)

 

3. Populate the CUSTOMER table with the data file attached here .
 

4. Createe a VIEW named as MY_VIEW to display only rows that belong to the logged in user.
 

5. Grant SELECT and INSERT privilege on MY_VIEW to DBSEC_CLERK.

6. Connect to DBSEC_CLERK/inft6157clerk. (This user was created in Project 4.)

7. Insert one row of data into MY_VIEW as DBSEC_CLERK by using the following data. You may need to make corrections on the statement, but keep the data as they are.

8. Verify your data insertion by querying MY_VIEW. You (as DBSEC_CLERK) should only see one row of data you have inserted. This signifies the success of your implementation. (Please submit a snapshot of this step even if it's not successful)

Answer rating: 100% (QA)

I can provide you with SQL scripts to perform the tasks youve outlined Please note that I cant provi... View the full answer