Explore topic-wise InterviewSolutions in .

In MYSQL, the CREATE VIEW STATEMENT is used to create a new view inside the database.

Here’s the basic syntax of the statement:

CREATE VIEW productList AS SELECT QTY, price, totalprice AS value FROM PRODUCT;

The VIEW is basically a VIRTUAL table which is USED to view certain and preferable rows or columns of the table. In practice, users can add a number of SQL functions, JOIN and WHERE clause to view the PRESENT data of the table.

Timestamp in MYSQL

Timestamp in MySQL is used when users want to convert the value from the current time zone to UTC zone for the sake of storage or UTC time zone to the current time zone for retrieval.

DateTime in MySQL is used to insert values ‘YYYY-mm-DD’ in the table where yyyy is the year, mm is the month and dd is the date. This keyword is used to retrieve or to DISPLAY DATETIME values in Mysql.

Here’s a code script to convert string data to UTF 8 in your database:

HEADER('Content-Type: text/plain; charset=utf-8');
$databasesToConvert = [ 'database1' */ ];
$typesToConvert = [ 'char', 'varchar', 'tinytext', 'mediumtext', 'text', 'longtext'];
CONVERT(CAST(CONVERT(`{$column}` USING ENGLISH) AS BINARY) USING utf8)";
$query = "UPDATE `{$table}` SET " . join( ', ', $converts );
echo "\N", $query, "\n";
echo "\n";
 

The degree of a table is the number of COLUMNS in the given table. It is also CALLED as Arity. It is the range of ENTITIES inside each ENTITY type that can be linked via a given RELATIONSHIP type.

It is the best way to upload large CSV FILES through the LOAD DATA command.

Syntax:

LOAD DATA LOCAL INFILE "your file NAME"

INTO TABLE table name

FIELDS TERMINATED by ’,’

LINES TERMINATED BY ’\n’

In MySQL, a temporary table is a distinct type of table that approves you to save a transient end RESULT SET, in which you can REUSE countless INSTANCES in a single session. A brief table is created with the aid of the use of the CREATE TEMPORARY TABLE statement.

HEAP tables are in memory. No TEXT or BLOB FIELDS are ALLOWED inside HEAP tables. They are TYPICALLY USED for high-speed transient storage.

SELECT COUNT(*) from TABLE_NAME where 'status' = 1;

SHOW INDEX FROM TABLE_NAME;

These are the set of RULES applied to COLUMNS on the table. It is USED to bound the type of data that can go into a table.

• PRIMARY KEY
• FOREIGN KEY
• UNIQUE:
• Not NULL

WHERE term is USED for FILTERING ROWS, and it APPLIES to every row but HAVING term is used to filter groups.

WHERE can be used without the GROUP BY but HAVING clause cannot be used without the GROUP BY.

SELECT * FROM EmployeeSalary
INTERSECT
SELECT * FROM ManagerSalary

SELECT EmpId, Project, Salary, COUNT(*) FROM EmployeeSalary GROUP BY EmpId, Project, Salary HAVING COUNT(*) > 1;

SELECT * FROM 'TableName' WHERE 'status' = 1 LIMIT 10

SELECT * FROM STUDENT where MOD(id,2) = 0

The stored procedure is LIKE a subprogram in a typical COMPUTING language which is stored in the database. A stored procedure contains the name, list of parameters, and the SQL statements. All the RELATIONAL database system works as PILLARS for stored procedures.

In this example, we are CREATING a simple procedure called job_data, when this procedure will get executed, all the data from "jobs" tables will get displayed.

DELIMITER //

CREATE PROCEDURE GetAllPages()
BEGIN
    SELECT * FROM pages WHERE title LIKE '%MySQL Interview Questions%';
END //

DELIMITER ;

SELECT id, name, department FROM EMPLOYEE where MOD(id,2) = 1

SELECT std_id, COUNT(std_id) as cnt FROM STUDENT GROUP by std_id having cnt > 1

We can USE SELECT NOW();

• CHAR can have a maximum of 255 characters, but VARCHAR can hold a maximum of 65,535 characters.
• CHAR field is a fixed length, but VARCHAR is a variable length field.
• CHAR uses STATIC MEMORY ALLOCATION, but VARCHAR uses dynamic memory allocation.

In Mysql, joins are used to RETRIEVE data from multiple TABLES. It is performed whenever two or more tables are joined.

There are three types of MySQL joins:-

• MySQL INNER JOIN
  SELECT columns FROM table_1 INNER JOIN table_2 ON table_1.column = table_2.column;
• MySQL LEFT JOIN
  SELECT columns FROM table_1 LEFT JOIN table_2 ON table_1.column = table_2.column;
• MySQL RIGHT JOIN
  SELECT columns FROM table_1 RIGHT JOIN table_2 ON table_1.column = table_2.column;

You can use this query in your phpmyadmin SELECT version();

The MySQL aggregate function performs a calculation on a SET of values, and it returns a single value as output. It IGNORES NULL values when it performs calculation except for the COUNT function.

MySQL provides many aggregate functions that are listed below.

