Database Programming Using SQL (Structure Query Language)

Universitas Kristen Maranatha

Introduction

Welcome to the Course!
In today's data-driven world, mastering database programming is essential for developing robust, efficient, and scalable applications. This course, "Database Programming Using SQL," aims to provide you with the foundational knowledge and practical experience needed to understand and apply Structured Query Language (SQL) in real-world database systems.

Buku Pemograman Database
Why This Course is Important:
As organizations increasingly rely on data to drive decision-making, the ability to manage and manipulate databases is a critical skill. This course equips you with the tools to design, query, and manage relational databases, preparing you for roles in software development, data analysis, and information systems. Through hands-on SQL practice, you will gain the confidence and competence needed to work with modern database technologies.

Learning Goals

By the end of this course, students will be able to:

1. Understand the fundamentals of relational database systems, including tables, relationships, keys, and normalization principles.

2. Write and execute SQL queries to retrieve, filter, sort, and aggregate data from one or more tables using SELECT, WHERE, ORDER BY, GROUP BY, and JOIN.

3. Manipulate data using INSERT, UPDATE, and DELETE statements to manage the contents of relational databases.

4. Design and implement relational database schemas by creating tables, defining primary and foreign keys, and applying constraints using SQL Data Definition Language (DDL).

5. Apply advanced SQL techniques such as subqueries, nested queries, views, stored procedures, and transactions to solve complex data manipulation tasks.

6. Utilize SQL Server Management Studio or MariaDB as a development and testing environment for database programming.

7. Implement data integrity and security practices through constraints, user privileges, and transaction control (BEGIN, COMMIT, ROLLBACK).

8. Analyze and optimize SQL queries for better performance and scalability in real-world database applications.

Course Operation Period

Period : September 15, 2025 ~ November 30, 2025

Course Level

Intermediate

Prerequisites

To complete this course successfully, you should have:

1. A basic knowledge of database design

2. Some instructing or instructional database design experience

3. Fluency of English

Evaluation Standard

Evaluation Standard
Item	Title	Score(Rate)	Implementing Week	Method	Remarks
Assignment
Discussion
Midterm
Final Exam
Total		100

Summary of Curriculum

