Querying with Transact-SQL Flashcards
What is SQL?
Structured Query Language
Good to Remember
SQL is a declarative language and not a procedural language.
Describe what you want to get not how you want to get it
Key Points
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.
The result of a SELECT statement is always a………………..
Virtual Table
What does the ‘AS’ statement do?
Assigns a new name or an alias name to a column
Waht is ‘NULL’
NULL is an unknown value and any column operation performed on NULL will return a NULL ( eg. 58 + NULL = NULL)
KEY POINTS
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.
Can you Concatenate or add 2 different data types?
NO
Explicit Conversion of Data Types?
Data Types : KEY POINTS
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.
Working with NULLs
CASE Expression Syntax
KEY POINTS : NULL Datatype
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).
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?
SELECT Name AS ProductName, Price FROM Production.Product;
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?
- TRY_CAST
- TRY_CONVERT
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;
The ISNULL function substitutes the specified value for NULL.
ISNULL (Transact-SQL) Function
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;
Removing Duplicates
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;
How do you Sort Results?
ORDER BY
Example of Using ORDER BY and TOP expressions
SELECT TOP 10 Id, ProductName, UnitPrice, Package
FROM Product
ORDER BY UnitPrice DESC;
OFFSET FETCH Clause
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;
KEY POINTS : ORDER BY
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.
Filtering and Using Predicates
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.
What is the wild card character in SQL?
%
LAB
you need to find the names, colors, and sizes of the products with a product model ID 1.
CODE:
SELECT Name,Color,Size from SalesLT.Product WHERE ProductModelID=1;
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’.
CODE:
SELECT ProductNumber, Name
FROM SalesLT.Product
WHERE Color IN (‘Black’,’Red’,’White’) and Size IN (‘S’,’M’);
TASK:
Retrieve the product number, name, and list price of products whose product number begins ‘BK-‘.
CODE:
SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE ‘BK-%’;
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.
CODE:
SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE ‘BK-[^R]%-[0-9][0-9]’;
The SQL JOIN syntax
SELECT column-names
FROM table-name1 JOIN table-name2
ON column-name1 = column-name2
WHERE condition
Inner Joins KEY POINTS:
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.
SELF JOIN KEY POINTS
- 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.
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.
CODE:
SELECT c.CompanyName, oh.SalesOrderID, oh.TotalDue
FROM SalesLT.Customer AS c
JOIN SalesLT.SalesOrderHeader AS oh
ON oh.CustomerID = c.CustomerID;
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.
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
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.
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;
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).
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;
KEY POINTS: Union Query
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).
KEY POINTS: INTERSECT and EXCEPT
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.
What does the UNION ALL statement do?
UNION ALL combines the results from both queries and retains duplicates.
What does the INTERSECT Statement do?
INTERSECT returns only rows that are present in both query resultsets.
What result does the following query return?
SELECT DISTINCT ProductID
FROM Sales.SalesOrderDetail
EXCEPT
SELECT ProductID
FROM Production.Product
WHERE Discontinued = 1
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.
KEY POINTS : Functions
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.
KEY POINTS: GROUP BY FUNCTIONS
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.
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
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
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?
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.
GROUP BY CLAUSE : GOOD TO REMEMBER
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.
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.
CODE:
SELECT ProductID,
UPPER(Name) AS ProductName,
ROUND(Weight, 0) AS ApproxWeight
FROM SalesLT.Product;
KEY POINTS : Subqueries
Subqueries are Transact-SQL queries nested within an outer query.
Scalar subqueries return a single value.
Multi-valued subqueries return a single-column rowset.
KEY POINTS : Corelates Subqueries
Correlated subqueries reference objects in the outer query.
KEY POINTS : APPLY Query
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.
KEY POINTS : VIEW Query
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.
KEY POINTS : Temporary Tables and Variables
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.
KEY POINTS: Querying Table-Valued Functions
Table-Valued Functions (TVFs) are functions that return a rowset.
TVFs can be parameterized.
KEY POINTS : Common Table Expressions
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.
KEY POINTS : INSERT STATEMENT
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
FIND EXAMPLES TO UPDATE,INSERT AND MERGE,DELETE DATA IN A TABLE
KEY POINTS : UPDATE,INSERT,MERGE
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
