Database management system is software that is used to manage the database. The database is a collection of inter-related data which is used to retrieve, insert and delete the data efficiently. It is also used to organize the data in the form of a table, schema, views, and reports, etc.
For Example, MySQL, Oracle, etc. are popular commercial DBMS used in different applications.
Below is the list of mostly asked interview questions related to DBMS:
Ques: What is database?
Ans: A information may be a logically coherent assortment of information with some inherent that means, representing some facet of globe and that is meant, designed and inhabited with information for a selected purpose.
Ques: What is DBMS?
Ans: It is a set of programs that permits user to form and maintain a info. In alternative words its general computer code that has the users with the processes of shaping, constructing and manipulating the info for numerous applications.
Ques: What is a Database system?
Ans: The database and DBMS software together is called as Database system.
Ques: What are the advantages of DBMS?
Ans:
- Redundancy is controlled.
- Unauthorized access is restricted.
- Providing multiple user interfaces.
- Enforcing integrity constraints.
- Providing backup and recovery.
Ques: What are the disadvantages in File Processing System?
Ans:
- Data redundancy and inconsistency.
- Difficult in accessing data.
- Data isolation.
- Data integrity.
- Concurrent access is not possible.
- Security Problems.
Ques: Describe the three levels of data abstraction?
Ans: There are three levels of abstraction:
- Physical level: The lowest level of abstraction describes how data are stored.
- Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
- View level: The highest level of abstraction describes only part of entire database.
Ques: Define the “integrity rules”?
Ans: There are two Integrity rules.
- Entity Integrity: States that “Primary key cannot have NULL value”
- Referential Integrity: States that “Foreign Key can be either a NULL value or should be Primary Key value of other relation.
Ques: What is extension and intension?
Ans:
- Extension: It is the number of tuples present in a table at any instance. This is time dependent.
- Intension: It is a constant value that gives the name, structure of table and the constraints laid on it.
Ques: What is System R? What are its two major subsystems?
Ans: System R was designed and developed over a period of 1974-79 at IBM San Jose Research Center. It is a prototype and its purpose was to demonstrate that it is possible to build a Relational System that can be used in a real life environment to solve real life problems, with performance at least comparable to that of existing system.
Its two subsystems are:
- Research Storage
- System Relational Data System.
Ques: How is the data structure of System R different from the relational structure?
Ans: Unlike Relational systems in System R:
- Domains are not supported
- Enforcement of candidate key uniqueness is optional
- Enforcement of entity integrity is optional
- Referential integrity is not enforced
Ques: What is Join?
Ans: An SQL Join is used to combine data from two or more tables, based on a common field between them. For example, consider the following two tables.
Student Table
ENROLLNO | STUDENTNAME | ADDRESS |
---|---|---|
1000 | geek1 | geeksquiz1 |
1001 | geek2 | geeksquiz2 |
1002 | geek3 | geeksquiz3 |
Student Course Table
COURSEID | ENROLLNO |
---|---|
1 | 1000 |
2 | 1000 |
3 | 1000 |
1 | 1002 |
2 | 1003 |
Following is join query that shows names of students enrolled in different courseIDs.
SELECT StudentCourse.CourseID, Student.StudentName
FROM StudentCourse
INNER JOIN Customers
ON StudentCourse.EnrollNo = Student.EnrollNo
ORDER BY StudentCourse.CourseID;
The above query would produce following result.
COURSEID | STUDENTNAME |
---|---|
1 geek1 | |
1 | geek2 |
2 | geek1 |
2 | geek3 |
3 | geek1 |
Ques: What is a view in SQL? How to create one
Ans: A view is a virtual table based on the result-set of an SQL statement. We can create using create view syntax.
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Ques. There is a table where only one row is fully repeated. Write a Query to find the repeated row
Name | Section |
---|---|
abc | CS1 |
bcd | CS2 |
abc | CS1 |
In the above table, we can find duplicate row using below query.
SELECT name, section FROM tbl GROUP BY name, section HAVING COUNT(*) > 1
Ques. What is the Query to find 2nd highest salary of an employee?
Ans:
SELECT max(salary) FROM EMPLOYEES WHERE salary IN (SELECT salary FROM EMPLOYEEs MINUS SELECT max(salary) FROM EMPLOYEES);
OR
SELECT max(salary) FROM EMPLOYEES WHERE salary <> (SELECT max(salary) FROM EMPLOYEES);
Ques. Get employee details from employee table whose first name ends with ‘n’ and name contains 4 letters
Ans: Select * from EMPLOYEE where FIRST_NAME like ‘_____n’ (Underscores)
Ques. Get employee details from employee table whose first name starts with ‘J’ and name contains 4 letters
Ans: Select * from EMPLOYEE where FIRST_NAME like ‘J_______ ‘ (Underscores)
Ques. Get employee details from employee table whose Salary greater than 600000
Ans: Select * from EMPLOYEE where Salary >600000
Ques. Get employee details from employee table whose Salary less than 800000
Ans: Select * from EMPLOYEE where Salary <800000
Ques. Get employee details from employee table whose Salary between 500000 and 800000
Ans: Select * from EMPLOYEE where Salary between 500000 and 800000
You may also like:- 260 One-Liner Information Security Questions and Answers for Fast Learning
- Top 20 HTML5 Interview Questions with Answers
- 80 Most Important Network Fundamentals Questions With Answers
- 100 Most Important SOC Analyst Interview Questions
- Top 40 Cyber Security Questions and Answers
- Top 10 React JS Interview Theory Questions and Answers
- CISSP – Practice Test Questions – 2024 – Set 20 (53 Questions)
- Part 2: Exploring Deeper into CCNA – Wireless (145 Practice Test Questions)
- Part 1: Mastering CCNA – Wireless (145 Practice Test Questions)
- [1z0-1085-20] Oracle Cloud Infrastructure Foundations 2020 Associate MCQ Questions – Part 3