SQL Interview

These are the most common and useful SQL interview questions useful for freshers as well as experienced candidates. From basics up to questions asked on advanced concepts of SQL have been also covered in this article.

You can go through these questions for a quick revision of major SQL concepts before appearing for an interview.

Let's start.

Q #1) What is SQL?

Answer: Structured Query Language is a database tool which is used to create and access database to support software application.

Q #2) What are tables in SQL?

Answer: The table is a collection of record and its information at
a single view.

Q #3) What are different types of statements supported by SQL?

Answer:

statements supported by SQL

There are 3 types of SQL statements

1) DDL (Data Definition Language): It is used to define the database structure such as tables. It includes three statements such as Create, Alter, and Drop.

Some of the DDL Commands are listed below

CREATE: It is used for creating the table.

CREATE TABLE table_name

column_name1 data_type(size),

column_name2 data_type(size),

column_name3 data_type(size),

ALTER: The ALTER
table is used for modifying the existing table object in the database.

ALTER TABLE table_name

ADD column_name datatype

ALTER TABLE table_name

DROP COLUMN column_name

2) DML (Data Manipulation Language): These statements are used to manipulate the data in records. Commonly used DML statements are Insert, Update, and Delete.

The Select statement is used as partial DML statement that is used to select all or relevant records in the table.

3) DCL (Data Control Language): These statements are used to set privileges such as Grant and Revoke database access permission to the specific
user.

Q #4) How do we use DISTINCT statement? What is its use?

Answer:

The DISTINCT statement is used with the SELECT statement. If the records contain duplicate values then DISTINCT is used to select different values among duplicate records.

Syntax:

SELECT DISTINCT column_name(s)

FROM table_name;

Q #5) What are different Clauses used in SQL?

Answer:

Clauses used in SQL

WHERE Clause: This clause is used to define the condition, extract and display only those records which fulfill the given
condition

Syntax:

SELECT column_name(s)

FROM table_name

WHERE condition;

GROUP BY Clause: It is used with SELECT statement to group the result of the executed query using the value specified in it. It matches the value with the column name in tables and groups the end result accordingly.

Syntax:

SELECT column_name(s)

FROM table_name

GROUP BY column_name;

HAVING clause: This clause is used in association with the GROUP BY clause. It is applied to each group of result or the entire result as a single group and much similar as WHERE clause, the only difference is you
cannot use it without GROUP BY clause

Syntax:

SELECT column_name(s)

FROM table_name

GROUP BY column_name

HAVING condition;

ORDER BY clause: This clause is to define the order of the query output either in ascending (ASC) or in descending (DESC) order. Ascending (ASC) is the default one but descending (DESC) is set explicitly.

Syntax:

SELECT column_name(s)

FROM table_name

WHERE condition

ORDER BY column_name ASC|DESC;

USING clause: USING clause comes in use while working with SQL Joins. It is used to check equality based on
columns when tables are joined. It can be used instead ON clause in Joins.

Syntax:

SELECT column_name(s)

FROM table_name

JOIN table_name

USING (column_name);

Q #6) Why do we use SQL constraints? Which constraints we can use while creating a database in SQL?

Answer:

Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it.

Constraints are defined while creating the database itself with CREATE TABLE statement or even after the table is created once with ALTER TABLE
statement.

There are 5 major constraints are used in SQL, such as

NOT NULL: That indicates that the column must have some value and cannot be left null

UNIQUE: This constraint is used to ensure that each row and column has unique value and no value is being repeated in any other row or column

PRIMARY KEY: This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one column to identify the particular record with a unique identity.

FOREIGN KEY: It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using
Primary Key

CHECK: It is used to ensure whether the value in columns fulfills the specified condition

Q #7) What are different JOINS used in SQL?

Answer:

SQL Joins

There are 4 major types of joins made to use while working on multiple tables in SQL databases

INNER JOIN: It is also known as SIMPLE JOIN which returns all rows from BOTH tables when it has at least one column matched

Syntax:

SELECT column_name(s)

FROM table_name1

INNER JOIN table_name2

ON column_name1=column_name2;

Example

In this example, we have a table
Employee with the following data

Employee table

The second Table is joining

joining

Enter the following SQL statement

SELECT Employee.Emp_id, Joining.Joining_Date

FROM Employee

INNER JOIN Joining

ON Employee.Emp_id = Joining.Emp_id

ORDER BY Employee.Emp_id;

There will be 4 records selected. These are the results that you should see

result of innerjoin

Employee and orders tables where there is a matching customer_id value in both the Employee and orders
tables

LEFT JOIN (LEFT OUTER JOIN): This join returns all rows from a LEFT table and its matched

rows from a RIGHT table.

Syntax:

SELECT column_name(s)

FROM table_name1

LEFT JOIN table_name2

ON column_name1=column_name2;

Example

In this example, we have a table Employee with the following data:

Employee table

Second Table is joining

joining 1

Enter the following SQL statement

SELECT Employee.Emp_id, Joining.Joining_Date

FROM Employee

LEFT OUTER JOIN
Joining

ON Employee.Emp_id = Joining.Emp_id

ORDER BY Employee.Emp_id;

There will be 4 records selected. These are the results that you should see:

result of LEFT OUTER JOIN

RIGHT JOIN (RIGHT OUTER JOIN): This joins returns all rows from the RIGHT table and its matched rows from a LEFT table.

Syntax:

SELECT column_name(s)

FROM table_name1

RIGHT JOIN table_name2

ON column_name1=column_name2;

Example

In this example, we have a table Employee with the following data

Employee table

The second Table is
joining

joining 1

Enter the following SQL statement

SELECT Employee.Emp_id, Joining.Joining_Date FROM Employee

RIGHT JOIN Joining

ON Employee.Emp_id = Joining.Emp_id

ORDER BY Employee.Emp_id;

Output:

Emp_id Joining_Date

E0012 2016/04/18

E0013 2016/04/19

E0014 2016/05/01

FULL JOIN (FULL OUTER JOIN): This joins returns all when there is a match either in the RIGHT table or in the LEFT table.

Syntax:

SELECT column_name(s)

FROM table_name1

FULL OUTER JOIN table_name2

ON
column_name1=column_name2;

Example

In this example, we have a table Employee with the following data:

Employee table

Second Table is joining

joining 1

Enter the following SQL statement:

SELECT Employee.Emp_id, Joining.Joining_Date

FROM Employee

FULL OUTER JOIN Joining

ON Employee.Emp_id = Joining.Emp_id

ORDER BY Employee.Emp_id;

There will be 8 records selected. These are the results that you should see

result of FULL OUTER JOIN

Q #8) What are transactions and their
controls?

Answer:

A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations performed like Creating, updating, deleting records in the database come from transactions.

In simple words, we can say that a transaction means a group of SQL queries executed on database records.

There are 4 transaction controls such as

COMMIT: It is used to save all changes made through the transaction

ROLLBACK: It is used to roll back the transaction such as all changes made by the transaction are reverted back and database remains as
before

SET TRANSACTION: Set the name of transaction

SAVEPOINT: It is used to set the point from where the transaction is to be rolled back

Q #9) What are properties of the transaction?

Answer:

Properties of the transaction are known as ACID properties, such as

Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully if not then transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes undone

Consistency: Ensures that all changes made through successful transaction are reflected
properly on database

Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on other

Durability: Ensures that the changes made in the database with committed transactions persist as it is even after a system failure

Q #10) How many Aggregate Functions are available there in SQL?

Answer:

SQL Aggregate Functions calculates values from multiple columns in a table and returns a single value.

There are 7 aggregate functions we use in SQL

AVG(): Returns the average value from specified columns

COUNT(): Returns number of table
rows

MAX(): Returns largest value among the records

MIN(): Returns smallest value among the records

SUM(): Returns the sum of specified column values

FIRST(): Returns the first value

LAST(): Returns Last value

Q #11) What are Scalar Functions in SQL?

Answer:

Scalar Functions are used to return a single value based on the input values.

Scalar Functions are as follows

UCASE(): Converts the specified field in upper case

LCASE(): Converts the specified field in lower case

MID(): Extracts and returns character from the text field

FORMAT(): Specifies the display
format

LEN(): Specifies the length of the text field

ROUND(): Rounds up the decimal field value to a number

Q #12) What are triggers?

Answer:

Triggers in SQL is kind of stored procedures used to create a response to a specific action performed on the table such as Insert, Update or Delete. You can invoke triggers explicitly on the table in the database.

Action and Event are two main components of SQL triggers when certain actions are performed the event occurs in response to that action.

Syntax:

CREATE TRIGGER name {BEFORE|AFTER} (event [OR..]}

ON table_name [FOR [EACH]
{ROW|STATEMENT}]

EXECUTE PROCEDURE functionname {arguments}

Q #13) What is View in SQL?

Answer:

A View can be defined as a virtual table that contains rows and columns with fields from one or more table.

Syntax:

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

Q #14) How we can update the view?

Answer:

SQL CREATE and REPLACE can be used for updating the view.

Following query syntax is to be executed to update the created view

Syntax:

CREATE OR REPLACE VIEW
view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

Q #15) Explain the working of SQL Privileges?

Answer:

SQL GRANT and REVOKE commands are used to implement privileges in SQL multiple user environments. The administrator of the database can grant or revoke privileges to or from users of database object like SELECT, INSERT, UPDATE, DELETE, ALL etc.

GRANT Command: This command is used provide database access to user apart from an administrator.

Syntax:

GRANT privilege_name

ON object_name

TO {user_name|PUBLIC|role_name}

[WITH GRANT
OPTION];

In above syntax WITH GRANT OPTIONS indicates that the user can grant access to another user too.

REVOKE command: This command is used provide database deny or remove access to database objects.

Syntax:

REVOKE privilege_name

ON object_name

FROM {user_name|PUBLIC|role_name};

Q #16) How many types of Privileges are available in SQL?

Answer:

There are two types of privileges used in SQL, such as

System Privilege: System privileges deal with an object of a particular type and specifies the right to perform one or more actions on it which include Admin
allows a user to perform administrative tasks, ALTER ANY INDEX, ALTER ANY CACHE GROUP CREATE/ALTER/DELETE TABLE, CREATE/ALTER/DELETE VIEW etc.

Object Privilege: This allows to perform actions on an object or object of another user(s) viz. table, view, indexes etc. Some of the object privileges are EXECUTE, INSERT, UPDATE, DELETE, SELECT, FLUSH, LOAD, INDEX, REFERENCES etc.

Q #17) What is SQL Injection?

Answer:

SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database such that once it is executed the database is opened for an attacker. This technique is usually used for attacking
Data-Driven Applications to have access to sensitive data and perform administrative tasks on databases.

