the topic of SQL DML commands for manipulating data

Truncate vs delete:

Types of insert:

## 2  ways of inserting values into tables;
insert into students values(4, 'Jim', null, 56);
## no need to add null;
insert into students (std_id, sname, marks) values (5,'tim', 77);

Constraints:

4 types of constraints while creating 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)
Parent’s email (Should be unique)
Address (If no address is mentioned then it should show “-“)

Update multiple records:

Update exercise:

Mode of working Update cmd:

Find all employees who were hired after year 2000:

To add new column to the table with alias name:

Distinct values from a column:

Find all employees who were hired after year 2000:

Querying schema:

Left Join:

select mv.id, mv.title, mm.first_name, mm.last_name
from movies as mv
left join members as mm 
on mm.movieid=mv.id;

Cross join:

select m.mealname, m.rate, d.drinkname, 
from meals as m 
cross join drinks as d;

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