SQL Data Manipulation Language (DML) PDF

SQL : Data Manipulation Language (DML) Ahmadi Irmansyah Lubis  Query Basic Structure Query SQL : select A1, A2,..., Ai from R1, R2,..., Ri where P  Ai : selected field ( * means “all fields”)  Ri : table name  P : requirement condition  Sample...

SQL : Data Manipulation Language (DML) Ahmadi Irmansyah Lubis  Query Basic Structure Query SQL : select A1, A2,..., Ai from R1, R2,..., Ri where P  Ai : selected field ( * means “all fields”)  Ri : table name  P : requirement condition  Sample Account ( , CustId, Balance, Type) Deposit ( , AcctNo, Date, Amount) Check ( , AcctNo, Date, Amount) ATMWithDrawal( , CustID, AcctNo, Amount, WithDrawDate) Customer( , Name, Phone, Address)  Sample Display AcctNo and Amount from table ATMWithdrawal  Sample SELECT AcctNo, Amount FROM ATMWithdrawal  Sample (with WHERE) Display all attributes from table ATMWithdrawal which have amount less than 50  Sample (with WHERE) Selected fields Table name SELECT * FROM ATMWithdrawal WHERE Amount < 50; Requirement condition  Sample (with WHERE) check condition for each row Is Amount less than 50? Yes! Amount < 50  Sample (with WHERE) Is Amount less than 50? Check condition for each row No ! Amount < 50 This row is ignored  Sample (with WHERE) Is Amount less than 50? Check condition for each row Yes ! Amount < 50  Sample (with WHERE) Is Amount less Check condition for each row than 50? Yes! Amount < 50  Sample (with WHERE) Check condition for each row Is Amount less than 50? No! This row is ignored Amount < 50  SQL Extensions SELECT … Extension untuk SELECT FROM … WHERE …  SELECT SQL allows duplication in relation and query result. Add keyword DISTINCT after SELECT to dismiss duplication Sample : ◦ Retrieve Account, Amount from Deposit relation SELECT DISTINCT AcctNo, Amount FROM Deposit ◦ Keyword ALL : duplication is still happened SELECT ALL AcctNo, Amount FROM Deposit  SELECT SELECT can contains arithmatic expression (+, –, , /, and operation in certain attribute Sample: SELECT AcctNo, Amount*10 FROM ATMWithdrawal WHERE Amount < 50; Result : Amount value will be multiplied by 10  SELECT We can give new attribute with AS Sample: SELECT AcctNo AS Number, Amount*10 AS Amt FROM ATMWithdrawal WHERE Amount < 50; Result :  Aggregate Function We can use aggregate function in SELECT ◦ avg: average value ◦ min: minimum value ◦ max: maximum value ◦ sum: sum of values ◦ count: number of values The value must be numeric Sample : SELECT MIN(Balance), MAX(Balance), AVG (Balance) FROM Account;  Aggregate Function Retrieve how many Customer SELECT count(*) FROM Customer Retrieve lowest balance of Account SELECT min(Balance) FROM Account Retrieve average amount of check which less than 50 SELECT avg(Amount) FROM Check WHERE Amount < 50  SQL Extensions SELECT … FROM … WHERE … Extention for WHERE  WHERE Define requirement condition Use : ◦ Logical comparation : and, or, not  Logical Comparation Retrieve Number and CustID for Account which have ‘checking’ type and balance more than 1000 SELECT Number, CustID FROM Account WHERE Balance > 1000 AND TYPE = ‘checking’;  WHERE Define requirement condition Use : ◦ Logical comparation : and, or, not ◦ Comparation : =, >, >=, ‘2008-04-01’; Retrieve ATM withdrawal with Amount less than 50 but the transaction is not done by CustID 1 SELECT * FROM ATMWithdrawal WHERE Amount < 50 AND CustID 1;  WHERE Define requirement condition Use : ◦ Logical comparation : and, or, not ◦ Comparation : =, >, >=, =, =, 1000; What is the result ? We must check every combination of every row in Account and every row in Deposit ! SQL Query with Two Tables  SQL Query with Two Tables No! Ignore.  SQL Query with Two Tables No! Ignore.  SQL Query with Two Tables No! Ignore.  SQL Query with Two Tables Yes! Input in result section.  SQL Query with Two Tables Yes! Input in result section.  SQL Query with Two Tables No! Ignore. SQL Query with Two Tables No! Ignore.  SQL Query with Two Tables All combination is fault! SQL Query with Two Tables No! Ignore. SQL Query with Two Tables No! Ignore. SQL Query with Two Tables No! Igone. Why? SQL Query with Two Tables No! Ignore.  SQL Query with Two Tables No! Three data is fault.  SQL Query with Two Tables Yes! Input in result section.  SQL Query with Two Tables Temporary Result SELECT A.Owner, A.Balance FROM Account A, Deposit D WHERE D.Account = A.Number and A.Balance > 1000; End result become : Thanks! Do you have any questions?