You have an interview lined up for an SQL-related job. Great! But are you well prepared to crack it? Do you know enough SQL to clear the interview? Are you wondering what SQL interview questions you will be asked?

If you are preparing for an SQL interview, this article will provide you the most commonly asked MySQL and SQL interview questions. This blog covers both basic and advanced SQL interview questions for freshers and experienced candidates that an interviewer might ask during an interview. Apart from guiding you in your interviews, the detailed answers provided in this article will give you a basic understanding of different concepts related to SQL and MySQL.

Table of Contents 

SQL is one of the important programming languages to interact with various database systems. It plays an important role in the collection and analysis of the data. There are various profiles like data analyst, database administrator, and data architect that require knowledge of SQL. It is a must-have skill for technical as well as non-technical individuals. 

Basic Level SQL Interview Questions

Q1. What is DBMS?

Ans. A Database Management System (DBMS) is system software for creating and managing databases. It serves as an interface between databases and end-users or application programs so that data is consistently organized and remains easily accessible. It allows end-users to create, read, update, and delete data in a database. There are two types of DBMS:

  • Relational Database Management System (RDBMS): In RDBMS, the data is stored in relations (tables). Example – MySQL.
  • Non-Relational Database Management System (often called NoSQL databases): It stores data in a non-tabular form.  Example – MongoDB

Explore courses related to SQL: 

Popular Technology Courses Popular Database Courses
Popular Programming Courses Popular Data Visualization Courses

Q2. What is SQL?

Ans. SQL (structured querying language) is a computer language used to create, update, and modify a database. It is the standard language for Relational Database System. All the RDMS like MySQL, MS Access, Oracle, and SQL Server use SQL as their standard database language.

Q3. What is MySQL?

And. MySQL is an open-source relational database management system (RDBMS) that is developed and distributed by Oracle Corporation. Supported by various operating systems, such as Windows, Unix, Linux, etc., MySQL can be used to develop different types of applications. Known for its speed, reliability, and flexibility, MySQL is mainly used for developing web applications.

Q4. What are the subsets of SQL? Explain them. 

Ans. The following are the three subsets of SQL: 

  1. Data Definition Language (DDL) – It allows end-users to CREATE, ALTER, and DELETE database objects.
  2. Data Manipulation Language (DML) – With this, you can access and manipulate data. It allows you to Insert, Update, Delete, and Retrieve data from the database.
  3. Data Control Language (DCL) – This lets you control access to the database. It includes the Grant and Revoke permissions to manipulate or modify the database.

Q5. What is the primary key?

Ans. A primary key constraint uniquely identifies each row/record in a database table. Primary keys must contain unique values. Null value and duplicate values are not allowed to be entered in the primary key column. A table can have only one primary key. It can consist of single or multiple fields.

Q6. What is a foreign key?

Ans. A foreign key (often called the referencing key) is used to link two tables together. It is a column or a combination of columns whose values match a Primary Key in a different table. It acts as a cross-reference between tables because it references the primary key of another table and established a link between them.

Learn – what is Database Administration?

Q7. What is RDBMS?

Ans. Relational Database Management System or RDBMS is based on the relational database model and is among the most popular database management systems.

Q8. What are the features of MySQL?

Ans. Here are some of the important features of MySQL:

  • It is reliable and easy to use 
  • It supports standard SQL (Structured Query Language)
  • MySQL is secure as it consists of a data security layer that protects sensitive data from unauthorized users
  • MySQL has a flexible structure and supports a large number of embedded applications 
  • It is one of the very fast database languages
  • It is a suitable database software for both large and small applications
  • MySQL offers very high-performance results compared to other databases
  • It is supported by many well-known programming languages, such as PHP, Java, and C++
  • It is free to download and use

Also Read>> PHP Interview Questions and Answers

Q9. What are the disadvantages of MySQL?

Ans. The disadvantages of MySQL are: 

  • It is hard to make MySQL scalable
  • It does not support a very large database size as efficiently 
  • MySQL does not support SQL check constraint
  • It is prone to data corruption

Q10. What are the differences between MySQL vs SQL?

Ans. The differences between MySQL and SQL are: 

