SQL For The Web

Many older web sites use Access Databases - SQL is the next generation -If you are serious about web sites and data management withing 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

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 Workgroup database is used by members of a single department or workgroups 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).