4. Data Manipulation (DML)

Question 1

Insert 2 rows into the persons table, having columns: id (not null), first_name (not null), country and score.

Answer 1

INSERT INTO customers
(id, first_name, country, score)
VALUES 
(6, 'Sahil', 'India', NULL),
(7, 'Prince', NULL, 92)

Question 2

Insert data from ‘customers’ table into ‘persons’ table.
persons: id (not null), person_name(not null), birth_date, phone (not null)
customers: id, first_name, country, score

Answer 2

insert into persons
select id, first_name, NULL, 'NA'
from customers

Question 3

Create table and insert data from another table in a single statement in Snowflake

Answer 3

CREATE TABLE region 
AS SELECT * FROM...

Question 4

Change the score of customer with id 6 to zero.

Answer 4

update customers
set score = 0
where id = 6;

If we skip where clause, score of all customers will be changed to 0.

Question 5

Change the country to UK and score to 0, for the customer with id 10.

Answer 5

update customers
set score=0, country='UK'
where id = 10;

Question 6

SQL command to Insert, update or delete values in customers table that are based on values in a staging_customers table or a subquery.

Answer 6

Merge

merge into customers as tgt
  using staging_customers as src
  on customers.id = staging_customers.id
  
  when matched and src.op = 'u' then
    update set
      tgt.name = src.name,
      tgt.email = src.email,
      tgt.updatedAt = CURRENT_DATE()
  
  when not matched and src.op='c' then
    insert (id, name, email, updatedAt)
    values (src.id, src.name, src.email, current_date())

Merging can be useful if the second table is a change log that contains new rows (to be inserted), modified rows (to be updated), or marked rows (to be deleted) in the target table.

Question 7

You want to sync your inventory table with the latest stock levels from the warehouse system. If the product from warehouse_stock exists in inventory, update its quantity. If not, add it to inventory.

inventory: product_id, product_name, quantity.

warehouse_stock: product_id, product_name, quantity, updated_at.

Answer 7

merge into inventory as inv
using warehouse_stock as whs
on inv.product_id = whs.product_id
when matched then
update set inv.quantity = whs.quantity
when not matched then
insert (product_id, product_name, quantity)
values (whs.product_id, whs.product_name, whs.quantity);

Question 8

Delete all customers with score 0.

Answer 8

delete from customers
where score = 0;

Question 9

Truncate is faster than delete because _.

Answer 9

it skips checking and logging

Question 10

delete all data from table persons.

Answer 10

truncate table persons;

Question 11

In PostgreSQL,
USING lets you reference another table (customers) to decide which rows in orders to update/delete.

Answer 11

DELETE FROM target_table
USING other_table_or_subquery
WHERE target_table.col = other_table_or_subquery.col;