Details for Devender

use WorldEvents

declare @EarliestDate date =

( select min(eventdate) from tblEvent)

declare @latestdate date = (

select max(eventdate) from tblEvent)

declare @NumberOfEvents int = (

select COUNT(*) from tblEvent e)

declare @EventInfo varchar(50)= 'Summary of events'

select

@EventInfo as 'tilte',

@EarliestDate as 'Earliset Date',

@LatestDate as 'Latest Date',

@NumberOfEvents as 'Number of Events'

use WorldEvents

--using group by

select c.countryname from tblCountry c

join tblEvent e

on c.CountryID=e.CountryID

group by CountryName

HAVING COUNT(e.EventID) > 8

-- using sub queri

select c.countryname

from tblCountry c

where (select COUNT(e.countryid)

from tblEvent e

where e.CountryID = c.CountryID

group by e.countryid) > 8

order by CountryName

use DoctorWho

select a.AuthorName , count(*) as episode,min(e.EpisodeDate) as[earliest date], max(e.EpisodeDate) as[latest date]

from tblAuthor a join tblEpisode e

on a.AuthorId = e.AuthorId

group by AuthorName

order by episode desc

select eventname,

case

when left(eventname,1) in ('a','e','i','o','u') and right (eventname , 1) in ('A','E','I','O','U')

THEN 'Begins and with Vowels'

when left(eventname,1) like right(eventname,1) then 'same letter'

end as 'Verdicts'

from tblEvent

where (case

when left(eventname,1) in ('a','e','i','o','u') and right (eventname , 1) in ('A','E','I','O','U')

THEN 'Begins and with Vowels'

when left(eventname,1) like right(eventname,1) then 'same letter'end ) is not null

use WorldEvents

SELECT Concat(EventName+' '+ 'category'+' (' , CategoryID , ') '),EventDate

FROM tblEvent

WHERE CountryID = 1

use WorldEvents

select count(*) as [Number Of Events],max(eventdate) as [Last Date],

min(eventdate) as [First Date]

from tblEvent

/*Use this to list the names of the companions who haven't

featured in any episodes. You should find there's one of them,

but we won't spoil the surprise by saying who it is!

*/

use DoctorWho

select cn.companionname --et.episodetype

from

tblCompanion cn full outer join tblEpisodeCompanion ci

on cn.CompanionId= ci.CompanionId

full outer join tblEpisode et

on et.EpisodeId=ci.EpisodeId

where episodetype is null

/*Write a query using inner joins to show all of the authors who have written episodes featuring the Daleks.

You may find the following relationship diagram useful to refer to:

*/

select a.authorname , t.title, e.enemyname from tblAuthor a

inner join tblEpisode t on a.AuthorId= t.authorid

inner join tblEpisodeEnemy ee on ee.EpisodeId=t.EpisodeId

inner join tblEnemy e on e.EnemyId=ee.EnemyId

where EnemyName ='Daleks'

/*Use this nugget of information to create a query listing out each event with the length of its name,

with the "shortest event" first:*/

select eventname, len(eventname) as [Length of name] from tblevent

order by len(eventname)

/*1 Events which aren't in the Transport category (number 14),

but which nevertheless include the text Train in the EventDetails column. 4 rows

*/

select * from tblEvent

where CategoryID != 14 and EventDetails like '%train%'

/*2 Events which are in the Space country (number 13),

but which don't mention Space in either the event name or the event details columns. 6 rows*/

select * from tblEvent

where CategoryID =13 and EventDetails not like '%space%' and eventname not like '%space%'

/*3 Events which are in categories 5 or 6 (War/conflict and Death/disaster),

but which don't mention either War or Death in the EventDetails column. 91 rows

Save this query listing possible anomalies as Primeval, then close it down.*/

select * from tblEvent

where CategoryID in (5,6) and EventDetails not like '%war%' and EventDetails not like '%death%'

--Create a query which lists out all of the tblEvent events which include the word Teletubbies:

select eventname ,eventdate from tblevent

where eventname like '%Teletubbies%'

/*Now add an OR condition to your query so that it lists out all events whose:

Name includes Teletubbies; or

Name includes Pandy.

This should give you two rows:

*/

select eventname , eventdate from tblevent

where eventname like '%Teletubbies%' or eventname like '%pandy%'

select eventname , eventdetails, eventdate from tblevent

where ( countryid in (8,22,30,35)

or

categoryid = 4

or

eventdetails like '% water %')

and

eventdate >= '1970-01-01'

--Create a query which lists out all of the events which took place in February 2005:

select eventname as [what] ,eventdate as [when] from tblevent

where eventdate between '2005-02-01' and '2005-02-28'

create table table_event(

Name_Event VARCHAR(20) , DATE_OF_EVENT DATE)

insert into table_event(Name_Event , DATE_OF_EVENT)

values('Chess' , '2024-03-26'),

('football' , '2024-01-10'),

('cricket', '2024-02-07'),

('basketball', '2024-04-13'),

('hockey', '2024-03-23');

select * from table_event

order by DATE_OF_EVENT