SQL Query Optimization Techniques

Slide Note

Learn advanced SQL techniques including subqueries, inserts, updates, joins, and data manipulation to enhance the performance of your database queries efficiently.

sfrai Follow

Uploaded on Sep 15, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

E N D

Presentation Transcript

SQL Part3

Advance SQL - Sub Query - Select in - Insert in to (Select ) - Update Set (Select..)

Sub Query SELECT column-names FROM table-name1 WHERE value IN (SELECT column-name FROM table-name2 WHERE condition) https://www.dofactory.com/s ql/subquery

Insert into INSERT INTO table2 (column1, column2, col umn3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition; INSERT INTO table2 SELECT * FROM table1 WHERE condition;

Insert intoSelect insert into Product(Product_Id, Product_NameThai) select Product_Id, Product_NameThai from T_Product insert into T_Product select * from Product

SELECT column-names FROM table-name1 WHERE value IN (SELECT column-name FROM table-name2 WHERE condition) SELECT ProductName FROM Product WHERE Id IN (SELECT ProductId FROM OrderItem WHERE Quantity > 100)

Update Sale_detail set Sale_detail.unit_price =(select price from Product where Sale_detail.productcode=Product.Productid)

Join Table select * from EX_SaleA,EX_Sale_DetailB where A.Receipt_No=B.Receipt_No

Step1. delete from Stage_SaleData step2. insert into Stage_SaleData(Receipt_No,Date,Cust_id,Branch_No, Product_Id,Total_Amount) Select A.Receipt_No, A.Date, A.Cust_Id,A.Branch_No,B.Product_Id,B.Total_Amount from TPS_Sale A, TPS_Sale_Detail B where A.Receipt_No = B.Receipt_No order by Receipt_No asc Step3. Select * from Stage_SaleData

Step 1. delete from sales Step 2. insert into Sales select Branch_No,Product_Id,Date, SUM(Total_amount) from Stage_SaleData group by DATE,Product_Id,Branch_No Step 3. Select * from Sales

Join TableWhere SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID; SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders, Customers Where Orders.CustomerID=Customers.CustomerID;