4420 Just to Pass

You're building an Employees table for the HR department. Among the columns in the table is StateCode, a CHAR(2) column to store the 2-character abbreviation of each employee's home state. The VP of HR pulls you aside and tells you a story about the last place she worked where they had bad data in the StateCode column and how it caused problems for reporting. She asks you to ensure the StateCode column cannot store any values aside from the 50 valid U.S. state codes. Mark any options that are valid ways to implement this business requirement. [ -Make StateCode a Foreign Key pointing to a Primary Key in a table called States that has 50 rows describing the states. -Appoint a data steward from the HR department to run a query against the -Employees table each morning that shows each state value and a count of how many rows are in the table with each value. -Make sure the application code is written to ensure only valid state codes are entered into the table by checking a hard-coded list in the program. -Add a CHECK constraint to the CREATE TABLE command that requires the StateCode column have one of the 50 valid state codes.]

-Make StateCode a Foreign Key pointing to a Primary Key in a table called States that has 50 rows describing the states. -Add a CHECK constraint to the CREATE TABLE command that requires the StateCode column have one of the 50 valid state codes.

Suppose you CROSS JOIN a table with 5 rows with a table that has 10 rows. How many rows will the result set have?

50 *Just multiply*

Choosing data types is a balance between selecting one that is large enough to store the values the column will have while minimizing the storage footprint of the database. Match the business requirement on the left with the appropriate data type on the right. [Gross Domestic Product (GDP) for the U.S.A. (hint: U.S.A GDP was 19.39 trillion in 2017).]

BIGINT

Transactions bring the database from a valid state to another valid state.

Consistency

The subquery runs once for each row in the outer dataset, which can cause performance to suffer.

Correlated Subqueries

Commands that are used to define (implement the structure of) the Schema of the database, including creating, altering, & dropping Schema of the database, including creating, altering, & dropping tables & other objects.

DDL

Which SQL subcategory items do these fall into (DDL, DML, TCL, or DCL): CREATE, ALTER, DROP

DDL

•Continuously-valued numeric data type. •Space consumed depends on the Precision specified. •Precision & Scale are specified in parenthesis.

DECIMAL Data Type

