SQL: 2021

SQL Query

Q1. You have 3 tables, employee, employee details and employee salary. Write a query to get the max salary among all employees without using the max keyword.
Q2. select first and last row from a table?

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

Q2. select first and last row from a table?

Answer:

declare @count int;

select @count = Count(1) from Projects;

with Cte as

(

select *, ROW_NUMBER() over (order by projectID desc) as rownum from Projects

)

select * from Cte where rownum =1 or rownum = @count

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

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

BEGIN

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

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

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

Q51-Q55

Q51. What is full join in sql?

Q52. What are different types of Triggers in SQL?

Q53. Can you call a function inside another function in sql?

Q54. Syntax of user defined scaler function in sql?

Q55. Difference between Char(4) and Varchar(4) ?

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

Q51. What is full join in sql?

Answer:

Full join also known as Full Outer Join.

There are 3 outer joins left outer join(or left join), Right outer Join (or right join) and full outer join (or full join)

The combination result of left outer join and right outer join is full join.

Left outer join = all values of left table will be present and in case key is not found then null will be placed against that values.

SELECT C.FirstName, C.LastName, O.OrderDate

FROM Customer C

FULL JOIN [Order] O ON C.Id = O.CustomerId

ORDER BY O.OrderDate

https://www.dofactory.com/sql/full-outer-join

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

Q52. What are different types of Triggers in SQL?

Answer:

A trigger is a special type of stored procedure in database that automatically invokes/runs/fires when an event occurs in the database server. A trigger uses the special table to keep a copy of the row which we have just inserted, deleted or modified.

There are three types of triggers in SQL Server.

DDL Trigger - DDL events are CREATE, ALTER and DROP
DML Trigger - INSERT, UPDATE, and DELETE
Logon Trigger

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

Q53. Can you call a function inside another function in sql?

Answer:

Yes you can call a function inside a function.

In fact, you can call the current function inside the function, to cause a loop.

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

Q54. Syntax of user defined scaler function in sql?

Answer:

CREATE FUNCTION Function_Name(@Parameter_Name Data_type,
.... @Parameter_Name Data_type
)
RETURNS Data_Type
AS
BEGIN
-- Function Body

RETURN Data
END

https://www.tutorialgateway.org/user-defined-functions-in-sql/#:~:text=The%20syntax%20the%20SQL%20Server,%2C%20INT%2C%20FLOAT%2C%20etc.

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

Q55. Difference between Char(4) and Varchar(4)

Answer:

Char:

1. Its of fixed length and if value is less than the size it will fill with blank spaces.

2. Select Length(<<char variable>>) will always give the same size, which is the size of char variable.

Varchar:

1. Its max length is fixed and value is small than only that part will be filled.

2. Select Length(<<varchar variable>>) will always give the size by which it is filled. if the values is small than less size value will come

https://www.geeksforgeeks.org/char-vs-varchar-in-sql/

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

Q41-Q45

Q41. What are the various return types of stored procedure in SQL?

Q42. What are the difference between Delete and Truncate?

Q43. Write a sql query to remove duplicate records from table?

Q44. What are aggregate functions in sql?

Q45. How we can update View in sql?

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

Q41. What are the various return types of stored procedure in SQL?

Answer:

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

Q42. What are the difference between Delete and Truncate?

Answer:

Delete

1. It can be used to delete specific data.

2. It locks the table row before deleting it.

3. We can rollback the changes.

4. It is slower than truncate

5. Indexes doesnt get reset.

6. Triggers can get fired.

Truncate

1. Only used to delete whole table.

2. It locks the entire table before deleting it.

3. We can not rollback the changes.

4. It is faster than Delete as it make less use of transaction log and it does not worry about rollback point.

5. Indexes got reset.

6. Triggers do not get fired on Truncate.

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

Q43. Write a sql query to remove duplicate records from table?

Answer:

So we have to delete only duplicate means one entry must still remain intable.

By Max we will get max of duplicate entries. So we have to delete all enteries which are not part of max (id). in case of single entry it will become part of max(id) as well.

DELETE FROM [SampleDB].[dbo].[Employee]
    WHERE ID NOT IN
    (
        SELECT MAX(ID) AS MaxRecordID
        FROM [SampleDB].[dbo].[Employee]
        GROUP BY [FirstName], 
                 [LastName], 
                 [Country]
    );

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

Q44. What are aggregate functions in sql?

Answer:

Below are the list of aggregate functions in sql

1) Count()

2) Sum()

3) Avg()

4) Min()

5) Max()

An aggregate function performs a calculation one or more values and returns a single value. The aggregate function is often used with the GROUP BY clause and HAVING clause of the SELECT statement.

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

Q45. How we can update View in sql?

Answer:

We can update view just like table.

UPDATE < view_name > SET<column1>=<value1>,<column2>=<value2>,.....
WHERE <condition>;

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

Q36-Q40

Q36. What is merge join?

Q37. What is incremental Data in sql?

Q38. What is transactional Data in sql?

Q39. What you mean by schema in sql?

Q40. What are the various return types in SQL function?

----------------------------------------------------------------------------------------------------------------------------------

Q36. What is merge join?

Answer:

----------------------------------------------------------------------------------------------------------------------------------

Q37. What is incremental Data in sql?

Answer:

----------------------------------------------------------------------------------------------------------------------------------

Q38. What is transactional Data in sql?

Answer:

----------------------------------------------------------------------------------------------------------------------------------

Q39. What you mean by schema in sql?

Answer:

----------------------------------------------------------------------------------------------------------------------------------

Q40. What are the various return types in SQL function?

Answer:

NOTE: Unlike Stored Procedure, Function returns an only single value.

User defined function can return 3 types of value

1. Scaler

2. Inline table valued - The value of the table variable should be derived from a single SELECT statement

3. Multi statement Table valued. - A user-defined multi-statement table-valued function returns a table variable as a result of actions performed by the function. In this, a table variable must be explicitly declared and defined whose value can be derived from multiple SQL statements.

https://www.dotnettricks.com/learn/sqlserver/different-types-of-sql-server-functions

Search This Blog

SQL Query

Q56-Q60

Q46-Q50

Q51-Q55

Q41-Q45

Q36-Q40