PostgreSQL and MS SQL for Beginners

Install PostgreSQL and pgAdmin, select the 64-bit Windows installer, and configure the default port and a simple password to connect to your PostgreSQL database via the query tool.

Celebrate reaching this milestone in the PostgreSQL and MS SQL for Beginners course and stay motivated with quality checks, q&a support, and a certificate.

Install Microsoft SQL Server 2019 Express and SQL Management Studio, then connect to the server using the SQL Shell. Create a database in Management Studio to verify the connection.

Learn to debug SQL server installation by uninstalling all components with admin PowerShell and msiexec /x, cleaning program files, then reinstalling to ensure a successful setup.

Create and connect a test database using pgAdmin, then design three tables for customers, products, and transactions. Learn core table design, data types, and primary and foreign key constraints.

Create a training database and a customer table in MS SQL using the create command, defining columns for customer ID, first name, last name, age, and email ID.

Create a classroom database and a table named science class with enrollment number (int), name (varchar), and signs marks (int), using the query tool and basic database creation steps.

Learn how to insert data into the customer table in PostgreSQL using insert into, with or without column names, handle missing values and not null constraints, and insert multiple rows.

learn to insert data into MS SQL tables using insert into with values, specifying columns, inserting single and multiple rows, and handling strings, integers, and missing columns.

learn to import data into a PostgreSQL table using the copy command from csv and text files, with delimiters and csv headers (csv only).

Learn to import data into an MS SQL table from a CSV file using bulk insert, ignoring the first row and setting the comma and newline terminators.

Practice inserting three student scores into the science class table using insert into, then import eight more scores from a CSV file into the same table.

Fetch data from the customer table using the select command, retrieve one or multiple columns, or all columns with a wildcard, and view result sets.

Execute select statements to fetch data from customer table, choosing first name and last name or all columns with the star wildcard, and run queries using F5 to visualize results.

Use the distinct keyword with select to fetch unique values and eliminate duplicates from a table. Learn selecting single or multiple columns, and that distinct removes exact duplicate rows.

Learn how to remove duplicate values with select distinct in MS SQL, using the customer table to show distinct first names and distinct name-age combinations.

Learn how to filter data in PostgreSQL using the where clause, including equality and greater-than conditions, selecting specific columns, and using distinct values and star.

Apply where clauses in ms sql to filter results, using conditions like age = 25, age > 25, or age > 35, and select first names or columns from customer.

Explore how PostgreSQL uses and, or, and not to combine multiple conditions. Apply them in where clauses with examples like age between 20 and 30 and not equal to 25.

Explore how to combine conditions using and, or, and not in MS SQL to filter customers by age ranges and names, with practical select queries.

Apply sql filtering with select and where to fetch names over 60, records for more than 35 but less than 60, and the rest not in those lists using logic.

Learn how to update records in a PostgreSQL table using update queries, including the update set where syntax and practical examples of changing age, last name, and emails.

Describe updating records in MS SQL by setting age and last name with a where clause, and updating emails based on first name conditions, with visible affected rows.

Master deleting rows in PostgreSQL by using delete from with a where clause to remove a single row, multiple rows, or all rows, illustrated with practical examples.

Master deleting data in MS SQL with delete from and conditions, removing specific rows by customer id or age, and clearing an entire table when needed.

Learn how to alter a PostgreSQL table by adding, dropping, modifying, or renaming columns, changing data types, and managing constraints or indexes using the alter table syntax.

Apply and manage constraints in PostgreSQL by adding and dropping not null and check constraints, defining primary and foreign keys, and testing with insertions and deletions.

Learn how to use alter in MS SQL to add, drop, or modify columns, apply not null and check constraints, rename via stored procedures, and manage primary keys.

Update Popeye's marks in the science class table from 33 to 45, delete the row for Rob, and rename the column from name to student_name.

create and restore a database from a backup file, then inspect the customer, product, and sales tables with basic select queries to learn data structure and relationships.

Troubleshoot PostgreSQL restoration by creating a new database, selecting the correct backup format (tar, dot backup, dot sql, or directory), and updating the binary path in pg admin when needed.

Create a PostgreSQL database from csv files by building the customer, product, and sales tables, then load data with the copy command into each table.

Back up the database to a tar file, then recover it after accidentally dropping the science class table, demonstrating how to back up and restore data.

Back up a complete database to a specified path, then share the backup file for others to restore. Restore by selecting the backup file from devices and naming the database.

Create a new SQL database from CSV and Excel sources, using create table and bulk insert, restore backups, and populate customer, product, and sales tables with imported data.

Learn to use the in operator in PostgreSQL to filter multiple values in one block, replacing multiple or conditions, with city examples like Philadelphia and Seattle.

Switch to the target database with use, query the customer table with select, and filter cities by Philadelphia or Seattle using or logic or the in statement.

Learn how to use the between condition in PostgreSQL to retrieve rows within a specific range, including numeric ages and date ranges, with inclusive bounds and not between options.

Use the between and not between operators in MS SQL to filter data by ranges, such as ages between 20 and 30, and ship dates within start and end date.

Learn pattern matching in PostgreSQL with the like condition, using % and _ wildcards and an escape character. Use not like and distinct for patterns starting with G.

Discover how the like statement enables pattern matching in MS SQL, using % and _ wildcards to filter names by starting letters, containing Nelson, or four-character first names.

Apply the in, between, and like operators in where clauses through exercises: list unique cities in north or east, filter sales between 100 and 500, and identify four-character last names.

Learn to add comments in sql queries, using multi-line comments /* ... */ and end markers, and single-line comments with --. See how comments name the lecture and guide execution.

Learn how to use the order by clause to sort results, such as California customers ordered by name, with ascending and descending options plus multiple columns.

Learn to use the order by clause in ms sql to sort results. Apply it to column names or numbers with alphabetic or numeric data, as shown in examples.

Learn how to limit query results in PostgreSQL using the limit clause, with examples ranking by age in descending and ascending order to fetch top or bottom records.

Replace the limit clause in MS SQL with select top to cap results, compare with MySQL, and use order by to show ascending or descending ages.

Apply sorting to the orders dataset by discount, showing only discounts greater than zero in descending order, and limit results to the top ten with the highest discounts.

Learn to use the as keyword to alias columns and tables in PostgreSQL, creating meaningful names like serial_number and customer_name for clearer results.

Learn to use as in MS SQL to alias tables, columns, and subqueries, with examples renaming customer ID to serial number and customer name to name, without changing the table.