Q56-Q60

Q56. How to return multiple tables by stored procedure in sql?

Q57. What are the difference between CAST and CONVERT?

Q58. What is no sql database?

Q59. When to choose no sql database?

Q60. Full Outer join vs Cross Join in sql?

============================================================================

Q56. How to return multiple tables by stored procedure in sql?

Answer:

Simply by having multiple select statements will return multiple tables in sql. 

CREATE PROCEDURE P2

AS 

BEGIN

SELECT * FROM Products

SELECT * FROM Products

SELECT * FROM Categories

END

This will return 3 tables ( 2 product + 1 categories).

exec p2

============================================================================

Q57. What are the difference between CAST and CONVERT?

Answer:

SELECT CAST ('10' as int) * 20,
       CONVERT (int, '10') * 20

Both are used to convert data from one type to another.

CAST:

1. It is ANSI SQL specification means all sql and infact non sql database understand concept of cast. 

2. No optional style parameter. 

CONVERT:

1. It is MS SQL sepecific. 

2. It has optional style parameter.  SELECT CONVERT(VARCHAR,GETDATE(),101)

where 101 is short value representing MMDDYYYY

============================================================================

Q58. What is no sql database?

Answer:

There are mainly 4 types of noSQL database

1. Key-value model—the least complex NoSQL option, which stores data in a schema-less way that consists of indexed keys and values. Examples: Cassandra, Azure, LevelDB, and Riak.
2. Column store—or, wide-column store, which stores data tables as columns rather than rows. It’s more than just an inverted table—sectioning out columns allows for excellent scalability and high performance. Examples: HBase, BigTable, HyperTable.
3. Document database—taking the key-value concept and adding more complexity, each document in this type of database has its own data, and its own unique key, which is used to retrieve it. It’s a great option for storing, retrieving and managing data that’s document-oriented but still somewhat structured. Examples: MongoDB, CouchDB.
4. Graph database—have data that’s interconnected and best represented as a graph? This method is capable of lots of complexity. Examples: Polyglot, Neo4J.

============================================================================

Q59. When to choose no sql database?

Answer:

1. When you want to do saving in storage prices.

2. Your data is little structured or no structured. 

3. You want to make use of cloud computing for storing data 

4. Following ACID is not very important. Follows CAP(consistency, availability, partition tolerance)

5. Your queries are not going to have complex logic for retrieving the data. 

============================================================================

Q60. Full Outer join vs Cross Join in sql?

Answer:

There is ON condition in case of crossjoin. 

FULL OUTER JOIN

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

CROSS JOIN

SELECT * 
FROM table1 
CROSS JOIN table2;

============================================================================

Q46-Q50

Q46.  What are the difference between Count(*) and Count(column name) in sql?

Q47. What is CQRS? when to use it?

Q48. What is composite primary key?

Q49. Difference between Dirty reads and Phantom Reads?

Q50. What are different types of isolation level in sql?

==================================================================================

Q46.  What are the difference between Count(*) and Count(column name) in sql?

Answer:

Performance wise there is not much difference that much care about. but in count we might get different value if our column name have nulls in some rows. 

Count(*)- return you count of all rows present. means it will have calculated even is 5 columns out of 6 columns are null. 

Count(columnname) - Give you count only for non null values of that column. 

==================================================================================

Q47. What is CQRS? when to use it?

Answer:

It is one of the database pattern and can be used in microservices and distributed systems. 

CQRS - Command Query Responsibility Segregation. 

CRS +QRS = CQRS

Command - Perform an action like create, update, delete in database. 

Query - Perform an action of retrieving the data. ie View of data

Here we maintain two database 1 for Command actitivites like creation update, delete of records and 

2nd for view of database. 

Data Replication techniques between two database

1) Event handlers: when there is a change in DB1 it will publish an event witch will be captured by DB2

2) SQL Triggers and procedures: Plain old technique by which we can copy data from one place to another. 

https://www.youtube.com/watch?v=hrmeeCEqzo8&ab_channel=GreenLearner

==================================================================================

Q48. What is composite primary key?

Answer:

When a composite key act as a primary key. it is called composite primary key. 

==================================================================================

Q49. Difference between Dirty reads and Phantom Reads?

Answer:

https://mohammaddarab.com/dirty-reads-vs-phantom-reads-sql-server/

==================================================================================

Q50. What are different types of isolation level in sql?

Answer:

https://www.sqlservercentral.com/articles/isolation-levels-in-sql-server

==================================================================================