Querying with Transact-SQL

Question 1

What is SQL?

Answer 1

Structured Query Language

Question 2

Good to Remember

Answer 2

SQL is a declarative language and not a procedural language.

Describe what you want to get not how you want to get it

Question 3

Key Points

Answer 3

KEY POINTS

• Transact-SQL is the language used to query data in Microsoft SQL Server and Azure SQL Database.
• Data is stored in tables, which may be related to one another through common key fields.
• Objects in a database are organized into schemas.
• The fully qualified naming syntax for an object is server_name.database_name.schema_name.object_name, but in most cases you can abbreviate this to schema_name.object_name.

Question 4

The result of a SELECT statement is always a………………..

Answer 4

Virtual Table

Question 5

What does the ‘AS’ statement do?

Answer 5

Assigns a new name or an alias name to a column

Question 6

Waht is ‘NULL’

Answer 6

NULL is an unknown value and any column operation performed on NULL will return a NULL ( eg. 58 + NULL = NULL)

Question 7

KEY POINTS

Answer 7

Use the SELECT statement to retrieve a rowset of data from tables and views in a database.

SELECT statements are written with the following clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. However, the query engine processes the clauses in this order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

In the SELECT clause, you can use * to return all columns, but generally you should specify explicit columns.

You can specify expressions in the SELECT clause to return the results of calculations.

You can use the AS keyword to specify aliases for columns in the rowset returned by the SELECT statement.

Question 8

Can you Concatenate or add 2 different data types?

Answer 8

NO

Question 9

Explicit Conversion of Data Types?

Question 10

Data Types : KEY POINTS

Answer 10

Transact-SQL supports a wide range of data types, which can be broadly categorized as exact numeric,approximate numeric, character, date/time, binary, and other (which includes specialized data types for handling data such as XML and spatial data).

Some data types are compatible, and values can be implicitly converted between them. Conversion between other data types requires the use of explicit conversion functions.

Question 11

Working with NULLs

Question 12

CASE Expression Syntax

Question 13

KEY POINTS : NULL Datatype

Answer 13

NULL is used to indicate an unknown or missing value. NULL is not equivalent to zero or an empty string.

Arithmetic or string concatenation operations involving one or more NULL operands return NULL. For example, 12 + NULL = NULL.

If you need to compare a value to NULL, use the IS operator instead of the = operator.

The ISNULL function returns a specified alternative value for NULL columns and variables.

The NULLIF function returns NULL when a column or variable contains a specified value.

The COALESCE function returns the first non-NULL value in a specified list of columns or variables).

Question 14

You write a query that returns the Name and Price columns from a table named Product in the Production schema. In the resulting rowset, you want the Name column to be named ProductName.

Which of the following Transact-SQL statements should you use?

Answer 14

SELECT Name AS ProductName, Price FROM Production.Product;

Question 15

You need to retrieve data from a column that is defined as char(1). If the value in the column is a digit between 0 and 9, the query should return it as an integer value. Otherwise, the query should return NULL.

Which two functions can you use to accomplish this?

Answer 15

• TRY_CAST
• TRY_CONVERT

Question 16

You write a Transact-SQL query that returns the Cellphone column from the Sales.Customer table. Cellphoneis a varchar column that permits NULL values. For rows where the Cellphone value is NULL, your query should return the text ‘None’. Select the correct function to complete the following query:

SELECT FirstName, LastName, __________(Cellphone, ‘None’) AS Cellphone

FROM Sales.Customer;

Answer 16

The ISNULL function substitutes the specified value for NULL.

Question 17

ISNULL (Transact-SQL) Function

Answer 17

Examples:

SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS ‘Max Quantity’ FROM Sales.SpecialOffer;

Testing for NULL in a WHERE clause

SELECT Name, Weight FROM Production.Product WHERE Weight IS NULL;

Question 18

Removing Duplicates

Answer 18

In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values.

The DISTINCT keyword can be used to return only distinct (different) values.

Example

SELECT DISTINCT City FROM Customers;

