Modernization of the way we used to maintain records has changed drastically, a lot of data being generated nowadays are in the form of pictures or videos that have become our sole source of keeping memories intact for many years to come. Similarly, multiple organizations and firms have employed a similar method in storing the company data in the form of digitized documents stored away in Database Management Systems (DBMS) which require a special style of language to mine or extract data that the company wishes to extract from its database and here where SQL comes in.

Structured Query Language (SQL) is a domain-specific programming language that is utilized by skilled professionals to manage data stored in the company’s database. SQL skills are in high demand in the market and serve as the foundational basics for any professional looking for a job or brighter prospects in the data industry. Here are a set of SQL Interview Questions that we believe you should prepare for SQL before going for an interview.

Q1. Describe a DBMS?

Database Management System (DBMS) is software that is solely responsible for creating, controlling, maintenance, and use of a database. DBMS may be defined as a folder that manages data in a database rather than saving a file in the system.

Q2. Define an RDBMS?

RDBMS abbreviates for Relational Database Management System. It is categorized with the storage of data into a compilation of tables, which is linked by similar topics between the columns of a table. It aids the user with relational operators to influence the data stored in the tables.

Q3. Describe SQL?

SQL abbreviates to Structured Queried Language and aids the operator to communicate with the database. It is a standard operating language that helps execute responsibilities such as recovery, updating, incorporating, and expunging data from the database.

Q4. Define a Database?

It is a structured table of data made to easily access, store, retrieve, and manage data.

Q5. Describe MySQL?

It is a multi-threaded, multiuser structured query language database management system with more than 11 million installations across the globe. The language is the second most well-known and popularly used open-source database programming in use.

MySQL is an oracle sponsored relational database management system (RDBMS) built on structured query language. It is supported by several operating systems which include Windows, LINUX, iOS, etc.

Q6. My SQL has been written in which language?

C & C++ are the languages in which MySQL has been written

Q7. Mention the technical specifications of MySQL?

Below are the technical specifications of MySQL:-

• Drivers
• Flexible structure
• Geospatial support
• Graphical tools
• JSON support
• OLTP and transactions
• High performance
• Manageable and easy to use
• MySQL Enterprise Monitor
• MySQL Enterprise Security
• Replication and high availability
• Security & storage management

Q8. Describe the difference between SQL and MySQL?

• SQL stands for structured query language and is used to interact with databases like MySQL
• MySQL is a database management system used for structured storage of data
• A PHP script is needed to store and mine data from this database
• SQL is a computer language whereas MySQL is an application
• SQL is used for the creation of various Database Management System (DBMS)

Q9. Distinguish between the database and a table?

There are 4 noticeable difference between a database & a table:-

1. Tables showcase structured data in a database, whereas a database is a collection of tables
2. Tables are grouped with relations to create a dataset; the dataset forms the database.
3. Data stored in the table in any form is part of the database, but the other way around is not possible.
4. A table is a collection of rows and columns used to store data, whereas a database is a collection of organized data and features used to access tables.

Q10. Distinguish between Tables and fields?

A table is a compilation of cells that are structured in a model which eventually form tables and rows. Columns may be categorized as a vertical collection of cells and rows may be categorized as a horizontal collection of cells.

There is also a reference to the cells laid out in a column to create an entity also termed as field once a header is provided to the so-called column.

A field may have several rows which may constitute a record.

E.g.

Table name:- Employee

Field name’s:- Emp ID, Emp Name, Date of Birth

Data:- 2866, Daniel Decker, 29/02/1984

Q11. What is the purpose of using a MySQL database server?

Below are some of the reasons as to why MySQL server is so famous with its users:-

• MySQL is an open-source database management system that is free of charge for private developers and small enterprises.
• MySQL’s community is vast and supportive, thus any issues faced are resolved at the earliest.
• Has multiple stable versions available
• It extremely quick, dependable and is beginner-friendly
• The download is free of cost

Q12. Describe the various tables present in MySQL?

There are majorly 5 tables present in MySQL: –

1. MyISAM
2. Heap
3. Merge
4. INNO DB
5. ISAM

Q13. How can an Operator install MySQL?

There multiple ways of installing MySQL in one’s system, but the best way to do it is manual. The manual installation allows the user to gain a better understanding of the system and aids in additional grasp over the database. There are several benefits linked to the manual installation of MySQL:-

1. Reinstalling, creating backups, or moving databases can be achieved in less than a minute.
2. Provides precise control over how and when MySQL closes or starts.

• MySQL can even be installed in a USB drive

Q14. How can a user check the MySQL version?

In WINDOWS MySQL command-line tool shows the version information without using any flags, but for a piece of more detailed information the operator may always feed in the below-mentioned command

MySQL> SHOW VARIABLES LIKE “%version%”.

and it will show a detailed discretion of the version of SQL that the user is using.

Q15. How to add columns in MySQL?

Several cells in a table are what may constitute a column and a set of cells in a column constitutes a row. To add columns in MySQL, the following statement of ALTER TABLE may be used:

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