MySQL SQL
1. It is a relational database that uses SQL to query a database 1. It is a query language
2. MySQL supports multiple storage engines and plug-in storage engines 2. SQL supports a single storage engine
3. It is a database that stores the existing data in a database in an organized manner. 3. SQL is used to access, update, and manipulate the data stored in a database
4. Supports many platforms 4. Supports only Linux and Windows
5. It has a complex syntax 5. It has a simpler syntax

Q11. What is a unique key?

Ans. A unique key is a set of one or more than one field/column of a table that uniquely identifies a record in a database table. A primary key is a special kind of unique key.

Q12. Explain the different types of indexes in SQL.

Ans. There are three types of indexes in SQL:

  1. Unique Index – It does not allow a field to have duplicate values if the column is unique indexed.
  2. Clustered Index – This index defines the order in which data is physically stored in a table. It reorders the physical order of the table and searches based on key values. There can be only one clustered index per table.
  3. Non-Clustered Index – It does not sort the physical order of the table and maintains a logical order of the data. Each table can have more than one non-clustered index.

Q13. What is the difference between TRUNCATE and DELETE?

Ans. This is one of the most commonly asked SQL interview questions. The difference between TRUNCATE and DELETE are:

DELETE TRUNCATE
Delete command is used to delete a specified row in a table. Truncate is used to delete all the rows from a table.
You can rollback data after using the delete statement. You cannot rollback data.
It is a DML command. It is a DDL command.
It is slower than a truncate statement. It is faster.

Q14. What is the difference between:

SELECT * FROM MyTable WHERE MyColumn <> NULL

SELECT * FROM MyTable WHERE MyColumn IS NULL

Ans. The first syntax will not work because NULL means ‘no value’, and you cannot use scalar value operators. This is why there is a separate IS – a NULL predicate in SQL.

Also explore: 

Q15. What is the difference between CHAR and VARCHAR?

Ans. CHAR is a fixed-length character data type, while VARCHAR is a variable-length character data type.

Q16. What is a subquery in SQL? What are the different types of a subquery?

Ans. A subquery is a query within another query. When there is a query within a query, the outer query is called the main query, while the inner query is called a subquery. There are two types of a subquery:

  • Correlated subquery: It obtains values from its outer query before it executes. When the subquery returns, it passes its results to the outer query.
  • Non-Correlated subquery: It executes independently of the outer query. The subquery executes first and then passes its results to the outer query. Both inner and outer queries can run separately.

Q17. What is collation sensitivity?

Ans. Collation sensitivity defines the rules to sort and compare the strings of character data, based on correct character sequence, case-sensitivity, character width, and accent marks, among others.

Q18. What are the different types of collation sensitivity?

Ans. There are four types of collation sensitivity, which include –

  • Accent sensitivity
  • Case sensitivity
  • Kana sensitivity
  • Width sensitivity

Q19. What is a “scheduled job” or “scheduled task”?

Ans. Scheduled job or task allows automated task management on regular or predictable cycles. One can schedule administrative tasks and decide the order of the tasks.

Check out the best Programming courses.

Q20. Can you name different types of MySQL commands?

Ans. SQL commands are divided into the following –

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)
  • Data Control Language (DCL)
  • Transaction Control Language (TCL)

MySQL Commands

Q21. Explain different DDL commands in MySQL.

Ans. DDL commands include –

  • CREATE – Used to create the database or its objects like table, index, function, views, triggers, etc.
  • DROP – Used to delete objects
  • ALTER – Used to change database structures
  • TRUNCATE – It erases all records from a table, excluding its database structure
  • COMMENT – Used to add comments to the data dictionary
  • RENAME – Used to rename a database object

Q22. Explain different DML commands in MySQL.

Ans. DML commands include –

  • SELECT – Used to select specific database data
  • INSERT – Used to insert new records into a table
  • UPDATE – It helps in updating existing records
  • DELETE – Used to delete existing records from a table
  • MERGE – Used to UPSERT operation (insert or update)
  • CALL – It is used when you need to call a PL/SQL or Java subprogram
  • EXPLAIN PLAN – Used to interpret data access path
  • LOCK TABLE – Used to control concurrency

Q23. Explain different DCL commands in MySQL.

Ans. DCL commands are –

  • GRANT – It provides user access privileges to the database
  • DENY – Used to deny permissions to users
  • REVOKE – Used to withdraw user access by using the GRANT command