SELECT COUNT (DISTINCT Country)

FROM Supplier;

SELECT DISTINCT Color,Size FROM Product;

Question 19

How do you Sort Results?

Answer 19

ORDER BY

Example of Using ORDER BY and TOP expressions

SELECT TOP 10 Id, ProductName, UnitPrice, Package

FROM Product

ORDER BY UnitPrice DESC;

Question 20

OFFSET FETCH Clause

Answer 20

The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.

Limitations in Using OFFSET-FETCH

ORDER BY is mandatory to use OFFSET and FETCH clause.

OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.

TOP cannot be combined with OFFSET and FETCH in the same query expression.

The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

Example 1 Skip first 10 rows from the sorted result set and return the remaining rows.

SELECT First Name + ‘ ‘ + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS;

Question 21

KEY POINTS : ORDER BY

Answer 21

By default, the SELECT statement returns all rows. If mulitple rows contain the same values for every column, they are duplicated in the results. Using the DISTINCT keyword eliminates duplicates, ensuring that only onerow for each distinct combination of column values is returned.

The order of rows in the result of a SELECT statement is not guaranteed unless you explicitly specify one or more columns in an ORDER BY clause. You can specify sort direction as ASC (the default) or DESC.

You can combine the ORDER BY clause with the TOP keyword to retrict the results so that they include only the top n rows (where n is the number or percentage of rows you want to return).

You can implement a query to retrieve a specified “page” of results by using the OFFSET and FETCH keywords with the ORDER BY clause.

Question 22

Filtering and Using Predicates

Answer 22

KEY POINTS

Use the WHERE clause to filter the results returned by a SELECT query based on a search condition.

A search condition is composed of one or more predicates.

Predicates include conditional operators (such as =, >, and <), IN, LIKE, and NOT.

You can use AND and OR to combine predicates based on Boolean logic.

Question 23

What is the wild card character in SQL?

Answer 23

%

Question 24

LAB

you need to find the names, colors, and sizes of the products with a product model ID 1.

Answer 24

CODE:

SELECT Name,Color,Size from SalesLT.Product WHERE ProductModelID=1;

Question 25

TASK:

Retrieve the product number and name of the products that have a color of ‘black’, ‘red’, or ‘white’ and
a size of ‘S’ or ‘M’.

Answer 25

CODE:

SELECT ProductNumber, Name
FROM SalesLT.Product
WHERE Color IN (‘Black’,’Red’,’White’) and Size IN (‘S’,’M’);

Question 26

TASK:

Retrieve the product number, name, and list price of products whose product number begins ‘BK-‘.

Answer 26

CODE:

SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE ‘BK-%’;

Question 27

Retrieve the product number, name, and list price of products whose product number begins ‘BK-‘.

Modify your previous query to retrieve the product number, name, and list price of products whose
product number begins ‘BK-‘ followed by any character other than ‘R’, and ends with a ‘-‘ followed by
any two numerals.

Answer 27

CODE:

SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE ‘BK-[^R]%-[0-9][0-9]’;

Question 28

The SQL JOIN syntax

Answer 28

SELECT column-names

FROM table-name1 JOIN table-name2

ON column-name1 = column-name2

WHERE condition

Question 29

Inner Joins KEY POINTS:

Answer 29

Inner joins return only rows where a match can be found in both tables.

Inner joins that match rows based on columns containing the same value in both tables are sometimes referred to as equi-joins.

Question 30

SELF JOIN KEY POINTS

Answer 30

• A self-join is an inner, outer, or cross join that matches rows in a table to other rows in the same table.
• When defining a self-join, you must specify an alias for at least one instance of the table being joined.

Question 31

TASK:

write a query that returns the company name from the SalesLT.Customer table, and the sales order ID and total due from the SalesLT.SalesOrderHeader table.

Answer 31

CODE:

SELECT c.CompanyName, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
JOIN SalesLT.SalesOrderHeader AS oh
ON oh.CustomerID = c.CustomerID;

Question 32