For Example: SELECT column_name(s) FROM table_name WHERE condition;

Q #18) What is SQL Sandbox in SQL Server?

Answer:

SQL Sandbox is the safe place in SQL Server Environment where untrusted scripts are executed. There are 3 types of SQL sandbox, such as

Safe Access Sandbox: Here a user can perform SQL operations such as creating stored procedures, triggers etc. but cannot have access to the memory and cannot create files.

External Access Sandbox: User can have access to files without
having a right to manipulate the memory allocation.

Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory.

Q #19) What is the difference between SQL and PL/SQL?

Answer:

SQL is a structured query language to create and access databases whereas PL/SQL comes with procedural concepts of programming languages.

Q #20) What is the difference between SQL and MySQL?

Answer:

SQL is a structured query language that is used for manipulating and accessing the relational database, on the other hand, MySQL itself is a relational database that uses SQL as the
standard database language.

Q #21) What is the use of NVL function?

Answer:

NVL function is used to convert the null value to its actual value.

Q #22) What is the Cartesian product of the table?

Answer:

The output of Cross Join is called as a Cartesian product. It returns rows combining each row from the first table with each row of the second table. For Example, if we join two tables having 15 and 20 columns the Cartesian product of two tables will be 15Ã—20=300 Rows.

Q #23) What do you mean by Subquery?

Answer:

Query
within another query is called as Subquery. A subquery is called inner query which returns output that is to be used by another query.

Q #24) How many row comparison operators are used while working with a subquery?

Answer:

There are 3-row comparison operators that are used in subqueries such as IN, ANY and ALL.

Q #25) What is the difference between clustered and non-clustered indexes?

Answer:

One table can have only one clustered index but multiple nonclustered indexes.

Clustered indexes can be read rapidly rather than non-clustered indexes.

Clustered indexes store
data physically in the table or view and non-clustered indexes do not store data in the table as it has separate structure from the data row

Q #26) What is the difference between DELETE and TRUNCATE?

Answer:

The basic difference in both is DELETE is DML command and TRUNCATE is DDL

DELETE is used to delete a specific row from the table whereas TRUNCATE is used to remove all rows from the table

We can use DELETE with WHERE clause but cannot use TRUNCATE with it

Q #27) What is the difference between DROP and TRUNCATE?

Answer:

TRUNCATE removes all rows from the table which cannot be retrieved
back, DROP removes the entire table from the database and it cannot be retrieved back.

Q #28) How to write a query to show the details of a student from Students table whose

name starts with K?

Answer:

SELECT * FROM Student WHERE Student_Name like â€˜K%â€™;

Here â€˜likeâ€™ operator is used for pattern matching.

Q #29) What is the difference between Nested Subquery and Correlated Subquery?

Answer:

Subquery within another subquery is called as Nested Subquery. If the output of a subquery is depending on column values of the parent query table
then the query is called Correlated Subquery.

SELECT adminid(SELEC Firstname+' '+Lastname FROM Employee WHERE

empid=emp. adminid)AS EmpAdminId FROM Employee

This query gets details of an employee from the Employee table.

Q #30) What is Normalization? How many Normalization forms are there?

Answer:

Normalization is used to organize the data in such a manner that data redundancy will never occur in the database and avoid insert, update and delete anomalies.

There are 5 forms of Normalization

First Normal Form (1NF): It removes all duplicate columns from the table.
Creates a table for related data and identifies unique column values

First Normal Form (2NF): Follows 1NF and creates and places data subsets in an individual table and defines the relationship between tables using the primary key

Third Normal Form (3NF): Follows 2NF and removes those columns which are not related through primary key

Fourth Normal Form (4NF): Follows 3NF and do not define multi-valued dependencies. 4NF also known as BCNF

Q #31) What is a Relationship? How many types of Relationships are there?

Answer:

The relationship can be defined as the connection between more than one tables in the
database.

There are 4 types of relationships

One to One Relationship

Many to One Relationship

Many to Many Relationship

One to Many Relationship

Q #32) What do you mean by Stored Procedures? How do we use it?

Answer:

A stored procedure is a collection of SQL statements that can be used as a function to access the database. We can create these stored procedures previously before using it and can execute these them wherever we require and also apply some conditional logic to it. Stored procedures are also used to reduce network traffic and improve
performance.

Syntax:

CREATE Procedure Procedure_Name

(

//Parameters

)

BEGIN

SQL statements in stored procedures to update/retrieve records

END

Q #33) State some properties of Relational databases?

Answer:

In relational databases, each column should have a unique name

The sequence of rows and columns in relational databases are insignificant

All values are atomic and each row is unique

Q #34) What are Nested Triggers?

Answer:

Triggers may implement data modification logic by using INSERT, UPDATE, and DELETE
statements. These triggers that contain data modification logic and find other triggers for data modification are called Nested Triggers.

Q #35) What is a Cursor?

Answer:

A cursor is a database object which is used to manipulate data in a row-to-row manner.

Cursor follows steps as given below

Declare Cursor

Open Cursor

Retrieve row from the Cursor

Process the row

Close Cursor

Deallocate Cursor

Q #36) What is Collation?

Answer:

Collation is a set of rules that check how the data is sorted by comparing it. Such as Character
data is stored using correct character sequence along with case sensitivity, type, and accent.

Q #37) What do we need to check in Database Testing?

Answer:

Generally, in Database Testing following thing is need to be tested

Database Connectivity

Constraint Check

Required Application Field and its size

Data Retrieval and Processing With DML operations

Stored Procedures

Functional flow

Q #38) What is Database White Box Testing?

Answer:

Database White Box Testing involves

Database Consistency and ACID properties

Database
triggers and logical views

Decision Coverage, Condition Coverage, and Statement Coverage

Database Tables, Data Model, and Database Schema

Referential integrity rules

Q #39) What is Database Black Box Testing?

Answer:

Database Black Box Testing involves

Data Mapping

Data stored and retrieved

Use of Black Box techniques such as Equivalence Partitioning and Boundary Value Analysis (BVA)

Q #40) What are Indexes in SQL?

Answer: The index can be defined as the way to retrieve the data more quickly. We can define indexes using CREATE
statements.

Syntax:

CREATE INDEX index_name

ON table_name (column_name)

Further, we can also create Unique Index using following syntax;

Syntax:

CREATE UNIQUE INDEX index_name

ON table_name (column_name)

******************

UPDATE: We have added few more short questions for practice.

Q #41) What does SQL stand for?

Answer: SQL stands for Structured Query Language.

Q #42) How to select all records from the table?

Answer: To select all the records from the table we need to use the following syntax:

Select *
from table_name;

Q #43) Define join and name different types of joins?

Answer: Join keyword is used to fetch data from related two or more tables. It returns rows where there is at least one match in both the tables included in the join. Read more here.

Type of joins are:

Right Join

Outer Join

Full Join

Cross Join

Self Join.

Q #44) What is the syntax to add a record to a table?

Answer: To add a record in a table INSERT syntax is used.

Example: INSERT into table_name VALUES (value1, value2..);

Q #45) How do you add a column to a table?

Answer: To add another column in
the table following command has been used.

ALTER TABLE table_name ADD (column_name);

Q #46) Define SQL Delete statement.

Answer: Delete is used to delete a row or rows from a table based on the specified condition.

The basic syntax is as follows:

DELETE FROM table_name

WHERE <Condition>

Q #47) Define COMMIT?

Answer: COMMIT saves all changes made by DML statements.

Q #48) What is the Primary key?

Answer: A Primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused.

Q #49) What are Foreign keys?

Answer:
When a one tableâ€™s primary key field is added to related tables in order to create the common field which relates the two tables, it called a foreign key in other tables.

Foreign Key constraints enforce referential integrity.

Q #50) What is CHECK Constraint?

Answer: A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.

Q #51) Is it possible for a table to have more than one foreign key?

Answer: Yes, a table can have many foreign keys and only one primary key.

Q #52) What are the possible values for the BOOLEAN data
field?

Answer: For a BOOLEAN data field, two values are possible: -1(true) and 0(false).

Q #53) What is a stored procedure?

Answer: A stored procedure is a set of SQL queries which can take input and send back output.

Q #54) What is identity in SQL?

Answer: An identity column in the SQL automatically generates numeric values. We can define a start and increment value of the identity column.

Q #55) What is Normalization?

Answer: The process of table design to minimize the data redundancy is called normalization. We need to divide a database into two or more table and define relationships between
them.

Q #56) What is a Trigger?

Answer: The Trigger allows us to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table)

Q #57) How to select random rows from a table?

Answer: Using a SAMPLE clause we can select random rows.

Example:

SELECT * FROM table_name SAMPLE(10);

Q #58) Which TCP/IP port does SQL Server run?

Answer: By default SQL Server runs on port 1433.

Q #59) Write a SQL SELECT query that only returns each name only once from a table?

Answer: To get each name only once, we need
to use the DISTINCT keyword.

SELECT DISTINCT name FROM table_name;

Q #60) Explain DML and DDL?

Answer: DML stands for Data Manipulation Language. INSERT, UPDATE and DELETE are DML statements.

DDL stands for Data Definition Language. CREATE, ALTER, DROP, RENAME are DDL statements.

Q #61) Can we rename a column in the output of SQL query?

Answer: Yes using the following syntax we can do this.

SELECT column_name AS new_name FROM table_name;

Q #62) Give the order of SQL SELECT?

Answer: Order of SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clause are mandatory.

Q #63) Suppose a Student
column has two columns, Name and Marks. How to get name and marks of the top three students.

Answer: SELECT Name, Marks FROM Student s1 where 3 <= (SELECT COUNT(*) FROM Students s2 WHERE s1.marks = s2.marks)

Q #64) What is SQL comments?

Answer: SQL comments can be put by two consecutive hyphens (â€“).

Q #65) Difference between TRUNCATE, DELETE and DROP commands?

Answer: DELETE removes some or all rows from a table based on the condition. It can be rolled back.

TRUNCATE removes ALL rows from a table by de-allocating the memory pages. The operation cannot be rolled back

DROP command
removes a table from the database completely.

Q #66) What are the properties of a transaction?

Answer: Generally, these properties are referred to as ACID properties. They are:

Atomicity

Consistency

Isolation

Durability.

Q #67) What do you mean by ROWID?

Answer: Itâ€™s an 18 character long pseudo column attached with each row of a table.

Q #68) Define UNION, MINUS, UNION ALL, INTERSECT ?

Answer: MINUS â€“ returns all distinct rows selected by the first query but not by the second.

UNION â€“ returns all distinct rows selected by either
query