Q24. Explain different TCL commands in MySQL.

Ans. DCL commands include –

  • COMMIT – Used to commit a transaction
  • ROLLBACK – Used to roll back a transaction
  • SAVEPOINT – Used to roll back the transaction within groups
  • SET TRANSACTION – Used to specify transaction characteristics

Q25. What are the different types of Database relationships in MySQL?

Ans. There are three types of Database Relationship –

  • One-to-one – Both tables can have only one record
  • One-to-many – The single record in the first table can be related to one or more records in the second table
  • Many-to-many – Each record in both the tables can be related to any number of records

Q26. What is Normalization?

Ans. Normalization is a database design technique to organize tables to reduce data redundancy and data dependency.

Q27. What are the different types of Normalization?

Ans. There are six different types of Normalization –

  • First Normal Form (1NF)
  • Second Normal Form (2NF)
  • Third Normal Form (3NF)
  • Boyce-Codd Normal Form (BCNF)
  • Fourth Normal Form (4NF)
  • Fifth Normal Form (5NF)

Q28. What is Denormalization?

Ans. Denormalization is a database optimization technique for increasing a database infrastructure performance by adding redundant data to one or more tables. 

Check out the list of Top Online IT Courses.

Q29. Is MySQL query case-sensitive?

Ans. MySQL queries are not case-sensitive by default. The following queries are the same.

SELECT * FROM `table` WHERE `column` = ‘value’

SELECT * FROM `table` WHERE `column` = ‘VALUE’

SELECT * FROM `table` WHERE `column` = ‘VaLuE’

Q30. How many TRIGGERS are allowed in the MySQL table?

Ans. 6 triggers are allowed in the MySQL table:

  • BEFORE INSERT
  • AFTER INSERT
  • BEFORE UPDATE
  • AFTER UPDATE
  • BEFORE DELETE 
  • AFTER DELETE

Q31. What are the different column comparison operators in MySQL?

Ans. The =, <>, <=, <, >=, >, <<, >>, < = >, AND, OR or LIKE operator are the comparison operators in MySQL. 

Comparisons operators are generally used with SELECT statements. They are used to compare one expression to another value or expression.

Q32. What syntax can we use to get a version of MySQL?

Ans. By using the given query in your phpmyadmin-

SELECT version();

Q33. What is Auto Increment in SQL?

Ans. Auto Increment allows a unique number to be generated whenever a new record is created in a table. Generally, it is the PRIMARY KEY field that we want to be created automatically every time a new record is inserted.

Q34. SQL Server runs in which TCP/IP port? Can it be changed?

Ans. SQL Server runs on port 1433, and it can be changed from the Network Utility TCP/IP properties.

Q35. Name symmetric key encryption algorithms supported in the SQL server.

Ans. SQL Server supports several symmetric key encryption algorithms, such as DES, Triple DES, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.

Q36. What is Database Relationship?

Ans. A Database Relationship is defined as the connection between two relational database tables. The primary table has a foreign key that references the primary key of another table. There are three types of Database Relationship –

  • One-to-one 
  • One-to-many 
  • Many-to-many

Q37. What is faster between a table variable and a temporary table?

Ans. Between these, a table variable is faster mostly as it is stored in memory, whereas a temporary table is stored on disk. In case the size of the table variable exceeds memory size, then both the tables perform similarly.

Also Read>> Top Tableau Interview Questions and Answers

Q38. Mention the command used to get back the privileges offered by the GRANT command?

Ans. REVOKE command is used to get back the privileges offered by the GRANT command.

 

Advanced Level SQL Interview Questions

Q39. How to find:

duplicate records with one field?

duplicate records with more than one field?

Ans. Finding duplicate records with one field: 

SELECT COUNT(field)

FROM table_name

GROUP BY field

HAVING COUNT(field) > 1

Finding duplicate records with more than one field:

SELECT field1,field2,field3, COUNT(*)

FROM table_name

GROUP BY field1,field2,field3

HAVING COUNT(*) > 1

Also Read>> Top Web Developer Interview Questions and Answers

Q40. What is a constraint, and how many levels of constraints are there?

Ans. Constraints are the representation of a column to enforce data entity and consistency. There are two levels of constraint – 

  • Column level – Limits only column data
  • Table level – Limits whole table data

