SQL- QUESTIONS AND ANSWERS

What is a Database?

• The Database is a collection of data. It supports storage and manipulates the data.
• An example of a Database is an Online Telephone Dictionary.

What is DBMS?

• DBMS is a software that is designed for storing and retrieving data.
• DBMS used in Airlines, Banking, and other Industries.

What is RDBMS?

• RDBMS is a software system that is stored data in a tabular format.
• An example of RDBMS is MySQL, Oracle, SQL Server.

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.
• 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, and REVOKE access permissions.

Difference between DBMS and RDBMS?

What is SQL?

SQL is a standard language used to perform tasks such as insert, update, and delete data from the database.

Difference between SQL Server and MYSQL?

Difference between Char and NChar?

Difference between Varchar and NVarchar?

What is Group By in SQL?

• Group By is used to group the rows that have the same values.
• Group By statement is used in aggregate functions like COUNT, MAX, MIN, SUM, AVG.

What is Order By in SQL?

Order By statement is used to sort data in ascending or descending order.

Difference between Having and Where Clause?

HAVING CLAUSE

• WHERE keyword could not be used with aggregate functions. So, we can use the HAVING Clause
• It is used in only the SELECT Query.
• It is used to filter values from a group.
• It checks the conditions after the aggregation takes place.
• After the GROUP BY Clause HAVING Clause is used.

WHERE CLAUSE

• WHERE Clause used in any SELECT query with UPDATE and DELETE Clause.
• It is used to filter rows.
• It checks conditions before the aggregation takes place.
• Before GROUP BY Clause WHERE Clause is used.

What are some common clauses used with SELECT query in SQL?

• SELECT: Retrieve column from the table.
• FROM: Retrieve the table.
• WHERE: It is used to provide conditions in the query.
• ORDER BY: It is used to sort the result set.
• GROUP BY: GROUP BY is used to group the rows that have the same values.
• HAVING: HAVING Clause is used to check conditions after the aggregation takes place
• TOP CLAUSE: SELECT TOP Clause is used to specify the number of records to return.

What are the TCL commands in SQL?

Transaction Control Language used to manage transactions in the database.

COMMIT COMMAND

• COMMIT Command is used to permanently save any transaction in the database.
• When we use DML command, the changes by this command are not permanent, until the current session closed. These changes made by the COMMIT Command.
• Syntax: COMMIT;

ROLLBACK COMMAND

• ROLLBACK Command to rollback changes, if they were not committed using the COMMIT command.
• It is also used with SAVEPOINT Command.
• The UPDATE Command to make some changes into the database, and realize that those changes were not required, then we can use the ROLLBACK command.
• Syntax: ROLLBACK TO Savepoint_Name;

SAVEPOINT COMMAND

• SAVEPOINT Command is used to temporarily save a transaction so that you can ROLLBACK to that point.
• Syntax: SAVEPOINT Savepoint_Name;

What are the Aggregate functions in SQL?

The aggregate function performs a calculation on a set of values and returns a single value.

• AVG- Calculate the average of the given value.
• COUNT- It returns the total number of records.
• MIN- It returns the minimum value in a set of values.
• MAX- It returns the maximum value in a set of values.
• SUM- Calculate the sum of the value.

What is the primary key?

• The primary key uniquely identifies each record in a table.
• By using the primary key, we create a relationship with another table.

What is a unique key?

• By using the unique key, uniquely identifies each record in the database.
• Example: Aadhar card number (UID).

What is the foreign key?

Foreign Key in a database table that is Primary key in another table.

Difference between Primary key and Unique key?

• The primary key does not allow NULL value. Whereas the Unique key allows only one NULL value.
• In the table, only one primary key and the multiple unique keys allow.

What is Composite key or Composite Primary Key in SQL Server?

The composite key refers to more than one column used to specify the primary key of a table.

What is a join? and explain types of join?

• A Join is used to combine two tables, it depends on the relationship between tables.

Inner Join: Inner join displays the matching record from two tables.

Left Join: Left join displays the left side table and only matching the record of the right-side table.

Right Join: Right join display the right-side table and only matching the record of the left side table.

