Question { SigmaTree, 6535 }

What are the other commands to know the structure of table
using MySQL commands except explain command?

Answer

show create table tbl_name;


eg:- show create table tbl_data;

Question { Tesco, 40990 }

what are the main differences between MyISAM and InnoDB
table storage structures ?
what are the advantages and disadvantages in usage of these ?

Answer

There is foolowing Differences between MYISAM and InnoDB
ENGINE:-

1)MYISAM does not support the foreign key constraint and
transaction but InnoDB support it.

2)MYISAM is faster then the InnoDB but in case of perforing
the count operation it takes more time then the InnoDB.

3) MYISAM occupies less memory sapce for tables rather than
InnoDB tables.

Question { Cognizant, 22304 }

how to retrive only second row from table in sql server
2000?

Answer

select top 1 * from(select top 2 * from tbl_test order by 1
asc) as at order by 1 desc

Question { 23898 }

How to find out the second largest element from mysql table

Answer

select marks from tbl_data where marks<(select max(marks)
from tbl_data) order by marks desc limit 1;

Question { 7726 }

1.what is the purpose of using alias name in sql server?
with certain example?

2.what is cursor?will u plz explain me in detail with
certain example?

Answer

Cursor:- Cursor is special data type of mssql server.
Cursor is mainly used for extracting the row by row data
from tables.
cursor is the only data type that can not be used in table.
Cursor is mainly used in stored procedure and when we have
to use cursor in SP need to perform the following steps:-
1)declaring a cursor.
2)Open a Cursor
3) fetching the cursor
4)close the cursor.

Question { 4815 }

can you any body tell me the how to decrease the logfile

Answer

We have to decrease the size of a log file by shrinking the
large file.

Syntex:-
DBCC dbname
go
DBCC shrinkfile(filename,sizevalue)
go.
for eg I want to shrink the filename data of databse userdb
to 7 mb.

dbcc userdb
dbcc shrinkfile(data,7)

Question { IBM, 16938 }

I Have Employee table having column name as ID,SALARY
how to get second max salary from employee table with id
ex
ID SALARY
1 20000
7 37000
2 5000

Answer

select top 1 id,salary from(select top 2 id,salary from
employee group by id,salary order by salary desc)a order by
salary asc

Question { 7164 }

What is a materialized view?

Answer

**A materialized view provides indirect access to table data
by storing the results of a query in a separate schema
object. Unlike an ordinary view, which does not take up any
storage space or contain any data.
**You can define a materialized view on a base table,
partitioned table or view and you can define indexes on a
materialized view.
**Materialized views are schema objects that can be used to
summarize, precompute, replicate, and distribute data. E.g.
to construct a data warehouse

Question { 8599 }

What is Covering Indexes?
Plz explain with example

Answer

An index covers the query if all the columns specified in
the query are part of the index. These are the columns that
are all columns referenced in the query, not just columns in
a WHERE clause.

eq:-SELECT DISTINCT lower(orig_airport) FROM Flights

here orig_airport is a index column.

Question { IBM, 79708 }

what is Constraint? How many types of constraints in SQL ?

Answer

Constraints are the way by Databse managing the data inegrity.

Types of constraint are:-
1)NOT Null
2)Default
3)Check
4)Rule
5)Primary
6)Unique
7)Foreign