Module 2

Question 1

What are the standard BIG FIVE aggregate functions? What are these functions used for?

Answer 1

1. These functions are MIN(), MAX(),
2. SUM(), AVG(), and COUNT().
3. SQL Server also provides additional aggregate functions.
4. Aggregate functions are used (many times in conjunction with GROUP BY and
5. HAVING) to summerize data.

Question 2

What types of code can be used in a stored procedure?

Answer 2

1. T-SQL or Transact SQL and CLR or
2. Common Language Runtime can be used to program stored procedures

Question 3

If you user an aggregate function within a view, that view will no longer be updatable, which means you will only be able to use that view to retrieve or SELECT data. Why?

Answer 3

1. Aggregate data cannot map back to a particular row in underlying tables,
   
   1. so the DBMS will not know how to update the underlying table to prioduce the indicated changes in the aggregated data.

Question 4

What is the difference between Data Control Language (DCL), Data Definition Language (DDL) and Data Manipulation Language (DML)?

Answer 4

1. All three are components of SQL.
2. DCL - configures local security in the database allowing you to create users and roles as well as grant permissions to database objects
3. DDL - provides the facility to create and manage the logical schema in a database.
4. DML - provides the ability to query and manipulate data nad is the most widely used of the three components of SQL.

Question 5

What is the difference between a declarative programming language and an imparative programming language?

Answer 5

1. Using an imperative langauge you want to specify what you want to happen step by step.
2. In a declarative langauge you organize set of statements that describe what you want to happen.
3. In a declarative language you do not specify what you want to happen step by step.
   4.

Question 6

Why is an inline UDF sometimes called a VIEW that can accept parameters?

Answer 6

can return a table or set of data based on a parameters

Question 7

How do you change the database context?

Answer 7

This is achieved by USE <database></database>

Question 8

What is the primary purpose of a stored procedure and how does a stored procedure differ from a trigger?

Answer 8

1. Stored procedures can be used to define an APL encapsulate complex logic, and provde flow control or repetition (more complicated program logic.)
2. A trigger can contain similar code, but the code excutes when specific events happen (related to insert, update, delete, and logon.).

Question 9

What is the difference between a correlated subquery and a non-correlated subquery? What are the uses of each?

Answer 9

1. A correlated subquery is a subquery that references the columns in the outer query. In a correlated subquery,
2. the inner query runs one time for each row in the outer subquery. In a non-correlated subquery on the other had this does not happen.
3. The inner query runs once.
4. Correlated subqueries are used to create complicated queries to accomplish data retrieval tasks that cannot be accomplished other ways (such as using a join.)
5. A non-correlated subquery is used to create more dynamic code that does not require the author of the code to know all the intermediate values in the database.

Question 10

The correct usages for UNION, UNION ALL, EXCEPT, and INTERSECT.

Answer 10

1. combine multiple queries using the set operators
2. Set operators combine results from two or more queries into a single result set.

Question 11

What types of constraints did we talk about in SQL Server?

Answer 11

1. We talked about check constraints,
2. NOT NULL constraints,
3. PRIMARY KEY constraints, and
4. FOREIGN KEY constraints (as part of RELATIONAL INTEGRITY constraints).

Question 12

The process for performing all joins (INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN).

Answer 12

1. An SQL JOIN clause is used to combine rows from two or more tables,
   
   1. based on a common field between them.

Question 13

What is a qualified name?

Answer 13

1. a combination of identifiers that uniquely defines the location of a database object.
2. constructed by using up to four identifiers that represent the logical structure
3. represents hierarchy of the SQL Server instance, the database name, the database schema, and the database object.

Question 14

What is a Partitioned View?

Answer 14

1. This is a view which displays horizontally divided data from a set of member tables across one or more servers,
2. making the data appear as if it is from one table

Question 15

What is the database operation you would choose to merge and retrive data stored in more than one table or view?

Answer 15

This is a JOIN peration (either INNER, LEFT OUTER, RIGHT OUTER, or FULL OUTER)

Question 16

describe the TOP clause

Answer 16

1. used to specify the number of records to return.
2. ery useful on large tables with thousands of records.
3. Returning a large number of records can impact on performance.

Question 17

What is the purpose of ranking functions and what are the two different ranking functions? What is teh difference in these two functions?

Answer 17

They provide simple analytics such as statistical ordering and segmentation.

The two different ranking functions are RANK() and DENSE_RANK()

RANK() will create gaps when there is a tie

DENSE_RANK() will assign the same number to ties, but will not create gaps.

Question 18