If a row in the right table does not have any matching rows from the left tables, the column of the table in the result set will have nulls.

Full Outer Join: Full outer join displays the matching or non-matching record of the table.

Self-Join: In the Self-join table joined with itself.

Cross Join: Cross join produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table.

Cross joins are used to return every combination of rows from two tables, this sometimes called a Cartension product.

Difference between Union and Union All?

• Union removes duplicate records. And Union All display duplicate record.
• Union displays the record in sorted ascending order whereas Union All displays records on non-sorted orders.
• The performance of the Union All better than the Union.

What is Normalization? What are all the different types of Normalizations?

• Normalization is a process of organizing data in the database.
• Normalization used to remove data redundancy and maintain data integrity.

Types of Normalization:

First Normal Form(1NF):

• First Normal Form removes duplicate data and Breaks into smaller logical pieces.

Second Normal Form(2NF):

• The table is a must in the First Normal Form.
• All column data should depend on the primary key.

Third Normal Form(3NF):

• It is in a Second Normal Form and it contains no transitive dependencies.

Boyce-Codd Normal Form (BCNF):

• It is in a Third Normal Form. It based on functional dependency.
• (FD) X→Y
• X is a super key of every functional dependency.

What is Denormalization?

• Denormalization is a database optimization technique.
• It is a process of adding redundant data for better performance.
• In the real world, the database is very huge and it will a lot of records. However, retrieving data from the normalization database can be slower. When we use Denormalization it increases the performance of the query.

What is a View?

• The view is a virtual table based on the result of an SQL statement.
• A View can be used to reduce the complexity of database schema.
• Views are used for security purposes in the database.

Explain types of views?

• USER DEFINED VIEW:- In the user-defined view, we can create, alter, rename, and drop the view.
• SYSTEM DEFINED VIEW:- System defined view divided into two parts:

Information schema:

• Information schema used to display information about the database.
• There are 21 information schemas in the system.
• Example: select * from INFORMATION__SCHEMA TABLES

Catalog view:

• Catalog view describes information about the database. These start with sys.
• Example: select * from sys.databases

What are Indexes and why it makes our search faster?

• Indexes are used to retrieve data from the database. By using B-Tree make our search faster.
• Example: If data from 1 to 100 and we want to search 80.

The B-Tree divided data into two section that is 1–50 and 51–100. Data 80 is greater than 50 therefore, the first section 1–50 is skipped only 51 to 100 is searched and gets the data.

What are the types of Indexes?

CLUSTERED INDEX

• In Clustered Index leaf node has actual data.
• The Clustered index will be automatically created when the primary key is defined.
• Clustered Indexes are the unique index that uses the primary key to organize the data within the table.
• Clustered Index Seek scanning a particular range of rows from a clustered index.

NON-CLUSTERED INDEX

• In the Non-Clustered Index, a leaf node does not contain any data.
• Non-Clustered Index used to increase the speed of queries.

COLUMN STORE INDEX

• Column store index used in the OLAP system.
• This type f index stored index data in the column-based format.
• It is mainly used for improving query performance.

What is a Table Scan?

Records are searched sequentially without an index it is called table scan.

What is a Clustered Index Scan?

A clustered index scan retrieves all the rows from the table.

What is the difference between Table Scan and Clustered Index Scan?

• When the table scan occurs then SQL Server reads all the rows and columns into memory.
• When the Index scan occurs, it’s going to read to all the rows and only the columns in the index.

What is a Clustered Index Seek?

• Clustered Index seeks to retrieve selective rows from the table.
• Clustered Index Seek has actual data.

What is an Index Structure?

Records are scan by the B-Tree Structure it is called index structure.

What is the difference between the Clustered and Non-Clustered Index?

What are the Different Types of Relationships in SQL?

ONE TO ONE

• In a One-to-One Relationship, one record in a table is associated with one and only one record in another table.
• Example: In a school database, each student has only one student ID, and each student ID is assigned to only one person.

ONE TO MANY

• In a One-to-Many Relationship, one record in a table can be associated with one or more records in another table.
• Example: Each Customer can have many Sales Orders.

MANY TO MANY