Summary of Curriculum
Topics and activities	Content name and URL	Remarks
Chapter 1: Introduction to Relational Databases A. Overview of Database Systems B. Introduction to RDBMS C. Components and Architecture of RDBMS Activities: • Lecture on DBMS vs RDBMS • Discussion: Real-life applications of relational databases • Lab: Introduction to Microsoft SQL Server and MariaDB installation	Chapter 1
Chapter 2: Introduction to Transact-SQL and SQL Syntax A. Overview of Transact-SQL (T-SQL) B. Introduction to Microsoft SQL Server Management Studio (SSMS) C. Creating Tables using T-SQL D. Modifying Table Structures using T-SQL E. Inserting, Updating, and Deleting Data F. SQL Operators G. Categories of SQL Syntax H. Differences between SQL Server and MySQL/MariaDB Syntax I. Syntax Comparison: SQL Server vs MySQL Activities: • Hands-on Lab: Creating and modifying tables using T-SQL in SSMS • Practice: Insert, update, and delete records with different SQL operators • Analysis: Compare SQL syntax between SQL Server and MySQL using real examples • Discussion: Identify key differences in T-SQL vs MySQL queries for common tasks	Chapter 2
Chapter 3: Views and Joins in SQL A. Introduction to Views and Their Benefits B. Practical Use Cases of Views C. Comparison of Views in SQL Server vs MySQL D. SQL Joins in SQL Server and MySQL/MariaDB: Activities: • Lab: Create views from single and multiple tables to summarize sales and purchases • Practice: Use aggregated views to generate monthly reports • Hands-on: Implement INNER, LEFT, RIGHT, and FULL JOINs on customer, sales, and product tables • Analysis: Compare how views and joins are implemented differently in SQL Server and MySQL	Chapter 3
Chapter 4: Stored Procedures in SQL A. What is a Stored Procedure? B. Advantages of Using Stored Procedures: C. Syntax Comparison: SQL Server vs MySQL D. Integrating Stored Procedures with Views: E. Stored Procedures for Inserting Data: F. Stored Procedures for Updating Data: H. Use of Variables in SQL: Activities: • Lab: Create and execute stored procedures to insert, update, and delete data • Practice: Secure data operations using stored procedures with parameters • Assignment: Combine stored procedures and views to produce sales and purchase reports • Exercise: Compare and implement variable usage in SQL Server and MySQL stored procedures	Chapter 4
Chapter 5: Using Functions in SQL A. What is a Function in SQL? B. Benefits of Using Functions in SQL: C. Types of SQL Functions: D. Function Categories (e.g., mathematical, string, date/time) E. Using Built-in Standard Functions F. Creating User-Defined Functions: G. Syntax Differences between SQL Server and MySQL H. Deterministic vs Non-Deterministic Functions Activities: • Lab: Practice using built-in functions to manipulate and analyze data • Workshop: Create custom scalar and table-valued functions • Assignment: Use user-defined functions to simplify complex queries • Comparison Task: Evaluate deterministic vs non-deterministic functions with examples in SQL Server and MySQL	Chapter 5
Chapter 6: Autonumbering in SQL A. Differences in Autonumber Terminology Across DBMS Platforms B. Implementing Autonumber in SQL Server and MySQL C. Using IDENTITY Columns for Autonumbering D. Autonumbering via Stored Procedures: E. Practical Exercises: Activities: • ⚙️ Lab: Define autonumbering columns using IDENTITY in SQL Server and AUTO_INCREMENT in MySQL • Practice: Create stored procedures to generate custom autonumber formats • Assignment: Complete exercises by creating tables and inserting data using SPInsert procedures • Comparison: Evaluate the flexibility of IDENTITY vs stored procedure-based autonumbering	Chapter 6
Chapter 7: Periodic Autonumbering in SQL A. Numbering with Annual Reset Format B. Numbering with Monthly Reset Format C. Numbering with Daily Reset Format: D. Use Cases and Examples for Periodic Autonumbering Activities: • ⚙️ Lab: Create stored procedures for autonumbering with daily, monthly, or yearly reset • Practice: o Create table and SPInsertPenjualanKasir o Create table and SPInsertPOGudang o Create table and SPInsertBarang • Analysis: Compare periodic autonumber techniques between SQL Server and MySQL • Discussion: When and why to use reset-based autonumbering in real-world business systems (e.g., invoices, purchase orders)	Chapter 7
Chapter 8: Using Triggers in SQL A. Definition and Purpose of Triggers B. Trigger Syntax in SQL Server and MySQL (INSERT/DELETE/UPDATE) C. How Triggers Work in SQL: D. Special Tables Used in Triggers: Activities: • ⚙️ Lab: Create INSERT, UPDATE, and DELETE triggers to maintain data integrity • Practice: Build triggers for automated tasks like history tracking or audit logging • Exercises: o Trigger to maintain integrity on insert o Trigger to validate or transform data on update o Trigger for auto-updating price history or stock status • Comparison: Analyze trigger behavior in SQL Server vs MySQL	Chapter 8
Chapter 9: Cursors in SQL A. What is a Cursor? B. Differences Between Cursors in SQL Server and MySQL C. Creating Tables and Inserting Sample Data for Cursor Operations D. Using Cursors in SQL Server and MySQL/MariaDB E. Scrollable Cursors: Activities: • Lab: Implement cursors to process row-by-row operations • Practice: Create stored procedures using cursors for: o Student attendance tracking o Student grade listing o Sales report by item o Calculating average sales per customer o Tracking sold item stock levels • Discussion: When to use cursors vs. set-based operations in SQL	Chapter 9
Chapter 10: SQL Program Flow and Error Handling A. What is a Variable in SQL? B. Differences in Variables between SQL Server and MySQL: C. Declaring Variables in SQL: D. Conditional Control Structures: IF...THEN...ELSE E. Block Structure: BEGIN...END F. Looping with WHILE G. Exiting a WHILE Loop H. Error Handling: I. CASE Statement for Conditional Logic Activities: • Lab: Use variables, conditions, and loops to simulate business logic • Practice: o Create stored procedures using IF, WHILE, and CASE o Implement error handling with TRY...CATCH (SQL Server) and HANDLER (MySQL) • Assignment: o Generate daily sales report per period o Create report of items that need restocking o Develop CheckPaymentStatus stored procedure	Chapter 10
Chapter 11: Transactions and Data Consistency in SQL A. Understanding Transactions and the Concept of Rollback B. The ACID Properties: C. ACID Implementation in SQL: D. Race Conditions, Locking, and Isolation Levels: Activities: • Lab: o Simulate transactions using BEGIN TRANSACTION, COMMIT, and ROLLBACK o Implement SAVEPOINT and ROLLBACK TO scenarios • ️ Practice: Handle race conditions using locking strategies and isolation levels • Case Study: o Explore real-world scenarios where transaction control ensures data integrity in concurrent environments • Final Review: Prepare for final exam/project with focus on transaction control and consistency mechanisms	Chapter 11
Chapter 12: Samples of Databases o Exploration of real or realistic sample databases o Review of best practices in database design o Sales Order database design comparison with other domains (Inventory, HR, Finance) • Activities: o Lab: Explore sample databases such as Northwind, AdventureWorks, and custom SalesOrderDB o Group project: Critique and improve the sample Sales Order schema o Reflection: Lessons learned from building a Sales Order database	Chapter 12

Textbook & References

PHP 4 Web Programming, Lee, 2002

Lecturer Information

Programming: Database; Computer Science; Hello, I am lecturer from Universitas Kristen Maranatha; teddy.marcus@it.maranatha.edu

Video Editor/Art Director

LPAA-BKAKP Maranatha: Bagus Andika Palay; Manik Pujangga Nashrullah; Sultan Raditya Sujono; Rama Ramadhan

How to provide assistance for learners

What does this course cover specifically?

You can learn about various computer engineering fields such as RDMS fundamental, programming, Structured Query Language, etc. and problem solving based on actual cases.

Do you have a separate course textbook?

Here are some basic and detailed books on this course: However, based on the weekly lecture data file, the contents of each unit are summarized. Pemrograman Database Membangun Aplikasi dengan Microsoft SQL Server dan MariaDB MySQL, Teddy Marcus Zakaria|Setia Budi|Rossevine Artha Nathasya (ISBN: 9786234665499), Zahir Publishing)