Structured Query Language: (SQL) Level 1

Gain hands-on practice using a standard structured query language (SQL).

course image

Course details

ceus icon CEUs 2.1

schedule icon Schedule Evenings, Days

location icon Location Online

cost icon Cost

Registration details

Structured Query Language: (SQL) Level 1

Gain hands-on practice using a standard structured query language (SQL).

What you’ll be able to do — competencies

  • Use the WHERE clause in a SELECT statement to retrieve filtered data from a relational database 
  • Apply aggregate, date and string functions to data retrieved from a relational database 
  • Use the ORDER BY, GROUP BY and HAVING clauses to retrieve data from a relational database 
  • Use a self-join operation to retrieve data from a single data source or a variety of operators, or subqueries to retrieve data from multiple data sources 
  • Use the INSERT, UPDATE, DELETE and TRUNCATE commands to manipulate data in a table 
  • Revise a relational database by creating, altering and dropping tables and views 

Course description

expand drawer icon collaspe drawer icon

Create queries using a standard structured query language (SQL) that is applicable to almost any relational database. Gain hands-on practice creating queries to retrieve data in various forms, including filtered, calculated, grouped and aggregated, sorted and consolidated from multiple data sources. You’ll also practice writing queries to manipulate data in a table and create, alter and drop tables and views. 

Prerequisites

expand drawer icon collaspe drawer icon
  • Basic Relational Database Essentials or familiarity with relational database products 

Syllabus

expand drawer icon collaspe drawer icon

Objective 1: Use the WHERE clause in a SELECT statement to retrieve filtered data from a relational database. 

  • Describe the role and classifications of Structured Query Language (SQL) commands 
  • Describe how to set up and use the Query Editor window in SQL Server Management Studio (SSMS) 
  • Use the SELECT and FROM clauses in a SELECT statement, without the WHERE clause to retrieve data 
  • Use a WHERE clause that applies relational operators and other operators, such as IN, BETWEEN/AND, and LIKE, to retrieve filtered data 
  • Use a WHERE clause in conjunction with the IS NULL operator to retrieve data with NULL values in a specific field 
  • Use a WHERE clause that applies compounded conditions connected with the AND, OR and NOT logical operators to retrieve filtered data 

Objective 1 Assignments 

  • Read: 
  • Chapter 1: pages 13 (Theoretical background, SQL) 
  • Chapter 2: pages 2732 (SELECT statement, FROM and WHERE clauses), 3643 (SELECT and ORDER BY clauses), 4952 (Predicates and operators), 7173 (LIKE predicate) 
  • Complete labs in class. 
  • Complete Skill Check (Required) 

Objective 2: Apply aggregate, date and string functions to data retrieved from a relational database 

  • Use the DATEPART, DATEADD and DATEDIFF date functions to retrieve calculated and filtered data 
  • Use the SUM, MAX, MIN, COUNT and AVG aggregate functions to retrieve aggregated data 
  • Manipulate string data types using string functions 
  • Format dates using the CAST and CONVERT functions 

Objective 2 Assignments 

  • Read chapter 2: pages 8083 (Date and time functions, CAST and CONVERT functions), 8587 (DATEADD, DATEDIFF and DATEPART functions), 5458 (NULLs), 3235 (GROUP BY clause), 6369 (String concatenation, SUBSTRING, LEFT, RIGHT, LEN, REPLACE, UPPER, LOWER, RTIM and LTRIM functions) 
  • Complete labs in class 
  • Complete skill check B (Required) 

Objective 3: Use the ORDER BY, GROUP BY and HAVING clauses to retrieve data from a relational database 

  • Use the ORDER BY clause to sort data 
  • Use the GROUP BY clause to retrieve aggregated and non-aggregated data 
  • Use the GROUP BY clause in conjunction with the ROLLUP and CUBE options to retrieve aggregated and non-aggregated data 
  • Use the HAVING clause in conjunction with aggregate functions to filter data 

Objective 3 Assignments 

  • Read chapter 2: pages 3236 (GROUP BY and HAVING clauses), 4143 (ORDER BY clause) 
  • Chapter 7: pages 234237 (Grouping sets, CUBE and ROLLUP subclauses) 
  • Complete labs in class. 
  • Complete Skill Check (Required) 

Objective 4: Use a self-join operation to retrieve data from a single data source or a variety of operators, or subqueries to retrieve data from multiple data sources 

  • Use the JOIN (INNER, OUTER, CROSS, FULL) operators to retrieve data from two different tables 
  • Use a self-join operation to retrieve data from a single table related to itself 
  • Use multiple joins to retrieve data from tables not directly related to each other 
  • Use the UNION and UNION ALL operators to combine data from multiple tables 
  • Use a subquery in the SELECT, FROM and WHERE clauses of a SELECT statement to retrieve data 
  • Use a subquery in conjunction with the ANY and ALL comparison operators in a WHERE clause to retrieve filtered data 
  • Use a correlated subquery and the EXISTS function in a WHERE clause to retrieve filtered data 

Objective 4 Assignments 

  • Read chapters 3, 4 
  • Chapter 6: pages 193196 (Set operators, UNION operator) 
  • Complete labs in class 
  • Complete Skill Check D (Required) 

Objective 5: Use the INSERT, UPDATE, DELETE and TRUNCATE commands to manipulate data in a table 

  • Use the INSERT command to insert new rows into an existing table 
  • Use the UPDATE command to update data in existing rows 
  • Use the INSERT command to insert new rows into an existing table 
  • Use the UPDATE command to update data in existing rows 

Objective 5 Assignments 

  • Read chapter 8: pages 249251 (INSERT VALUES and INSERT SELECT statements), 262265 (Deleting data, DELETE and TRUNCATE statements), 266268 (Updating data, UPDATE statement) 
  • Complete labs in class 
  • Complete Skill Check (Required) 

Objective 6: Revise a relational database by creating, altering and dropping tables and views 

  • Use the SELECT INTO command to create a new table from an existing table. 
  • Use the CREATE TABLE command in conjunction with field and table level constraints to create a table with enhanced data integrity 
  • Use the ALTER TABLE command in conjunction with the ADD command to add a column to an existing table 
  • Use the ALTER TABLE command in conjunction with the REFERENCES key word to set a relationship between two tables and apply the referential integrity constraint 
  • Use the DROP TABLE command to drop a table 
  • Create a view using the CREATE VIEW command in conjunction with the ENCRYPTION, SCHEMABINDING and TOP key words 
  • Alter a view using the ALTER VIEW command 
  • Drop a view using the DROP VIEW command 

Objective 6 Assignments 

  • Read chapter 8: pages 252253 (SELECT INTO statement) 
  • Chapter 1: pages 2025 
  • Chapter 5: pages 171177 (read up to CHECK OPTION) 
  • Complete labs in class 
  • Complete Skill Check F 
  • Complete class evaluations in class 

Next available start dates

Online — Evenings

Registration closed expand drawer icon collaspe drawer icon
Structured Query Language: (SQL) Level 1
hours icon

Total classroom hours

21 hours

schedule icon

Schedule

Online

Evenings, 6:00 PM - 9:00 PM, 7 T Th - 9/22/2020 - 10/13/2020

cost icon

Total cost

$749

instructor icon

Instructor: Victor Leal

notes icon

Notes

Online courses require an internet connection and the ability to interact both through audio and video using either a web cam/microphone, speakers/headset using a computer or via a mobile phone connection. Any costs associated with connectivity or equipment are the responsibility of the student.

Interested in custom learning solutions for your team?

Tombolo Institute Corporate Solutions offers tailor-made professional development programs to meet your organization’s goals.

Get started