Common Technical Interview Questions
Most commonly asked campus placement technical interview questions based on database and sql, mostly asked by the companies like Infosys, Capgemini, Cognizant, Capgemini, DXC, Accenture etc.
What is a database?
A Database is a logical, consistent and organized collection of data that it can easily be accessed, managed and updated. Databases, also known as electronic databases are structured to provide the facility of creation, insertion, and updating of the data efficiently and are stored in the form of a file or set of files, on the magnetic disk, tapes and another sort of secondary devices. Database mostly consists of the objects (tables), and tables include of records and fields. Fields are the basic units of data storage, which contain the information about a particular aspect or attribute of the entity described by the database. DBMS is used for extraction of data from the database in the form of the queries.
What is a database system?
The collection of database and DBMS software together is known as a database system. Through the database system, we can perform many activities such as-
The data can be stored in the database with ease, and there are no issues of data redundancy and data inconsistency.
The data will be extracted from the database using DBMS software whenever required. So, the combination of database and DBMS software enables one to store, retrieve and access data with considerate accuracy and security.
What are the advantages of DBMS?
Redundancy control
Restriction for unauthorized access
Provides multiple user interfaces
Provides backup and recovery
Enforces integrity constraints
Ensure data consistency
Easy accessibility
Easy data extraction and data processing due to the use of queries
What do you mean by transparent DBMS?
The transparent DBMS is a type of DBMS that keeps its physical structure hidden from users. Physical structure or physical storage structure implies to the memory manager of the DBMS, and it describes how the data is stored on disk.
What is RDBMS?
RDBMS stands for Relational Database Management Systems. It is used to maintain the data records and indices in tables. RDBMS is the form of DBMS that uses the structure to identify and access data concerning the other piece of data in the database. RDBMS is the system that enables you to perform different operations such as update, insert, delete, manipulate, and administer a relational database with minimal difficulties. Most of the time RDBMS uses SQL language because it is easily understandable and is used often.
Describe the types of database languages.
Data Definition Language (DDL): DDL is used to define and modify the structure of database objects such as tables, indexes, and views. Examples of DDL commands include CREATE, ALTER, and DROP.
Data Manipulation Language (DML): DML is used to retrieve, insert, update, and delete data from a database. Examples of DML commands include SELECT, INSERT, UPDATE, and DELETE.
Data Control Language (DCL): DCL is used to control access to the database by defining permissions and privileges for database users. Examples of DCL commands include GRANT and REVOKE.
Transaction Control Language (TCL): TCL is used to manage transactions, which are a group of database operations that need to be executed together as a single unit of work. Examples of TCL commands include COMMIT and ROLLBACK.
What is meant by ACID properties in DBMS?
Are NULL values in a database the same as that of blank space or zero?
No, a NULL value is very different from that of zero and blank space as it represents a value that is assigned, unknown, unavailable, or not applicable as compared to blank space which represents a character and zero represents a number.
What is the Relationship?
The Relationship is defined as an association among two or more entities. There are three types of relationships in DBMS-
One-To-One: Here one record of any object can be related to one record of another object.
One-To-Many (many-to-one): Here one record of any object can be related to many records of other objects and vice versa.
Many-to-many: Here more than one record of an object can be related to n number of records of another object.
What is a degree of Relation?
The degree of relation is a number of attributes of its relation schema. A degree of relation is also known as Cardinality it is defined as the number of occurrences of one entity that is connected to the number of occurrences of another entity. There are three degrees of relation they are one-to-one(1:1), one-to-many(1:M), and many-to-one(M: M).
What is data abstraction in DBMS?
Data abstraction in DBMS is a process of hiding irrelevant details from users. Because database systems are made of complex data structures so, it makes accessible the user interact with the database.
What is normalization?
Normalization is a process of analyzing the given relation schemas according to their functional dependencies. It is used to minimize redundancy and also used to minimize insertion, deletion, and update distractions. Normalization is considered as an essential process as it is used to avoid data redundancy, insertion anomaly, updation anomaly, and deletion anomaly.
They're most commonly used normal forms are:
First Normal Form(1NF)
Second Normal Form(2NF)
Third Normal Form(3NF)
Boyce & Codd Normal Form(BCNF)
What is an Entity type?
An entity type is specified as a collection of entities, having the same attributes. Entity type typically corresponds to one or several related tables in the database. A characteristic or trait which defines or uniquely identifies the entity is called entity type.
What is an attribute?
An attribute refers to a database component. It is used to describe the property of an entity. An attribute can be defined as the characteristics of the entity. Entities can be uniquely identified using the attributes. Attributes represent the instances in the row of the database.
What is Data Independence?
Data independence specifies that "the application is independent of the storage structure and access strategy of data." It makes you able to modify the schema definition at one level without altering the schema definition at the next higher level.
It makes you able to modify the schema definition at one level and should not affect the schema definition at the next higher level.
There are two types of Data Independence:
Physical Data Independence: Physical data is the data stored in the database. It is in the bit format. Modification on the physical level should not affect the logical level.
Logical Data Independence: Logical data in the data about the database. It basically defines the structure. Such as tables stored in the database. Modification at the logical level should not affect the view level.
What is denormalization?
Denormalization is the opposite of normalization; redundant data is added to speed up complex queries that have multiple tables that need to be joined. Optimization of the read performance of a database is attempted by adding or grouping redundant copies of data.
What is the difference between BETWEEN and IN operators in SQL?
The BETWEEN operator is used to represent rows based on a set of values. The values may be numbers, text, or dates. The BETWEEN operator returns the total number of values that exist between two specified ranges.
The IN condition operator is used to search for values within a given range of values. If we have more than one value to choose from, then we use the IN operator.
What is Join?
The Join operation is one of the most useful activities in relational algebra. It is most commonly used way to combine information from two or more relations. A Join is always performed on the basis of the same or related column. Most complex queries of SQL involve the JOIN commands.
There are the following types of join:
Inner joins: Inner join is of 3 categories. They are:
Theta join
Natural join
Equi join
Outer joins: Outer join have three types. They are:
Left outer join
Right outer join
Full outer join
Difference between DBMS and RDBMS in table form
What is the difference between a DELETE command and TRUNCATE command
Describe the types of keys?
There are the following types of keys:
Primary key: The Primary key is an attribute in a table that can uniquely identify each record in a table. It is compulsory for every table.
Candidate key: The Candidate key is an attribute or set of an attribute that can uniquely identify a tuple. The Primary key can be selected from these attributes.
Super key: The Super key is a set of attributes that can uniquely identify a tuple. The super key is a superset of the candidate key.
Foreign key: The Foreign key is a primary key from one table, which has a relationship with another table. It acts as a cross-reference between tables.
Difference between Clustered and Non-clustered index?
SQL Queries –
Write a query to get the current date.
SELECT GETDATE();
Write a query to find all the employees whose salary is between 50000 to 100000.
SELECT * FROM EmployeePosition
WHERE Salary BETWEEN '50000' AND '100000';
Write a query to find the names of employees that begin with ‘S’
SELECT * FROM EmployeeInfo
WHERE EmpFname LIKE 'S%';
Write a query in SQL to find the minimum and maximum number from the integer column
SELECT MAX(Column_Name), MIN(Column_Name) FROM Table_Name;
Write a Query to find the Nth highest value of an integer column from the table.
SELECT TOP 1 Column_Name
FROM (
SELECT DISTINCT TOP N Column_Name
FROM Table_Name
ORDER BY Column_Name DESC
)
ORDER BY Column_Name ASC;
Write a query in SQL to find the second-highest value of an integer column from the table?
Select MAX(Column_Name)
from Table_Name
where Column_Name NOT IN (Select MAX(Column_Name) from Table_Name);
Write a query to show all the record of those students whose Marks is greater than 82 and age is 22
SELECT *
FROM Student
WHERE Stu_Marks > 82 and Stu_Age = 22;
Write a Query to add another column in the existing table:
ALTER TABLE Table_Name
ADD Column_Name Datatype ( Length_of_Column) ;
Write a query to fetch 50% records from the EmployeeInfo table.
SELECT *
FROM EmployeeInfo
WHERE
EmpID <= (SELECT COUNT(EmpID)/2 from EmployeeInfo);
Write a SQL query to create a table where the structure is copied from other table.
To create an Empty table
CREATE TABLE NewPatientsTable
SELECT *
FROM Patients
WHERE 1=0;
Write a query to fetch even and odd rows from a table.
Fetch even rows using MOD() function:
SELECT *
FROM Patients
WHERE MOD(PatientID,2)=0;
Fetch odd rows using MOD() function:
SELECT *
FROM Patients
WHERE MOD(PatientID,2)=1;
Write a query to fetch the number of patients whose weight is greater than 68.
SELECT COUNT(*)
FROM PatientsCheckup
WHERE Weight > '68';
Write an SQL query to fetch all those employees who work on Projects other than P1, without using NOT operator.
SELECT EmpId
FROM EmployeeSalary
WHERE Project <> 'P1';
Write an SQL query to display the total salary of each employee adding the Salary with Variable value.
SELECT EmpId,
Salary+Variable as TotalSalary
FROM EmployeeSalary;
Write an SQL query to fetch all the EmpIds which are present in either of the tables – ‘EmployeeDetails’ and ‘EmployeeSalary’.
SELECT EmpId FROM EmployeeDetails
UNION
SELECT EmpId FROM EmployeeSalary;
Write an SQL query to fetch the EmpIds that are present in EmployeeDetails but not in EmployeeSalary.
SELECT EmpId FROM
EmployeeDetails
where EmpId Not IN
(SELECT EmpId FROM EmployeeSalary);
Write an SQL query to display both the EmpId and ManagerId together.
SELECT CONCAT(EmpId, ManagerId) as NewId
FROM EmployeeDetails;
Write an SQL query to update the employee names by removing leading and trailing spaces.
UPDATE EmployeeDetails
SET FullName = LTRIM(RTRIM(FullName));
Also Read: Most Asked Technical Interview Questions