T-SQL Study Guide

What are SQL numeric data types?

-bit -decimal and numeric -float and real -int, bigint, smallint, and tinyint -money and smallmoney

What is the DISTINCT keyword?

Allows you to select unique records from a table.

What is an Alias in SQL?

An alias is a shorthand for a table or a column name and can make queries easier to read. I have found them very useful with performing JOINS. Aliases only exists for the duration of the scope.

What is the WHERE clause?

Specifies the search condition for the rows returned by the query. There is no limit to the number of predicates that can be included in a search condition.

What is Pattern Matching in SQL?

TBD

What is an INNER JOIN?

In an inner join, records in both tables are selected where the join condition is met.

What is denormalization?

It is a technique used to access the data from higher to lower normal forms of databases. It is also a process of introducing redundancy into a table by incorporating data from the related tables.

What is a Navigation structure in a DBMS?

Records/objects are found by following references from other objects

What is the HAVING clause?

Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group. (The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.)

What are Aggregate functions?

The aggregate functions are COUNT, MAX, MIN, SUM, AVG.

What is the _ wildcard operator?

Use the underscore character _ to match any single character in a string comparison operation that involves pattern matching, such as LIKE. Examples include: -'a_c' // abc & azc is valid -'a__d' // abcd & axyd is valid -'__c' // abc & xyc is valid

How can you select unique records from a table?

Using the DISTINCT keyword.

What is a hash index?

With a hash index, data is accessed through an in-memory hash table. Hash indexes consume a fixed amount of memory, which is a function of the bucket count.

What is a trigger?

A database trigger is a code or programs that automatically execute with response to some event on a table or view in a database. Mainly, trigger helps to maintain the integrity of the database.

What is a Foreign Key?

A foreign key is a field or a group of fields that refers to the PRIMARY key in other tables.

What is SQL injection?

A technique to manipulate a back-end database and retrieve information that was unauthorized. To prevent SQL injection attacks you must sanitize inputs and use parameterized queries.

What are arithmetic operators?

Arithmetic operators run mathematical operations on two expressions of one or more data types. Examples include: -[ + ], [ -], [ * ], [ / ], [ % ]

What are SQL binary data types?

Binary data types of either fixed length or variable length. -binary [ ( n ) ] | Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes. -varbinary [ ( n | max) ] | Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The ANSI SQL synonym for varbinary is binary varying.

What is the CONTAIN predicate?

Searches for precise or fuzzy matches to single words and phrases, words within a certain distance of one another, or weighted matches. Examples include: -WHERE CONTAINS(NameColumn, 'abc'); -WHERE CONTAINS(NameColumn, ' abc OR xyz '); -WHERE CONTAINS((NameColumn, ColorColumn), 'abc');

What is Data Manipulation Language?

DML commands allow you to manipulate / change actual data in a database. DML commands are not auto-committed, which means changes can be rolled back. Commands include: -SELECT -INSERT -UPDATE -DELETE

What is a hierarchical structure in a DBMS?

Data is organized into a tree-like structure and uses one-to-many relationships.

What is a relationship and what are they?

Database Relationship is defined as the connection between the tables in a database. There are various data basing relationships, and they are as follows: One to One Relationship One to Many Relationship Many to One Relationship Self-Referencing Relationship

What are string operators?

SQL Server supports two types of string operators including: -String concatenation operators: ['string1' + ' ' + 'string2'] -Wildcard string operators: [ % ], [ [ ] ], [ [^] ], [ _ ]

What are SQL data types?

SQL data types support the following types: -Binary -Numeric -Character(s) -Unicode Character(s) -Date / Time

What is the difference between SQL and MySQL?

SQL is a programming language whereas MySQL is an open-source relational database management system. It was owned by MySQL AB which has now been acquired by Oracle and MySQL directly competes against Oracle DB.

What are set operators?

