Difference between revisions of "RDBMS PostgreSQL"

Latest revision as of 16:00, 7 May 2019

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, PostgreSQL and Microsoft Access. In this series we are handling with PostgreSQL database.

The data in an RDBMS is stored in database objects which are called as tables. A table is a collection of related data entries and contains rows and columns to store data.

A field is a column in a table that maintains specific information about every record in the table. A row of table is called record. It contains the specific information of each individual entry in the table. A column is a vertical entity in the table which contains all information associated with a specific field in a table.

PostgreSQL is a powerful, open source relational database system. It runs on all major operating systems, including Linux, Windows and Mac OS. It supports most SQL data types, foreign keys, joins, views, triggers and stored procedures.. It has programming interfaces for C/C++, Java, Perl, Python, Ruby etc.

The Spoken Tutorial Effort for RDBMS – PostgreSQL has been contributed by Nirmala Venkat from Spoken Tutorials.

The domain review of this series is done by Prof. N.L.Sarda from IIT Bombay.

Learners: UG/PG CSE/IT/CS students to learn industry database management.

Basic Level

1. Overview of RDBMS - PostgreSQL
   • Introduction to RDBMS
   • Common Software based on RDBMS
   • Prerequisites for learning PostgreSQL
   • OS and PostgreSQL version used
   • Features of PostgreSQL
   • Who can use PostgreSQL?
   • Glimpse of Spoken Tutorials available on PostgreSQL
   • About Spoken Tutorial project
   • About Spoken Tutorial workshops
   • Timed discussion forum of Spoken Tutorials
2. Installation of PostgreSQL
   • Installation of PostgreSQL on Linux
   • Installation of PostgreSQL on Windows
   • Installation of PgAdmin
   • Connect to PostgreSQL database
   • Set password for the database
   • Show the current user and current date
   • Disconnect from the server
3. Create database using PgAdmin
   • How to connect to the server
   • Introduction to database and its objects
   • How to create a database
   • Table and its attributes
   • PgAdmin Interface
   • How to create a table
   • Various data types
   • Create table syntax
4. Table with Primary keys
   • Insert single row to the table
   • Insert Multiple Row to the table
   • How to execute the query
   • Retrieve the data using select statement
   • Introduction to Primary keys
   • Rules to define a Primary key
   • Data redundancy
   • How to prevent duplication of data
   • Create a table with primary keys
   • Drop table
5. Select statement
   • View data in the Edit data window
   • Insert records in the Edit data window
   • Inserting a NULL value in a record
   • Basic SELECT statement
   • SELECT statement with WHERE clause
   • Comparison / Relational operators in select statement
   • Arithmetic expressions in select statement
   • Column Alias
   • Logical Operators in select statement
   • Examples for various select statement
6. Select with aggregate functions
   • DISTINCT clause in SELECT statement
   • How to use BETWEEN operator in SELECT statement
   • How to use LIKE operator in SELECT statement
   • Usage of percent and underscore wildcards with examples
   • IN operator in SELECT statement
   • Aggregate functions
   • List of aggregate functions
   • How to use count, sum ,max functions with examples
   • Count function with Null
   • WHERE condition in aggregate functions
7. Foreign key constraint
   • What is a Foreign key?
   • Foreign key link between students and department table
   • Create a parent table department
   • Alter table command
   • Add a new column to the existing students table
   • How to add a Foreign key using Alter table command
   • Foreign key violation error
   • Advantages of using Foreign key
   • Alter table alter column command
   • Commonly used constraints
8. Aggregation facilties in SQL
   • Aggregate functions
   • What is Group by clause?
   • Syntax for Group by clause
   • Examples for using Group by clause with one/multiple columns
   • Having clause
   • Syntax for Having clause
   • Examples for Having clause
   • Order by clause
   • Syntax for Order by clause
   • Examples for Order by clause with ascending and descending order
9. Updating Data
   • Update statement
   • Syntax for Update statement
   • Examples for Update statement
   • Updating multiple columns
   • Syntax for Delete statement
   • Examples for Delete statement
   • Delete rows for different conditions
   • Delete all rows
   • Basics of common SQL statements
   • Data Manipulation Language