mssql What are some database interview questions that I can Practice

[Mahesh]

I am preparing for job interview. Can you guys please help

Hi guys, these are some of the RBMS Interview Questions by artoftesting

 

Ques.4. What is the difference between DBMS and RDBMS?
Ans. The primary difference between DBMS and RDBMS is, in RDBMS we have relations between the tables of the database.
Whereas in DBMS there is no relation between the tables (data may even be stored in files).

RDBMS has primary keys and data is stored in tables. DBMS has no concept of primary keys with data stored in navigational or hierarchical form.

RDBMS defines integrity constraints in order to follow ACID properties. While DBMS doesn’t follow ACID properties.

Ques.5. What is a table?
Ans. A table is a database object that is used for storing related records in the form of rows and columns.

Ques.6. What is a field in a table?
Ans. A field is an entity used for storing a particular type of data within a table like numbers, characters, dates, etc.

Ques.7. What is a tuple, record, or row in a table?
Ans. A tuple is a single record or single row in a table. For example, in the case of a student table, the data corresponding to a student will be called a tuple or record of that student.

Ques.8. What is SQL?
Ans. SQL stands for Structured Query Language, It is a language used for creating, storing, fetching, and updating data and database objects in RDBMS.

Ques.9. What are the different types of SQL commands?
Ans. SQL commands are the set of commands used to communicate and manage the data present in the database. The different type of SQL commands are-

1. DDL – Data Definition Language
2. DML – Data Manipulation Language
3. DCL – Data Control Language
4. TCL – Transactional Control Language

Ques.10. Explain DDL commands. What are the different DDL commands in SQL?
Ans. DDL refers to Data Definition Language. The DDL commands are used to define or alter the structure of the database. The different DDL commands are-

• CREATE – Used to create a table in the DB
• DROP – Drops the table from the DB
• ALTER – Alters the structure of the DB
• TRUNCATE – Deletes all the records from the DB but not its database structure
• RENAME – Renames a DB object

Ques.11. Explain DML commands. What are the different DML commands in SQL?
Ans. DML refers to Data Manipulation Language. These commands are used for managing data present in the database. Some of the DML commands are – select, insert, update, delete, etc.

Ques.12. Explain DCL commands. What are the different DCL commands in SQL?
Ans. DCL refers to Data Control Language. These commands are used to create roles, grant permission, and control access to the database objects. The three DCL commands are-

• GRANT – Grants permission to a database user.
• REVOKE – Removes access privileges from a user-provided with the GRANT command.
• Deny – Explicitly prevents a user from receiving particular permission(e.g. preventing a particular user belonging to a group to receive the access controls.

Ques.13. Explain TCL commands. What are the different TCL commands in SQL?
Ans. TCL refers to Transaction Control Language. These commands are used to manage the changes made by DML statements. These are used to process a group of SQL statements comprising a logical unit. The three TCL commands are-

• COMMIT – Commit write the changes to the database
• SAVEPOINT – Savepoints are the breakpoints, these divide the transaction into smaller logical units which could be further roll-backed.
• ROLLBACK – Rollbacks are used to restore the database since the last commit.

Ques.14. What are SQL constraints?
Ans. SQL constraints are the set of rules that impose some restrictions while inserting, deleting, or updating the data in the databases. In SQL, we have both column level as well as table level constraints which are applied at columns and tables respectively. Some of the constraints in SQL are – Primary Key, Foreign Key, Unique Key, Not NULL, DEFAULT, CHECK, and Index constraint.

Ques.15. What is a Unique constraint?
Ans. A unique constraint is used to ensure that a field or column will have only a unique value (no duplication).

Ques.16. What is a Primary Key?
Ans. A primary key is a column or a combination of columns that uniquely identifies a record in the database. A primary key can only have unique and not NULL values and there can be only one primary key in a table.

Ques.17. What is the difference between a unique key and a primary key?
Ans. A unique key allows null value (although only one) but a primary key doesn’t allow null values. A table can have more than one unique keys columns while there can be only one primary key. A unique key column creates a non-clustered index whereas the primary key creates a clustered index on the column.

Ques.18. What is a composite key?
Ans. A composite key is a primary key with multiple columns as in the case of some tables a single field might not guarantee unique and not null values. So a combination of multiple fields is taken as the primary key.

Ques.19. What is a NULL value?
Ans. A NULL value in SQL is an unknown or blank value. Since NULL is an unknown value so the NULL value cannot be compared with another NULL value. Hence we cannot use the ‘=’ operator in where condition with NULL. For this, we have an IS NULL clause that checks if the value in the field is NULL or not.

Ques.20. What is a Not NULL constraint?
Ans. A Not NULL constraint is used for ensuring that the value in the field cannot be NULL.

Ques.21. What is a Foreign Key?
Ans. A foreign key is used for enforcing referential integrity in which a field marked as a foreign key in one table is linked with a primary key of another table. With this referential integrity, we can have only the data in the foreign key which matches the data in the primary key of the other table.

Ques.22. What is a Check constraint?
Ans. A check constraint is used to limit the value entered in a field. For example, we can ensure that field ‘Salary’ can only have a value greater than 1000 using check constraint.

CREATE TABLE EMP_SALARY(
EmpID int NOT NULL, 
NAME VARCHAR (30) NOT NULL, 
Salary INT CHECK (Salary > 1000), 
PRIMARY KEY (EmpID)
);

Ques.23. What is a Default constraint?
Ans. A Default constraint is used for providing a default value to a column when no value is supplied at the time of insertion of record in the database.

Ques.24. What is a clustered index?
Ans. A clustered index defines the order in which data is physically stored in the database tables. A table can have only one clustered index.

Ques.25. What is a non-clustered index?
Ans. In the case of a non-clustered index, the data and the indices are stored in different locations. A non-clustered index has a jump table containing key-values with pointers pointing to the location containing the actual rows. There can be multiple clustered indexes in a table.

Ques.26. What is the difference between delete, truncate and drop command?
Ans. The difference between the Delete, Truncate and Drop command is –

• Delete command is a DML command. It removes rows from a table based on the condition specified in the where clause, being a DML statement we can rollback changes made by the delete command.
• Truncate is a DDL command. It removes all the rows from the table and also frees the space held, unlike the delete command. It takes a lock on the table while the delete command takes a lock on rows of the table.
• Drop is a DDL command. It removes the complete data along with the table structure (unlike the truncate command that removes only the rows).

Ques.27. What are the different types of joins in SQL?
Ans. Joins are used to combine records from multiple tables. The different types of joins in SQL are-

1. Inner Join – To fetch rows from two tables having matching data in the specified columns of both the tables.
   SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
    
2. Left Join – To fetch all rows from the left table and matching rows of the right table
   SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
    
3. Right Join – To fetch all rows from right table and matching rows of the left table
   SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
    
4. Full Outer Join – To fetch all rows of the left table and all rows of right table
   SELECT * FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
    
5. Self Join – Joining a table to itself, for referencing its own data
   SELECT * FROM TABLE1 T1, TABLE1 T2 WHERE T1.columnA = T2.columnB;

Ques.28. What is the difference between cross join and full outer join?
Ans. 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.