SQL set operators are used to combine the results obtained from two or more queries into a single result. The queries which contain two or more subqueries are known as compounded queries. Examples include: -UNION | Concatenates the results of two queries, excludes duplicates -UNION ALL | Concatenates the results of two queries, includes duplicates -INTERSECT | Returns distinct rows that are output by both the left and right input queries operator. -EXCEPT | Returns distinct rows from the left input query that aren't output by the right input query.

What are the TRUNCATE, DELETE and DROP statements?

-Both Delete and Truncate statements are both Data Manipulation Language (DML) statements but Delete has a Where clause allowing it to delete fields or records and Truncate can delete an entire table but preserve the table structure. Both use some kind of logging and are possible to roll back the changes under some circumstances. -Drop statements are Data Definition Language (DDL) statements and will remove the data in the table and the table itself and is not possible to roll back.

What are SQL character(s) data types?

-char [ ( n ) ] | Fixed-size string data. n defines the string size in bytes and must be a value from 1 through 8,000 -varchar [ ( n | max ) ] | Variable-size string data. Use n to define the string size in bytes and can be a value from 1 through 8,000 or max -nchar | nchar [ ( n ) ] | Fixed-size string data. n defines the string size in byte-pairs and must be a value from 1 through 4,000. The storage size is two times n bytes -nvarchar [ ( n | max ) ] | Variable-size string data. n defines the string size in byte-pairs and can be a value from 1 through 4,000 -ntext | Variable-length Unicode data with a maximum string length of 2^30 -1 (1,073,741,823) bytes -text | Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647)

What are SQL date / time data types?

-date | Defines a date in SQL Server. -datetime | Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock. -datetime2 | A datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision. -datetimeoffset | Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock. -smalldatetime | Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds. -time | Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.

What are other SQL data types?

-table | a special data type used to store a result set for processing at a later time. table is primarily used for temporarily storing a set of rows that are returned as the table-valued function result set -uniqueidentifier | a 16-byte GUID -user defined

What is the GROUP BY clause?

A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group.

What is the TOP clause?

A SELECT statement clause that is used to specify the number of records to return. It is useful on large tables with thousands of records.

What is a UNIQUE constraint?

A UNIQUE constraint is a type of field restriction that dictates that each value in this column must be unique. A column with this constraint can still allow for NULL values, however. It's similar to a PRIMARY key.

What is a clustered index?

A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.

What is a database?

A collection of data generally stored electronically and controlled by a database management system. Typically modeled in rows and columns in a series of tables.

What is a Cursor?

A database Cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records.

What is a DBMS (Database Managements System)?

A database management system is a software for creating and managing databases. It is an interface between databases and application programs. Some examples of DBMS are SQL Server, MySQL, PostgreSQL, and MongoDB.

What is a non-clustered index?

A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.

What is a Primary Key?

A primary key is a type of constraint that is used to uniquely identify each table record. In a relational database, a table cannot have more than one primary key but the primary key can be a field or a group of fields.

What is a Query?

A query is a request for data in a database table or a group of tables. It can either be a select query or an action query.

What is RDBMS? How is it different from DBMS?

A relational database management system is a system used to manage relational databases. Every RDBMS is a DBMS. Some key differences are: -RDBMS store data in tables whereas other DBMS might store data in files or objects. -Their structure is based on a tabular structure while other database management systems might have a navigation or hierarchical form. -RDBMS supports normalization whereas other DBMS might not.

What is Transact-SQL (T-SQL)?

A set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language (SQL), including transaction control, exception and error handling, row processing and declared variables.

What is a SQL statement?

A statement is text that the database recognizes as a valid command. Consists of clauses, commands, and parameters.

What is a Recursive Stored Procedure?

A stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps programmers to use the same set of code any number of times.

What is a subquery?

A subquery is a query within another query. The outer query is called the main query, and the inner query is called the subquery. SubQuery is always executed first, and the result of subquery is passed on to the main query.

How many foreign keys can a table refer to?

A table can reference a maximum of 253 other tables and columns as foreign keys. SQL Server 2016 increases the limit for the number of other tables and columns that can reference columns in a single table from 253 to 10,000. However, self referencing FK (where table column is referencing itself in the same table) still is limited to 253 in all the versions of SQL Server.

