SQL for the Web BIT201

Study SQL

  • Communicate with Relational Databases
  • SQL (structured query language) is the most common language used for communicating
    with relational databases.

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

Course Structure and Content

There are 12 lessons in this course:

  1. Introduction to Databases
  2. Fundamentals of SQL
  3. Building a database with SQL
  4. Storing & Retrieving Data
  5. Advanced SQL database access methods
  6. Database Security
  7. Using SQL in applications
  8. Cursors
  9. Stored procedures
  10. Error Handling
  11. Dynamic SQL
  12. Advice & Tips

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.

The aims covered include:
  • 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 is SQL?

SQL (structured query language) is the most common language used for communicating with relational databases.

Traditionally, programming languages such as Basic, C# and C++ are known as ‘procedural’ languages. This means that in order to do something, a procedure is written telling the computer what to do.

For example, to get the computer to draw a box, you would have a procedure similar to the following:

  1. Move the pointer to the top left of the screen
  2. Draw a line horizontally until it reaches the edge of the screen
  3. Draw a line down until it reaches the bottom of the screen
  4. Draw a line to the left until it reaches the left hand side of the screen
  5. Draw a line up until it meets the top left corner of the screen

 

SQL is a ‘non-procedural’ language. Instead, you tell SQL what you want and it communicates with the DBMS to retrieve your request without you having to tell SQL exactly how to do it.

For example, to retrieve all records from a database where all people lived in Australia, you would use the following SQL command:

Select * from records where location=’Australia’

The DBMS then understands what SQL is asking and will go and retrieve the data and send it back to SQL.

SQL commands are performed in one of two ways:

1) Via a console

You can execute SQL commands via a console that is connected to a DBMS. This may be the SQL query builder in Microsoft Access, a web page connected to a database of some sort, or either via SQL Server Express, Enterprise Manager & Query Analyser for SQL Server. You would simply type in your request in SQL and after the task has been completed, you will see the result. This form of using SQL is generally only used by professional database administrators due to the large margin for error and lack of database security (someone could easily wipe out a database by typing the wrong command).

 

2) Via an application

This is the most common form of SQL programming. An application acts as a ‘front end’ for the database, allowing someone with basic computer knowledge to use a database. For example, a button that says ‘add record’ would appear on a page with blank fields for ‘name’ and ‘address’. The operator would enter the name and address then click the button. This button would then fire off an SQL command, telling the DBMS to add a new record to the database with the data the operator typed in.

 

WHAT IS COVERED IN EACH LESSON?

  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
   

Enrol Now!

Fee Information (S3)
Prices in Australian Dollars

PlanAust. PriceOverseas Price
A 1 x $869.00  1 x $790.00
B 2 x $473.00  2 x $430.00

Note: Australian prices include GST. 
More information about
Fees & Payment Plans.

Enrol Now 5% discount!
Select a payment plan:

Courses can be started anytime
from anywhere in the world!

All orders processed in Australian dollars.