Rapid POSTGRESQL learning, PART-3

4 parts to easily understand how to interact with postgresql

Transcript

1. Rapid POSTGRESQL learning, PART-3 By: ali masudianpour masud.amp@gmail.com Rapid POSTGRESQL learning. 1
2. Combining Queries, UNION UNION used to append the result of a query to the result of other query – It eliminated the duplicate rows • SELECT [Fields name] FROM [Table name] [Conditions] UNION SELECT [Fields name] FROM [Table name] [Conditions] – Example: • SELECT * FROM Ordinary_customers WHERE 1 UNION SELECT * FROM pro_customers – To avoid elimination duplicate rows we use ALL keyword • SELECT [Fields name] FROM [Table name] [Conditions] UNION ALL SELECT [Fields name] FROM [Table name] [Conditions] Rapid POSTGRESQL learning. 2
3. Combining Queries, INTERSECT INTERSECT returns the rows that appear in both queries, to be more clear INTERSECT returns only common rows – It also eliminates duplication • SELECT [Fields name] FROM [Table name] [Conditions] INTERSECT SELECT [Fields name] FROM [Table name] [Conditions] • Example: – SELECT * FROM Ordinary_customers WHERE 1 INTERSECT SELECT * FROM pro_customers Rapid POSTGRESQL learning. 3
4. Combining Queries, EXCEPT EXCEPT returns all the queries that are in the query one and not in the query two – SELECT [Fields name] FROM [Table name] [Conditions] EXCEPT SELECT [Fields name] FROM [Table name] [Conditions] – Example: • SELECT * FROM Ordinary_customers WHERE 1 EXCEPT SELECT * FROM pro_customers Rapid POSTGRESQL learning. 4
5. Combining queries- notes In combining queries always bear in mind that: – Both queries should be compatible while we are using UNION, INTERSECT and EXCEPT – They must have similar columns and both columns must have similar data types Rapid POSTGRESQL learning. 5
6. Aggregate Functions – SUM • Is used to calculate the summation of a columns value – SELECT SUM([column name]) FROM [Table name]; – AVG • Is used to calculate the average of a columns value – SELECT AVG([column name]) FROM [Table name]; – MAX • Is used to get the maximum value of a columns value – SELECT MAX([column name]) FROM [table name]; – COUNT • Is used to get the count of rows per a column – SELECT COUNT([Column name]) FROM [table name] • We can use DISTINCT to ignore repeated values • SELECT COUNT(DISTINCT [column name]) FROM [table name] Rapid POSTGRESQL learning. 6
7. GROUP BY Used to group together those rows in the table that have same values in all columns listed in the group by clause. – SELECT [column name] FROM [Table name] GROUP BY [column name] • Example: – SELECT field1, COUNT(field2) FROM testable GROUP BY field1; – We can group by more than one field Rapid POSTGRESQL learning. 7
8. HAVING To have a condition into GROUP BY clause we use HAVING – SELECT [column name] FROM [table name] GROUP BY [column name] HAVING [condition] • Example – SELECT field1, field2 FROM tableTest GROYUP BY field1 HAVING SUM(field1)>=100000; – Note: • WHERE is used to eliminate rows, but in front, HAVING is used to eliminate groups – Example • SELECT order_date, SUM(order_total) AS total FROM orders GROUP BY order_date HAVING order_date>’2012-09-12’ Rapid POSTGRESQL learning. 8
9. JOIN Join is a query that accesses more than one table or more than one row of the same table at once. – We need a condition to make joins – Most of the times, condition will be the PRIMARY KEY and FORIGN KEY in tables Rapid POSTGRESQL learning. 9
10. INNER JOIN Selects all rows from both tables as long as there is a match between the columns in both table. To be more clear, if there are rows in the first table that do not have match in second table, those will not be listed as result. – Syntax • SELECT [Column name] FROM [First table name] INNER JOIN [second table name] ON [Condition] – Condition could be: firstTable.column1=secondTable.column2 – Note: rows that have not column2 in first table and rows that have not column1 in second table will not be listed. – The join table will include the row for each row in the table that match in second table based on our join condition. Rapid POSTGRESQL learning. 10
11. OUTER JON The outer join differs from inner join in that rows are returned in result even when there are no matches through the JOIN criteria on the second table. – It can be useful when there is a need to merge data from two tables and to include all rows from both tables without depending on a match. – OUTER JOIN have 3 types • RIGHT JOIN ( RIGHT OUTER JOIN ) • LEFT JOIN ( LEFT OUTER JOIN ) • FULL JOIN ( FULL OUTER JON ) Rapid POSTGRESQL learning. 11
12. OUTER JOINS – LEFT JOIN • In LEFT OUTER JOIN all rows in first table (table that is left to JOIN statement) will be shown in result, but rows in second table that do not have matches in first table will not be shown in result. – RIGHT JOIN • In RIGHT OUTER JOIN all rows in second table (table that is right to JOIN statement) will be shown in result, but rows In first table that do not have matches in second table will not be shown in result. – FULL JOIN • All rows in both tables (first and second table) will be shown in result even if rows in both tables have no match. – SYNTAX • Syntax is the same in all joins but the only thing that changes would be LEFT/RIGJT/FULL – Example • SELECT [Column name] FROM [First table name] LEFT/ RIGHT/ FULL JOIN [second table name] ON [Condition] • Condition could be: firstTable.column1=secondTable.column2 Rapid POSTGRESQL learning. 12
13. CROSS JOIN The CROSS JOIN creates a Cartesian product between two sets of data. – This type of join does not maintain any relationship between the sets. – Instead return the result which is the number of first rows multiplied by the number of rows in the second table. – it returns every possible combination of rows between the joined sets. – Syntax • SELECT [column name] FROM [first table name] CROSS JOIN [second table name]; Rapid POSTGRESQL learning. 13
14. Sub Query Expressions – Sub query expression is basically a query that used in another query – Sub queries are used with expressions – Types of sub queries • EXISTS • NOT EXISTS • IN • NOT IN Rapid POSTGRESQL learning. 14
15. Sub Query Expressions – Exists • Evaluates if the sub query returns any row – SELECT [column name] FROM [table name] WHERE EXISTS (SELECT [column name] FROM [table name] WHERE [condition]) – NOT EXISTS • NOT EXISTS is the opposite of EXISTS – SELECT [column name] FROM [table name] WHERE NOT EXISTS (SELECT [column name] FROM [table name] WHERE [condition]) – IN • There is 2 sides – 1: if the left hand side is an expression the right side must be a sub query – 2: if any equal row in sub query found, it is true, otherwise false • Syntax – SELECT [column name] FROM [table name] WHERE [column name] IN (SELECT [column name] FROM [table name] WHERE [condition]) – NOT IN • NOT IN is the opposite of IN Rapid POSTGRESQL learning. 15
16. VIEWS Views are similar to tables, but they are not physically a material. – VIEWS can create virtual tables – How to create a view • CREATE OR REPLACE VIEW [view name] AS [query] – Example: • CREATE OR REPLACE VIEW students AS SELECT id,name,lastName FROM tbl_students; – Temporary VIEW • When we need a temporary view we use temporary views – CREATE OR REPLACE TEMP VIEW [view name] AS [query] – Usage • Using views is completely the same with ordinary tables – SELECT * FROM [viewname] – REMOVE • To remove a view we use the following syntax – DROP VIEW [view name] – Notes • Views are read only • Views can not be written or updated Rapid POSTGRESQL learning. 16
17. END OF PART 3 End of Part 3 – In part 4 we will discuss about: • TRANSACTIONS • SYSTEM COLUMNS • CHANGING TABLES AND COLUMNS • LOGICAL OPERATIORS • COMPARISON OPERATORS • MATHEMATICAL OPERATORS • STRING FUNCTIONS AND OPERATIONS Rapid POSTGRESQL learning. 17