SQL, is an important tool in any data scientist’s arsenal. Good knowledge of SQL is required for seamless workflow management in a data science role.
Owing to the importance of SQL in a traditional data science role, it is one of the most important things to brush up on before going in for an interview. Questions can range from basic concepts such as an explanation of what RDBMS is to the difference between various functions in the software.
SQL can be used to get data from a live database. For example, if a database is used in production, it can be dynamically accessed and queried using SQL. This removes the need for exporting into another format and allows for better data management.
Here are 10 interview questions you will be asked about SQL in your next interview.
1) What is DBMS?
DBMS stands for a database management system. DBMS are present everywhere in the world today, as they are the most popular way to access and interact with databases. They are also scalable and can handle big datasets with ease.
DBMS supports databases that contain all kinds of data, such as images, strings, numbers and more.
2) What are the types of DBMS?
There are two kinds of database management system:
Relational Database Management System: In this DBMS, the data is stored in a relational form. This means that structures such as tables and arrays are used to present the data. A popular example is MySQL.
Non-Relational Database Management System: In this DBMS, data is not stored in a relational form. A popular example is MongoDB.
3) What do you mean by table and field in SQL?
A table means an organized set of data in an SQL database. It is organized in terms of rows and columns for easy access to the data.
A field is the name of columns in a table. This includes different sub-data points that offer a greater perspective in the table.
4) What is the difference between SQL and MySQL?
SQL refers to the standard language that was constructed to conduct database tasks like retrieval and manipulation. SQL determines how the data is stored in an organized database.
MySQL is simply an RDBMS software that is used to manage SQL servers. Other software include SQL Server, Oracle and IBM DB2.
5) What are the constraints in SQL?
Constraints are the main way to specify rules in SQL databases. It is easily extensible and can be applied to one or multiple fields in a table. Constraints can be enforced either when the table is created or using the ALTER TABLE command.
6) What are the different types of constraints in SQL? Offer a brief explanation of each.
- NOT NULL: Ensures that a null value is not inserted into a field
- CHECK: Ensures that all entries in a field satisfy a set condition
- DEFAULT: Assigns a default value if no value is entered for a specific field
- UNIQUE: Ensures that all values entered into a field are unique.
- INDEX: This constraint runs an indexing function on the database, making it easier to query.
7) What is a join in SQL?
Join is a command in SQL which allows for the fields of two different tables to be joined together. This union is conducted based on a related column between the two databases and allows for the consolidation of databases.
8) What are the different types of join in SQL?
There are many different types of joins in SQL. Some of them include
Inner Join: This join retrieves records that have matching records in both the tables. It is a simple join that consolidates a field in both tables and is hence used for queries.
Left/Right Outer Join: Depending on the side of the join, the command retrieves all the records from one table and just the matched records from another table. This is used when the integrity of tables is important.
Full Outer Join: This join just retrieves all the records that are matched in both the left and right tables.
9) What is an index?
An index is a data structure in SQL that allows for the quick querying of a database. It is an index structure that keeps track of the location of data in the database and consumes additional space.
10) What are the different kinds of index in SQL?
Unique and non-unique index:
Unique indices maintain data integrity in a large database. They do so by ensuring that no two rows have identical key values.
Non-unique index is used, instead, to improve query performance. It does so by maintaining a list of frequently-accessed data values.
Clustered and non-clustered index:
In a clustered index, the order of the rows in the index and the order of the rows in the database are similar. This increases the query performance of the database greatly, as it presents a linear-access path for data access.
Non-clustered index is very similar to a clustered index, except that a separate entity is created to contain the index. This references the original database.