What are transactions?

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

What is a unique index?

A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique. Uniqueness can be a property of both clustered and nonclustered indexes.

What is a View?

A view is a virtual table which consists of a subset of data contained in a table. Views are not virtually present, and it takes less space to store. Views can have data of one or more tables combined, and it is depending on the relationship.

What are SQL operators?

An operator is a symbol specifying an action that is performed on one or more expressions. The following tables lists the operator categories that SQL Server uses. Examples include: -Arithmetic Operators -Comparison Operators -Logical Operators -String Operators

What are SQL joins?

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table. Syntax: SELECT * FROM tableA join_type JOIN tableB ON ( join_condition ) Examples include: -INNER JOIN -LEFT [ OUTER ] JOIN -RIGHT [ OUTER ] JOIN -FULL [ OUTER ] JOIN -CROSS JOIN

What are SQL subsets?

Categories of SQL commands that can be divided into four subsets: -Data Definition Language (DDL) -Data Manipulation Language (DML) -Data Control Language (DCL) -Transaction Control Language (TCL)

What is the difference between a clustered and non-clustered index?

Clustered indexes store row data in order. Only a single clustered index can be created on a database table. This works efficiently only if data is sorted in increasing and decreasing order or a limit is specified on the columns involved in the table. Such a sequential arrangement of data on disks reduces block reads. In non-clustered indexes, data is arranged in a random way, but a logical ordering is internally specified by the index. Thus, the index order is not the same as the physical ordering of data. A "create index" statement creates a non-clustered index by default, which also creates a clustered index on the primary key. The index keys are sorted here with the leaf containing a pointer to the page. Non-clustered indexes work well with tables where data is modified frequently and the index is created on columns used in order by WHERE and JOIN statements.

What are comparison operators?

Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types. Examples include: -[ = ], [ > ], [ < ] -[ != ], [ >= ], [ <= ] -[ <> ], [ !> ], [ !< ]

What are Constraints in SQL?

Constraints in a relational database are restrictions placed on a field or column. Common constraints are: -NOT NULL -UNIQUE -PRIMARY KEY -FOREIGN KEY

What is Data Control Language?

DCL commands are used to grant and take back authority from any database user. Commands include: -Grant -Revoke

What is Data Definition Language?

DDL commands allow you to make changes on data structures (schemas, tables, etc). DDL commands are auto-committed, which means it permanently saves all changes in the database. Commands include: -CREATE -ALTER -DROP -TRUNCATE -RENAME

What is the OVER clause?

Determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

What is IDENTITY?

IDENTITY keyword allows the user to create a unique number to be generated when a new record is inserted into the table.

What is a FULL JOIN?

In a full join, records in both tables are selected whether they meet the join condition or not. It's essentially both a LEFT and a RIGHT join and it also may include NULL values to indicate no data exists.

What is a LEFT JOIN?

In a left join, all rows from the left or first table are selected along with records from the right or second table where the join condition is met. If records do not meet the join condition, NULL values are introduced to indicate no data exists.

What are Tables and Fields?

In a relational database, tables are collections of data consisting of fields and records. Fields are columns or attributes of a record. (Records, or rows, are a grouping of related fields.)

What is a RIGHT JOIN?

In a right join, all rows from the right or second table are selected along with records from the left or first table where the join condition is met. If records do not meet the join condition, NULL values are introduced to indicate no data exists.

What is an Index?

In database systems, an index is a data structure defined on columns in a database table to significantly speed up data retrieval operations. An index is a small copy of a database table sorted by key values. Without an index, query languages like SQL may have to scan the entire table from top to bottom to choose relevant rows.

What are indexes?

Indexes are special data structures associated with tables or views that help speed up queries. A table can have multiple column indexes. Indexes require storage, will have to update every time a table is updated. Examples include: -Hash -Clustered -Non-clustered -Unique

How does a join condition define the way two tables are related in a query?

It defines it as: -Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table. -Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.

