In this chapter, students will learn how SQL code formatting, commenting, and aliasing is used to make queries easy to read and understand.  Students will also be introduced to query processing order in the database versus the order of the SQL syntax in a query.

Introduction

Formatting - player BMI

Commenting - player BMI

Commenting - how many Kiwis in the NBA?

Aliasing

Ambiguous column names

Aliasing - team BMI

Query order

Processing order

Syntax order - New Zealand earthquakes

Syntax order - Japan earthquakes

Syntax order - very large earthquakes

 Introduction, Review and The Order of Things

This chapter introduces filtering with WHERE and HAVING and some best practices for how (and how not) to use these keywords. Next, it explains the methods used to interrogate data and the effects these may have on performance.  Finally, the chapter goes over the roles of DISTINCT() and UNION in removing duplicates and their potential effects on performance.

Filtering with WHERE

Column does not exist

Functions in WHERE

Test your knowledge of WHERE

Filtering with HAVING

Row filtering with HAVING

Filtering with WHERE and HAVING

Test your knowledge of HAVING

Interrogation after SELECT

SELECT what you need

Limit the rows with TOP

Should I use ORDER BY?

Managing duplicates

Remove duplicates with DISTINCT()

UNION and UNION ALL

UNION or DISTINCT()?

Filtering and Data Interrogation

This chapter is an introduction to sub-queries and their potential impacts on query performance.  It also examines the different methods used to determine if the data in one table is present, or absent, in a related table.

Sub-queries

Uncorrelated sub-query

Correlated sub-query

Sub-query vs INNER JOIN

Presence and absence

INTERSECT

EXCEPT

Interrogating with INTERSECT

Alternative methods 1

IN and EXISTS

NOT IN and NOT EXISTS

NOT IN with IS NOT NULL

Alternative methods 2

INNER JOIN

Exclusive LEFT OUTER JOIN

Test your knowledge

Sub-queries and presence or absence

Students are introduced to how STATISTICS TIME, STATISTICS IO, indexes, and executions plans can be used in SQL Server to help analyze and tune query performance.

Statistik waktu

STATISTICS TIME dalam kueri

Hasil STATISTICS TIME

Statistik pembacaan halaman

STATISTICS IO: Contoh 1

STATISTICS IO: Contoh 2

Perbandingan STATISTICS IO

Indeks

Uji pemahaman Anda tentang indeks

Indeks clustered

Rencana eksekusi

Operator Sort dalam rencana eksekusi

Uji pengetahuan Anda tentang rencana eksekusi

Penyetelan kinerja kueri: catatan akhir

Query performance tuning

Orders dataset

NBAPlayers dataset

NBATeams dataset

NBAPlayersStatistics dataset

Sebuah tugas yang sangat krusial bergantung pada keterampilan pengodean SQL Anda. Anda diberi kode untuk diperbaiki. Kode tersebut menghasilkan hasil yang Anda perlukan, tetapi berjalan terlalu lambat, dan penataannya buruk sehingga sulit dibaca. Tenggatnya besok. Anda perlu menata ulang kode dan mencoba berbagai metode untuk meningkatkan kinerja. Tekanannya tinggi! Dalam kursus ini, kita akan menggunakan SQL pada himpunan data dunia nyata, dari olahraga hingga geosains, untuk mempelajari praktik pengodean yang baik dan berbagai cara bagaimana kita dapat meningkatkan kinerja kueri untuk mencapai hasil yang sama.

Intermediate SQL Server

Pelajari cara memformat ulang kode dan coba metode berbeda untuk meningkatkan kinerja kueri SQL.

Meningkatkan Kinerja Kueri di SQL Server

Dalam kursus ini, mahasiswa akan belajar menulis kueri yang efisien dan mudah dibaca serta dipahami.

Pengembang SQL Server

SQL Server untuk Administrator Database

Uji pemahaman Anda tentang indeks

Meningkatkan Kinerja Kueri di SQL Server

Latihan interaktif praktis