Following are the most used constraints that can be applied to a table:

  • NOT NULL
  • UNIQUE
  • CHECK
  • DEFAULT
  • PRIMARY KEY
  • FOREIGN KEY

Q41. What are the authentication modes in SQL Server?

Ans. SQL Server has two authentication modes –

  • Windows Mode – Default. This SQL Server security model is integrated with Windows
  • Mixed Mode – Supports authentication both by Windows and by SQL Server

We can change modes by selecting tools of SQL Server configuration properties and then hover to the security page.

Q42.  What is PL/SQL?

Ans. PL/SQL or Procedural Language for SQL was developed by Oracle. It is an extension of SQL and enables the programmer to write code in a procedural format. Both PL/SQL and SQL run within the same server process and have features like – robustness, security, and portability of the Oracle Database.

Q43. What is SQL Profiler?

Ans. SQL Server Profiler is a graphical user interface for creating and saving data about each event of a file. It also allows a system administrator to analyze and replay trace results when a problem is being diagnosed. SQL Server Profiler is used to:

  • Examine the problem queries to find the cause of the problem
  • Diagnose slow-running queries
  • Determine the Transact-SQL statements that lead to a problem
  • Monitor the performance of SQL Server 
  • Correlate performance counters to diagnose problems

Q44. What is the SQL Server Agent?

Ans. SQL Server Agent is a Microsoft Windows service that executes day-to-day tasks or jobs of SQL Server Database Administrator (DBA). This service enables the implementation of tasks on scheduled date and time.

Also Read>> Top Hadoop Interview Questions and Answers

Q45. What is Data Integrity?

Ans. Data integrity attributes to the accuracy, completeness, and consistency of the data in a database. It also refers to the safety and security of data and is maintained by a collection of processes, rules, and standards that were implemented during the design phase. Three types of data integrity are:

  • Column Integrity
  • Entity Integrity
  • Referential Integrity

Q46. What is the difference between Rename and Alias?

Ans. Rename is actually changing the name of an object. Alias is giving another name (additional name) to an existing object. Rename involves changing the name of a database object and giving it a permanent name whereas Alias is a temporary name given to a database object.

Syntax of a table Alias:

SELECT column1, column2….

FROM table_name AS alias_name

WHERE [condition];

Syntax of a table Rename:

RENAME TABLE {tbl_name} TO {new_tbl_name};

Q47.  Which are the main steps in Data Modeling?

Ans. Following are the main steps in Data Modeling:

  • Identify and analyze business requirement
  • Create a quality conceptual and logical data model
  • Select the target database to create scripts for physical schema using a data modeling tool

Q48. What is Referential Integrity?

Ans. Referential integrity is a relational database concept that suggests that accuracy and consistency of data should be maintained between primary and foreign keys.

Q49. What is Business Intelligence?

Ans. Business intelligence (BI) includes technologies and practices for collecting, integrating, analyzing, and presenting business information. It combines business analytics, data mining, data visualization, data tools and infrastructure, and best practices.

Q50. Mention the types of privileges available in SQL?

Ans. Following are the types of privileges used in SQL:

System Privilege: It deals with an object of a specific type and indicates actions on it which include admin that helps users to perform administrative tasks, alter any cache group, and alter any index.

Object Privilege: It helps users to perform actions on an object using commands like table, view, indexes. There are other object privileges used in SQL are EXECUTE, INSERT, SELECT, FLUSH, LOAD, INDEX, UPDATE, DELETE, REFERENCES, etc.

Q51. What is the difference between a clustered and non-clustered index?

Ans. Clustered Index – A clustered index is used to order the rows in a table. It has leaf nodes consisting of data pages. A table can possess only one clustered index.

Non-clustered Index – A non-clustered index stores the data and indices at different places. It also has leaf nodes that contain index rows. A table can possess numerous non-clustered indexes.

Q52. What is ERD?

Ans. ERD or Entity Relationship Diagram is a visual representation of the database structures and shows a relationship between the tables. The ER Diagrams have three basic elements:

  • Entities – An entity is a person, place, thing, or event for which data is collected.
  • Attributes – It refers to the data we want to collect for an entity. It is a property, trait, or characteristic of an entity, relationship, or another attribute.
  • Relationships – It describes how entities interact.

Q53. How will you find the unique values, if a value in the column is repeatable?

