query?
A: PL/SQL tables are not stored in the database, and is usually created inside a PL/SQL block with only one column.
A: If Oracle is used in a UNIX server, then Oracle SID is set in the .profile file. It denotes the database.
queries?
What are inline views?
42 Views 43 Fundamentals 44 Views 45 Views 46 Views 47 Fundamentals 48 Index 49 Views 50 Triggers 51 Index
What are the
advantages of force views? What type of DBMS is supported by Oracle 8i? What are materialized views? What are the rules that are to be followed while inserting data into views?
Can you insert records into materialized views? What is the difference between a snapshot and a materialized view? What is the advantage of using a clustered index? What are the enhancements on Views in Oracle 8i? What are the new types of triggers added in Oracle 8i? Executing the query on a million records performance. it?
Select *, revenue- cost from tableA
52 Index 53 Summary tables 54 Partition 55 Datatypes 56 Locks 57 Nested tables 58 PL/SQL 59 Queries 60 PL/SQL
61 Queries to display records hierarchy tree level
Difference between Bitmap and B-Tree indices.
Which feature in Oracle 8i supports incremental updates?
Hash and composite partitioning
techniques What are the
constraints imposed on creating a
column of 'LONG' datatype?
What happens when you use a 'for
update of clause' in a select stmt?
How are data stored in a nested table? What are REF cursors? What is a star query? Give eg. There are 2 exceptions in a PL/SQL block. When will both the
exceptions get executed? (Twisted) When do you use 'connect by prior' in a query?
62 String functions 63 Procedure 64 PL/SQL 65 Numeric functions 66 Fundamentals 67 Date functions What is the difference between a translate and a replace of a string? Egs?
What happens when you modify and re- compile a
procedure? What are bind variables?
How will you find the byte size of a particular column? 'Select sysdate from dual' returns 5 records. What's the implication?
How will you fetch the age of a person, given his DOB column in a table, using a simple query?
Srl NO #Subject ComplexityQuestions Answers 1 Control M Average What is Control M?
2 Control M Complex Integerated Components of Enterprise/CS
3 Control M Simple Owner of the production file controlled 'prod' user.
Control-M is a multi-platform job scheduling and control system. It executes as a background activity and schedules, submits, tracks and follows up the execution of jobs. Used in conjunction with Enterprise Control Station
(Enterprise/CS) it provides centralised workstation control of the job scheduling production environment. An instance of Control-M resides on each of the
machines for which scheduling is
required. Enterprise/CS resides on a Sun workstation and can control multiple instances of Control-M.
Enterprise/CS is comprised of three integrated components which work in unison to provide centralised control of the entire job scheduling environment. 1. The first component, the data-centre, consists of one or more computers which are
managed by one Control-M monitor. Enterprise/CS can control many data- centres of the same or different types of platforms. 2.The second component, a gateway, provides communication between data- centres and the Enterprise/CS
workstation(s). 3.The third component, the Enterprise/CS
Workstation, provides an easy-to-use
graphical user interface and local SQL database, used to maintain all the data- centres.
4 Control M Average
5 Control M Simple What the environment variable CTM_ODATE mean?Order date for the job. 6 Control M Average Stages of execution
7 Control M Simple PCP
General Structure of the HOME dorectory of
the 'prod' user runlib - Storage area for all scripts and binaries used by a Control M controlled application overlib - Temporary area for emergency fixes rellib - Used as holding area prior to release into run library
oldlib - Contains the previous version of
any updated files. adhoc - Emergency one off runs INIT - Initialisation files :
ksh.env, csh.env, INIT, pcp related files, 1. Control M checks the permissions of the file, comparing it with the job owner field. It copies the file, making a number of changes to the script. These changes are made so that Control-M can trace and post-process the job output. 2. Control-M switches user to the job owner, changes directory to the job owners HOME directory and executes the .profile/.cshrc scripts. 3. Main script startup 4. The Parameter Construction Program (PCP) is used to create a file of options, parameters and other configuration information, some of which may change depending on the date. 5. Main script execution 6.Control M - Final processing
The Parameter Construction Program should be used to create any variable configuration, parameters or startup code required by a group of scripts.
8 Control M Average How do you call a sql?
9 Control M Average How do you call a Informatica session? Using pmcmd command in the script 10 Control M Simple What is RIT?
Call the oracle login instances and giving the sql script as a parameter
It's a Run time Information Template. It tell us the information about the control M job reg. Its scheduling time, Inputs reqired, database which it is using, Whether it is doing Insert/ Update / Delete
Sl. No. Subject Complexity Questions Answers
1 simple what is a Data Warehouse?
19 Complex Subject-oriented, Integrated, Time-variant, Non-volatile.
17 Medium <Diagram>
35
18 Medium Top-down and Bottom-up approaches.
2 simple Data Mart
25 Medium Yes.
30 simple OLTP system, Legacy system, Files, Other sources.
14 simple
4 simple A method by which multidimensional analysis occurs.
DataWareh
ousing A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.
DataWare housing
What is the conventional definition of a DWH? Explain each term.
DataWare
housing Draw the architecture of a Datawarehousing system. What are the goals of the Data warehouse?
DataWare housing
What are the approaches in constructing a
Datawarehouse and the datamart?
DataWareh
ousing A data structure that is optimized for access. It is designed to facilitate end-user analysis of data. It typically supports a single, analytic application used by a distinct set of workers. Datawareh
ousing Can a datamart be independent? Datawareh
ousing What are the sources for a datawarehouse? DataWareh
ousing What the difference is between a database, a data warehouse and a data mart?
A database is an organized collection of information. -- A data warehouse is A very large database with special sets of tools to extract and cleanse data from operational systems
and to analyze data.
-- A data mart is A focused subset of A data warehouse that deals with A single area of data and is organized for quick analysis.
DataWareh
5 average/simple
42 40
20 OLAP Medium ROLAP eg.BO, MOLAP eg.Cognos, HOLAP, DOLAP
24 Medium
7 simple Star Schema
DataWareh
ousing what do you mean by Multidimensional Analysis? The ability to manipulate information by a variety of relevant categories or “dimensions” to facilitate analysis and understanding of the underlying data. It is also sometimes referred to as “drilling-down”, “drilling-across” and “slicing and dicing”
What is the difference between OLAP, ROLAP, MOLAP ,DOLAP?
Difference between OLAP & OLTP?
What are the different types of OLAP? Give an eg.
Data modelling
Which is the suitable data model for a datawarehouse? Why?
MultiDimensional Model. optimized for data warehouse, data mart and online analytical processing (OLAP) applications. The main advantage of this database is query performance.
DataWareh ousing
16 complex
8 simple Snowflake Schema
32 Complex What is Galaxy schema?
36 What is Dimension & Fact ?
41 Different types of Dimensions
23 Twisted Yes. In snowflake schema, they are normalized.
37
38 Different types of Facts? Additive, semi-additive, non-additive
48 Event tracking & coverage
43 What is Granularity?
22 simple Is the Fact table normalized? Yes
12 average/simple Yes
21 Complex
31 Medium What are fact constellations? Multiple fact tables sharing the dimension tables. DataWareh
ousing What are Additive Facts? Or what is meant by Additive Fact?
The fact tables are mostly very huge and almost never fetch a single record into our answer set. We fetch a very large number of records on which we then do, adding, counting, averaging, or taking the min or max. The most common of them is adding. Applications are simpler if they store facts in an additive format as often as possible. Thus, in the grocery example, we don’t need to store the unit price. We compute the unit price by dividing the dollar sales by the unit sales whenever necessary.
DataWareh
ousing An extension of the star schema by means of applying additional dimensions to the dimensions of a star schema in a relational environment.
Datawareh
ousing A set of fact tables with some mutual dimension tables is called galaxy schema. Confirmed, Degenerate, Junk, Mini, Multivalued, Too-Many, Dirty customer, Demographic mini dimensions
Normalizat
ion Are the dimensional tables normalized? If so when? What is Transaction fact table & Centipede Fact table?
What are the types of Factless fact tables?
Granularity
The level of detail of the facts stored in a data warehouse. Normalizat
ion
DataWareh
ousing Can 2 Fact Tables share same dimensions Tables?
Project- related
Give egs. of the fact, dimensional tables,
datamarts/DWH used in your project. Explain what data each contains.
Datawareh ousing
15 simple
27 simple What is metadata?
9 simple What is data quality?
28 ETL simple 46
29 Medium What are surrogate keys?
26 simple Erwin, Embarcedaro, Rational Rose, Oracle Designer.
33 Medium Materialized views?
34 Medium No.
44 Definition of Adhoc Queries?
45
DataWareh
ousing
What is a Fact less fact table ?
A factless fact table captures the many-to-many relationships between dimensions, but contains no numeric or textual facts. They are often used to record events or coverage information.( A fact which does not have any measures ) Common examples of factless fact tables include:
o Identifying product promotion events (to determine promoted products that didn’t sell)
o Tracking student attendance or registration events o Tracking insurance-related accident events
o Identifying building, facility, and equipment schedules for a hospital or University.
Datawareh
ousing Data that describes data and other structures, such as objects, business rules, and processes. DataWareh
ousing Data quality (information quality) is defined as standardizing and consolidating customer and/or business data. By cleansing/enhancing the data and combining related records to avoid duplicate entries, you’re able to create a single record view. Within Informatica, this all takes place prior to the initial load to the target database as well as during the on-going data maintenance and updating processes.
How do you achieve data quality?
By Cleansing - The process of resolving inconsistencies and fixing the anomalies in source data, typically as part of the ETL process.
Mining. Datawareh
ousing System generated, artificial primary keys in alternative to the natural keys like SSN etc. Datawareh
ousing Name a few data modelling tools. Datawareh
ousing
Views storing pre-computed results are called materialized views.
Datawareh
ousing Can you insert into materialized views?
What is ODS (Operational Data Store), DSS (Decision support System), Data Staging Area, Data Presentation Area.
47
49 average/simpleSCD Types
6 simple what is a Hypercube? A means of visually representing multidimensional data.
11 complex/average Partition, aggregation, indexing..
Explain slice and dice ? What is Market-Basket analysis?
DataWareh
ousing Type 1 : Keep Most recent Values in TargetType 2 : Keep a full history of changes in the target
Type 3 : Keep the current and previous values in the target. DataWareh
ousing DataWareh
ousing Explain the performance improvement techniques in DW?
To slice and dice is to break a body of information down into smaller parts or to examine it from different viewpoints so that you can understand it better. This term can be compared to drill down, which is the process of dividing an information area up into finer and finer layers in a hierarchy, but with the purpose of narrowing in to one small area or item.