SQL Query Optimization Techniques

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 into…Select…
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_Sale A,EX_Sale_Detail B
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 Table…Where
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;
LAB
1. Select * from T_product
2. Delete from T_product
3. Import Excel 
ใน  
Web
4. Rename from “Product
$
”  to be “Product”
   5. delete
 from T_Product
   6. insert
 into T_Product
       select
 * from Product
LAB
Reference
https://www.w3schools.com/sql/sql_join.asp
Set SQL Server
การ
Set 
ให้แก้ไข 
Design 
ได้
1. Click Tool>>>>option
การ
Set 
ให้แก้ไข 
Design 
ได้
2. Set Property
Set Font
Slide Note
Embed
Share

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


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


  1. SQL Part3

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

  3. 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

  4. 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;

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

  6. 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)

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

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

  9. 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

  10. 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

  11. 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;

  12. LAB 1. Select * from T_product 2. Delete from T_product 3. Import Excel Web 4. Rename from Product$ to be Product

  13. LAB 5. delete from T_Product 6. insert into T_Product select * from Product

  14. Reference https://www.w3schools.com/sql/sql_join.asp

  15. Set SQL Server

  16. Set Design 1. Click Tool>>>>option

  17. Set Design 2. Set Property

  18. Set Font

More Related Content

giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#