Java 3000: SQL & SQLite, Part II (SQL3)
3.0 (4 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
1,009 students enrolled

Java 3000: SQL & SQLite, Part II (SQL3)

Discover the world of Views, Multi-Table Operations, Join Types, and more!
3.0 (4 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
1,009 students enrolled
Created by Randall Nagy
Last updated 8/2016
English
English [Auto-generated]
Current price: $23.99 Original price: $34.99 Discount: 31% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 3 hours on-demand video
  • 1 downloadable resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Understand how to use VIEW in SQL.
  • Practice creating and managing SQL Triggers.
  • Understand what "indexing" is, as well as how to create alternative SQL keys.
  • Discover the 100% fail-proof way to manage JDBC options for SQLite.
  • Discover how to use MUTLIPLE database files.
  • Understand On-Line Transaction Pressing (OLTP.)
  • Understand natural joins, inner joins, left, right, full, and outer joins.
  • Learn how to use inheritance to create new database entities.
  • Use Java Interfaces to create polymorphic data-access objects.
  • Discover what a “Database Entity” is, and why SMEs need to create them.
  • SME SPEAK: Learn what "ACID" means.
  • SME SPEAK: Discover the VDBE, and why it is important.
  • SME SPEAK: Understand what WAL is, and why it is important.
Requirements
  • This training assumes that you have installed SQLite, as well as the JDBC Driver.
  • Those who are new to using SQLite from Java should complete SQL2 before taking SQL3 (optional)
Description

From managing advanced SQL relationships to fail-safe SQLite configurations, in this third & final segment of our SQLite Training for Java you will learn how to enforce primary key relationships, create Database "Triggers," SQL Views, alternative Indexes, as well as other advanced database strategies.

Complex SQL JOIN types will also be reviewed, as well as the type of JOINS we can complete with SQL. 

Focusing on using JDBC, DevOps topics will be discussed from the vantagepoint of the Java programming environment.  Non-programmer techniques will also detail how to accomplish database backup, data exportation, duplication, as well as restoration from the Operating System.

Additional "SME Speak" topics will cover database theory (ACID,) as well as overview expert-level topics in SQLite for future research. Having covered the basics, be sure to enjoy this opportunity to impress your friends, co-workers & employers alike by learning the advanced features of SQL ... as well as SQLite!

Who this course is for:
  • Students who are looking to understand mode advanced SQL & SQLite Operations
  • Learners who want to be able to perform more advance queries.
  • Professionals who need to know more about how to configure & manage SQLite.
  • Developers who need to access more than one database file at a time.
  • Anyone who is interested in learn more about SQLite "SELECT" limitations.
Course content
Expand 19 lectures 02:52:19
+ Beyond The Basics
19 lectures 02:52:19
Enjoy a quick review of SQLite, as well as how we installed the SQLite Console during the SQL1 Training Session.
Preview 00:45

Much like any other SQL Database Technology, in order to use SQLite in Java, we will need to install a driver. 


Also much like any other JDBC Database, enjoying the widest cross-tech support of the type of statements & functions we use can take some trial and error. 


Because cross-technologies operations can be somewhat complex across different database technologies, the mission of my SQL3 training offering is to master the most advances, as well as the LESS TRIVIAL operations so as to allow the student to re-use their  own SQL objects (DAOs) across as many SQL Technologies as possible. 


Please note that if you have not worked with SQLite under Java before, then new students are encouraged to complete our SQL1 and SQL2 Training opportunities before completing SQL3.


Preview 01:04
  • Introduction to the ADVANCED TRACK
  • Discover how single statements operate as an “all or nothing” process.
  • See how transaction & batches can support business operations better.
  • Review how an “anatomic level of work” supports workflow.
  • Consider prepared statement efficiencies in anatomic operations.
  • Learn the “SME Speak” Database Professionals use to describe database technologies.
Preview 08:19
  • Learn how to use SQL Scripts to manage business operations.
  • Review how to create CSV reports.
  • Revisit the MightyMaxims workflow to import & parse SQL data.
Preview 03:52
  • Enjoy an in-depth review of ACID, as well as how each concept relates to SQLite.
4100 - Transactions, Part Two
10:54
  • Review the technologies required to create SQL for any JDBC Application.
  • Understand why matching file versions to API interfaces is important.
  • Discover how to “unlock” database if and when then become permanently locked.
  • Understand what is meant by an “Embedded” Database.
  • Review the basic set of transactional keywords, defaults, and alternatives.
  • Review a JDBC activity that uses Transactional Processing.
  • Discover the TRANSACTIONAL difference between “COMMIT” and “ROLLBACK.”
4200 - SQL Batched Operations
13:21
  • Learn how to detect if a JDBC Driver supports “Batch Mode.”
  • Discover how BATCH MODE is different from TRANSACTION MODE.
  • Review a JDBC activity that uses Batch Processing.
  • Learn how Prepared Statement become more efficient in transactional processing.
  • Learn how to manage & re-submit failed database operations in SQL.
4300 - SQL Batched Operations, Part Two
06:32
  • Understand this latest innovation in SQL transaction processing.
  • Learn when to use SAVEPOINTS, as well as how save-points are affected by traditional transnational events.
  • Understand what a statement stack is, as well as how savepoint management affects commit & rollback operations.
  • Discover how Savepoints are managed by JDBC, as well as by the textual SQL Standard.
  • Review a JDBC source-code example designed to manage SQL Savepoints using Java.
  • Understand the difference between RELEASE and ROLLBACK operations.
4400 - SQL Savepoints
15:37
  • Learn how SQLite manages ACID state transitions.
  • Discover how to create transactions that have EXCLUSIVE database access.
  • Understand the various stated required to access SQLite Databases.
  • Understand why it can be important to upgrade to SQLite 3.7 and beyond.
  • Discover what WAL is, and why it is important.
  • Discover the VDBE, and what the VDBE has in common with cross-tech operations.
4500 - SQL Lock States
11:28
  • Discover what an “Entity” is, and why SMEs need to understand how to create them.
  • Learn how to pick-and-choose between selected multi-table fields in SQLite.
  • Review a JDBC application designed to allow students to experiment with multi-table database entities.
4600 - Practical Joins
05:53
  • Apply what you have learned by creating an Entity that aggregates joined events between the Friends and well as Friend-Contact tables.
  • Review our solution & test case for the Entity-creation activity.
  • Learn how to use inheritance to create new database entities.
  • Review the use of Java Interfaces so as to create polymorphic data-access objects.
4601 - Dual Join - LAB
08:59
  • Understand the difference between natural joins, inner joins, left, right, full, and outer joins.
  • Learn which type of join operation work best with SQLite.
  • Review & update a JDBC example designed to test various SQL JOIN operations under SQLIte.
  • Discover why using field-names can be superior to using integral constants.
  • Understand what the “natural order” is, as well has how natural ordering ca impact result-set ordering.
  • Discover why outer joins can create better data exports.
4610 - Dual Join Review
08:50
  • Discover how to create VIEWS of entities.
  • Learn how to use VIEWS in select statements.
  • Understand the difference between TEMP and default view types.
4700 - SQL Views
13:00
  • Gain insight on how indexing works for Primary Keys.
  • Understand what indexing is, as well as when to avoid using it.
  • Discover the “two mode” os data collection activities.
  • Learn what composite keys are
  • Discover why the “3 second rule” can be too slow for data processing activities.
  • Learn how On-Line Transaction Pressing (OLTP) differs from analytical data processing.
  • Practice creating ad-hoc indexes using the SQLite console application.
4710 - SQL Indexing
08:12
  • Discover how to create & remove events that execute upon database changes.
  • Review a familiar metaphor to understand triggered events.
  • Practice creating a “work flow trigger” to manage an “Authors” table.
4720 SQL Triggers
07:35
  • Understand how to use multiple databases under SQLite.
  • Discover how multiple database files support multiple client operations.
  • Review how to access tables when multiple database files are used
4800 SQL - Using Mulitple Databases
11:56
  • Review primary key creation & enforcement concepts.
  • Review SQL PRAGMA, as well how to enforce keyed table relationships.
  • Discover the most effective way to manage SQLite Options.
  • Review a Java application that test key-management strategies.
4870 - SQLite Configuration
12:09
  • Delve deeper into the SQL options.
  • Review default connection string operations.
  • Review the reporting results from the previous module.
  • Review how to set SQL-Specific Pragmas from a JDBC Enumeration.
  • Discover SQLite's “Open Mode” and where to find the integral constants.
  • Discover how to execute a “VACUUM” using JDBC.
4875 - SQLite Configuration, Part Two
12:03
  • Understand how to manage default “auto vacuum” settings.
  • Review how to use path characters for running commands.
  • Complete a hands-on activity to dump & backup a database from the operating system.
  • Review a JDBC application designed to backup and restore databases from Java.
4900 - SQLite DevOps
11:50