Overview

Details

Associate in Arts Degree, Database Development Practitioner

summa cum laude, GPA: 4.0
De Anza College, 2024-08-23

PDF | Return to overview

The Database Development Practitioner A.A. prepares students for a position in the database field to work as a data analyst, business analyst, database project coordinator or database engineer. It includes database management system fundamentals, SQL, PL/SQL, large scale data processing and big data and analytics. Students become proficient in organizing essential information and abstract relationships into a database. They also learn to update, maintain and repair databases. Database skills are applied by software engineers, business analysts, database architects, database administrators, database designers and reporting analysts.

PDF | External link | Return to overview

CS 3A • OBJECT-ORIENTED PROGRAMMING METHODOLOGIES IN PYTHON • 4.5 Units

Systematic introduction to fundamental concepts of computer science through the study of the Python programming language. Coding topics include control structures, functions, classes, string processing, lists, tuples, dictionaries, working with files, and elementary graphics. Concept topics include algorithms, data abstraction, problem solving strategies, code style, documentation, debugging techniques and testing.

Student Learning Outcomes

• A successful student will be able to write and debug Python programs which make use of the fundamental control structures and function-building techniques common to all programming languages. Specifically, the student will use data types, input, output, iterative, conditional, and functional components of the language in his or her programs.

• A successful student will be able to use object-oriented programming techniques to design and implement a clear, well-structured Python program. Specifically, the student will use and design classes and objects in his or her programs.

PDF | External link | Return to overview

CS 3B • INTERMEDIATE SOFTWARE DESIGN IN PYTHON • 4.5 Units

Systematic treatment of intermediate concepts in computer science through the study of Python object-oriented programming (OOP). Coding topics include Python sequences, user-defined classes and interfaces, modules, packages, collection classes, threads, lambda expressions, list comprehensions, regular expressions and multi-dimensional arrays. Concept topics include OOP project design, recursion, inheritance, polymorphism, functional programming, linked-lists, FIFOs, LIFOs, event-driven parsing, exceptions, and guarded code.

Student Learning Outcomes

• A successful student will be able to use the Python environment to define the basic abstract data types (stacks, queues, lists) and iterators of those types to effectively manipulate the data in his or her program.

• A successful student will be able to write and debug Python programs which make use of inheritance, i.e., the "is a" relationship, common to all OOP languages. Specifically, the student will define base and derived classes and use common techniques such as method chaining in his or her programs.

PDF | External link | Return to overview

CS 3C • ADVANCED DATA STRUCTURES & ALGORITHMS IN PYTHON • 4.5 Units

A systematic treatment of advanced data structures, algorithm analysis, and abstract data types in the Python programming language, intended for computer science majors as well as non-majors and professionals seeking advanced Python experience. Coding topics include large program software engineering design, multi-dimensional arrays, string processing, primitives, compound types, and allocation of instance and static data. Data structure concept topics include dynamic memory, inheritance, polymorphism, hierarchies, recursion, linked-lists, stacks, queues, trees, hash tables, and graphs. Algorithm concept topics include searching, big-O time complexity, analysis of all major sorting techniques, top down splaying, AVL tree balancing, shortest path algorithms, minimum spanning trees, and maximum flow graphs.

Student Learning Outcomes

• The successful student will be able to write and incorporate balanced trees, hash tables, directed graphs and priority queues in his or her software.

• The successful student will be able to analyze the time complexity of a variety of algorithms and data structure access techniques and choose the best algorithm and/or data structure for the project at hand.

PDF | External link | Return to overview

CS 31A • INTRODUCTION TO DATABASE MANAGEMENT SYSTEMS • 4.5 Units

Introduction to database design and use of database management systems for applications. Topics include database architecture, comparison to file-based systems, historical data models, conceptual model; integrity constraints and triggers; functional dependencies and normal forms; relational model, algebra, database processing and Structured Query Language (SQL), database access from Applications-Embedded SQL, JDBC, Cursors, Dynamic SQL, Stored Procedures. Emerging trends will be studied, such as NoSQL databases, internet and databases, and Online Analytical Processing (OLAP). A team project that builds a database application for a real-world scenario is an important element of the course.

