Home / Non-Accredited / Microsoft Office & Computer Skills / Getting Started with Microsoft SQL Server: SQL Querying (2 days)
Quick Look Course Summary:Getting Started with Microsoft SQL Server: SQL Querying (2 days)
-
Length: 2 day(s)
-
Price (at your venue): 1 Person R 14,642 EX VAT 3 Person R 9,476 EX VAT 10 Person R 6,474 EX VAT
-
Certification Type:Non-Accredited
-
Locations & Venues: Off-site or in-house. We train in all major city centres throughout South Africa.
Get Free & personalised
Training Advice
Getting Started with Microsoft SQL Server: SQL Querying (2 days)
Get the data you need straight from SQL Server – accurately, quickly and without waiting for someone else to run the report.
Much of a company's important data – sales, stock, customers, payroll, transactions – sits in Microsoft SQL Server databases, but many of the people who need it only see what a fixed report or an Excel export gives them. This hands-on SQL Server course teaches delegates to ask the database directly using T-SQL, the query language SQL Server uses, so they can answer their own questions and check the figures behind the reports.
Day one covers the fundamentals of querying: connecting with SQL Server Management Studio, writing and saving SELECT queries, filtering with conditions, patterns and NULLs, date, text and aggregate functions, sorting, ranking and grouping, and combining tables with joins and set operators. Day two moves to advanced querying and changing data: subqueries, inserting, updating and deleting rows, creating tables with constraints, views, indexes and transactions.
Delegates practise on a sample database throughout, and each topic ends with a short exercise based on a real reporting request. No previous SQL experience is needed; comfort with Excel tables and basic logic helps.
Why this course
For your organisation
- Staff who can answer data questions without queuing for IT
- Fewer errors from re-keying and reworking exported data
- Analysts and report writers who understand the data behind the reports
- A safer approach to changing data, using tested queries and transactions
For your delegates
- Useful queries written from the first morning
- A clear method for joining tables and summarising data
- Saved queries and exercise scripts to reuse at work
- Confidence to read and adapt queries written by others
What delegates will be able to do
- Connect to a SQL Server database and write, save and run SELECT queries
- Filter data with conditions, ranges, text patterns and NULL checks
- Use date, text and aggregate functions to calculate and tidy results
- Sort, rank, group and summarise data, including cross-tab results with PIVOT
- Combine data from several tables with joins, UNION, INTERSECT and EXCEPT
- Write subqueries, including correlated subqueries and EXISTS tests
- Insert, update and delete rows safely inside transactions
- Create and change tables with constraints, and build views and indexes
- Export query results for use in Excel and other systems
Who should attend
Business and data analysts, report writers, finance, sales and operations staff who work with system data, Excel power users who want to go to the source, and junior developers or IT support staff who are new to SQL. No SQL experience is needed. This is a querying course: installing, securing and maintaining SQL Server is covered in BOTI's five-day SQL Server administration course.
Course outline
Day 1 – SQL querying fundamentals
Getting started with SQL Server and T-SQL
- What a relational database is: tables, rows, columns and keys
- Connecting with SQL Server Management Studio and finding your way around
- Your first SELECT statement and choosing columns
- Saving, opening, changing and re-running query scripts
- Column aliases, DISTINCT and comments for readable queries
Filtering data
- WHERE with one or several conditions, using AND, OR and NOT
- BETWEEN and IN for ranges and lists
- Finding missing values with IS NULL and IS NOT NULL
- Matching text with LIKE and wildcards
Functions for dates, text and totals
- Date functions: GETDATE, DATEADD, DATEDIFF and date parts
- Text functions: LEFT, RIGHT, LEN, REPLACE, TRIM and CONCAT
- Converting data types with CAST and CONVERT
- Aggregates: COUNT, SUM, AVG, MIN and MAX
- CASE expressions for categories and conditional results
Sorting, ranking and grouping
- ORDER BY, TOP and OFFSET-FETCH
- Ranking with ROW_NUMBER, RANK and DENSE_RANK
- GROUP BY, and filtering groups with HAVING
- Turning rows into columns with PIVOT, and back with UNPIVOT
Querying more than one table
- Inner joins on key columns
- Left, right and full outer joins for unmatched records
- Self joins and joining three or more tables
- Stacking and comparing results with UNION, INTERSECT and EXCEPT
Exporting query results
- Copying results to Excel with column headers
- Saving results as text, CSV and XML files
- Day one practical: answering a set of reporting questions
Day 2 – Advanced querying and changing data
Subqueries
- Subqueries in WHERE with IN and comparison operators
- Testing for related records with EXISTS and NOT EXISTS
- Correlated subqueries that run once per row
- Subqueries in HAVING, and subqueries several levels deep
- When a join or a common table expression reads better
Changing table data
- Adding rows with INSERT, including INSERT from a SELECT
- Changing values with UPDATE and removing rows with DELETE
- Testing the WHERE clause before changing anything
- Common mistakes that change the wrong rows, and how to avoid them
Creating and changing tables
- CREATE TABLE with suitable data types
- Primary key, foreign key, unique, check and default constraints
- ALTER TABLE to add, change and remove columns
- Dropping tables safely
Views
- Creating a view to simplify a complex query
- Using views to show users only the columns they need
- Changing data through a view, and its limits
- Altering and dropping views
Indexes and transactions
- How indexes speed up queries, and what they cost
- Creating and dropping clustered and non-clustered indexes
- BEGIN TRANSACTION, COMMIT and ROLLBACK
- Using a transaction to protect a set of related changes
Final practical and close
- Building a small reporting solution: tables, data, a view and queries
- Review of both days and a query reference sheet
- Next steps: Power BI reporting or SQL Server administration
How it is delivered
Two days, 08:30-16:00. In person at your offices anywhere in South Africa, at a BOTI venue, or live online. One delegate or a group of up to 20. Each delegate works at a computer with SQL Server (the free Express or Developer edition is fine) and SQL Server Management Studio; laptops with the software can be supplied on request. A student manual per delegate, sample databases and exercise scripts are included. We deliver it at your offices in Johannesburg, Pretoria, Durban, Cape Town and anywhere else in South Africa, at a BOTI venue in Johannesburg, or live online for teams in different places.
What our clients say
“The facilitator effectively encouraged participation and used practical examples, discussions and interactive activities to ensure that the concepts were understood and could be applied in the workplace.”
“Business Optimization Training Institute facilitated Microsoft Excel Training for our company. The course was efficiently scheduled and planned around our timetable. We were impressed by the quality of training.”
“The training was very informative and practical, leaving our employees feeling confident in their ability to leverage Excel more effectively.”
Frequently asked questions
Can you run this course at our offices?
Yes. Most groups are trained at the client's premises anywhere in South Africa; we can also host it at a BOTI venue or deliver it live online.
How many people can attend?
From one delegate to groups of 20, each at their own computer. The price per delegate drops as the group grows – see the price table, or ask us for a quote.
Is it accredited?
No – it is a non-accredited short course and delegates receive a BOTI certificate of attendance.
Do we need to install SQL Server?
Each delegate needs SQL Server and SQL Server Management Studio, both available free from Microsoft. We send setup instructions beforehand, or supply laptops that are ready to use.
Is this course for database administrators?
No. It teaches querying and working with data. Installing, securing, backing up and tuning SQL Server is covered in our five-day SQL Server database administration course.
Can the exercises use our own data?
Yes, with care. The facilitator can work with an anonymised extract or a training copy of your database – never a live production system.
Related courses
Get your price – the more delegates, the less each pays
Change the options to see your price per person, then email yourself the quote and course outline.
Contact us now – we reply within 15 minutes during office hours
Monday to Friday, 08:00–17:00. After hours, we reply on the next working day – or call 011 882 8853.