Clone Tables:
SHOW CREATE TABLE tutorials_tbl
--Get the complete structure about the table.
-- Rename this table and create another table.
-- If you want to copy data from old table then you can do it by using INSERT INTO... SELECT statement.
REF: https://www.tutorialspoint.com/mysql/mysql-clone-tables.htm
SELECT VERSION( ) - Server version string
SELECT DATABASE( ) - Current database name (empty if none)
SELECT USER( ) - Current user name
SHOW STATUS - Server status indicators
SHOW VARIABLES - Server configuration variables
Database Export/Import
-- Exporting Data with the SELECT ... INTO OUTFILE Statement
-- LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl; (INFILE)
What is the difference between Primary Key and Unique Key?
Both
Primary and Unique Key is implemented for Uniqueness of the column.
Primary Key creates a clustered index of column where as an Unique
creates unclustered index of column. Moreover, Primary Key doesn’t allow
NULL value, however Unique Key does allows one NULL value.
How to store picture file in the database. What Object type is used?
Storing Pictures in a database is a bad idea. To store picture in a database Object Type ‘Blob’ is recommended.
What is Data Mining?
Data Mining is a subcategory of Computer
Science which aims at extraction of information from set of data and
transform it into Human Readable structure, to be used later.
What is an ERD?
ERD stands for Entity Relationship
Diagram. Entity Relationship Diagram is the graphical representation of
tables, with the relationship between them.
What are indexes in a Database. What are the types of indexes?
Clustered Index
- Only one per table.
- Faster to read than non clustered as data is physically stored in index order.
Nonclustered Index
- Can be used many times per table.
- Quicker for insert and update operations than a clustered index.
Clustered Index
A clustered index determine the physical order of DATA in a table.For this reason a table have only 1 clustered index.
like "dictionary" No need of any other Index, its already Index according to words
Nonclustered Index
A non clustered index is analogous to an index in a Book.The data is store in one place. the index is store in another place and the index have pointers to the storage location of the data.For this reason a table have more than 1 Nonclustered index.
like "Chemistry book" at staring there is a separate index to point Chapter location and At the "END" there is another Index pointing the common WORDS location
• BLOB or TEXT fields are not allowed
• Only comparison operators can be used =, <,>, = >,=<
• AUTO_INCREMENT is not supported by HEAP tables
• Indexes should be NOT NULL
How do you control the max size of a HEAP table?
Maximum size of Heal table can be controlled by MySQL config variable called max_heap_table_size
A clustered index determine the physical order of DATA in a table.For this reason a table have only 1 clustered index.
like "dictionary" No need of any other Index, its already Index according to words
Nonclustered Index
A non clustered index is analogous to an index in a Book.The data is store in one place. the index is store in another place and the index have pointers to the storage location of the data.For this reason a table have more than 1 Nonclustered index.
like "Chemistry book" at staring there is a separate index to point Chapter location and At the "END" there is another Index pointing the common WORDS location
What is the difference between Primary Key and Unique Key
Both Primary and Unique Key is implemented
for Uniqueness of the column. Primary Key creates a clustered index of
column where as an Unique creates unclustered index of column. Moreover,
Primary Key doesn’t allow NULL value, however Unique Key does allows
one NULL value.
What are Heap tables?
HEAP tables are present in memory and they are used for high speed storage on temporary basis.• BLOB or TEXT fields are not allowed
• Only comparison operators can be used =, <,>, = >,=<
• Indexes should be NOT NULL
How do you control the max size of a HEAP table?
Maximum size of Heal table can be controlled by MySQL config variable called max_heap_table_size
What is the default port for MySQL Server?
The default port for MySQL server is 3306.
How do you login to MySql using Unix shell?
We can login through this command:
# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>
What happens when the column is set to AUTO INCREMENT and if you reach maximum value in the table?
It stops incrementing. Any further inserts are going to produce an error, since the key has been used already.
How can we find out which auto increment was assigned on Last insert?
LAST_INSERT_ID will return the last value assigned by Auto_increment and it is not required to specify the table name
How can you see all indexes defined for a table?
SHOW INDEX FROM <tablename>;
What do you mean by % and _ in the LIKE statement?
% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.
Is Mysql query is case sensitive?
No.
What are the different tables present in MySQL?
Total 5 types of tables are present:
- MyISAM - Each MyISAM table is stored on disk, MyISAM is the default storage engine as of MySQL
- Heap
- Merge
- INNO DB - lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.
- ISAM
MySQL Database Engines: MySQL is a SQL database processing layer on top of a storage engine. The default engine is MyISAM which is very fast but does not support higher level functionality such as foreign keys or transactions. One must use the InnoDB database storage engine to support foreign keys and transactions.
What is the difference between CHAR and VARCHAR?
When
the table is created, CHAR is used to define the fixed length of the
table and columns. The length value could be in the range of 1-255.
VARCHAR command is given to adjust the column and table length as
required
What are the limits for using columns to create the Index?
The maximum limits of indexed columns that could be created for any table is 16.
What is the use of ENUM in MySQL?
Use
of ENUM will limit the values that can go into a table. For instance;
the user can create a table giving specific month values and other month
values would not enter into the table.
-- enum includes varibales that variable can be insert.What is meant by transaction and ACID properties?
Transaction
is logical unit of work where either all or none of the steps should be
performed. ACID is the abbreviation for Atomicity, Consistency,
Isolation, and Durability that are properties of any transaction.
What are the standard SQL commands every SQL developer should know?
The basic SQL commands can be organized into the following categories:- Data Manipulation Language (DML)
- INSERT: Creates records. The “Create” in CRUD.
- SELECT: Retrieves records. The “Read” in CRUD.
- UPDATE: Modifies records. The “Update” in CRUD.
- DELETE: Deletes records. The “Delete” in CRUD.
- Data Definition Language (DDL)
- CREATE: Creates a new object.
- ALTER: Alters an existing object.
- DROP: Deletes an existing object.
- Data Control Language: (DCL)
- GRANT: Grants privileges to users.
- REVOKE: Revokes privileges previously granted to a user.
Name four ways to maintain data integrity within a RDBMS.
AWhen
it comes to storing data accurately, consistently, and reliably within a
RDBMS, there are four general types of data integrity that you can
implement:
- Entity (Row) Integrity: Avoids duplicate rows in tables.
- Domain (Column) Integrity: Restricts the type, format, or range of values to enforce valid entries.
- Referential Integrity: Ensures rows used by other records cannot be deleted.
- User-Defined Integrity: Enforces rules set by the user that do not fall into the other categorie
What is the purpose of database normalization and how does it work?
AThe
primary purpose of normalization is to make databases more efficient by
eliminating redundant data and ensuring data dependencies are coherent. Storing data logically and efficiently reduces the amount of space the
database takes up and improves performance. The set of guidelines used
to achieve normalization are called normal forms, numbered from 1NF to
5NF. A form can be thought of as a best-practice format for laying out
data within a database.
Use following command for taking backup of your MySQL database using mysqldump utility.
mysqldump –u[user name] –p[password] [database name] > [dump file]
mysql -u username -p database_name < file.sql (IMport)
Reference:
- https://www.tutorialspoint.com/mysql/mysql-administration.htm
- https://www.tecmint.com/10-mysql-database-interview-questions-for-beginners-and-intermediates/
- https://career.guru99.com/top-50-mysql-interview-questions-answers/
- https://www.guru99.com/wildcards.html (_, % in LIKE in sQL)
- https://www.upwork.com/i/interview-questions/sql/ (important)
- http://upworktestru.com/mysql-test-upwork-answers-questions/ (important for upwork test)
- https://blogs.oracle.com/mysql/how-to:-storing-and-retrieving-spatial-data-on-mysql-server-with-connectornet
- http://www.mysqltutorial.org/select-random-records-database-table.aspx (rand()) (VVI)
*Names of databases, tables and columns can be up to 64 characters in length
create table foo (id int auto_increment, name int);
The id column cannot be auto incremented because it has not been defined as a primary key
Use the SQL_NO_CACHE option in the query.
upwork test:
No comments:
Post a Comment