Write a program specification that describes all specifications and functions pr

Question

Write a program specification that describes all specifications and functions properly producing the correct input and output for the HR subsystem tables (REGIONS, JOBS). A program specification are simple requirements that describe what you need to do for this assignment.

Write PL/SQL code that INSERTS, UPDATES, and DELETES from REGIONS and JOBS and is understandable and well organized containing no errors. The guided practice assignment provided the basics on how to do this. Also, pay attention to particular coding standards. Also, make sure you are validating any user input in your code. Please adopt those in your application. Please refer to the guided practice assignment for guidance.

Region Table

Jobs Table

Explanation / Answer

Dear Friend,

Sorry I am not able to following your template why because i am not understanding what you need.

Please send me mail once about template, i will send later. My mail id is yoganjula.g@hotmail.com

PL/SQL Commands : Each and every operations in PL/SQL we should use some COMMANDS like CREATE command is for creating table or database and so on, INSERT command is for insert new records in table and so on we have COMMANDS.

1.CREATE COMMAND: CREATE command is for creating table or database and so on.

SYNTAX :

CREATE TABLE TABLE_NAME(Col1 Data_Type1,Col2 Data_Type2...);

Ex :

REGIONS

CREATE TABLE REGIONS(REGION_ID integer PRIMARY KEY, REGION_NAME varchar(200));

JOBS:

CREATE TABLE JOBS(JOB_ID varchar(100) PRIMARY KEY, JOB_TITLE varchar(200),

MIN_SALARY integer,MAX_SALARY integer);

2.INSERT COMMAND:

Insert command use for inserting set of records in TABLE.

SYNTAX:

INSERT INTO TABLE_NAME VALUES(Col1_Value,Col1_Value,....);

EX :

REGIONS:

INSERT INTO REGIONS VALUES(1,'Europe');
INSERT INTO REGIONS VALUES(2,'Americas');
INSERT INTO REGIONS VALUES(3,'Asia');
INSERT INTO REGIONS VALUES(4,'Jane');
INSERT INTO REGIONS VALUES(5,'Middle East and Africa');

JOBS:

INSERT INTO JOBS VALUES('AD_PRES','President', 20080,40000);
INSERT INTO JOBS VALUES('AD_VP','Administratin Vice President', 15000,30000);
INSERT INTO JOBS VALUES('AD_ASST','Administratin Assistance', 3000,6000);
INSERT INTO JOBS VALUES('FI_MGR','Finance Manager', 8200,16000);
INSERT INTO JOBS VALUES('FI_ACCOUNT','Accountant', 4200,9000);

3.SELECT COMMAND:

If you want see the data from table then we should use SELECT command

EX :

REGIONS:

select *from REGIONS;

OutPut:

REGION_ID | REGION_NAME

JOBS:

select *from JOBS;

OutPut:

JOB_ID | JOB_TITLE | MIN_SALARY | MAX_SALARY

4. DELETE COMMAND :

If you want delete particular record from TABLE then you can use.

SYNTAX :

DELETE FROM table_name. WHERE condition;

EX :

REGIONS :

DELETE FROM REGIONS WHERE REGION_ID=5;

select *from REGIONS;

Output :

REGION_ID | REGION_NAME

JOBS:

DELETE FROM JOBS WHERE JOB_ID='AD_PRES' and MIN_SALARY = 20080;
select *from JOBS;

Output :

JOB_ID | JOB_TITLE | MIN_SALARY | MAX_SALARY

5.UPDATE COMMAND :

If user want to update data in particular record then should use UPDATE command.

Syntax :

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

EX:

REGIONS:

UPDATE REGIONS
SET REGION_ID = 101, REGION_NAME = 'INDIA'
WHERE REGION_ID=1;

select *from REGIONS;

Output:

JOBS:

UPDATE JOBS
SET MIN_SALARY = 18000, MAX_SALARY = 28000
WHERE JOB_ID='AD_VP';
select *from JOBS;

Output:

This about PL/SQL basic command and solution for given scenario.

Thank you.

With Regards,

Yoganjula Reddy G.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.