Shared Flashcard Set

Details

Oracle Interview Questions
Oracle Interview Questions
22
Computer Science
Professional
08/07/2011

Additional Computer Science Flashcards

 


 

Cards

Term
What is RDBMS?
Definition
Relational Data Base Management Systems (RDBMS)
Term
What is normalization?
Definition

Database normalization is a data design and organization process applied to data structures based on

rules that help build relational databases. In relational database design, the process of organizing data

to minimize redundancy.

Term
What is Stored Procedure?
Definition

A stored procedure is a named group of SQL statements that have been previously created and stored

in the server database.

Term
What is Trigger?
Definition

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE)

occurs.

Term
What is View?
Definition

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as

updating or deleting rows.

Term
What is Index?
Definition

An index is a physical structure containing pointers to the data. Indices are created in an existing table

to locate rows more quickly and efficiently.

Term
What is the difference between clustered and a non-clustered index?
Definition
Take the example of a phone book. The actual data - that is, the name, address and phone number records - is ordered by the name. If you want to look up Joe Bloggs's phone number, you open the book somewhere near the middle, maybe see the names there start with "M", but "Bloggs" is before "M", so you go a bit earlier in the book. You keep narrowing it down until you find the entry labelled Bloggs, and that's it - all the data for that record is right there. That's a bit like a clustered index.

On the other hand, a book might have a table of contents, sorted alphabetically. If you want to find out about llamas, you search the contents for llamas, which probably then gives you a page number, at which point you go to the page, and there's the data about llamas. The difference here is that you've had to do an extra bit of indirection - following the page number pointer - in order to get to the data. You can probably now see that while you can have as many tables of contents, ordered in any way you like, one set of data can only be physically arranged in one way. This means you can have many non-clustered indexes, but only one clustered index on a table.
Term
What is cursors?
Definition
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis
Term
What's the difference between a primary key and a unique key?
Definition

Both primary key and unique enforce uniqueness of the column on which they are defined. But by

default primary key creates a clustered index on the column, where are unique creates a nonclustered

index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key

allows one NULL only.

Term

How to implement one-to-one, one-to-many and many-to-many relationships while

designing tables?

Definition

One-to-One relationship can be implemented as a single table and rarely as two tables with primary

and foreign key relationships.

One-to-Many relationships are implemented by splitting the data into two tables with primary key and

foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables

forming the composite primary key of the junction table.

Term
What is difference between DELETE & TRUNCATE commands?
Definition

Delete command removes the rows from a table based on the condition that we provide with a WHERE

clause. Truncate will actually remove all the rows from a table and there will be no data in the table

after we run the truncate command.

Term
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Definition
Having specifies a search condition for a group or an aggregate.  Where is much more specific and works for select, delete, update etc.  Having only works with select.
Term
What is sub-query? Explain properties of sub-query.
Definition
A query within a query.
Term
What is @@ERROR?
Definition

The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there

was no error, @@ERROR returns zero.

Term
What are primary keys and foreign keys?
Definition

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be

null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys

and constraints. A table can have only one Primary key.

Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship

between tables.

Term
What is data integrity? Explain constraints?
Definition
Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.
Term
How to get @@error and @@rowcount at the same time?
Definition

If @@Rowcount is checked after Error checking statement then it will have 0 as the value of

@@Recordcount as it would have been reset.

Term
What is Identity?
Definition

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and

increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers,

the value of this cannot be controled. Identity/GUID columns do not need to be indexed.

Term
How do you load large data to the SQL server database?
Definition

BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to

Imports a data file into a database table or view in a user-specified format.

Term
What is an inner join?
Definition
Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate.
Term
What is an outer join?
Definition
It returns all of the table on the left for a left join, and then all of the right table that match the conditional clause.  Vice versa for right outer join.
Term
What is a cross join?
Definition
It will combine the two tables and create rows for each permutation of the two rows.
Supporting users have an ad free experience!