SQL Basics Vol 1

SQL Basics Vol. 1

What is this?

What we'll cover today

    1. what is DDL, DML?
    2. select and where
    3. sub query
    4. joins
    5. group by and having
    6. aggregations - count, sum, max
    7. order by
    8. limit offset 
    9. create table
    10. alter table
    11. drop table
    12. insert records
    13. update records
    14. delete records
    15. truncate table

what is DDL, DML?

    1. DDL: DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema.
    Examples of DDL commands: 
    CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers).
    DROP – is used to delete objects from the database.
    ALTER-is used to alter the structure of the database.

    2. DML(Data Manipulation Language): The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements. 
    Examples of DML commands: 
    INSERT – is used to insert data into a table.
    UPDATE – is used to update existing data within a table.
    DELETE – is used to delete records from a database table.

select and where

select * from `transaction` select * from `transaction` limit 10 select fk_id_instrument from `transaction` select fk_id_instrument from `transaction` where total_amount > 100000 select distinct fk_id_instrument from `transaction` where total_amount > 100000

sub query

select distinct `name` from instrument where id in ( select fk_id_instrument from `transaction` where total_amount > 100000 )

join

select distinct p.name from `transaction` t join instrument i on i.id = t.fk_id_instrument join product p on p.id = i.fk_id_product where total_amount > 100000

group by and having

select p.name, sum(total_amount) from `transaction` t join instrument i on i.id = t.fk_id_instrument join product p on p.id = i.fk_id_product where total_amount > 100000 group by p.name select p.name, sum(total_amount) from `transaction` t join instrument i on i.id = t.fk_id_instrument join product p on p.id = i.fk_id_product where total_amount > 100000 group by p.name having sum(total_amount) > 50000000000

order by

select p.name, sum(total_amount) from `transaction` t join instrument i on i.id = t.fk_id_instrument join product p on p.id = i.fk_id_product where total_amount > 100000 group by p.name having sum(total_amount) > 50000000000 order by sum(total_amount) desc

limit offset

select p.name, sum(total_amount) from `transaction` t join instrument i on i.id = t.fk_id_instrument join product p on p.id = i.fk_id_product where total_amount > 100000 group by p.name having sum(total_amount) > 500000000 order by sum(total_amount) desc limit 2 offset 2

create table

create table trainings2021 ( id int NOT NULL, training_date datetime, topic varchar(100) NOT NULL, attendee_count int, avg_feedback_score float, notes_provided boolean ) create table trainings_archive as select * from trainings2021;

alter table

alter table trainings2021 add duration_in_hours int alter table trainings2021 modify column topic nvarchar(1000)

drop table

drop table trainings2021

insert records

insert into trainings2021 ( id, training_date, topic, attendee_count, avg_feedback_score, notes_provided) values ( 1, '2021-07-12', 'SQL Basics Vol. 1', 45, 9.98, 0)

update records

update trainings2021 set attendee_count = 46, avg_feedback_score = 9.96 where topic = 'SQL Basics Vol. 1'

delete records

delete from trainings2021 where training_date < '2020-03-01' delete from trainings_archive

truncate table

truncate table trainings2021

Was this useful?

Yes

No

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

Thanks

Bye