Advertisements

Archive

Archive for the ‘SQL Trivia’ Category

SQL Trivia – SQL Server Code names

January 25, 2018 Leave a comment

 
Every time the SQL Server Product team starts working with their new version of SQL Server they give a Code Name to it, and before the final name is announced (which goes with the year name) the Code name gets popular among them, developers, users and the whole SQL community. But sometimes its very tricky and confusing to keep track of the Code Names for the older versions.

So, here is a list of SQL Server various versions I’ve compiled which lists the SQL Server release Final Name with Code Name, and other details like Year released, Version number and OS supported:

Year Final Name Version Code Name OS Support
2017 SQL Server 2017  14.0 Helsinki Win & Linux
2016 SQL Server 2016  13.0 Windows
2014 SQL Server 2014  12.0 Hekaton Windows
2012 SQL Server 2012  11.0 Denali Windows
2010 SQL Server 2008 R2  10.5 Kilimanjaro Windows
2010 SQL Azure DB  10.25 Cloud DB Azure
2008 SQL Server 2008  10.0 Katmai Windows
2005 SQL Server Analysis Services  – Picasso Windows
2005 SQL Server 2005  9.0 Yukon Windows
2003 SQL Server Reporting Services  – Rosetta Windows
2003 SQL Server 2000 x64 (64 bit)  8.0 Liberty Windows
2000 SQL Server 2000 x86 (32 bit)  8.0 Shiloh Windows
1999 SQL Server 7.0 OLAP Services  – Plato Windows
1998 SQL Server 7.0  7.0 Sphinx Windows
1996 SQL Server 6.5  6.5 Hydra Windows
1995 SQL Server 6.0  6.0 SQL95 Windows
1993 SQL Server 4.21  4.21 Windows
1992 SQL Server 4.2  4.2 OS/2
1991 SQL Server 1.1 (16 bit)  1.1 Pietro OS/2
1990 SQL Server 1.0 (16 bit)  1.0 Filipi OS/2

 

You can also check my post on [SQL and its history] !!!


Advertisements

SQL Trivia – Multiple ways to get list of all tables in a database

August 22, 2016 Leave a comment

 
This is one of a typical Interview Question you might face while going for an opening of a SQL Developer. This looks very simple question but its also an important question to judge a person if he/she knows SQL basics and has really worked in SQL Server or not.

–> So, here are following ways you can query the SQL Server metadata and system tables to get list of all tables in a particular database, by using:

1. sys.tables

select * from sys.tables

2. sys.objects with filter of type = ‘U’

select * from sys.objects where type = 'U'

3. sys.sysobjects with filter of type = ‘U’

select * from sys.sysobjects where type = 'U'

4. INFORMATION_SCHEMA.TABLES

select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'TABLE'

5. sp_msforeachtable undocumented function

exec sp_msforeachtable 'print ''?'''

 

Out of all these the best option is to use the 4th one, i.e. INFORMATION_SCHEMA.TABLES views, as Microsoft itself suggest to use it instead of first three & the 5th option.


Categories: SQL Trivia

SQL Trivia – What all schemas cannot be dropped in a SQL Server database?

August 4, 2016 Leave a comment

 
–> The following four built-in database schemas cannot be dropped:
 

1. The “dbo” schema: is the default database schema for new objects created by users having the db_owner or db_ddl_admin roles. The dbo schema is owned by the dbo user account. By default, users created with the CREATE USER Transact-SQL command have dbo as their default schema.

2. The “guest” schema: is used to contain objects that would be available to the guest user. This schema is rarely used.

3. The “sys” schema: is reserved by SQL Server for system objects such as system tables and views.

4. The “INFORMATION_SCHEMA” schema: is used by the Information Schema views, which provide ANSI standard access to metadata. This schema is contained in each database. Each information schema view contains metadata for all data objects stored in that particular database. Information schema views are based on sys catalog view definitions.
 

… notes from: Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012


Categories: SQL Trivia Tags:

SQL Trivia – What are Niladic functions in SQL Server?

March 30, 2016 1 comment

 
Niladic functions are functions that do not accept any parameters, are specified without parentheses, and return only one result.
 