What is an indexed view? How does Microsoft implement the view in order to allow it to be indexed?

Answer 18

1. An indexed view is a view where the data can be indexed just like a table.
2. In order for this to work, Microsoft “materializes” the view using as temporary table which is then indexed.

Question 19

What is Transact-SQL and who developed it?

Answer 19

a variabt of SQL that was developed by Microsoft and Sybase

Question 20

Is SQL an imperative or declarative programming language?

Answer 20

declarative

Question 21

What types or joins are available in SQL Server 2012? What is each used for?

Answer 21

1. JOIN or INNER JOIN - This join is used to retrieve data that matches based on the join condition specified in the ON clause.
2. LEFT OUTER JOIN - This join is used to retrieve all data from the left table and the data that matches in the right table based on the join condition specified in the ON clause.

Question 22

Wht are the three types of programming objects you can create in Microsoft SQL, Server and what are the three types of functions suppored in Microsoft SQL Server?

Answer 22

1. The three pgorammable objects are Stored Procedures, Functions, and Triggers.
2. The three types of functions are Scalar, Inline table-valued, Multistatement table-valued

Question 23

Describe why you would use the data control language componenet of the SQL programming language.

Answer 23

1. SQL DCL statements can be used to create logical security objects and to grant or revoke permissions.
2. To connect to a database, a user must be able to authenticate using either a Windows or SQL Server login.
3. SQL Server can be configured to use either Windows authentication (which lerverages the host operating system),.
4. a local SQL Server login usign SQL Server authentication,
5. or bother SQL Server and Windows authentication.

Question 24

How do you create a column or database name alias?

Answer 24

1. This is done by specifying AS and then the alias name after the column in the SELECT statement or
2. the table name in the FROM clause.
3. For tables the AS can be omitted and you can just follow the table name wityh a space and then the alias name in the FROM clause.

Question 25

What is the function of the IN keyword, EXISTS keyword, LIKE keyword, and TOP keyword?

Answer 25

1. IN - Allows you to specify multiple values in a list in the WHERE clause (for example: WHERE value IN [item1, item2, item3]). 2. EXISTS - This keyword is used in the WHERE clause in combination with a subquery and is considered to be met if the subquery returns at least one row. 3. LIKE - This keyword is used in the WHERE clause in order to search for a specified pattern in a column 4. TOP - This keyword is used immediate have SELECT to limit the number of rows returned in T-SQL Example: SELECT TOP 10 Last\_Name FROM Name;

Question 26

Explain the use of the semicolon (;) in SQL.

Answer 26

1. the statement terminatore 2. is placed to mark the end of a SQL statement. 3. not currently required for all statements, but it is good practice to use a semicolon to future-proof your queries as SQL standards evolve.

Question 27

When would you use a stored procedure and when would you use a trigger?

Answer 27

1. You must invoke a stored procedure using a nexec or similar command where a trigger executes automatically when the associated event happens. 2. You would then use a stored procedure when you wanted to encapsulate code for alter use (complex locig or to create an API) 3. you would use a trigger when you wanted code to execute automatically when the associated event happens.

Question 28

What are the major components of a recursive CTE?

Answer 28

1. You need a WITH clause containing two SELECT statements with a UNION ALL between them and 2. an outer SELECT statement that uses or references the with.

Question 29

What is the ALTER and how is it used?

Answer 29

1. ALTER is used to specify changes to an existing database objects. It is often used in conjunction with ADD and DROP 2. (example: ALTER TABLE ADD or ALTER TABLE DROP) 3. to add items to or remove items from database objects, 4. In the noted example you would be adding columns or removing columns.

Question 30

Is SQL case sensitive?

Answer 30

No

Question 31

The process for using TOP to restrict the number of rows retrieved

Answer 31

SELECT TOP number|percent column\_name(s) FROM table\_name;

Question 32

Define the logical operators AND, OR , and NOT and what is their precedence?

Answer 32

1. AND - All conditions must be true 2. OR - Any condition must be true 3. NOT - Reverses the truth of the original condition 4. Order of precedence is NOT, AND, OR

Question 33

What are the key requirements to keep in mind when creating an INSERT statement?

Answer 33

1. The column values in the VALUES or SELECT clause must match the order and data type of the columns specified in the INSERT clause. 2. The column values in the VALUES or SELECT clause must respect any constraints present in the table, for example: foreign keys or NOT NULL constraints. 3. The columns do not have to be individually listed in the INSERT statement. 4. If they are not listed, they are assumed to follow the default column order in the table and a value must be provided for each column.