Mastering SQL Commands: Select, Where, Distinct, Operators, and More
Unleash the power of SQL commands with this comprehensive guide covering SELECT queries, WHERE conditions, DISTINCT identifiers, various operators like equal to, greater than, less than, AND, OR, and NOT logical operations. Learn how to fetch data efficiently from tables and apply filters to meet your querying needs.
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. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
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.
E N D
Presentation Transcript
Select Table Field name SELECT column1, column2, ... FROM table_name; Field SELECT * FROM table_name SELECT Product_id, Product_nameEng from T_Product SELECT * FROM T_Product
Select >> From >> Where >> Order by Select >> From >> Where >> group by
SELECT DISTINCT Distinct() SELECT DISTINCT column1, column2, ... FROM table_name; SELECT DISTINCT(Receipt_no) from T_Sales_Detail
SELECT DISTINCT select Receipt_no from T_Sales_Detail SELECT DISTINCT(Receipt_no) from T_Sales_Detail Receipt_No Receipt_No F16012019G300001 F16012019G300001 F16012019G300001 F16012019G300001 F16012019G300001 F16012019G300002 F16012019G300002 F16012019G300002 F17012019G300001 F17012019G300001 F17012019G300001 F17012019G300002 F17012019G300002 F17012019G300002 F16012019G300001 F16012019G300002 F17012019G300001 F17012019G300002
Where SELECT column1, column2, ... FROM table_name WHERE condition SELECT column1, column2, ... FROM table_name WHERE condition1 and condition2 and condiion3 SELECT column1, column2, ... FROM table_name WHERE condition1 or condition2 or condition 3
= > < >= <= <> Equal to Greater than Less than Greater than or equal to Less than or equal to Not equal to BETWEEN LIKE IN
Where select Product_Id,Product_NameEng, Unit_Price,Product_Type_Id from T_Product where Unit_Price >=3000 select Product_Id,Product_NameEng, Unit_Price,Product_Type_Id from T_Product where Unit_Price =3000
And, Or, Not SELECT column1, column2, ... FROM table_name WHERE condition1 and condition2 and condiion3 SELECT column1, column2, ... FROM table_name WHERE condition1 or condition2 or condition 3 SELECT column1, column2, ... FROM table_name WHERE NOT condition; Or = And = Not =
And, Or, Not selectProduct_Id,Product_NameThai, Unit_Price,Product_Type_Id fromT_Product whereUnit_Price>50 orProduct_Type_Id='PT006' select Product_Id,Product_NameThai, Unit_Price,Product_Type_Id from T_Product where Unit_Price >50 and Product_Type_Id='PT006' Product_Id G001 G002 G003 G004 G006 G007 G008 G009 G011 G012 G013 G014 G015 G016 G017 G018 G019 Product_NameThai Unit_Price Product_Type_Id PT001 PT009 PT006 PT006 PT008 PT006 PT009 PT006 PT002 PT006 PT004 PT008 PT006 PT008 PT009 PT008 PT008 60 200 20 10 Product_Id Product_NameThai G012 G015 Unit_Price Product_Type_Id PT006 PT006 190 60 20000 40 150 20 200 190 290 3000 60 2000 60 30000 20000
And, Or, Not select Product_Id,Product_nameEng from T_Product where not Product_nameEng ='Beer' select Product_Id,Product_nameEng , Unit_Price from T_Product where Unit_Price >10 and (Product_nameEng ='Beer' or Product_nameEng='Coke')
Order by Desc|Asc Order by Desc Asc SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
Order by Desc|Asc select Product_Id,Product_nameEng,Unit_price from T_Product order by unit_price desc select Product_Id,Product_nameEng,Unit_price from T_Product order by unit_price asc
INSERT SQL Command Description Field Field Table INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...) Field name Field Field Table INSERT INTO table_name VALUES (value1, value2, value3, ...)
INSERT SQL Command Description ( Field Table) insert into Color(Color_Id,Color_Desc) values('CL006','Black ) 1 Field Field name ( Field Table) Filed name Field insert into Color(Color_Id) values('CL010') insert into Color values('CL007','Yellow ) ERROR insert into Color values('CL009 )
Null, Not Null SQL Command Description Null SELECT column_names FROM table_name WHERE column_name IS NULL Null SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
Null, Not Null SQL Command Description Null select * from Brand where brand_name is null Null select * from Brand where brand_name is not null
Update UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; Update TableName Set FieldName1=Value1, FieldName2=value2, [Where condition]
Update Update T_Product Set Product_NameEng='Water' (!!! Table) Data Type Varchar,char Update T_Product Set Product_NameEng='Water', Product_NameThai=' ', Unit_Price=70 Where Product_id='G001'
Delete DELETE FROM table_name WHERE [condition] SQL Command Description (!!! Table) Delete From T_Product Delete From T_Product Where Product_id='G001'
Delete SQL Command Description (!!! Table) Delete From TPS_Product Delete From TPS_Product Where Product_id='G001'
Select Top SELECT TOP number|percent column_name(s) FROM table_name WHERE condition; SELECT column_name(s) FROM table_name WHERE condition LIMIT number; **** Run
Select Top SELECT TOP 3 * FROM T_Product SELECT TOP 50 PERCENT * FROM T_Product
Select Function Select Min ( ), Select Max ( ), Select AVG ( ), Select Count ( ), Select Sum ( )
Select MIN, MAX SELECT MIN(column_name) FROM table_name WHERE condition; SELECT MAX(column_name) FROM table_name WHERE condition;
Select MIN, MAX select MIN(Unit_price) from T_Product select MAX(Unit_price) from T_Product select MAX(Unit_price) from T_Product where product_type_id='PT001'
Select Count, AVG, SUM SELECT COUNT(column_name) FROM table_name WHERE condition SELECT AVG(column_name) FROM table_name WHERE condition SELECT SUM(column_name) FROM table_name WHERE condition
Select Count, AVG, SUM Product_Id Product_NameEng Unit_Price Product_Type_Id G001 Beer G005 Coke G010 Green Tea G020 Water G011 Make up G013 Medicine G012 Meat G015 milk G007 Cookie G003 Bread G004 Candy G009 Egg G006 Computer G016 Printer G018 Telephone G019 Television G014 Microwave G017 Shampoo G008 Diaper G002 Blanket 60 12 20 12 PT001 PT001 PT001 PT001 PT002 PT004 PT006 PT006 PT006 PT006 PT006 PT006 PT008 PT008 PT008 PT008 PT008 PT009 PT009 PT009 select COUNT(Product_Id) from T_Product where product_type_id='PT001' 200 290 190 60 40 20 10 20 select AVG(unit_price) from T_Product where product_type_id='PT001' 20000 2000 30000 20000 3000 select sum(unit_price) from T_Product where product_type_id='PT001 60 150 200
LIKE SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;
LIKE LIKE Operator Description WHERE CustomerName LIKE 'a%' Finds any values that start with "a" WHERE CustomerName LIKE '%a' Finds any values that end with "a" WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position WHERE CustomerName LIKE 'a__%' Finds any values that start with "a" and are at least 3 characters in length WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o"
LIKE select Product_Id,Product_nameEng, Unit_price,Product_Type_Id from T_Product where Product_NameEng like'B% >>>> ( B) select Product_Id,Product_nameEng, Unit_price,Product_Type_Id from T_Product where Product_NameEng like'%B%' >>>> ( B ) select Product_Id,Product_nameEng, Unit_price,Product_Type_Id from T_Product where Product_NameEng like'%B' >>>> ( B)
Select Where value in SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...); SELECT column-name FROM table-name1 WHERE column-name IN (SELECT STATEMENT) https://www.dofactory.com/s ql/subquery
Select Where value in select Product_Id,Product_nameEng, Unit_price,Product_Type_Id from T_Product where Product_NameEng in ('Beer','Blanket','Bread') select Product_Id,Product_nameEng, Unit_price,Product_Type_Id from T_Product where Product_NameEng not in ('Beer','Blanket','Bread')
Select Where value in SELECT column-names SELECT * FROM table-name1 WHERE value IN (SELECT column-name FROM table-name2 WHERE condition) select * from T_Sales_detail where Product_Id in (Select Product_Id from T_Product where Product_Type_Id ='PT001') order by Receipt_No asc
Select Where value in select * from T_Sales_detail where Product_Id in (Select Product_Id from T_Product where Product_Type_Id ='PT001') order by Receipt_No asc select A.Receipt_no,A.Product_Id,A.Product_nameEng, A.Qty,A.Unit_Price,A.Total_amt,B.Product_Type_Id from T_Sales_detail A, T_Product B where B.Product_Type_Id ='PT001' and A.Product_Id=B.Product_Id order by Receipt_No asc
Between SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
Between select Product_Id,Product_nameEng, Unit_price,Product_Type_Id from T_Product where Unit_price between 20 and 50 Between And A,B select Product_Id,Product_nameEng, Unit_price,Product_Type_Id from T_Product where Unit_price >= 20 and Unit_price<=50
Not Between select Product_Id,Product_nameEng, Unit_price,Product_Type_Id from T_Product where Unit_price not between 20 and 50
Alias (): Alias Column Syntax SELECT column_name AS alias_name FROM table_name; select Product_Id as Id, Product_nameEng as Name from T_Product
Alias (): Alias Table Syntax SELECT column_name(s) FROM table_name AS alias_name; select A.Product_Id ,A.Product_nameEng from T_Product as A
Join Table Table 2 Table Table Join Table Filed Join ( Field ) Join Table ?????? select * from EX_SaleA,EX_Sale_DetailB where A.Receipt_No=B.Receipt_No
