70+ Best SQL MySQL Interview Questions To Hire Or Get Hired With - Algrim.co

70+ Best SQL MySQL Interview Questions To Hire Or Get Hired With - Algrim.co SQL is the computer programming language often used in the design of retrieving and writing data to a database. Often it used in MySQL, SQLLite, Postgres or any of the other table-oriented databases. As a computer programmer looking to get employed, you will be required to know a firm understanding of SQL. It is the backbone to any backend engineering or programming. Without this skill set it will be difficult to create any type of product, as most information on the web or even through iOS sends API hooks that eventually write information to a database. In this regard, this is in the top three for required skills in order to achieve getting employment in a software development organization. Below are 70 of the best SQL interview questions that should help you to either employ someone or be employed by an organization. You can use the below questions to study and help you prepare for interviews. Or you can use the below questions in your interview process and help move along yo ur candidates more efficiently in the process. MySQL is one of the most popular databases in the past 30 years. It has been a cornerstone to most innovations that exist on the web. Only up until recently, in the past 7 years, have other database types been more widely adopted. And even then, there's some debate as to whether MySQL is still more effective in terms of scalability and reliability. 1. What is SQL? The term SQL is short for Structured Query Language. SQL refers to a standard language used to carry out among other tasks; insertion, updating, retrieval and deletion of data in the data. Ideally, it can be said that SQL’s main role is to communicate with the database. 2. What does DBMS refer to in SQL? DBMS which is short for Data Management System is a program creates and maintains the use of a database. In other words, DBMS can also be termed as a File Manager that is involved in data management in a database as opposed to saving data the data in file systems. 3. In SQL, what is a database? This is a collection of organized data for easy access, storage, and retrieval. It can also be referred to as structured data format that can be accessed in different ways. Examples of a database include: Bank Management Database and School Management Database. 4. Name the different types of SQL commands SQL commands are categorized in the following types; • DDL â€" Data Definition Language • DML â€" Data Manipulation Language • DQL â€" Data Query Language • DCL â€" Data Control Language • TCL â€" Transaction Control Language 5. In SQL, explain the role of DDL commands and in what different commands are there. In SQL, DDL commands define or alter the structure of a database. There are several different DDL commands namely; • CREATE-creates databases and database objects • ALTER-alters existing database and objects • DROP-drops databases and databases objects • TRUNCATE-removes all records from the table but does not change the structure of the database • RENAME-renames database objects 6. In SQL, explain the role of DML commands and name the commands. DML commands are used to manage data present in the database. DML commands in SQL are; • SELECT-selects specific data from the database • INSERT-inserts a new record into the table • UPDATE-updates the existing records • DELETE-deletes existing records from a table 7. In SQL, Which are the DCL commands and what is their role? The role of DCL commands is to create roles as well as grant permission to the database objects. There are three DCL commands in SQL namely; • GRANT-provides user access • DENY-denies user access • REVOKE-removes user access 8. In SQL, which are the TCL commands and what is their role? The role of TCL commands is to manage changes made by DML statements. There are two main TCL commands namely in SQL namely; • COMMIT-writes and stores changes in the database • ROLLBACK-restores the databases since the last commit 9. What is the function of the Index in SQL? The index speeds up the performance of queries by making faster the process of data retrieval from the table. The index can either be created on a single column or a group of columns. 10. What are the major differences between DELETE and TRUNCATE DELETE Used to delete rows in a table After deleting a statement, you can rollback data It is a DML command It is slower compared to the truncate statement TRUNCATE Used to delete all the rows in a column It is not possible to rollback data It is a DDL command It is faster than the DELETE command 11. In SQL, what is meant by table and field? A table refers to a collection of data that is organized in form of rows and columns whereas a filed refers to the specific number of rows and columns in a table. A classic example of how information appears in a table and field is below. Table: Student Information • Field: Stu Id, Stu Name, Stu Marks 12. In SQL, what does the term joins refer to Join clauses are used to combine rows from several tables. There are 4 joins in SQL namely; • Inner Join • Right Join • Left Join • Full Join 13. In SQL, what is the main difference between CHAR and VARCHAR2 data type? Whereas both CHAR and VARCHAR2 are used in the storage of character strings, Char is for strings of fixed lengths while Varchar2 is used for strings with variable lengths. A practical example of how the two work is that char (10) is only able to store 10 characters and cannot take any more or less of that number. On the other hand, varchar2 (10) can store any length that does not exceed 10 that is an even number such as 2, 4, 6 and 8. 14. In SQL, what is a Primary key? This is a single column or a collection of columns that uniquely identifies individual rows in a table. However, null values are not allowed during the identification process. 15. In SQL, what are constraints? Constraints specify the limit or data type of the table. This specification takes place during the creation or alteration of the table statement. The common constraints are; • NOT NULL • CHECK • DEFAULT • UNIQUE • PRIMARY KEY • FOREIGN KEY 16. What is the main difference between SQL and MySQL? SQL is a standard language that stands for Structured Query Language. MySQL, on the other hand, is a data management system whereas SQL operates as the core relational database used to access and manages the database, MySQL is a Relational Database Management System (RDMS) that include SQL, Informix, and Server. 17. What is meant by data integrity in SQL? Data consistency refers to the accuracy and consistency of data stored in a database. The term also refers to integrity constraints to enforce the rules of the business when data is entered into a database or application. 18. Name the three types of indices in SQL • Unique index-this index helps in the maintenance of data integrity by making sure that key values are not replicated in more than a single row. This means that no two rows should have identical key values. The unique index comes into application immediately after the primary key has been identified. The index ensures that the values of each index key column are unique. • Clustered Index-This index reorders the physical order of a table and ensures the search is based on the key values. There can only be one clustered index for each table at any given time. • Non-Clustered Index-this type of index does not maintain a logical order of the data by not altering the physical order of the table. There can be several non-clustered indices in a single table. 19. What is meant by De-normalization in SQL? This is a technique used to access data from a lower form to a higher form in a database. One of the benefits of Denormalization is that the process helps database managers increase the performance of the entire database infrastructure by introducing redundancy into a table. The redundant data is added to a table through the incorporation of data queries that combine data from several tables into a single table. 20. What are Entities and Relationships in SQL? Entities-In the real world, an entity refers to a person, place or something but in SQL; an entity refers to that where data can be stored in a database. An example of an entity in SQL is a table. For examples, bank databases have tables that contain customer information. This information is stored in form of a set of attributes-further divided into rows within the table, for each customer. Relationships-Relationships are links between entities that are interrelated. For example, in the case of a bank database, the name of the customer is linked to his/her account number and contact information. This information will always appear in the same table. However, there can also be a relationship between data in separate tables. For example, the customer’s name can be stored in one table while the account number appears in a different table. 22. What are the advantages of Normalization? • More efficient data access • Better organization of the database • Greater flexibility easier security implementation • Reduced redundancy and data duplication • Improved data consistency after modification • Increased ease of modification • Reduced data retrieval time 23. Name the three operators available in SQL There are three main operators in SQL namely; • Arithmetic Operators • Logical Operators • Comparison Operators 24. Are NULL values similar to those of zero or blank space? NULL values are not the same as zero or blank values. This is because in SQL, the term NULL refers to a value that is unavailable, assigned, unknown or not applicable. However, zero is an arithmetic number while blank space in a digital character. 25. In SQL, what is a sub-query? A subquery refers to a query that is inside another query where the outer query is referred to as the main query. During the process of data retrieval, subqueries are first to be executed and the results passed on to the main query. Subqueries are nested in SELECT, UPDATE and other queries in SQL. A subquery also uses comparison operators such as >, 26. How many types of sub-queries are available in SQL and what is their role? • Sub-queries are found in two different types, Correlated and Non-Correlated sub-queries. • Correlated sub-query- These queries select data referenced from a table in the outer query. The correlated sub-query is considered to be a dependent query as it has to refer to a different table. • Non-Correlated sub-query-this query is independent based on the fact that the output in the sub-query is substituted in the main query. 27. In SQL, name the different transaction controls. A transaction is a sequence task that is performed in a database in a logical manner in order to come up with the desired results. Some common tasks performed by transactions are, creating, updating and deleting records. In simple terms, a transaction is a group of SQL queries executed on database records. 28. There are four main transaction controls namely; • COMMIT-used for saving all the changes made through the transaction • ROLLBACK-used in rolling back the transaction and specifically by reverting back any changes made by the transaction hence the database remains as it was before. • SET TRANSACTIONS-sets the name of the transaction • SAVEPOINT-sets the point from where the transaction needs to be set back. 29. What are the properties of the transaction? A transaction has four different properties namely; • Atomicity-ensures that all transactions are completed and if the process is not successfully completed, it is aborted at the point of failure and the previous transaction rolled back to its original state. • Isolation-Ensures that all transactions take place independently and that any changes effected by one transaction are not reflected in a different one. • Durability-Ensures that any changes made after a transaction persists even in the event of a systems failure. 30. How many Aggregate Functions does SQL have? In SQL, there are 7 Aggregate Functions namely: • AVG (): Returns the average value from columns • COUNT () - Returns number of rows in a table • MAX () - Returns largest value among records • MIN () - Returns smallest value in records • SUM ()-Returns the sum of values in a column • FIRST () - Returns the first value • LAST () - Returns Last value 31. What are the Scalar Functions and what role do they play in SQL? Scalar Functions return a single value based on the Input Values. There are 6 Scalar Functions in SQL namely: • UCASE () - Converts a specified field in upper case • LCASE () - Converts a specified field in lower case • MID ()-Extracts & returns characters from the text field • FORMAT () - Specifies the display format • LEN () - Specifies text field’s length • ROUND ()-Rounds up into a number the decimal field value. 32. What is View in SQL? View is the term that refers to a virtual table with both rows and columns and fields from one or more tables. Syntax: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition 32. How many types of privileges are there in SQL? There are two main types of privileges in SQL namely; • Systems Privilege-This deals with a particular type of object that specifies the right action or actions to be performed which can include a user being given administrative tasks. Examples of system privileges are; ALTER ANY INDEX, ALTER ANY CACHE GROUP CREATE/ALTER/DELETE TABLE, CREATE/ALTER/DELETE VIEW etc. • Objective Privileges: object privileges perform actions on an object or objects of another user. Common types of objective privileges include; EXECUTE INSERT, UPDATE, DELETE, SELECT, FLUSH LOAD, INDEX, and REFERENCES etc. 33. In SQL server, what is referred to as SQL Sandbox? • SQL Sandbox is the location where untrusted scripts are executed in an SQL Server environment. There are three types of SQL Sandboxes namely; • Safe Access Sandbox-in this, a user can be able to perform actions such as creating triggers and stored procedures. However, they cannot gain access to the memory nor create files. • External Access Sandbox-In this, users are given access to the files but can make any changes with regards to memory allocation. • Unsafe Access Sandbox-This is where the untrusted codes are stored and users have access to the memory. 34. What is the difference between SQL and PL/SQL? SQL stands for Structured Language Query that creates and accesses databases while PL/SQL refers to the procedural concepts of programming language. 35. What is referred to as Data Control Language in SQL? Data Control Language is a subset of a database that makes the decision regarding what part of the database should be accessed and at which point in time should the access be granted. In general, data control language allows the user to control access to the database. There are two commands in data control language namely; • GRANT-grants specific user access to perform a task in the database • REVOKE-Cancels previously granted or denied permissions. 36. What is the difference between DROP and TRUNCATE? • TRUNCATE removes all rows from a table and after the removal with no option of retrieval while DROP removes an entire table from the database with no optional of retrieval. 37. What is referred to as Normalization? Normalization organizes data in such a way that redundancy should not occur in the database as well as ensuring that insert, update and delete anomalies do not occur. 38. How many forms of Normalization are there? • First Normal Form (1NF) - removes all duplicate columns from a table. In addition, the first normal form creates a table for related data and identifies unique column values. • First Normal Form (2NF)-creates and places data subsets into individual tables. In addition, it defines the relationship between tables using the primary key. • Third Normal Form (3NF)-Removes unrelated columns through the product key • Fourth Normal Form (4NF) - does not define multi-valued dependencies. 39. What is Stored Procedures and what is it used for? This is a collection of SQL statements used as a function to access the database. Stored procedures are also used to improve performance by reducing network traffic. Syntax: CREATE Procedure Procedure_Name ( //Parameters ) AS BEGIN SQL statements in stored procedures to update/retrieve records END 40. What are some of the properties of Relational Databases? • Each column in a relational database should have a unique name. • The sequence of rows and columns in insignificant • Each row is unique and all values are atomic in relational databases 41. What is a Cursor in SQL? This is a database object used to manipulate data in a row-to-row manner. A cursor follows the following steps; • Declare Cursor • Open Cursor • Retrieve row from the Cursor • Process the row • Close Cursor • Deallocate Cursor 42. What is Collation in SQL? This is a set of rules that compares data to check how it has been sorted. An example of this is Character data that is stored using the correct character sequence along with type, accent and case sensitivity. 43. What does Database White Box testing involve? • 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 44. What does Database Black Box testing involve? • Data Mapping • Data stored and retrieved • Use of Black Box techniques such as Boundary Value Analysis (BVA) and Equivalence Partitioning 45. What are Indices in SQL? Thus is a way of retrieving data more quickly. Using CREATE statements; indices can be created using the following syntaxes. Syntax: CREATE INDEX index_name ON table_name (column_name) Syntax: CREATE UNIQUE INDEX index_name ON table_name (column_name) What is the syntax that adds a record to a table? The INSERT syntax is used to add a record to a table Ex: INSERT into table_name VALUES (value1, value2..); 46. What is the difference between local and global variances? Local variables are used and exist inside a function and can be created whenever a function is called. Local variables are not known to any other function. Global variables are used and exist throughout the program. Similar variables already declared in global cannot be used in any other functions. It is not possible to create global variables whenever that function is called. 47. In SQL, what is referred to as Constraint? A constraint is used to refer to the limit on the data type of a table. 8. What is Auto Increment in SQL? Auto Increment allows users to create a unique number that is generated after the insertion of a new record into a table. AUTO INCREMENT keyword can only be used on an Oracle database while IDENTITY keywords can be used in an SQL Server. In most cases, this keyword is used whenever the PRIMARY KEY is used. 49. What is referred to as a Data-warehouse in SQL? A Data-warehouse in SQL is the nerve center of data received from multiple sources of information. This data is consolidated, transformed and made available for mining and online processing. Data in a Data-warehouse is stored in subsets called Data Marts. 50. What are user-defined functions in SQL? User-defined functions in SQL are functions that are designed to use logic whenever required. It is not a necessity that the same logic be written several times but rather, user-functions can be executed whenever needed. 51. What the main types of user-defined functions? • Scalar Functions. • Inline Table valued functions. • Multi statement valued functions. 52. What is referred to as Online Transaction Processing (OLTP) in SQL? OLTP in SQL is involved in the management of transaction-based applications used for among other activities; data retrieval and processing. Ideally, OLTP makes data management more efficient. OLTP systems primary objective is to serve real-time transactions. 53. What are the advantages of Stored Procedure in SQL? Stored procedure can be used as modular programming-meaning that it can be created, stored and called to action whenever the need arises. The benefit of this is that there is faster execution of queries thereby reducing network traffic as well as providing better data security. The downside of stored procedure is that it can only be executed in the Database and utilizes a lot of memory in the database server. 54. What is a CLAUSE is SQL? A Clause is designed to set a limitation by providing a condition to a query or a set of queries. This is mostly done by filtering some rows from the whole set of records. Example- â€" Query that has WHERE condition Query that has HAVING condition 55. What is an ALIAS command in SQL? ALIAS can be used to describe a table or a column in SQL. It can also be used to refer to identify a table or a column in a WHERE clause. Example Select st.StudentID, Ex.Result from student st, Exam as Ex where st.studentID = Ex. StudentID 56. What us Union, Minus and Interact commands in SQL? • UNION-used to combine the results from two different tables and eliminates duplicate rows from the tables. • MINUS-this operator returns rows from the first query but not those from the second query. • INTERSECT- this operator returns rows from both queries. 57. Differentiate between Aggregate and Scalar functions Aggregate functions evaluate mathematical calculations and produce the results as single values. The calculations are done from table columns. Scalar functions on the other hand return single values based on the input value. Example Aggregate â€" max(), count - Calculated with respect to numeric. Scalar â€" UCASE(), NOW() â€" Calculated with respect to strings. 58. Differentiate between Union and Union All command • Union-omits duplicate records by returning only distinct results set for more than one select statement. • Union All-returns all rows including the ones duplicated in a result set of a different select statement. 59. Which is faster between Union and Union All queries and why? Union All is faster than Union as it doesn’t remove duplicate records. Union query has to check duplicate values which take a little bit of time to remove. 60. In SQL, what is Self-Join? Self-join is a join where a table is joined with itself, especially if the said table has a Foreign Key that references its own Primary Key. 61. How can you get unique records from a table? By using the DINSTINCT keyword 62. What is the main difference between GUI Testing and Database Testing? • GUI Testing is a Front-end or User-interface testing while Database testing is a back-end testing or data testing. • GUI Testing deals with items that can be tested and are open to users such as Menus and Forms whereas Database Testing deals with items that cannot be accessed by the user. • A user performing GUI Testing is not required to know Structured Query Language (SQL) while a person performing Database Testing is required to have SQL knowledge. 63. Name the different types of indices in SQL • Unique Index • Clustered Index • Non-Clustered Index • Bit-Map index • Normal index • Composite index • B-tree index • Function based index 64. Which are the most used SQL joins? The most used joins in SQL are INNER JOIN and LEFT OUTER JOIN and RIGHT OUTER JOIN. 65. What do you understand by the term case manipulation functions?” These are functions that convert data from the format already installed in the table to upper, lower or mixed case. Case manipulation function is used in almost every aspect of the SQL statement. Case manipulation functions are also used to for data when you have no idea whether the data you are searching for is in the lower or the upper case. 66. Name the case manipulation functions available in SQL • UPPER: converts characters into uppercase. • LOWER: converts characters into Lowercase. • INITCAP: converts character values to uppercase for the initials of each word. 67. In SQL, what do you understand by character manipulation? Character manipulation in SQL is used to change, extract or alter the character string. Ideally, when one or more characters are passed through the character manipulation function, the functions performed on these words. 68. Name the different character manipulation functions in SQL • CONCAT: joins two or more values. • SUBSTR: used to extract the string of specific length. • LENGTH: return the length of the string in numerical value. • INSTR: finds the exact numeric position of a specified character. • LPAD: padding of the left-side character value for right-justified value. • RPAD: padding of right-side character value for left-justified value. • TRIM: removes all the defined character from the beginning, end or both beginning and end. • REPLACE: replaces a specific sequence of character with other sequences of character. 69. Name the different types of joins in SQL Since joins are used to merge and retrieve data from two tables. The common types of joins in SQL are; • Inner Join- there are three types of inner joins namely; • Theta join • Natural join • Equijoin • Outer Join-the type of outer joins are; • Right outer join • Left outer join • Full outer join 70. Name the difference between SQL, MySQL and SQL Server SQL denotes the Structured Query Language that is used to communicate with a relational database by providing a way to manipulate data and create databases. MySQL and SQL-Server, on the other hand, are relational database management systems that primarily use SQL as their standard database language. In addition, MySQL is an open source system hence available for free while SQL is not an open source software application. 71. In SQL what is meant by “Trigger” These are a special type of procedures that are stored and only executed automatically after the data has been modified or in place of data modification. In simple terms, Trigger allows the user to execute a batch of code when using queries such as insert or update against a specific table. 72. Name the different types of collation sensitivity • Case Sensitivity â€" A and a and B and b. • Accent Sensitivity • Kana Sensitivity â€" Japanese Kana characters. • Width Sensitivity â€" Single-byte character and double-byte character. Being prepared The normal hiring process for engineers is go through a verbal interview process and then proceed to these types of technical questions. You may experience these questions being asked to you either virtually, through a pair-programming session, where you and one of the previously hired engineers go through a process that requires you to use or answer the types of questions and skills above. Or in other circumstances, you may be asked to answer these types of questions in front of a whiteboard or chalkboard. Where another accompanying engineer may ask you to write on the whiteboard your SQL query answering the subject matter he/she inquired about. Be prepared to have some type of interaction that is above the verbal level. Sharing your insight It can always be quite helpful to share projects or previous engagements where you'd had the opportunity to use SQL/MySQL/Postgres or other. In this sense, think of it as sharing your portfolio. You'll want to walk through what the project was, what your responsibilites were inside the project, what hiccups or errors occured (so what went well and what went not so well) and ultimately the insight that you gleemed from the whole initaitive start to finish. I've found this can be quite helpful in not only making yourself stand out as a distinguishable figure (becasue you are using storytelling to your advantage) but you are also proving your experience and showing your communication skills as an engineer, which helps tremendously. Sharing any type of insight you have about SQL/MySQL/Postgres can be quite helpful. That includes industry knowledge or changes in functionality as well as comparisons against competetive products or databases which you can verbally debate. Footnote As with any of the interview questions pages, I employ you to please email me if you have additional questions that you would like listed or if you see any mistakes that can easily be cleaned up. This resource is vital for those looking to find employment as well as those looking to vet candidates in a professional engineering position.