UNION ALL â€“ returns all rows selected by either query, including all duplicates.

INTERSECT â€“ returns all distinct rows selected by both queries.

Q #69) What is a transaction?

Answer: A transaction is a sequence of code that runs against a database. It takes the database from one consistent state to another.

Q #70) What is the difference between UNIQUE and PRIMARY KEY constraints?

Answer: A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys.

The primary key cannot contain Null values whereas Unique key can contain Null
values.

Q #71) What is a composite primary key?

Answer: Primary key created on more than one column is called composite primary key.

Q #72) What is an Index?

Answer: An Index is a special structure associated with a table speed up the performance of queries. The index can be created on one or more columns of a table.

Q #73) What is the Subquery?

Answer: A Subquery is a subset of select statements whose return values are used in filtering conditions of the main query.

Q #74) What do you mean by query optimization?

Answer: Query optimization is a process in which a database system compares
different query strategies and select the query with the least cost.

Q #75) What is Collation?

Answer: Set of rules that define how data is stored, how case sensitivity and Kana character can be treated etc.

Q #76) What is Referential Integrity?

Answer: Set of rules that restrict the values of one or more columns of the tables based on the values of the primary key or unique key of the referenced table.

Q #77) What is Case Function?

Answer: Case facilitates if-then-else type of logic in SQL. It evaluates a list of conditions and returns one of the multiple possible result expressions.

Q #78) Define
a temp table?

Answer: A temp table is a temporary storage structure to store the data temporarily.

Q #79) How can we avoid duplicating records in a query?

Answer: By using DISTINCT keyword duplicating records in a query can be avoided.

Q #80) Explain the difference between Rename and Alias?

Answer: Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column.

Q #81) What is a View?

Answer: A view is a virtual table which contains data from one or more tables. Views restrict data access of the table by selecting only required values and make complex queries
easy.

Q #8). What are the advantages of Views?

Answer: Advantages of Views:

Views restrict access to the data because the view can display selective columns from the table.

Views can be used to make simple queries to retrieve the results of complicated queries. For example, views can be used to query information from multiple tables without the user knowing.

Q #83) List the various privileges that a user can grant to another user?

Answer: SELECT, CONNECT, RESOURCES.

Q #84) What is schema?

Answer: A schema is a collection of database objects of a User.

Q #85) What is a
Table?

Answer: A table is the basic unit of data storage in the database management system. Table data is stored in rows and columns.

Q #86) Does View contain Data?

Answer: No, Views are virtual structure.

Q #87) Can a View based on another View?

Answer: Yes, A View is based on another View.

Q #88) What is the difference between Having clause and Where clause?

Answer: Both specify a search condition but Having clause is used only with the SELECT statement and typically used with GROUP BY clause.

If GROUP BY clause is not used then Having behaved like WHERE clause only.

Q #89) What is the
difference between Local and Global temporary table?

Answer: If defined in inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the DB but its rows disappear when the connection is closed.

Q #90) What is CTE?

Answer: A CTE or common table expression is an expression which contains temporary result set which is defined in a SQL statement.

Conclusion

Thatâ€™s all for now.

SQL is an essential component of the database system. Having well-versed knowledge of database along with SQL concepts will definitely be beneficial
to crack the interview for the concerned profile.

Apart from some major concepts, there are some hidden facts that remain unseen and affects your performance in the interview. In this tutorial, I have tried to recollect some of those concepts which seem small but should not be neglected.

Hope in this article, you will find answers to most frequently asked SQL interview questions. The knowledge of SQL is must for any tester and this article will help you in preparing the interview.

source: https://www.softwaretestinghelp.com/50-popular-sql-interview-questions-for-testers/

################################ SQL SERVER
################################################

In this tutorial, I will be covering some of the most frequently asked SQL Server Interview Questions to make you familiar with the type of questions that can be asked during a Job Interview related to the SQL SERVER.

The list includes questions from almost all important areas of the SQL Server. These will help you in dealing with the questions beginners and advanced level interview.

SQL Server is one of the most important Relational Database Management Systems (RDBMS) for performing functions of retrieving and storing data. Therefore, many questions are asked from this topic during technical
interviews.

Let's start.

Q #1) Which TCP/IP port does SQL Server run on?

Ans. By default SQL Server runs on port 1433.

Q #2) What is the difference between clustered and non-clustered index?

Ans. A clustered index is an index that rearranges the table in the order of the index itself. Its leaf nodes contain data pages. A table can have only one clustered index.

A non-clustered index is an index that does not re-arranges the table in the order of the index itself. Its leaf nodes contain index rows instead of data pages. A table can have many non-clustered indexes.

Q #3) List
the different index configurations possible for a table?

Ans. A table can have one of the following index configurations:

No indexes

A clustered index

A clustered index and many non-clustered indexes

A non-clustered index

Many non-clustered indexes

Q #4) What is the recovery model? List the types of recovery models available in SQL Server?

Ans. The recovery model tells SQL Server what data should be kept in the transaction log file and for how long. A database can have only one recovery model.

It also tells SQL server which backup is possible in a particular recovery model selected.
There are three types of recovery model:

Full

Simple

Bulk-Logged

Q #5) What are the different backups available in SQL Server?

Ans. Different possible backups are:

Full backup

Differential Backup

Transactional Log Backup

Copy Only Backup

File and Filegroup backup

Q #6) What is a Full Backup?

Ans. A full backup is the most common type of backup in SQL Server. This is the complete backup of the database. It also contains part of the transaction log so it can be recovered.

Q #7) What is OLTP?

Ans. OLTP means Online transaction
processing which follows rules of data normalization to ensure data integrity. Using these rules complex information is broken down into a most simple structure.

Q #8) What is RDBMS?

Ans. RDBMS or Relational Data Base Management Systems are database management systems that maintain data in the form of tables. We can create relationships between the tables. An RDBMS can recombine the data items from different files, providing powerful tools for data usage.

Q #9) What are the properties of the Relational tables?

Ans. Relational tables have six properties:

Values are atomic.

Column values are of
the same kind.

Each row is unique.

The sequence of columns is insignificant.

The sequence of rows is insignificant.

Each column must have a unique name.

Q #10) What's the difference between a primary key and a unique key?

Ans. The differences between the primary key and a unique key are:

The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. They create a clustered index on the column and cannot be null.

A Unique key is a column whose values also uniquely identify every row in a table but they create a non-clustered index by default and it allows one
NULL only.

Q #11) When is the UPDATE_STATISTICS command used?

Ans. As the name implies UPDATE_STATISTICS command updated the statistics used by the index to make the search easier.

Q #12) What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Ans. The differences between HAVING CLAUSE and WHERE CLAUSE is:

Both specify a search condition but Having clause is used only with the SELECT statement and typically used with GROUP BY clause.

If GROUP BY clause is not used then Having behaved like WHERE clause only.

Q #13) What is Mirroring?

Ans. Mirroring is a high availability
solution. It is designed to maintain a hot standby server which is consistent with the primary server in terms of a transaction. Transaction Log records are sent directly from the principal server to a secondary server which keeps a secondary server up to date with the principal server.

Q #14) What are the advantages of the Mirroring?

Ans. Advantages of Mirroring are:

It is more robust and efficient that Log shipping.

It has an automatic failover mechanism.

The secondary server is synced with the primary in near real-time.

Q #15) What is Log Shipping?

Ans. Log shipping is nothing but the automation
of backup and restores of a database from one server to another standalone standby server. This is one of the disaster recovery solutions. If one server fails for some reason we will have the same data available on the standby server.

Q #16) What are the advantages of Log shipping?

Ans. Advantages of Log Shipping:

Easy to set up.

The secondary database can be used as a read-only purpose.

Multiple secondary standby servers are possible

Low maintenance.

Q #17) Can we take the full database backup in Log shipping?

Ans. Yes, we can take the full database backup. It wonâ€™t affect the log
shipping.

Q #18) What is an execution plan?

Ans. An execution plan is a graphical or textual way of showing how the SQL server breaks down a query to get the required result. It helps a user to determine why queries are taking more time to execute and based on the investigation user can update their queries for the maximum result.

In Query Analyzer is an option called â€œShow Execution Planâ€ (located on the Query drop-down menu). If this option is turned on it will display a query execution plan in a separate window when a query is run again.

Q #19) What is the Stored Procedure?

Ans. A
stored procedure is a set of SQL queries that can take input and send back output. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

Q #20) List the advantages of using Stored Procedures?

Ans. Advantages of using Stored procedures are:

Stored procedure boosts application performance.

Stored procedure execution plans can be reused as they cached in SQL Server's memory which reduces server overhead.

Stored procedures can be reused.

Stored
procedures can encapsulate logic. You can change the stored procedure code without affecting clients.

Stored procedures provide better security for your data.

Q #21) What is identity in SQL?

Ans. An identity column in the SQL automatically generates numeric values. We can be defined as a start and increment value of the identity column. Identity columns do not need to be indexed.

Q #22) What are the common performance issues in SQL Server?

Ans. Following are the common performance issues:

Deadlocks

Blocking

Missing and unused indexes.

I/O bottlenecks

Poor Query
plans

Fragmentation

Q #23) List the various tools available for performance tuning?

Ans. There are various tools available for performance tuning:

Dynamic Management Views

SQL Server Profiler

Server Side Traces

Windows Performance monitor.

Query Plans

Tuning advisor

Q #24) What is a performance monitor?

Ans. Windows performance monitor is a tool to capture metrics for the entire server. We can use this tool for capturing events of the SQL server also.

Some useful counters are â€“ Disks, Memory, Processors, Network, etc.

Q #25) What are 3 ways to get a count
of the number of records in a table?

Ans. SELECT * FROM table_Name

SELECT COUNT(*) FROM table_Name

SELECT rows FROM indexes WHERE id = OBJECT_ID(tableName) AND indid< 2

Q #26) Can we rename a column in the output of the SQL query?

Ans. Yes by using the following syntax we can do this.

SELECT column_name AS new_name FROM table_name;

Q #27) What is the difference between a Local and a Global temporary table?

Ans. If defined inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists
permanently in the database but its rows disappear when the connection is closed.

Q #28) What is the SQL Profiler?

Ans. SQL Profiler provides a graphical representation of events in an instance of SQL Server for monitoring and investment purpose. We can capture and save the data for further analysis. We can put filters as well to captures the specific data we want.

Q #29) What do you mean by authentication modes in SQL Server?

Ans. There are two authentication modes in SQL Server.

Windows mode

Mixed Mode â€“ SQL and Windows.

Q #30) How can we check the SQL Server
version?

Ans. By running the following command:

