Microsoft Sql Server2000

To connect to a remote database server from local.

In Ms sql server , clik on enterprise manager. In the console you will have the databases listed in the tree structure.In that tree Right-Click on the "SQL Server Group" item.In the poup menu click "New sql Server registration".It will open a wizard.In the Welcome wizard press Next. Then another wizard appears asking to select a server from the available server.If you dont see one , just type the ip address and add it.On clicking Next, You will need to set authentication mode in another wizard, then you need to selectsql server group.Atlast Finish.After the process succeeds, you can see the database added to your existing database.To be more precise, it is not only the database, but also one among the "SQL Server Group". So any changes made to this database , will be affected in the server.

If you want to import the database for local testing alone, then follow the below procedure.

First create a database with the name of the database that you are going to import.Then Right-click on the local "SQL Server Group" and click on "All Tasks".In that select "Import Data". Then you will be going through a set of wizards,that asks for input.In the "Import/Export" wizard click Next.Then in "Data Source" wizard, specify the server address, then choose appropriate authentication and then choose the database to be imported.Then in the "Destination" wizard , choose the "Local" system as server, give the appropriate authentication and then choose the database you created in the local system.Then in the "table copy or query" wizard , choose "copy objects and data between sql server databases" option.Then click on Next in the following wizard. A copy of the database will be copied from the server to the local one.

Mysql5.0 database import export

Recently i faced a issue of migrating a database and its content between MySql5.0 servers.In order to migrate i need to export the database contents from one server and import it into the other from the command prompt.This is how it is done.

To export:

In the command prompt go to the bin directory of MySql5.0,and enter the following command.

mysqldump --user="username" --password="userpassword" databasename>filename.sql

If you get an error saying access denied or something like that, replace your username with the default one(root) in the above command .

Thats it , you will get a .sql file in the bin directory with the name mentioned in the command line.

To import:

In order to import , place that .sql file in the bin directory of the other MySql5.0 and enter the following command.

mysql --user="username" --password="userpassword" databasename < filename.sql

Thats it. But do remember to create a database with the name you specify in the command line.

Joins

Types of Joins :

EQUIJOINS or INNERJOIN
NATURAL JOINS
NON-EQUIJOINS
OUTER JOINS
SELF JOINS


EQUIJOINS or INNERJOIN :
The EQUIJOIN joins two tables with a common column in which each is usually the primary key.

The syntax for an EQUIJOIN is

SELECT TABLE1.COLUMN1, TABLE2.COLUMN2...FROM TABLE1, TABLE2 [, TABLE3 ]WHERE TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME[ AND TABLE1.COLUMN_NAME = TABLE3.COLUMN_NAME ]

NATURAL JOINS :

A NATURAL JOIN is nearly the same as the EQUIJOIN; however, the NATURAL JOIN differs from the EQUIJOIN by eliminating duplicate columns in the joining columns. The JOIN condition is the same, but the columns selected differ.

The syntax is as follows:

SELECT TABLE1.*, TABLE2.COLUMN_NAME [ TABLE3.COLUMN_NAME ]FROM TABLE1, TABLE2 [ TABLE3 ]WHERE TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME[ AND TABLE1.COLUMN_NAME = TABLE3.COLUMN ]

NON-EQUIJOINS :

NON-EQUIJOIN joins two or more tables based on a specified column value not equaling a specified column value in another table.

The syntax for the NON-EQUIJOIN is

SELECT TABLE1.*,TABLE2.COLUMN_NAME FROM TABLE1, TABLE2 [, TABLE3 ]WHERE TABLE1.COLUMN_NAME != TABLE2.COLUMN_NAME[ AND TABLE1.COLUMN_NAME != TABLE2.COLUMN_NAME ]

OUTER JOINS :

An OUTER JOIN is used to return all rows that exist in one table, even though corresponding rows do not exist in the joined table.In many implementations, the OUTER JOIN is broken down into joins called

LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

LEFT OUTER JOIN

It returns all rows that exist in the table that is to the left of the join, even though corresponding rows do not exist in the joined table that is to the right of the join.

RIGHT OUTER JOIN

It returns all rows that exist in the table that is to the right of the join, even though corresponding rows do not exist in the joined table that is to the left of the join.

FULL OUTER JOIN

It returns only the rows that has the corresponding match in both tables.

SELF JOINS

The SELF JOIN is used to join a table to itself, as if the table were two tables, temporarily renaming at least one table in the SQL statement. Self joins are useful when all of the data you want to retrieve resides in one table, but you must somehow compare records in the table to other records in the table.

The syntax is as follows:

SELECT A.LAST_NAME, B.LAST_NAME, A.FIRST_NAMEFROM EMPLOYEE_TBL A, EMPLOYEE_TBL BWHERE A.LAST_NAME = B.LAST_NAME;

To understand with examples Click Here

categories of the data integrity

Entity Integrity ensures that there are no duplicate rows in a table.

Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values.

Referential integrity ensures that rows cannot be deleted, which are used by other records (for example, corresponding data values between tables will be vital).

User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories.

Difference between Primary Key and Unique Key

A primary key is a special case of unique keys. The major difference is that for unique keys the implicit NOT NULL constraint is not automatically enforced, while for primary keys it is. Thus, the values in a unique key columns may or may not be NULL.

Point to Remember :

Cannot set primary key constraint to a nullable column.
Cannot drop a column when it has the primary key constraint.
A table cannot have more than one primary key.

Constraints

A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.

Some of the constraints are

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

create table table_name(column_name1 type not null primary key);

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

create table table_name(column_name1 type unique);

A FOREIGN KEY constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce
referential integrity.

create table table_name(column_name1 type not null foreign key references reference_table_name(primary_column_name));

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

create table table_name(column_name1 type constraint check_columnname check (column_name > 10));

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

create table table_name(column_name1 type not null);

A Candidate key uniquely identifies rows in a table. Any of the identified candidate keys can be used as the table's primary key. Any of the candidate keys that is not part of the primary key is called an Alternate key. One can describe a Candidate Key as a Super Key that contains only the minimum number of columns necessary to determine uniqueness.

nth highest or lowest value in sql

To fetch the nth highest or lowest value in sql.

For example To fetch the 4th highest value , replace 1 by 4 in the below query.

select * from test e where 1=(select count(distinct sal) from test where e.sal>=sal);

To fetch the 4th lowest value , replace 1 by 4 and replace the '>' with '<' in the above query.

Normalization

what is Normalization?

It's the process of efficiently organizing data in a database.

Goals of the Normalization:

1.Eliminate redundant data
2.Ensure data dependencies make sense

The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF).

In practical applications, you'll often see 1NF, 2NF, and 3NF along with the occasional 4NF.
Fifth normal form is very rarely seen.

First normal form (1NF) sets the very basic rules for an organized database:

* Eliminate duplicative columns from the same table.
* Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

for examples click here

Second normal form (2NF) further addresses the concept of removing duplicative data:

* Meet all the requirements of the first normal form.
* Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
* Create relationships between these new tables and their predecessors through the use of foreign keys.

for examples click here

Third normal form (3NF) goes one large step further:

* Meet all the requirements of the second normal form. * Remove columns that are not dependent upon the primary key.

for examples click here

Finally, fourth normal form (4NF) has one additional requirement:

* Meet all the requirements of the third normal form.
* A relation is in 4NF if it has no multi-valued dependencies.

Second Max value - query

Its often asked in interviews , "Write a query to retrive the second maximum salary from a table." , especially when we are questioned about Databases.Here is a solution...

Select max(salary) from tablename where salary not in(select max(salary) from tablename);

or

select max(salary) from tablename where salary<(select max(salary) from tablename);