Lesson Structure
There are twelve lessons in the course.
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 aliases
- Eliminating duplicate rows with DISTINCT
- 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 data
- Updating 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
- Using 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
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.
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 employee’s 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.
Introducing 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. 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.
Database Management System (DBMS)
A Database Management System (DBMS) 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).
What Are the Advantages of Studying with ACS Distance Education?
- You can start at any time to suit you.
- The courses are self-paced.
- You can study when and where suits you. They are flexible to fit in around you and your lifestyle.
- Keep up to date on your learning.
- Update your CV.
- It is not just a course, it is a package of learning that includes – course notes, tutor feedback, self-assessment tests, research tasks and assignments.
- Our tutors are all experts in their field, with years of experience in their field.
- They are also keen and enthusiastic about their subject and enjoy working with students to improve their knowledge and skills.
- Courses are also updated regularly to meet the demands of the changing needs for knowledge and to keep our courses up to date.
What Next?
SQL databases have been around for a long time and don't show any signs of going anywhere. Having this skill may be one of the best things you can do in order to change your career path. There are many job opportunities all over the world that require people with database skills. Even if you have a job doing something else, the things you will learn in this course can benefit you across a multitude of industries and jobs.
When you talk IT, one of the most common things that a business will have is a database of some sort. SQL is the world’s leading database language and learning this will give you the skills to branch out into many different areas. Not all databases are built the same.
A skilful knowledge of SQL is useful for anyone wanting to –
- Improve and develop their IT career.
- Expand their job and business opportunities.
- Develop or increase their knowledge of SQL and databases.
Remember skills in SQL are in high demand. So why wait?
You can enrol today by clicking the “Enrol Now” button above.
Or
Click here to ask a Question
Or Request a Prospectus Here.