SQL for Theater Seat Booking System

Query to find consecutive available seat in Theater or Bus.

Its most frequent asked question in interview and also required during data analysis.


  create table thtr(rowid int, stno int, sts char(2))

  insert into thtr values(1,1,'b'),(1,2,'b'),(1,3,'v'),(1,4,'b')
  insert into thtr values(2,1,'b'),(2,2,'v'),(2,3,'v'),(2,4,'b')
  insert into thtr values(3,1,'v'),(3,2,'v'),(3,3,'v'),(3,4,'v')
  insert into thtr values(4,1,'v'),(4,2,'b'),(4,3,'v'),(4,4,'b')
  insert into thtr values(5,1,'v'),(5,2,'b'),(5,3,'v'),(5,4,'v')
  insert into thtr values(5,5,'v'),(5,6,'b'),(5,7,'v'),(5,8,'v')


declare @seatNeeded int =2

select rowid, count(*)/@seatNeeded as r_avl  from (
select *,stno as s, stno-ROW_NUMBER() over (partition by rowid order by stno) as rn
from thtr where  sts='V'
)t group by rowid,rn
having count(*)>=@seatNeeded

Q2) How many customers brought each product how many times during week?

 ;WITH CTE AS
 (
 SELECT PRD_ID, COUNT(DISTINCT CUSTID) AS NO_OF_CUST, COUNT(PRD_ID) AS NO_OF_TIMES , datepart(WEEK,pdate)AS PWEEK
 FROM pur
 GROUP BY prd_id,datepart(WEEK,pdate)
 )
 SELECT PRD_ID,SUM(NO_OF_CUST) AS NO_OF_CUST, NO_OF_TIMES FROM CTE
 GROUP BY PRD_ID,NO_OF_TIMES,PWEEK

Query Compilation Process

SQL Server query optimiser is a cost based optimiser, which means that is tried to come up with a low cost execution plan for each SQL statement. Each execution plan has associate cost in term of the amount of computing resource used. The query optimiser analyses the entire possible plan and chooses one of the lowest estimated cost plans. Some time a complex SELECT statement has thousands of possible plans in that case optimiser will not compile the entire possible plan instead it tried to find an execution plan that has a cost reasonably close to the theoretical cost. 

       Parsing process just convert statement into system understandable format, after that Algebrizer validate the statement and make sure that  referenced object , column do exists and the statement is valid to process the data. Then algebrized expression tree will compiled by the query optimiser. After that execution plan generated, it is placed in cache and then execute. 

MultiServer Query

Querying Data from multi server simultaneously

    How nice if you can do the multi-server query. You can query from different database that are sitting on different server and get result together. For example if you want to know how many database are running under less compatibility level on my all the environment (development, QA, staging), or you want to know what all jobs are failing in my environments. Let’s have fun below J

·         How to do that?

 

1.       To do this first we need to register the server in “Central Management Server” in register server menu.  Open SSMS go to View menu and click on Register server option. Now new

2.       Now right click on the “Central Management Server” and select “Register Central Management Server” now give the credential for the base server where this configuration will store, and give the name of this central management server.

3.       Now right click on this newly register server and select option “New server registration” and give the all credential, similarly you can register many server that you need to query.

4.       Then right click on the central registered server and select new query. Now your query can get data from all register server in one result set.

  

·          I want to know how many of jobs are failing in my all the environment   :

; WITH CTE AS (
SELECT CASE @@servername WHEN 'EDQASSIS' THEN 'QA' WHEN 'EDFSSIS'
THEN 'Development' ELSE ''END [server name]
,j.name,js.step_Name
,CASE js.run_Status WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3
THEN 'Canceled' END AS RunStatus,
CAST(CONVERT(VARCHAR(8)
,CAST(js.run_Date AS VARCHAR(20))+cast(js.run_time AS VARCHAR(20)) )
AS DATETIME)JobRunDate
FROM MSDB..SYSJOBS J INNER JOIN MSDB..SYSJOBHISTORY JS ON j.job_id=js.job_id
)
SELECT * FROM cte WHERE runstatus='Failed'  

·         I want to know the compatibility level, recovery model of all the database of my all the environment.

SELECT @@servername,Name

,case Compatibility_level when 70 then 'SQL Server 2008 through SQL Server 2008 R2'

when 80 then 'SQL Server 2008 through SQL Server 2008 R2'

when 90 then 'SQL Server 2008 through SQL Server 2012'

when 100 then 'SQL Server 2008 through SQL Server 2016 and Azure SQL Database'

when 110 then 'SQL Server 2012 through SQL Server 2016 and Azure SQL Database'

when 120 then 'SQL Server 2014 through SQL Server 2016 and Azure SQL Database'

when 130 then 'SQL Server 2016 Community Technology Preview 2 (CTP2) through SQL Server 2016' end

as CompatibilityLevel,

 CASE  recovery_model WHEN 1 THEN 'FULL' WHEN 2 THEN 'BULK LOGGED' WHEN 3 THEN 'SIMPLE' END AS RecoveryModel FROM SYS.DATABASES

 

References : https://msdn.microsoft.com/en-us/library/bb964743.aspx