Q1. What are the different subsets of SQL?
DDL (Data Definition Language) – It allows you to perform various operations on the database such as CREATE, ALTER and DELETE objects.
DML ( Data Manipulation Language) – It allows you to access and manipulate data. It helps you to insert, update, delete and retrieve data from the database.
DCL ( Data Control Language) – It allows you to control access to the database. Example – Grant, Revoke access permissions.
Q2. What do you mean by DBMS? What are its different types?
A Database Management System (DBMS) is a software application that interacts with the user, applications and the database itself to capture and analyze data.
A DBMS allows a user to interact with the database. The data stored in the database can be modified, retrieved and deleted and can be of any type like strings, numbers, images etc.
There are two types of DBMS:
Relational Database Management System: The data is stored in relations (tables). Example – MySQL.
Non-Relational Database Management System: There is no concept of relations, tuples and attributes. Example – Mongo
Q3. What do you mean by table and field in SQL?
A table refers to a collection of data in an organised manner in form of rows and columns. A field refers to the number of columns in a table. For example:
Field: Stu Id, Stu Name, Stu Marks
Q4. What are joins in SQL?
A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there. There are 4 joins in SQL namely:
Q5. What is the difference between CHAR and VARCHAR2 datatype in SQL?
Both Char and Varchar2 are used for characters datatype but varchar2 is used for character strings of variable length whereas Char is used for strings of fixed length. For example, char(10) can only store 10 characters and will not be able to store a string of any other length whereas varchar2(10) can store any length i.e 6,8,2 in this variable.
Q6. What is a Primary key?
Table – Primary key is a column (or collection of columns) or a set of columns that uniquely identifies each row in the table.
Uniquely identifies a single row in the table
Null values not allowed
Example- In the Student table, Stu_ID is the primary key.
Q7. What are Constraints?
Constraints are used to specify the limit on the data type of the table. It can be specified while creating or altering the table statement. The sample of constraints are:
Q8. What is the difference between DELETE and TRUNCATE statements?
DELETE vs TRUNCATE
Delete command is used to delete a row in a table. Truncate is used to delete all the rows from a table.
You can rollback data after using delete statement. You cannot rollback data.
It is a DML command. It is a DDL command.
It is slower than truncate statement. It is faster.
Q9. What is a Unique key?
Uniquely identifies a single row in the table.
Multiple values allowed per table.
Null values allowed.
Q10. What is a Foreign key?
Foreign key maintains referential integrity by enforcing a link between the data in two tables.
The foreign key in the child table references the primary key in the parent table.
The foreign key constraint prevents actions that would destroy links between the child and parent tables.
Q11. What are Entities and Relationships?
Entities: A person, place, or thing in the real world about which data can be stored in a database. Tables store data that represents one type of entity. For example – A bank database has a customer table to store customer information. Customer table stores this information as a set of attributes (columns within the table) for each customer.
Relationships: Relation or links between entities that have something to do with each other. For example – The customer name is related to the customer account number and contact information, which might be in the same table. There can also be relationships between separate tables (for example, customer to accounts).
Q12. What is Normalization and what are the advantages of it?
Normalization is the process of organizing data to avoid duplication and redundancy. Some of the advantages are:
Better Database organization
More Tables with smaller rows
Efficient data access
Greater Flexibility for Queries
Quickly find the information
Easier to implement Security
Allows easy modification
Reduction of redundant and duplicate data
More Compact Database
Ensure Consistent data after modification
Q13. What is the difference between DROP and TRUNCATE commands?
DROP command removes a table and it cannot be rolled back from the database whereas TRUNCATE command removes all the rows from the table.
Q14. What is ACID property in a database?
ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data transactions are processed reliably in a database system.
Atomicity: Atomicity refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.
Consistency: Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.
Isolation: The main goal of isolation is concurrency control.
Durability: Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.
Q15. What do you mean by “Trigger” in SQL?
Trigger in SQL is are a special type of stored procedures that are defined to execute automatically in place or after data modifications. It allows you to execute a batch of code when an insert, update or any other query is executed against a specific table.
Q16. Are NULL values same as that of zero or a blank space?
A NULL value is not at all same as that of zero or a blank space. NULL value represents a value which is unavailable, unknown, assigned or not applicable whereas a zero is a number and blank space is a character.
Q17. What is the difference between cross join and natural join?
The cross join produces the cross product or Cartesian product of two tables whereas the natural join is based on all the columns having the same name and data types in both the tables.
Q18. What is subquery in SQL?
A subquery is a query inside another query where a query is defined to retrieve data or information back from the database. In a subquery, the outer query is called as the main query whereas the inner query is called subquery. Subqueries are always executed first and the result of the subquery is passed on to the main query. It can be nested inside a SELECT, UPDATE or any other query. A subquery can also use any comparison operators such as >,< or =.
Q19. Write a SQL query to get the third highest salary of an employee from employee_table?
SELECT TOP 1 salary
SELECT TOP 3 salary
ORDER BY salary DESC) AS emp
ORDER BY salary ASC;
Q20. What is the need of MERGE statement?
This statement allows conditional update or insertion of data into a table. It performs an UPDATE if a row exists, or an INSERT if the row does not exist.
Q21. What is the difference between ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?
HAVING clause can be used only with SELECT statement. It is usually used in a GROUP BY clause and whenever GROUP BY is not used, HAVING behaves like a WHERE clause.
Having Clause is only used with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query.
Q22. What is an ALIAS command?
ALIAS name can be given to any table or a column. This alias name can be referred in WHERE clause to identify a particular table or a column.
Select emp.empID, dept.Result from employee emp, department as dept where emp.empID=dept.empID
In the above example, emp refers to alias name for employee table and dept refers to alias name for department table.
Q23. Name the operator which is used in the query for pattern matching?
LIKE operator is used for pattern matching, and it can be used as -.
% – It matches zero or more characters.
For example- select * from students where studentname like ‘a%’
_ (Underscore) – it matches exactly one character.
For example- select * from student where studentname like ‘abc_’
Q24. How can you select unique records from a table?
You can select unique records from a table by using the DISTINCT keyword.
Select DISTINCT studentID from Student
Using this command, it will print unique student id from the table Student.
Q25. What is a View?
A view is a virtual table which consists of a subset of data contained in a table. Since views are not present, it takes less space to store. View can have data of one or more tables combined and it depends on the relationship.
Q26. What are Views used for?
A view refers to a logical snapshot based on a table or another view. It is used for the following reasons:
Restricting access to data.
Making complex queries simple.
Ensuring data independence.
Providing different views of same data.
Q27. What is a Stored Procedure?
A Stored Procedure is a function which consists of many SQL statements to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required which saves time and avoid writing code again and again.
Q28. List some advantages and disadvantages of Stored Procedure?
A Stored Procedure can be used as a modular programming which means create once, store and call for several times whenever it is required. This supports faster execution. It also reduces network traffic and provides better security to the data.
Q29. What is a foreign key?
A foreign key is one table which can be related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.
Q30. What is Denormalization.
Denormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables
Q31. What is the difference between Cluster and Non-Cluster Index?
Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index.
A nonclustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching
Q32. What is Online Transaction Processing (OLTP)?
Online Transaction Processing (OLTP) manages transaction based applications which can be used for data entry, data retrieval and data processing. OLTP makes data management simple and efficient. Unlike OLAP systems goal of OLTP systems is serving real-time transactions.
Example – Bank Transactions on a daily basis
Q33. How can you create an empty table from an existing table?
Example will be -.
Select * into studentcopy from student where 1=2
Here, we are copying student table to another table with the same structure with no rows copied.
Q34. How to fetch common records from two tables?
Common records result set can be achieved by -.
Select studentID from student INTERSECT Select StudentID from Exam