Following is the list of Niladic functions:

Niladic function (SQL-92)

Value returned
CURRENT_TIMESTAMP Current date and time.
CURRENT_USER Name of user performing an insert.
SESSION_USER Name of user performing an insert.
SYSTEM_USER Name of user performing an insert.
USER Name of user performing an insert.

 

Execution of below query with these functions gives following results:

SELECT 
	 CURRENT_TIMESTAMP AS 'CURRENT TIMESTAMP'
	,CURRENT_USER AS 'CURRENT USER'
	,SESSION_USER AS 'SESSION USER'
	,SYSTEM_USER AS 'SYSTEM USER'
	,USER AS 'USER'

Niladic functions


Categories: SQL Trivia Tags: ,

SQL Trivia – Find second (or nth) Highest Salary or Marks

January 5, 2014 Leave a comment

At a beginner level this is the most basic and widely asked SQL Interview Question, and I’ve seen many people talk about this when you ask them to ask a basic SQL question.

Or, I could say it’s a tollgate to judge if a candidate knows basic SQL or not 🙂

So, here on demand of lot of people who ping me or email me for this, I’ve drafted this post on various ways or Queries to get the desired results.
 

–> I’ll create a dummy dbo.Employee table and populate with some dummy records (all these are my school/college friends):

USE tempdb
GO

CREATE TABLE dbo.Employee (
	EmpID INT PRIMARY KEY NOT NULL,
	EmpName VARCHAR(100),
	Salary Money
)
GO

INSERT INTO dbo.Employee
SELECT 1,  'Manoj',   20000
UNION ALL
SELECT 2,  'Saurabh', 50000
UNION ALL
SELECT 3,  'Kanchan', 30000
UNION ALL
SELECT 4,  'Hema',    10000
UNION ALL
SELECT 5,  'Bhanu',   60000
UNION ALL
SELECT 6,  'Prakash', 80000
UNION ALL
SELECT 7,  'Vivek',   40000
UNION ALL
SELECT 8,  'Ramesh',  55000
UNION ALL
SELECT 9,  'Kapil',   65000
UNION ALL
SELECT 10, 'Dev',     90000
GO

select * from dbo.Employee order by Salary DESC
GO

 

–> We will check for these conditions, 2nd highest & nth highest salary records from the above query, as shown below:

2nd highest is: 6, Prakash, 80000.00
5th highest is: 8, Ramesh,  55000.00
7th highest is: 7, Vivek,   40000.00

 

–> 2nd Highest Salary:

1. For SQL Server 2005 and +

;WITH CTE AS (
SELECT 
	EmpID, EmpName, Salary, 
	ROW_NUMBER() OVER(ORDER BY Salary DESC) as RN
FROM dbo.Employee
)
SELECT EmpID, EmpName, Salary
FROM CTE
WHERE RN = 2
GO

2. For SQL Server 2000

SELECT TOP 1 EmpID, EmpName, Salary
FROM (SELECT TOP 2 EmpID, EmpName, Salary 
	  FROM dbo.Employees 
	  ORDER BY Salary DESC
) X
ORDER BY Salary ASC
GO

 

–> nth Highest Salary: Just parametrize the numbers

1. For SQL Server 2005 +

DECLARE @n INT = 5

;WITH CTE AS (
SELECT 
	EmpID, EmpName, Salary, 
	ROW_NUMBER() OVER(ORDER BY Salary DESC) as RN
FROM dbo.Employee
)
SELECT EmpID, EmpName, Salary
FROM CTE
WHERE RN = @n
GO

2. For SQL Server 2000

DECLARE @n INT = 5

SELECT TOP 1 EmpID, EmpName, Salary
FROM (SELECT TOP (@n) EmpID, EmpName, Salary 
	  FROM dbo.Employees 
	  ORDER BY Salary DESC
) X
ORDER BY Salary ASC
GO

--// Final Cleanup
DROP TABLE dbo.Employee
GO

 

There are lot other ways also to get the same results and you can come up with different Queries with more optimizations.

Please do let me know if you have any comments and suggestions, thanks!