Student Learning Outcomes

• Create a conceptual database design

• Use Structured Query Language to perform queries on a database

PDF | External link | Return to overview

CIS 44F • Introduction to Big Data and Analytics • 4 Units

This course is an introduction to Big-Data deluge, management of unstructured and structured data and design of large scale database systems. Concepts covered include map-reduce parallel processing algorithms, real-time analytics, classification, and predictive analytics, attributes of Big-Data and related issues. The course also introduces large-scale file systems and operations and parallel processing algorithms.

Student Learning Outcomes

• Design, implement and debug a large scale database system using technology like Hadoop or Cassandra.

• Perform data analysis using a large-scale database systems given a set of user requirements.

PDF | External link | Return to overview

CIS 64B • Introduction to SQL • 4.5 Units

Introduction to Oracle SQL (Structured Query Language), DML (Data Manipulation Language) processing techniques, DDL (Data Definition Language) techniques, selecting and sorting data, joins, SQL functions, Oracle objects, Oracle data processing concepts to maintain large database systems.

Student Learning Outcomes

• Design solutions for introductory level problems using appropriate design methodology incorporating interpreted database constructs.

• Create algorithms, code, document, debug, and test introductory level SQL programs.

PDF | External link | Return to overview

CIS 64C • Introduction to PL/SQL • 4.5 Units

This course covers Oracle PL/SQL features including data definition and data manipulation using expressions, control structures, and Oracle objects. Error handling, predefined packages, triggers, transactions, and advanced PL/SQL features are also covered.

Student Learning Outcomes

• Design solutions for introductory level problems using appropriate design methodology incorporating procedural database constructs.

• Create algorithms, code, document, debug, and test introductory level PL/SQL programs.

PDF | External link | Return to overview

CIS 9 • Introduction to Data Science • 4.5 Units

This course is an introduction to data science, which covers data analytics and machine learning. Topics covered include data gathering and data wrangling, data assessment and visualization, supervised and unsupervised machine learning, natural language processing.

Student Learning Outcomes

• Collect, clean, analyze, and visualize data to meet and defend a measured objective.

• Gather data and choose a model to train and tune the machine learning tool and interpret the result.

PDF | External link | Return to overview

CIS 44H • R Programming • 4.5 Units

This course is an introduction to the R programming language and its utility in big data analytics. Topics covered include data objects, data cleansing, merging and sorting, statistical analysis of data, data graphics, and visualization, and working with R-Studio.

Student Learning Outcomes

• Design, implement and debug R programs to process data from various sources for data analysis.

• Use R-graphics to display and visualize data.

PDF | External link | Return to overview

CIS 18A • Introduction to Unix/Linux • 4.5 Units

This course is an introduction to the features of the Unix/Linux operating system including text editing, text file manipulation, electronic mail, Internet utilities, directory structures, input/output handling, and shell features.

Student Learning Outcomes

• Use the Unix/Linux Operating System utilities and shell features for basic file manipulation, networking, and communication.

PDF | External link | Return to overview

CIS 18B • Advanced UNIX/LINUX • 4.5 Units

Expanded coverage of regular expressions and grep. Advanced topics in Unix/Linux include egrep, find, sed, awk, file archiving, compression, and conversion, version control, makefile, basic shell scripts and installation of a Linux distribution.

tudent Learning Outcomes

• Use the Unix/Linux Operating System utilities, shell features, and regular expressions for advanced text file manipulation.

PDF | External link | Return to overview

PostgreSQL for Everybody Specialization

University of Michigan, 2022-08-17

PDF | External Link | Return to overview

Across these four courses, you’ll learn how to use the PostgreSQL database and explore topics ranging from database design to database architecture and deployment. You’ll also compare and contrast SQL and NoSQL approaches to database design. The skills in this course will be useful to learners doing data mining or application development.

