Tag: Study

NOTES | Querying Full-Text Data

Creating Full-Text Catalogs and Indexes Full-text search allows approximate searches in SQL Server 2012 databases. Before you start using full-text predicates and functions, you must create full-text indexes inside full-text catalogs. After you create full-text indexes over character columns in your database, you are able to search for: Simple terms—that is, one or more specific words or phrases. Prefix terms, which are terms the words or phrases begin with. Generation terms, meaning inflectional forms of words. Proximity terms, or words or phrases close to another word or phrase. Thesaurus terms, or synonyms of a word. Weighted terms, which are words…

NOTES | Grouping and Windowing

SQL Server can perform some data analysis operations. A data analysis function is a function applied to a set of rows, and it returns a single value. An example of such a function is the SUM aggregate function. A data analysis function can be either a group function or a window function. The two types differ in how you define the set of rows for the function to operate on. You can use grouped queries to define grouped tables, and then a group function is applied to each group. Or, you can use windowed queries that define windowed tables, and…

NOTES | Combining Sets

Using Joins Often, data that you need to query is spread across multiple tables. The more normalized the environment is, the more tables you usually have. The tables are usually related through keys, such as a foreign key in one side and a primary key in the other. Then you can use joins to query the data from the different tables and match the rows that need to be related. Cross Joins A cross join is the simplest type of join, though not the most commonly used one. This join performs what’s known as a Cartesian product of the two…

NOTES | Filtering and Sorting Data

While the traditional way to filter data in T-SQL is based on predicates, it also supports filtering data based on a specified number of rows and ordering. To do so, use TOP and OFFSET. Filtering Data with Predicates ON, WHERE, and HAVING are the three query clauses which enable your filtering of data based on predicates as supported by T-SQL. Predicates, Three-Valued Logic, and Search Arguments Not all filtering aspects are obvious, but you need to understand how to form a predicate to maximize query efficiency and how predicates interact with NULLs; for this, you will need to be familiar…

NOTES | Getting Started with the SELECT Statement

Using the FROM and SELECT Clauses The FROM and SELECT clauses are two principal clauses that appear in almost every query that retrieves data. When you execute a SELECT statement, it usually retrieves one or more rows of information – the exact number depends on how you construct the statement. These rows are collectively known as a result set. Brief summary of the clauses in a SELECT statement: The FROM Clause FROM – This is the second most important clause in the SELECT statement and is also required. You use the FROM clause to specify the tables or views from…

NOTES | A Few Things

In preparing for this exam, my plan is to work my way through each chapter in the Training Kit book, to take notes, gather more information from across the internet and the book SQL Queries For Mere Mortals. In doing so, I’ll be documenting my notes. At times, it can/may be word for word from the training kit or SQL Queries book. This is a summary/collection for me to quickly refer back on, reread or review again.