SELECT @@Version

Q #31) Is it possible to call a stored procedure within a stored procedure?

Ans. Yes, we can call a stored procedure within a stored procedure. It is called the recursion property of the SQL server and these types of stored procedures are called nested stored procedures.

Q #32) What is the SQL Server Agent?

Ans. SQL Server agent allows us to schedule the jobs and scripts. It helps in implementing the day to day DBA tasks by automatically executing them on a scheduled basis.

Q #33) What is the
PRIMARY KEY?

Ans. The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused.

Q #34) What is a UNIQUE KEY constraint?

Ans. A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

Q #35) What is FOREIGN KEY

Ans. When a one tableâ€™s primary key field is added to related tables to create the common field which relates the two tables, it called a foreign key in other
tables.

Foreign Key constraints enforce referential integrity.

Q #36) What is a CHECK Constraint?

Ans. A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.

Q #37) What are a Scheduled Jobs?

Ans. The scheduled job allows a user to run the scripts or SQL commands automatically on a scheduled basis. The user can determine the order in which commands need to execute and the best time to run the job to avoid the load on the system.

Q #38) What is a heap?

Ans. A heap is a table that
does not contain any clustered index or non-clustered index.

Q #39) What is BCP?

Ans. BCP or Bulk Copy is a tool by which we can copy a large amount of data to tables and views. BCP does not copy the structures the same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

Q #40) What is Normalization?

Ans. The process of table design to minimize the data redundancy is called normalization. We need to divide a database into two or more tables and define relationships between them. Normalization usually involves dividing a database into two
or more tables and defining relationships between the tables.

Q #41) List the different normalization forms?

Ans. Different normalization forms are:

1NF (Eliminate Repeating Groups): Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF (Eliminate Redundant Data): If an attribute depends on only part of a multi-valued key, remove it to a separate table.

3NF (Eliminate Columns Not Dependent On Key): If attributes do not contribute to a description of the key, remove them to a
separate table. All attributes must be directly dependent on the primary key.

BCNF (Boyce-Codd Normal Form): If there are non-trivial dependencies between candidate key attributes, separate them into distinct tables.

4NF (Isolate Independent Multiple Relationships): No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF (Isolate Semantically Related Multiple Relationships): There may be practical constraints on information that justifies separating logically related many-to-many relationships.

ONF (Optimal Normal Form): A model limited to only simple (elemental) facts, as
expressed in Object Role Model notation.

DKNF (Domain-Key Normal Form): A model free from all modification is said to be in DKNF.

Q #42) What is De-normalization?

Ans. De-normalization is the process of adding redundant data to a database to enhance the performance of it. It is a technique to move from higher to lower normal forms of database modeling to speed up database access.

Q #43) What is a Trigger and types of a trigger?

Ans. The trigger allows us to execute a batch of SQL code when a tabled event occurs (Insert, update or delete command executed against a specific table). Triggers are
stored in and managed by DBMS. It can also execute a stored procedure.

3 types of triggers that are available in the SQL Server are as follows:

DML Triggers: DML or Data Manipulation Language triggers are invoked whenever any of the DML commands like INSERT, DELETE or UPDATE happens on the table or the view.

DDL Triggers: DDL or Data Definition Language triggers are invoked whenever any0 changes occur in the definition of any of the database objects instead of actual data. These are very helpful to control the production and development of database environments.

Logon Triggers: These are very special triggers that fire in case of the logon
event of the SQL Server. This is fired before the setup of a user session in the SQL Server.

Q #44) What is the Subquery?

Ans. A Subquery is a subset of select statements whose return values are used in filtering conditions of the main query. It can occur in a SELECT clause, FROM clause and WHERE clause. It nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.

Types of Sub-query:

Single-row sub-query: where the subquery returns only one row

Multiple-row sub-query: where the subquery returns multiple rows, and

Multiple column sub-query: where the sub-query returns multiple
columns

Q #45) What is a Linked Server?

Ans. Linked Servers is a concept by which we can connect another SQL server to a Group and query both the SQL Servers database using T-SQL Statements sp_addlinkedsrvloginisssed to add link server.

Q #46) What is Collation?

Ans. Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.

Q #47) What is View?

Ans. A view is a virtual table that
contains data from one or more tables. Views restrict data access of the table by selecting only required values and make complex queries easy.

Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database

Q #48) Where SQL server usernames and passwords are stored in a SQL server?

Ans. They get stored in System Catalog Views sys.server_principals and
sys.sql_logins.

Q #49) What are the properties of a transaction?

Ans. Generally, these properties are referred to as ACID properties. They are:

Atomicity

Consistency

Isolation

Durability

Q #50) Define UNION, UNION ALL, MINUS, INTERSECT?

Ans. UNION â€“ returns all distinct rows selected by either query.

UNION ALL â€“ returns all rows selected by either query, including all duplicates.

MINUS â€“ returns all distinct rows selected by the first query but not by the second.

INTERSECT â€“ returns all distinct rows selected
by both queries.

Q #51) What is SQL Server used for?

Ans. SQL Server is one of the very popular Relational Database Management Systems. This is a product from Microsoft to store and manage the information in the database.

Q #52) Which language is supported by SQL Server?

Ans. SQL Server is based upon the implementation of the SQL also known as Structured Query Language to work with the data inside the database.

Q #53) Which is the latest version of SQL Server and when it is released?

Ans. SQL Server 2017 is the latest version of SQL Server that is available in the market and
Microsoft launched this on 2 October 2017 with the support of the Linux O/S.

Q #54) What are the various editions of SQL Server 2017 that are available in the market?

Ans. SQL Server 2017 is available in 4 editions. These are as follows:

Enterprise: This supports leading the high performance for the Tier 1 database along with the capability of supporting business intelligence and workloads of advanced analytics.

Standard: This supports mid-tier applications to achieve fast performance. This can be easily upgraded to an enterprise edition also without having any changes in the coding part.

Express: This is available for free
and supports the building of web and mobile applications up to 10 GB in the size.

Developer: This supports building, testing and demonstrating applications in a non-production.

Q #55) What are functions in the SQL Server?

Ans. Functions are the sequence of the statements which accept inputs, process the inputs to perform some specific task and then provide the outputs. Functions should have some meaningful name but these should not start with a special character such as %,#,@, etc.

Q #56) What is a User-Defined function in the SQL Server and what is its advantage?

Ans. User-Defined Function is a function that can be written
as per the needs of the user by implementing your logic. The biggest advantage of this function is that the user is not limited to pre-defined functions and can simplify the complex code of pre-defined function by writing a simple code as per the needs.

This returns Scalar value or a table.

Q #57) Explain the creation and execution of a user-defined function in the SQL Server?

Ans. A User-Defined function can be created in the following way:

Create Function fun1(@num int)

returns table

return select * from employee where empid=@num

This function can be executed as
follows:

select * from fun1(12)

So, in the above case, a function with the name of â€˜fun1â€™ is created to fetch employee details of an employee having empid=12.

Q #58) What are the Pre-Defined functions in the SQL Server?

Ans. These are Built-In functions of the SQL Server like String functions which are provided by SQL Server like ASCII, CHAR, LEFT, etc. string functions.

Q #59) Why are Views required in the SQL Server or any other database?

Ans. Views are very beneficial because of the following reasons:

Views are required to hide the complexity that is
involved in the database schema and also to customize the data for a particular set of users.

Views provide a mechanism to control access to particular rows and columns.

These help in aggregating the data to improve the performance of the database.

Q #60) What is TCL in SQL Server?

Ans. TCL is Transaction Control Language Commands which are used to manage the transactions in the SQL Server.

Q #61) Which TCL Commands are available on the SQL Server?

Ans. There are 3 TCL Commands in the SQL Server. These are as follows:

Commit: This command is used to save the transaction permanently in the
database.

Rollback: This is used to roll back the changes that are done i.e. to restore the database in the last committed state.

Save Tran: This is used for saving the transaction to provide the convenience that the transaction can be rolled back to the point wherever required.

Q #62) What are the 2 types of classifications of constraints in the SQL Server?

Ans. Constraints are classified into the following 2 types in the SQL Server:

Column Types Constraints: These constraints are applied to the columns of a table in the SQL Server. The definition of these can be given at the time of the creation of a table in the database.

Table
Types Constraints: These constraints are applied on a table and these are defines after the creation of a table is completed. Alter command is used to apply the table type constraint.

Q #63) How is table type constraint applied to a table?

Ans. Table Type Constraint is applied in the following way:

Alter Table Name of the Constraint

Alter Table Constraint_1

Q #64) What are the different types of Columns Types Constraints in the SQL Server?

Ans. SQL Server provides 6 types of Constraints. These are as follows:

SQL Server provides 6 types of Constraints. These are as
follows:

Not Null Constraint: This puts a constraint that the value of a column

cannot be null.

Check Constraint: This puts a constraint by checking some particular

condition before inserting data in the table.

Default Constraint: This constraint provides some default value that can

be inserted in the column if no value is specified for that column.

Unique Constraint: This puts a constraint that each row of a particular

column must have a unique value. More than one unique constraint can

be applied to a single table.

Primary Key Constraint: This puts a constraint to have a primary key in the

table to identify each
row of a table uniquely. This cannot be null or

duplicate data.

Foreign Key Constraint: This puts a constraint that the foreign key should

be there. A Primary key in one table is the foreign key of another table.

Foreign Key is used to create a relation between 2 or more tables.

Q #65) What command is used to delete a table from the database in the SQL Server and how?

Ans. Delete Command is used to delete any table from the database in the SQL Server. Following is the way to use this command:

Delete Name of the table

Example: If the name of a table is â€œemployeeâ€ then delete command to delete
this table can be written as Delete employee.

Q #66) Why is replication required on the SQL Server?

Ans. Replication is the mechanism that is used to synchronize the data among the multiple servers with the help of a replica set.

This is mainly used to increase the capacity of the reading and to provide an option to its users to select among various servers to perform the read/write operations.

Q #67) What command is used to create a database in the SQL Server and how?

Ans. CREATEDATABASE Command is used to create any database in the SQL Server. Following is the way to use this
command:

CREATEDATABASE Name of the Database

Example: If the name of a database is â€œemployeeâ€ then create command to create this database that can be written as CREATEDATABASE employee.

Q #68) What function does a database engine serve in the SQL Server?

Ans. Database Engine is a type of service in the SQL Server which starts as soon as the Operating System starts. This may run by default depending upon the settings in the O/S.

Q #69) What are the advantages of having an index on the SQL Server?

Ans. The index has the following advantages:

Index
supports the mechanism of having faster data retrieval from the database.

