SQL and Database Introduction

Databases drive nearly every Internet and Intranet Web site. This course teaches students how to master their power through a thorough grounding in SQL and sound database design fundamentals. SQL is the flexible, easy-to-use language for retrieving, adding, editing, and deleting information from databases. Sound database design concepts will help your databases to grow with your organization and provide flexible, responsive access to complex information.

After completing this course, students will be able to:

• Understand Database Vocabulary
• Incorporate Relational Database Concepts and Data Modeling
• Retrieve Data With SELECT
• Insert, Update, and Delete Data
• Insert, Update, and Delete Data
• Use Joins
• Create, Edit, and Drop Tables
• Implement Transactions
• Implement Transactions
  

Target audience

Students should have an excellent working knowledge of HTML gained through prior experience or from an attendance on an advanced level course.

Course content

Introduction: Databases and the Web Overview • The File System • Databases and the ACID Test

An Overview of SQL The Role of SQL • SQL History and Major DBMS vendors

Relational Databases Queries and Recordsets • Exercise 1: Building a Simple SQL Query

More about the Parts of Relational Databases Tables • Primary Keys, Foreign Keys, and Relationships

Retrieving Data with SELECT Comparison Operators for the WHERE Clause • Apostrophes in Database Text • Calculated Columns: Doing Math in Select Statements • Select Distinct • Selecting All Columns • Exercise 2: Selecting Data from the Database

Data Manipulation: INSERT, UPDATE, DELETE INSERT Statements • Exercise 3: Inserting Data into the Database

UPDATE Statements Exercise 4: Updating Campsales Data

DELETE Statements Exercise 5: Deleting Campsales Data

Data Retrieval: More About SELECT ORDER BY • Exercise 6: Practicing with ORDER BY • Aggregate Functions • Exercise 7: Practicing with Aggregate Functions • Extended WHERE Conditions • Exercise 8: Practicing with Extended WHERE Clauses

Fine-Tuning Aggregate Functions GROUP BY • The HAVING Clause • Exercise 9: Using GROUP BY and HAVING to Analyze Sales Data

Querying Data Across Tables Basic Joins • Exercise 10: Selecting From Multiple Tables

Reusing Queries as Views Exercise 11: Creating Views

Query Within A Query Subqueries • Exercise 12: Subqueries vs. Joins

Database Definition SQL CREATE TABLE • Exercise 13: Creating a Table for a Guest Book

Database Design Data Modeling • Handling more than one phone number • Exercise 14: Adding Columns with ALTER TABLE

Establishing a Primary Key Auto-numbered Columns

Relationships Between Tables Normalization • Exercise 15: Normalizing the guestbook Table • Online Transaction Processing vs. Decision Support

Restructuring Tables Renaming Tables or Columns • Adding or Deleting Columns • Exercise 16: Normalizing the Campsales Database

Data Integrity: Controlling What Goes Into a Database Default values • Exercise 17: Setting Default Values for our Guestbook • Check Constraints • Exercise 18: Checking Input • Creating Foreign Keys • Exercise 19: Defining Primary & Foreign Keys

Many-to-Many Relationships Tying It All Together: Creating The Movies Database

Introduction to Transactions

Appendix A: The Campsales Database Diagram of the relationships among the tables • Table Data Types • Sample Data (Pre-Exercises)

Appendix B: Major SQL Statements

Appendix C: Recommended Resources

About Courseware Company :: Instructor-led training courseware :: Self-study solutions :: Buy online today