SQL Basics Vol. 2

WHAT? There's more?

What we already covered

  1. select and where
  2. distinct
  3. sub query
  4. join
  5. group by and having
  6. aggregations
  7. order by
  8. limit offset
  9. views and stored procedures
  10. null vs blank vs like
  11. cast
  12. built in functions

What we'll cover today

  1. ​create table
  2. drop table
  3. insert records
  4. update records
  5. delete records
  6. truncate table
  7. case statements
  8. select into
  9. temp tables
  10. union
  11. Misc. queries

create table

create table trainings2020 ( --id int NOT NULL AUTO_INCREMENT, -- mysql id int IDENTITY(1,1) PRIMARY KEY, training_date datetime, topic varchar(100) NOT NULL, attendee_count int, avg_feedback_score float, notes_provided boolean ) --Check later CREATE TABLE trainingsArchive AS SELECT training_date, topic FROM trainings2020

alter table

alter table trainings2020 add duration_in_hours int alter table trainings2020 alter column topic varchar(500)

drop table

drop table trainings2020

insert records

insert into training2020 ( training_date, topic, attendee_count, avg_feedback_score, notes_provided) values ( '2020-02-28', 'SQL Basics Vol. 1', 45, 9.98, 0)

update records

update training2020 set attendee_count = 46, avg_feedback_score = 9.96 where topic = 'SQL Basics Vol. 1' update t set notes_provided = 1 from training2020 t join trainingArchive a on a.topic = t.topic where a.training_date < '2020-03-01'

delete records

delete from training2020 where training_date < '2020-03-01' delete from trainingArchive

truncate table

truncate table training2020

case statements

select topic, case when training_date >= '2020-03-01' then 'This financial year' when training_date >= '2019-03-01' and training_date <= '2020-03-01' then 'Last financial year' else 'Long ago' end from training2020

select into

select topic, training_date into training2021 from training2020 where topic = 'SQL Basics Vol. 1' select * into training2021 from training2020

temp tables

select * into #training2021 from training2020​ --check create table #training2022 ( training_date datetime, topic varchar(100) NOT NULL, attendee_count int, avg_feedback_score float, notes_provided boolean )

union

select topic, training_date from training2020 union select topic, training_date from #training2021 select topic, training_date from training2020 union all select topic, training_date from #training2021

Was this training useful?

Yes

No

Only time will tell

Go to deckdeckgo.com/poll and use the code {0}
Awaiting votes

Thanks

Bye