MS-Access and SQL

MS-Access and SQL

Access is a relational database management system (RDBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. … It can also import or link directly to data stored in other applications and databases. Introduction to SQL

SQL Course overview

Installing the test environment

What is SQL?

Editors and Platforms to learn SQL

Complete SQL in a Class

Quick-start introduction

Using the basic SELECT statement

Selecting Rows

Selecting columns

Counting rows

Inserting data

Updating data

Deleting data

Import and Export data

Fundamentals of SQL

Databases and tables

SQL syntax overview

Data Definition, Data Manipulation, Data Control, Transactional Control statements

Creating tables

Deleting a table

Inserting rows into a table

Deleting rows from a table

What is NULL?

Controlling column behaviours with constraints

Changing a schema with ALTER

Filtering data with WHERE, LIKE, and IN

Removing duplicates with SELECT DISTINCT

Sorting with ORDER BY

How Relationships Work in SQL

Understanding Joins

Accessing related tables with JOIN

Multiple related tables

Explaining SQL Strings

About SQL strings

Finding the length of a string

Selecting part of a string

Removing spaces with TRIM

Making strings uppercase and lowercase

Numbers and SQL

About numeric types

Finding the type of a value

Integer division and remainders

Rounding numbers

SQL Functions and Clause

The Aggregate functions MIN, MAX, AVG, SUM and COUNT, UPPER, LENGTH, LOWER

The GROUP BY and HAVING clauses Grouping in a combination with joining

Triggers in SQL

Concept of Trigger

Create Trigger for (Insert, Update, Delete)

Alter Trigger

What are Subselects and Views in SQL

Creating a simple subselect

Searching within a result set

Creating a view

Creating a joined view

Maintaining SQL Server Database

Backup Database

Restore Database

SQL Server Job Creation

How to create job in SQL Server Agent

How to schedule job

Topics Covered as follows:

Access environment and tools

Database terminology and concept

Designing database in Access

Understanding RDBMS

Working with the Design side of Tables

Create Query

Join Tables That Have No Common Fields

Work with Subdatasheets

Create Sub queries

Working with the runtime of Tables

Data Migration and Importing

Working with the Design side of Queries

Working with the runtime of Queries

Working with the Design side of Forms

Adding Controls to Forms

Creating Sub forms

Organizing Information with Tab Pages

Displaying a Summary of Data in a Form

Applying Conditional Formatting

Working with the runtime of Forms, Managing Switchboard

Working with the Design side of Reports

Organize Report Information

Format Reports Include Charts in a Report

Add a Calculated Field to a Report

Add a Sub report to an Existing Report

Working with the runtime of Reports

Working with the Design side of Macros

Creating a Macro Restricting Records Using a Condition

Automating Data Entry Using a Macro

Working with the runtime of Macros

How to create a functional specification

Build a real-world business application

Putting altogether and deployment