Course Objectives:
In this course, you will compose SQL queries to retrieve desired information from a database:
- Connect to the SQL Server database and execute a simple query.
- Include a search condition in a simple query.
- Use various functions to perform calculations on data.
- Organize the data obtained from a query before it is displayed on- screen.
- Retrieve data from multiple tables.
- Export the results of a query.
Module 1: SQL - An Overview
- Creating Database (Brief Explanation 10-15 minutes)
- Relational Databases
- Using SQL
- Using Object Explorer
- Hiding system objects
Module 2: Queries
- Writing queries
- Indentation and case
- Setting criteria using WHERE
- Wildcards- ľo get íesults by matching with ceítain keywoíd / patteín, foí e.g. LIKE syntax
- Setting criteria for dates- Setting ceítain date foímats like "2021-06-01" oí "1/6/2021" oí "1 June 2021", etc.
- Dealing with nulls
Module 3: Exporting
- Results, text and grid options
- Copying and pasting column headings
- Exporting using SQL & the menu
Module 4: Calculations
- Calculated columns
- Aliases
- String functions and concatenation
- Using GetDate - ľo get cuííent database date time, foí e.g. cuííent datetime in the foím of "yyyy-MM-dd HH:mm:ss"
- Converting dates - Foímatting dates
- Useful date functions
Module 5: Aggregation and grouping
- SUM, AVG, MIN, MAX
- Counts
- Grouping by fields
- Using HAVING to filter results
Module 6: Subqueries & Joins
- Inner joins
- Table aliases
- Left and right outer joins
- Cross joins
- Full outer joins
- Self joins
- Union and Union All
- Using Subqueries
- Subquery Best Practices and Considerations
Module 7: SQL Best Pactices and Optimizations
- What to do and not to do when writing SQL queries?
- Refrain using ORDER BY
- Avoid using SELECT
- Using CTEs (Common Table Expressions)
- Practice Labs
Module 8:
- RANK()
- Cumulative sums - ľo calculate íunning total, we do use this foí analysis.
- Calculating delta, i.e. use of LAG() function
- Retrieving data for reports based on multiple criteria / conditions (massive use of case whens)
- Extract data from column with JSON values
- Add subtotals to summaries, ROLLUP() function End of the session
Programme scope, delivery arrangements and suitability are confirmed during enquiry.