Details for Bhavya59

CREATE PROC BirthYear

    @FirstDate DATETIME,

    @LastDate DATETIME

AS

SELECT

               EventName,

               EventDate,

               CountryName

FROM tblEvent e

               INNER JOIN tblCountry c ON e.CountryID=c.CountryID

               WHERE EventDate BETWEEN @FirstDate AND @LastDate

EXEC BirthYear @FirstDate='01 jan 1991',@LastDate='31 dec 1991'

SELECT EventName

,EventDetails

FROM tblEvent e

INNER JOIN tblCountry c ON e.CountryID=c.CountryID

INNER JOIN tblCategory ca ON e.CategoryID=ca.CategoryID

WHERE e.CountryID NOT IN(

SELECT TOP 30 CountryID FROM tblCountry

ORDER BY CountryName DESC

)

AND e.CategoryID NOT IN(

SELECT TOP 15 CategoryID FROM tblCategory

ORDER BY CategoryName DESC

)

SELECT [CountryName]

  FROM tblCountry c

               WHERE (

                              SELECT COUNT(*) FROM tblEvent e

                              WHERE e.CountryID=c.CountryID

               )>8

               ORDER BY CountryName

SELECT CASE

                              WHEN EventDate>='1701-01-01' AND EventDate<'1801-01-01' THEN '18th Century'

                              WHEN EventDate>='1801-01-01' AND EventDate<'1901-01-01' THEN '19th Century'

                              WHEN EventDate>='1901-01-01' AND EventDate<'2001-01-01' THEN '20th Century'

                              WHEN EventDate>='2001-01-01' THEN '21th Century'

                              ELSE ''

      END AS Century

                 ,COUNT(EventID)

  FROM tblEvent

  GROUP BY CUBE((CASE

                              WHEN EventDate>='1701-01-01' AND EventDate<'1801-01-01' THEN '18th Century'

                              WHEN EventDate>='1801-01-01' AND EventDate<'1901-01-01' THEN '19th Century'

                              WHEN EventDate>='1901-01-01' AND EventDate<'2001-01-01' THEN '20th Century'

                              WHEN EventDate>='2001-01-01' THEN '21th Century'

                              ELSE ''

      END))

SELECT SUBSTRING(CategoryName, 1, 1) AS CategoryInitial

               ,COUNT(EventID) AS 'No of Events'

               ,AVG(CAST(LEN(EventName) AS DECIMAL(5,2))) 'Avg event name length'

  FROM tblEvent e

               INNER JOIN tblCategory c ON e.CategoryID=c.CategoryID

               GROUP BY SUBSTRING(CategoryName, 1, 1)

SELECT ContinentName

      ,CountryName

                 ,COUNT(EventID) AS 'No of Events'

  FROM tblCountry c

               INNER JOIN tblContinent c1 ON c.ContinentID=c1.ContinentID

               INNER JOIN tblEvent e ON c.CountryID=e.CountryID

               WHERE ContinentName!='Europe'

               GROUP BY ContinentName,CountryName

               HAVING COUNT(EventID)>5

SELECT AuthorName

               ,DoctorName

               ,COUNT(EpisodeId) AS 'Episodes'

  FROM tblEpisode e

  INNER JOIN tblAuthor a

               ON e.AuthorId=a.AuthorId

  INNER JOIN tblDoctor d

               ON e.DoctorId=d.DoctorId

               GROUP BY AuthorName, DoctorName

               HAVING COUNT(EpisodeId)>5

               ORDER BY Episodes DESC

SELECT a.AuthorName

               ,COUNT(EpisodeId) AS 'No of Episode'

               ,MIN(EpisodeDate) AS 'Earliest Date'

               ,MAX(EpisodeDate) AS 'LATEST Date'

  FROM tblEpisode e INNER JOIN tblAuthor a

               ON e.AuthorId=a.AuthorId

               GROUP BY AuthorName

               ORDER BY [No of Episode] DESC

SELECT c.CategoryName AS Categories

               ,COUNT(c.CategoryID) AS TotalEvents

               FROM tblCategory c LEFT JOIN tblEvent e

                              ON c.CategoryID=e.CategoryID

               GROUP BY c.CategoryName

               ORDER BY TotalEvents DESC