Applied Learning Project

This course series utilizes a custom autograding environment for an authentic set of graded and practice assignments, including: creating and manipulating tables, designing data models, constructing advanced queries, techniques for working with text in databases, including regular expressions, and more.

What you'll learn

• How to use the PostgreSQL database effectively

• Explore database design principles

• Dive into database architecture and deployment strategies

• Compare and contrast SQL and NoSQL database design approaches and acquire skills applicable to data mining and application development

External link | Return to overview

Database Architecture, Scale, and NoSQL with Elasticsearch

University of Michigan, 2022-08-17

PDF | External Link | Return to overview

In this final course, you will explore database architecture, PostgreSQL, and various scalable deployment configurations. You will see how PostgreSQL implements basic CRUD operations and indexes, and review how transactions and the ACID (Atomicity, Consistency, Isolation, Durability) requirements are implemented.

You’ll learn to use Elasticsearch NoSQL, which is a common NoSQL database and a supplement to a relational database to high-speed search and indexing. We will examine Elasticsearch as an example of a BASE-style (Basic Availability, Soft State, Eventual Consistency) database approach, as well as compare and contrast the advantages and challenges associated with ACID and BASE databases.

What you'll learn

• Understand PostgreSQL architecture; analyze and compare SQL and NoSQL.

• Compare and contrast ACID and BASE style architectures and databases.

• Create and utilize an Elasticsearch index in different contexts.

External link | Return to overview

JSON and Natural Language Processing in PostgreSQL

University of Michigan, 2022-07-04

PDF | External Link | Return to overview

Within this course, you’ll learn about how PostgreSQL creates and uses inverted indexes for JSON and natural language content. We will use various sources of data for our databases, including access to an online API and spidering its data and storing the data in a JSON column in PostgreSQL. Students will explore how full-text inverted indexes are structured. Students will build their own inverted indexes and then make use of PostgreSQL built-in capabilities to support full-text indexes.

What you'll learn

• Compare Python, PostgreSQL, and JSON.

• Index and retrieve natural language text and JSON data; access API data and store it in a database.

• Create a GIN-based text[] reverse index and ts_vector index; build a search engine in PostgreSQL.

External link | Return to overview

Intermediate PostgreSQL

University of Michigan, 2022-07-02

PDF | External Link | Return to overview

This course covers a wide range of SQL techniques, beyond basic CRUD (Create, Read, Update, and Delete) operations in PostgreSQL. You will learn the specifics of aggregation, transactions, reading and parsing CSV files and inserting data into a database. You’ll also take a look at how PostgreSQL handles and indexes text data.

Specifically, students will do assignments that alter table schemas, create stored procedures, construct advanced queries, explore sorting and grouping query data, and techniques for working with text in databases including regular expressions.

What you'll learn

• Utilize SQL commands for editing tables in a PostgreSQL database and produce properly normalized tables from CSV files.

• Appropriately handle text and dates in databases and create stored procedures.

• Identify hashtag algorithm and their attributes.

• Construct regular expressions to select rows that match a pattern.

External link | Return to overview

Database Design and Basic SQL in PostgreSQL

University of Michigan, 2022-06-26

PDF | External Link | Return to overview

In this course you will learn more about the historical design of databases and the use of SQL in the PostgreSQL environment. Using SQL techniques and common commands (INSERT INTO, WHERE, ORDER BY, ON DELETE CASCADE, etc) will enable you to create tables, column types and define the schema of your data in PostgreSQL. You will learn about data modeling and how to represent one-to-many and many-to-many relationships in PostgreSQL. Students will do hands-on assignments creating tables, inserting data, designing data models, creating relational structures and inserting and querying relational data in tables.

What you'll learn

• Utilize psql and SQL commands to implement CRUD (Create, Read, Update, and Delete) operations for tables in a PostgreSQL database.

• Identify and utilize the functions of primary, logical, and foreign keys within a database.

• Build and differentiate between one-to-many and many-to-many relationships within PostgreSQL.

