Question:- What do you mean by data integrity?
Answer:- Data integrity is the assurance of accuracy and consistency of data over its whole life cycle. It is a critical aspect of the design, implementation, and usage of systems that store, process, or retrieve data. Data integrity also defines integrity constraints for enforcing business rules on data when it is entered into a database or application.
Question:- What is a data warehouse?
Answer:- A data warehouse is a large store of accumulated data, from a wide range of sources, within an organization. The data helps drive business decisions.
Question:- How would you find the second highest salary from the following table?
Answer:- select * from employee select max(e_salary) from employee where e_salary not in (select max(e_salary) from employee)
Question:- Why is the FLOOR function used in SQL Server?
Answer:- The FLOOR() function helps to find the largest integer value to a given number, which can be an equal or lesser number.
Question:- State the differences between clustered and non-clustered indexes
Answer:- • Clustered Index: It is used to sort the rows of data by their key values. A clustered index is like the contents of a phone book. We can open the book at “David” (for “David, Thompson”) and find information for all Davids right next to each other. Since the data is located next to each other, it helps a lot in fetching the data based on range-based queries. A clustered index is actually related to how the data is stored; only one clustered index is possible per table. • Non-clustered Index: It stores data at one location and indexes at another location. The index has pointers that point to the location of the data. As the indexes in a non-clustered index are stored in a different place, there can be many non-clustered indexes for a table.
Question:- What do you know about CDC in SQL Server?
Answer:- CDC refers to change data capture. It captures recent INSERT, DELETE, and UPDATE activity applied to SQL Server tables. It records changes to SQL Server tables in a compatible format.
Question:- What is the difference between SQL and MySQL?
Answer:- Now Let’s compare the difference between SQL and MySQL. SQL • It is a structured query language used in a database • It is used for query and operating database system • It is always the same • It supports only a single storage engine • The server is independent MySQL • It is a database management system • It allows data handling, storing, and modifying in an organized manner • It keeps updating • It supports multiple storage engines • During backup sessions, the server blocks the database
Question:- State the differences between SQL and PL/SQL
Answer:- SQL • It is a database structured query language • It is an individual query that is used to execute DML and DDL commands • It is a declarative and data-oriented language • It is mainly used for data manipulation • It provides interaction with the database server • It cannot contain PL/SQL code PL/SQL • It is a programming language for a database that uses SQL • It is a block of codes that are used to write the entire procedure or a function • It is a procedural and application-oriented language • It is used for creating applications • It does not provide interaction with the database server • It can contain SQL because it is an extension of SQL
Question:- What is the ACID property in a database?
Answer:- The full form of ACID is atomicity, consistency, isolation, and durability. ACID properties are used to check the reliability of transactions. • Atomicity refers to completed or failed transactions, where a transaction refers to a single logical operation on data. This implies that if any aspect of a transaction fails, the whole transaction fails and the database state remains unchanged. • Consistency means that the data meets all validity guidelines. The transaction never leaves the database without finishing its state. • Concurrency management is the primary objective of isolation. • Durability ensures that once a transaction is committed, it will occur regardless of what happens in between such as a power outage, fire, or some other kind of disturbance.
Question:- What is the need for group functions in SQL?
Answer:- Group functions operate on a series of rows and return a single result for each group. COUNT(), MAX(), MIN(), SUM(), AVG(), and VARIANCE() are some of the most widely used group functions.
Question:- What is AUTO_INCREMENT?
Answer:- AUTO_INCREMENT is used in SQL to automatically generate a unique number whenever a new record is inserted into a table. Since the primary key is unique for each record, this primary field is added as the AUTO_INCREMENT field so that it is incremented when a new record is inserted. The AUTO-INCREMENT value starts from 1 and is incremented by 1 whenever a new record is inserted. Syntax: CREATE TABLE Employee( Employee_id int NOT NULL AUTO-INCREMENT, Employee_name varchar(255) NOT NULL, Employee_designation varchar(255) Age int, PRIMARY KEY (Employee_id) )
Question:- What is the difference between DELETE and TRUNCATE commands?
Answer:- • DELETE: This query is used to delete or remove one or more existing tables. • TRUNCATE: This statement deletes all the data from inside a table. The difference between DELETE and TRUNCATE commands are as follows: • TRUNCATE is a DDL command, and DELETE is a DML command. • With TRUNCATE, we cannot really execute and trigger, while with DELETE, we can accomplish a trigger. • If a table is referenced by foreign key constraints, then TRUNCATE will not work. So, if we have a foreign key, then we have to use the DELETE command. The syntax for the DELETE command: DELETE FROM table_name [WHERE condition]; Example: select * from stu delete from stu where s_name=’Bob’ The syntax for the TRUNCATE command: TRUNCATE TABLE Table_name; Example: select * from stu1 truncate table stu1 This deletes all the records from a table.
Question:- What is the difference between DROP and TRUNCATE commands?
Answer:- If a table is dropped, all things associated with that table are dropped as well. This includes the relationships defined on the table with other tables, access privileges, and grants that the table has, as well as the integrity checks and constraints. To create and use the table again in its original form, all the elements associated with the table need to be redefined. However, if a table is truncated, there are no such problems as mentioned above. The table retains its original structure.
Question:- What is a “TRIGGER” in SQL?
Answer:- The trigger can be defined as an automatic process that happens when an event occurs in the database server. It helps to maintain the integrity of the table. The trigger is activated when the commands, such as insert, update, and delete, are given. The syntax used to generate the trigger function is: CREATE TRIGGER trigger_name