The relational database MySQL remains one of the most extensively used Database Management systems over the years. A database is a collection of data in layman’s terms: structured, organized, and stored in the digital form to ensure easy retrieval, access, management, and manipulation of business data. While SQL(Structured Query Language) is a type of query language, adopted by different RDBMS to query their database. With an ongoing demand to resurface businesses online, there is a growing need for professionals with advanced data management skillsets. An early 2021 Indeed report indicates that SQL is the most in-demand skill among all jobs in data, comprising 42.7% of all job openings. Ergo, it’s absolutely essential to prepare yourself with some of the most frequently asked SQL interview questions. So let’s get started.
1. What is SQL and what role does it play in DBMS?
SQL (Structured Query Language) is a primary language to interact with the database. With the help of SQL, the user can extract, access, modify data from the database and also update it whenever there is a firm’s requirement. For example: if an enterprise has all sorts of details such as UAN, Name, address, paygrade, and other details of their employee, with the help of SQL, all of these data can be queried to find the required information in no time.
2. What is DBMS?
DBMS acronym for Database Management System is a program that manages and controls the creation, edit, and use of a big heap of data known as a “database”. In easy words, think of DBMS as a file management system that manages data in a database rather than saving it in the file system.
3. DBMS vs RDBMS
One can say that RDBMS Is an extension of DBMS. The key difference is RDBMS (Relational Database Management) applications store their data in a tabular form, while DBMS applications are used to store data as files. One example of RDBMS is Oracle. Usually, recruiters begin with such SQL interview questions to ensure that you are the right fit.
4. What are the usage of SQL?
SQL is responsible for queuing the relational data and data structure present in the database. Here are some of the SQL functionalities:
- Executing queries against a database.
- Retrieving data from the database.
- Inserting, Updating, and deleting records.
- Creating View-mode in the database.
- Creating new tables in the database.
- Performing complex operations on the database.
- Creating an additional database upon request.
4. IS SQL a programming Language?
Not Really! Although SQL does satisfy the definition of programming language, it isn’t a general-purpose programming language such as JAVA or C++. SQL as a language does not support loop, logistic regression, conditional statement, and other intricacies which makes a traditional programming language. It can only be used for data manipulation. Likewise, SQL, with its specific application domain, can be defined as a domain-specific language.
Meanwhile, If you are planning to quench your curiosity in Non-relational (NoSQL) systems such as MongoDB, you can opt for a bundle course at GUVI. You will receive individual certification upon completion, which you can add to your resume to add value. Read MongoDB Vs MySQL to know about the key differences.
5. Define Subsets of SQL
There are mainly three significant subsets of SQL:
- DCL: DCL short for Data Control Language is processed to control administrative access to the dataset and include commands such as REVOKE, GRANT, etc.
- DDN: The DDN (Data Definition is used to define data structures if it consists of commands like ALTER, CREATE, DROP, etc.
- DML: DML ( Data Manipulation Language) is used to manipulate already existing data in the database with command categories such as UPDATE, SELECT, INSERT, etc.
6. What are tables and Fields in SQL?
A table as the name suggests is a set of data that are organized in a model with rows and columns. Rows are organized vertically, while columns are organized horizontally. A table has a set number of columns known as fields but can have any number of rows, referred to as Record.
Field: Name, Age, Sex, DOB, Ref Doc, and Department
Data: John, 36, M, 23/12/1984, Dr. Hopkins, Orthopaedics.
7. How can one create a Table in SQL?
The command one can use to create a table in Sql is pretty simple.
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );
You can start off by assigning the keyword, CREATE TABLE, then you can assign the name of the table. After that in braces, you have to list out all the columns along with their relevant datatypes.
For example, if you want to create above specified patient model for Hospital:
CREATE TABLE patient ( name varchar(25), age int, gender varchar(25), INSERT INTO t(dob) VALUES(TO_DATE('21/12/2020', 'DD/MM/YYYY')), refdoc varcher(25), department archer(40), .... );
8. What is a primary Key in SQL?
Just like in India, everyone has got a unique 16-digits Aadhar Card Digits and PAN Card No, a primary key is a field or the combination of fields that uniquely specify a row. Its value can not be NULL.
9. What is a foreign Key?
On the other hand, a foreign key is specified as a key that is related to the primary key of another number. To generate a foreign key, one needs to create a relationship between two tables by referencing a foreign key with a primary key of another table. The foreign key acts like a cross-reference between two tables. The primary-forging key relationship is one of the crucial relationships as it often manages the ACID properties of the database.
10. List out different types of database management system?
There are mainly four types of database management system:
- Relational Database (RDBMS)
- Network Database (IDMS)
- Hierarchal Database (DBMS)
- Object-oriented Database
RDBMS is the most widely used database because of its easy accessibility and tech support for reading complex queriers.
11. What is Normalization and what are different types of normalization?
Normalization is the process to organise fields and tables of the database for a motive to reduce dependency and redundancy. The primary use of the normalization is to remove the DELETE, INSERT and UPDATE distractions. Normalizations deconstruct the tables into small partitions and then link them using the various relationships to minimize the chances of redundancy.
However, there are some rules of database normalization which are commonly known as Normal form, they are:
FIRST NORMAL FORM (1NF): Removes the duplicate columns from the table. The rule is also responsible for the creation of tables for the related data and the identification of unique columns.
SECOND NORMAL FORM (2NF): The primary use of the second normal form is to meet all the specified requirements of the first normal form. It places the subset of data into separate tables and creations relationships between tables using primary keys.
THIRD NORMAL FORM (3NF): Similarly, 3NF should meet all the requirements of Second normal form. It also removes the columns which are not directly dependents on primary key constraints.
FOURTH NORMAL FORM (4NF): The 4NF tries to meet all the requirements of above form and it should not have any multi-valued dependancies.
Using the above steps, you can remove the anomalies, redundancies, and inconstancies of any database.
12. What is an Index in SQL
Indexes are the special lookup tables that the database search engine can use to speed up the process of data retrieval. As we know the high cost of the query leads to falling in the performance of the query, an index enables the processor to increase the performance and allow faster data retrieval’s. Yes, the word index synonym with an index chapter of a book, why would you go through every page of the book, when you can directly go to the index. Similarly, indexing in sql has a unique value that implies that an index can’t be duplicated.
13. What are the diff types of Indexes in SQL?
Diff Types of Index in SQL are:
- Clustered Index
- Unique Index
- NonClustered Index
- B-Tree Index
- Bit-Map Index
- Normal Index
- Function Based Index
- Composite Index
14. Difference between a Clustered and Non-clustered index?
A clustered index is basically used to rearrange the physical order of the table and search based on key values. While the non-clustered index does not alter the physical order of the table and rather maintains the logical order of the database. Each table can only has one clustered index, while each table can comprise of maximum of 999 clustered index. Other differences includes:
- Clustered index sort and store data row in the table or view based on their key value, while non-cluster has a structure separate from the data row.
- Clustered indexes store the data information and the data itself whereas non-clustered index stores only the information, and then it will refer you to the data stored in clustered data.
- Reading from a clustered index is much faster than reading from a non-clustered index from the same table.
15. What are Joins? What is the most widely used SQL Join?
Just as the name suggests, joins are the functions to merge two tables or retrieve data from the tables. Although it depends on the relationship between the tables. The types of SQL joins:
INNER JOIN: They are of three types:
- Theta join
- Natural join
OUTER JOIN: They are of three types
- Right outer join
- Left outer join
- Full outer join
The most commonly used SQL Joins are INNER JOIN, Right OUTER JOIN and Left OUTER JOIN.
16. Mention the SQL query to display the current date?
SQL has an inner-built function known as GetDate (), which is used to print the current timestamp. It is one of the highly asked SQL interview questions.
17. What are operators?
Similar to other programming languages, operators are special characters or keywords reserved to perform a specific operations in SQL queries. There are mainly three types of operators used in SQL:
- Logical Operators: ALL, ANY, AND, EXISTS, INSULL, IN, LIKE, BETWEEN, OR, NOT, UNIQUE.
- Comparison Operator: =, <>, <,>,<=,>=,!>, !<, !=
- Arithmetic Operators: Subtraction (-), division (/), Addition(+), Multiplication(*) etc.
18. Explain Set Operators in SQL?
SQL queries containing set operations are known as Compound Queries. The set operators used in SQL are Union, Intersect, Union All or Minus.
There is a plethora of other details and intricacies within the SQL, however, due to length constraints, we won’t be able to mention all of the SQl interview questions. That’s why we suggest you opt for GUVI’s professionally curated SQL Course. You can get access to 25+ self-paced exclusive lessons curated under the guidance of top industry experts, which will not only enhance your knowledge but will also make you Job-Ready!!