TASK:

The sales manager wants a list of all customer companies and their contacts (first name and last name),
showing the sales order ID and total due for each order they have placed. Customers who have not
placed any orders should be included at the bottom of the list with NULL values for the order ID and
total due.

Answer 32

CODE:

SELECT c.CompanyName, c.FirstName, c.LastName, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
LEFT JOIN SalesLT.SalesOrderHeader AS oh
ON c.CustomerID = oh.CustomerID
ORDER BY oh.SalesOrderID DESC

Question 33

TASK :

Write a query that returns a list of customer IDs, company names, contact names
(first name and last name), and phone numbers for customers with no address stored in the database.

Answer 33

CODE:

SELECT c.CompanyName, c.FirstName, c.LastName, c.Phone
FROM SalesLT.Customer AS c
LEFT JOIN SalesLT.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
WHERE ca.AddressID IS NULL;

Question 34

TASK:

Some customers have never placed orders, and some products have never been ordered. Create a query
that returns a column of customer IDs for customers who have never placed an order, and a column of
product IDs for products that have never been ordered. Each row with a customer ID should have a
NULL product ID (because the customer has never ordered a product) and each row with a product ID
should have a NULL customer ID (because the product has never been ordered by a customer).

Answer 34

CODE:

SELECT c.CustomerID, p.ProductID
FROM SalesLT.Customer AS c
FULL JOIN SalesLT.SalesOrderHeader AS oh
ON c.CustomerID = oh.CustomerID
FULL JOIN SalesLT.SalesOrderDetail AS od
ON od.SalesOrderID = oh.SalesOrderID
FULL JOIN SalesLT.Product AS p
ON p.ProductID = od.ProductID
WHERE oh.SalesOrderID IS NULL
ORDER BY ProductID, CustomerID;

Question 35

KEY POINTS: Union Query

Answer 35

Use UNION to combine the rowsets returned by mulitple queries.

Each unioned query must return the same number of columns with compatible data types.

By default, UNION eliminates duplicate rows. Specify the ALL option to include duplicates (or to avoid the overhead of checking for duplicates when you know in advance that there are none).

Question 36

KEY POINTS: INTERSECT and EXCEPT

Answer 36

Use INTERSECT to return only rows that are returned by both queries.

Use EXCEPT to return rows from the first query that are not returned by the second query.

Question 37

What does the UNION ALL statement do?

Answer 37

UNION ALL combines the results from both queries and retains duplicates.

Question 38

What does the INTERSECT Statement do?

Answer 38

INTERSECT returns only rows that are present in both query resultsets.

Question 39

What result does the following query return?

SELECT DISTINCT ProductID

FROM Sales.SalesOrderDetail

EXCEPT

SELECT ProductID

FROM Production.Product

WHERE Discontinued = 1

Answer 39

All non-discontinued products that have been sold

Combining queries using the EXCEPT set operator filters the results to include only rows that are returned by the first query but not by the second query.

Question 40

KEY POINTS : Functions

Answer 40

Scalar functions return a single value based on zero or more input parameters.

Logical functions return Boolean values (true or false) based on an expression or column value.

Window functions are used to rank rows across partitions or “windows”. Window functions include RANK, DENSE_RANK, NTILE, and ROW_NUMBER.

Aggregate functions are used to provide summary values for mulitple rows - for example, the total cost of products or the maximum number of items in an order. Commonly used aggregate functions include SUM, COUNT, MIN, MAX, and AVG.

Question 41

KEY POINTS: GROUP BY FUNCTIONS

Answer 41

You can use GROUP BY with aggregate functions to return aggregations grouped by one or more columns or expressions.

All columns in the SELECT clause that are not aggregate function expressions must be included in a GROUP BY clause.

The order in which columns or expressions are listed in the GROUP BY clause determines the grouping hierarchy.

You can filter the groups that are included in the query results by specifying a HAVING clause.

Question 42

What value will the following query return in the OrderStatus column for rows with a Status value of 2?

