DBMS Interview Questions and Answers
Question - 71 : - Write a query to create a duplicate table with and without data present?
Answer - 71 : -
Consider you have a table named Customers, having details such as CustomerID, CustomerName and so on. Now, if you want to create a duplicate table named ‘DuplicateCustomer’ with the data present in it, you can mention the following query:
1 CREATE TABLE DuplicateCustomer AS SELECT * FROM Customers;
Similarly, if you want to create a duplicate table without the data present, mention the following query:
1 CREATE TABLE DuplicateCustomer AS SELECT * FROM Customers WHERE 1=2;
Question - 72 : - Mention a query to calculate the even and odd records from a table
Answer - 72 : -
To write a query to calculate the even and odd records from a table, you can write two different queries by using the MOD function.
So, if you want to retrieve the even records from a table, you can write a query as follows:
1 SELECT CustomerID FROM (SELECT rowno, CustomerID from Customers) where mod(rowno,2)=0;
Similarly, if you want to retrieve the odd records from a table, you can write a query as follows:
1 SELECT CustomerID FROM (SELECT rowno, CustomerID from Customers) where mod(rowno,2)=1;
Question - 73 : - Write a query to remove duplicate rows from a table?
Answer - 73 : -
To remove duplicate rows from a table, you have to initially select the duplicate rows from the table without using the DISTINCT keyword. So, to select the duplicate rows from the table, you can write a query as follows:
1 SELECT CustomerNumber FROM Customers WHERE ROWID (SELECT MAX (rowid) FROM Customers C WHERE CustomerNumber = C.CustomerNumber);
Now, to delete the duplicate records from the Customers table, mention the following query:
1 DELETE FROM Customers WHERE ROWID(SELECT MAX (rowid) FROM Customers C WHERE CustomerNumber = C.CustomerNumber);
Question - 74 : - Write a query to retrieve the last day of next month in Oracle.
Answer - 74 : -
To write a query to retrieve the last day of the next month in Oracle, you can write a query as follows:
1 SELECT LAST_DAY (ADD_MONTHS (SYSDATE,1)) from dual;
Question - 75 : - What are the different type of relationships in the DBMS?
Answer - 75 : -
Relationships in DBMS depicts an association between the tables.
Different types of relationships are:
- One-to-One: This basically states that there should be a one-to-one relationship between the tables i.e. there should be one record in both the tables. Example: Among a married couple, both wife and husband can have only one spouse.
- One-to-Many: This states that there can be many relationships for one i.e. a primary key table hold only one record which can have many, one or none records in the related table. Example: A Mother can have many children.
- Many-to-Many: This states that both the tables can be related to many other tables. Example: One can have many siblings and so do they have.
Answer - 76 : - Stored Procedure is a group of SQL statements in the form of a function that has some unique name and is stored in relational database management systems(RDBMS) and can be accessed whenever required.
Question - 77 : - What is meant by trigger?
Answer - 77 : - Trigger is one of the very important codes or programs which get executed automatically in response to the events that occur in a table or a view. For Example, If a new record is inserted in an employee database then the data gets created automatically in the related tables like salary, department and roles tables.
Question - 78 : - What are different types of joins in SQL?
Answer - 78 : -
There are 4 types of SQL Joins:
- Inner Join: This type of join is used to fetch the data among the tables which are common in both the tables.
- Left Join: This returns all the rows from the table which is on the left side of the join but only the matching rows from the table which is on the right side of the join.
- Right Join: This returns all the rows from the table which is on the right side of the join but only the matching rows from the table which is on the left side of the join.
- Full Join: This returns the rows from all the tables on which the join condition has put and the rows which do not match hold null values.
Answer - 79 : - A Join is one of the SQL statements which is used to join the data or the rows from 2 or more tables on the basis of a common field/column among them.
Question - 80 : - How is the pattern matching done in the SQL?
Answer - 80 : -
With the help of the LIKE operator, pattern matching is possible in the SQL.’%’ is used with the LIKE operator when it matches with the 0 or more characters and ‘_’ is used to match the one particular character.
Example:
SELECT * from Emp WHERE name like ‘b%’;
SELECT * from Emp WHERE name like ‘hans_’;