Explore topic-wise InterviewSolutions in .

It is USED to CONCATENATE TWO or more strings.

SELECT CONCAT('BestInterview', ' ', 'Question') AS 'Name';

Comparisons OPERATORS are used to comparing ONE EXPRESSION to ANOTHER value or expression. It is just LIKE = , < , > , => , =<, <>

In Mysql, a trigger is a database object that is directly associated with a table. It will be activated when a defined action is executed for the table. It can be PERFORMED when you run ONE of the following MySQL LIKE INSERT, UPDATE and DELETE occurred in a table. It's activation time can be BEFORE or AFTER

mysql&GT; delimiter //

mysql> CREATE TRIGGER age_check BEFORE INSERT ON people FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF;

mysql> delimiter ;

MySQL -u username -p database_name < file.SQL
Here username is your DATABASE username like "ROOT," database_name is your database name, file.sql is your SQL file name with COMPLETE PATH.

The default port is 3306. We can CHANGE it in /etc/MySQL/my.conf there is a port variable. We can UPDATE this port ACCORDING to our NEED

1. DDL

In MySQL, DDL is the short FORM for Data Definition Language, which is used in DATABASE schemas and descriptions while deciding how data should RESIDE in the database.

• CREATE
• ALTER
• DROP
• TRUNCATE
• COMMENT
• RENAME

DML is a short form for Data Manipulation Language which is used in data manipulation and mostly includes common SQL statements to store, modify, retrieve, delete and update data in a database.

• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE
• CALL
• EXPLAIN PLAN
• LOCK TABLE

DCL is a short form for Data Control Language including commands which are concerned with User rights, permissions and other controls within the database system.

• GRANT
• REVOKE

The standard way to perform case INSENSITIVE queries in SQL is to use the SQL UPPER or lower functions like the following:

select * from users where upper(first_name) = 'AJAY';

OR

select * from users where lower(first_name) = 'ajay';

The method is to make the FIELD you are searching as uppercase or lowercase then ALSO make the search string uppercase or lowercase as per the SQL function.

In MySQL, the time PERIOD cardinality REFERS to the SPECIALTY of facts VALUES that can be put into columns. It is a type of property that influences the potential to search, cluster and kind data.

• Low Cardinality − All values for a column have to be the same.
• High Cardinality − All values for a column ought to be unique.

A DATABASE index is the structure of a fact that improves the velocity of operations in a table. It can be created the USAGE of one or greater columns, presenting the FOUNDATION for each fast random lookups and efficient ORDERING of getting admission to records. Indexes are used to locate rows with specific column values quickly.

CREATE INDEX [index name] ON [table name]([column name]);

• Don't USE "sp_" while creating a stored procedure.
• Don't use "*" while you are USING the SELECT command. Also avoid unnecessary columns in SELECT clause.
• You can use table aliases while writing queries.
• Avoid the usage of the wildcard (%) at the starting of a predicate.
• DISTINCT and UNION NEED to be USED solely if it is necessary.

Mysql is not CASE sensitive. Its case-SENSITIVITY relies on the UNDERLYING operating system as OS determines the case sensitivity of TABLES names and database. In windows, database and table names are not case sensitive but in the case of UNIX, it is case sensitive in nature. Especially on the UNIX host, database ACCEPTS the upper case and lower-case table names.

TRUNCATE

• It removes all rows from a table.
• It does not require a WHERE clause.
• Truncate cannot be used with indexed views.
• It is performance wise faster.

• It removes Some or All rows from a table.
• A WHERE clause is used to REMOVE particular rows according to the MATCHED condition. All rows will be deleted when we did not use Where condition in Query.
• It removes rows ONE by at a time.
• It can be used with indexed views.

• It removes a table from the database.
• All table's rows, indexes, and privileges will also be removed when we used this command.
• The operation cannot be ROLLED back.

These are most commonly used storage engine in MySQL are MyISAM and InnoDB.

• MyISAM does no longer support transactions, however InnoDB supports transactions.
• MyISAM HELPS Table-level LOCKING, however InnoDB supports Row-level Locking.
• MyISAM helps full-text search, however InnoDB does not.
• MyISAM designed for the need for speed but InnoDB designed for most performance.
• MyISAM does now not aid OVERSEAS keys, but InnoDB helps foreign keys.
• We can use commit and rollback with InnoDB however not in MyISAM.
• MyISAM does no longer assist ACID (Atomicity, Consistency, Isolation, and Durability) however InnoDB supports the ACID property.
• In the InnoDB table, the AUTO_INCREMENT field is a section of the index but now not in MyISAM.
• MyISAM stores its TABLES, data, and indexes in disk area the usage of a separate table name.FRM, desk name.MYD and table name.MYI however InnoDB stores its tables and indexes in a tablespace.