• Recall key people, organizations, and innovations that were instrumental to building the SQL standard.

External link | Return to overview

Using Databases with Python

University of Michigan, 2024-09-06

PDF | External Link | Return to overview

This course will introduce students to the basics of the Structured Query Language (SQL) as well as basic database design for storing data as part of a multi-step data gathering, analysis, and processing effort. The course will use SQLite3 as its database. We will also build web crawlers and multi-step data gathering and visualization processes. We will use the D3.js library to do basic data visualization. This course will cover Chapters 14-15 of the book “Python for Everybody”. To succeed in this course, you should be familiar with the material covered in Chapters 1-13 of the textbook and the first three courses in this specialization. This course covers Python 3.

What you'll learn

• Use the Create, Read, Update, and Delete operations to manage databases

• Explain the basics of Object Oriented Python

• Understand how data is stored across multiple tables in a database

External link | Return to overview

Advanced SQL for Application Development

LinkedIn Learning, 2024-07-30

PDF | External Link | Return to overview

Many applications require a relational database. But poorly designed data architecture and poorly written SQL can result in subpar performance, unreliable services, and difficulty scaling. This course includes hands-on examples and lessons that show how to build scalable and resilient databases to support any application. Learn how to write optimized SQL for transaction processing, use indexes to reduce read latency, partition data to improve scalability, and use established design patterns. Instructor Dan Sullivan also explores object relational mapping and shows how to respond to database errors such as query timeouts and refused connections. After completing this course, you will be able to design robust database applications that can scale to meet increasingly demanding workloads. Learning objectives

• Creating tables and loading data
• Parameterizing SELECT statements
• Indexing tables
• Object relational mapping
• Partitioning
• Error trapping
• Monitoring and logging
• Automated schema migration

External link | Return to overview

Advanced Python: Practical Database Examples

LinkedIn Learning, 2024-07-12

PDF | External Link | Return to overview

Looking for a hands-on opportunity to take your Python skills to the next level? In this course, instructor Kathryn Hodge takes you through a series of practical database examples to help level up your Python applications.

Learn how to create an API that serves data from a database using FastAPI, Flask, MySQL, Postman, SQLAlchemy, endpoints, and more. Get proven tips on how to develop analysis applications with pandas, the high-performance Python library featuring robust and integrated built-in data structures. Test out your new coding skills as you go in the exercise challenges at the end of each section. By the end of this course, you’ll be ready to start building full-stack task list applications with Flask, a microframework designed uniquely for Python that lets you integrate data from a database directly to an app.

External link | Return to overview

Snowflake Cloud Data Platform: Getting Started

Edureka, 2024-06-05

PDF | External Link | Return to overview

Step into the world of Snowflake Cloud Data Platform: Getting Started course, where you will explore Snowflake and learn the fundamentals that will give you the foundation to navigate and leverage this powerful cloud data platform.

During this course, you will explore the applications of Snowflake and delve into its fundamentals. By the end of this course, you will be able to:

• Discuss the core concepts, benefits, and architecture of Snowflake.

• Apply UDFs, procedures, and streams for efficient data management.

• Organize account access and security protocols effectively.

• Illustrate Snowflake's performance and query execution.

• Demonstrate proficiency in storage management and data protection.

• Apply data-sharing techniques within the Snowflake platform.

This introductory short course is designed for Freshers, Database Engineers, Database Administrators, and SQL Developers seeking to explore the Snowflake Cloud Data Platform.

Prior working experience with RDBMS and SQL is recommended but not mandatory for this course.

Begin an educational journey to learn about the Snowflake Cloud Data Platform and its concepts for handling and analyzing large volumes of data.

What you'll learn

• Working with Snowflake UDFs, account security, virtual warehouse, storage, data protection, and data sharing to leverage Snowflake's capabilities.

External link | Return to overview

Choosing a Database: PostgreSQL, MySQL, Mongo, and Cloud

LinkedIn Learning, 2024-04-19

PDF | External Link | Return to overview

