• Home
  • SQL Server
    • Articles
    • T-SQL Puzzles
    • Output Puzzles
    • Interview Questions
    • Performance Tuning
    • SQL SERVER On Linux
    • Resources
  • SSRS
    • SSRS Articles
    • Interview Questions
  • SSAS
    • SSAS Articles
    • DAX
  • SQL Puzzles
  • Interview Questions
    • SQL Interview Questions
    • Data Interview Questions
  • Python Interview Puzzles
  • New Features(SQL SERVER)
    • SQL SERVER 2017
    • SQL SERVER 2016
    • SQL SERVER On Linux
  • Social
    • Expert Exchange
      • Top Expert in SQL
      • Yearly Award
      • Certifications
      • Achievement List
      • Top Expert of the Week
    • HackerRank (SQL)
    • StackOverflow
    • About Me
      • Contact Me
      • Blog Rules

Improving my SQL BI Skills

Improving my SQL BI Skills

Tag Archives: Cardinality

Cardinality in SQL

08 Wednesday Apr 2015

Posted by Pawan Kumar Khowal in SQL Concepts, SQL SERVER, SQL Server Interview Questions

≈ 1 Comment

Tags

Cardinality, Cardinality in SQL, Cardinality in SQL SERVER, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, puzzle sql developer, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Interview Questions, SQL Joins, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, TOP Clause, Tricky Questions, TSQL, TSQL Interview questions, TSQL Queries


Cardinality

Last week one of my friend called me and asked me what is Cardinality. He told me that in one of the interviews the interviewer asked this question. Before this I have never heard that this question was asked in a MSBI Interview.

https://en.wikipedia.org/wiki/Cardinality_(SQL_statements)

As per Wikipedia article on Cardinality (SQL statements), defines it as follows:

Cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. The lower the cardinality, the more duplicated elements in a column

There are 3 types of cardinality:

High-cardinality, Normal-cardinality, and Low-cardinality

High-cardinality refers to columns with values that are very uncommon or unique. High-cardinality column values are typically identification numbers, email addresses, or user names. An example of a data table column with high-cardinality would be a Emps table with a column named EMPID. Since the values held in the EMPID column are unique, this column’s cardinality type would be referred to as high-cardinality.

Normal-cardinality refers to columns with values that are somewhat uncommon. Normal-cardinality column values are typically names, street addresses, or vehicle types. An example of a data table column with normal-cardinality would be a CUSTOMER table with a column named LAST_NAME, containing the last names of customers. While some people have common last names, such as Smith, others have uncommon last names. Therefore, an examination of all of the values held in the LAST_NAME column would show “clumps” of names in some places (e.g.: a lot of Smith’s ) surrounded on both sides by a long series of unique values. Since there is a variety of possible values held in this column, its cardinality type would be referred to as normal-cardinality.

Low-cardinality refers to columns with few unique values. Low-cardinality column values are typically status flags, Boolean values, or major classifications such as gender. An example of a data table column with low-cardinality would be a CUSTOMER table with a column named NEW_CUSTOMER. This column would contain only 2 distinct values: Y or N, denoting whether the customer was new or not. Since there are only 2 possible values held in this column, its cardinality type would be referred to as low-cardinality.

Keep learning. We all need to learn.

MSBISkills.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

Advanced SQL SERVER Interview Questions – Set 1

07 Tuesday Apr 2015

Posted by Pawan Kumar Khowal in SQL Concepts, SQL SERVER, SQL Server Interview Questions

≈ 1 Comment

Tags

Cardinality, Fragmentation, GUID, Hash Join, In which scenarios we should not use CTE's, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Merge Join, Nested Loop, Objective Puzzle, Physical join in SQL, Physical joins in SQL Server, puzzle sql developer, Puzzles, Queries for SQL Interview, Red Flags, Red Flags in SQL, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Interview Questions, SQL Joins, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, statistics, statistics in SQL, statisticsin SQL SERVER, T SQL Puzzles, TOP Clause, Tricky Questions, TSQL, TSQL Interview questions, TSQL Queries, Unique Index vs Unique Constraint


Advance SQL SERVER Interview Questions – Set 1

Recently I have attended a SQL Server Architect Interview. There were 3 interviewers & since the requirement is for SQL Architect, they asked all the questions from SQL only. Please find some of the questions below. Will post answers to these questions tomorrow.

