What is the difference between DELETE and TRUNCATE statements? Answer- DELETE Delete command to used to perform delete operation on the row of a table It is a DML command It is comparatively slower than the truncate statement Users can roll back after using it

TRUNCATE Truncate is used to delete all the rows of the table The user cannot roll back the data It is a DDL command It is comparatively faster

2. What are the different subsets of SQL Answer- There are four main Different Subsets of SQL DDL (Data Definition Language) - It consists of commands that are used to define the database system. DML (Data Manipulation Language) - These are the commands that deal with the manipulation of data that is already present in the database. DCL (Data control language)- This from its name itself suggests that it is used to control the data i.e provide permissions and other controls of the database system TCL (Transaction control language)- These commands are used to control the transaction of the database system

3. What do you mean by DBMS? Do you know how many types of DBMS systems are there? Answer- Database management (DBMS) is a software application that interacts with the user, application, and database. It helps in achieving the tasks like performing analytics, modifying the data, deleting the data, and storing the data.

There are in total four types of DBMS available Hierarchical- In a Hierarchical database, the data is structured in the format of a tree following the top-down or both up format. There is a parent-child relationship between the entities called nodes. Relational Model - This is one of the most common and widely used data models. This model is based on the concept of normalization and storing the data in the form of rows and columns in the table. The data manipulation or creation takes place with the help of SQL. Network Model- In this model entities are organized in the form of a graph in which data can be accessed from several paths. In this model, each child can have multiple parents and it caters to the need for many complex relationships like many-to-many relationships. Object-Oriented- Object-Oriented Model data is stored in the form of objects. The structure in which data is stored is called class. It defined database which is a collection of both data members and data values

4. What do you mean by table and field in SQL? Answer- Tables refers to the collection of data in an organized manner in form of rows and columns

A field refers to the number of columns inside the table

5. What are joins in SQL? Answer- A join clause is used to combine two rows from two or more tables based on the common columns. It is used to merge two or more tables or retrieve certain data from there.

There are four types of joins in SQL Inner Join- The INNER JOIN creates a new table by combining column values of two tables based upon the condition given. The query compares each row of columns of both the tables based on the condition to find which pair satisfies the given situation. When the join predicate is satisfied the matched values of rows of A and B are combined into the result row. LEFT JOIN- This join returns all the rows of the table on the left side of the join and matches rows for the table on the right side of the join. The result set will contain null if there is no matching row on the right side. The LEFT JOIN is also often called as LEFT OUTER JOIN. RIGHT JOIN - The RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the tables on the right side of the join and matching rows for the table on the left side of the join. The result set will contain null if there is no matching row on the left side. The RIGHT JOIN is also often called as RIGHT OUTER JOIN. FULL JOIN - It is a combination of results provided by LEFT JOIN and RIGHT JOIN. The result set will contain all the rows from both tables. The result will be null for the rows in which there is no matching.

6. What is the difference between CHAR and VARCHAR2 datatype in SQL? Answer - Both of them are used to store the strings but there’s one significant difference between the two

Char- Char is used to store a string of fixed length. For example, char(10) can only store a string of 10 characters.

Varchar2 - Varchar2 is used to store a string of variable length. For example, Varchar2(10) can store a string of sized 10, 2, 5, or simply any length less than 10.

7. What is a primary key? Answer - A set of attributes that can be used to uniquely identify every tuple is a primary key. There are many candidates key in a table and out of those only one can be the primary key. Simply, there is the only primary key in the table

8. What are constraints? Answer - Constraints are some rules that are used to limit the type of data that goes into the table. There can be different levels of constraints like column level or table level that apply to the table according to the use cases

Some examples of constraints are NOT NULL - NOT NULL constraint ensures that no null values are allowed to enter the table UNIQUE - It ensures that no values in the columns are the same or repeated. CHECK - This constant ensures that all values in a column satisfy a special condition DEFAULT - This constraint ensures that all the values in a column satisfy a special condition. INDEX - This constraint is used to create and retrieve data from the index quickly

9. What is the difference between SQL and MySQL Answer - SQL i.e Structured query language is a core of relational database which is used for accessing and managing databases and is based on the simple English language.

MySQL is an open-source database management system that works on many platforms. It is backed by the most popular company ORACLE and support many operating systems to perform database operations

10. What is a unique key Answer - The answer to this can be divided into four simple points It is used to uniquely identify the rows in a table Multiple values are allowed per table It can be null Duplication of values is not allowed with the unique key

11. What is a foreign key? Answer: - The answer to this can be divided into four points The foreign key is used to maintain the referential integrity between the values of two different tables A foreign key can destroy the actions that would harm the referential integrity between two tables The foreign key in the child table references the primary key in the primary table

12. What do you mean by data integrity? Answer - A data integrity means Data should be accurate Data should be consistent over the lifecycle of the program It should follow the integrity constraints to follow the business rules on data

13. What is the difference between clustered and non clustered indexes in SQL? Answer -

Clustered Index - The clustered index is faster The clustered index requires comparatively less memory for operation A table can have only one clustered index In clustered index leaf nodes are actual data itself In clusters index clustered key defines the order of data within the table

Non-Clustered Index - Non- clustered index is slower Non - clusters index requires more memory for operation A table can have multiple non clustered index In non clustered index leaf nodes are not the actual data itself rather, they only contain included columns IN a non-clustered index, the index key defines the order of data within the index

14. Write a SQL Query to return the current date Answer - In SQL, there’s a build-in function known as GetDate( ) that is used to return the current date and time stamp

Syntax - GETDATE ( )

Example

SELECT GETDATE ( )

Output 2021-03-18 16:14:18:280

15. What is denormalisation? Answer - Denormalisation is a database optimization technique in which we add redundant data to one or more tables. It is an optimization technique that we generally do after performing normalization, hence it doesn’t mean denormalisation means not doing or performing normalization

It increases the performance of the entire table as it introduces redundancy into the table.

16. What are entities and relationships? Answer - Entities- A person, place, or thing in the real world which can be distinctly identified and about which data can be stored in the database. For example in a hospital database entities can be doctors, nurses, and employees.

Relationships - A relationship is a link between different entities that has a certain form of relationship with each other. For example - the customer name is related to the customer account number which can be related to each other in many ways.

17. What is an index? Answer - Index are the ones which Allows performance tuning methods Allows faster retrieval of records from the given table Creates an entry for each of the values

18. Explain different types of indexes Answer - There are three types of indexes available Unique Index - The unique index does not allow the field to have duplicate values if the column is unique and indexed. In the case of the primary key unique index is applied automatically. Clustered Index - The index reorders the physical order of the table and searches based on a key value. The catch here is that each table can have at most one clustered index Non-Clustered Index - Non clustered index does not alter the physical address of the table and maintains a logical order of the data. Each table can have many non-clustered indexes.

19. What is normalization and what are the advantages of it? Answer - Normalisation is the process of arranging/structuring the data to avoid duplication and redundant values.

The advantages associated with normalization are More tables with smaller rows Better organization Modification is easy More compact database Faster queries and database traversal Greater flexibility Reductio of redundant and duplicate values Efficient data access Ensure consistency after modification of data

20. What is the difference between a drop and truncate commands? Answer - Drop commands completely removes the table from the database and rolling back of data is also not possible. Syntax - DROP object object_name

The truncate command removes the rows from the table but the instance of the table stays in the database. Similar to drop command changes cannot be rolled back. Syntax - TRUNCATE table table_name

21. What are the different types of normalization? Answer - There are four different types of normalisation 1NF, 2NF, 3NF, and BCNF