Data: Data is collection of raw facts, figures, audio, text or any representation of real world which has some implicit or hidden meaning.

Structure: Structure is the arrangement of the basic components or building blocks which is representation for laying the foundation of the system. It also gives relationship or ordering of all its building blocks.

Data Structures: Data Structures is an unordered set which is responsible for storing the data in convenient way in computer memory. It provides the following facilities to the user.

Define Data Structures: It enables the users to define basic data structures definition by using the mechanism of programming language.

Insertion: It is a basic operation of the Data Structures, which provides mechanism to add an extra element to existing the data structure such that the properties definition restriction of data structure remains conserve.

Search: It is the basic operation of data structure which returns the address of element if it is present in the data structures and it returns a null pointer if the element is not present in the data structure. In doing so search operation explored the underline property of data structures in order to minimized search time.

Deletion: It is an operation of data structure which requires searching for the element. Deleting, deallocating the particular element occupy memory such that the underlying properties definition of data structures remains conserved.

Used of Data Structures:
A Data Structures finds used it in many situations where essential principles of  Computer Science need to be implemented.

In Design of Operating System:
            We requires data structure like
·         Linked List data structures for Process Control Block
·         Priority queue data structures for implementing priority scheduling
·         First in first out queue also known as pipe data structure to perform first come first serve CPU Scheduling of processors etc.
In Computer Network:
            We make use of data structure to establish a connection from source node to destination node in connection oriented services the data structure used is FIFO Queue.

In Microprocessor:  
            Every microprocessor make used of a stack data structure for
o   Expression inter convention form
Postfix ßà Prefix ßà Infix
o   Expression Evaluation
o   Implied Mode:
In implied mode one of the ways is to make used of stack and data or instruction on top of stack is implied to be access need.

In General:
            In general a specific kind of data structure can be used where real world problem is solved using computer.
o   Sorting: It makes used of data structure known as array.
o   Matrix Operation: Like two dimensional arrays.
o   Searching :   Which makes used of an array in case of linear search or a sorted array or a binary search in case of  binary search.
Data Structures
   
 HTML is the language used on the World Wide Web. It is the language recommended by the W3C. The World Wide Web Consortium (W3C) is the main international standards organization for the World Wide Web (abbreviated WWW or W3).

  HTML stands for Hyper Text Markup Language.  A markup language is a set of markup tags. A HTML markup tags is a keyword enclosed in angle brackets. HTML uses markup tags to describe web pages.

  The HTML tags are also called as elements. HTML tags are elements normally comes in pairs like <b> and </b>. The first tag in the pair is the start tag and the second tag is end tag. Some HTML tags have empty content. Empty  tags are closed in the start tag <br/>. Most HTML tags can be nested i.e. they can contain other HTML tags.

Eg: <p><b>This is nested tag</b></p>

  Many HTML tag have attributes, Attributes provides additional information about the element. Attributes are always specified in the start tag. Attributes comes in name and value pairs like name=”Value”.

Eg: <body bgcolor=”green”>

  HTML is not case sensitive that means <p> and <P> will be rendered the same by the web browser. It is better to use lowercase tags because W3C recommends lowercase in HTML 4, and demands lowercase tags in future version of (X) HTML.

  To create a web page a plain text editor (like Notepad) can be used. For a beginner this is the best way to learn HTML. However, Professional web developers often prefer HTML editors like FrontPage or Dreamweaver, instead of writing plain text. While saving an HTML file, either the .htm or the .html extension can be used. To view the web page any web browser like the Internet Explorer or Mozilla Firefox can be used.
There are a number of RDBMS packages available. These programs vary in power,
flexibility, and price. However, they all work in essentially the same way.


  • It is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.
  •  It uses a standard form of the well-known SQL data language.
  •  It is released under an open-source license.
  •  It works on many operating systems and with many languages.
  •  It works very quickly and works well even with large data sets.
  • PHP ships with a number of functions designed to support MySQL databases.
