With Statement in SQL

-- With statement use in incorporating playcount and join tables with songs.

With play_count AS (SELECT song_id,

   COUNT(*) AS 'times_played'

FROM plays

GROUP BY song_id)

Select songs.title, songs.artist, play_count.times_played from songs join play_count on play_count.song_id = songs.id;

--Delete when students cannot enrolled 2 courses at the same time.

DELETE FROM statistics_students
WHERE id in (
  SELECT id
  FROM history_students);

--Subquery sample to find the number of students from drama = the same grade as a particular student with the id =20

select * from drama_students where grade = (select grade from band_students where id=20)

-- To find student details from band who are not in drama club.

select first_name, last_name from band_students where id NOT IN

(select id from drama_students);

-- to find out the grades that are in both band and drama club.

select grade from band_students where exists (select grade from drama_students);

-- to find the top 2 , always think of order in desc + limit, mssql can use top 2

select * from cars order bytrip_completed limit 2;