Q16. In MySQL how can you delete a table?

The drop table statement not only removes the data in the table, but it also removes the structure and definition from the database permanently. Thus, the user needs to be extremely careful whilst using this command, the reason is if once deleted there is no recovery option in MySQL. The command is as follows:-

DROP TABLE table_name

Q17. Define a Primary Key?

Primary Key may be described as a compilation of fields that meticulously define a row. This is a Unique Key and has an unspoken NOT NULL constraint, implicating Primary keys cannot have NULL values.

Q18. Define a Unique Key?

This key provides a separately pre-defined constraint that exclusively distinguishes every record in the database which insinuates a distinctiveness for the column or the set of columns.

Q19. Define a Foreign Key?

This is a key that can be linked to the Primary Key of another table. Connections need to be fabricated between the two tables by providing a reference to the foreign key with the primary key of another table.

Q20. Describe a Join?

It is a keyword utilized to question data from multiple tables established on the connections between the fields of the table. Keys play a crucial part when JOIN’s are employed.

Q21. Describe the multiple ‘JOIN’ and explain each?

JOIN’s are tools that help the user retrieve data and depend on the links between tables. Following are the types of ‘JOIN’ used in SQL:-

• Inner JOIN – This JOIN is used to return rows with at least a single match of rows between tables
• Right, JOIN – This JOIN helps return rows that are similar between tables and all rows of the right-hand side table. To simplify this JOIN returns all rows from the right-hand side table irrespective of any matches from the left-hand side table.
• Left JOIN – This JOIN helps return rows that are similar between tables and all rows of the left-hand side table. To simplify this JOIN returns all rows from the left-hand side table irrespective of any matches from the right-hand side table.
• Full JOIN – This JOIN return rows if there are any similar rows in any of the tables. So basically, this JOIN returns all the rows from both the right-hand and left-hand side tables.

Q22. Define Normalization?

It is the procedure of reducing redundancies and dependencies by structuring fields and tables of a database. The primary motive of ‘Normalization’ is to add, modify, and delete that can be merged into a single table.

Q23. Define De-Normalization?

It is a method employed to gain access to data from higher to lower normal types of database. It is also a way of implementing redundancy into a table by integrating data from the correlated tables.

Q24. Distinguish the multiple normalizations?

Normalizations may be dissected into 5 forms:-

1. First Normal Form (1NF) :- It helps in eliminating all identical columns from a table which aids the formation of tables for the associated data and recognition of distinctive columns.
2. Second Normal Form (2NF):– Meeting all the obligations of the 1NF. Retaining the subsets of data in separate tables and the creation of relations between tables using primary keys.
3. Third Normal Form (3NF):– This shall meet all the constraints of 2NF and eradicating the columns which are not reliant on the primary key limits.
4. Fourth Normal Form (4NF):– Meeting all the obligations of the 3NF and it shall not have multi-valued dependencies.

Q25. Define a View?

The view is a computer-generated table that comprises a subsection of data enclosed in a table. Views are NOT virtually present and require a lesser amount of storage capacity. The view can have data of one or more tables pooled in one and depends on the connection.

Q26. Define an Index?

An index is a routine tweaking method to permit faster reclamation of records from a table. An Index designs an entry for every value which makes data recovery quicker.

Q27. Describe the distinct kinds of Indexes?

There are primarily 3 types of Index’s:-

1. Unique Index – This style of Indexing does not grant the field to have identical values if the column is uniquely indexed. This Index can be used spontaneously once the primary key is defined.
2. Clustered Index – This style of index rearranges the raw order of the table and searches based on key values. Each table may consist of only one Clustered Index.

• Non-Clustered Index – Non-Clustered Index does not modify the raw order of the table and retains the plausible order of data. Each table may consist of 999 non-clustered indexes.

Q28. Define a Cursor?

A database cursor is a command which facilitates a cross-over of the rows/records in a table. This may be visible as a hint to one row in a collection of rows. It is extremely useful for traversing the retrieval, addition, and removal of database archives.

Q29. Define a Database relationship and what are they in SQL?

It is defined as the link between the tables in a database. There are several database-based relationships, and they are as follows:-

• One to One relationship
• One to Many relationships
• Many to One relationship
• Self-referencing relationship

Q30. Describe a Query?

A database query is a code created to recover information from the database. The query may be fabricated in a way to match the user’s expectation of the result set which may simply be a question to the database.

Q31. Describe a Subquery?

As the word describes, it is a query inside a query. The exterior query is known as the – Main Query and the innermost query is called a Subquery. Subqueries are forever implemented first and the outcome from the Subquery is then passed on to the main query.

Q32. Describe the types of Subqueries?

There are majorly 2 styles of subqueries:-

• Correlated Subquery – These cannot be considered as independent queries but may refer to the column in a table listed in the FROM the list of the main query.
• Non-Correlated Subquery – These may be considered as independent queries and the output of these subqueries may be substituted in the main query.