• AVG()
• COUNT()
• SUM()
• MIN()
• MAX() etc

Between operator is used to select a range of data between TWO values. It can be TEXTS, numbers, and DATES, etc.

Syntax: Select * from TABLENAME where FIELDNAME between VALUE1 and VALUE2

IN operator is used to CHECK for a value in the given SET of values.

Syntax: Select * from TABLENAME where FIELDNAME IN ('VALUE1','VALUE2',...)

SELECT ID, GROUP_CONCAT(DEPARTMENT SEPARATOR ' ') AS department FROM EMPLOYEE GROUP by id;

INNODB

ALTER TABLE EnterTableName ENGINE = EnterEngineName;

Storage ENGINES are Mysql components, that can handle the OPERATIONS for different table types to STORE and MANAGE information in a database.

• InnoDB
• MyISAM
• MEMORY
• MERGE
• EXAMPLE
• CSV
• ARCHIVE etc

• DATA Security
• It is USED to RETRIEVE large amounts of records from a database quickly and efficiently.
• On-Demand Scalability
• High Performance
• Excellent Uptime
• Reduced Total COST of OWNERSHIP

It is a LANGUAGE but not a PROGRAMMING Language. It is a relational DATABASE MANAGEMENT SYSTEM.

The maximum size of the HEAP table can be controlled by the MySQL CONFIG variable CALLED max_heap_table_size.

It is a table that points to a table in ANOTHER MySQL DATABASE instance. It can be seen as a VIEW of this remote database. Other RDBMS have the same concepts for EXAMPLE database LINKS.

There are a number of unique FEATURES that are INCORPORATED in Mysql 8 include-

• Unicode 9.0 support
• Window functions
• Recursive SQL syntax statements
• Support Native JSON data
• Support for document store FUNCTIONALITY

In general slow query, the log is BASICALLY used in Mysql for the determination of which DATABASE queries will take a long duration to run. Moreover, a slow query log in MySQL simplifies that operations in the context of efficient and time-consuming queries.

In order to enable the slow query log the command- GET global slow_query_log – ‘ON’; is used

To write an optimized query in MySQL, below are some GUIDELINES:

• Functions should not be USED in PREDICATES.
• At the beginning of predicates, do not use the wildcard symbols such as %.
• Use only needed columns in the SELECT clause.
• Always use the inner join option.
• Use of the “Order by” clause is NECESSARY for SQL, in CASE of assuming the sorted results.

Images in MySQL can be stored as blobs. For SAVING them: All the database images are converted into the blobs FIRST. Then, they will GET INSERTED into the database, and LATER on, it will get stored into the disk.

Yes, the primary key can be DROPPED in MYSQL with the use of the “ALTER TABLE” statement.

Syntax:

ALTER TABLE table_name DROP PRIMARY KEY

In MySQL, an ENUM is actually a string object whose value is selected from the list of permitted values pre-defined during the TIME of a column creation. It is USED because it provides compact data storage in ADDITION to readable queries and output.

Here’s the DIFFERENCE between an ENUM and a SET

UPDATE `your_table_name` SET new_field=old_field

Regex is an operational and powerful pattern that can help the users for the IMPLEMENTATION of a powerful search utility function for database systems. Moreover, it is an operator which is used when we require matching REGULAR expressions. In addition to this, it also supports a wider range of meta-characters which allow flexibility and more control over the database structure when performing pattern matching.

Match BEGINNING of string(^):

SELECT name FROM users WHERE name REGEXP '^bestinterviewquestion';

Binary DATA can be stored in Mysql in different WAYS such as

• The short length of binary data can be stored in the form of string as a VARCHAR
• A BLOB data type allows USERS to store an arbitrary amount of binary data.

SELECT id, NAME FROM USERS WHERE PHONE IS NULL;

In MySQL, a trigger corresponds to a set of actions running AUTOMATICALLY when a PARTICULAR change OPERATION like SQL INSERT, UPDATE, or DELETE query is performed on a table.

CREATE TRIGGER data_backup BEFORE UPDATE ON users
   FOR EACH ROW
   BEGIN
     IF NEW.amount < 0 THEN
        SET NEW.amount = 0;
     ELSEIF NEW.amount > 100 THEN
       SET NEW.amount = 100;
     END IF;
   END;//

In MySQL, standards-based DRIVERS for JDBC, ODBC, and .NET are provided in order to enable developers in building DATABASE applications with their LANGUAGE of choice.

Available Drivers in MySQL:

• PHP Driver
• JDBC Driver
• ODBC Driver
• C WRAPPER
• PYTHON Driver
• PERL Driver
• RUBY Driver
• CAP11PHP Driver
• Ado.net5.mxj

JDBC, ODBC, and .Net drivers are necessary for MySQL as they enable the developers for building their database applications.

IS NULL checks to see if the cell is empty but IS NOT NULL checks to see if the cell is not empty.

Example :

SELECT id FROM USERS WHERE 'user_type' IS NOT NULL;

SELECT id FROM users WHERE 'user_type' NOT IS NULL;

NOTE: This is very ESSENTIAL MYSQL query interview questions.