• A Many-to-Many Relationship occurs when multiple records in a table are associated with multiple records in another table.
• Example: Customers can purchase various Products, and Products can be purchased by many Customers.

What is a Query?

A Query is a request for data or information from a database to operate.

What are a Subquery and Co-related query?

SUBQUERY: A subquery is a query under query. First, execute inner query and result pass to outer query then outer query executes.

CO-RELATED QUERY: The inner query needs an outer query value. Then the result pass to the outer query.

What are the types of subquery?

SINGLE ROW SUBQUERY

• It returns zero or one row.
• It is used with a comparison operator in a WHERE or HAVING clause.

MULTIPLE ROW SUBQUERY

• It returns one or more rows but only one column returns.
• It is used with an IN, ANY, or ALL clause.

MULTIPLE COLUMN SUBQUERIES

• It returns one or more columns used in the FROM clause.

CORRELATED SUBQUERIES

• It depends upon the outer query and cannot execute on its own.
• NONCORRELATED SUBQUERY both outer query and inner query are independent of each other.

NESTED SUBQUERIES

• Subqueries are placed within another subquery.

What is a stored procedure? Why it is used?

• A stored procedure is nothing but a group of SQL statements compiled into a single execution plan.
• Create once and call it N number of times.
• It reduces network traffic.

Explain types of parameters in Stored Procedure?

IN

• This is the Default Parameter for the procedure.
• It always receives the values from the calling program.

OUT

• This parameter always sends the values to the calling program.

IN OUT

• This parameter performs both operations.
• It receives value from as well as sends the values to the calling program.

What is the Recursive Stored Procedure?

Recursive stored procedure refers to a stored procedure that calls by itself until it reaches some boundary condition.

Advantages and Disadvantages of Stored Procedure?

ADVANTAGES

• It reduces network traffic.
• It can be used as a security mechanism.
• It allows for faster execution.
• A Stored Procedure can be used as modular programming which means create once, store and call several times whenever it is required.

DISADVANTAGE

• The procedure can be executed only in the database and utilizes more memory in the database server.

What are the Functions?

• The function is compiled and executed every time it is called.
• This cannot modify the data received as parameters and function must return a value.

Explain types of Functions?

SYSTEM FUNCTION: Built-in functions are used in SQL SELECT expressions to calculate values and manipulate data like Mathematical Functions, Ranking Functions, String Functions, etc.

USER DEFINED FUNCTION

• SQL Server allows us to create our functions called user-defined functions.
• User define functions accepts a parameter and return the result. There are two types of UDF.

Scalar Function: Scalar function takes one or more values but returns a single value.

Table-Valued Function: A Table-Valued Function is a user-defined function that returns data of a table type.

There are two types of Table-Valued Function:

• Inline Table-Valued Function: This function returns a table data type based on a single SELECT statement.
• Multi Statement Table-Valued Function: This function returns the tabular result set. It contains only one statement or more than one statement.

Difference between Stored Procedure and Function?

What is a trigger ? and types of a trigger?

• A Trigger is nothing but logic.
• Triggers are a special kind of stored procedure. It executes implicitly.
• When an event occurs, the trigger is automatically executed.

Types of Trigger:

Instead of Trigger: Instead of Trigger will be fired instead of Insert, Update and Delete operations on a table.

After Trigger: After Trigger fired after Insert, Update and Delete operations on a table.

What are local and global variables and their differences?

GLOBAL VARIABLE

• Global variables declared outside the function. A user cannot declare them.
• The global variable starts with @@
• It stores session related information.

LOCAL VARIABLE

• It is declared inside a function. A user declares the local variable.
• By default, a local variable starts with @
• Every local variable scope has the restriction to the current batch or procedure within any given session.

Which is the constraint in SQL?

• Check Constraint: The Check Constraint specifies a condition on each row in a table.
• Default Constraint: The Default Constraint used to provide a default value to the column.
• Not Null Constraint: By using Not Null Constraint column can hold NULL values.
• Unique Constraint: The Unique Constraint ensures that all value in the column is different.
• Primary Key Constraint: The Primary Key Constraint uniquely identifies each record in a table.
• Foreign Key Constraint: A Foreign Key Constraint used to link two tables together.

