Computer Science-QA151 online

Computer Science-QA151 Online Services

b. Write a query against the EMP.ENAME column using a LIKE operator to return all Employee Names that end in “S”. Provide a screenshot.
c. Write a query to return the rows of data from the EMP table for Employees that received a Commission (COMM) excluding the Employee that currently has a 0 dollar value. Provide a screenshot.
d. Write a query against the EMP table to test against a List of Values using the BETWEEN clause on the SAL column where the salary range is between 1100 and 2975 and where the DEPTNO is in either 20 or 30. Order the output by SAL. Provide a screenshot.
e. Write a query against the SCOTT.DEPT table where DNAME = ‘ACCOUNTING’ or LOC=’CHICAGO’. Provide a screenshot.
f. Write a query against the EMP table that returns the EMPNO, ENAME and JOB values for Employees with the jobs SALESMAN and MANAGER using a Subquery in the WHERE clause. Provide a screenshot.

g. Write a query that JOINS the EMP table and the DEPT table. Exclude the PRESIDENT from the result set and order the result set by HIREDATE in descending order. Provide a screenshot.
h. Create a VIEW in the SCOTT schema that hides the JOIN condition in 1g. Call it “View1h”. Then SELECT from the VIEW the DEPTNO, DNAME, LOC, ENAME and HIREDATE where the HIREDATE is between 01-FEB-81 and 01-FEB-82. Provide a screenshot.

2. Create a table called BENEFIT in the SCOTT schema that contains a BENEFIT_ID column with 4 digits, a BENEFIT_TYPE column with variable length up to 30 characters, the EMPNO column with 5 digits and an ELIGIBILITY_DATE column to represent the date an Employee becomes eligible to participate in the Benefits program. Use a table constraint to make the BENEFIT_ID column a primary key and name it “benefit_id_pk”. Execute the create table command and then show the structure in SQL Developer. Provide a screenshot.

a. Alter the SCOTT.BENEFIT table and add a Foreign Key for the EMPNO column. Call it “BENEFIT_EMPNO_FK”. Execute the command and provide a screenshot of the Constraints tab for the BENEFIT table.

b. Alter the SCOTT.BENEFIT table and add a column called JUNK with a numeric column with a width of 1 and a check constraint which is less than or equal to 9. Provide a screenshot of the constraints tab for the altered table structure.

c. Alter the SCOTT.BENEFIT table and mark the column JUNK as unused. Run the following command to verify that the BENEFIT table has a column marked as unused: “select * from dba_unused_col_tabs where owner = ‘SCOTT’;”. Provide a screenshot of the results.

d. Alter the SCOTT.BENEFIT table and drop the unused column JUNK. Provide a screenshot of the results/

e. Create a backup table of the SCOTT.DEPT table and call it “DEPT_BAK”. Provide a screenshot of the new table using the data tab.

3. Create a B*Tree Index on the SCOTT.BENEFIT table’s EMPNO column. Call it “benefit_empno_idx”. Provide a screenshot of the Indexes tab.

4. Create a Sequence for the SCOTT.BENEFIT.BENEFIT_ID column. Call it “BENEFIT_BEN_ID_SEQ”. Start incrementing with 100 and increment by 100. Make sure to create it in the SCOTT schema. Expand the Sequence node under the SCOTT schema and click on the sequence you just created. Provide a screenshot.

5.Run the following INSERT commands and then verify the data in the table. Provide a screenshot.
insert into SCOTT.benefit
values (scott.benefit_ben_id_seq.nextval, ‘HEALTH’, 7698, ’01-MAY-81′);

insert into SCOTT.benefit
values (scott.benefit_ben_id_seq.nextval, ‘DENTAL’, 7902, ’03-DEC-81′);

insert into SCOTT.benefit
values (scott.benefit_ben_id_seq.nextval, ‘LIFE’, 7839, ’17-NOV-81′);

6 .Run an UPDATE to set the benefit_typeforbenefit_id=300 from “LIFE” to “VISION” then INSERT another row for “LIFE” using EMPNO=7369 so that it comes after the three heath related benefits. Provide a screenshot of the data tab.