This forms a data structure in a way that helps in minimizing data comparisons.

This improves the performance of the retrieval of the data from the database.

Conclusion

This is all about SQL Server Interview Questions. I hope this article must have provided insight regarding the questions that can be asked in an interview and you can now confidently handle your interview process.

Practice all the important SQL Server topics for better understanding and appearing for the interview
confidently.

source:https://www.softwaretestinghelp.com/sql-server-interview-question-for-testers-2/

############################## MYSQL ########################################

1. What is MySQL?

MySQL is an open source DBMS which is built, supported and distributed by MySQL AB (now acquired by Oracle)

2. What are the technical features of MySQL?

MySQL database software is a client or server system which includes

Multithreaded SQL server supporting various client programs and libraries

Different backend

Wide range of application programming
interfaces and

Administrative tools.

3. Why MySQL is used?

MySQL database server is reliable, fast and very easy to use. This software can be downloaded as freeware and can be downloaded from the internet.

4. 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 =, <,>, = >,=<

â€¢ AUTO_INCREMENT is not supported by HEAP tables

â€¢ Indexes should be NOT
NULL

5. What is the default port for MySQL Server?

The default port for MySQL server is 3306.

What are the advantages of MySQL when compared with Oracle?

MySQL is open source software which is available at any time and has no cost involved.

MySQL is portable

GUI with command prompt.

Administration is supported using MySQL Query Browser

7. Differentiate between FLOAT and DOUBLE?

Following are differences for FLOAT and DOUBLE:

â€¢ Floating point numbers are stored in FLOAT with eight place accuracy and it has four
bytes.

â€¢ Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.

8. Differentiate CHAR_LENGTH and LENGTH?

CHAR_LENGTH is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.

9. How to represent ENUMs and SETs internally?

ENUMs and SETs are used to represent powers of two because of storage optimizations.

10. What is the usage of ENUMs in MySQL?

ENUM is a string object used to specify set of predefined values
and that can be used during table creation.

Create table size(name ENUM('Small', 'Medium','Large');

11. Define REGEXP?

REGEXP is a pattern match in which matches pattern anywhere in the search value.

12. Difference between CHAR and VARCHAR?

Following are the differences between CHAR and VARCHAR:

CHAR and VARCHAR types differ in storage and retrieval

CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and
255

When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.

13. Give string types available for column?

The string types are:

SET

BLOB

ENUM

CHAR

TEXT

VARCHAR

14. How to get current MySQL version?

SELECT VERSION ();

is used to get the current version of MySQL.

15. What storage engines are used in MySQL?

Storage engines are called table types and data is stored in files using various
techniques.

Technique involves:

Storage mechanism

Locking levels

Indexing

Capabilities and functions.

SQL Interview

16. What are the drivers in MySQL?

Following are the drivers available in MySQL:

PHP Driver

JDBC Driver

ODBC Driver

C WRAPPER

PYTHON Driver

PERL Driver

RUBY Driver

CAP11PHP Driver

Ado.net5.mxj

17. What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type?

TIMESTAMP column is updated with Zero when the table is created. UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to current time whenever there is a change in other fields of the table.

18. What is the difference between primary key and candidate key?

Every row
of a table is identified uniquely by primary key. There is only one primary key for a table.

Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.

19. 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>

20. What does myisamchk do?

It compress the MyISAM tables, which reduces their disk or memory usage.

21. 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.

22. What is the difference between MyISAM Static and MyISAM Dynamic?

In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths.

MyISAM Static would be easier to restore in case of corruption.

23. What are federated tables?

Federated tables which allow access to the tables located on other databases on other servers.

24. What, if a table has one
column defined as TIMESTAMP?

Timestamp field gets the current timestamp whenever the row gets altered.

25. 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.

26. 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.

27. How can you see all indexes defined for a
table?

Indexes are defined for the table by:

SHOW INDEX FROM <tablename>;

28. What do you mean by % and _ in the LIKE statement?

% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.

29. How can we convert between Unix & MySQL timestamps?

UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp

FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.

30. What are the column comparisons operators?

The = , <>, <=, <, >=,
>,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.

31. How can we get the number of rows affected by query?

Number of rows can be obtained by

SELECT COUNT (user_id) FROM users;

32. Is Mysql query is case sensitive?

No.

SELECT VERSION(), CURRENT_DATE;

SeLect version(), current_date;

seleCt vErSiOn(), current_DATE;

SELECT VERSION(), CURRENT_DATE;

SeLect version(), current_date;

seleCt vErSiOn(),
current_DATE;

All these examples are same. It is not case sensitive.

33. What is the difference between the LIKE and REGEXP operators?

LIKE and REGEXP operators are used to express with ^ and %.

SELECT * FROM employee WHERE emp_name REGEXP "^b";

SELECT * FROM employee WHERE emp_name LIKE "%b";

SELECT * FROM employee WHERE emp_name REGEXP "^b";

SELECT * FROM employee WHERE emp_name LIKE "%b";

34. What is the difference between BLOB AND TEXT?

A BLOB is a binary large object that can hold a variable amount of data.
There are four types of BLOB â€“

TINYBLOB

BLOB

MEDIUMBLOB and

LONGBLOB

They all differ only in the maximum length of the values they can hold.

A TEXT is a case-insensitive BLOB. The four TEXT types

TINYTEXT

TEXT

MEDIUMTEXT and

LONGTEXT

They all correspond to the four BLOB types and have the same maximum lengths and storage requirements.

The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive for BLOB values and case-insensitive for TEXT values.

35. What is the difference between
mysql_fetch_array and mysql_fetch_object?

Following are the differences between mysql_fetch_array and mysql_fetch_object:

mysql_fetch_array() -Returns a result row as an associated array or a regular array from database.

mysql_fetch_object â€“ Returns a result row as object from database.

36. How can we run batch mode in mysql?

Following commands are used to run in batch mode:

mysql ;

mysql mysql.out

mysql ;

mysql mysql.out

37. Where MyISAM table will be stored and also give their formats of
storage?

Each MyISAM table is stored on disk in three formats:

The â€˜.frmâ€™ file stores the table definition

The data file has a â€˜.MYDâ€™ (MYData) extension

The index file has a â€˜.MYIâ€™ (MYIndex) extension

38. What are the different tables present in MySQL?

Total 5 types of tables are present:

MyISAM

Heap

Merge

INNO DB

ISAM

MyISAM is the default storage engine as of MySQL .

39. What is ISAM?

ISAM is abbreviated as Indexed Sequential Access Method.It was developed by IBM to store and retrieve
data on secondary storage systems like tapes.

40. What is InnoDB?

lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.

41. How MySQL Optimizes DISTINCT?

DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

42. How to enter Characters as HEX Numbers?

If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just
prefix HEX numbers with (Ox).

A HEX number string will be automatically converted into a character string, if the expression context is a string.

43. How to display top 50 rows?

In MySql, top 50 rows are displayed by using this following query:

SELECT * FROM

LIMIT 0,50;

SELECT * FROM

LIMIT 0,50;

44. How many columns can be used for creating Index?

Maximum of 16 indexed columns can be created for any standard table.

45. What is the different between NOW() and CURRENT_DATE()?

NOW () command is used
to show current year,month,date with hours,minutes and seconds.

CURRENT_DATE() shows current year,month and date only.

46. What are the objects can be created using CREATE statement?

Following objects are created using CREATE statement:

DATABASE

EVENT

FUNCTION

INDEX

PROCEDURE

TABLE

TRIGGER

USER

VIEW

47. How many TRIGGERS are allowed in MySql table?

SIX triggers are allowed in MySql table. They are as follows:

BEFORE INSERT

AFTER INSERT

BEFORE UPDATE

AFTER UPDATE

BEFORE DELETE and

AFTER
DELETE

48. What are the nonstandard string types?

Following are Non-Standard string types:

TINYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

49. What are all the Common SQL Function?

CONCAT(A, B) â€“ Concatenates two string values to create a single string output. Often used to combine two or more fields into one single field.

FORMAT(X, D) â€“ Formats the number X to D significant digits.

CURRDATE(), CURRTIME() â€“ Returns the current date or time.

NOW() â€“ Returns the current date and time as one value.

MONTH(), DAY(),
YEAR(), WEEK(), WEEKDAY() â€“ Extracts the given data from a date value.

HOUR(), MINUTE(), SECOND() â€“ Extracts the given data from a time value.

DATEDIFF(A, B) â€“ Determines the difference between two dates and it is commonly used to calculate age

SUBTIMES(A, B) â€“ Determines the difference between two times.

FROMDAYS(INT) â€“ Converts an integer number of days into a date value.

50. Explain Access Control Lists.

An ACL (Access Control List) is a list of permissions that is associated with an object. This list is the basis for MySQL serverâ€™s
security model and it helps in troubleshooting problems like users not being able to connect.

MySQL keeps the ACLs (also called grant tables) cached in memory. When a user tries to authenticate or run a command, MySQL checks the authentication information and permissions against the ACLs, in a predetermined order.

source: https://career.guru99.com/top-50-mysql-interview-questions-answers/

################################# MYSQL ############################################

List of the Most Popular MySQL Interview Questions with Example Queries (Latest MySQL questions of 2020):

MySQL is an essential part of the
modern web application and the knowledge of MySQL is a common requirement for any web developer's job.

A list of basic, as well as advanced level MySQL questions for freshers and experienced professionals, are explained in this article with necessary examples.

These questions will, in turn, help any job seeker to increase their confidence level and face the interview successfully.

Most Frequently Asked MySQL Interview Questions

Enlisted below are the most popular MySQL interview questions that are commonly asked in the interviews by experts.

Q #1) What is MySQL?

Answer:

MySQL is
an open-source DBMS which is developed and distributed by Oracle Corporation.

It is supported by most of the popular operating systems, such as Windows, Linux, etc. It can be used to develop different types of applications but it is mainly used for developing web applications.

MySQL uses GPL (GNU General Public License) license so that anyone can download and install it for developing those applications which will be published or distributed freely. But if a user wants to develop any commercial application using MySQL then he/she will need to buy the commercial version of MySQL.

Q #2) What are the features of
MySQL?

Answer:

MySQL has several useful features that make it a popular database management software.

Some important features of MySQL are mentioned below.

It is reliable and easy to use too.

It is a suitable database software for both large and small applications.

Anyone can install and use it at no cost.

It is supported by many well-known programming languages, such as PHP, Java, C++, PERL, etc.

It supports standard SQL (Structured Query Language).

The open-source license of MySQL is customizable. Hence, a developer can modify it according to the requirements of the
application.

Q #3) What is the default port number of MySQL?

Answer:

The default port number of MySQL is 3306.

Q #4) How can you find out the version of the installed MySQL?

Answer:

The version of the installed MySQL server can be found out easily by running the following command from the MySQL prompt.

mysql> SHOW VARIABLES LIKE â€œ%version%â€;

Q #5) What are the advantages and disadvantages of using MySQL?

Answer:

There are several advantages of MySQL which are making it a more popular database system
now.

Some significant advantages and disadvantages of MySQL are mentioned below.

Advantages:

It is well-known for its reliable and secure database management system. Transactional tasks of the website can be done more securely by using this software.

It supports different types of storage engines to store the data and it works faster for this feature.

It can handle millions of queries with a high-speed transactional process.

It supports many advanced level database features, such as multi-level transactions, data integrity, deadlock identification, etc.

Maintenance and debugging processes are easier for this
software.

Disadvantages:

It is hard to make MySQL scalable.

It is not suitable for a very large type of database.

The uses of stored routines and triggers are limited to MySQL.

Q #6) What is the function of myisamchk?

Answer:

myisamchk is a useful database utility tool that is used to get information about MyISAM database tables.

It is also used for checking, debugging, repairing and optimizing database tables. It is better to use this command when the server is down or when the required tables are not in use by the server.

Syntax:

myisamchk [OPTION]
table_nameâ€¦

The available options of this tool can be retrieved by using the following command.

myisamchk â€“help

To check or repair all MyISAM tables, the following command will be required for executing from the database directory location.

myisamchk *.MYI

Q #7) What are the purposes of using ENUM and SET data types?

Answer:

ENUM data type is used in the MySQL database table to select any one value from the predefined list.

The value of a particular field can be restricted by defining the predefined list as the field which is
declared as ENUM will not accept any value outside the list.

The SET data type is used to select one or more or all values from the predefined list. This data type can also be used to restrict the field for inserting only the predefined list of values like ENUM.

Example:

Run MySQL server from the command prompt and execute the following SQL commands to know the use of ENUM and SET data type.

The following SQL commands create a new database named â€˜newdbâ€™ and select the database for use.

CREATE DATABASE newdb;

USE newdb;

ENUM and SET data
types

The following SQL command will create a table named clients with the fields ENUM and SET data type.

CREATE TABLE clients (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50),

membership ENUM('Silver', 'Gold', 'Diamond'),

interest SET('Movie', 'Music', 'Concert'));