What is Data Integrity and Referential Integrity?

DATA INTEGRITY: It is used to maintain the accuracy and consistency of data in a table.

Classification of Data Integrity:

1. System/Pre-defined integrity

• Entity Integrity:

Entity Integrity ensures each row in a table is a uniquely identify.

We can apply this using a primary key, unique key, and not null.

• Referential Integrity:

Referential integrity ensures the relationship between the Tables.

We can apply this using a Foreign Key constraint.

• Domain Integrity:

Domain integrity ensures the data values in a database follow defined rules for values, range, and format.

A database can enforce these rules using Check and Default constraints.

2. User-Defined Integrity

• Triggers

REFERENTIAL INTEGRITY

• Referential integrity refers to the accuracy and consistency of data within a relationship.
• In relationships, data is linked between two or more tables.
• This is achieved by having the foreign key reference a primary key value.

What is Auto Increment?

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

What are Tables and Fields?

• A table is a field containing records and fields.
• The field is the basic element of the data. Depending on the file design fields may be fixed or variables length.
• The field contains a single value.

What is Deadlock? and how we can prevent and avoid it?

• Deadlock is a situation where a set of processes are blocked because each process is holding a resource and waiting for another resource acquired by some other process.

DEADLOCK

• Example: Process 1 is holding Resource 1 and waiting for resource 2 which is acquired by process 2, and process 2 is waiting for resource 1.

Deadlock Prevention Technique:

Mutual Exclusion: If at least one or more resource is in non-shareable mode.

Hold and Wait: Processes which are already holding at least 1 resource may request new resource.

No pre-emption:

• Resources can’t be released by force.
• Only the process holding the resource can release it, which does so after the process has finished its task.

Circular wait: Two or more process form a circular chain where each process waits

Deadlock Avoidance Technique:

• For one instance of each resource type, the Resource Allocation Graph Algorithm is used.
• For the avoidance of multiple instances of each resource type, Banker’s Algorithm is used

What is Datawarehouse?

Datawarehouse stores a large amount of data in different sources.

What is Self-Join?

• Self-Join is used to join a table to itself.
• To join a table itself means that each row of the table is combined with itself and with every other row of the table.
• Especially when the table has a FOREIGN KEY which references its PRIMARY KEY.

What is Cross-Join?

• Cross-Join is a variation of the subquery.
• Cross join produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table.

What is User-defined function? What are its various types?

SQL Server allows us to create our functions called a user-defined function.

Two Types of UDF:

Scalar Function: It is a function that returns a single scalar value.

Table-Valued Functions: It returns a table as output.

• Inline: It Returns a table data type based on a SELECT statement.
• Multi-statement: It Returns a tabular result-set but, unlike inline. Multiple SELECT statements can be used inside the function body.

What is Online Transactional Processing and Online Analytical Processing?

• OLTP and OLAP both are online processing systems.
• OLTP is an Online Transaction Processing system. OLTP system is to record the current Update, Insertion, and Deletion while transaction.
• OLAP is an Online Analytical Processing system. OLAP database stores historical data that has been inputted by OLTP.

What are the differences between OLTP and OLAP?

What are the aggregate and scalar functions?

Scalar Function: SQL scalar functions return a single value, based on the input value.

• UCASE()- Converts a field to upper case
• LCASE()- Converts a field to lower case.
• MID()- Extract characters from a text field.
• LEN()- Returns the length of a text field.
• ROUND()- Rounds a numeric field to the number of decimals specified.
• NOW()- Returns the current system date and time.
• FORMAT()- Formats how a field is to be displayed.

Aggregate functions: SQL aggregate functions return a single value, calculated from values in a column.

• AVG() — Returns the average value
• COUNT() — Returns the number of rows
• FIRST() — Returns the first value
• LAST() — Returns the last value
• MAX() — Returns the largest value
• MIN() — Returns the smallest value
• SUM() — Returns the sum

What is the Cursor? How to use a Cursor?

• A cursor contains information on a select statement and the rows of data accessed by it.
• If we need to deal with multiple rows of data then we must use cursor.

Implicit Cursor:

