Intermediate SQL

Intermediate SQL

Gain confidence using a wide range of advanced SQL techniques. This course will expand your SQL knowledge and provide skills for writing powerful queries that perform complicated searches and sorts of data.
6 weeks / 24 Course Hrs
SHARE
  • Details
  • Syllabus
  • Requirements
  • Instructor
  • Reviews

Details

Expand your knowledge of Structured Query Language (SQL), the industry standard database programming language. In this course, you'll learn techniques that will enable you to write powerful queries that perform complicated searches and sorts on your data.

By the end of this course, you'll be able to use a wide range of advanced SQL techniques with confidence. Your new skills with databases will enhance your competitiveness in the technical fields of software development and database administration.

Enrollment Options:
Instructor-Led
6 weeks Access Course Code: ins
Start Dates*Oct 16 | Nov 13 | Dec 11 | Jan 15
*Choose start date in cart
$115.00

Syllabus

The first lesson will discuss the importance of Structured Query Language (SQL) and why it's used in conjunction with databases. You'll do a quick review of some of the basic concepts of the SQL language, including commonly used keywords, SQL data types, constraints, logical operators, and comparison operators. The lesson will also go over running queries in the SQL Design View and SQL View interfaces of Microsoft Access.

In this lesson, you'll discover how to query multiple tables using subqueries. You'll learn about the different types of subqueries and how subqueries are processed. You'll find out how to create nested subqueries, subqueries linked by comparison operators, and subqueries that are linked through the following powerful keywords IN, NOT IN, EXISTS, ANY, SOME, ALL.

In this lesson, you'll learn how to use unions to create queries that combine records from multiple queries, enabling you to either exclude or include duplicate records. You also will learn how to order the results from a union query, how to create an alias within a union query, and how to use a text string to display a value in a union query.

In this lesson, you'll find out how to use advanced joins to query multiple tables simultaneously. You'll learn to create inner joins and outer joins. You will learn to create joins that contain aggregate functions and joins that are nested. You'll also find out how to create joins using SQL-92 and SQL-89 syntax.

In this lesson, you'll learn how to use advanced update techniques to update data stored in your tables. You'll see how to use the UPDATE and SET keywords in an update statement and how to incorporate a WHERE clause within an update statement. The lesson will go over how to update single fields and multiple fields and how to update multiple records simultaneously, as well as how to update date fields. You'll also learn how to update columns with calculated fields and how to use an update statement to update multiple columns to NULL and how to insert data into NULL columns.

In this lesson, you'll find out how to use advanced insertion techniques to add to your tables. You'll learn how to create conditional and nonconditional insert statements and how to insert records that contain NULL values. You'll also explore how to insert records without the specification of column names and how to insert calculated values into a table. Finally, you'll see how to insert values from functions, how to insert a single record using a conditional insert statement, and how to insert multiple records using a conditional insert statement.

In this lesson, you'll learn advanced filtering techniques that implement the LIKE operator. The lesson will begin with a review of the LIKE operator and will then go into more advanced LIKE operator queries. You'll learn how to use the percent (%) wildcard with the LIKE operator to compare a value to a string expression and to find a range of values. You'll learn how to use the exclamation mark (!) and the percent (%) sign wildcards with the LIKE operator to match character and digit patterns. You'll learn how to use the bracket ([]) wildcard with the LIKE operator to search for a range of characters and to search for a set of characters. You'll also learn how to use the LIKE operator to search for a combination of characters and digits.

In this lesson, you'll see how to create, query, and modify temporary tables. The lesson will begin with a discussion on the importance of temporary tables and when to use them. You'll then explore how to create and access temporary tables. You'll learn how to create a temporary table based on a standard table and how to create a temporary table based on a complex join. You'll see how to query a temporary table and how to find the second highest value in a column of a temporary table. You'll also see how to add a column and a datatype to a temporary table. After that, you'll explore how to add a default value to a temporary table. Finally, the lesson will go over how to remove a column from a temporary table and how to delete a temporary table.

In this lesson, you'll learn how to handle duplicate values stored in a table and how to limit the results of a result set. You'll learn how to implement the DISTINCT keyword against a single field, how to implement the DISTINCT keyword against multiple fields, how to implement the DISTINCT keyword against a subquery, how to implement the DISTINCTROW keyword, how to implement the TOP keyword on a descending order field, how to implement the TOP keyword on an ascending order field, and how to implement the TOP PERCENT keywords.