Ans. To find the unique values when the value in the column is repeatable, we can use DISTINCT in the query, such as:

SELECT DISTINCT user_firstname FROM users;

We can also ask for several distinct values by using: 

SELECT COUNT (DISTINCT user_firstname) FROM users;

Q54. Explain database white box testing.

Ans. White Box Testing is concerned with the internal structure of the database. The users are unaware of the specification details.

  • Database white box testing includes testing of database triggers and logical views that support database refactoring.
  • Validates database tables, data models, database schema
  • Performs module testing of database functions and SQL queries 
  • Selects default table values to check on database consistency
  • Adheres to referential integrity rules

Q55. Exhibit the students who are having the same batch ID and study in the same department as student ids, 1002 and 1004.

Ans. 

select x.student_id ,

x.department_id

from students x

where (department_id, batch_id)

in (Select department_id , batch_id

from students

where student_id in (1002,1004))

and x.student_id not in (1002, 1004)

Q56. What is the ACID property in SQL?

Ans. ACID is short for Atomicity, Consistency, Isolation, Durability. It ensures Data Integrity during a transaction. 

Atomicity: It means either all the operations (insert, update, delete) inside a transaction take place or none.  So, if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.

Consistency: Consistency ensures that the data must meet all the validation rules. Irrespective of whatever happens in the middle of the transaction, Consistency property will never leave your database in a half-completed state.

Isolation: It means that every transaction is individual. One transaction can’t access the result of other transactions until the transaction is completed. 

Durability: It implies that maintaining updates of committed transactions is important. These updates must never be lost. It refers to the ability of the system to recover committed transaction updates if either the system or the storage media fails.

Also Read>> Top MongoDB Interview Questions and Answers

Q57. Explain string functions in SQL?

Ans. SQL string functions are used for string manipulation.

Following are the extensively used SQL string functions:

  • UPPER(): Converts character data to upper case
  • LOWER(): Converts character data to lower case
  • SUBSTRING() : Extracts characters from a text field
  • RTRIM(): Removes all whitespace at the end of the string
  • LEN(): Returns the length of the value in a text field
  • REPLACE(): Updates the content of a string.
  • LTRIM(): Removes all whitespace from the beginning of the string
  • CONCAT(): Concatenates function combines multiple character strings

Q58. What are the differences between the Primary key and Unique key?

Ans. Differences between Primary key and Unique key are:

Primary Key Unique Key
Enforces column uniqueness in a table Determines a row that isn’t a primary key
Does not allow NULL values Accepts one NULL value
Has only one primary key Has more than one unique key
Creates clustered index Creates non-clustered index
Primary Key on CREATE TABLE Syntax: 

CREATE TABLE Students (

    ID int NOT NULL PRIMARY KEY,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Age int

);

Unique Key on CREATE TABLE Syntax: 

CREATE TABLE Students (

    ID int NOT NULL UNIQUE,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Age int

);

Q59. Write the SQL query to convert the string to UPPERCASE and LOWERCASE.

Ans. The SQL query used to convert the string to UPPERCASE and LOWERCASE is:

STRING UPPER(“naukrilearning”); => NAUKRILEARNING

STRING LOWER(“LEARNERS”); => learners

Q60. What is the procedure to hide a specific table name of the schema?

Ans. By using SYNONYMS, we can hide a specific table name of the schema.

Syntax:

CREATE SYNONYM STU for STUDENTS;

After creating the above synonym, we can access the data of STUDENTS table using STU as table name as below

SELECT * from STU;

Q61. What is the syntax to eliminate duplicate rows?

Ans. By using the DISTINCT keyword, we can eliminate duplicate records.

Syntax:

SELECT DISTINCT CLASS_ID

FROM STUDENTS;

Q62. Find out nth highest salary from emp table?

Ans. Syntax:

select salary from

(select salary, rownum EP from

(select salary from employees

order by salary desc))

where EP=n;

Q63. Name the encryption mechanisms in the SQL server.

Ans. This is one of the most popular SQL interview questions. The encryption mechanism used in SQL server are –

  • Transact-SQL functions – Individual items can be encrypted as they are inserted or updated using Transact-SQL functions.
  • Asymmetric keys – It is made up of a private key and the corresponding public key. Each key can decrypt data encrypted by the other.
  • Symmetric keys – It is used for both encryption and decryption.
  • Certificates – Also known as a public key certificate, it binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key.
  • Transparent Data Encryption – It is a special case of encryption using a symmetric key that encrypts an entire database using that symmetric key.