• It is automatically declared by Oracle whenever a SQL statement is executed.
• It is used to process the instructions INSERT, UPDATE, DELETE, and SELECT.
• During the processing of an implicit cursor, Oracle automatically performs operations OPEN, FETCH, and CLOSE.

Explicit Cursor:

• An explicit cursor is used for queries that return more than one registration.
• It is declared and called by the programmer.

What is the SQL Server Agent?

SQL Server Agent will help to run the job after a specific interval of time.

Difference between the DROP, DELETE, and TRUNCATE?

DROP

• Remove the table from the database.
• All the tables’ rows, indexes, and privileges will also be removed.
• No DML triggers will be fired.
• DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.
• DELETE operations can be rolled back, while DROP and TRUNCATE operations cannot be rolled back.

DELETE

• Removes rows one at a time and records an entry in the transaction log for each deleted row.
• The delete can be used with indexed views.
• It maintains the log, so it slower than TRUNCATES.
• Delete uses more transaction space than the Truncate statement.

TRUNCATE

• TRUNCATE removes all rows from a table.
• We cannot use the WHERE clause with TRUNCATE.
• Truncate cannot be used with indexed views.
• TRUNCATE is faster than DELETE.

What are String Functions in SQL?

What is the SQL CASE statement?

• The CASE statement returns the value based on the condition.
• We can use a case statement in Where, Order by and Group by clause.

What is the CDC in SQL?

• CDC Stands for Changed Data Capture. It helps to synchronize data between the source to the destination.
• CDC helps us to capture the insert, delete, update activities on the table data.
• If we enable the CDC in the SQL server it tracks the changes in the SQL server.
• In CT table Column name as Operation display the changed data like –Delete → 1 Insert → 2 Before update → 3 After update → 4

What is Locking?

• When we apply a lock on data then another database user cannot update the data until the lock is released.
• Locks are released by Rollback or Commit SQL statements.
• Lock prevents two or more database users from updating the same data at the same time.

What are the types of Locking?

Update Lock

• Update lock is a lock just before the Exclusive lock.
• When we put update lock then other transactions do a Select.

Exclusive Lock

• An exclusive lock is a row-level lock.
• When a statement modifies data, its transaction holds an exclusive lock on data that prevents other transactions from accessing the data.

Shared Lock

• Shared Locks are applying during the execution of the select query.
• Updating the data is not allowed until the shared lock is released.

Read Uncommitted data

• It reads uncommitted data. A query in the current transaction can read data modified within another transaction but not yet committed.

Explain 8KB pages in SQL?

• A database page is an 8 KB chunk of data.
• When we insert any data into a database, it saves the data to a series of 8 KB pages in the file.
• If multiple data files in filegroup then SQL Server allocates pages to all data files based on a round-robin mechanism.

What is Pivot and Unpivot in SQL?

• Pivot converts data from row-level to column level.
• Unpivot converts data from the column level to the row level.

What is Star Schema, Snowflake Schema, and Galaxy Schema?

STAR SCHEMA

• The center of the star can have one fact tables and several associated dimension tables.
• The dimension table is not joined to each other.
• The fact table would contain key and measure.
• Denormalized Data structure and query also run faster.

SNOWFLAKE SCHEMA

• A Snowflake Schema is an extension of a Star Schema, and it adds additional dimensions.
• It uses smaller disk space.
• Normalized Data Structure.

GALAXY SCHEMA

• A Galaxy Schema contains two fact table that shares dimension tables.
• The schema is viewed as a collection of stars hence the name Galaxy Schema.

What is Audit Trail? Give one example of the Audit you have used in your project?

• SQL Server Audit is the process of tracking and logging the actions that are performed on the SQL Server instance to achieve the main audit goals of securing the company data
• For example, you may find an organization that tracks the operations on one table that contains financial data
• The audit level will be higher for other organizations, that track all the changes and operations on a SQL Server database

Explain Backup and Restore in SQL Server?

Backup: Backup is a copy of SQL Server data that can be used to restore and recover the data after a failure.

Restore: Restoring is the process of copying data from a backup and applying logged transactions to the data.

• Restore is what you do with backups.
• Take the backup file and turn it back into a database.

