Oracle Questions for Viva-Voce

• What is Data.
• What is DBMS. Give example. 
• What are the advantages of DBMS
• What is RDBMS. Give example. 
• What are the advantages of RDBMS.
• What is normalisation.
• What is table and record.
• What are attributes.
• what is tuple.
• What is key. And types of key.
• What is primary key.
• What is foreign key.
• What is the difference between primary key and unique key
• what is a query.
• What is DDL. 
• What are DDL  queries.
• What is DML.
• What are DML queries.
• Difference between char and varchar.
• Difference between alter and update.
• Difference between drop and  delete.
• What is join and types of join.
• What is CURSOR. And purpose of each parts of cursor.
• What is stored procedure. Advantage of stored procedure.
• What is stored function. Advantage of stored function.
• Difference between stored procedure and stored functions

Oracle Queries II

1. Count total number of employee in accounts department.
2. Display total number of employee in each department.
3. Find the difference between minimum and maximum salary of an employee.
4. Display job history of all employees including their phone number and name that are from the department administration.
5. Display name and contact number of an employee worked as Junior Admin Assistant.
6. Display position wise sum and average salary of an employee.
7. Display name of employee heading more than one department.
8. Display name, contact number of employee along with the number of department they are heading.
9. Find number of employees having salary greater than 20000.00
10. Display total salary to be paid on each position in a year,
11. Display various designation of accounts department.
12. Find total post in software design.
13. Display details of department having zero employee.
14. Display details of department having atleast 2 employees.
15. Display average salary to be given in any month.
16. Display name of department having maximum employee.
17. Display details of employee including name, address, phone  number, department name, position held, joining and ending date having minimum salary.
18. Display details of employee with designation having salary greater than average salary.
19. Display minimum, maximum, average and sum of salary of all positions.
20. Count total employee joined the organisation in the year 1981.
21. Display employee name, position, date of birth of all employee joined organisation in the month of march.
22. Display name of employee, date of birth and phone number worked as assistant administrator.
23. Display name of all engineers.
24. Display total employee worked as programmer.
25. Display details in increasing order of salary.

Oracle - TABLE STRUCTURE & DATA

1. Create following tables 

EMPL

Field Name	Data Type	Size
empno	Number	5
lastname	Varchar2	20
firstname	Varchar2	20
dob	Date
address	Varchar2	30
city	Varchar2	20
telno	Varchar2	20
depno	Number	5

JOBHISTORY

FieldName	Data Type	Size
empno	Number	5
position	Varchar2	50
startdate	Date
enddate	Date
salary	Number	8,2

DEPTM

Field name	Data type	Size
depno	Number	5
dname	Varchar2	20
location	Varchar2	15
head	Number	5

2. Insert following data in DEPTM table

depno	dname	location	head
1	accounts	floor 3	1
2	administration	floor 2	1
3	software design	floor 1	2
4	communications	floor 1	3
5	engineering	floor 4	5
6	human resource	floor 2	7

3. Insert following data in EMPL table

empno	lastname	firstname	dob	address	city	telno	depno
1	Jones	Elizabeth Barbara	05-Jan-44	26 Agnews Terrace	Shamrock Bay	212 337 2288	1
2	Smith	Robert	07-Feb-47	18 Marsh Street	Edinburgh	031 732 8972	1
3	White	Allan	05-May-61	6 Remote Place	North Berwick	121 555 6622	1
4	Reid	Gordon	10-Aug-63	9 Noble Road	Penicuik	629 424 6713	1
5	Murphy	Brian Charles	30-Jun-54	9 Roberts Street	Biggar	331 229 4147	1
7	Gibson	James	09-Mar-48	11 Depressed Way	Glasgow	041 447 8001	2
8	Allen	Smith	18-Sep-58	25 Crisis Avenue, Leith	Edinburgh	031 337 4166	2
9	Andrews	John	02-Jan-58	73 Long Road	Lengthitown	70 229 7213	2
10	Reagan	Anne	17-Aug-61	82 Longstone Road, Longstone	Edinburgh	031 111 2799	2
11	North	Annabel	01-Sep-62	35 Marchmont Terrace, Marchmon	Edinburgh	031 447 2266	3
12	South	Todd James	28-Feb-59	10 Shandon Road, Merchiston	Edinburgh	031 333 1008	3
13	East	Ian	13-May-42	47 Colinton Road, Craighlochar	Edinburgh	031 424 5665	3
14	West	Jack	15-Jun-46	15 South West Gardens	Peebles	466 3176	3
15	Allen	Ester	27-Feb-55	10 Troon Street, Leith	Peebles	031 424 2907	3
16	Cowan	Audrey	03-Apr-40	12 Down Street	Brayend	228 9321	4
17	Stevenson	John David	12-Dec-54	16 Rubber Road	Stampingham	337 6262	4

4. Insert following data in JOBHISTORY table

empno	position	startdate	enddate	salary
1	Accounts Manager	12-Jan-1976		30000.00
1	Accountant	10-Mar-1968	11-Feb-1972	15000.00
2	Assistant Accounts Manager	8-May-1976	1-Jan-1980	25000.00
2	Accountant	7-Jun-1971	8-May-1976	20000.00
2	Junior Accountant	6-Jul-1967	7-Jun-1971	8000.00
3	Junior Accountant	4-Sep-1981	5-Aug-1984	8000.00
4	Accountant	5-Oct-1989		16000.00
5	Junior Accountant	1-Dec-1978	2-Nov-1980	10000.00
6	Assistant Accountant	12-Jan-1980		20000.00
7	Admin Manager	10-Mar-1980		30000.00
7	Assistant Admin Manager	9-Apr-1974	10-Mar-1980	22000.00
7	Senior Admin Assistant	1-Dec-1968	9-Apr-1974	16000.00
8	Assistant Admin Manager	10-Mar-1980		20000.00
9	Admin Assistant	8-Jun-1982	10-Jul-1988	10000.00
10	Junior Admin Assistant	22-Sep-1983	6-Sep-1986	6000.00
11	Junior Admin Assistant	23-Oct-1980	13-Mar-1982	8000.00
12	Systems Analyst	9-Feb-1986	21-Aug-1988	31000.00
12	Analyst Programmer	17-Jan-1984	9-Feb-1986	25000.00
12	Analyst Programmer	17-Jan-1984	9-Feb-1986	25000.00
13	Senior Systems Analyst	21-Aug-1988		35000.00
14	Programmer	16-Nov-1986	25-Apr-1991	21000.00
15	Programmer	1-Oct-1984	16-Nov-1986	16000.00
16	Communications Engineer	13-May-1975	21-Jun-1977	12000.00
17	Trainee Engineer	13-Sep-1982	30-Aug-1985	11000.00
17	Electronic Engineer	1-Sep-1980	13-Sep-1982	9000.00