SQL For The Web

Course CodeBIT201
Fee CodeS3
Duration (approx)100 hours
QualificationStatement of Attainment
  



Many older web sites use Access Databases - SQL is the next generation

If you are serious about websites and data management within computer software systems, you need to know SQL

Why choose this course?
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 is most suitable for programmers and web developers who are looking to expand their skills with SQL server

The Best Teaching Staff
Our tutors include SQL experts from both Australia and the UK; who not only teach, but work as programmers, using SQL on a daily basis.
Save
Save

Lesson Structure

There are 12 lessons in this course:

  1. Introduction to Databases
    • What is a database
    • DBMS
    • The relational model
    • Primary keys
    • Foreign keys
    • Relationships
    • Normalisation
    • Other normal forms
    • De normalisation
  2. Fundamentals of SQL
    • Installing a DBMS
    • SQL
    • The select statement
    • Common errors
    • Identifiers
  3. Building a database with SQL
    • Building a database: RAD tool, CSV file, opening database, commands, etc
    • Data types and MS access
  4. Retrieving, Storing, Updating and Deleting Data
    • Retrieving data
    • Retrieving from tables with relationships
    • Creating column aliasesEliminating duplicate rows withDISTINCT
    • Filtering rows with WHERE
    • Matching patterns with LIKE
    • Escaped and unescaped patterns
    • Range filtering with BETWEEN
    • List filtering with IN
    • Evaluating conditional values with CASE
    • Sorting rows by ORDER BY
    • Storing, updating and deleting dataUpdating rows with UPDATE
    • Deleting rows with DELETE
  5. Advanced SQL database access methods
    • Relational databases
    • Creating outer joins with OUTER JOIN
    • Subqueries
    • Summarising data
    • Grouping rows
    • Using HAVING for filtering rows
    • Set operations
    • Union
    • Intersect
    • Except
    • Handling duplicates
  6. Database Security
    • Security is important
    • Triggers
  7. Using SQL in applications
    • Uusing SQL in an application
    • Using SQL in web sites
    • Using SQL in desk top applications
    • Using SQL in mobile devices
    • Embedded SQL
    • SQL injection
  8. Cursors
    • What are cursors
    • Preventing updates and deletions
    • Scrollable Cursors
  9. Stored procedures
    • Introduction
    • Compound statements
    • Stored functions
    • Stored modules
    • Views
    • Indexes
    • Controlled flow statements
  10. Error Handling
    • Stability
  11. Dynamic SQL
    • Introduction
    • Execution of dynamic SQL
    • Single step execution
    • Two step execution
    • Dynamic cursors
  12. Advice and Tips
    • Common mistakes
    • Assuming the client knows what they need
    • Underestimating project scope
    • Only considering technical factors
    • Not seeking client feedback
    • Skipping beta testing

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.

Aims

  • 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 You Will Do

  • 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.

What are Data bases?

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 Work group 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.

When computers were first used in business applications, database systems were developed to store data electronically. This information was usually stored in a text file where each record had a ‘record number’ that allowed the computer to easily find the information you need.

As computers became more advanced, the demand for more powerful software grew. The old database systems (known as ‘flat file’ databases) became redundant as ‘relational’ databases were developed. Relational databases allowed multiple sets of records (known as tables) to have data that related to each other.

For example, you may have a number of ‘suppliers’ and a number of ‘customers’. In the past, you would need to have two separate databases that would not be able to talk to each other.
With the use of a relational database, you could have two separate tables that have a common ‘category’. You could then link suppliers to customers via this category to match which products suit which customers. If a supplier offers a special, you could easily retrieve a list of customers that match that supplier to let them know of the special.

Whatever the size of the computer that hosts the database and regardless of whether the machine is connected to a network the flow of information between the database and user is the same. The DBMS (Database Software) masks the physical details of the database storage so that the application need only concern itself with the logical characteristics of the data, and not how the data is stored

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 Access
  • Microsoft SQL Server
  • Microsoft MSDE
  • Oracle
  • Sybase SQL Server
  • MySQL
There are many other DBMSs 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).
 

 

WHAT NEXT?
 
ENROL (go to top) or
 
Use our FREE Counselling Service to Connect with a Tutor
 

 



Credentials

ACS is an Organisational Member of the British Institute for Learning and Development
ACS is an Organisational Member of the British Institute for Learning and Development

Member of Study Gold Coast, Education Network
Member of Study Gold Coast, Education Network

ACS Global Partner - Affiliated with colleges in seven countries around the world.
ACS Global Partner - Affiliated with colleges in seven countries around the world.



Need assistance?



Start Now!