SQL DATABASES BIT201

Learn SQL(Structured Query Language) for the Web

 

This course provides a comprehensive introduction to SQL. By the end of the course, the student should be able to easily understand all the major aspects of SQL.

 

Is this course right for me?

This course will suit beginners who are new to databases, experienced database users may also find this course of benefit to update or expand their current knowledge using databases in SQL.

 

Learn SQL(Structured Query Language) for the Web

 

12 lessons filled with easy to understand instructions

Screenshots of working examples

100 hours total study with up to 12 months to complete

No classes, submission dates or schedules! 

 

This course provides a comprehensive introduction to SQL. By the end of the course, the student should be able to easily understand all the major aspects of SQL.

This course will suit beginners who are new to databases, experienced database users may also find this course of benefit to update or expand their current knowledge using databases in SQL.

 

WHAT IS A DATABASE?

A database is simply a place where you can store data. The most basic form of a database is a card file system, where information of different contacts or clients are written on cards that are then stored in alphabetical order. Each card is called a ‘record’ and each record contains ‘fields’ such as ‘name’, ‘address’ and ‘phone number’. Databases come in all sizes, from simple collections of a few records to large systems holding millions of records. A Personal Database is designed for use by a single person on a single computer. Such a database usually has a simple structure and a relatively small size, whereas a Departmental or Workgroup database is used by members of a single department or work groups within an organisation. This type of database is generally larger than a personal database and far more complex in its structure, by having the functionality to enable multiple users to access the same database simultaneously. Bigger still, are Enterprise databases, which are used to model critical information flow within entire large organisations.

 

COURSE STRUCTURE

There are 12 lessons as follows:

  1. Introduction to Databases -What is a database, DBMS, The relational model, Primary keys, Foreign keys, Relationships, Normalisation, Denormalization
  2. Fundamentals of SQL -Installing DBMS, SQL, The select statement, Common errors, Identifiers
  3. Building a database with SQL - Understand how to build and maintain a database with SQL.
  4. Storing & Retrieving Data -Reviewing data, Retrieving data from tables with relationships, Creating column aliases, Eliminating duplicate rows, Filtering rows, Matching patterns, Escaped and unescaped patterns, Range filtering, List filtering, Evaluating conditional values, Sorting rows, Sorting, updating and deleting data,
  5. Advanced SQL database access methods -Relational databases, Creating outer joins, Sub queries, Summarising data, Grouping rows, Set operations
  6. Database Security -Privilege hierarchy, Triggers, Security etc
  7. Using SQL in applications -Using SQL in an application, Embedded SQL, Object oriented RAD tool, etc
  8. Cursors -What are cursors, scope of cursors, benefits of a cursor, scrollable cursor, etc.
  9. Stored procedures -Stored procedures, Compound statements, Stored functions, Stored modules, Views, Indexes, Controlled flow statements
  10. Error Handling -Stability, Constraint violation, Exception, etc
  11. Dynamic SQL -What is dynamic SQL, Executing dynamic SQL, Dynamic cursors.
  12. Advice & Tips -Common Mistakes: Assuming client knows their needs, Underestimating project scope, Only considering technical factors, Not asking client for feedback, etc.

Each lesson culminates in an assignment which is submitted to the school, marked by the school's tutors and returned to you with any relevant suggestions, comments, and if necessary, extra reading.

 

Duration: 100 hours

 

Aims

On successful completion of the course you should be able to do the following:

  • Understand the concept of relational databases.
  • Understand the fundamentals of SQL.
  • Build and maintain a database with SQL.
  • Define how to store data in a database using SQL.
  • Understand advanced and more efficient ways of working with databases in SQL.
  • Keep databases secure with SQL.
  • Understand how to use SQL in real world applications.
  • Define how to use cursors to work with data in a database.
  • Understand how to re-use common code and develop efficient database driven applications with the use of stored procedures.
  • Define the benefits of error handling and how to implement it.
  • Define how dynamic SQL works in applications.

 

