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