Q64. What is the procedure to pass variables in a SQL routine?

Ans. Variables can be passed to a SQL routine by using:

  • “&” symbol
  • SQLPLUS command

Also Read>> Top Database Interview Questions and Answers

Q65. Can a view be updated/inserted/deleted? If yes, at what conditions?

Ans. It is not possible to add the data through a view if the view contains the following:

  • Group by clause
  • Group functions
  • DISTINCT keyword
  • Columns defined by expressions
  • Pseudo column ROWNUM keyword
  • NOT NULL column in the base table that is not selected by the view.

Q66. How can you create an SQL table from another table without copying any values from the old table?

Ans. Syntax: 

CREATE TABLE new_table

  AS (SELECT *

      FROM old_table WHERE 1=2);

This will create a new table with the same structure as the old table with no rows copied.

Q67. Explain what is an inline view?

Ans. An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In-line views are used to reduce complex queries by removing join operations and summarizing multiple separate queries into a single query.

Syntax:

SELECT SALARY FROM

(SELECT SALARY, ROWNUN EP FROM

(SELECT SALARY FROM EMPLOYEES ORDER BY SALARY DESC) )

WHERE EP=7

Q68. What command is used to create a table by copying the structure of another table?

Ans. Syntax:

CREATE TABLE STU AS

SELECT * FROM STUDENTS

WHERE 1=2

Invalid Condition

We have to give the invalid condition in the where clause, where the whole data will copy to the new table (STU table). 

Q69. Mention the use of the DROP option in the ALTER TABLE command.

Ans. The use of the DROP option in the ALTER TABLE command is to drop a particular COLUMN.

Syntax:

ALTER TABLE TABLE_NAME

DROP COLUMN COLUMN_NAME

Q70. What are the aggregate functions in SQL?

Ans. SQL aggregate functions allow us to return a single value, which is calculated from values in a column.

Following are the aggregate functions in SQL:

  • AVG() : This function returns the average value
  • COUNT(): This function returns the number of rows
  • MAX() : It returns the largest value
  • MIN() : This function returns the smallest value
  • ROUND(): This function rounds a numeric field to the number of decimals specified
  • SUM() : It returns the sum

Q71. Write the SQL query to update the student names by removing leading and trailing spaces.

Ans. This can be done by using ‘Update’ command with ‘LTRIM’ and ‘RTRIM’ function.

Syntax:

UPDATE StudentDetails 

SET FullName = LTRIM(RTRIM(FullName));

Q72. Write the SQL query to fetch alternate records from a table

Ans. Records can be fetched for odd and even row numbers:

  • Syntax to fetch even numbers:

Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=0

  • Syntax to fetch odd numbers:

Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=1

Q73. How do you return a hundred books starting from the 15th?

Ans. The syntax will be: 

SELECT book_title FROM books LIMIT 15, 100. 

The first number in LIMIT is the offset, and the second is the number.

Q74. What will the query select all teams that lost either 1, 3, 5, or 7 games?

Ans. We will use-

SELECT team_name FROM teams WHERE team_lost IN (1, 3, 5, 7)

Q75. How will you delete a column?

Ans. We can delete a column by –

ALTER TABLE techpreparation_answers DROP answer_user_id.

Q76. What is the meaning of this query – Select User_name, User_isp From Users Left Join Isps Using (user_id)?

Ans. It means:

SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id

Q77. How will you see all indexes defined for a table?

Ans. By using:

SHOW INDEX FROM techpreparation_questions;

Q78. How would you change a table to InnoDB?

Ans. By using:

ALTER TABLE techpreparation_questions ENGINE InnoDB;

Q79. Name the default port for the MySQL server.

Ans. The default port for the MySQL server is 3306.

Q80. What is the possible way to add five minutes to a date?

Ans. By using:

 ADDDATE(techpreparation_publication_date, INTERVAL 5 MINUTE)

Q81. What is the possible way to convert between Unix timestamps and Mysql timestamps?

Ans. Example:

UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp

FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp

Q82. How do you implement Enums and sets internally in MySQL? 