ENUM and SET data types?2

Insert queries will create two records in the table. ENUM field only accepts data from the defined list.

â€˜Premiumâ€™ value does not exist on the ENUM list. Hence, the value of the ENUM field will be empty for the
second record. SET can accept multiple values and both the data will be inserted in the second record.

INSERT INTO clients (name, membership,interest)

VALUES ('Sehnaz','Gold', 'Music'),

('Sourav','Premium', 'Movie,Concert');

SELECT * FROM clients;

ENUM and SET data types3

Q #8) What are the differences between a primary key and a foreign key?

Answer:

The database table uses a primary key to identify each row uniquely. It is necessary to declare the primary key on those tables that require to create a
relationship among them. One or more fields of a table can be declared as the primary key.

When the primary key of any table is used in another table as the primary key or another field for making a database relation, then it is called a foreign key.

The differences between these two keys are mentioned below.

The primary key uniquely identifies a record, whereas foreign key refers to the primary key of another table.

The primary key can never accept a NULL value but foreign key accepts a NULL value.

When a record is inserted in a table that contains the primary key then it is not necessary to insert the value on the table
that contains this primary key field as the foreign key.

When a record is deleted from the table that contains the primary key then the corresponding record must be deleted from the table containing the foreign key for data consistency. But any record can be deleted from the table that contains a foreign key without deleting a related record of another table.

Example:

Two tables named manufacturers and items will be created after executing the following two SQL commands.

Here, the primary key of the manufacturer's table is used as a foreign key in the items table with the field name manufacturer_id. Hence, the manufacturer_id field will
contain only those values that exist in the manufacturer's table.

CREATE TABLE manufacturers (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50));

CREATE TABLE items (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50),

type VARCHAR(50),

brand VARCHAR(50),

manufacturer_id INT,

FOREIGN KEY (manufacturer_id) REFERENCES manufacturers(id));

primary key and foreign key

Q #9) What are the differences between CHAR and VARCHAR data
types?

Answer:

Both CHAR and VARCHAR data types are used to store string data in the field of the table.

The differences between these data types are mentioned below:

CHAR data type is used to store fixed-length string data and the VARCHAR data type is used to store variable-length string data.

The storage size of the CHAR data type will always be the maximum length of this data type and the storage size of VARCHAR will be the length of the inserted string data. Hence, it is better to use the CHAR data type when the length of the string will be the same length for all the records.

CHAR is used to store
small data whereas VARCHAR is used to store large data.

CHAR works faster and VARCHAR works slower.

Example:

The following SQL statement will create a table named Customers. In this table, the data type of name field is VARCHAR and the data type of phone field is CHAR.

The size of the name field will depend on the length of the inserted value. The size of the phone field will always be 14 characters even if the length of the inserted value is less than 14 characters.

CREATE TABLE customers (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50),

phone
CHAR(14))

differences between CHAR and VARCHAR data types

Q #10) What is the purpose of using the TIMESTAMP data type?

Answer:

A TIMESTAMP data type is used to store the combination of date and time value which is 19 characters long.

The format of TIMESTAMP is YYYY-MM-DD HH:MM: SS. It can store data from â€˜1970-01-01 00:00:01â€™ UTC to â€˜2038-01-19 03:14:07â€™ UTC. By default, the current date and time of the server get inserted in the field of this data type when a new record is inserted or updated.

Q #11) What is the difference between mysql_fetch_array() and
ysql_fetch_object() ?

Answer:

Both mysql_fetch_array() and mysql_fetch_object() are built-in methods of PHP to retrieve records from MySQL database table.

The difference between these methods is that mysql_fetch_array() returns the result set as an array and mysql_fetch_object() returns the result set as an object.

Example:

$result = mysql_query("SELECT id, name FROM clients");

//using mysql_fetch_array()

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {

printf("ID: %s Name: %s", $row[0],
$row[1]);

}

//using mysql_fetch_object()

while ($row = mysql_fetch_object($result)) {

printf("ID: %s Name: %s", $row->id, $row->name);

}

Q #12) How can you filter the duplicate data while retrieving records from the table?

Answer:

A DISTINCT keyword is used to filter the duplicate data from the table while retrieving the records from a table.

Example:

The following SQL command shows all the records of the items table. The output shows that the table contains duplicate values in the Type
field.

SELECT * from items;

filter the duplicate data when retrieving records from the table

The following SQL command will display the values of the type field by removing duplicate values.

SELECT DISTINCT type from items;

filter the duplicate data when retrieving records from the table

Q #13) What is the difference between NOW() and CURRENT_DATE()?

Answer:

Both NOW() and CURRENT_DATE() are built-in MySQL methods. NOW() is used to show the current date and time of the server and CURRENT_DATE() is used to show only the date of the server.

SELECT
now();

SELECT NOW()

SELECT current_date();

Select CURRENT_DATE()

Q #14) Which statement is used in a select query for partial matching?

Answer:

REGEXP and LIKE statements can be used in a select query for partial matching. REGEXP is used to search records based on the pattern and LIKE is used to search any record by matching any string at the beginning or end or middle of a particular field value.

Example:

First, check the existing records of the â€˜clients' table by executing the select query.

SELECT * FROM
clients;

statement used in a select query for partial matching1

Run SELECT query with REGEXP clause to search those records from the clients where the client name starts with â€˜Sâ€™

SELECT * FROM clients WHERE name REGEXP â€œ^Sâ€;

statement used in a select query for partial matching2

Run SELECT query with LIKE clause to search those records from the clients where the client name starts with â€˜Aâ€™

SELECT * FROM clients WHERE name LIKE â€œA%â€;

statement used in a select query for partial matching3

Q #15)
Which MySQL function is used to concatenate string?

Answer:

CONCAT() function is used to combine two or more string data. The use of this function is here with an example.

Example:

The following SELECT query with CONCAT() function will combine five words, â€˜Welcome â€˜, â€˜toâ€™, â€˜SoftwareTestingHelpâ€™,â€™.â€™ and â€˜comâ€™.