WHAT THE COURSE COVERS

  • Explain the difference between a flat file and relational database.
  • Define DBMS.
  • Write an SQL statement that will perform a set task.
  • Explain the difference between an Integer and Numeric data type.
  • Learn what the four DDL commands are.
  • Define set function.
  • Write the SQL code needed to create a database for a car sales yard.
  • What is the importance of a primary key.
  • What is a RAD tool?
  • Gain an understanding of referential integrity rules.
  • Give an example of a logical connective and how it would be used in an SQL select statement.
  • Learn the appropriate use of the keyword select.
  • Learn about JOINS.
  • Explain the access privilege hierarchy.
  • Learn how to declare ROLE.
  • Write an SQL statement for takes away or grants rights to a member of a particular list.
  • Explain Embedded SQL.
  • What is 3 tier web database architecture?
  • Explain the benefits vs disadvantages of using a web based database application rather than a client based application.
  • Develop a list of code needed to send a personalised email out to each customer.
  • Explain the benefits of using cursors.
  • What clause do you need to use to prevent updates and deletions within the scope of a cursor?
  • Develop a stored procedure for creating an employees table.
  • Explain the difference between stored procedures, stored functions and stored modules.
  • Develop error handling.
  • Explain what an exception is and how to handle one with SQL.
    • Describe the differences between dynamic and static SQL.

     

    Extract from Course Notes:

    DBMS

    A DBMS (or Database Management System) is a software program that acts as a 'go-between' (interface) for the database information. When an application requires data, it sends its' request to the DBMS which searches the database records for the relevant data and then returns the result back to the application.

    You can think of a DBMS as a librarian. You go to the library and ask them "have you got any books on car engine repairs". The librarian then goes and looks through the shelves, finds all the relevant books and brings them back to you. Without the librarian, you would need to search through all the shelves yourself, which is slow and inefficient.

    DBMSs make life easy and reduce the time it takes for an application to process data.

    It is important to understand the difference between a Database and a DBMS. Many people confuse the two or do not realise there is a difference which can cause confusion.

    DBMSs come in all shapes and sizes with different features and uses. Many have features that are unique and almost all DBMSs are incompatible with each other (although some have partial compatibility).

    Some examples of popular DBMSs are:

    • Microsoft SQL Server
    • PostgreSQL
    • Oracle
    • Sybase SQL Server
    • MySQL

    There are many others also around too. Some are general ones used for a variety of applications and some are specifically designed for certain applications (such as banking and finance).

    This lesson covers the basics of relational databases, which is relevant for most new DBMSs. As SQL is one of the most popular database languages, it is relevant to many DBMSs but this course will focus on SQL use in Microsoft Access.

    The reason for this is Microsoft Access has an easy to use and understand 'front end' and skills developed can easily be carried across to Microsoft SQL Server or MSDE which are more heavy duty DBMS.

     

    The Relational Model

    To become proficient with SQL, the User/Programmer should be familiar with the Relational Model i.e. the design that all modern relational databases are built upon. The foundation of the relational model, Set Theory, describes how to perform common algebraic operations (such as Unions and Intersections) on database tables in much the same way as they're performed on mathematical sets. Tables are similar to sets: they're collections of distinct elements having common properties. The structure of the relational database designed using a relational model, is effectively the combining of two dimensional tables, which contain columns and rows of data, which relate to each other by way of a common data structure (Primary and Foreign keys). The database serves the purpose of combining data sets in order to summarise and manipulate the stored information held within the tables.

    ACS is a Vocational School and Technical School; offering Online Training Courses in Computing. Learn about IT, by External Studies

     

    Valuable knowledge that will benefit you into the future

    SQL spans many different software platforms and has a broad range of uses that can help you easily gather the information you need by quickly searching and sorting through large amounts of data.

    Learn how to program one of the most popular database languages there is today. This course will give you a foundation that you will be able to expand on and give you a good understanding of how databases work.

    Database job growth is estimated  to grow 11 percent between 2014 and 2024, One of the fastest growing careers in the world!!

    You can take these skills anywhere in the world and find work as SQL is used by every major country across many different industries. 

     

    Professions that could be held:

    • Database Administrator(DBA)
    • Database Developer
    • Database support and maintenance
    • Database security
    • Data miner
    • Data reporting and business integration

    Combining SQL with other IT knowledge will broaden your ability and skills to do even more, things such as:

    • Computer programming
    • Computer security
    • Web development
    • Networking
    • Business Analysis
    • Marketing

     

    Study online at your own pace

    Using our modern online portal for study is a simple and effective way of learning. Once enrolled, you will receive your online account to our login.training system that will give you all the lessons assignments and self-assessment tests so you can start studying straight away.

     

    Get started today and make a difference
    Simply click on the ENROL NOW icon at the top of this page