RSS (RDF Site Summary, Really Simple Syndication) feeds are a common use
of XML. RSS is an XML vocabulary to describe news items, which can then be
integrated (also called content syndication) into your own web site. PHP.net
has an RSS feed with the latest news items at http://www.php.net/news.rss.
You can find the dry specs of the RSS specification at http://web.resource.org/
rss/1.0/spec, but it’s much better to see an example. Here is part of the RSS file
we’re going to parse:

<?xml version="1.0" encoding="UTF-8"?>
<rdf:RDF
xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"
xmlns="http://purl.org/rss/1.0/"
xmlns:dc="http://purl.org/dc/elements/1.1/"
>
<channel rdf:about="http://www.php.net/">
<title>PHP: Hypertext Preprocessor</title>
<link>http://www.php.net/</link>
<description>The PHP scripting language web site</description>
<items>
<rdf:Seq>
<rdf:li rdf:resource="http://qa.php.net/" />
<rdf:li rdf:resource="http://php.net/downloads.php" />
</rdf:Seq>
</items>
</channel>
<!-- RSS-Items -->
<item rdf:about="http://qa.php.net/">
<title>PHP 4.3.5RC1 released!</title>
<link>http://qa.php.net/</link>
<description>PHP 4.3.5RC1 has been released for testing. This is
➥the first release candidate and should have a very low number
➥of problems and/or bugs. Nevertheless, please download and test
➥it as much as possible on real-life applications to uncover any
➥remaining issues. List of changes can be found in the NEWS
➥file.</description>
<dc:date>2004-01-12</dc:date>
</item>
<item rdf:about="http://www.php.net/downloads.php">
<title>PHP 5.0 Beta 3 released!</title>
<link>http://www.php.net/downloads.php</link>
<description>PHP 5.0 Beta 3 has been released. The third beta of
➥PHP is also scheduled to be the last one (barring unexpected
➥surprises). This beta incorporates dozens of bug fixes since
➥Beta 2, better XML support and many other improvements, some
➥of which are documented in the ChangeLog. Some of the key
➥features of PHP 5 include: PHP 5 features the Zend Engine 2.
➥XML support has been completely redone in PHP 5, all
➥extensions are now focused around the excellent libxml2
➥library (http://www.xmlsoft.org/). SQLite has been bundled
➥with PHP. For more information on SQLite, please visit their
➥website. A new SimpleXML extension for easily accessing and
➥manipulating XML as PHP objects. It can also interface with
➥the DOM extension and vice-versa. Streams have been greatly
➥improved, including the ability to access low-level socket
➥operations on streams.<description><dc:date>2003-12-21<
➥dc:date>
</item>
<!-- / RSS-Items PHP/RSS -->
</rdf:RDF>

This RSS files consists of two parts: the header, describing the site from
which the content is syndicated, and a list of available items. The second part
consists of the news items. We don’t want to refetch the RSS file from http://
php.net every time a user visits a page that displays this information. Thus,
we’re going to add some caching. Downloading the file once a day should be
sufficient because news isn’t updated more often than daily. (On php.net, other
sites might have different policies.)

We’re going to use the PEAR::XML_RSS class that we installed with pear
install XML_RSS. Here is the script:

<?php
require_once "XML/RSS.php";
$cache_file = "/tmp/php.net.rss";

First, as shown previously, we include the PEAR class and define the location
of our cache file:

if (!file_exists($cache_file) ||
(filemtime($cache_file) < time() - 86400))
{
copy("http://www.php.net/news.rss", $cache_file);
}

Next, we check whether the file has been cached before and whether the
cache file is too old (86,400 seconds is one day). If it doesn’t exist or is too old,
we download a new copy from php.net and store it in the cache file:

$r =& new XML_RSS($cache_file);
$r->parse();

We instantiate the XML_RSS class, passing our RSS file, and call the
parse() method. This method parses the RSS file into a structure that can be
fetched by other methods, such as getChannelInfo() that returns an array containing
the title, description, and link of the web site, as shown here:

array(3) {
["title"]=>
string(27) "PHP: Hypertext Preprocessor"
["link"]=>
string(19) "http://www.php.net/"
["description"]=>
string(35) "The PHP scripting language web site"
}

getItems() returns the title, description, and link of the news item. In the
following code, we use the getItems() method to loop over all items and display
them:

foreach ($r->getItems() as $value) {
echo strtoupper($value['title']). "\n";
echo wordwrap($value['description']). "\n";
echo "\t{$value['link']}\n\n";
}
?>

When you run the script, you will see that it outputs the news items from
the RSS file:

PHP 4.3.5RC1 RELEASED!
PHP 4.3.5RC1 has been released for testing. This is the first release
candidate and should have a very low number of problems and/or bugs.
Nevertheless, please download and test it as much as possible on real-life
applications to uncover any remaining issues. List of changes can be found
in the NEWS file.
http://qa.php.net/

PHP 5.0 BETA 3 RELEASED!
PHP 5.0 Beta 3 has been released. The third beta of PHP is also
scheduled to be the last one (barring unexpected surprises). This
beta incorporates dozens of bug fixes since Beta 2, better XML
support and many other improvements, some of which are documented in
the ChangeLog. Some of the key features of PHP 5 include: PHP 5
features the Zend Engine 2. XML support has been completely redone in
PHP 5, all extensions are now focused around the excellent libxml2
library (http://www.xmlsoft.org/). SQLite has been bundled with PHP.
For more information on SQLite, please visit their website. A new
SimpleXML extension for easily accessing and manipulating XML as PHP
objects. It can also interface with the DOM extension and vice-versa.
Streams have been greatly improved, including the ability to access
low-level socket operations on streams.

 Dynamic programming is a method for solving complex problems in maths and computer science in which large problems are broken down into smaller problems.

Through solving the individual smaller problems, the solution to the larger problem is discovered.

Stage – division of sequence of a system into various sub parts is called stages
State – a specific measurable condition of the system
Recursive equation – at every stage in dynamic programming the decision rule is determined by evaluating an objective function called recursive equation

Principle of optimality – it states that an optimal set of decisions rules has the property that regardless of the ith decisions, the remaining decisions must be optimal with respect to the outcome that results from the ith decision. This means that optimal immediate decision depends only on current state and not how you got there

The two basic approaches for solving dynamic programming are

  • Backward recursion
  • Forward recursion 
Backward Recursion

  • It is a schematic representation of a problem involving a sequence of n decisions
  • Then dynamic programming decomposes the problem into a set of n stages of analysis, each stage corresponding to one of the decisions. each stage of analysis is described by a set of elements decision, input state, output state and returns.

Forward Recursion 
  • This approach takes a problem decomposed into a sequence of n stages and analyzes the problem starting with the first stage in the sequence, working forward to the last stage it is also known as deterministic probability approach
Advantages

  • the process of breaking down a complex problem into a series of interrelated sub problems often provides insight into the nature of problem
  • Because dynamic programming is an approach to optimization rather than a technique it has flexibility that allows application to other types of mathematical programming problems
  • The computational procedure in dynamic programming allows for a built in form of sensitivity analysis based on state variables and on variables represented by stages
  • Dynamic programming achieves computational savings over complete enumeration.

Disadvantages

  • more expertise is required in solving dynamic programming problem then using other methods
  • lack of general algorithm like the simplex method. It restricts computer codes necessary for inexpensive and widespread use

Video



 

1.                   What is an Entity?

The basic data item stored in database is called entity. An entity can be any object, item, place, person, concept, or activity about which data is stored.

2.                  What is an attribute?

An attribute is a property of an entity. It describes a part of an entity. Entity could have one or more attributes.

3.                  What is ER diagram?

An Entity Relationship Diagram is diagrammatic representation of the logical structure of a database system.

4.                  Describe the concept of keys.

Candidate key
An attribute that uniquely identifies a row is called candidate key. It is also called das surrogate key.
Primary key
A candidate key that you choose to identify rows uniquely is called a primary key.
Alternate key
If there are multiple candidate keys in a table, the candidate keys that are chosen as primary key are called the alternate keys.
Composite key
When the key that uniquely identifies the rows of a table is made up of more than one attribute, it is called as a composite key.

Foreign key
Two tables can be related using a common attribute. When a primary key of one table is also available as an attribute in another related table it is called a foreign key.

5.                  What are joins?

Sometimes, data from multiple tables is to be displayed using select statement. For this purpose, the tables in use must have a column that is equated. This is termed as simple join or multiple join.

Sometimes, you might want to display all records from one table and some from another. This type of join is called an outer join. An outer join is only possible between two tables.

There are two types of outer join, namely left and right. In a left outer join, all the rows of the first table named in the FROM clause are displayed. In a right outer join all the rows from the second table mentioned in the FROM clause are displayed. In either case, all the matching rows from other table are displayed.

6.                  What is a sub query?

Sometimes the results of one query are dependant on the results of another query. For this purpose one query is nested inside another query, this is called as sub query.

7.                  What are the types of constraints?

You can enforce data integrity by using constraints. Constraints are divided in to five categories.

Primary key constraint

A primary key constraint is defined on a column are a set of columns whose values uniquely identify the rows in a table. It cannot contain null values.

Unique constraint

Unique constraints are used to enforce uniqueness on non-primary key columns. It allows null values but only one row can have a null value.
Multiple unique constraints can be created on a table.

Foreign Key constraint

You can use the foreign key constraint to remove the inconsistency in two tables when the data in the one table is dependant on the other table.
Check constraint

It enforces domain integrity by restricting the values to be inserted in a column. It is possible to define multiple check constraints on a single column. These are evaluated in the order in which they are defined.
Default Constraint

A default constraint can be use to assign a constant value to a column and the user need not insert values in to that column.

8.                  What is a rule?

The required integrity can be enforced by specifying a check constraint or by defining a rule. But check constraint modifies the table structure. The constraint can there fore be implemented using rules with out changing the table structure. This rule is applied before an insert or update statement .
A rule must be bound to a column or a user-define d data type. This is done using stored procedure sp_bindrule. Rules do not apply to data that has already been inserted in the table. The existing values in tables do not have to meet the criteria specified by the rule.

9.                  What are indexes?

To speed up data retrieval indexes are used. Indexes also enforce the uniqueness of rows.

Advantages

1.      Improves the speed of execution.

2.      Enforces uniqueness of data.

3.      Speeds up joins between tables.

Disadvantages

1.Takes disk space to store.

2.Data modification takes longer.

3.Takes time to create index.

Types of indexes

Clustered index

1.The data is physically sorted

2.One clustered index can be created per table, so you should build it on attributes that have a high percentage of unique values and that are not modified often.

Nonclustered index

1.The physical order of the rows is not the same as the index order.

2.There can be as many as 249 nonclustered indexes per column.

10.               What are views?

A view is a virtual table, which gives access to a subset of columns from one or more tables. It is a query stored as an object in the database. Hence a view is an object that derives it data from one or more tables.

Advantages

1.A view serves as a security mechanism.

2.A view simplifies the usage of complex queries.

11.                What are store procedures and its advantages?
A stored procedure is collection or batch of Transact-SQL statements and control flow language that is stored under one name, and executed as single unit. It helps in improving the performance of a query. It is a precompiled object. As it is ready to execute no time is needed for parsing and compiling the procedure.
Advantages
Improved performance – Applications do not have to compile the procedure over and over again.
Reduction in network congestion – applications need not submit multiple SQL statements to server for the purpose of processing.
Enhanced accuracy – SQL statements included in a procedure are designed by experienced programmers and are therefore more efficient, error free, and tested.
Better security mechanism – users can be granted permission to execute a stored procedure even if they do not own it.
Types of stored procedures
1.User-defined
2.System defined
These are prefixed with sp_. These are for administrative purpose and are stored in the database and are accessible to all users.
3.Temporary
These are prefixed with #, stored in tempdb and are automatically dropped when connection terminates.
4.Remote
These are created and stored in remote servers and can accessed by users with appropriate permissions.
5.Extended
These are dlls that are executed outside SQL Server. They are prefixed by xp_.

12.               Explain about BCP and DTS.

BCP

The transfer data from an external source to SQL Server is performed using Bulk Copy Program utility. The external source is a flat file. Data transfer from external source to SQL Server in BCP IN. The transfer of data from SQL Server to external source is BCP OUT.

DTS

Data transformation services can be used to import and export data between heterogeneous data sources and SQL Server. The external data sources include Visual FoxPro, MS Excel, Paradox, MS Access, Dbase, and text files.

 

13.               What are transactions and their properties?

A transaction is a sequence of operations performed together as a single unit of logical work. It has four properties.

Atomicity – it states that either all the data modifications are performed or none are performed.

Consistency - it states that all the data is in a consistent state after a successful completion of transaction.

Isolation – it states any data modification made my concurrent transactions must be isolated from the modifications made by other concurrent transaction.

 Durability – it states that ay change made by a completed transaction remains permanently in the system.

14.               Locking mechanism.

SQL Server uses the concept of locking to ensure transactional integrity and database consistency. Locking, by functionally prevents users from accessing information being changed by other users. In a multi-user environment, Locking prevents users from changing the same data at the same time. In SQL Server locking is implemented automatically.

SQL Server implements multi-granular locking, which allows transactions to lock different types of resources at different levels. SQL Server can lock the following resources.

RID – is a row identifier that individually locks a row in a table.
Key – is a rowlock with in an index.
Page – is an 8k page or an index page.
Extent – is a contiguous group of 8k data pages or index pages.
Table – is the entire table, including all data and indexes.
Database – is the complete database.
                            
Shared Locks

It is used for operations that do not change or update the data. This allows concurrent transactions to read a resource and no other transaction can modify the data on that resource.
Update Locks

This lock is implemented when a transaction modify a row. Only one update lock is allowed on a resource at a time.

Exclusive locks

Intent locks

Schema Locks

15.               What is deadlock?

A dead lock is a situation in which two users (or transactions) have locks on separate objects, and each user is waiting for a lock on the other’s object. It usually occurs in a multi-user environment.

16.               What are triggers?

A trigger is a block of code that constitutes with a set of T-SQL statements that are activated in response to certain actions. A trigger can also be interpreted as a special kind of stored procedure that is executed whenever an action, such as data modification, takes place.
A trigger is always defined on a table, and is said to have fired whenever the data in the underlying table is affected by any of the Data Manipulation Language (DML) statements-INSERT, UPDATE, or DELETE. A trigger fires in response to an event like insertion, updation, and deletion of data.
Triggers help in maintaining consistent, reliable, and correct data in tables. They enable the performance of complex actions and cascade these actions to other dependant tables.
Characteristics of a trigger:

·       It can be associated with tables.
·       It cannot be defined on temporary tables or views. However, it can reference temporary tables and views.
·       Whenever any data modification statement is issued then SQL Server fires it automatically.
·                         It cannot be explicitly invoked or executed, as in the case of stored procedures.
·         Triggers can be nested up to 16 levels. The nesting of triggers occurs when a trigger performs an action that initiates another trigger.
·       It prevents incorrect, unauthorized, and inconsistent changes in data.
·       It cannot return data to the user.

There are three types of triggers
1) INSERT
2) DELETE
3) UPDATE