SELECT CONCAT(â€˜Welcome â€˜,to â€˜,'SoftwareTestingHelp','.',com');

function used to concatenate string1

CONCAT() function can be used on any table as well. The following SELECT
query will show the output by combining two fields, brand and type of items table.

SELECT CONCAT(brand,'=>',type) from items;

function used to concatenate string 2

Q #16) How can you change the name of any existing table by using the SQL statement?

Answer:

The following SQL command is used to rename an existing table of the database.

RENAME TABLE table_name TO new_name

Example:

The following command will show the table list of the newdb database.

SHOW TABLES;

change the name of any existing table by
using the SQL statement1

The following rename command will rename the table items by new name products.

RENAME TABLE items TO products;

SHOW TABLES;

change the name of any existing table by using the SQL statement2

Q #17) How can you retrieve a portion of any column value by using a select query?

Answer:

SUBSTR() function is used to retrieve the portion of any column. The use of this function is explained here with an example.

Example:

Here, the first select command is used to show all the records of the Products table and
the second select command is executed using SUBSTR function and that prints only the first five characters of the name field.

SELECT * FROM products;

SELECT SUBSTR(name,1,5) FROM products;

retrieve the portion of any column value by using a select query1

Q #18) What is the purpose of using a HEAP table?

Answer:

The table which uses a hashed index and stores in the memory is called the HEAP table. It works as a temporary table and it uses the indexes that make it faster than another table type.

When MySQL crashes for any reason then all the data stored in this
table can be lost. It uses fixed-length data types. Hence BLOB and TEXT data types are not supported by this table. It is a useful table for those MySQL tasks where speed is the most important factor and temporary data is used.

Q #19) How can you add and remove any column of a table?

Answer:

The syntax for adding any column in an existing table is shown below.

ALTER TABLE table_name ADD COLUMN column_name column_definition [FIRST|AFTER existing_column]

Example:

DESCRIBE command is used to show the structure of the products table.

DESCRIBE
products;

add and remove any column of a table1

The following ALTER command with ADD COLUMN clause will add a new field named â€˜price' in the table products.

ALTER TABLE products ADD COLUMN price DECIMAL(5,2);

DESCRIBE products;

add and remove any column of a table

The syntax for removing any column from an existing table is shown below.

ALTER TABLE table_name DROP COLUMN column_name;

Example:

The following ALTER command with a DROP COLUMN clause will remove the field named â€˜brand' in the table
â€˜products'.

ALTER TABLE products DROP COLUMN brand;

DESCRIBE products;

add and remove any column of a table?

SQL Interview

Q #20) What is an index? How can an index be declared in MySQL?

Answer:

An index is a data structure of a MySQL table that is used to speed up the querie

SQL Interview

MySQL Tutorial Drupal Interview Questions

Question 8. How Would You Select All The Users, Whose Phone Number Is Null?

Answer :

SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

Question 9. What Does This Query Mean: Select User_name, User_isp From Users Left Join Isps Using (user_id) ?

Answer :

Itâ€™s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id

MYSQL DBA Interview Questions

Question 10. How Do You Find Out Which Auto Increment Was Assigned On The Last Insert?

Answer
:

SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you donâ€™t have to specify the table name.

Drupal Tutorial

Question 11. What Does -i-am-a-dummy Flag To Do When Starting Mysql?

Answer :

Makes the MySQL engine refuse UPDATE and DELETE commands where the WHERE clause is not present.

PHP5 Interview Questions

Question 12. On Executing The Delete Statement I Keep Getting The Error About Foreign Key Constraint Failing. What Do I Do?

Answer :

What it means is that so of the data that
youâ€™re trying to delete is still alive in another table. Like if you have a table for universities and a table for students, which contains the ID of the university they go to, running a delete on a university table will fail if the students table still contains people enrolled at that university. Proper way to do it would be to delete the offending data first, and then delete the university in question. Quick way would involve running SET foreign_key_checks=0 before the DELETE command, and setting the parameter back to 1 after the DELETE is done. If your foreign key was formulated with ON DELETE CASCADE, the data in dependent tables will be removed automatically.

Question 13.
When Would You Use Order By In Delete Statement?

Answer :

When youâ€™re not deleting by row ID. Such as in DELETE FROM techpreparation_com_questions ORDER BY timestamp LIMIT 1.

WordPress Tutorial

Question 14. How Can You See All Indexes Defined For A Table?

Answer :

SHOW INDEX FROM techpreparation_questions;

Question 15. How Would You Change A Column From Varchar(10) To Varchar(50)?

Answer :

ALTER TABLE techpreparation_questions CHANGE techpreparation_content techpreparation_CONTENT
VARCHAR(50).

WordPress Interview Questions

Question 16. How Would You Delete A Column?

Answer :

ALTER TABLE techpreparation_answers DROP answer_user_id.

Joomla Tutorial

Question 17. How Would You Change A Table To Innodb?

Answer :

ALTER TABLE techpreparation_questions ENGINE innodb;

Joomla Interview Questions

Question 18. When You Create A Table, And Then Run Show Create Table On It, You Occasionally Get Different Results Than What You Typed In. What Does Mysql Modify In Your Newly Created
Tables?

Answer :

1. VARCHARs with length less than 4 become CHARs

2. CHARs with length more than 3 become VARCHARs.

3. NOT NULL gets added to the columns declared as PRIMARY KEYs

4. Default values such as NULL are specified for each column

PHP+MySQL Interview Questions

Question 19. How Do I Find Out All Databases Starting With 'tech' To Which I Have Access To?

Answer :

SHOW DATABASES LIKE â€˜tech%â€™;

CakePHP Tutorial

Question 20. How Do You Concatenate Strings In Mysql?

Answer
:

CONCAT (string1, string2, string3)

CakePHP Interview Questions

Question 21. How Do You Get A Portion Of A String?

Answer :

SELECT SUBSTR(title, 1, 10) from techpreparation_questions;

Question 22. What's The Difference Between Char_length And Length?

Answer :

The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but theyâ€™re not the same for Unicode and other encodings.

CodeIgniter Tutorial

Question 23.
How Do You Convert A String To Utf-8?

Answer :

SELECT (techpreparation_question USING utf8);

CodeIgniter Interview Questions

Question 24. What Do % And _ Mean Inside Like Statement?

Answer :

% corresponds to 0 or more characters, _ is exactly one character.

Drupal Interview Questions

Question 25. What Does + Mean In Regexp?

Answer :

At least one character. Appendix G. Regular Expressions from MySQL manual is worth perusing before the interview.

PHP7 Tutorial

Question 26. How Do
You Get The Month From A Timestamp?

Answer :

SELECT MONTH(techpreparation_timestamp) from techpreparation_questions;

PHP7 Interview Questions

Question 27. How Do You Offload The Time/date Handling To Mysql?

Answer :

SELECT DATE_FORMAT(techpreparation_timestamp, â€˜%Y-%m-%dâ€™) from techpreparation_questions; A similar TIME_FORMAT function deals with time.

MYSQL DBA Interview Questions

Question 28. How Do You Add Three Minutes To A Date?

Answer :

ADDDATE(techpreparation_publication_date, INTERVAL
3 MINUTE)

Question 29. What's The Difference Between Unix Timestamps And Mysql Timestamps?

Answer :

Internally Unix timestamps are stored as 32-bit integers, while MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.

Question 30. How Do You Convert Between Unix Timestamps And Mysql Timestamps?

Answer :

UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp, FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp.

Question 31. What Are Enums Used For In Mysql?

Answer
:

You can limit the possible values that go into the table. CREATE TABLE months (month ENUM â€˜Januaryâ€™, â€˜Februaryâ€™, â€˜Marchâ€™,â€¦); INSERT months VALUES (â€™Aprilâ€™);

Question 32. How Are Enums And Sets Represented Internally?

Answer :

As unique integers representing the powers of two, due to storage optimizations.

Question 33. How Do You Start And Stop Mysql On Windows?

Answer :

net start MySQL, net stop MySQL

PHP5 Interview Questions

Question 34. How Do You Start Mysql On
Linux?

Answer :

/etc/init.d/mysql start

Question 35. Explain The Difference Between Mysql And Mysql Interfaces In Php?

Answer :

mysql is the object-oriented version of mysql library functions.

Question 36. What's The Default Port For Mysql Server?

Answer :

3306 is the default port for MYSQL.

WordPress Interview Questions

Question 37. What Does Tee Command Do In Mysql?

Answer :

tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command
note.

Question 38. Can You Save Your Connection Settings To A Conf File?

Answer :

Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that itâ€™s not readable by others.

Question 39. How Do You Change A Password For An Existing User Via Mysqladmin?

Answer :

mysqladmin -u root -p password "newpassword"

Question 40. Use Mysqldump To Create A Copy Of The Database?

Answer :

mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql

Joomla Interview
Questions

Question 41. Have You Ever Used Mysql Administrator And Mysql Query Browser?

Answer :

Describe the tasks you accomplished with these tools.

Question 42. What Are Some Good Ideas Regarding User Security In Mysql?

Answer :

There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.

CakePHP Interview
Questions

Question 43. Explain The Difference Between Myisam Static And Myisam Dynamic. ?

Answer :

In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.

Question 44. What Does Myisamchk Do?

Answer :

It compressed the MyISAM tables, which reduces their disk usage.

Question 45. Explain
Advantages Of Innodb Over Myisam?

Answer :

Row-level locking, transactions, foreign key constraints and crash recovery.

Question 46. Explain Advantages Of Myisam Over Innodb?

Answer :

Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due
to tablespace complexity.

Question 47. What Are Heap Tables In Mysql?

Answer :

HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or

BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.

Question 48. How Do You Control The Max Size Of A Heap Table?

Answer :

MySQL config variable max_heap_table_size.

Question 49. What Are Csv Tables?

Answer :

Those are the special
tables, data for which is saved into comma-separated values files. They cannot be indexed.

Question 50. Explain Federated Tables?

Answer :

Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.

Question 51. What Is Serial Data Type In Mysql?

Answer :

BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

Question 52. What Happens When The Column Is Set To Auto Increment And You Reach The Maximum Value For That
Table?

Answer :

It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.

Question 53. Explain The Difference Between Bool, Tinyint And Bit. ?

Answer :

Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.

Question 54. Explain The Difference Between Float, Double And Real. ?

Answer :

FLOATs store floating point numbers with 8 place accuracy and take up 4
bytes.

DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes.

REAL is a synonym of FLOAT for now.

Question 55. If You Specify The Data Type As Decimal (5,2), What's The Range Of Values That Can Go In This Table?

Answer :

999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.

Question 56. What Happens If A Table Has One Column Defined As Timestamp?

Answer :

That field gets the current timestamp whenever the row gets altered.

Question 57. But What If You
Really Want To Store The Timestamp Data, Such As The Publication Date Of The Article?

Answer :

Create two columns of type TIMESTAMP and use the second one for your real data.

Question 58. Explain Data Type Timestamp Default Current_timestamp On Update Current_timestamp ?

Answer :

The column exhibits the same behavior as a single timestamp column in a table with no other timestamp columns.

Question 59. What Does Timestamp On Update Current_timestamp Data Type Do?

Answer :

On initialization places a zero in that column, on future updates puts
the current value of the timestamp in.

Question 60. Explain Timestamp Default 2006:09:02 17:38:44? On Update Current_timestamp. ?

Answer :

A default value is used on initialization, a current timestamp is inserted on update of the row.

Question 61. If I Created A Column With Data Type Varchar(3), What Would I Expect To See In Mysql Table?

Answer :

CHAR(3), since MySQL automatically adjusted the data type.

Question 62. General Information About Mysql.

Answer :

MySQL is a very fast, multi-threaded, multi-user, and robust SQL
(Structured Query Language) database server.

Question 63. Why Sql Is A Database Management System?

Answer :

A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL. Since computers are very good at handling large amounts of data, database management plays a central role in computing, as stand-alone utilities, or as parts of other applications.

Question 64. Why Use
Mysql?

Answer :

MySQL is very fast, reliable, and easy to use. If that is what you are looking for, you should give it a try. MySQL also has a very practical set of features developed in very close cooperation with our users. You can find a performance comparison of MySQL to some other database managers on our benchmark page. See section 12.7 Using Your Own Benchmarks. MySQL was originally developed to handle very large databases much faster than existing solutions and has been successfully used in highly demanding production environments for several years. Though under constant development, MySQL today offers a rich and very useful set of functions. The
connectivity, speed, and security make MySQL highly suited for accessing databases on the Internet.

Question 65. How Mysql Optimizes Distinct ?

Answer :

DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY will in many cases also need a temporary table.

When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.

If you don't use columns from all used tables, MySQL will stop the scanning of the not used tables as soon as it has found the first match.

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

In the case,
assuming t1 is used before t2 (check with EXPLAIN), then MySQL will stop reading from t2 (for that particular row in t1) when the first row in t2 is found.

Question 66. How Mysql Optimizes Limit ?

Answer :

In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING:

If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.

If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole
table.

When combining LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.

In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries until the key value changes. In this case LIMIT # will not calculate any unnecessary GROUP BY's.

As soon as MySQL has sent the first # rows to the client, it will abort the query.

LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result columns.

The size of temporary tables uses the LIMIT # to calculate how much
space is needed to resolve the query.

Question 67. Mysql - Speed Of Delete Queries ?

Answer :

If you want to delete all rows in the table, you should use TRUNCATE TABLE table_name. The time to delete a record is exactly proportional to the number of indexes. To delete records more quickly, you can increase the size of the index cache.

Question 68. What Is The Difference Between Mysql_fetch_array And Mysql_fetch_object?

Answer :

mysql_fetch_array â€” Fetch a result row as an associative ARRAY, a numeric array, or both

mysql_fetch_object â€” Fetch a result row as an
OBJECT.

Question 69. What Are The Different Table Present In Mysql?

Answer :

MyISAM : This is default. Based on Indexed Sequntial Access Method. The above SQL will create a MyISA table.

ISAM : same

HEAP : Fast data access, but will loose data if there is a crash. Cannot have BLOB, TEXT & AUTO INCRIMENT fields

BDB : Supports Transactions using COMMIT & ROLLBACK. Slower that others.

InoDB : same as BDB

Question 70. What Is Primary Key?

Answer :

A primary key is a single column or multiple columns
defined to have unique values that can be used as row identifications.

Question 71. What Is Foreign Key?

Answer :

A foreign key is a single column or multiple columns defined to have values that can be mapped to a primary key in another table.

Question 72. What Is Index?

Answer :

An index is a single column or multiple columns defined to have values pre-sorted to speed up data retrieval speed.

Question 73. What Is Join?

Answer :

Join is data retrieval operation that combines rows from multiple tables under certain matching
conditions to form a single row.

Question 74. What Is Union?

Answer :

Join is data retrieval operation that combines multiple query outputs of the same structure into a single output. By default the MySQL UNION removes all duplicate rows from the result set even if you donâ€™t explicit using DISTINCT after the keyword UNION.

SELECT customerNumber id, contactLastname name

FROM customers

UNION

SELECT employeeNurrber id, firstname name

FROM employees

id name

103 Schmitt

112 King

114 Ferguson

119 Labrune

121
Bergulfsen

Question 75. What Is Isam?

Answer :

ISAM (Indexed Sequential Access Method) was developed by IBM to store and retrieve data on secondary storage systems like tapes.

Question 76. What Is Innodb?

Answer :

lnnoDB is a transaction safe storage engine developed by Innobase Oy (an Oracle company now).

Question 77. What Is Bdb (berkeleydb)?

Answer :

BDB (BerkeleyDB) is transaction safe storage engine originally developed at U.C. Berkeley. It is now developed by Sleepycat Software, Inc. (an
Oracle company now).

Question 78. What Is Csv?

Answer :

CSV (Comma Separated Values) is a file format used to store database table contents, where one table row is stored as one line in the file, and each data field is separated with comma.

Question 79. What Is Transaction?

Answer :

A transaction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the transaction concept to allow users to group one or more SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either
all committed (applied to the database) or all rolled back (undone from the database).

Question 80. What Is Commit?

Answer :

Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.

Question 81. What Is Rollback?

Answer :

Rollback is a way to terminate a transaction with all database changes not saving to the database server.

Question 82. How Many Groups Of Data Types?

Answer :

MySQL support 3 groups of data types as listed below:

String Data
Types - CHAR, NCHAR, VARCHAR, NVARCHAR, BINARY, VARBINARY, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT, ENUM, SET.

Numeric Data Types - BIT, TINYINT, BOOLEAN, SMALLINT, MEDIUMINT, INTEGER, BIGINT, FLOAT, DOUBLE, REAL, DECIMAL.

Date and Time Data Types - DATE, DATETIME, TIMESTAMP, TIME, YEAR.

Question 83. What Is The Differences Between Char And Nchar?

Answer :

Both CHAR and NCHAR are fixed length string data types. But they have the following differences:

CHARs full name is CHARACTER.

NCHARs full name is NATIONAL CHARACTER.

By default,
CHAR uses ASCII character set. So 1 character is always stored as 1 byte.

By default, NCHAR uses Unicode character set. NCHAR data are stored in UTF8 format. So 1 character could be stored as 1 byte or upto 4 bytes.

Both CHAR and NCHAR columns are defined with fixed lengths in units of characters.

Question 84. How To Escape Special Characters In Sql Statements?

Answer :

There are a number of special characters that needs to be escaped (protected), if you want to include them in a character string. Here are some basic character escaping rules:

The escape character () needs to be escaped as ().

The single quote
(â€˜) needs to be escaped as (â€˜) or (â€œ) in single-quote quoted strings.

The double quote () needs to be escaped as (â€œ) or (â€œâ€œ) in double-quote quoted strings.

The wild card character for a single character () needs to be escaped as (_).

The wild card character for multiple characters (%) needs to be escaped as (%).

The tab character needs to be escaped as (t).

The new line character needs to be escaped as (n).

The carriage return character needs to be escaped as (r).

Question 85. How To Concatenate Two Character Strings?

Answer :

If you want concatenate multiple character strings
into one, you need to use the CONCAT() function. Here are some good examples:

SELECT CONCAT(â€™Welcomeâ€™,â€™ toâ€™) FROM DUAL;

Welcome to

SELECT CONCAT(wjâ€™,â€™centerâ€™,â€™.comâ€™) FROM DUAL;

wisdomjobs.com

Question 86. How To Enter Characters As Hex Numbers?

Answer :

If you want to enter characters as HEX numbers, you can quote HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox). A HEX number string will be automatically converted into a character string, if the expression context is a string. Here are some good
examples:

SELECT X313233â€™ FROM DUAL;

123

SELECT 0x414243 FROM DUAL;

ABC

Question 87. How To Enter Boolean Values In Sql Statements?

Answer :

If you want to enter Boolean values in SQL statements, you use (TRUE), (FALSE), (true), or (false). Here are some good examples:

SELECT TRUE, true, FALSE, false FROM DUAL;

Question 88. How To Convert Numeric Values To Character Strings?

Answer :

You can convert numeric values to character strings by using the CAST(value AS CHAR) function as shown in the following examples:

SELECT
CAST(4123.45700 AS CHAR) FROM DUAL;

4123.45700

Question 89. How To Get Rid Of The Last 2 0's?

Answer :

SELECT CAST(4.12345700E+3 AS CHAR) FROM DUAL;

4123.457

SELECT CAST(1/3 AS CHAR);

0.3333

Question 90. How To Use In Conditions?

Answer :

An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE. Some examples are :

SELECT 3 IN (1,2,3,4,5) FROM DUAL;

SELECT 3 NOT IN (1,2,3,4,5) FROM DUAL;

SELECT Yâ€™ IN
(â€˜Fâ€™,â€™Yâ€™,I) FROM DUAL;

Question 91. How To Use Like Conditions?

Answer :

A LIKE condition is also called pattern patch. There are 3 main rules on using LIKE condition:

is used in the pattern to match any one character.

% is used in the pattern to match any zero or more characters.

ESCAPE clause is used to provide the escape character in the pattern.

Question 92. How To Present A Past Time In Hours, Minutes And Seconds?

Answer :

If you want show an article was posted â€œn hours n minutes and n seconds agoâ€™, you can use the
TIMEDIFF(NOWO, pastTime) function as shown in the following are:

SELECT TIMEDIFF(NOWO, â€˜2006-07-01 04:09:49â€™) FROM DUAL;

06:42:58

SELECT TIM E_FORMAT(TI M EDI FF( NOWO, â€˜2006-06-30 04:09:49â€™),

â€˜%H hours, %i minutes and %s seconds ago.â€™) FROM DUAL;

30 hours, 45 minutes and 22 seconds ago.

Question 93. How To Add A New Column To An Existing Table In Mysql?

Answer :

ALTER TABLE tip ADD COLUMN author VARCHAR(40);

Query OK, 1 row affected (0.18 sec)

Records: 1 Duplicates: 0 Warnings: 0

Question 94. How To Delete An Existing Column In A
Table?

Answer :

ALTER TABLE tip DROP COLUMN create_date;

Query OK, 1 row affected (0.48 sec)

Records: 1 Duplicates: 0 Warnings: 0

SQL Interview

Question 95. How To Rename An Existing Column In A Table?

Answer :

ALTER TABLE tip CHANGE COLUMN subject title VARCHAR(60);

Question 96. How To Rename An Existing Table In Mysql?

Answer :

ALTER TABLE tip RENAME TO faq;

Question 97. How To Create A Table Index In Mvsql?

Answer :

If you have a table with a lots of rows, and you know that one of the columns will be used often as a search criteria, you can add an index for that column to improve the search performance. To add an index, you can use the â€œCREATE INDEXâ€ statement as
shown in the following script:

<pre>mysql> CREATE TABLE tip (id INTEGER PRIMARY KEY,

subject VARCHAR(80) NOT NULL,

description VARCHAR(256) NOT NULL,

create_date DATE NULL); Query OK,

0 rows affected (0.08 sec)</pre>

mysql> CREATE INDEX tip_subject ON tip(subject);

Query OK,

0 rows affected (0.19 sec)

Records: 0 Duplicates: 0 Warnings: 0

Question 98. How To Get A List Of Indexes Of An Existing Table?

Answer :

If you want to see the index you have just created for an existing table, you can use the â€œSHOW INDEX FROM tableNameâ€
command to get a list of all indexes in a given table.

Question 99. How To Drop An Existing Index In Mysql?

Answer :

If you donâ€™t need an existing index any more, you should delete it with the â€œDROP INDEX indexName ON tableNameâ€ statement. Here is an example SQL script :

mysqi> DROP INDEX tip_subject ON tip;

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

Question 100. How To Drop An Existing View In Mysql?

Answer :

If you have an existing view, and you dont want it anymore, you can delete it by using the
â€œDROP VIEW viewNameâ€ statement

Question 101. How To Create A New View In Mysql?

Answer :

You can create a new view based on one or more existing tables by using the

â€œCREATE VIEW viewName AS selectStatementâ€ .

Question 102. How To Increment Dates By 1111 Mysql?

Answer :

If you have a date, and you want to increment it by 1 day, you can use the DATE_ADD(date, INTERVAL 1 DAY) function. You can also use the date interval add operation as â€œdate + INTERVAL 1 DAY.

source:
https://www.wisdomjobs.com/e-university/mysql-interview-questions.html