Answer:- Single-column Indexes: A single-column index is created for only one column of a table. Syntax: CREATE INDEX index_name ON table_name(column_name); Composite-column Indexes: A composite-column index is created for two or more columns of a table. Syntax: CREATE INDEX index_name ON table_name (column1, column2) Unique Indexes: A unique index is used for maintaining the data integrity of a table. A unique index does not allow multiple values to be inserted into the table. Syntax: CREATE UNIQUE INDEX index ON table_name(column_name)

Answer:- Entities: An entity can be a person, place, thing, or any identifiable object for which data can be stored in a database. For example, in a company’s database, employees, projects, salaries, etc., can be referred to as entities. Relationships: A relationship between entities can be referred to as a connection between two tables or entities. For example, in a college database, the student entity and the department entities are associated with each other. That ends the section of basic interview questions. Let us now move on to the next section of intermediate interview questions.

Answer:- SQL operators are the special keywords or characters that perform specific operations. They are also used in SQL queries. These operators can be used within the WHERE clause of SQL commands. Based on the specified condition, SQL operators filter the data. The SQL operators can be categorized into the following types: • Arithmetic Operators: For mathematical operations on numerical data • addition (+) • subtraction (-) • multiplication (*) • division (/) • remainder/modulus (%) • Logical Operators: For evaluating the expressions and return results in True or False • ALL • AND • ANY • ISNULL • EXISTS • BETWEEN • IN • LIKE • NOT • OR • UNIQUE • Comparison Operators: For comparisons of two values and checking whether they are the same or not • equal to (=) • not equal to (!= or <>) • less than (<), • greater than (>) • less than or equal to (<=) • greater than or equal to (>=) • not less than (!<) • not greater than (!>) • Bitwise Operators: For bit manipulations between two expressions of integer type. It first performs conversion of integers into binary bits and then applied operators • AND (& symbol) • OR (|, ^) • NOT (~) • Compound Operators: For operations on a variable before setting the variable’s result to the operation’s result • Add equals (+=) • subtract equals (-=) • multiply equals (*=) • divide equals (/=) • modulo equals (%=) • String Operators: For concatenation and pattern matching of strings • + (String concatenation) • += (String concatenation assignment) • % (Wildcard) • [] (Character(s) matches) • [^] (Character(s) not to match) • _ (Wildcard match one character)

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.

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.

Answer:- select * from employee select max(e_salary) from employee where e_salary not in (select max(e_salary) from employee)

Answer:- The FLOOR() function helps to find the largest integer value to a given number, which can be an equal or lesser number.

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.

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.

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

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

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.

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.

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) )