Ans. To implement an ENUM column, use the given syntax: 

CREATE TABLE table_name ( … col ENUM (‘value1′,’value2′,’value3’), … );

Q83. How can we restart SQL Server in the single user or the minimal configuration modes?

Ans. The command line SQLSERVER.EXE used with ‘–m’ will restart SQL Server in the single-user mode. 

The command line SQLSERVER.EXE used with ‘–f’ will restart it in the minimal configuration mode.

Q84. What is the use of the tee command in Mysql?

Ans. Tee is a UNIX command that takes the standard output of a Unix command and writes it to both the terminal and a file. Tee followed by a filename turns on MySQL logging to a specified file. It can be paused by a command note.

Q85. Is it possible to save your connection settings to a conf file?

Ans. Yes, it is possible, and you can name it ~/.my.conf. You can also change the permissions on the file to 600 so that it’s not readable by others.

Q86. How to convert numeric values to character strings?

Ans. We 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

Q87. Use mysqldump to create a copy of the database?

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

Q88. What are federated tables?

Ans. Federated tables allow access to the tables situated on other databases on other servers in MySQL. It lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table pulls the data from the remote (federated) tables. Data is not stored on the local tables.

Q89. What are the different groups of data types in MySQL?

Ans. There are three groups of data types in MySQL, as listed below:

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

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

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

Q90. What is the procedure to concatenate two character strings?

Ans. To concatenate various character strings into one, you can use the CONCAT() function. Example:

SELECT CONCAT(’Naukri’,’ Learning’) FROM DUAL;

Naukri Learning

SELECT CONCAT(‘Learner’,’Thing’) FROM DUAL;

Learner Thing 

Q91. What is the procedure to change the database engine in Mysql? 

Ans. By using: 

ALTER TABLE EnterTableName ENGINE = EnterEngineName;

Q92. What is the default storage engine in MySQL?

Ans. InnoDB is the default storage engine in MySQL.

Q93. What is COALESCE?

Ans. COALESCE returns the first non-NULL expression within its arguments from more than one column in the arguments. 

The syntax for COALESCE is –

COALESCE (expression 1, expression 2, … expression n)

Q94. What syntax is used to create an index in MySQL?

Ans. By using-

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

Q95. What are the different storage engines/table types present in MySQL?

Ans. MySQL supports two types of tables: transaction-safe tables (InnoDB and BDB) and non-transaction-safe tables (HEAP, ISAM, MERGE, and MyISAM). 

  • MyISAM: This is a default table type that is based on the Indexed Sequential Access Method (ISAM). It extends the former ISAM storage engine. These tables are optimized for compression and speed.
  • HEAP: It allows fast data access. However, the data will be lost if there is a crash. HEAP table cannot have BLOB, TEXT, and AUTO_INCREMENT fields.
  • BDB: It supports transactions using COMMIT and ROLLBACK. It is slower than the others.
  • InnoDB: These tables fully support ACID-compliant and transactions.
  • MERGE: Also known as the MRG_MyISAM engine, MERGE is a virtual table that combines multiple MyISAM tables that have a similar structure to one table. 

Q96. What are the differences between and MyISAM and InnoDB?

Ans. The following are the differences between and MyISAM and InnoDB

MyISAM  InnoDB
No longer supports transactions Supports transactions
It supports Table-level Locking It helps in Row-level Locking
No longer assist ACID (Atomicity, Consistency, Isolation, and Durability) Supports ACID property
Supports FULLTEXT index Does not support FULLTEXT index

Q97. What drivers are available in MySQL?

Ans. Below are the drivers available in MySQL:

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

Q98. What is a Join? What are the different types of joins in MySQL?

Ans. Join is a query that retrieves related columns or rows. There are four types of joins in MySQL: 

  • Inner Join – it returns the rows if there is at least one match in two tables. 
  • Left Join – returns all the rows from the left table even if there is no match in the right table. 
  • Right Join – returns all the rows from the right table even if no matches exist in the left table. 
  • Full Join – would return rows when there is at least one match in the tables.

We hope that these SQL interview questions will boost your interview preparation.

————————————————————————————————————–

If you have recently completed a professional course/certification, click here to submit a review and get FREE certification highlighter worth Rs. 500.

5.00 avg. rating (96% score) - 1 vote