3 Weeks Beginners Guide to Ace Data Science Interview: #Day 21

Interview Questions based on SQL and DBMS

About the Series

Data Science field is an exciting career choice and seeing a lot of hiring across fresh, lateral and experienced job positions. It’s one matter of knowing the concepts and another is to crack the rigorous interviews for data science positions. If one is aware of the different questions and the interview process, the candidate is on the right path to an excellent career in the evolving Data Science field.

Our 3-week beginners guide to Ace Data Science Interview will be a useful asset for individuals who are preparing for the Data Science interviews. Every day for the next 21 days, we will talk about the different areas of the Data Science field and cover them eloquently. So sit back and start reading the article to get a finer understanding of the Data Science field and go prepared for the interviews.

RDBMS is one of the most commonly used databases till date, and therefore SQL skills are indispensable in most of job roles. In this SQL Interview Questions blog, I will introduce you to the most frequently asked questions on SQL (Structured Query Language). This blog is the perfect guide for you to learn all the concepts related to SQL, MS SQL Server, and MySQL database.

Are you ready for questions-

Question 1: What do you mean by table and field in SQL?

Answer:

A table refers to a collection of data in an organized manner in the form of rows and columns. A field refers to the number of columns in a table. For example:

Table: student information
Field: Stu Id, Stu Name, Stu Marks, Stu Age.

Question2: What is a schema in SQL Server?

Answer:

A schema is a collection of database objects including tables, views, triggers, stored procedures, indexes, etc. …

Question 3:What do you mean by DBMS? What are its different types?

Answer:

A database is a structured collection of data.

A Database Management System (DBMS) is a software application that interacts with the user, applications and the database itself to capture and analyze data.

A DBMS allows a user to interact with the database. The data stored in the database can be modified, retrieved and deleted and can be of any type like strings, numbers, images, etc.

There are two types of DBMS:

• Relational Database Management System: The data is stored in relations (tables). Example — MySQL.
• Non-Relational Database Management System: There is no concept of relations, tuples, and attributes. Example — Mongo

Question 4: What is the difference between DELETE and TRUNCATE statements?

Answer:

Question 5: What is the difference between CHAR and VARCHAR2 datatype in SQL?

Answer:

Both Char and Varchar2 are used for character datatype but varchar2 is used for character strings of variable length whereas Char is used for strings of fixed length. For example, char(10) can only store 10 characters and will not be able to store a string of any other length whereas varchar2(10) can store any length i.e 6,8,2 in this variable.

Question 6: What is the Primary key?

Answer:

• A Primary key is a column (or collection of columns) or a set of columns that uniquely identifies each row in the table.
• Uniquely identifies a single row in the table
• Null values not allowed

Question 7: What is the difference between SQL and MySQL?

Answer:

• SQL is a standard language used to wiggle around with databases, which stands for Structured Query Language.
• Whereas MySQL is a database management system.
• SQL is the core of relational database which is used for accessing and managing database, MySQL is an RDMS (Relational Database Management System) such as SQL Server, Informix, etc.

Question 8:What is a Foreign key?

Answer:

• The foreign key maintains referential integrity by enforcing a link between the data in two tables.
• The foreign key in the child table references the primary key in the parent table.
• The foreign key constraint prevents actions that would destroy links between the child and parent tables.

Question 9: List the different type of joins?

Answer:

There are various types of joins that are used to retrieve data between the tables. There are four types of joins, namely:

Inner join: Inner Join in MySQL is the most common type of join. It is used to return all the rows from multiple tables where the join condition is satisfied.

Left Join: Left Join in MySQL is used to return all the rows from the left table but only the matching rows from the right table where the join condition is fulfilled.

Right Join: Right Join in MySQL is used to return all the rows from the right table but only the matching rows from the left table where the join condition is fulfilled.

Full Join: Full join returns all the records when there is a match in any of the tables. Therefore, it returns all the rows from the left-hand side table and all the rows from the right-hand side table.

Question 10: What is the ACID property in a database?

Answer:

ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data transactions are processed reliably in a database system.

Atomicity: Atomicity refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means 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. In simple words, you can say that your transaction never leaves the database without completing its state.

Isolation: The main goal of isolation is concurrency control.

Durability: Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.

Question 11: What is the difference between cross join and natural join?

Answer:

The cross join produces the cross product or Cartesian product of two tables whereas the natural join is based on all the columns having the same name and data types in both the tables.

Question 12:

It’s a general question though which interviewer want’s to know about your diversified working Area.

Interviewer: Do you have experience with Spark or big data tools for machine learning?

You’ll want to get familiar with the meaning of big data for different companies and the different tools they’ll want. Spark is the big data tool most in demand now, able to handle immense datasets with speed. Be honest if you don’t have experience with the tools demanded, but also take a look at job descriptions and see what tools pop up: you’ll want to invest in familiarizing yourself with them.

Summary

In this blog, I have presented you with the basics concepts of Machine learning and I hope this blog was helpful and would have motivated you enough to get interested in the topic.

If this blog helped you in any way, then do Follow and Clap👏, because your encouragement catalyzes inspiration and help to create more cool stuff like this. As always, I welcome feedback and constructive criticism, love to hear from your end.

Check what’s in Day1, Day2, Day3, Day4, Day5, Day6, Day7, Day8, Day9, Day10.