Writing SQL training course (2 days)
You may have a fantastic database, but unless you can extract useful information from it, it may as well not exist! This two-day course shows you how to mine your database, from getting a mailing list of people meeting certain criteria through to compiling summary information for the board. The course will use either SQL Server 2005, 2008 R2 or 2012 on the course (the differences between the versions for the purposes of writing SQL are trivial).
Writing SQL training - Course Contents
The contents of our Writing SQL course are as follows (any items marked with a * will be covered if time allows):
Using SQL Server
- Using Object Explorer
- Hiding system objects
Creating databases - overview
- Designing databases
- Primary keys
- Indexing fields
Working with tables
- Creating tables
- Allowing nulls or not
- Identity fields
Database diagrams
- Creating database diagrams
- Joining tables with relationships
Simple Queries
- Creating queries
- Comments
- Indentation and case
Setting criteria using WHERE
- Wildcards (% and _)
- Setting criteria for dates
- Coping with nulls
Outputting to Excel
- Results, text and grid options
- Copying and pasting column headings
- Exporting using SQL
- Exporting using the menu
Calculations
- Calculated columns
- Using aliases
- String functions and concatenation
Calculations using dates
- GetDate
- Converting dates
- Useful date functions
|
Basic joins
- Inner joins
- Table aliases
- Left and right outer joins
More exotic joins
- Cross joins
- Full outer joins
- Self joins
Aggregation and grouping
- SUM, AVG, MIN, MAX
- Various ways to count
- Grouping by fields
- Using HAVING to filter results
More complicated grouping (*)
- Using COMPUTE
- Using ALL when grouping
- Using ROLLUP
- Using CUBE
Views
- The view designer
- Limitations of the designer
- Scripting views
Scalar functions
- Types of function
- Creating functions
- Passing arguments
Overview of stored procedures (*)
- Passing parameters
- Basic variables
- Creating stored procedures
Subqueries (*)
- The concept of a subquery
- Using ALL, ANY and IN
- Correlated subqueries
- Using EXISTS
|
Click on any link above to show free exercises for the topic in question. You will get more out of the course if you are already familiar with at least one relational database, whether built in Access or SQL Server. There will be a maximum of 6 people on each course, and each person will have their own computer.