17.               What is Normalization? Explain three normal forms?

Normalization is a scientific method of breaking down complex table structures in to simple table structures by using certain rules. Hence reduce redundancy in a table and eliminate inconsistency problems and disk space usage.

First Normal Form

A table is said to be in 1NF when each cell of the table contains precisely one value.

Functional dependency

If you have two attribute A and B, A is said to be functionally dependant on B, if for each value of B, there is exactly one value of A.

Second Normal Form

Identify the functionally dependent keys and place them in a different table.

Third Normal Form

A relation is said to be 3NF when every non-key attribute is functionally dependant only on the primary key.

18.               What are Cursors and types of cursors?

A cursor is a work area called private SQL area, which executes SQL statements and stores the results.

 

 

Cursor Types

So you can specify the four-cursor types for Transact-SQL cursors. These cursors vary in their ability to detect changes to the result set and in the resources, such as memory and space in tempdb, they consume. The four API server cursor types supported by SQL Server are:
  • Static cursors
  • Dynamic cursors
  • Forward-only cursors
  • Key set-driven cursors
Static cursors detect few or no changes but consume relatively few resources while scrolling, although they store the entire cursor in tempdb.
Dynamic cursors detect all changes but consume more resources while scrolling, although they make the lightest use of tempdb.
Key set-driven cursors lie in between, detecting most changes but at less expense than dynamic cursors.
Although the database API cursor models consider a forward-only cursor to be a distinct type of cursor, SQL Server does not. SQL Server considers both Forward only and scroll to be options that can be applied to static, key set-driven, and dynamic cursors.
19.               What is Encryption option in SQL Server?
Encryption is a method for keeping sensitive information confidential by changing data into an unreadable form. Encryption ensures that data remains secure by keeping the information hidden from everyone, even if the encrypted data is viewed directly. Decryption is the process of changing encrypted data back into its original form and so authorized users can view it.
20.              What are time-stamped data types?
It is a database-wide unique number. The storage size is 8 bytes. A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value, thereby changing the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.
21.               Correlated queries.
In correlated queries a sub query is executed for each row the parent query is executed.
Ex:
“Select e. * from EMP e where e.sal >(select Avg (Sal) from emp where e.deptno = emp.deptno  “
22.              What is OLAP and how it works with SQL Server?
OLAP Services is a new middle-tier server for online analytical processing (OLAP). OLAP Services provides wizards, editors, and information to make OLAP technology easier to use. OLAP Services supports various data and storage models to help you create and maintain an OLAP system that meets your organization’s needs.
25. What are the advantages of SQL Server 7.0 over SQL Server 6.5?
·         Trigger Enhancements
·         Recursive triggers
·         Multiple triggers per INSERT, UPDATE, or DELETE statement
·         Data Transformation Services
·         Web Assistant Wizard
The Web Assistant Wizard has been enhanced in SQL Server 7.0. In addition to exporting SQL Server data out to an HTML file, it can also import tabular data from an HTML file into SQL Server, and post to and read from HTTP and FTP locations.
·         Row-level locking
SQL Server 7.0 supports complete row-level locking on both data pages      and index pages.
·         SQL Server 7.0 supports applications that span a broad range of platforms.
·         Gigabytes of memory, and a terabyte or more of disk storage.
·         Stored Procedures
The stored procedure model has been enhanced in SQL Server 7.0 to provide improved performance and increased application flexibility. When a stored procedure is compiled and placed in the procedure cache, all users of the stored procedure share that one copy of the compiled plan.
·         The most notable feature is update replication. Using update     replication, data replicated by SQL Server 7.0 can be modified at multiple sites.

SQL-Server

Truncate & Delete

1.Truncate a table means data will be lost, but structure of the table will be present in the database.

Syntax: Truncate table <table name>;

2.If you drop a table then the structure of the table also goes along with the data.
Syntax: drop table <table name>;

3.You can’t delete a table; you can delete the data in a record wise manner.

Syntax: delete from <table name> where <condition>


4.The difference between the truncate and delete is you can delete the data at a stretch buy using the truncate, by using the delete you can delete only one record at a time.

5.When you have truncated the table then all the records will lose. Then immediately if you add a record to that table the row number will be ‘1’ only.

Index:


  1. If you have a clustered index on a column, then also you can assign a primary key to that column?

  1. You are having rowid already then why you are going for the index?

  1. How many non-clustered indexes can be allowed in a table?

Syntax:
Create index <index name>

On

<Table name (<col1>, <col2>, -------)>;

Syntax:
Drop index <index name>

Rules

9.Rules will maintain the domain integrity.

10.Is it possible to create a rule at the time of creating a table?
No.

11.Is it possible to drop a rule?
Yes.

12.What is the difference between the rule and check constraint?
If a check constraint is applied to a table it will enforce the constraint to previously existing data also. If that data violates the constraint then constraint statement gives an error message.

If a rule is applied on a table it will not apply to the already existing data in the table. The rule will be imposed from now on the updation and insertion of data.

Syntax

          Create rule <rule name> as <condition>
               Sp_bindrule <rule name>, <object name>
               Drop rule <rule name>

Keys


13.How many types of keys are there? What is the importance of those keys?
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               14.What is the difference between the primary key and unique?

15.What is the primary key and candidate key and what are there advantages?

16.Is it possible to hide the database?

17. What is the maximum size of a row?

18.What is the maximum number of rows can a table allowed? Then how many columns are allowed in a table?

19.What is normalization?

20.What is the redundancy?

21.What are the normalization forms?

22.What is the difference between the DBMS/RDBMS?

23.What is replication?

Replication:

Definition:

“Replication is an important and powerful technology for distributing data and the execution of stored procedures across an enterprise. “

The replication technology allows you to make duplicate copies of your data, move those copies to different locations, and synchronize the data automatically so that all copies have the same data values. Replication can be implemented between databases on the same server or different servers connected by LANs, WANs, or the Internet.

24.What are the types of replication?

Types of the replications:
  • Snapshot Replication
Option: Snapshot, with immediate-updating Subscribers
  • Transactional Replication
Option: Transactional, with immediate-updating Subscribers
  • Merge Replication
Which type of replication you choose for your application depends on your requirements for data consistency, site autonomy, and network resources.
25.Differences between the SQL-server 2000 and SQL-Server 7.0.

26.What is the sql using by the SQL-Server and oracle?

SQL-Server: Transact-SQL
Oracle: ANSI-SQL

28.Is it possible to create a view with out a base table?

Actually the answer is NO.
But this is possible by using the “FORCED VIEWS”
Syntax:
Create force view XYZ
As
Select * from ZZZ;

29.Index is the only second object, which holds data in the database.

30. “MODIFY” is a keyword only it is not a clause.
               
31.What will happen if you add a not null constraint on to a column if that column is already having the null values and what is the status of those null values, which are already present?

An error is given stating that null values are found.

32.I am doing a transaction, I am having four activities in those four fourth activity got some problem, then will be the transaction committed or roll backed?

Automatically it will rollback, because a transaction is said to be committed when all the activities of that transaction is perfectly executed.

Joins

33.What is a join?

34.How many types of joins are there?

35.What is the difference between the outer join and left outer join?

36.How to make a copy of a table (if you have a table with some name X, is it possible to create a table with a name Y by using the table X?

Explanation:

SQL server

Select <columns> into <new table> from <old table>


Oracle
Create table <table name> as select * from <table name>

Write a query to display the details of employees whose salary is more than the ‘BLAKE’ salary.

Sql> select B. * from EMP A, EMP B
Where A.ename=’BLAKE’
A.     Sal < B.sal;
It is also called aliasing.
It is a self-joining concept.

38.What is schema?

Schema:

A description of a database generated by the data definition language (DDL) of the database management system (DBMS). In OLAP Services, a schema is a description of multidimensional objects such as cubes, dimensions, and so forth.
39.Is it possible to create a trigger on multiple tables?

NO.

40.Is it possible to create a single stored procedure on multiple tables?

YES.


What is SQL and where does it come from?

Structured Query Language (SQL) is a language that provides an interface to relational database systems. IBM developed SQL in the 1970s for use in System R. SQL is a de facto standard, as well as an ISO and ANSI standard. SQL is often pronounced SEQUEL.

In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data Definition Language), used for creating and modifying tables and other database structures.

The development of SQL is governed by standards. A major revision to the SQL standard was completed in 1992, called SQL2. SQL3 support object extensions and will be (partially?) Implemented in Oracle8.

How can I eliminate duplicates values in a table?

Choose one of the following queries to identify or remove duplicate rows from a table:

Method 1:

   SQL> DELETE FROM table_name A WHERE ROWID > (
     2    SELECT min(rowid) FROM table_name B
     3    WHERE A.key_values = B.key_values);

Method 2:

   SQL> create table table_name2 as select distinct * from table_name1;
   SQL> drop table_name1;
   SQL> rename table_name2 to table_name1;

Method 3:

   SQL> Delete from  where rowid not in(
   SQL>     select max(rowid) from
   SQL>     group by  );

Method 4:

   SQL> delete from mytable t1
   SQL> where  exists (select 'x' from mytable t2
   SQL>                 where t2.key_value1 = t1.key_value1
   SQL>                   and t2.key_value2 = t1.key_value2
   SQL>                   and t2.rowid     != t1.rowid);

Note: If you create an index on the joined fields in the inner loop, you for all intensive purposes eliminate N^2 operations (no need to loop through the entire table on each pass by a record).


How can I generate primary key values for my table?

Create your table with a NOT NULL column (say SEQNO). This column can now be populated with unique values:

SQL> UPDATE table_name SET seqno = ROWNUM;
or use a sequences generator:

SQL> CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;

SQL> UPDATE table_name SET seqno = sequence_name.NEXTVAL;
Finally, create a unique index on this column.

How can I get the time difference between two date columns

select floor((date1-date2)*24*60*60)/3600)
       || ' HOURS ' ||
       floor((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600)/60)
       || ' MINUTES ' ||
       round((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600 -
       (floor((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
       || ' SECS ' time_difference from   ...

How does one count different data values in a column?

       select dept, sum(  decode(sex,'M',1,0)) MALE,
                     sum(  decode(sex,'F',1,0)) FEMALE,
                     count(decode(sex,'M',1,'F',1)) TOTAL
        from   my_emp_table
        group  by dept;

How does one select every n th row from a table?

Method 1: Using a subquery
        SELECT *
        FROM   emp
        WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
                             FROM   emp);
Method 2: Use dynamic views (available from Oracle7.2):
        SELECT *
        FROM   ( SELECT rownum rn, empno, ename
                 FROM emp
               ) temp
        WHERE  MOD(temp.ROWNUM,4) = 0;

How does one select the top n rows from a table?

        SELECT *
          FROM TableX a
         WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
                        FROM tableX b
                       WHERE b.maxcol >= a.maxcol)
         ORDER BY maxcol DESC;

How to implement if-then-else in a select statement?

The Oracle decode function acts like a procedural statement inside an SQL statement to return different values or columns based on the values of other columns in the select statement.

Example:
        select decode(sex, 'M', 'Male',
                           'F', 'Female',
                           'Unknown'
                     )
        from   employees;

Note: The decode function is not ANSI SQL and are rarely implemented in other RDBMS offerings. It is one of the good things about Oracle, but use it sparingly if portability is required.

How can one dump/ examine the exact content of a database column?

       SELECT DUMP(col1)
        FROM tab1
        WHERE cond1 = val1;

        DUMP(COL1)
        -------------------------------------
        Typ=96 Len=4: 65,66,67,32

For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.

Can one drop a column from a table?

Oracle does not provide a way to DROP a column.

1. update t1 set column_to_drop = NULL;
   rename t1 to t1_base;
   create view t1 as select <specific columns> from t1_base;
2. create table t2 as select <specific columns> from t1;
   drop table t1;
   rename t2 to t1;

Can one rename a column in a table?

No
1. rename t1 to t1_base;
   create view t1 <column list with new name> as select * from t1_base;
2. create table t2 <column list with new name> as select * from t1;
   drop table t1;
   rename t2 to t1;

How can I change my Oracle password?

Issue the following SQL command: ALTER USER <username> IDENTIFIED BY <new_password>
/
From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type "password user_name".

Workaround for snapshots on tables with LONG columns

You can use the SQL*Plus COPY command instead of snapshots if you need to copy LONG and LONG RAW variables from one location to another. Eg:

COPY TO SCOTT/TIGER@REMOTE     -
CREATE IMAGE_TABLE USING       -
       SELECT IMAGE_NO, IMAGE  -
       FROM   IMAGES;

Note: If you run Oracle8, convert your LONGs to LOBs, as it can be replicated.
Subscribe to: Posts (Atom)
Copyright © 2012 OpenTechZone | Kesari Technologies |