SQL the acronym used for the Structured Query Language. When you go for an interview related to DBA (Database Administrator), you might have to face questions from RDBMS (Relational Database Management System). There are many RDBMS application such as MySQL, MS SQL Server, SQLite, Oracle, you must be aware of before you tackle the interview. In this article, we are going to discuss all those questions frequently asked in the SQL interviews. We will start to form the basics then move to the advanced level and we will also mention some important queries often used in SQL management.
SQL Interview question
Questions: What is Data?
Answer: Data can be defined as a set of values, facts, information, and statistics collected together for references or analysis.
Questions: What is a Database?
Answer: The database can be defined as a collection of data that can be retrieved and modified.
Questions: What is SQL?
Answer: SQL stands for Structured Query Language which is a standard language used for the RDBMS (Relational Database Management System). It is basically used to communicate or interact with the database so we can perform different queries on databases.
Questions: What is DBMS?
Answer: DBMS stands for Database Management System which was initially introduced in 1960. It is used to store the data and allow us to perform queries on it such as modifications, insertions, update, deletion, etc.
Questions: Name the different types of DBMS.
- Hierarchical databases
- Network databases
- Relational databases
- Object-oriented databases
- Graph databases
- ER model databases
- Document databases
Questions: Give some Key Feature of SQL
- Non-Procedural language
- Unified language
- Common Language for all relational Databases
Questions: What is RDBMS?
Answer: Relational Database Management System (RDBMS) is a Database Management System (DBMS) which store data in Relational model. RDBMS first introduced in 1970 as an updated version of DBMS. Like DBMS, RDBMS gives us control over the database so we can create and modify the database.
Questions: How DBMS is different from RDBMS?
Answer: RDBMS store Data in the form of Tables, whereas DBMS store data in the form of files.
RDBMS support multi-users whereas DBMS support, single users.
Questions: Name some RDBMS software use SQL.
- MS SQL server
Questions: What is a Table in SQL?
Answer: A table is a collection of rows and columns which have different attributes stored in it.
Questions: What Does a JOIN clause do in SQL?
Answer: The JOIN clause used to combine rows from two or more than two tables on the basis of the related column.
Questions: Name the different type of JOINS in SQL
- INNER JOIN
- RIGHT JOIN
- LEFT JOIN
- FULL JOIN
Questions: How SQL is different from a programming language?
Answer: A SQL is a Query language which is used to manipulate the data stored in the database or to create a database, and the programming language is used by the developers to instruct a computer to perform an action in order to solve the problems.
There are many features and operation that a programming language offer that you won’t get in the SQL.
Questions: Name the different Datatypes of SQL.
- Unicode Character
Questions: How the VARCHAR is different from the CHAR?
- A CHAR is used to store a fixed length of string whereas a VARCHAR is used to store a variable length of a string.
- The CHAR is faster than VARCHAR.
- A CHAR uses static memory allocation, whereas a VARCHAR uses Dynamic memory allocation.
Questions: Name the various DBMS languages.
- DDL (Data Definition Language)
- DML (Data Manipulation Language)
- DCL (Data Control Language)
- DQL (Data Query Language)
Questions: Define DDL and name all the task performs by DDL.
Answer: DDL stands for Data Definition Language. It is a set of SQL commands which is used to create, modify, and delete the database structure but not the data.
Task performed by DDL
- CREATE (use to create objects in the database)
- ALTER (use to alter the database)
- DROP (use to delete the rows and column from the database)
- TRUNCATE (it is used to delete all the rows and columns from the database)
- RENAME (it is used to rename the objects in the database)
Questions: Explain DML and name its tasks.
Answer: DML stands for Data Manipulation Language and it is an area of SQL that allow changing the data within a database.
- EXPLAIN PLAN
- LOCK TABLE
Questions: How DELETE clause is different from TRUNCATE?
Answer: A TRUNCATE is used to delete all the rows and columns or whole table altogether whereas with a DELETE clause you can delete a specific row using the WHERE clause.
Questions: Explain DCL and name its tasks.
Answer: Data Control Language (DCL) is a component of the SQL statement that controls access of data to the database. The DCL statements often group with DML statements
Questions: What is a DBA?
Answer: The DBA stands for the Database Administrator and it is a person who is responsible for the design, construction, and maintenance of a database.
Questions: Name the various type of indexes used on index sequential.
- Ordered index
- Hashed Index
Questions: What is the VIEW?
Answer: VIEW is a special clause use in SQL which is used to create a virtual table and occupies no space.
CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE CONDITION;
Questions: What is the primary key?
Answer: A primary key is a constraint or a column of a table which all values are unique and contain no NULL value.
Questions: What are the constraints?
Answer: When we set some limit on the Data types of the created table those limits are known as constraints.
Types of constraints
- NOT NULL
- PRIMARY KEY
- FOREIGN KEY
Questions: What is an Entity?
Answer: An entity is a person, place, thing or concept about which information is recorded.
Questions: What are the Attributes in DBMS?
Answer: The property that characterizes an entity or entity sets is called its attributes.
Questions: What are the keys?
Answer: A Key in SQL is an attribute or a set of attributes that identifies a tuple in a relation. Keys are defined in a tuple to access or sequence the stored data in an efficient way.
Questions: Name the Different type of keys.
- Super Key
- Candidate key
- Primary Key
- Composite key
- Foreign key
- Alternate key
Questions: What is a Foreign Key?
Answer: A foreign key is an attribute of a table which values match a primary key of another table.
Questions: What is HASHING?
Answer: It is a technique in which a given key field value is converted into an address of storage location by applying some operation on it.
Questions: Name the Different Techniques of Hashing
- Method of Division
- Division Reminder Method
- Midsquare Method
- Truncation Method
- Shifting Method
Questions: What are Domain Constraints?
Answer: Some attributes have some specific values in the real-world scenario for those we use Domain Constraint.
For e.g. the age of a person could not be a negative number it will always positive.
Questions: What is Normalization?
Answer: Normalization is a method of minimizing or breaking down the complexity of a table structure into a simple table structure with the help of some certain rules. It is used to reduce the redundancy in a table and remove the problems of inconsistency and disk space usage.
Questions: What is Data Mining?
Answer: A data mining is a process where potentially useful and previously unknown data or set of information is extracted from a large volume of Data.
Questions: Name the techniques of Data Mining
- Association of Rules
- Fuzzy Logic classification
- Decision Tree
- Neural Network
- Genetic Algorithms
Questions: What is a transaction?
Answer: A transaction can be defined as a group of tasks performed on a database. A transaction is a very small unit of the program and it may contain several low-level tasks.
Questions: Name all the properties of the Transaction
Answer: The transaction has four basic properties which are known as ASID
Questions: Define Atomicity property of Transaction.
Answer: A Transaction is an atomic unit of processing which means a transaction must be treated as an atomic unit, which is either perform all operations or none.
Questions: Define the durability property of Transaction.
Answer: The Durability property of transaction states that the changes applied to the database by a committed transaction must persist in the database. The changes must not be lost because of any failure.
Questions: What is the default sorting method of ORDER BY clause?
Answer: By default ORDER BY following the ASC or Ascending sorting.
Questions: What is the difference between BETWEEN and IN clause in SQL?
Answer: The BETWEEN clause is used to query over a range of sets whereas the IN clause is used to query for some specific sets inside the parenthesis.
E.g for BETWEEN and IN
SELECT * FROM Employee WHERE emp_id BETWEEN 20 AND 30; SELECT * FROM Employee WHERE emp_id IN (30,34,37,38);
Questions: Write an SQL query to select all employees details whose name start from K
Answer: SELECT * FROM Employees
WHERE emp_name like ‘A%’;
Questions: What is the difference between a NULL value and Zero?
Answer: A NULL Value is used to represent those values which are not assigned or unavailable whereas Zero is an Integer which possesses a value.
Questions: Write a query to count all the record in the table student.
Answer: SELECT COUNT(*)
Questions: Name all the Operators in the SQL.
- Arithmetic Operators
- Logical Operators
- Comparison operators
Questions: Write a query to show all the distinct city from the table employees.
Answer: SELECT DISTINCT city
Questions: Name all the string functions in SQL.
Questions: Write a query to add a column ‘Home_City ’ to a table students
Answer: ALTER TABLE students ADD(Home_City);
Questions: Write a query and change the header name student_id of table student with a temporary name id.
Answer: SELECT student_id AS id