State Creating tables syntax with an example.
SQL server tables are used to store data. Each table is divided into rows and columns. Each row has some data against the specified column. Each column has a data type thereby restricting the type of data it can accept.
Syntax:CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )
Example:CREATE TABLE Customer
(ID Integer (10),
First_name Varchar(20));
State syntax of Inserting data into tables with an example.
Insert : Insert statement is used to insert data (row) in a table.
Syntax:Insert into table_name
Values (value1, 2 ..)
Example:Insert into customer values (1,’steve’,’james’);
What are the different tables present in MySQL? Explain each of them.
1. MyISAM - This is the default table based on sequential search. It provides a high speed storage and data retrieval.
2. HEAP - Used for a faster data access. HEAP table cannot have varied data types. They use hash indexes by default, which makes them very fast, and very useful for creating temporary tables. Crash recovery is not possible.
3. BDB(BerkelyDb) - Supports transactions using COMMIT and ROLLBACK. Relatively slow. They have a greater chance of surviving crashes.
4. Example - Serves as an example of how to begin writing new storage engines. When an example table is created the server creates a table format file in the database directory. The file begins with the table name and has an .frm extension.
5. InnoDb - Has all COMMIT, ROLLBACK and crash recovery mechanisms. Increased performance. InnoDb also supports Foreign key constraints.
How can we repair a MySQL table?
REPAIR TABLE syntax in sql can be used to repair tables that are corrupted. It is also used to repair MYISAM and ARCHIVE tables as well.
Syntax:Tbl_name
[QUICK] [EXTENDED] [USE_FRM]
Quick : Repairs the index tree only.
Extends : MySQL creates the index row by row instead of creating one index at a time with sorting
Use_frm : option tells MySQL not to trust the information in the .MYI file header and to re-create it using information from the .frm file.
What are HEAP tables in MySQL?
Tables that are present in the memory are called as HEAP tables. When creating a HEAP table in MySql, user needs to specify the TYPE as HEAP. These tables are now more commonly known as memory tables. These memory tables never have values with data type like “BLOB” or “TEXT”. They use indexes which make them faster.
Describe MyISAM table.
In MySQL MyISAM is the default storage engine. MyISAM tables store data values with the low byte first. Even though MyISAM tables are very reliable, corrupted tables can be expected if there is a hardware failure, the pc shuts down unexpectedly. MyISAM tables are reliable because any change made to a table is written before the sql statement returns. Even though MyISAM is the default storage engine it is advisable to specify
ENGINE = MYISAMExplain the capabilities of command ALTER table.
1. Allows renaming an existing table
2. ALTER TABLE can be used to update or modify values of a column.
3. It can be used to drop columns
4. It can be used to add constraints to a table using CHECK
5. It can be used to remove a CHECK constraint.
6. It can be used to add foreign key constraints to a table.
