MS Access & SQL – Fundamentals to Advanced

Start Dates: TBC

Duration: 1 Day

CPD Hours: 8 CPD hours per day

Full Fee: €100

Network Members Fee: €60

https://www.ifsskillnet.ie Book Now

Programme overview

In this training, you learn how to optimise the accessibility and maintenance of data with the SQL programming language, and gain a solid foundation for building, querying, and manipulating databases. This course describes the standard language, but also identifies deviations from the standard in two widely-used database products, Oracle and Microsoft SQL Server. For the hands-on exercises, the course environment allows the students to choose between working with the PostgreSQL database product or Microsoft SQL Serve

Learning outcomes:

Write SQL code based on ANSI/ISO standards to build database
structures.
Update database content with SQL and transaction handling
Retrieve data with filter conditions and from multiple tables using
various types of join.
Process data with row and aggregate functions.

Who is the course for?

Anyone planning to work with relational databases and who will use
SQL to extract or modify data.

Course Content:

Creating tables and columns
▪ Building tables with CREATE TABLE
▪ Modifying table structure with ALTER TABLE
▪ Adding columns to an existing table
▪ Removing tables with DROP TABLE

Protecting data integrity with constraints
▪ Guaranteeing uniqueness with primary key constraints
▪ Enforcing integrity with foreign key constraints
▪ Imposing business rules with check constraints
Improving performance with indexes
▪ Expediting data retrieval with indexes
▪ Recommending guidelines for index creation

Modifying table contents
▪ Adding table rows with INSERT
▪ Changing row content with UPDATE

Removing rows with DELETE

Applying transactions
▪ Controlling transactions with COMMIT and ROLLBACK
▪ Deploying BEGIN TRANSACTION in SQL Server

Writing Single Table queries
▪ Retrieving data with SELECT
▪ Specifying column expressions
▪ Sorting the result with ORDER BY
▪ Handling NULL values in expressions

Restricting rows with the WHERE filter
▪ Testing for equality or inequality
▪ Applying wildcard characters
▪ Avoiding NULL value pitfalls

Applying the ANSI/ISO standard join syntax
▪ Matching related rows with INNER JOIN
▪ Including nonmatched rows with OUTER JOIN
▪ Creating a Cartesian product with CROSS JOIN

Combining results with set operators
▪ Stacking results with UNION
▪ Identifying matching rows with INTERSECT
▪ Utilizing EXCEPT to find nonmatching rows

Processing data with row functions
▪ Conditional formatting with the CASE expression
▪ Utilizing the CASE expression to simulate IF tests
▪ Dealing with NULL values

Performing analysis with aggregate functions
▪ Summarizing data using SUM, AVG and COUNT
▪ Finding the highest/lowest values with MAX and MIN
▪ Defining the summary level with GROUP BY
▪ Applying filter conditions with HAVING

Trainer Profile

Please contact info@ifsskillnet.ie for further information on training providers.