the topic of SQL DML commands for manipulating data

Truncate vs delete:

2 types of Insert into students table:

Create a students table with following fields and constraints.

Roll_No (No null values + should be unique)
Name (No Null values accepted)
Age (Should accept only values ranging 5 to 15)
Parents_Email (Should be unique)
Address (If no address is mentioned then it should show “-“)

4 types of constraints while creating table:

Update multiple records:

Update exercise:

Mode of working Update cmd:

Querying schema:

Inner Join:

Use classicmodels. Show the total sales done by each customer using below tables and sort it by highest to lowest sales.

Sales = SUM(priceEach * quantityOrdered)

Customers
Orders
Orderdetails

day2_insert_table_practice Download

practice-tables-assignment Download

Full outer join:

select
 c.customernumber, c.customername, count(o.ordernumber) as Total_orders
from
 customers c
left join 
 orders o 
on 
 c.customerNumber = o.customerNumber
group by 
 c.customernumber, c.customername
 
union

select
 c.customernumber, c.customername, count(o.ordernumber) as Total_orders
from
 customers c
Right join 
 orders o 
on 
 c.customerNumber = o.customerNumber
group by 
 c.customernumber, c.customername;

TCL commands:

Write the insert query, update query and delete query under Start Transaction. Use savepoint for each transaction. Check Rollback and Rollback to savepoint