DBMS Interview Questions

Ques. What is the difference between cross join and full outer join?

A cross join returns the cartesian product of the two tables. So there is no condition or on clause as each row of TabelA is joined with each row of TableB whereas a full outer join will join the two tables on the basis of the condition specified in the on clause and for the records not satisfying the condition null value is placed in the join result.

ArtOfTesting

Ans.

DBMS Interview Questions

Ques. What is a View in SQL?

CREATE VIEW VIEW_NAME AS SELECT COLUMN1, COLUMN2 FROM TABLE_NAME WHERE CONDITION;

ArtOfTesting

Ans. A view is a virtual table. It is a named set of SQL statements that can be later referenced and used as a table.

DBMS Interview Questions

Ques. Can we use ‘where’ clause with ‘GROUP BY‘?

SELECT Employee, Count(Project ) FROM Emp_Project WHERE Employee != 'A' GROUP BY Project;

ArtOfTesting

Ans. Yes, we can use ‘where’ clause with ‘GROUP BY’. The rows that don’t meet the where conditions are removed first and then the grouping is done based on the GROUP BY column.

DBMS Interview Questions

Ques. What is a coalesce function?

COALESCE(NULL, NULL, 5, ‘ArtOfTesting’) will return the value 5.

ArtOfTesting

Ans. Coalesce function is used to return the first, not NULL value out of the different values or expressions passed to the coalesce function as parameters. Example-

DBMS Interview Questions

Ques. What are the triggers in SQL?

CREATE TRIGGER triggerName triggerTime{Before or After} triggerEvent{Insert, Update or Delete} ON tableName FOR EACH ROW triggerBody

ArtOfTesting

Ans. Triggers are special types of stored procedures that get executed when a specified event occurs. Syntax-

DBMS Interview Questions

Ques. How can we remove orphan records from a table?

DELETE PT FROM ParentTable PT LEFT JOIN ChildTable CT ON PT.ID = CT.ID WHERE PT.ID IS NULL

For Complete List Check- ArtOfTesting

Ans. We need to create a join on the parent and child tables and then remove the rows from the child table where id IS NULL.