SELECT DISTINCT clause

The syntax of the DISTINCT clause is as follows:

The DISTINCT clause is used in the SELECT statement to remove duplicate rows from a result set. The DISTINCT clause keeps one row for each group of duplicates. You can use the DISTINCT clause on one or more columns of a table.

SELECT DISTINCT
column_1
FROM table_name
SELECT DISTINCT
column_1,
column_2
FROM
tbl_name;
Example :
Distinct
SELECT DISTINCT  bcolor  FROM t1 ORDER BY bcolor;
Distinct_OP.PNG

PostgreSQL DISTINCT on multiple columns

SELECT DISTINCT  bcolor, fcolor FROM t1 ORDER BY bcolor,      fcolor;
distinct2.PNG
Because we specified both bcolor and fcolor columns in the SELECT DISTINCT clause, PostgreSQL combined the values in both bcolor and fcolor columns to evaluate the uniqueness of rows.

PostgreSQL DISTINCT ON ORDER BY example

The following statement sorts the result set by the  bcolor and  fcolor, and then for each group of duplicates, it keeps the first row in the returned result set.

SELECT DISTINCT ON (bcolor) bcolor, fcolor FROM t1 ORDER BY bcolor, fcolor;
distinct3.PNG

PostgreSQL SELECT

Select distinct rows by using DISTINCT operator.
Filter rows by using WHERE clause.
Sort rows by using the ORDER BY clause.
Select rows based on various operators such as BETWEEN, IN and LIKE.
Group rows into groups by using GROUP BY clause
Apply condition for groups by using HAVING clause.
Join to another table by using INNER JOIN, LEFT JOIN, RIGHT JOIN clauses.

 

PostgreSQL Server and Database Objects

 

Server service

When you install a PostgreSQL instance, you will have a corresponding PostgreSQL server service.

Database

A database is a container of other objects such as tables, views, functions, indexes, etc. You can create as many databases as you want inside a PostgreSQL server.

Table

The table is used to store the data. You can have many tables in a database. A special feature of PostgreSQL table is inheritance. Meanings a table (child table) can inherit from another table (parent table) so when you query data from the child table, the data from parent table is also showing up.

Schema

A schema is a logical container of tables and other objects inside a database. Each PostgreSQL database may have multiple schemas. It is important to note that schema is a part of ANSI-SQL standard.

Function

The function is a block reusable SQL code that returns a scalar value of a list of records. In PostgreSQL, functions can also return composite objects.

Cast

Casts enable you to convert one data type into another data type. Casts actually backed by functions to perform the conversion. You can also create your own casts to override the default casting provided by PostgreSQL.

 

PostgreSQL

PostgreSQL is free and open source software. Its source code is available under PostgreSQL license, a liberal open source license. You are free to use, modify and distribute PostgreSQL in any form.

PostgreSQL requires very minimum maintained efforts because of its stability.  Therefore, if you develop applications based on PostgreSQL, the total cost of ownership is low in comparison with other database management systems

What make PostgreSQL stand out

-multi-version concurrency control (MVCC) feature

-It allows you to add custom functions developed using different programming languages such as C/C++, Java, etc.

-you can define your own data types, index types, functional languages, etc.

check PostgreSQL Version

select version()

 

Database Questions and Answers

This set of Database Questions & Answers focuses on “Join Operations – 2”

1. A_____ is a query that retrieves rows from more than one table or view:
a) Start
b) End
c) Join
d) All of the mentioned

2.Which product is returned in a join query have no join condition:
a) Equijoins
b) Cartesian
c) Both
d) None of the mentioned

3.Which is a join condition contains an equality operator:
a) Equijoins
b) Cartesian
c) Both and b
d) None of the mentioned

4.Which operation are allowed in a join view:
a) UPDATE
b) INSERT
c) DELETE
d) All of the mentioned

 

Inner Join VS Equi Join

Inner Join

In the join condition, you can also use other operators like <,>,<>.

Equi Join

Equi join is a special type of join in which we use only equality operator. Hence, when you make a query for join using equality operator then that join query comes under Equi join.

  1. Inner join can have equality (=) and other operators (like <,>,<>) in the join condition.
  2. Equi join only have equality (=) operator in the join condition.
  3. Equi join can be an Inner join, Left Outer join, Right Outer join

Types of joins

Joins help retrieving data from two or more database tables. The tables are mutually related using primary and foreign keys.
Cross JOIN
Cross JOIN is a simplest form of JOINs which matches each row from one database table to all rows of another.
In other words it gives us combinations of each row of first table with all records in second table.
 Cross_Join

INNER JOIN

The inner JOIN is used to return rows from both tables that satisfy the given condition.

inner_Join

Inner_Join_Example

Inner_Join_Example_op

Natural JOIN

Natural Join is a type of Inner join which is based on column having same name and same datatype present in both the tables to be joined.

Natural_JoinNatural_Join_OP

Outer JOIN

Outer Join is based on both matched and unmatched data. Outer Joins subdivide further into,

  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

Left Outer Join

The left outer join returns a result table with the matched data of two tables then remaining rows of the left table and null for the right table’s column.

Left_Outer_JoinLeft_Outer_Join_op

Right Outer Join

The right outer join returns a result table with the matched data of two tables then remaining rows of the right table and null for the left table’s columns.

Right_Outer_Join.PNG

Right_Outer_Join_op1.PNG

Full Outer Join

The full outer join returns a result table with the matched data of two table then remaining rows of both left table and then the right table.

Full_Outer_Join

Full_Outer_Join_OP.PNG