There are a lot of different databases out there with their own pros and cons. It’s important to know what your options are. In this course, instructor Amataverna Lee covers PostgreSQL, MySQL, Mongo (a NoSQL database), and several cloud-based databases, to help you choose what will be best for you. You’re likely to encounter PostgreSQL in your career, so it’s important to be familiar with this type of database and how it differs from other types. MySQL is one of the most popular SQL dialects used today, so this is another important one to understand. Amataverna introduces you to Mongo, a nonrelational database that does not use SQL, and shows you some tools that you would need to use in Mongo. She also goes over several popular cloud-based options: Snowflake, Snowsight, Amazon Web Services (Redshift and S3), and Google Cloud Platform. With an understanding of these options, you can make a more informed choice on what is best for you.

External link | Return to overview

Data Cleaning in Snowflake: Techniques to Clean Messy Data

Coursera Project Network, 2024-04-16

PDF | External Link | Return to overview

in 2006, the British mathematician Clive Humby coined the phrase "Data is the new Oil". This analogy has been proven correct as data powers entire industries nowadays but if left unrefined, is effectively worthless.

This 2.5 hours-long guided project is designed for business analysts & data engineers eager to learn how to Clean Messy Data in Snowflake Data Platform. By the end of the project, you will

• Be able to identify common data quality issues then use SQL String functions to remove unwanted characters and split rows into multiple columns.

• Extract dates from Text fields then use SQL date functions for comparisons and calculations.

• Identify and correct missing and duplicated data then answer business questions using SQL statements.

To achieve these objectives, we will work on a real example from the field, you will play the role of a Data Analyst in the marketing department, who has been tasked with answering a business question, but the customer data they have received presents several data quality challenges. What you'll learn

• Use Snowflake UI to identify data quality issues then use SQL String functions to remove unwanted characters and split rows into multiple columns.

• Extract dates from Text fields then use SQL date functions for comparisons and calculations.

• Identify and correct missing and duplicated data and answer business questions using aggregation statements.

External link | Return to overview

Learning SnowflakeDB

LinkedIn Learning, 2024-03-25

PDF | External Link | Return to overview

Are you in the process of trying to scale your business but bumping up against storage and capacity limits? Rather than shelling out more resources for expensive hardware and software licenses, consider Snowflake, the popular data warehouse and data management platform with unique data exchange capabilities for your business. Join instructor Lynn Langit and learn about how to use Snowflake—the power of the cloud awaits you.

Discover why Snowflake is one of the most rapidly growing cloud-based data management platforms in the industry. Explore the features and functions of the platform, what Snowflake does, how it works, and how to get the most out of its data storage and query processing power with hands-on examples, demos, and scenarios along the way. Lynn gives you tips and techniques for account management, authentication, security protocols, architectural patterns, the data life cycle, and more.

External link | Return to overview

Advanced Snowflake: Deep Dive Cloud Data Warehousing and Analytics

LinkedIn Learning, 2024-03-25

PDF | External Link | Return to overview

If you’re familiar with the Snowflake platform but need to learn more, this course can help you supercharge your skills. Instructor Janani Ravi deep dives into architecture and advanced concepts. After a concise introduction, Janani gets started with table structures in Snowflake, covering topics like micropartitioning, clustering, and creating permanent, temporary, and transient tables. She explains views in Snowflake, then demonstrates several useful techniques for optimizing query performance. Janani goes over working with semi-structured data, including loading and querying parquet data and JSON data. Plus, she shows you how to articulate the access control system in Snowflake, create users, view grants, and grant privileges to roles.

External link | Return to overview

Everybody's Introduction to Snowflake

LinkedIn Learning, 2024-03-23

PDF | External Link | Return to overview

Data—and by extension, databases—rule much of our lives. And if you’re a data analyst or work with data at all, understanding databases is not just a desirable skill, it’s a necessity. In this course, get an introduction to Snowflake, one of the newest databases in the market that’s gaining popularity and a loyal following. Tim Ngwena teaches you about this cloud-based data platform designed to power your data-driven applications and analytics. Tim starts with a look at the basic concepts of databases, before diving into the unique features of Snowflake, including its architecture, how it works, security features, and more. He highlights its core strengths—and details situations when Snowflake outperforms traditional databases.

