Advanced SQL

What functions can be used with the OVER clause?

1. Aggregate functions: AVG() SUM() COUNT() MAX() MIN() 2. Window functions: ROW_NUMBER() RANK() DENSE_RANK()

DDL (Data Definition Language) category is composed of what commands?

1. CREATE 2. ALTER 3. DROP 4. RENAME 5. TRUNCATE 6. COMMENT

What are the four categories of SQL commands?

1. Data Definition Language (DDL) 2. Data Query Language (DQL) 3. Data Manipulation Language (DML) 4. Data Control Language (DCL)

How to restore a database from a BAK file? (Or, in some cases, install a new database like AdventureWorks)

1. Download AdventureWorks2017.bak 2. Open SSMS and connect to server 3. Right click Databases in Object Explorer 4. Select "Restore Database" 5. Under Source, select Device 6. Click Add and select the bak file 7. Click OK

DCL (Data Control Language) category is composed of what commands?

1. GRANT 2. REVOKE

DML (Data Manipulation Language) category is composed of what commands?

1. INSERT 2. UPDATE 3. DELETE 4. MERGE 5. CALL 6. EXPLAIN PLAN 7. LOCK TABLE

DQL (Data Query Language) category is composed of what commands?

1. SELECT

What is a synonym in SQL?

A synonym is a database object that: 1. Provides an alternative name for another database object (aka the base object) that can exist on a local or remote server. 2. Provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.

What is a trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be considered to be similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures. Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself; so when the trigger is fired because of data modification, it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

What does the ROW_NUMBER function do?

Assigns a sequential integer to each row of a result set / partition. When you use the ROW_NUMBER function, ranks are assigned sequentially, even when the result is the same. Instead of 1,2,2,2,5 where the second, third, and fourth result are the same, the result would be 1,2,3,4,5.

What is the syntax to drop a table?

DROP TABLE tablename;

What does the OVER clause do?

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.

Give an example of OVER and PARTITION BY in a query.

For example, obtaining the Gender Total and providing this total for each employee row. Note: tterm-29his is different from getting a total count of genders via GROUP BY, which would only return two rows/counts: one row for Female and one row for Male. SELECT Gender, COUNT(Gender) OVER(PARTITION BY Gender) GenderTotal FROM tblEmployees;

Provide an example of a SELF JOIN.

Given an employees table with an EmployeeID column and a ManagerID column, you could self join the table with itself on these two columns. SELECT * FROM Employees E LEFT JOIN Employees M ON E.ManagerID = M.ManagerID; This would pair an employee row from E with the employee's respective manager row from M (if applicable).

What does the ORDER BY clause in a SELECT statement do?

It determines the order in which the entire query result set is returned.

What does PARTITION BY clause do?

It divides the result set into partitions. Similar in the sense of GROUP BY, but it doesn't actually roll up / aggregate the rows.

What does the ORDER BY in the OVER clause do?

It orders the rows in each partition by the column specified.

What does the ISNULL function do?

It returns a specified value if the expression is NULL. Otherwise, it will return the expression. The expression can be a column or a hardcoded value. ISNULL(expression, value)

Provide an example of ROW_NUMBER(), OVER, and PARTITION BY.

Provide row numbers for each partition of customers by city: SELECT first_name, last_name, city, ROW_NUMBER() OVER(PARTITION BY city, ORDER BY first_name) as row_number FROM customers ORDER BY city;

What is the type of some of the most commonly used window functions?

Ranking functions. These include: ROW_NUMBER RANK DENSE_RANK NTILE

Provide an example of ISNULL usage.

SELECT E.name, ISNULL(M.name, 'No Manager') AS Manager FROM tblEmployeesSelfJoin E LEFT JOIN tblEmployeesSelfJoin M ON E.ManagerId = M.EmployeeId;

True or False: PARTITION BY divides the result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

True

True or false: dropping a database or table is irreversible.

True

True or false: dropping a table permanently erases all data from the table, as well as the metadata that defines the table in the data dictionary.

True

True or false: local temp tables are available in nested stores procedures.

True. A temp table defined in stored procedure A can be referenced by caller stored procedure B.

True or False: you can perform JOINs with different columns from the same table.

True. This would be a SELF JOIN.

How partitions will there be if we perform a (PARTITION BY Gender)?

Two: Male and Female.

What are window functions in SQL?

Window (or windowing or windowed) functions perform calculation over a set of rows.