PostgreSQL

Its a Open source relational database management system ( DBMS )

This blog is specially target for Sqoop . 

Sqoop is a special tool designed on the top of Apache Hadoop which is used to transfer data from Any RDBMS to hdfs/hive/Hbase and vise versa.

In Sqoop we may asked to fetch whole table or specific columns from table for that we must aware about RDBMS select query . 

In this Blog we focus on PostgreSQL :

SELECT Queries :

To fetch all records from Customer_Benefits table :

SELECT * FROM Customer_Benefits;

Using AND and OR :

SELECT * FROM Customer_Benefits 
WHERE policy = 'Child Endowment'
AND region = 'NY' OR product_id ='4343';

Comparison :

SELECT * FROM Customer_Benefits
WHERE benefits_id > 3 AND premium = 4000;

Alternative to BETWEEN using Comparison :

SELECT name FROM Customer_Benefits 
WHERE premium >= 5000 AND premium <= 9070; 

Using CASE :

 SELECT benefits_id,
 CASE WHEN benefits_id=70 THEN 'Old'
 WHEN benefits_id=89 THEN 'Old'
 ELSE 'Fresh'
 END AS old FROM Customer_Benefits;

Using CASE,LIMIT :

SELECT customer_name,
CASE WHEN premium > 4000 THEN 'over $4000.00'
WHEN premium = 4000 THEN '$4000.00'
ELSE 'under $4000.00'
END AS premium_range
FROM Customer_Benefits
LIMIT 20;

Comparison <= ,!=  :

SELECT * FROM Customer_Benefits 
WHERE premium <= 9000 AND policy != 'Diamond life';

Using DISTINCT :

SELECT DISTINCT policy FROM Customer_Benefits;

Using DISTINCT ON ,ORDER BY:

SELECT DISTINCT ON (policy) policy, date
FROM Customer_Benefits 
ORDER BY policy, date DESC;

                                                                  Retrieves the most recent report for each policy. But if we had not used ORDER BY to force descending order of date-time values for each policy, we'd have gotten a report from an unpredictable time for each policy. The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON
group.

Using EXCEPT :

SELECT policy FROM Customer_Benefits 
EXCEPT 
SELECT policy FROM Customer_Benefits 
where benefits_id > 3;

Using EXPLAIN :

 EXPLAIN SELECT * FROM Customer_Benefits WHERE customer_name LIKE 'J%';

Using GROUP BY :

SELECT city, max(premium)
FROM Customer_Benefits
GROUP BY city;

Using HAVING :

SELECT city, max(premium)
FROM Customer_Benefits
GROUP BY city HAVING max(premium) < 4000;

Using HAVING and INNER JOIN :

SELECT count(e.policy) AS "number of policy",
p.name AS customer_name
 FROM Policy_Detail AS e INNER JOIN Customer_Benefits AS p
ON (e.p_id = p.id)
GROUP BY customer_name
HAVING count(e.policy) > 1;

Using IS NULL :

 SELECT * FROM Customer_Benefits WHERE customer_name IS NULL

Using LIKE :

SELECT * FROM Customer_Benefits 
WHERE customer_name LIKE ('%s');
SELECT * FROM employee WHERE name LIKE '%D____;

Using UNION :

SELECT customer_name FROM Customer_Benefits where benefits_id = 1
UNION
SELECT customer_name FROM Customer_Benefits where benefits_id = 2 
LIMIT 11;