External link | Return to overview

Advanced PostgreSQL

LinkedIn Learning, 2023-09-12

PDF | External Link | Return to overview

PostgreSQL is a powerful, open-source, object-relational database system. It’s popular around the world, given its strong reputation for reliability, feature robustness, and performance. In this course, Janani Ravi dives into advanced topics in PostgreSQL. After showing how to install and set up PostgreSQL, Janani shows you how to work with geospatial data, which allows you to specify geographic objects such as locations, lines, and polygons, all on a geographic map, and also supports location-based queries like distance computation and area computation. Next, learn about performing full-text search operations, and how to work with triggers, which let you set up an automated execution of a function when a certain type of operation is performed. Finally, Janani explores transactions, which allow you to bundle multiple steps into a single, all-or-nothing operation.

External link | Return to overview

Advanced SQL – Window Functions

LinkedIn Learning, 2023-08-31

PDF | External Link | Return to overview

Window functions are one of the most radical, fundamental enhancements to modern SQL. They allow access to neighboring rows without using subqueries, thus enabling amazing opportunities for concise, elegant, high-performing solutions.

This course teaches the foundations and intricacies of window function processing and how to use it to implement practical solutions to everyday challenges. You can learn how to use different constructs and advanced solution techniques and how to utilize the declarative and composable nature of SQL and its processing order. By the end of the course you’ll better understand the fundamental pros and cons of each method.

External link | Return to overview

Oracle Database 19c: PL/SQL

LinkedIn Learning, 2023-08-29

PDF | External Link | Return to overview

PL/SQL is the companion programming language to Oracle Database. Having a modern programming language that integrates with the database itself makes the Oracle Database platform scalable and extensible for more applications. In this course, Bob Bryla reviews the major features of Oracle PL/SQL under Oracle Database 19c. Bob covers key aspects of this procedural language, including looping, conditional statements, and exception handling when something goes wrong. He shows how to more effectively use the built-in procedures of PL/SQL, as well as write your own. Bob highlights ways to simplify coding of loop iteration in PL/SQL programs. Plus, he covers the PL/SQL security model and demonstrates how to run SQL queries and DDL statements from within PL/SQL.

External link | Return to overview

Analyzing Big Data with Hive

LinkedIn Learning, 2023-07-12

PDF | External Link | Return to overview

Businesses thrive by making informed decisions that target the needs of their customers and users. To make such strategic decisions, they rely on data. Hive is a tool of choice for many data scientists because it allows them to work with SQL, a familiar syntax, to derive insights from Hadoop, reflecting the information that businesses seek to plan effectively.

This course shows how to use Hive to process data. Instructor Ben Sullins starts by showing you how to structure and optimize your data. Next, he explains how to get Hue, the Hadoop user interface, to leverage HiveQL when analyzing data. Using the newly configured option, he then demonstrates how to load data, create aggregate tables for fast query access, and run advanced analytics. He also takes you through managing tables and putting functions to use. This course is designed to help you find new ways to work with datasets so you can answer the tough data science questions that come your way.

External link | Return to overview

Advanced Python: Working with Databases (2020)

LinkedIn Learning, 2023-04-30

PDF | External Link | Return to overview

To create functional and useful Python applications, you need a database. Databases allow you to store data from user sessions, track inventory, make recommendations, and more. However, Python is compatible with many options: SQLite, MySQL, and PostgreSQL, among others. Selecting the right database is a skill that advanced developers are expected to master. This course provides an excellent primer, comparing the different types of databases that can be connected through the Python Database API. Instructor Kathryn Hodge teaches the differences between SQLite, MySQL, and PostgreSQL and shows how to use the ORM tool SQLAlchemy to query a database. The final chapters put your knowledge to practical use in two hands-on projects: developing a full-stack application with Python, PostgreSQL, and Flask and creating a data analysis app with pandas and Jupyter Notebook. By the end, you should feel comfortable creating and using databases and be able to decide which Python database is right for you.

External link | Return to overview

PostgreSQL Essential Training (2020)

LinkedIn Learning, 2023-04-02

PDF | External Link | Return to overview

PostgreSQL is trusted with mission critical information by some of the biggest companies in the world. It’s also highly flexible, which makes it a great choice for data science researchers, educators, nonprofit organizations, and businesses of all sizes. In this course, Adam Wilbert, who has spent the last decade helping people take their first steps in the world of relational databases, introduces you to PostgreSQL. Adam demonstrates how to set up your own server and create a customized relational database. He shows you the features of PostgreSQL that protect your data from unauthorized access, ensure that it’s accurate, and simplify the process of querying information so that you can make actionable decisions. PostgreSQL is a powerful, robust platform. Get started learning how it works!

External link | Return to overview

Getting Started with the Geodatabase

Esri Training, 2023-02-10

PDF | Return to overview

The geodatabase is the native data storage format for ArcGIS. Learn about geodatabase components and functionality as well as steps to create and add data to a file geodatabase. This course prepares you for courses that focus on advanced geodatabase components.

External link | Return to overview

Advanced SQL for Query Tuning and Performance Optimization (2019)

LinkedIn Learning, 2022-09-18

PDF | External Link | Return to overview

SQL queries can be fast and highly efficient, but they can also be slow and demand excessive CPU and memory resources. For many SQL programmers, occasional bouts with long-running queries and poor performance are simply par for the course. But by gaining a better understanding of how databases translate SQL queries into execution plans, you can take steps to avoid these issues. In this course, Dan Sullivan shows you how to analyze query execution plans and use data modeling strategies to boost query performance. Dan describes how SQL queries are executed, highlights different types of indexes and how they factor in query tuning, covers several methods for performing joins, and discusses how to use partitioning and materialized views to improve performance. Plus, Dan shows you how to run PostgreSQL in GitHub Codespaces so you can get started learning faster.

External link | Return to overview

Advanced SQL for Data Scientists

LinkedIn Learning, 2022-09-18

PDF | External Link | Return to overview

Many data scientists know how to work with SQL—the industry-standard language for data analysis. But as data sizes grow, you need to know how to do more than simply read and write from a database. This course provides a more sophisticated approach to designing data models and optimizing queries in SQL. Instructor Dan Sullivan begins with the logical and physical design of tables—with particular focus on very large databases—and then presents a deep dive review of indexes, including specialized indexes and when to use them. The next section introduces query optimization and shows how to optimize basic, multi-join, and more complex queries. The course also covers SQL extensions, including user-defined functions and specialized data types. The techniques taught here enable more efficient analysis of large data sets using SQL, statistics, and custom business logic.

External link | Return to overview

Introduction to Spatial Databases with PostGIS and QGIS 3

Udemy, 2022-08-14

PDF | External Link | Return to overview

This course is intended to provide an introduction to spatial databases in general and PostGIS in particular to GIS professionals who are interested in expanding their skillset to multi-user enterprise level spatial databases. This course uses the latest (as of 2/18) versions of PostgreSQL (10.1), PostGIS (2.4), and QGIS (3.0). I believe it is the most current and thorough course on spatial databases available today. You will learn

• What a spatial database is and why you would want to use one.
• What SQL is, why you would want to use it, and how it can be applied to geospatial concepts.
• How to install PostGIS locally for development purposes and how to access a production version via a network or the internet.
• How to load your spatial data into PostGIS and access it from a variety of clients, especially QGIS
• The basics of SQL for both spatial and non-spatial queries
• How to validate data and control user access with the tools built-in to PostGIS
• Optimizing your queries for the best performance
• How to work with raster data in PostGIS
• The basics of programming custom functions with PL/pgSQL
• The basics of database administration to keep your database operating smoothly

External link | Return to overview