Q33. Define a Stored Procedure?

This procedure is a function that comprises several SQL statements to access the DBMS. Multiple SQL statements are compiled into a ‘Stored Procedure’ and maybe employed anywhere as per requirement basis.

Q34. Define a ‘Trigger’?

The trigger is a code that automatically executes with some event on a table or with a view in a database. E.g., On the joining of a new hire, new records need to be entered in fields like employee ID, Name, Date of birth, etc.

Q35. Distinguish between DELETE and TRUNCATE commands?

DELETE command is utilized to delete rows from the table and a WHERE clause may be applied for a provisional set of considerations. Commit and Rollback may be executed post deletion of the statement.

TRUNCATE deletes every row from the table. Truncate control cannot be turned backward.

Q36. Define Local and global variables and describe their disparities?

Local variables are the variables that can be applied or occur within the function. They are unknown to the other functions and cannot be referred to or utilized. Variables can be established whenever the functions are called.

Global variables are the variables that can be employed or be present all over the program. Identical variable proclaimed in a global variable cannot be utilized in functions. Global variables cannot be established whenever a particular function is called.

Q37. Define a CONSTRAINT?

A constraint may be employed to restrict the data type of a table. It may also be specified at the time of creating or altering the table. Some examples of constraints are:-

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

Q38. Define Data Integrity?

It defines the precision and consistency of the data stored in a database. It may also identify integrity constraints to implement business guidelines on the data when it is registered into the application or database.

Q39. Describe auto Increment?

This enables the operator to fabricate a new number to be generated when a record is inserted into the table. AUTOINCREMENT keyword may be used in Oracle and IDENTITY keyword may be used in an SQL SERVER.

This keyword is used when the primary key is used.

Q40.Distinguish between Cluster and Non-Cluster Index?

Cluster Index is employed for the convenient recovery of data from the database by adjusting the way the records are stored. A database sorts out rows by columns which are destined to be Clustered Index.

Non-Clustered Index does not adjust the way data was stored in the database, rather fabricates a completely different entity inside the table. It usually points back to the original table rows after investigating.

Q41. Describe a Datawarehouse?

It is a fundamental storehouse of data from numerous data sources. Data are collected, transformed, and made available for mining and online processing. Warehouse data have a subcategory of data dubbed as Data Mart.

Q42. Define a Self-Join?

It is a query employed to evaluate itself. It is utilized to assess values in a column with other values in the same column and table.

Q43. Define a Cross-Join?

Cross join describes as a query to calculate the results of the number of rows in the first table multiplied by several rows in the second table. If a WHERE clause is applied in a cross join, then the query will act like an INNER JOIN.

Q44. Describe User Defined functions and their types?

User-defined functions are fabricated to create logic whenever required. It is not required to write the same logic multiple times. Rather, the function may be called or deployed at any given point in time.

There are 3 styles of User-defined functions:-

1. Scalar Functions – returns unit, return clause defined by variant
2. Inline table-valued functions, return table as a return
3. Multi statement valued functions returns table as a return

Q45. Describe Collation?

It describes as a compilation of guidelines that establish how character data may be categorized and compared. It can be used to assess A and other language characters, also depending on the width of the characters.

all values may be used to compare these character data.

Q46. Describe the distinct styles of Collation sensitivity?

These are the different types of collation sensitivities:-

• Case Sensitivity – A & a, and B & b.
• Accent Sensitivity
• Kana Sensitivity – Japanese Kana characters.
• Width Sensitivity – Single byte & double-byte character.

Q47. Define recursive Stored Procedure?

A stored procedure that demands by itself until it achieves some kind of boundary condition. This recursive function or procedure helps computer operators use the identical set of codes ‘n’ number of times.

Q48. How can a user add foreign keys in MySQL?

This is the key to linking one or more tables together in MySQL. It helps in matching the primary key field of another table to connect the two tables. It allows the user to a parent-child relationship within the tables. This can be executed either way:-

1. Using the CREATE TABLE command
2. Using the ALTER TABLE command

Following is the syntax used to define a foreign key using CREATE or ALTER TABLE

FOREIGN KEY [foreign_key_name] (col_name, …)

            REFERENCES parent_tbl_name (col_name, …)

Q49. Describe how can the user create a database in MySQL workbench?

To do so the initial step would be to:-

• launch the MySQL workbench and log in using a username and password
• Choose the Schema menu from the navigation tab
• Right-click under the schema menu and pick the ‘Create Schema’ option.

Or

• Click on the database icon ( similar to a barrel)
• A new dialog box would appear
• After filling all the details in the dialog box
• Click on Apply and Finish to complete the database creation.

Q50. How can the user create a Table in MySQL workbench?

• Launch the MySQL workbench
• Go to the navigation tab
• Choose the ‘Schema Menu’ which will showcase all the previously created databases
• Select any database and right-click on it
• In the sub-menus, we need to select the tables option
• Right-click on the tables sub-menu
• Choose ‘Create table Option’