SELECT [CompanionName]

,EpisodeId

FROM tblCompanion c LEFT OUTER JOIN tblEpisodeCompanion ec

ON c.CompanionId=ec.CompanionId

WHERE EpisodeId IS NULL

SELECT [Title]

,AuthorName

,DoctorName

,EnemyName

,LEN(Title)+ LEN(AuthorName) + LEN(DoctorName) + LEN(EnemyName) AS TotalLength

FROM tblEpisode ep, tblAuthor a, tblDoctor d, tblEnemy en, tblEpisodeEnemy ee

WHERE

ep.AuthorId=a.AuthorId

AND ep.DoctorId=d.DoctorId

AND ep.EpisodeId=ee.EpisodeId

AND ee.EnemyId=en.EnemyId

AND (LEN(Title)+ LEN(AuthorName) + LEN(DoctorName) + LEN(EnemyName))<40

SELECT [Title]

,EnemyName

,AuthorName

FROM tblEpisode ep, tblEpisodeEnemy ee, tblEnemy en, tblAuthor a

WHERE

ep.EpisodeId=ee.EpisodeId AND ee.EnemyId=en.EnemyId AND ep.AuthorId=a.AuthorId

AND EnemyName='Daleks'

SELECT [EventName]

,[EventDate]

,DATENAME(WEEKDAY,EventDate)+' '+DATENAME(DAY,EventDate)+

CASE

WHEN DATEPART(DAY,EventDate) IN (1,21,31) THEN 'st '

WHEN DATEPART(DAY,EventDate) IN (2,22) THEN 'nd '

WHEN DATEPART(DAY,EventDate) IN (3,23) THEN 'rd '

ELSE 'th '

END +

DATENAME(MONTH,EventDate)+' '+DATENAME(YEAR,EventDate) AS 'FullDate'

FROM [WorldEvents].[dbo].[tblEvent]

ORDER BY EventDate

SELECT [EventName]

,CASE

WHEN EventName LIKE '[AEIOUaeiou]%' AND EventName LIKE '%[AEIOUaeiou]' THEN 'begin and end with a vowel'

WHEN LEFT(EventName, 1) = RIGHT(EventName, 1) THEN 'Same Letter'

ELSE 'Boring events'

END

AS Verdict

FROM [WorldEvents].[dbo].[tblEvent]

SELECT [CountryName]

,CASE

WHEN ContinentID=1 OR ContinentID=3 THEN 'Eurasia'

WHEN ContinentID=5 OR ContinentID=6 THEN 'Americas'

WHEN ContinentID=2 OR ContinentID=4 THEN 'Somewhere hot'

WHEN ContinentID=7 THEN 'Somewhere cold'

ELSE 'Somewhere else'

END

AS CountryLocation

FROM [WorldEvents].[dbo].[tblCountry]

ORDER BY CountryLocation DESC

SELECT TOP (1000) [ContinentID]

,[ContinentName]

,[Summary]

--USING ISNULL

,ISNULL(Summary,'No Summary') AS UsingISNULL

--USING CASE

,CASE

WHEN Summary IS NULL THEN 'No Summary'

ELSE Summary

END

AS UsingCASE

--Using COALESCE

,COALESCE (Summary,'No Summary') AS UsingCOALESCE

FROM [WorldEvents].[dbo].[tblContinent]

SELECT

EventName+' (Category'+Cast(CategoryID AS nvarchar)+')' AS 'Event(Category)'

,[EventDate]

FROM [WorldEvents].[dbo].[tblEvent]

WHERE (CategoryID=5 OR CategoryID=6) AND CountryID=1

SELECT [EventName]

,LEN(EventName) AS [Length of Name]

FROM [WorldEvents].[dbo].[tblEvent]

ORDER BY [Length of Name]

SELECT EventName, EventDetails, FORMAT(EventDate, 'yyyy-MM-dd') Date

FROM tblEvent

WHERE (CountryID IN (4, 8, 22, 30, 35) OR EventDetails LIKE '%Water%')

AND EventDate>='1970-01-01'