Oracle Basic Questions:
http://bcahub.shareittips.com/ty-bca/oracle/basic-oracle-queries-part-1/
http://bcahub.shareittips.com/ty-bca/oracle/basic-oracle-queries-part-2/
Q : Why we use indexes on a table? Does it impact inserting records in a table?
Keep in mind that while an index can speed up SELECTs, it can also can slow down INSERTs and UPDATEs. In addition, an index occupies disk space, which can be an issue not only for performance but also for backup / replication purposes. Just something you should keep in mind before adding 19,000 indexes to a database - there is definitely a happy medium between no indexes and too many.
I will mention that with a compound index, let's say columns a, b and c, the optimizer will use the index for a query on column a, or a and b, or a and b and c, or a and c. However, the index will not be able to optimize on queries against column b, column c, or columns b and c. What you use and what will work best depends on your schema, the nature of your queries, where your performance counts, the load on your system, hardware, levels of transactions, acceptable query times, type of application, etc. I strongly recommend running Index Tuning Wizard, feeding it a SQL trace of the typical activity on your system. The wizard should identify which types of indexes will work best for your scenario
Q:Difference b/w procedure and function?
Function has to return some value using return clause whereas procedure may or may not return any value( no out parameter).
We can use functions in SQL query but can't use procedure.
Procedure may return multiple or more than 1 value where as fuction always return single value.
function cannot change database enviornment it means you cannot write any update,create statements here but in procedure it is possible
Q. Which is more faster - IN or EXISTS?
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.
In many cases,EXISTS is better because it requires you to specify a join condition, which can invoke an index scan. However IN is often better if the result of subquery are very small.
For Exists condition if condition is satisfied then it will exit, but for IN condition till upto last record it will search.
Find out nth highest salary from emp table?
select salary from (
select rownum num, c.* from (
select salary from employee order by 1 desc) c)d
where num = #
Q-1: How to determine the structure of the any table? Give to 2 options?
Use the describe command or use the dbms_metadata.get_ddl package.
Q-2: Give the two types of tables involved in producing a star schema and the type of data they hold.
Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.
Q-3: If I am executing command “select 8 from emp” then what would be happening?
If total number of rows are in EMP table 14 then we would get output 8 , up to 14 times.
Q-4: A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
Q-5: What is a Cartesian product?
A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
Q-6: What is the default ordering of an ORDER BY clause in a SELECT statement?
Ascending.
Q-7: What type of index should you use on a fact table?
A Bitmap index.
Q-8: What is basic difference between view and materialized view?
Simple view is logical and only definition stored in data dictionary view of system schema means it doesn’t occupy any space in database, but materialized view is real table and it occupy space in database.
Q-9: Can a table contain more than 1 primary key?
No we can’t able to create more than 1 primary key on single table.
Q-10: Any trick to create more than one primary key on single table?
Create one primary and create NOT NULL column with Unique Constraints. Using this trick we can able to create more than one primary key in single table.
Q-11: Can Check constraint be used for self referential integrity ? How ?
Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.
Q-12: Can null keys be entered in cluster index, normal index? OR can I able to insert NULL values in cluster index or normal index?
Yes we can insert NULL keys in cluster index and normal index.
Q-13: What is the use of Data Dictionary?
Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc. Data Dictionary stores Oracle database metadata.
Q-14: Are views automatically updated when base tables are updated?
Yes views are automatically updated if any base table updated.
Q-15: What is the SYSDATE? Is Sysdate a system variable or a system function?
SYSDATE is system function.
Q-16: What is the difference between alias and synonym?
Alias is temporary and used with one query. Synonym is permanent and not used as alias.
Q-17: What is the difference between foreign key and reference key?
Foreign key is the key i.e. attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.
Q-18: Can DUAL table be deleted, dropped or altered or updated or inserted?
Nope, dual is a dummy table. It will return table not exists exception.
Q-19: Any effect will be occurring after updating or changing DUAL table?
Output using database functions are becoming unstable and might be wrong or producing errors.
Q-20: What is the difference between SQL*Plus commands and SQL commands?
SQL commands are terminated using / or ; (semi-colon) but SQL*Plus commands are terminated using enter key.
Q-21: Can I execute SQL*Plus command from buffer?
No. SQL*Plus command never saved in buffer only SQL commands are saved in buffer.
Q-22: Is it possible to use LONG columns in WHERE clause or ORDER BY?
No. We can’t use LONG column in where clause or order by clause of query.
Q-23: What is ROWNUM and ROWID in Oracle table?
ROWNUM and ROWID are pseudo columns.
Q-24: Give difference of ROWID and ROWNUM?
ROWID has a physical significance i.e you can read a row if you know rowid. It is complete physical address of a row. While ROWNUM is temporary serial number allocated to each returned row during query execution.
Q-25: Can we insert more than single NULL in unique constraint or unique index?
Yes we can insert more than one NULL value in unique constraint or unique index. Because every NULL is not similar.
Q-26: Can we create unique constraint using non-unique index?
Yes we can create unique constraint or primary key using non-unique index.
Q 27. Explain materialized views and how they are used.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
Q 28. How would you determine what sessions are connected and what resources they are waiting for?
Use of V$SESSION and V$SESSION_WAIT
29. Name a tablespace automatically created when you create a database.
The SYSTEM tablespace.
30. How can you rebuild an index?
ALTER INDEX REBUILD;
31. You have just compiled a PL/SQL package but got errors, how would you view the errors?
SHOW ERRORS
32. How can you gather statistics on a table?
The ANALYZE command.
33. How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
34. What is the difference between the SQL*Loader and IMPORT utilities?
These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.
35. Name two files used for network connection to a database.
TNSNAMES.ORA and SQLNET.ORA
36.Table Employee
Employee-Num | Type
1 | A
1 | B
2 | A
3 | B
From the above table I only want records where an employee has both Type A and Type B?
Ans:SELECT orig.emplNr
FROM Employee as orig join Employee as dummy on orig .empl=dummy.empl and dummy.type='B' where orig .type='A';
36: Difference b/w char/varchar and varchar2?
Both CHAR and VARCHAR2 types are used to store character string values, however, they behave very differently. The VARCHAR type should not be used:
CHAR
CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.
VARCHAR
Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.
VARCHAR2
VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.
what is normalization? what is the advantage of normalization?
Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating two factors: redundancy and inconsistent dependency.
Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed the data must be changed in exactly the same way in all locations. A customer address change is much easier to implement if that data is stored only in the Customers table and nowhere else in the database.
What is an inconsistent dependency ? While it is intuitive for a user to look in the Customers table for the address of a particular customer it may not make sense to look there for the salary of the employee who calls on that customer. The employee's salary is related to or dependent on the employee and thus should be moved to the Employees table. Inconsistent dependencies can make data difficult to access; the path to find the data may be missing or broken.
There are a few rules for database normalization. Each rule is called a normal form. If the first rule is observed the database is said to be in first normal form. If the first three rules are observed the database is considered to be in third normal form. Although other levels of normalization are possible third normal form is considered the highest level necessary for most applications.
As with many formal rules and specifications real world scenarios do not always allow for perfect compliance. In general normalization requires additional tables and some customers find this cumbersome. If you decide to violate one of the first three rules of normalization make sure that your application anticipates any problems that could occur such as redundant data and inconsistent dependencies.
NOTE: The following descriptions include examples.
First Normal Form
* Eliminate repeating groups in individual tables.
* Create a separate table for each set of related data.
* Identify each set of related data with a primary key.
Do not use multiple fields in a single table to store similar data. For example to track an inventory item that may come from two possible sources an inventory record may contain fields for Vendor Code 1 and Vendor Code 2.
But what happens when you add a third vendor? Adding a field is not the answer; it requires program and table modifications and does not smoothly accommodate a dynamic number of vendors. Instead place all vendor information in a separate table called Vendors then link inventory to vendors with an item number key or vendors to inventory with a vendor code key.
Second Normal Form
* Create separate tables for sets of values that apply to multiple records.
* Relate these tables with a foreign key.
Records should not depend on anything other than a table's primary key (a compound key if necessary). For example consider a customer's address in an accounting system. The address is needed by the Customers table but also by the Orders Shipping Invoices Accounts Receivable and Collections tables. Instead of storing the customer's address as a separate entry in each of these tables store it in one place either in the Customers table or in a separate Addresses table.
Third Normal Form
* Eliminate fields that do not depend on the key.
Values in a record that are not part of that record's key do not belong in the table. In general any time the contents of a group of fields may apply to more than a single record in the table consider placing those fields in a separate table.
For example in an Employee Recruitment table a candidate's university name and address may be included. But you need a complete list of universities for group mailings. If university information is stored in the Candidates table there is no way to list universities with no current candidates. Create a separate Universities table and link it to the Candidates table with a university code key.
EXCEPTION: Adhering to the third normal form while theoretically desirable is not always practical. If you have a Customers table and you want to eliminate all possible interfield dependencies you must create separate tables for cities ZIP codes sales representatives customer classes and any other factor that may be duplicated in multiple records. In theory normalization is worth pursuing; however many small tables may degrade performance or exceed open file and memory capacities.
It may be more feasible to apply third normal form only to data that changes frequently. If some dependent fields remain design your application to require the user to verify all related fields when any one is changed.
Other Normalization Forms
Fourth normal form also called Boyce Codd Normal Form (BCNF) and fifth normal form do exist but are rarely considered in practical design. Disregarding these rules may result in less than perfect database design but should not affect functionality.
Q. You have 2 tables
Employee(empid,emp_name,dept_id(foreign key for dept table))
Dept(dept_id,dept_name)
There can be some departments having no employees.
Select all departments with total number of employees in each department?
Ans:SELECT a.deptno,count(b.emp_id) from dept a, employee b where a.deptno=b.dept_id(+) group by a.deptno;
SQL> select deptno, count(e.empno)
2 from employees e
3 right outer join
4 departments d
5 using (deptno)
6 group by deptno;Q:You have a table A
emp_id,dept_id,salary,month
Find out top 3 employees from each dept having max salary?
(goldman Sachs)
Q:Table A(emp_name , salary)
Find out top 3 employees having max salary?(goldman Sachs)
Q:Table A(emp_id,dept_id,salary)
Find out all employees having salary>20000 dept wise?(goldman Sachs)
Q13:Display employees who earn the lowest salary of each department?(goldman Sachs)
Q14:Can anyone plz let me know the output for this query?
select 1 from dual
minus
select 'a' from dual;
Ans:This will produce Error
ORA-01790: expression must have same datatype as
corresponding expression
because 1 is number and a is string
Q15:Need to list department name and max salary for each?(Amazon)
Ans:Select max(employee.salary), department.dname from department, employee where employee.deptno = department.deptno group by department.dname;
Q16:Need to list department name,employee name and max salary for each?
Q17:You have 3 teams playing matches and you have to decide pair of matches b/w them. Write a query that can return this result.
or you have following table. Select pair for the peoples having same city.
Hint: It should not pair with itself and A,B=B,A so it should return only 1 record.(Pega Systems)
SQL> desc alwar_wale;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(2)
NAME VARCHAR2(30)
CITY VARCHAR2(20)
SQL> select * from alwar_wale;
ID NAME CITY
---------- ------------------------------ --------------------
1 Anand Alwar
2 Saurabh Alwar
3 Lokesh Alwar
SQL> select a.name,b.name from alwar_wale a,alwar_wale b where a.city=b.city and
a.id<>b.id and a.id>b.id;
NAME NAME
------------------------------ ------------------------------
Lokesh Anand
Saurabh Anand
Lokesh Saurabh
SQL> select a.name,b.name from alwar_wale a,alwar_wale b where a.id<>b.id and a.
id>b.id;
NAME NAME
------------------------------ ------------------------------
Lokesh Saurabh
Lokesh Anand
Saurabh Anand
SQL>
Q18: Return day of the week ?
SQL> select to_char(sysdate,'D') from dual;
3
SQL> select to_char(sysdate,'Day') from dual;
Tuesday
Q19: Insert dummy records in table?
SQL> INSERT INTO T_INV_IND SELECT ROWNUM * 2, ROWNUM * 3, 'Comment - '||ROWNUM F
ROM DUAL CONNECT BY ROWNUM < 1000000;
SQL>
Q20: Select employees getting second highest salary from each department?
SQL> select EmployeeName,a.salary,a.department from plc2_employees a,(select sal
ary,department from (select row_number() over (partition by department order by
salary desc) as row_dt, salary, department from plc2_employees) a where a.row_dt
=2) b where a.department=b.department and a.salary=b.salary;
select Deptno, max(sal) from emp where sal not in (select max(sal) from emp group by deptno) group by deptno;
Q21:What is the fastest query method for a table?
Expected answer: Fetch by rowid
Q22: What will the Output for this Coding?
Declare
Cursor c1 is select * from emp FOR UPDATE;
Z c1%rowtype;
Begin
Open C1;
Fetch c1 into Z;
Commit;
Fetch c1 into Z;
end;
Ans:
If the table which we are using in the cursor is empty
then procedure will run successfully.
But if table contains rows in it, it will give error as " fetch out of sequence"
It will ORA:01002 fetch out of sequence error
If the cursor has been opened with the FOR UPDATE clause,
fetching after a COMMIT will return the error.
Cisco:
Q.What is difference b/w bitmap index and b-tree index?
Q.select trunc(sysdate) from dual?
What is output of this?
Q.What is mutating table trigger errors in oracle?
Q.What are refresh methods for materialized view?
Q.Find out employees getting salary more then average salary of their departments?
Ans:
SQL> select a.emp_name,a.proj_name from amz_dept a,(select proj_name,avg(salary)
sal from amz_dept group by proj_name) b where a.proj_name=b.proj_name and a.sa
lary>b.sal;
Anil CCS
Putla CCS
Kolimi CCS
Sarath Support
Q.What is PRAGMA RESTRICT_REFERENCES?
Q.Do you know about util_file package and where it can be used?
JDA:
Q1: you have below table.
empid,emp_name,mgr_id,dept.
Find the manager name of employee 'Vinod';
select a.emp_name from emp a, emp b where a.emp_id=b.mgr_id and b.emp_name='Vinod';
Q2.Find out all employees getting salary greater then average salary of that company.
Ans: select * from emp where sal>(select avg(sal) from emp);
Q3.Write a pl-sql code block using bulk update?
Q4. You have a Table emp having data below.
eid, salary
1 100
2 200
3 300
4 400
Write a query to return result as
1 100
2 300
3 600
4 1000
Ans:select a.empno, sum(b.sal) bsal from emp a, emp b
where a.empno>=b.empno group by a.empno order by bsal;
select empno, sum(sal) over(order by empno rows UNBOUNDED PRECEDING ) running_sum from emp;
1. Employee salary keeps getting revised every year and historical data managed in table year wise. Write a query to return only latest salary of employee?<Levadata>
Ans: Latest salary will be that is inserted most recently. It means most recent (max) row id will contain latest salary
select * from emp_temp a where rowid< (select max(rowid) from emp_temp b where a.empno=b.empno);
select * from emp_temp a where rowid not in (select max(rowid) from emp_temp b group by empno);
2. EMP_HIST table has below structure<Levadata>
id(Sequence generated number) emp_name year salary
1 XXX 2011 10000
2 YYYY 2011 12000
3 XXX 2012 14000
4 YYYY 2012 16000
5 ZZZZ 2013 1000
6 YYYY 2013 20000
Find out percentage increment per year along with previous year salary? If it is joining year, then previous year salary should be zero.
Ans:
create table emp_temp1 as select * from emp;
insert into emp_temp1
select EMPNO, ENAME, JOB, MGR, HIREDATE+600,
SAL+600, COMM, DEPTNO from emp;
with tab as (select ename, sal,NVL(LAG(sal) over(partition by ename order by hiredate),0) last_sal,hiredate from emp_temp1)
select ename,sal,(sal-last_sal)/last_sal*100, last_sal from tab where last_sal<>0;
3. Design datamodel for manufacturing unit. <Levadata>
Considerations: MFG unit receives raw material from multiple vendors. Different type of raw material is procured. Assume compnay in pen manufacturing unit and it is in need of 200 pens manufacturing, and it procures 120 nibs from vendor X and 80 nibs from vendor Y in different costs. Model should consider units demands and rates from each vendor.
This datamodel should be suitable for OLAP system where management want to generate reports for various procured items.
4. Sell yourself by telling about yourself about studies/work experiance and data modeling/ Big data concepts.<Levadata>
5. What id UOM(Unit of measure)?<Levadata>
6.Have you worked on database migrations? Explain how you handle where rows needs to be handled in middle of some sequence.<Levadata>
Ex: MD_HIERARCY_LEVEL
7. What is SQOOP? MAP-REDUCE job will be created for SQOOP import/export?<Levadata>
8. You need to find out count of SKU's/location. How it decides number of mappers and will reduce job be created for this operation?<Levadata>
9. When reduce job will be created?<Levadata>
10. H-BASE is what kind of database?<Levadata>
Ans: column family database
Q. Can we have commit inside a trigger?
Ans: Trigger is part of parent transaction, and there should not be separate transaction control statement inside a trigger. But you can use Autonomous transaction if it is absolutely required.
Note: Trigger creation will not fail if it has transaction control statement but execution will fail
Ex:
DROP TABLE anand;
DROP TABLE anand_BK;
create table anand (id number);
create table anand_bk (id number, bkdate date);
CREATE OR REPLACE TRIGGER trig1
BEFORE INSERT
ON anand
FOR EACH ROW
BEGIN
INSERT INTO anand_bk values(:NEW.id, SYSDATE);
COMMIT;
end;
/
insert into anand values(1);
Error at line 20
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "WWFCP901.TRIG1", line 3
ORA-04088: error during execution of trigger 'WWFCP901.TRIG1'
Q. Find out number of employees in each department in single row. Department number should be heading of column.
Ans: To transpose rows into columns PIVOT can be used easily( apart from using SUM(Decode) for each dept)
with t as (select deptno from emp)
select * from t PIVOT(count(*) as dept for deptno in (10 as dept1,20,30,40,50));
Q. Find out minimum and maximum salary of each department and position? Positions should be headers .
with t as(select deptno,job,sal from emp)
select * from t PIVOT(min(sal) as minsal,max(sal) maxsal for (job) in ('CLERK','SALESMAN','MANAGER'))
http://www.oracle-developer.net/display.php?id=506
Q. Write a program to find out prime numbers between 1 to 100
Ans:
/* Formatted on 06-07-2016 09:32:20 PM (QP5 v5.265.14096.38000) */
--Write a program to find out prime numbers between 1-100
DECLARE
-- is_prime NUMBER;
int_prime NUMBER;
TYPE t_prime IS VARRAY (50) OF INTEGER;
v_prime t_prime := t_prime (1);
BEGIN
FOR i IN 2 .. 100
LOOP
-- <<OUTER>>
int_prime := I;
FOR j IN 2 .. (FLOOR (i / 2))
LOOP
-- is_prime := MOD (i, j);
IF MOD (i, j) = 0
THEN
int_prime := NULL;
--CONTINUE;
-- GOTO OUTER;
EXIT;
END IF;
END LOOP;
IF INT_PRIME IS NOT NULL
THEN
v_prime.EXTEND;
v_prime (v_prime.COUNT) := I;
END IF;
-- DBMS_OUTPUT.PUT_LINE(int_prime);
END LOOP;
FOR k IN 1 .. v_prime.COUNT
LOOP
DBMS_OUTPUT.put_line (v_prime (k));
END LOOP;
END;
/
Q. Find out employees having same salary?
Ans:select a.ename,b.ename from emp a, emp b where a.sal=b.sal and a.empno<b.empno;
select * from emp where sal in(
select sal from emp where rowid not in(
select max(rowid) from emp group by sal));
select a.empno,a.ename,a.sal from scott.emp a where a.sal in
(select sal from scott.emp group by sal having count(*)>1);
Q. Select the list of employees whose salary is greater then average salary of their department.
Ans:Select * from emp a
where sal >(select avg(sal) from emp b where a.deptno = b.deptno);
Q. Reverse a string in oracle without using reverse function?
Ans:
--Reverse a string in oracle without using reverse function.
--select reverse('ANAND') FROM DUAL;
DECLARE
NAME VARCHAR2 (30) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
REV_NAME NAME%TYPE;
BEGIN
FOR I IN 1 .. LENGTH (NAME)
LOOP
REV_NAME := SUBSTR (NAME, I, 1) || REV_NAME;
END LOOP;
DBMS_OUTPUT.PUT_LINE (REV_NAME);
END;
/
Q. Find out details of manager id 7698 all all his subordinates in the hierarchy?
Ans: select empno,ename,mgr,level from emp --where level<=2
start with empno=7698
connect by prior empno=mgr
order siblings by ename;