Commands used to manage the data in the database, such as SELECT (reading), INSERT, UPDATE, and DELETE operations.SELECT (re

DML

Which SQL subcategory items do these fall into (DDL, DML, TCL, or DCL): SELECT, INSERT

DML

INSERT, UPDATE, DELETE

DML Queries

Margo has just graduated from the U of Utah IS program and has been hired as a Jr. Database Developer building an app for elderly people to set reminders to take their medication. She created a table called Medication, and has just realized she has made some mistakes in the CREATE TABLE command. Margo wants to revert to the point where she was prior to creating the table so she can change the CREATE TABLE command to be correct and then re-create the table. What command should she use to remove the table?

DROP TABLE Medication;

The American National Standards Institute created the SQL standard ("ANSI-SQL") in 1989, and have updated it many times since then. The most recent update was in 2016. Mark the item that is not a good reason to adhere to the ANSI-SQL standard.

To try all the latest SQL features from a particular vendor.

______________is a Stored Procedure that is automatically invoked (triggered) by the occurrence of a given data manipulation event.

Trigger

A Stored Procedure is a block of SQL code (typically with multiple commands) given a name and stored in the database.

True

A Subquery is a query inside of another query.

True

Aggregations are computations on groups of rows vertically

True

Aliasing is good for making your queries less wordy, or to change the context of a column for a query.

True

CHECK constraints are used to apply custom constraints to tables or individual columns.

True

CROSS JOINs result in a Cartesian Product. Every row from the first data set is matched with every row in the other dataset. There is no match criteria.

True

CRUD is just a way of describing the operations software programs take to persist data changes over time.

True

Calculated columns are computations on individual rows horizontally.

True

Choosing data types is a balance between selecting one that is large enough to store the values the column will have while minimizing the storage footprint of the database.

True

Code written in an Abstract way maximizes potential for Code Reuse

True

Constraint names are shown in error messages and help with troubleshooting.

True

DDL focuses on the Structure [CREATE, ALTER, DROP, TRUNCATE, RENAME]

True

DML focuses on the Data [SELECT, INSERT, DELETE, UPDATE, CALL]

True

For Set Operators to work, each participating data set must be be Union Compatible.

True

INNER JOINs return rows where there is a match on both sides of the JOIN.

True

It's common to create Unique Indexes to enforce uniqueness on 1 or more columns in the table.

True

JOIN ... ON ... is the recommended syntax to use because it provides the most flexibility and control of the SQL logic.

True

JOINs combine datasets horizontally.

True

OUTER JOINs return rows from the primary data set and show values of matching rows from the other data set. If the other data set doesn't have a matching row, NULLs are shown for those columns in the result set.

True

Once you alias a table or column, you cannot use the original name from that point forward.

True

Running a DELETE without a WHERE clause will remove every row from the table!

True

SQL is the standard for Relational Database Management Systems.Systems

True

SQL supports Procedural functionality.

True

Set Operators conduct operations on datasets vertically.

True

Setting a surrogate primary key in SQL we use: PRIMARY KEY IDENTITY(1,1)

True

Subqueries help make SQL the dynamic & powerful language that it is. They are one of the most important aspects of SQL.

True

The ALTER command is used to add columns or constraints to a table after it has been created.

True

The COALESCE() function returns the first non-NULL argument.

True

The HAVING clause is used to filter row groups based on aggregate predicates.

True

The JOIN ... USING ... syntax is superior to NATURAL JOIN because it allows you to specify which columns to match on.

True

The WHERE clause uses Boolean Logic

True

To handle the NULLable columns in the SELECT query, we can use the COALESCE () function.

True

Use the AS keyword to alias tables & columns.

True

You should always use the same block of code to do the same thing.

True

_______stacks 2 or more datasets on top of each other

UNION

What is the difference between UNION and UNION ALL?

UNION removes duplicate rows, and UNION ALL leaves the duplicate rows in the result set.

is a stored block of SQL code that returns a scalar value to the calling process.

User Defined Functions

You are building a query for a report requested by the HR department. The want to see each Employee's full name, job title, and tenure at the company. You have concatenated these columns to create a column called FullName: FirstName (VARCHAR(25) NOT NULL) MiddleName (VARCHAR(25) DEFAULT NULL) LastName (VARCHAR(25) NOT NULL) When you run the query, you find that some rows are showing NULL for the concatenated FullName column. How should you fix this so the query shows the full name of each Employee?

You should use the COALESCE() function to place an empty string into the concatenate function when MiddleName is NULL.

What type of programming language is SQL?

Declarative & Procedural

Declarative Programming

Describes what must be accomplished rather than describe how to accomplish it.

The effects of committed transactions will remain even in the event of system failures.

Durability

Choose the item that is not correct about Union Compatibility.

Each data set must have the same number of rows.

Calculated columns are aggregations!

False

It is not common to CAST() data types of participating data sets to make them Union Compatible.

False

Subqueries can never replace JOINs

False

You can only join on Primary Key & Foreign Key columns.

False

Remember, you almost always use ORDER BY when aggregating in SQL.

False, GROUP BY

You can DROP a table with Foreign Keys pointing to it. There is no need to first drop or truncate the child table

False, must drop/truncate child table first

You're building a SELECT query that needs to show each Customer and how many Orders each Customer has placed. The query is shown below. SELECT CustomerID, COUNT( OrderID ) AS OrderCount FROM Orders; The query is returning an error. What clause is missing that would correct the problem if it was added to the query?

GROUP BY

You are writing a SELECT query and you need to filter rows out of the result set based on the result of an aggregate predicate. Which clause of the SELECT query should you put the predicate in? [HAVING, WHERE, ORDER BY, GROUP BY]

HAVING

Match the item on the left with the appropriate item on the right. -Joins combine data sets... -Set Operators combine data sets...

Horizontally Vertically

You're creating a table called Players to store information about sports players in an app you're building that will make you a filthy rich. You have decided to implement a Surrogate Primary Key. Which data type property should you use for the Surrogate Primary Key column?

IDENTITY(1,1)

Which join type may cause rows to be filtered from the result set? [RIGHT OUTER JOIN, INNER JOIN, FULL OUTER JOIN, LEFT OUTER JOIN]

INNER JOIN

Choose the command type that does not support a WHERE clause. [INSERT, UPDATE, SELECT, DELETE]

INSERT

Query used to move data from one table to another

INSERT

Choosing data types is a balance between selecting one that is large enough to store the values the column will have while minimizing the storage footprint of the database. Match the business requirement on the left with the appropriate data type on the right. [Summit elevations in feet of mountains around the world (hint: Mt. Everst is 29,029 feet above sea level).]

INT

________are separate data structures that are synchronized with tables they are built upon.

Indexes

Ensures the outcome of transactions are the same whether they run concurrently or sequentially.

Isolation

•The LIKE clause is used to match patterns in strings. •The % (percent) wildcard allows for any number of characters. •The _ (underscore) wildcard allows for 1 character.

LIKE

____________ is syntactic sugar because it allows you to write joins without specifying the match columns.

NATURAL JOIN

A value must be provided, or the INSERT will fail. FirstName INT ________________

NOT NULL

A numerical or other measurable factor forming one of a set that defines a system or sets the conditions of its operation.

Parameters

A question or inquiry

Query

Mark the keywords that are minimum required for a SELECT query to be ran. Choose 2. (HAVING, GROUP BY, WHERE, FROM, SELECT, ORDER BY)

SELECT, FROM

Choosing data types is a balance between selecting one that is large enough to store the values the column will have while minimizing the storage footprint of the database. Match the business requirement on the left with the appropriate data type on the right. [Temperature in farenheight around the world (hint: record coldest is -128.6, hottest is 134.1)]

SMALLINT

Which is not a reason to have a SQL standard? -Application longevity. -Reduced training costs. -Reduced dependence on a single vendor. -Seamless cross platforming capabilities -Portability of code between Relational Database Management Systems.

Seamless cross platforming capabilities

BETWEEN, IN, LIKE, IS NULL, EXISTS, DISTINCT

Special Operators

Runs once regardless of how many rows are in the outer dataset.

Stand-alone Subqueries

CHAR(N)• •Fixed-width Character data type. •The length of a CHAR column is fixed to the length that you declare(0 - 255) when you create the table. •When CHAR values are stored, they are right-padded with spaces to the specified length. VARCHAR(N)• •Variable-width Character data type.

String Data Types

Database containing the Metadata about the User Databases on the server.

System Catalog

Choose the item that is not a DML command. [DELETE, UPDATE, INSERT, TRUNCATE, SELECT]

TRUNCATE

DATE •'YYYY-MM-DD' Range is '1000-01-01' to '9999-12-31' DATETIME •'YYYY-MM-DD hh:mm:ss' •Range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' TIME •'hhh:mm:ss'

Temporal Data Types

Removing the details to focus on the general concept or task at hand.

Abstraction

COUNT(), MIN(), MAX(), SUM(), AVG()

Aggregate functions, used to aggregate data over groups of rows.

The difference between aggregations vs calculated columns is:

Aggregations are computations on groups of rows. Calculated columns are expressions between 2 or more columns in the same row.

Transactions are treated as a single atomic unit of work, which either succeeds completely or rolls back to the state prior to the transaction.

Atomicity