Question
What are statistics? Where they are used and how to check statistics.
What are the types of Fragmentations
Can we use GUID as Primary key in a table ?
Difference between Unique Index vs Unique Constraint
What are RED Flags in SQL Server and what is there usage ?
What are the types of physical joins in SQL Server ? What are the different join operators in SQL Server?
What is a Latch in SQL Server ?
Difference between Latch and Lock ..
2014 New Features in DB Engine.
In which scenarios we should not use CTE’s.
What do you mean by Cardinality in SQL Server.
What are Histogram and Density Vector.
Why we can’t put Order by inside the view?
What is a cross apply and how to use this?
Can we use more than one CTE in a single select query?
I have opened a nested transaction inside an outer transaction, if i do rollback which transaction will be undone?
How do you know the total transaction count?
I have created a table variable can we use it in a nested stored procedure? If not what is the scope of a table variable?
If suppose we have a user defined data type. If we can modify the length of the data type does it effects in all places across the database?
Can we fire a trigger manually?
What are the magic tables? Do we have “Updated” magic table?
What is the difference between UnionAll and Merge?
Basic difference between stored procedure and user defined function?
See we have a simple query that’s calling a static function, like “Select * from employee where joiningdate < getstaticdate()”? Does it call function for every time or only for matched rows? How you tune this query?
Why should we use CTE?
What is the difference between sub query and correlated query
What is the difference between pessimistic locking and optimistic locking?
What is XACT_ABORT ON?
How to filter nested stored procedure code from profiler?
What happens when a rollback happens in inside a nested stored procedure?
What is a bitmap index?
How to avoid bookmark lookup in execution plan?
When index scan happens?
Does temp tables and table variables both stored in tempdb?
Why cursors are so costly?
Can we call a procedure from a function?
Can we write DML inside a function?
What is the best value for MAXDOP value?
Which is better “Left Outer” Or “NOT EXIST”?
How to find the statistics are outdated?
How to find the query running on a given SPID?
What are the limitations on “SET ROWCOUNT”?
While creating a “Include” columns on which basis we should consider main column and include columns?
How to find the last statistics update date?
An indexed view is referring only one base table. Both view and table are having index defined on them. Which index would be utilized when a query executed against the table.
I have an indexed view, now base table data has been modified, does the modified data reflected in view automatically?
Does “TRUNCATE” DDL or DML command?
I have written a recursive CTE, it’s been running infinitely and failed with an error. The requirement is that the recursion required for 45000 times. How could you be able to handle the situation?
What is index reorganization?
How sql engine knows which index has to be used while dealing with indexed views?
How to prevent bad parameter sniffing? What exactly it means?
What dll file that handle the transaction logs in logshipping?
How to find all dependent objects of a table?
Which is better a CTE or a subquery? Why?
How to rebuild a master database? Ho to restore a master database?
Any alternative to triggers?
Top performance tuning tools
What events need to be added to capture execution plan in sql profiller?
What is the new lock escalation in sql 2008?
What is a filtered index?
What happens when a transaction runs on SQL server? Let’s say simple update statement “Update Table set col1 = value where col2 = value”
What is fragmentation? How it happens?
I have a log file which is of 250 GB. Log is full. We don’t have a disk space on any other drive for creating .ndf, auto growth is ON , and essentially there are no options to allocate new space for the file. What’s your action plan?
How B-Tree formed for Clustered and non clustered indexes?
How B-Tree forms for indexes with included column?
Does alzebrizer tree stores in memory for stored procedures, views and constraints?
How to design TempDB files? And what is the limit?
How VLF’s created for tempDB?
When the checkpoint can happen? What it exactly do?
When the lazywriter happens and what it’ll do?
What is memory grant in SQL Server?
Why resourceDB introduced?
How to move master database?
How to rebuild master database and what are the considerations?
What is a boot page?
What is .TUF file? What is the significance of the same? Any implications if the file is deleted?
What are the different shrink options?
How do you know whether statistics are latest or expired?
How to create linked server
Can you give some examples for One to One, One to Many and Many to Many relationships?
How to capture a trace from production without any impact on performance?
How to capture the long running queries?
You have any idea on Table Partitions?
You have got a request to execute a query which is an “Update” query. That update is updating 5 million rows, after an hour it’s still executing and you are getting lot of requests from report users that their things are getting slow down. What’s your action plan?
See we have a view which is getting data from different tables. One day it’s starts executing infinitely. I have seen no blocking , no bulk operation happened. I have stopped all jobs and maintenance plans on the server. No one is connected to the database but still it’s been taking longer time. What might be the possible reasons?
Can you explain sql server transaction log architecture?
What are the phases of sql server database restore process?
Do you have any idea about sparse column?
We have a procedure which is running fine till today afternoon. Suddenly it started taking long time to execute and there of it leads to a timeout error from application. What might be happening? How you troubleshoot?
What are the critical issues you faced in your career?
How to change the port number for SQL Server? Default port no of SQL SERVER
Difference between ISNULL and COAELSCE
Difference between partition by and patindex
What is the difference between CROSS / OUTER APPLY AND JOINS in T-SQL?
What is stuff function? Difference between stuff and replace?
We have a query which is running fine in development but facing performance issues at production. You got the execution plan from production DBA. Now you need to compare with the development execution plan. What is your approach?
What is lock Escalation
How do you retrieve random 5 rows from a table
What exactly you check in the query execution plan window?
What is the output of len(1234.56)
Where in MS SQL Server is ’100’ equal to ‘0’?
Explain clearly about all the normal forms with proper example
I have a table with millions of rows. I want to retain only last 5% of the rows ? How do u do it ? (Expectation :- transfer last 5% to temp table
Considerations  while designing a DB
Considerations while creating tables and its naming conventions
Explain transaction log in detail
I have a 500 GB Table which is performing slow ? Your plan of action ?
Explain Column Store Index
Explain Isolation Levels ? Default one ?

 

Happy Learning..

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

SQL Server Query – 6 | Nth Highest Salary

03 Tuesday Aug 2010

Posted by Pawan Kumar Khowal in T SQL Puzzles

≈ 3 Comments

Tags

Cardinality, Complex SQL Challenges, complex sql statement(puzzle), Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Khowal, Learn complex SQL, Learn SQL, Learn T-SQL, msbi skills, MSBISkills, msbiskills.com, Second highest salary puzzle


T-SQL Query | [Nth Highest Salary Puzzle] – Write a query which will find the Nth highest salary from the table. In this case we are finding 2nd Highest Salary

Sample Input

Name Salary
e5 45000
e3 30000
e2 49000
e4 36600
e1 58000

Expected Output

Name Salary
e2 49000

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create table

CREATE TABLE NthHighest
(
 Name  varchar(5)  NOT NULL,
 Salary  int  NOT NULL
)

--Insert the values
INSERT INTO  NthHighest(Name, Salary)
VALUES 
('e5', 45000),
('e3', 30000),
('e2', 49000),
('e4', 36600),
('e1', 58000)

--Check data
SELECT Name,Salary FROM NthHighest
 

Update May 14 | Solution


--


---------------------------------------
--Sol 1 | Pawan Kumar Khowal
---------------------------------------

SELECT * FROM NthHighest N WHERE 1 = (SELECT DISTINCT COUNT(*) FROM NthHighest N1 WHERE N1.Salary > N.Salary )

---------------------------------------
--Sol 2 | Pawan Kumar Khowal
---------------------------------------

SELECT TOP 1 WITH TIES Name , Salary FROM NthHighest N1 
WHERE Salary IN (SELECT TOP 2 WITH TIES Salary FROM NthHighest ORDER BY Salary DESC)
ORDER BY Salary

---------------------------------------
--Sol 3 | Pawan Kumar Khowal
---------------------------------------

SELECT A.Name, B.Salary
FROM (
	SELECT MAX(Salary) Salary
	FROM NthHighest N1 
	WHERE N1.Salary != (SELECT MAX(Salary) FROM NthHighest) ) B
CROSS APPLY (SELECT NAME FROM NthHighest WHERE SALARY = B.SALARY ) A

---------------------------------------
--Sol 4 | Pawan Kumar Khowal
---------------------------------------

;WITH CTE AS
(
	SELECT * , RANK() OVER (ORDER BY SALARY DESC) rnk FROM NthHighest
)
SELECT Name, Salary FROM CTE WHERE rnk = 2


--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

20.000000 77.000000

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

Blog Stats

  • 1,097,352 hits

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,131 other subscribers

Pawan Khowal

502 SQL Puzzles with answers

Achievement - 500 PuzzlesJuly 18, 2018
The big day is here. Finally presented 500+ puzzles for SQL community.

200 SQL Server Puzzle with Answers

The Big DayAugust 19, 2016
The big day is here. Completed 200 SQL Puzzles today

Archives

June 2023
M T W T F S S
 1234
567891011
12131415161718
19202122232425
2627282930  
« Oct    

Top Articles

  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…

Archives

  • October 2020 (29)
  • September 2018 (2)
  • August 2018 (6)
  • July 2018 (25)
  • June 2018 (22)
  • May 2018 (24)
  • April 2018 (33)
  • March 2018 (35)
  • February 2018 (53)
  • January 2018 (48)
  • December 2017 (32)
  • November 2017 (2)
  • October 2017 (20)
  • August 2017 (8)
  • June 2017 (2)
  • March 2017 (1)
  • February 2017 (18)
  • January 2017 (2)
  • December 2016 (5)
  • November 2016 (23)
  • October 2016 (2)
  • September 2016 (14)
  • August 2016 (6)
  • July 2016 (22)
  • June 2016 (27)
  • May 2016 (15)
  • April 2016 (7)
  • March 2016 (5)
  • February 2016 (7)
  • December 2015 (4)
  • October 2015 (23)
  • September 2015 (31)
  • August 2015 (14)
  • July 2015 (16)
  • June 2015 (29)
  • May 2015 (25)
  • April 2015 (44)
  • March 2015 (47)
  • November 2012 (1)
  • July 2012 (8)
  • September 2010 (26)
  • August 2010 (125)
  • July 2010 (2)

Article Categories

  • Analysis Services (6)
    • DAX (6)
  • Data (2)
    • Data warehousing (2)
  • Integration Services (2)
  • Magazines (3)
  • Python (29)
  • Reporting Services (4)
  • SQL SERVER (820)
    • Download SQL Interview Q's (212)
    • SQL Concepts (323)
    • SQL Performance Tuning (155)
    • SQL Puzzles (331)
    • SQL SERVER 2017 Linux (6)
    • SQL Server Interview Questions (308)
    • SQL SERVER Puzzles (332)
    • T SQL Puzzles (547)
    • Tricky SQL Queries (439)
  • UI (30)
    • ASP.NET (5)
    • C# (13)
    • CSS (9)
    • OOPS (3)
  • Uncategorized (5)

Recent Posts

  • Python | The Print and Divide Puzzle October 30, 2020
  • Python | Count consecutive 1’s from a list of 0’s and 1’s October 30, 2020
  • Python | How to convert a number into a list of its digits October 26, 2020
  • Python | Validate an IP Address-IPV6(Internet Protocol version 6) October 26, 2020
  • Python | Print the first non-recurring element in a list October 26, 2020
  • Python | Print the most recurring element in a list October 26, 2020
  • Python | Find the cumulative sum of elements in a list October 26, 2020
  • Python | Check a character is present in a string or not October 26, 2020
  • Python | Check whether a string is palindrome or not October 26, 2020
  • Python | Find the missing number in the array of Ints October 26, 2020
  • Python | How would you delete duplicates in a list October 26, 2020
  • Python | Check whether an array is Monotonic or not October 26, 2020
  • Python | Check whether a number is prime or not October 26, 2020
  • Python | Print list of prime numbers up to a number October 26, 2020
  • Python | Print elements from odd positions in a list October 26, 2020
  • Python | Print positions of a string present in another string October 26, 2020
  • Python | How to sort an array in ascending order October 26, 2020
  • Python | How to reverse an array October 26, 2020
  • Python | Find un-common words from two strings October 26, 2020
  • Python | How to convert a string to a list October 26, 2020
  • Python | Find unique words from a string October 26, 2020
  • Python | Calculate average word length from a string October 26, 2020
  • Python | Find common words from two strings October 26, 2020
  • Python | Find the number of times a substring present in a string October 26, 2020
  • Python | Find maximum value from a list October 26, 2020
  • Python | How to find GCF of two numbers October 26, 2020
  • Python | How to find LCM of two numbers October 26, 2020
  • Python | How to convert a list to a string October 26, 2020
  • Python | Replace NONE by its previous NON None value October 26, 2020
  • Microsoft SQL Server 2019 | Features added to SQL Server on Linux September 26, 2018

Create a website or blog at WordPress.com

  • Follow Following
    • Improving my SQL BI Skills
    • Join 231 other followers
    • Already have a WordPress.com account? Log in now.
    • Improving my SQL BI Skills
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar