Intermediate SQL: Querying, Data Manipulation, and Security

via Coursera

Go to class Write review

Details

Go to class

Provider

Coursera
Pricing

Paid Course
Languages

English
Certificate

Certificate Available
Effort

12 weeks, 1 hour a week
Sessions

Self-Paced
Level

Intermediate
Subtitles

English

Found in

Overview

Google, IBM & Meta Certificates – 40% Off

One plan covers every Professional Certificate on Coursera.

Unlock All Certificates

This course dives deep into SQL techniques, focusing on querying, data manipulation, and security. Learners will develop skills that are crucial for working with databases in real-world applications, from retrieving data to ensuring its integrity. The course covers advanced SQL queries, modifying clauses, handling complex joins, and understanding database security practices. You’ll learn how to work with subqueries, relational operators, and cursors, while mastering ways to protect data against hardware failure and external threats. By combining theoretical knowledge with hands-on examples, this course provides practical skills for professional database developers. What sets this course apart is its blend of theory and real-world scenarios. You'll not only grasp the core concepts but also apply them to real database management tasks like securing user access and handling errors. The course prepares you for various challenges faced by database administrators and developers. This course is ideal for intermediate-level learners with a foundational understanding of SQL who want to enhance their querying and database security skills. Prior experience with basic SQL is recommended. This course is part two of a three-course Specialization designed to provide a comprehensive learning pathway in this subject area. While it delivers standalone value and practical skills, learners seeking a more integrated and in-depth progression may benefit from completing the full Specialization. From SQL All-in-One For Dummies Copyright © 2024 by John Wiley & Sons, Inc., Hoboken, New Jersey Used by arrangement with John Wiley & Sons, Inc.

Syllabus

Values, Variables, Functions, and Expressions

This module introduces essential SQL tools for data preprocessing, focusing on set and value functions, expressions, and data type conversions. Learners will discover how to manipulate, summarize, and transform data efficiently to support accurate analysis. Practical examples illustrate the use of conditional expressions and casting for flexible data handling.

SELECT Statements and Modifying Clauses

This module introduces essential SQL techniques for retrieving and manipulating data efficiently. Learners will explore how to filter, group, and sort data using various clauses, and discover strategies for optimizing query performance in real-world database scenarios.

Querying Multiple Tables with Subqueries

This module delves into the use of subqueries in SQL to retrieve and manipulate data across multiple related tables. Learners will explore different types of subqueries, including single-value, quantified, and correlated subqueries, and learn how to incorporate them into SELECT, INSERT, UPDATE, and DELETE statements. The module also covers performance considerations and tuning techniques for optimizing nested queries.

Querying Multiple Tables with Relational Operators

This module introduces key SQL techniques for combining data from multiple tables, including the use of relational operators such as UNION, INTERSECT, and EXCEPT, as well as various JOIN strategies. Learners will gain practical skills in constructing complex queries and understanding the differences between join types and their clauses.

Cursors

This module introduces the concept of SQL cursors for row-by-row data processing, examining different cursor types and their behaviors. Learners will discover how to control data retrieval order and integrate procedural logic with SQL for advanced data manipulation. Practical examples illustrate how to declare, open, and manage cursors effectively.

Protecting Against Hardware Failure and External Threats

This module examines strategies to safeguard database systems from hardware failures and external threats. Learners will explore RAID configurations, backup methods, and common security vulnerabilities such as SQL injection and phishing. By the end, you will understand how to enhance database reliability and defend against both physical and cyber risks.

Protecting Against User Errors and Conflicts

This module delves into strategies for maintaining database integrity and performance in the face of user errors and concurrent access. Learners will explore transaction management, ACID properties, locking mechanisms, and techniques for handling constraints and partitioning. By the end, you'll understand how to safeguard data consistency and optimize database operations.

Assigning Access Privileges

This module introduces the principles of database access control using SQL Data Control Language (DCL). Learners will discover how to classify users, assign and revoke privileges, and safeguard data integrity by managing roles and permissions effectively.

Error Handling

This module introduces strategies for managing SQL errors, including interpreting SQLSTATE codes, utilizing the diagnostics area, and implementing the WHENEVER directive to handle exceptions. Learners will gain practical skills in detecting, analyzing, and responding to SQL execution issues to ensure robust application behavior.

Database Development Environments

This module introduces the landscape of major relational database management systems, highlighting their market presence, core features, and SQL implementations. Learners will explore practical considerations for selecting and working with different DBMS products, with a focus on Microsoft SQL Server and its unique capabilities.

Interfacing SQL to a Procedural Language

This module introduces the integration of SQL with procedural programming languages to create robust database applications. Learners will discover how client libraries, such as MySQL's C API, enable communication between application code and database servers. Practical examples illustrate how to bridge the gap between SQL's data manipulation capabilities and the control structures of procedural languages.

Using SQL in an Application Program

This module examines how SQL can be integrated with popular programming languages such as C, C++, Java, and Python. Learners will investigate the challenges of combining SQL with procedural code, including data type mismatches and deprecated embedding techniques. Practical examples illustrate modern approaches to embedding SQL within application programs.