SELECT OrderNumber, CHOOSE(Status, ‘Ordered’, ‘Shipped’, ‘Delivered’) AS OrderStatus

FROM Sales.SalesOrderHeader

Answer 42

Shipped.

The CHOOSE function returns the value in the 1-based ordinal position of the list that corresponds to the value returned by the expression in the first parameter

Question 43

You run the following query:

SELECT COUNT(ProductID) AS ProductIDCount, COUNT(Quantity) AS QuantityCount, SUM(UnitPrice) AS PriceSum

FROM Sales.SalesOrderDetail;

What does the value returned by the query for the QuantityCount column represent?

Answer 43

The number of rows containing a Quantity value.

The COUNT function counts the number of rows containing a non-null value for the specified column or expression.

Question 44

GROUP BY CLAUSE : GOOD TO REMEMBER

Answer 44

You cannot use column aliases in a GROUP BY clause, so you must specify the columns or expressions on which you want to group. When the grouping fields represent a hierarchy, you should generally specify them in order of increasing granularity.

Question 45

TASK:

Write a query to return the product ID of each product, together with the product name formatted as upper case and a column named ApproxWeight with the weight of each product rounded to the nearest whole unit.

Answer 45

CODE:

SELECT ProductID,
UPPER(Name) AS ProductName,
ROUND(Weight, 0) AS ApproxWeight
FROM SalesLT.Product;

Question 46

KEY POINTS : Subqueries

Answer 46

Subqueries are Transact-SQL queries nested within an outer query.

Scalar subqueries return a single value.

Multi-valued subqueries return a single-column rowset.

Question 47

KEY POINTS : Corelates Subqueries

Answer 47

Correlated subqueries reference objects in the outer query.

Question 48

KEY POINTS : APPLY Query

Answer 48

The APPLY operator enables you to execute a table-valued function for each row in a rowset returned by a SELECT statement. Conceptually, this approach is similar to a correlated subquery.

CROSS APPLY returns matching rows, similar to an inner join. OUTER APPLY returns all rows in the original SELECT query results with NULL values for rows where no match was found.

Question 49

KEY POINTS : VIEW Query

Answer 49

Views are database objects that encapsulate SELECT queries.

You can query a view in the same way as a table, however there are some considerations for updating them.

Question 50

KEY POINTS : Temporary Tables and Variables

Answer 50

Temporary tables are prefixed with a # symbol and stored in a temporary workspace (the tempdb database in SQL Server).

Temporary tables are automatically deleted when the session in which they were created ends.

Excessive use of temporary tables can negatively affect overall database server performance.

Table variables are prefixed with a @ symbol and are stored in memory.

Table variables are scoped to the batch in which they are created.

Table variables work best with small sets of data.

Question 51

KEY POINTS: Querying Table-Valued Functions

Answer 51

Table-Valued Functions (TVFs) are functions that return a rowset.

TVFs can be parameterized.

Question 52

KEY POINTS : Common Table Expressions

Answer 52

A derived table is a subquery that generates a multicolumn rowset. You must use the AS clause to define an alias for a derived query.

Common Table Expressions (CTEs) provide a more intuitive syntax or defining rowsets than derived tables, and can be used mulitple times in the same query.

You can use CTEs to define recursive queries.

Question 53

KEY POINTS : INSERT STATEMENT

Answer 53

Use the INSERT statement to insert one or more rows into a table.

When inserting explicit values, you can omit identity columns, columns that allow NULLs, and columns on which a default constraint is defined.

Identity columns generate a unique integer identifier for each row. You can also use a sequence to generate unique values that can be used in multiple tables

Question 54

FIND EXAMPLES TO UPDATE,INSERT AND MERGE,DELETE DATA IN A TABLE

Question 55

KEY POINTS : UPDATE,INSERT,MERGE

Answer 55

Use the UPDATE statement to modify the values of one or more columns in specified rows of a table.

Use the DELETE statement to delete specified rows in a table.

Use the MERGE statement to insert, update, and delete rows in a target table based on data in a source table