SQL Querying: Advanced
In today’s competitive environment, information is one of the most important factors in determining the success of an organization. If you are able to manage and retrieve information efficiently, you can streamline the organization’s processes and give it a competitive edge. Some data analysis tasks may require that you query multiple tables simultaneously or create complex views of data.
Training at a glance
Level
Intermediate
Duration
1 Day
Experience
1 year: IT business-level professionals
Average Salary
$92,298
Labs
Yes
Level
Intermediate
Duration
1 Day
Experience
1 year: IT business-level professionals
Average Salary
$92,298
Labs
Yes
Training Details
In the course SQL Querying: Fundamentals, you learned the basics of SQL querying. In this course, you will build on this foundation, learning advanced querying techniques, such as writing nested queries, using JOINs to combine data from multiple tables, summarizing query results, using advanced filtering, and using views to simplify repetitive querying tasks. You will also learn how to perform common database administration tasks such as duplicating tables, modifying table structures, inserting, updating, or deleting data, and indexing tables to optimize query performance. You will also learn how to work with multiple tasks as a transaction.
In this course, you will work with advanced queries to manipulate and index tables. You will also create transactions so that you can choose to save or cancel the data entry process. You will:
- Use nested queries to generate query output.
- Manipulate table data by inserting and updating records in a table and deleting records from a table.
- Manipulate table structure.
- Create views, manipulate data through views, modify the view structure, and drop views.
- Create indexes on table columns and drop inefficient indexes.
Lesson 1: Using Nested Queries
- Topic A: Search Based on Values from a Subquery
- Topic B: Compare Values from a Subquery
- Topic C: Search Based on the Existence of Records
- Topic D: Generate Output Using Correlated Subqueries
- Topic E: Filter Grouped Data Within Subqueries
- Topic F: Perform Multiple-Level Subqueries
Lesson 2: Manipulating Table Data
- Topic A: Insert Rows
- Topic B: Modify and Delete Data
Lesson 3: Manipulating Table Structure
- Topic A: Create a Table
- Topic B: Create a Table with Constraints
- Topic C: Modify a Table’s Structure
- Topic D: Delete Tables
Lesson 4: Working with Views
- Topic A: Create a View
- Topic B: Manipulate Data in Views
- Topic C: Modify and Delete Views
Lesson 5: Indexing Data
- Topic A: Create Indexes
- Topic B: Drop Indexes
Lesson 6: Managing Transactions
- Topic A: Define Transactions
- Topic B: Commit Transactions
Students should have basic computer skills and SQL skills and be familiar with concepts related to database structure and terminology.
- Business Analysts
- Data Analysts
- Developers
- Those needing to know how to query in a SQL database.
To ensure your success, we recommend you first take the following Logical Operations courses, or have equivalent skills and knowledge:
SQL Querying: Fundamentals
Course-specific Technical Requirements
Hardware:
Each computer will need the following minimum hardware configurations:
- 2 GHz or faster 64-bit (x64) processor
- 4 gigabytes (GB) RAM
- 40 GB available hard disk space
- CD-ROM drive (if installing any software from a CD-ROM)
- Keyboard and mouse (or other pointing device)
- 1024 x 768 (or higher) resolution monitor recommended
- Network cards and cabling for local network access
- Internet access (contact your local network administrator)
- Printer (optional) or an installed printer driver
- Projection system to display the instructor’s computer screen
Software:
To prepare a system for the class, install the following software according to the instructions provided. You will need the following software:
Microsoft® Windows® 10 64-bit with sufficient licenses.
Windows 10 requires activation unless you have volume-licensing agreements. There is a grace period for activation. If the duration of your class will exceed the activation grace period (for example, if you are teaching the class over the course of an academic semester), you should activate the installations at some point before the grace period expires. Otherwise, the operating system may stop working before the class ends.
- Microsoft® SQL Server® 2017 Express. (When this course was written, the installation file for this free software was available for download from https://www.microsoft.com/en-us/sql- server/sql-server-downloads.)
- Microsoft® SQL Server® Management Studio (SSMS). (When this course was written, the installation file for this free software was available for download from https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio- ssms.)
Upcoming Classes
We offer more than just SQL Training
Our successful training results keep our corporate and military clients returning. That’s because we provide everything you need to succeed. This is true for all of our courses.
Strategic Planning & Project Management
From Lean Six Sigma to Project Management Institute Project Management Professional, Agile and SCRUM, we offer the best-in-class strategic planning and project management training available. Work closely with our seasoned multi-decade project managers.
IT & Cybersecurity
ATA is the leading OffSec and Hack the Box US training provider, and a CompTIA and EC-Council award-winning training partner. We offer the best offensive and defensive cyber training to keep your team ahead of the technology skills curve.
Leadership & Management
Let us teach your team the high-level traits and micro-level tools & strategies of effective 21st-century leadership. Empower your team to play to each others’ strengths, inspire others and build a culture that values communication, authenticity, and community.
