PostgreSQL: From Beginner to Intermediate

Embark on PostgreSQL from beginner to intermediate, guided by an important note that highlights key considerations for learning and practicing database skills.

Download and install PostgreSQL from postgresql.org, select your operating system, run the installer, uncheck pkg admin folder, set a password, and keep port 5432 as configured.

Learn to start PostgreSQL from the command prompt by locating cmd, updating the PostgreSQL install path in PATH, then running psql -U POSTGRES and entering your password to access SQL.

Download Ubuntu from the Microsoft Store and install it, then create a username and password and complete the setup. Run updates with sudo apt update to finish installation.

Install the postgresql package on Linux via the terminal for Mac and Windows users. Run sudo apt-get install postgresql postgresql-contrib and approve the disk space prompt.

Create a database from scratch using the create command in PostgreSQL, part of data definition language. List databases with backslash l, and end SQL with a semicolon to confirm creation.

Connect to database, then create an employee table with id as serial primary key and first_name and last_name as varchar(50), and verify with backslash c and backslash d t commands.

Learn to create PostgreSQL users using the super user, with optional passwords, valid until dates, and the option to create a database.

Explore sql constraints, including primary key, foreign key, not null, unique, and check; learn how keys ensure unique, non-null identifiers and link tables through references.

Explore why data types matter in PostgreSQL for accuracy, storage, and speed, and review core types such as integers, floats, serial, text, date/time, and boolean.

practice test demonstrates creating a test database and two tables, employee and project, with serial primary keys, a not null name, a unique email, and a foreign key referencing employee.

Learn how to use the drop command in SQL to remove a table or a database, including removing the project table and the test database.

Connect to PostgreSQL using the super user Posterous and learn how to remove a user. Execute drop user with the target name and verify by listing users again.

Truncate a table to remove all its values while preserving the table structure, and contrast this with drop, which removes both data and the table.

Learn to use the alter command in PostgreSQL to modify a table's structure by adding, renaming, or dropping columns and constraints, including setting a primary key.

Learn how to grant and revoke privileges in PostgreSQL, assigning database and table rights to users. Apply both all privileges and specific permissions, including grant option.

Learn how to revoke privileges in PostgreSQL, both specific and all, on databases and tables, from a user like David, with practical command examples.

Create a user named John with password one, two, three and grant all privileges on database company. Grant John the truncate privilege on table office, then revoke it from John.

Learn how to insert data into a PostgreSQL table using the insert command, create an employee table with id, name, and salary, and use both column-listed and unnamed insert forms.

Use the delete command to remove a specific row or all rows from a table, using a where clause with a primary key such as employee ID.

Learn the differences between drop, truncate, and delete: remove a table or database, clear table data while preserving structure, or delete specific records, with rollback considerations.

Explore updating an existing table value with the update command, using set and where clauses to modify a specific employee's salary by id from 2000 to 1000.

Learn to use the select command to retrieve all data or specific columns from a table, and filter by a where clause to view employee names and salaries.

Practice test three demonstrates using data manipulation language commands—insert, update, select, and delete—on an employee table, including inserting id five, updating salary from 5000 to 6000, and deleting the record.

Learn to assign a temporary alias to a column using as, renaming salary to new salary in a select from the employee table.

Learn to perform arithmetic operations in PostgreSQL using addition, subtraction, multiplication, and division; compute new salaries by multiplying by two and alias the result as new_salary.

Master using comparison operators in PostgreSQL to filter data. Retrieve all columns from a table where salary is greater than 2000, illustrated with employees 2 and 3.

Learn how to use the order by clause in PostgreSQL to sort query results by a chosen attribute using select statement, ascending or descending, demonstrated with an employee ID example.

Learn to use aggregate functions in PostgreSQL on a salary attribute, including avg, sum, max, min, and count, with example results and a note on more functions.

Discover how to use the distinct clause with a select statement to suppress duplicate values when retrieving one or more attributes in PostgreSQL, illustrated with department names.

Learn how the PostgreSQL limit clause retrieves a specific number of rows from a table, exemplified by getting the first two rows from a department table.

Group rows by department names, apply aggregate functions to compute maximum salaries, and filter results with the having clause.

Explore how to combine data across multiple tables using joins, including inner, left outer, and right outer joins, to build intuition before coding.

Master inner join to link country and state tables by country_id and retrieve matching records, including country id, country name, and state name, as shown with Egypt and USA.

Learn how left outer join, right outer join, and full outer join work with country and state data linked by the country_id key.

Create a view as a virtual table to copy selected data from department, enabling safe computations and privilege management by granting access to specific columns like department name and salary.