Improve Your Sql Knowledge

What is normalization? Explain different levels of normalization?

Check out the article Q100139 from Microsoft knowledge base and of
course, there's much more information available in the net. It'll be a
good idea to get a hold of any RDBMS fundamentals text book,
especially the one by C. J. Date. Most of the times, it will be okay
if you can explain till third normal form.

What is denormalization and when would you go for it?

As the name indicates, denormalization is the reverse process of
normalization. It's the controlled introduction of redundancy in to
the database design. It helps improve the query performance as the
number of joins could be reduced.

How do you implement one-to-one, one-to-many and many-to-many
relationships while designing tables?

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.

It will be a good idea to read up a database designing fundamentals
text book.

What's the difference between a primary key and a unique key?

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.

What are user defined datatypes and when you should go for them?

User defined datatypes let you extend the base SQL Server datatypes by
providing a descriptive name, and format to the database. Take for
example, in your database, there is a column called Flight_Num which
appears in many tables. In all these tables it should be varchar(8).
In this case you could create a user defined datatype called
Flight_num_type of varchar(8) and use it across all your tables.

See sp_addtype, sp_droptype in books online.

What is bit datatype and what's the information that can be stored
inside a bit column?

Bit datatype is used to store boolean information like 1 or 0 (true or
false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0
and there was no support for NULL. But from SQL Server 7.0 onwards,
bit datatype can represent a third state, which is NULL.

Define candidate key, alternate key, composite key.

A candidate key is one that can identify each row of a table uniquely.
Generally a candidate key becomes the primary key of the table. If the
table has more than one candidate key, one of them will become the
primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called
composite key.

What are defaults? Is there a column to which a default can't be bound?

A default is a value that will be used by a column, if no value is
supplied to that column while inserting data. IDENTITY columns and
timestamp columns can't have defaults bound to them. See CREATE
DEFUALT in books online.

Back to top
SQL Server architecture (top)

What is a transaction and what are ACID properties?

A transaction is a logical unit of work in which, all the steps must
be performed or none. ACID stands for Atomicity, Consistency,
Isolation, Durability. These are the properties of a transaction. For
more information and explanation of these properties, see SQL Server
books online or any RDBMS fundamentals text book.

Explain different isolation levels

An isolation level determines the degree of isolation of data between
concurrent transactions. The default SQL Server isolation level is
Read Committed. Here are the other isolation levels (in the ascending
order of isolation): Read Uncommitted, Read Committed, Repeatable
Read, Serializable. See SQL Server books online for an explanation of
the isolation levels. Be sure to read about SET TRANSACTION ISOLATION
LEVEL, which lets you customize the isolation level at the connection
level.

CREATE INDEX myIndex ON myTable(myColumn)

What type of Index will get created after executing the above statement?

Non-clustered index. Important thing to note: By default a clustered
index gets created on the primary key, unless specified otherwise.

What's the maximum size of a row?

8060 bytes. Don't be surprised with questions like 'what is the
maximum number of columns per table'. Check out SQL Server books
online for the page titled: "Maximum Capacity Specifications".

Explain Active/Active and Active/Passive cluster configurations

Hopefully you have experience setting up cluster servers. But if you
don't, at least be familiar with the way clustering works and the two
clusterning configurations Active/Active and Active/Passive. SQL
Server books online has enough information on this topic and there is
a good white paper available on Microsoft site.

Explain the architecture of SQL Server

This is a very important question and you better be able to answer it
if consider yourself a DBA. SQL Server books online is the best place
to read about SQL Server architecture. Read up the chapter dedicated
to SQL Server Architecture.

What is the difference between Oracle, SQL and PL/SQL?

What is the difference between Oracle, SQL and PL/SQL?

The article is very interesting. Too Good! And also please describe what is the difference between SQL and Oracle and PL/SQL.
Sudhat, thanks for the comments. I love to hear feedback.
And your question is a common one so I thought I would address it as a blog entry instead of a reply to your comment. I'm going to expand on it a little bit also. In addition to SQL and PL/SQL, I hear people refer to SQL commands so I am going to address the question, "What is the difference between SQL, SQL Commands and PL/SQL.

What is Oracle?

I cover this answer in the entry, Learn Oracle - What is Oracle?. I also cover SQL and PL/SQL very briefly in that article. I'll try to be a little more explicit in this entry.

What is SQL?

SQL is the Structured Query Language. Actually, I think I covered SQL pretty nicely in the article Sudhat commented on, Intro to basic SQL.

I'll just add that SQL is composed of DML and DDL. DML are the keywords you use to access and manipulate data, hence the name Data Manipulation Language. DDL are the keywords you use to create objects such as views, tables and procedures, hence the name Data Definition Language. Examples of DML are SELECT, UPDATE, INSERT, MERGE, DELETE, etc. Examples of DDL are CREATE TABLE, ALTER VIEW, CREATE OR REPLACE PROCEDURE, etc.

Example DDL:

CREATE TABLE whatever ( column1 NUMBER NOT NULL PRIMARY KEY, column2 VARCHAR2(10), column3 DATE );
Example DML:

INSERT INTO whatever (column1, column2, column3) VALUES (1, 'Text Data', sysdate ); SELECT column1, column2, column3 FROM whatever; COMMIT;

What are SQL Commands?

SQL commands are commands that aren't actually a part of the SQL standard but are supported by the tools that support SQL. For example, SQL*Plus has been around a long time and many tools that allow scripting also allow SQL*Plus commands. When someone asks me about a SQL command, I first clarify if the mean a SQL keyword or a SQL*Plus (or some other tool) command.

SQL commands are usually meant to help format output: BREAK, BTITLE, COLUMN, PRINT or they are meant to create or store data or scripts: COMPUTE, DEFINE, STORE, SAVE. There are also commands that interact with the database: SHUTDOWN, CONNECT, COPY. And there is at least one that interacts with data: XQUERY.

So when you hear someone refer to a SQL command, first ask if they really mean SQL or if they mean the SQL tool they are using.

What is PL/SQL?

The quick answer is from the PL/SQL User Guide:

PL/SQL, Oracle's procedural extension of SQL, is an advanced fourth-generation programming language (4GL). It offers software-engineering features such as data encapsulation, overloading, collection types, exceptions, and information hiding. PL/SQL also supports rapid prototyping and development through tight integration with SQL and the Oracle database.

But what does that mean? The key here are the words: procedural extension of SQL. PL/SQL is a procedural language like C++, Java, ADA, etc. If has variables and variable declarations, conditional controls like IF and CASE. It has looping structures such as LOOP, FOR LOOP and the WHILE LOOP. PL/SQL uses SQL to use, manipulate and save data to the database.

If I wanted to create my own, very short, definition of PL/SQL it would be this: PL/SQL is the Oracle native programming language that provides database-centric application development. It can natively call static SQL and provides multiple methods of calling dynamic SQL.

Example PL/SQL:

BEGIN -- A PL/SQL cursor FOR c1 IN ( SELECT * FROM whatever ) -- This is SQL called by PL/SQL LOOP DBMS_OUTPUT.PUT_LINE( 'Column1 is: ' || TO_CHAR(c1.column1) || ', Column2 is: ' || c1.column2 || ', Column3 is: ' || TO_CHAR(c1.column3 ) ); END LOOP; END;

And now the answer to the main question: what is the difference between SQL and PL/SQL? SQL is a data oriented language for selecting and manipulating sets of data. PL/SQL is a procedural language to create applications. You don't normally have a "SQL application". You normally have an application that uses SQL and a relational database on the back-end. PL/SQL can be the application language just like Java or PHP can. SQL may be the source of data for your screens, web pages and reports. PL/SQL might be the language you use to build, format and display those screens, web pages and reports.

Think of it like this: The code that makes your program function is PL/SQL. The code that manipulates the data is SQL DML. The code that creates stored database objects is SQL DDL. DDL compiles the code that is written in PL/SQL. PL/SQL may call SQL to perform data manipulation. The commands that format the output of a tool are not related to the SQL standard or to PL/SQL.

I hope that clears it up some!, I may continue the series.

Take care

download it here.

for further queries

Email me shraddha_garg@rediffmail.com

SQL Tutorial

SQL (Structured Query Language) is a computer language aimed to store, manipulate, and retrieve data stored in relational databases. The first incarnation of SQL appeared in 1974, when a group in IBM developed the first prototype of a relational database. The first commercial relational database was released by Relational Software (later becoming Oracle).

Standards for SQL exist. However, the SQL that can be used on each one of the major RDBMS today is in different flavors. This is due to two reasons: 1) the SQL standard is fairly complex, and it is not practical to implement the entire standard, and 2) each database vendor needs a way to differentiate its product from others. In this tutorial, such differences are noted where appropriate.

This SQL programming help site lists commonly-used SQL statements, and is divided into the following sections:

• SQL Commands: Basic SQL statements for storing, retrieving, and manipulating data in a relational database.
• Table Manipulation: How SQL statements are used to manage tables inside the database.
• Advanced SQL: Advanced SQL commands.
• SQL Syntax: A single page that lists the syntax for all the SQL commands in this tutorial.

For each command, the SQL syntax will first be presented and explained, followed by an example. By the end of this tutorial, you should have a good general understanding of the SQL syntax, and be able to write SQL queries using the correct syntax. My experience is that understanding the basics of SQL is much easier than mastering all the intricacies of this database language, and I hope you will reach the same conclusion as well.

If you are interested in how to retrieve data using SQL, we recommend that you start with the SQL Commands section. If you are interested in understanding how SQL can be used to manipulate database tables, we recommend that you start with the Table Manipulation section. If you are looking for help on a specific SQL command, you can use the Site Map to find the command you are looking for.

Okay, enough introduction. Bookmark this site now and start now to learn SQL!