In this lesson, you'll learn how to use string functions to manipulate strings. You'll learn how to use the LEFT () and RIGHT () functions to return characters of a string combined with concatenation, how to use the StrConv () function to convert values stored in columns, and how to convert string values typed directly into the StrConv () function. You'll also learn how to use the LEN () function to count values stored in a column and how to count values typed directly into the LEN () function.

In this lesson, you'll learn how to use the NOW () function to insert the date and time and how to insert a calculated date and time. You'll find out how to use the YEAR () function in a join query to extract the year from a date. And you'll learn how to use the YEAR (), MONTH (), MONTHNAME (), WEEKDAY (), and WEEKDAYNAME () functions to extract the year, month, name of the month, week, and name of the week from a date. You'll also learn how to use the FORMAT () function in a subquery to format dates.

In this lesson, you'll learn how to create and implement parameter queries. You'll find out how to create a parameter query with one prompt and how to create a parameter query with two prompts. You'll then learn how to create a parameter query that prompts the user for a date and how to create a parameter query that prompts the user for two dates. Finally, the lesson will discuss how to create a parameter query that queries two tables using a join and how to create a parameter query that implements the LIKE operator in a join query.

Requirements

  • Adobe Flash Player. Click here to download the Flash Player.
  • Adobe Acrobat Reader. Click here to download the Acrobat Reader.
  • Email capabilities and access to a personal email account.

Requirements:

Hardware Requirements: 

  • This course must be taken on a PC. It is not suitable for Mac users.

Software Requirements: 

  • Windows XP or later.
  • Browser: The latest version of Google Chrome or Mozilla Firefox are preferred. Microsoft Edge is also compatible.
  • Microsoft Access 2003 or later. Microsoft Access is bundled with Microsoft Office here (not included in enrollment).
  • Depending on your operating system, you may be required to install additional service packs, which are available at the Microsoft Download Center.
  • Adobe Flash Player. Click here to download the Flash Player.
  • Adobe Acrobat Reader. Click here to download the Acrobat Reader.
  • Software must be installed and fully operational before the course begins.

Other:

  • Email capabilities and access to a personal email account.

Prerequisites:

There are no prerequisites to take this course.

Instructional Material Requirements:

The instructional materials required for this course are included in enrollment and will be available online.

Instructor

Cecelia Allison Cecelia Allison

Dr. Cecelia Allison is an experienced software tester and technical specialist with more than 15 years of hands-on experience using and teaching SQL. She is also a technical writer, a published author, and a university professor. She holds a Bachelor of Science in finance, a Master of Science in information systems and a Doctor of Business Administration in management information systems.

Reviews

Great course by Cecilia. It was well written, easy to read and understand. The assignments enforced what was presented in the lesson.

Cecelia, I've learned a lot from both of your SQL courses (Introduction & Intermediate). I appreciate the opportunity to study online (good use of time since I work full-time with a daily commute). I've enjoyed these classes, thank you.

Cecelia Allison is by far the best instructor that I've had. She teaches in a way that makes it so easy to understand. I have read books by others who taught SQL and there is no comparison. I strongly recommend her course to anyone wanting to learn SQL.

I enjoyed the class. Great writing! Things were explained very well. For me, some things were a review and I was challenged by several new things, so it was the perfect course for me. I feel the content was very practical and I can put it to use immediately in my job. Thank you!

Cecelia is so organized and she presents the information in understandable components. Her instructions are outstanding! This is the second course I’ve taken by Cecelia and I would highly recommend her courses.

This is my second course from this particular instructor. She definitely is an expert in her field and provided a learning environment that was conducive to learning and understanding. I appreciate that the information was understandable as I had no prior experience with SQL before her two classes. Thank you again.

I really like this instructor's teaching style. The lessons are the right length and always presented in a way which makes it easy to retain the information. Her teaching by example helps me apply what I learn and better understand how to approach similar problems.

I had last used MS Access in 2002. My new work assignment required the use of MSA 2010. The use of the MSA tool plus the SQL technique training helped me greatly to "get up to speed" on both SQL use with MSA and the new environment that MSA 2010 provides. The thorough explanations and comparisons in SQL, as I had been using SQL/89, and the options made available proved valuable.

I took the Introduction course with Cecelia as well, and I enjoyed both courses. I really liked how the Intermediate course had you try what she was teaching during the lesson and prior to trying it for the first time in an assignment. It helped me to learn the material in a more effective way. I also found the tips to be especially helpful, because she explained the reasoning behind doing things one way vs another. It was like she was reading my mind! I would definitely take another course taught by Cecelia and would recommend her classes.