7.Delete from SCOTT.EMP the employee with EMPNO=7369. Did the record delete? Or did it throw an error? Provide a screenshot of the result.

Please Define the Following Database Terminology

Automatic Database Diagnostics Monitor (ADDM):
The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The ADDM analysis includes the following.

• CPU load
• Memory usage
• I/O usage
• Resource intensive SQL
• Resource intensive PL/SQL and Java
• RAC issues
• Application issues
• Database configuration issues
• Concurrency issues
• Object contention

You can read more about our case study assignment help services here.

How it Works

How It works ?

Step 1:- Click on Submit your Assignment here or shown in left side corner of every page and fill the quotation form with all the details. In the comment section, please mention Case Id mentioned in end of every Q&A Page. You can also send us your details through our email id support@assignmentconsultancy.com with Case Id in the email body. Case Id is essential to locate your questions so please mentioned that in your email or submit your quotes form comment section.

Step 2:- While filling submit your quotes form please fill all details like deadline date, expected budget, topic , your comments in addition to Case Id . The date is asked to provide deadline.

Step 3:- Once we received your assignments through submit your quotes form or email, we will review the Questions and notify our price through our email id. Kindly ensure that our email id assignmentconsultancy.help@gmail.com and support@assignmentconcultancy.com must not go into your spam folders. We request you to provide your expected budget as it will help us in negotiating with our experts.

Step 4:- Once you agreed with our price, kindly pay by clicking on Pay Now and please ensure that while entering your credit card details for making payment, it must be done correctly and address should be your credit card billing address. You can also request for invoice to our live chat representatives.

Step 5:- Once we received the payment we will notify through our email and will deliver the Q&A solution through mail as per agreed upon deadline.

Step 6:-You can also call us in our phone no. as given in the top of the home page or chat with our customer service representatives by clicking on chat now given in the bottom right corner.

Case Approach

Scientific Methodology

We use best scientific approach to solve case study as recommended and designed by best professors and experts in the World. The approach followed by our experts are given below:

Defining Problem

The first step in solving any case study analysis is to define its problem carefully. In order to do this step, our experts read the case two three times so as to define problem carefully and accurately. This step acts as a base and help in building the structure in next steps.

Structure Definition

The second step is to define structure to solve the case. Different cases has different requirements and so as the structure. Our experts understand this and follow student;s university guidelines to come out with best structure so that student will receive best mark for the same.

Research and Analysis

This is the most important step which actually defines the strength of any case analysis. In order to provide best case analysis, our experts not only refer case materials but also outside materials if required to come out with best analysis for the case.

Conclusion & Recommendations

A weak conclusion or recommendations spoil the entire case analysis. Our expert know this and always provide good chunks of volume for this part so that instructors will see the effort put by students in arriving at solution so as to provide best mark.

Related Services

Prior to completing working in your Oracle12c Lab environment, you should have loaded the Scott Schema and the HR Schema into your PDB container database. Please complete the following Hands-On Lab Assignments using your Oracle12c Environment

1. Open a SQL*Plus session and connect as SYSDBA. Alter you session to connect to your PDB database. Use the SHOW CON_NAME to verify that you are connected to the expected PDB container database. Run the following DECODE command against your HR schema’s REGIONS table (we could simply query the regions table for this but we want to demonstrate DECODE): select REGION_ID, DECODE(REGION_ID, 1, ‘Europe’, 2, ‘Americas’, 3, ‘Asia’, 4, ‘Middle East and Africa’, ‘Undefined’) from HR.REGIONS; Provide a screenshot or copy / paste the text from your SQL*Plus session to verify everything from your connection through the DECODE query and its result set. Also, after analyzing the query results, explain why we did or did not see the “Undefined” value presented.

2. Continuing with your SQL*Plus session, Insert a row into the HR.REGIONS table that only has the value of ‘5’ for REGION_ID and leave REGION_NAME NULL. After that write a query using the CASE function that will perform the same function as Lab 1. Provide a screenshot of both commands and their results sets.

