Advanced SQL database — tables, joins, constraints and query syntax — written by Aivasu Tech Institute's mentors. Free to read, structured to actually help you learn.
PostgreSQL notes by Aivasu Tech Institute cover 42 topics — from postgresql home to postgresql case — each explained with short definitions, syntax and runnable code examples. They are 100% free, need no signup, and work as quick revision for college exams, PostgreSQL interviews and Aivasu Tech Institute's mentor-led PostgreSQL course in Pitampura, Delhi.
No notes found. Try a different search term, or browse all PostgreSQL notes.
PostgreSQL is an advanced, free, and open-source relational database system.
PostgreSQL is a free open-source database system that supports both relational (SQL) and non-relational (JSON) queries.
To install PostgreSQL locally on your computer, visit the installer by EDB, and download the newest version compatible with your operating system.
If you have followed the steps from the Install PostgreSQL page, you now have a PostgreSQL database on you computer.
In the previous chapter we learned how to connect to the database using the SQL Shell (psql) application, which is a command-based application that allows us to
To create a new database table using the SQL Shell, make sure you are connected to the database. If not, follow the steps in the Get Started chapter of this tut
To insert data into a table in PostgreSQL, we use the INSERT INTO statement.
To retrieve data from a data base, we use the SELECT statement.
To add a column to an existing table, we have to use the ALTER TABLE statement.
The UPDATE statement is used to modify the value(s) in existing records in a table.
To change the data type, or the size of a table column we have to use the ALTER TABLE statement.
To remove a column from a table, we have to use the ALTER TABLE statement.
The DELETE statement is used to delete existing records in a table.
The DROP TABLE statement is used to drop an existing table in a database.
Up until now in this tutorial we have worked with a very small and simple table in our PostgreSQL database.
We can operate with different operators in the WHERE clause:
To retrieve data from a database, we use the SELECT statement.
The SELECT DISTINCT statement is used to return only distinct (different) values.
The WHERE clause is used to filter records.
The ORDER BY keyword is used to sort the result in ascending or descending order.
The LIMIT clause is used to limit the maximum number of records to return.
The MIN() function returns the smallest value of the selected column.
The COUNT() function returns the number of rows that matches a specified criterion.
The SUM() function returns the total sum of a numeric column.
The AVG() function returns the average value of a numeric column.
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
The IN operator allows you to specify a list of possible values in the WHERE clause.
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
SQL aliases are used to give a table, or a column in a table, a temporary name.
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
The INNER JOIN keyword selects records that have matching values in both tables.
The LEFT JOIN keyword selects ALL records from the "left" table, and the matching records from the "right" table. The result is 0 records from the right side
The RIGHT JOIN keyword selects ALL records from the "right" table, and the matching records from the "left" table. The result is 0 records from the left side
The FULL JOIN keyword selects ALL records from both tables, even if there is not a match. For rows with a match the values from both tables are available, if
The CROSS JOIN keyword returns the Cartesian product of two or more tables (combines every row from the first table with every row from the second table).
The UNION operator is used to combine the result-set of two or more queries.
The GROUP BY clause groups rows that have the same values into summary rows, like "find the number of customers in each country".
The HAVING clause was added to SQL because the WHERE clause cannot be used with aggregate functions.
The EXISTS operator is used to test for the existence of any record in a sub query.
The ANY operator allows you to perform a comparison between a single column value and a range of other values.
- returns TRUE if ALL of the sub query values meet the condition
The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement).
Join Aivasu Tech Institute's PostgreSQL course — live mentorship, hands-on projects, and 100% placement support in Delhi NCR.
Enroll Now — Free Demo Available✳ Common questions
What students search before reading PostgreSQL notes.