What is the different Backup available in SQL Server?

• Full Backup
• Differential Backup
• Transaction Log Backup
• Tail-Log Backup
• File and Filegroup Backup
• Partial Backup
• Copy-Only Backup

What is Performance tuning in SQL?

• Performance tuning is the improvement of system performance.
• Performance tuning includes query optimization, SQL client code optimization, database index management, and in another sense, better coordination between developers and DBAs.

What is Union, minus and Intersect commands?

UNION: Union displays a unique record. It avoids duplicate data.

MINUS: MINUS returns the difference between the first and second SELECT statement.

INTERSECT: Returns us the results that are both to be found in the first and second SELECT statements.

What are RowNumber(), Partition(), Rank() and DenseRank()?

• RowNumber(): The ROW_NUMBER function simply returns the row number of the sorted record
• Partition(): It first groups the records and assigns the same number to the groups.
• Rank(): The RANK function is used to retrieve ranked rows based on the condition of the ORDER BY clause.
• DenseRank(): The DENSE_RANK function does not skip any ranks if there is a tie between the ranks of the preceding records.

What is the SELECT statement?

• The SELECT statement is used to select data from a database.
• The data returned is stored in a result table, called the result-set.

What is update and alter statements?

• The UPDATE statement is used to modify the existing records in a table.
• The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Which are the Operators in SQL?

ARITHMETIC OPERATORS

BITWISE OPERATORS

COMPARISON OPERATORS

LOGICAL OPERATORS

What is CTE in SQL Server?

• Common Table Expression (CTE) in SQL Server defines a temporary result set which we can then use in a Select, Insert, Update, or Delete statement.
• It helps us to manage complicated queries.

What is an ALIAS command?

Aliases used to make column names more readable.

What are Wild Card operators? Why use Like operator?

• % The percent sign represents zero, one, or multiple characters
• _ The underscore represents a single character
• The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Difference between stuff(), replace() and substring()?

• STUFF(): STUFF is used to replace the part of the string with some other string OR It deletes a specified length of characters within a string and replaces it with another set of characters.
• REPLACE(): REPLACE is used to replace all the occurrences of the given pattern in a string.
• SUBSTRING(): SUBSTRING returns the part of the string from a given string expression.

What is a Scheduled job in SQL Server?

• We can run the SQL Agent job schedule query that tells what jobs are scheduled to run on an SQL Server system equipped with SQL Server Agent.
• To do so, you will need to join two tables, including one that stores jobs and one that stores schedules, using SQL.

What is Dot Notation in SQL Server?

• Dot notation (sometimes called the membership operator) qualifies an SQL identifier with another SQL identifier of which it is a component.
• Example- Table1.name = Table2.name

What is Cascading referential integrity and when we use it?

Cascading referential integrity constraints allow you to define the actions when a user tries to delete or update a key for which foreign keys exist.

What is a Transaction? What are ACID properties in the Database?

TRANSACTION

• The transaction helps us to logical group activities. So, activities that are part of that logical group either all pass or all will fail.
• Either all the activity has executed either all the activity has failed.

ACID PROPERTY

• Atomicity: Either all operations are executed or none of the operations are executed.
• Consistency: Data is in a consistent state when a transaction starts and when it ends.
• Isolation: Transactions do not affect each other. If more than 1 transaction is executing simultaneously then every transaction should be executed as if no other transaction is running.
• Durability: Committed data would not be lost, even after power failure

What is Checkpoint?

• Checkpoint Starts from the point where the things failed.
• Do not start from first Starts from the last failed point.
• If you want to Checkpoint first disable the transaction.

Which TCP/IP port does SQL Server run on?

TCP/IP Port runs ON 1433.

What is an execution plan?

Execution plans can tell you how a query will be executed, or how a query was executed.

What are Bottleneck and Fragmentation?

Bottleneck: This situation usually occurs when any shared resources like the SQL database are concurrently accessed by too many people.

Though bottlenecks are inevitable in every system, it should be addressed to save users from loss of time and effort.

Fragmentation: Fragmentation can be defined as a database feature of the server that promotes control on data which is stored at table level by the user.

How can we check the SQL Server version?

