2022-Basics of SQL using PostgreSQL

سرفصل های دوره

A course that teaches SQL from the ground up

1. Introduction

1. Learning Objectives

2. Introduction to Relational Databases and Management Systems

3. Introduction to SQL

4. Install PostgreSQL in Windows

5. Install PostgreSQL in MAC

6.1 dvdrental.zip

6.2 training.zip

6. PGAdmin and Sample Databases

7. Introduction-Quiz.html

2. The Basics of SQL

1. SELECT columns and ROWS FROM tables

2. More SELECT using ALIASes

3. ORDER rows in ASCENDING or DESCENDING order

4. Exercises with SELECT and ORDER BY

5. Selecting unique entries using DISTINCT

6. Exercises with DISTINCT clause

7. Filtering rows using the WHERE clause

8. Exercises using the WHERE clause

9. Limiting the number of rows using the LIMIT clause

10. The Basics-Quiz.html

11. Using the SELECT and ALIAS statements..html

12. Assignment-1 Using the WHERE clause.html

13. Assignment-2 Using WHERE clause.html

3. Joining multiple tables with JOINS

1. INNER JOINS- Explanation

2. INNER JOINS - Example

3. INNER JOINS - A more complex example

4. OUTER JOINS

5. SELF JOINS

6. JOINS - Quiz.html

7. Simple JOINs.html

8. Use JOINs to determine the names of all the actors in Club Graffiti in one SQL q.html

9. Use JOINs to determine the films that Emily Dee has acted in.html

4. Create, Read, Update and Delete Operations (CRUD)

1. Basic Datatypes in SQL

2. Basic CRUD on databases, tables and rows

3.1 11-crud2.zip

3. Manipulating columns in SQL

4. Constraints on columns PRIMARY KEY and SERIAL

5. Constraints on columns NOT NULL, DEFAULT, UNIQUE and CHECK

6.1 13-foreignkey.zip

6. Foreign Keys Constraints and their implications

7. CRUD-Quiz.html

8. CRUD Operations.html

5. Built-in functions, Date, Time and Timestamps

1. Useful built-in functions in Postgres

2. The DATE Datatype

3. The TIME Datatype

4. TIMESTAMPs and TIMESTAMPs with TIMEZONE

6. Combining rows into GROUPS

1. Basic grouping of data using the GROUP BY and HAVING clauses

2. Aggregate calculation deep dive

3. Complex grouping using multiple columns and extracted data

4.1 handhelds.csv

4. Combine many grouping operations into a single query

5. Common SET operations to stack rows on top of each other.

6. Quiz - GROUPS.html

7. Mini-Project - I

1. In-depth information about the DVD rental store.html

8. Complex SQL queries

1. Use Sub-queries to construct complex queries.

2. Use the CASE statement to take decisions

3. Common Table Expressions (CTE)

4. Virtual Tables using VIEWS

5. Virtual Tables with MATERIALIZED VIEWS

9. Transactions in SQL

1. An introduction to SQL TRANSACTIONS

2. Implicit and Explicit Transactions

10. SQL Programming using PLPGSQL

1. Introduction to PLPGSQL

2. IF-ELSE Control structures in Functions

11. Triggers and Trigger Functions

1. Introduction to TRIGGERS and TRIGGER functions

2.1 triggerfunctions2.zip

2. Updating the materialized view automatically

3. Manipulating data before insertion into the table using trigger functions

12. Mini-Project - II

1. Problem Description Build an audit table to track any changes in other tables

2. Solution

13. Database Optimizations

1.1 csvfiles.zip

1. Speed up searches of text and numbers using INDEX

2. The Binary Search Algorithm

3. The Price we Pay - UPDATES and INSERTS take longer

4. Dead Tuples and the VACUUM command

14. Database Administration and Access Control

1. Database Administration Basics

2. Postgres Schemas and Roles

3. Postgres Schemas, Roles and Groups

4. Controlling column and row access

15. Using Python to interface with the PostgreSQL database

1. A quick tour of basic Python concepts

2. Python functions

3. Using psycopg2 to connect to a database

4. Using variables to execute multiple queries in Postgres

16. A Primer on Flask Web Development

1. A primer on Flask Web development

2. Using Flask and Jinja2 templates to create dynamic websites

17. Building a 3-Tier Web Application

1. Introduction and Database setup

2. Building the students search page

3. Building the students scores page

4. Building the add new students page

5. Building the course statistics page

139,000 تومان

خرید اشتراک افزودن به سبد خرید

خرید دانلودی فوری

در این روش نیاز به افزودن محصول به سبد خرید و تکمیل اطلاعات نیست و شما پس از وارد کردن ایمیل خود و طی کردن مراحل پرداخت لینک های دریافت محصولات را در ایمیل خود دریافت خواهید کرد.

تولید کننده: Udemy-Training