3. Continue with SQL*Plus. Use the “set linesize 100” SQL*Plus formatting command to prevent screen wrap. Write a query using the COALESCE function against the HR.LOCATIONS table selecting the CITY and STATE_PROVINCE columns then applying the COALESCE function against a third column, in which we will use the STATE_PROVINCE column again in the first position and the CITY column in the second position. Provide a column alias name to the column created in the COALESCE clause and call it EXAMPLE. Provide a screenshot of your command and the results sets and provide a brief explanation as to why you got the particular result set.

4. Using SQL*Plus, run an example of how many employees are in each department of the SCOTT.EMP table using the following command.
selectdeptno, count(*) from SCOTT.EMP group by deptno;
Next run a PIVOT command to see how we can PIVOT the rows into columns using the following command.
select * from (select deptnofrom SCOTT.EMP)PIVOT(COUNT(*)For(DEPTNO)IN(10, 20, 30, 40));
Provide a screenshot of both commands and their execution result sets.

5. Continue using SQL*Plus. The following command concatenates some string data that is related to building some ANALYZE TABLE commands for tables in the HR schema. Start be setting some SQL*Plus parameters. The first “set heading off” will remove the column headings from the result set. Then “set pagesize 0”, this will stop SQL*Plus from breaking every so often to repeat the column headings. The goal is that you will execute the statement that concatenates the strings needed as well as the column for table_name from metadata to use some dynamic SQL and string concatenation to build working DBA command sets with little to no effort.
select ‘analyze table ‘ || ‘HR.’ || table_name || ‘ COMPUTE STATISTICS;’from dba_tables where owner = ‘HR’;
After the commands have been created run them and then provide a screenshot of all the work above or copy / paste from SQL*Plus to do the same.

6. Set you Heading back on in SQL*Plus as well as setting your page size to 30. Write a select command in SQL*Plus to select from the HR.JOB_HISTORY table. Pad the right side of the EMPLOYEE_ID column data with a “#” for 5 spaces. Pad the left side of the DEPARTMENT_ID column data with “-“ for 5 spaces. Run the command then describe the HR.JOB_HISTORY table. Provide a screenshot or copy / paste and also explain why we do or don’t see 5 symbols for both columns selected.

7. Continuing with the HR.JOB_HISTORY table, write a select statement to TRIM out the “1” from both sides of the EMPLOYEE_ID column, also list the EMPLOYEE_ID column separately in the same command so you can see the difference. Provide a screenshot and explain the results.

8. Write a statement against the HR.JOB_HISTORY table that pulls the original JOB_ID, pulls the JOB_ID back in lowercase and pulls the JOB_ID back with the initial letters in uppercase.

9. Write a statement to determine the character length of the STREET_ADDRESS column in the HR.LOCATIONS table. Provide a screenshot.

10. Write a statement to pull the first three digits (AREA CODE) of the PHONE_NUMBER in the HR.EMPLOYEES table. Make sure to only pull distinct values as we want to just see the distinct area codes where our employees live. Provide a screenshot.

11. Create a user with you first name. Then give it the DBA role. Provide a screenshot of your commands and results.

12. Create a profile that only allows three connection attempts. Call it “logon_attempts”. Create a user called “SAM” for new employee that starts in a week and assign him to the “login_attempts” profile. Give Samthe DBA role. Alter Sam to have his account locked until the day he starts. Provide a screenshot of the steps and results.

13. Connect with the DBA account you created in Lab 11 and make sure you are maintaining your connection to your PDB database (pdborcl is default). Run the appropriate command to verify the container database you are connected to. Sam has started working so use your DBA account to unlock his account. You also decided for now that he does not need DBA privileges so revoke that from his account and add CONNECT and RESOURCE. Provide a screenshot or copy / paste the steps and results from above.

14. Issue a command to verify SAM’s Role Privileges. SAM would be the “grantee” from the view. Provide a screenshot.

Product code: Computer science-QA151

Looking for best Computer Science-QA151 online,please click here

Summary