What are logical operators?

Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN. Examples include: -AND | TRUE if both Boolean expressions are TRUE. -BETWEEN | TRUE if the operand is within a range. -EXISTS | TRUE if a subquery contains any rows. -IN | TRUE if the operand is equal to one of a list of expressions. -LIKE | TRUE if the operand matches a pattern. -NOT | Reverses the value of any other Boolean operator. -OR | TRUE if either Boolean expression is TRUE.

What is the [ ^ ] wildcard operator?

Matches any single character that is not within the range or set specified between the square brackets [^]. These wildcard characters can be used in string comparisons that involve pattern matching, such as LIKE. Examples include: -'Ab[^c]' -`[^a-z]` -'[^0-9A-z]%'

What is the [ ] wildcard operator?

Matches any single character within the specified range or set that is specified between brackets [ ]. These wildcard characters can be used in string comparisons that involve pattern matching, such as LIKE. Examples include: -'[ab]' -`[a-c]` -'[a-d]%'

What is the % wildcard operator?

Matches any string of zero or more characters. This wildcard character can be used as either a prefix or a suffix. Examples include: -'char%' -'%char' -'%char%' -'ch%ar'

What are SQL predicates?

Predicates are expressions that evaluate to TRUE, FALSE, or UNKNOWN. They are used in the search condition of WHERE clauses and HAVING clauses, the join conditions of FROM clauses, and other constructs where a Boolean value is required. Examples include: -CONTAINS -FREETEXT -IS [NOT] NULL | Determines whether a specified expression IS or IS NOT NULL.

What are SQL clauses?

SQL Clauses are used SQL statements that receive a conditional expression to filter rows from the entire set of records. Examples include: -WHERE -HAVING -ORDER BY -TOP -GROUP BY

What are the advantages and disadvantages of stored procedures?

Stored procedure can be used as a modular programming -means create once, store and call for several times whenever required. This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data. Disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.

What is a Stored Procedure?

Stored procedure is a group of SQL statements that forms a logical unit and performs a particular task. Stored Procedures are used to encapsulate a set of operations or queries to execute on a database. Stored procedures can be compiled and executed with different parameters and results and may have any combination of input/output parameters.

What is SQL?

Structured Query Language; the most popular programming language for managing relational databases. It was developed by IBM in the early 1970s.

What is Transaction Control Language?

TCL commands are used to manage transactions (DML statements only) in a database. Commands include: -COMMIT -ROLLBACK -SAVEPOINT

What is the ORDER BY clause?

The ORDER BY clause is used in SQL for sorting records. It is used to arrange the result set either in ascending or descending order. When we query using the SELECT statement, the result is not in an ordered form.

What are connection strings?

The connection string that includes the source database name, and other parameters needed to establish the initial connection. The default value is an empty string.

What are the various forms of Normalization?

The normal forms can be divided into 5 forms, and they are explained below: First Normal Form (1NF):. This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns. Second Normal Form (2NF):. Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys. Third Normal Form (3NF):. This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints. Fourth Normal Form (4NF):. Meeting all the requirements of the third normal form and it should not have multi-valued dependencies.

What is normalization?

The process of reorganizing data in a database so that there is no redundancy of data (all data is stored in only one place) and data dependencies are logical (all related data items are stored together).

Why is normalization important?

There are three main reasons to normalize a database. The first is to minimize duplicate data, the second is to minimize or avoid data modification issues, and the third is to simplify queries.

What are all the different types of indexes?

There are three types of indexes: Unique Index -This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when the primary key is defined. Clustered Index -This type of index reorders the physical order of the table and search based on the key values. Each table can have only one clustered index. NonClustered Index -NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 nonclustered indexes.

What are the types of subquery?

There are two types of subquery -Correlated and Non-Correlated. A correlated subquery cannot be considered as an independent query, but it can refer to the column in a table listed in the FROM the list of the main query. A Non-Correlated subquery can be considered as an independent query and the output of the subquery is substituted in the main query.

What are user defined functions?

User defined functions are the functions written to use that logic whenever required. It is not necessary to write the same logic several times. Instead, function can be called or executed whenever needed.