Check version:- Select @@Version

Explain types of database and default database

Centralized database:

• Data access by remote Location.
• The data is stored at a centralized location and the users from different locations can access this data.

Hierarchical database:

• Data is organized in a tree-like structure.
• Parent-Child Relationship is present e.g. Department and Employee relationship.

Network database:

• The network database has many to many relationships.
• It allows the child to have multiple parents.

Personal database:

• Data is collected and stored on personal computers which are small and easily manageable.

Cloud database:

• Data is highly stored on a cloud-like hybrid, public, or private cloud.
• Ability to pay for storage capacity and bandwidth on per user basis, scalability to provide data on-demand, along with high availability.

Distributed database:

• It is exactly the opposite of a centralized database.
• The data is not in one place and is distributed at various sites of an organization.
• These sites are connected with the help of communication links which helps them to access the distributed data easily.

Relational database:

• The table consists of rows and columns where the column has an entry for data for a specific category and rows contain instance for that data defined according to the category.

Types of Default Database:

• Master
• Model
• Tempdb
• Msdb
• Pubs

DATE function in SQL Server?

• GETDATE(): It will return the current date along with time.
• DATEPART(): It will return the part of the date or time.
• DATEADD(): It will display the date and time by add or subtract the date and time interval.
• DATEDIFF(): It will display the date and time between two dates.
• CONVERT(): It will display the date and time in different formats.

What are types of CTE?

CTE is a temporary result set that can be used within the execution of a SINGLE insert, update, delete or select query.

• Non-Recursive: The Non-Recursive form of a CTE can be used as an alternative to derived tables and views.
• Recursive: It is a CTE that references itself. A recursive CTE is useful in querying hierarchical data.

Difference between BETWEEN and IN condition operator?

• The BETWEEN operator is used to display rows based on a range of values.
• The IN operator is used to check for values contained in a specific set of values.

Difference between null values, zero and blank space?

• NULL: NULL means it does not have any value, not even garbage value.
• ZERO: ZERO is an integer value.
• BLANK SPACE: BLANK is a simply empty String value.

Use of Distinct keyword in SQL?

DISTINCT keyword returns only distinct values.

What is the coalesce function and why it is used?

COALESCE()

• This function returns the first non-null value from the argument list.
• If all expressions evaluate to null, then the COALESCE function will return null.
• This function does not limit the number of arguments, but they must all of the same data type.

What are the measures and dimensions?

FACT

• A fact table is a primary table in a dimensional model.
• A Fact Table contains Measurements/facts and Foreign key to the dimension table.

DIMENSION

• A dimension table contains dimensions of a fact.
• They are joined to the fact table via a foreign key.
• Dimension tables are de-normalized tables.

What is Collation?

Collation is defined to specify the sort order in a table.

Types of sort order:

• Case sensitive
• Case Insensitive
• Binary

What is Temp table in SQL Server?

• Temp table used to temporary data stored in SQL Server.
• There are two types of temp table:

LOCAL TABLE

• Local table created using the # symbol.
• The Local Temp Table deleted when the user closes the connection.

GLOBAL TABLE

• Global table created using the ## symbol.
• The Global Temp Table deleted when the user closes the connection.

What is a Table Variable in SQL Server?

• The table variable is created when they are declared and are dropped when they go out of the scope.
• The table variable is always useful for less amount of data. It stores data on disk.
• Table variables can have a primary key, but indexes cannot be created on them.

What is Common Table Expression in SQL Server?

• The Common Table Expression is a temporary result set that you can reference to Select, Insert, Update, Delete, and View statements.
• There are two types of CTE:

RECURSIVE CTE: A Recursive CTE is one that references itself within that CTE. It is useful when working on hierarchical or structural data.

• ANCHOR QUERY: This is the first statement executed in the case of a recursive CTE.
• SEPARATOR: It is a separator between anchor query and recursive query.
• RECURSIVE QUERY: This is the CTE query that references the same CTE and resulting in recursion.

NON-RECURSIVE CTE: A Non-recursive CTE is one that does not reference itself within the CTE.

If you are a newbie to database learning — SQL Server recommended is the following must-watch video: -