SQL- All Concepts

SQL Concepts Question and Solution 

CTE

Window Functions


Types of Window functions

  • Aggregate Window Functions
    SUM(), MAX(), MIN(), AVG(). COUNT()
  • Ranking Window Functions
    RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
  • Value Window Functions
    LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
ALL
ALL is an optional keyword. When you will include ALL it will count all values including duplicate ones. DISTINCT is not supported in window functions

ex:
SELECT order_id, order_date, customer_name, city, order_amount
,MAX(order_amount) OVER(PARTITION BY city) as maximum_order_amount
FROM [dbo].[Orders]

ex:

3
4
5
 
SELECT order_id,order_date,customer_name,city, order_amount,
DENSE_RANK() OVER(ORDER BY order_amount DESC) [Rank]
FROM [dbo].[Orders]


ex:

ROW_NUMBER() with PARTITION BY

Value Window Functions

Value window functions are used to find first, last, previous and next values. The functions that can be used are LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

ex-

SELECT order_id,customer_name,city, order_amount,order_date,
--in below line, 1 indicates check for next row of the current row
LEAD(order_date,1) OVER(ORDER BY order_date) next_order_date
FROM [dbo].[Orders]

ex-
SELECT order_id,order_date,customer_name,city, order_amount,
FIRST_VALUE(order_date) OVER(PARTITION BY city ORDER BY city) first_order_date,
LAST_VALUE(order_date) OVER(PARTITION BY city ORDER BY city) last_order_date
FROM [dbo].[Orders]

CASE 

ex- 

Select
CASE
WHEN Salary >=80000 AND Salary <=100000 THEN 'Director'
WHEN Salary >=50000 AND Salary <80000 THEN 'Senior Consultant'
Else 'Director'
END AS Designation,
Min(salary) as MinimumSalary,
Max(Salary) as MaximumSalary
from Employee
Group By
CASE
WHEN Salary >=80000 AND Salary <=100000 THEN 'Director'
WHEN Salary >=50000 AND Salary <80000 THEN 'Senior Consultant'
Else 'Director'
END




Recursive CTEs :

Recursive CTEs are best in working with hierarchical data such as org charts for the bill of materials


Comments

Post a Comment

Popular posts from this blog

Read and Navigate XML - Beautiful Soup

 ### How to read and navigate XML There is a Python library called BeautifulSoup, which makes reading in and parsing XML data easier. Here is the link to the documentation: [Beautiful Soup Documentation](https://www.crummy.com/software/BeautifulSoup/) The find() method will find the first place where an xml element occurs. For example using find('record') will return the first record in the xml file: ```xml <record>   <field name="Country or Area" key="ABW">Aruba</field>   <field name="Item" key="SP.POP.TOTL">Population, total</field>   <field name="Year">1960</field>   <field name="Value">54211</field> </record> ``` The find_all() method returns all of the matching tags. So find_all('record') would return all of the elements with the `<record>` tag. Run the code cells below to get a basic idea of how to navigate XML with BeautifulSoup. To naviga...
Read more

difference-between-stream-processing-and-message-processing

 difference-between-stream-processing-and-message-processing Message Processing implies operations on and/or using  individual  messages. Stream Processing encompasses operations on and/or using individual messages as well as operations on collection of messages as they flow into the system. For e.g., let's say transactions are coming in for a payment instrument - stream processing can be used to continuously compute hourly average spend. In this case - a sliding window can be imposed on the stream which picks up messages within the hour and computes average on the amount. Such figures can then be used as inputs to fraud detection systems Although Rabbit supports streaming, it was actually not built for it(see Rabbit´s web site) Rabbit is a Message broker and Kafka is a event streaming platform. Kafka can handle a huge number of 'messages' towards Rabbit. Kafka is a log while Rabbit is a queue which means that if once consumed, Rabbit´s messages are not there anymore in c...
Read more

WordNet in Python

Image
  WordNet   is an English dictionary which is a part of   Natural Language Tool Kit   (NLTK) for Python. This is an extensive library built to make   Natural Language Processing   (NLP) easy. Some basic functions will be discussed in this article. To start using WordNet, you have to import it first: from  nltk.corpus  import  wordnet Synsets and Lemmas In WordNet, similar words are grouped into a set known as a  Synset  (short for  Synonym-set ). Every Synset has a name, a part-of-speech, and a number. The words in a Synset are known as  Lemmas . Getting Synsets The function  wordnet.synsets('word')   returns an array  containing all the Synsets related to the word passed to it as the argument. Example: print ( wordnet.sysnets ( 'room' )) Output: [Synset(‘room.n.01’), Synset(‘room.n.02’), Synset(‘room.n.03’), Synset(‘room.n.04’), Synset(‘board.v.02’)] The method returned five Synsets; four have the name...
Read more