SQL collection 1
soft-state
state may change over time
What does the PRIMARY KEY do?
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Which of the following statements creates a database user in the current database from a TomBrown SQL Server login ID?
CREATE USER TomBrown;
What is CTE?
CTE is the abbreviation for Common Table Expression. A CTE is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
Which pseudocolumn can be used with a sequence to display the current value of that sequence?
CURRVAL
Why would you want to model a time component when designing a system that lets people buy bars of gold?
The price of gold fluctuates and, to determine the current price, you need to know the time of purchase
Which keyword can be used with the INSERT and UPDATE statement to set a column value to the value previously defined in a DEFAULT constraint?
DEFAULT
delete clause in merge statement
DELETE feature of the "update clause" within MERGE can be included. WHEN MATCHED THEN UPDATE SET 05 WWA.NOTES = ONT.SALES_REP 06 DELETE WHERE WWA.INV_DATE < TO_DATE('01-SEP-09')
Which keyword overrides the default sort order in an ORDER BY clause?
DESC
Example of Now Function Syntax
Example of Now Function Syntax SELECT NOW() FROM table_name;
A single-row subquery can return several columns, but only one row, of results to the outer query.
F
A unique identifier can only be made up of one attribute. True or False?
F
You can determine the number of characters in a column by using the COUNT function.
F
You must make sure all entities of a proposed system can fit onto one diagram. It is not allowed to break up a data model into more than one diagram
F
Which function adds or subtracts months from a given date?
"ADD_MONTHS. The syntax of the ADD_MONTHS function is ADD_MONTHS(date, n), where n is a positive or negative integer."
Which statement would you use to define a default value for an existing column?
"ALTER TABLE. You would also use the ALTER TABLE statement to add, modify, or drop columns."
Name three TCL statements.
"COMMIT, ROLLBACK, and SAVEPOINT"
Which character function is functionally equivalent to using the || operator?
"CONCAT. The syntax of the CONCAT function is CONCAT(column1|expression1, column2|expression2)"
novalidate
novalidate, u slučaju da enablujemo constraint ova opcija nam omogućava da "validira" postojeće vrijednosti u tabeli
The COALESCE function provides one way to substitute constant values for which values?
null
The GROUPING function lets you substitute another value for a/an ____________________ value when you use the ROLLUP or CUBE operator.
null
hashed sharding
hashed sharding uses the hash of a value to map to a particular chunk
ALL_objects (tables...)
information about all the tables accessible to teh user
he subquery used to create a(n) ____ view can contain an ORDER BY clause.
inline
Code example 4-1 SELECT VendorName AS Vendor, InvoiceDate AS Date FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID; (Refer to code example 4-1.) This type of join is called a/an
inner join
Which type of join joins a table to itself?
a self join
A union combines the rows from two or more what?
all of the above (SELECT statements, result tables, queries)
sign _
denotes one carachter
sign %
denotes one or many caracthers
"When joining more than two tables using an ON clause, in which order are the joins evaluated?"
from left to right
The CUBE operator is similar to the ROLLUP operator except that
it adds summary rows for every combination of groups
NOT EQUAL signs
!= or
default and datatype precedance
1. datatype 2. default 3. constraint
"If you have a five column list with the CUBE extension, how many combinations would be created in the result set?"
25 or 32 combinations
Each of the following column attributes is a column constraint, except
DEFAULT
Why the Trigger Fires Multiple Times in Single Login?
It happens because multiple SQL Server services are running and also as intellisense is turned on.
user_obJeCts1
The USER_OBJECTS view contains information about all objects owned by the user.
Non-Schema Objects
Users Roles Public Synonyms
The processing that's done by the DBMS is typically referred to as
back-end processing
USER_IND_COLUMNS
kolekcija koja daje informacije o indexima na koloni
A(n) ____ is used to combine the results of two queries
set operator
when the column can be modified
table page 429
A relational database consists of one or more what?
tables
What's the name of the system database that stores temporary tables?
tempdb
if create the savepoint with the same name
than the first one is deleted
If ZipCode is a varchar column that contains the value 93702, what will the Solution column evaludate to? ISNUMERIC(ZipCode) AS Solution
true
In a/an ________________, a table can contain information about two or more entities.
unnormalized data structure
redis operations
-range -diff -union -intersection
[:alpha:]
Alphabetic characters [a-zA-Z]
An entity can only have one UID. True or False?
F
All of the following would be instances of the entity PERSON except which?
Male
Why does the SELECT TOP clause be useful on large tables?
Performance - Returning a large number of records can impact on performance.
Which of the following statements returns the value of a variable named @InvoiceCount?
RETURN @InvoiceCount;
revoke object privileges
REVOKE privilege ON object FROM user
DISTINCT syntax
SELECT DISTINCT column_name,column_name FROM table_name;
A table alias is assigned in the FROM clause.
T
What are the four group functions that can only be used with numeric datatypes?
"SUM, AVG, VARIANCE, and STDDEV"
"If you fail to name a constraint, what will Oracle name the constraint?"
"SYS_Cn, where n is an integer making the constraint name unique in that schema"
Which three functions allow you to perform explicit datatype conversions?
"TO_CHAR, TO_DATE, and TO_NUMBER"
Non-equijoins
connect data by looking for relationships that don't involve equality, such as "less than" or "greater than" relationships, or situations where data in one table is within a range of values in another.
Rules enforced at the table level concerning acceptable data which can be entered into a table are called what?
constraints
neo4j scalability
harder to move to separate servers -most operations involve many nodes -master slave replication may be an option
5. Cartesian product
join condition is ommited or invalid; all rows in the first table is joined to all rows in second table
cascade constraints
kod drop table ne dozvoljava da brišemo tabelu ako postoji refferial integrity, ovom opcijom brišemo foreign key gdje je potrebno
How many PRIMARY KEY constraints can be defined on a table?
only one
"When testing each row in the emp table to determine whether any employee doesn't have an email, how should the WHERE clause be written if you want the information about those employees who don't have an email to be displayed?"
the IS NULL comparison operator
Which clause of the ALTER TABLE statement would you use to add a NOT NULL constraint to an existing table?
the MODIFY clause
Which statement do you use to assign a privilege to a user or role?
the GRANT statement
When coding a definition for a column that will contain a high percentage of null values, what attribute can you use to optimize the storage?
the SPARSE attribute
Where would you go to create, modify, or delete logins using the Management Studio?
the Security folder for the server
"Which metacharacter, when used in a SQL regular expression, represents a match to any character in the database character set?"
the period (.)
Which of the following is a valid SQL statement to retrieve data from the ORDERS table?
SELECT order#, orderdate, shipdate FROM orders;
Which of the following is not a valid column alias name?
Total Sales
What is the difference between UNION and UNION ALL?
The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.
Can you add SQL functions to a view?
Yes, You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
Can you have multiple Schemas in a Database?
Yes, You may have one or multiple schemas in a database.
What do you use to uniquely identify each row in a table?
a primary key
A subquery that returns a single row of data is known as which type of subquery?
a single-row subquery
The <> (not equal) comparison operator can only be used with which type of subquery?
a single-row subquery
Check constraints you create using DDL can be defined at the
both a and b (table level, column level)
"If a composite primary key is required, the PRIMARY KEY constraint must be defined at what level?"
the table level
What are the Different Index Configurations a Table can have?
A table can have one of the following index configurations: No indexes A clustered index A clustered index and many non-clustered indexes A non-clustered index Many non-clustered indexes
What is a table?
A table is a collection of related data entries and it consists of columns and rows.
Each of the following statements about triggers is true except for one. Which one?
A trigger can have more than one batch.
What are the first two words in the command used to disable a constraint on the column called id on the emp table?
ALTER TABLE
Dropping Columns
ALTER TABLE ORDER_RETURNS DROP COLUMN CRUISE_ORDER_DATE; ALTER TABLE ORDER_RETURNS DROP (CRUISE_ORDER_DATE);
If the DISTINCT keyword is not included in the STDDEV function, the ____ keyword will be assumed.
AVG SUM NVL
When you add the concept of time to your data model, you are:
Adding complexity to your model.
What is the rule of Second Normal Form?
All non-UID attributes must be dependent upon the entire UID.
DBA_
All objects in the database
implcit rollback
An implicit rollback occurs when a program abnormally terminates. In other words, uncommitted changes at the time of an abnormal termination of, for example, SQL*Plus or SQL Developer will not be committed to the database.
Which character function returns an occurrence of one string of characters within a column value or expression?
"INSTR. The syntax of the INSTR function is INSTR(column|expression, 'string' [, m] [, n])"
"When using Oracle proprietary join syntax, what operator would you use in a WHERE clause to produce an outer join?"
(+)
To concatenate character strings in a string expression, which operator do you use?
+
redis commands for keys
-DEL key -EXISTS key -KEYS pattern -MOVE key db -TYPE key
What is a FOREIGN KEY?
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Which statement is true about the DENY and REVOKE statements?
A denied permission can't be granted by role membership, but a revoked permission can
What does a LEFT JOIN return?
A left join returns all rows from the left table, and the matched rows from the right table;
priority names
A local user account contains a single namespace for the tables, views, sequences, and private synonyms it owns. you can create a public synonym with the same name as an existing user account synonym
What is Junk Dimension?
A number of very small dimensions may get lumped together to form a single dimension, i.e. a junk dimension - the attributes are not closely related. Grouping of Random flags and text Attributes in a dimension and moving them to a separate sub dimension is known as junk dimension.
Which two categories of statements cause an autocommit to occur?
DCL and DDL statements
When sorting the results in descending order, which of the following values will be presented first in the output?
NULL
What do Null Values represent?
NULL values represent missing unknown data. By default, a table column can hold NULL values
Which operator compares a value to a specific list of values?
the IN operator
Which one of the regular expression functions returns the substring value that matches a given pattern in a given string?
REGEXP_SUBSTR
"Which statement would you use to change the name of a table, view, sequence, or synonym?"
RENAME. The syntax is RENAME old_name TO new_name;
You can use the Object Dependencies dialog box of the Management Studio to do all but one of the following. Which one is it?
Review the stored procedures and views that a specific table depends on
What is SQL?
SQL stands for Structured Query Language. SQL lets you access and manipulate databases.
A(n) UPDATE operation can be performed on a complex view that contains an arithmetic operation.
T
Which group function would you use to determine the number of rows that meet a certain condition?
COUNT(*)
create role;
CREATE ROLE manager; GRANT create table TO manager; GRANT manager TO Dehhan
If the DISTINCT keyword is not included in the SUM function, the ____ keyword will be assumed.
UNIQUE RANDOM
Unique indexes are automatically created on columns that have which two types of constraints?
UNIQUE and PRIMARY KEY
Can you use an LIKE as a conditional selection for the SELECT clause?
Yes, you can use an LIKE as a conditional selection for the SELECT clause
Each of the following is a benefit provided by using views except for one. Which one?
You can create a view that simplifies data insertion by hiding a complex INSERT statement within the view.
"List the values that would match the following regular expression: ['abc']{3,5}"
abcabcabc or abcabcabcabc or abcabcabcabcabc
set unused column syntax
alter table set
A user-defined function
can return a single scalar value or a single table value
Expressions coded in the HAVING clause
can use either aggregate search conditions or non-aggregate search conditions
Expressions coded in the WHERE clause
can use non-aggregate search conditions but can't use aggregate search conditions
The SELECT statement for a view
can use the ORDER BY clause if it also uses the TOP clause
To run a SELECT statement from an application program, you store the statement in the ________________ object for the database connection.
command
What character should separate columns in the SELECT list?
commas
A view is a SELECT statement that is stored with the ________________.
database
A SQL Server database consists of two files: a database file and a ___________.
log file
A multiple-row operator expects how many values?
one or more
"When modifying a sequence, which sequence numbers are affected?"
only future sequence numbers
What uniquely identifies each row in a table?
primary key
To apply the second normal form, you move columns that don't depend on the entire primary key to another table and establish a relationship between the two tables. This
reduces redundancy and makes maintenance easier
REGEXP_SUBSTR
returns the substring matching the regular expression pattern that you specify. The following function uses the x flag to match the first string by ignoring spaces in the regular expression: REGEXP_SUBSTR('oracle', 'o r a c l e', 1, 1, 'x')
Whenever you use the Management Studio to create, alter, or delete database objects, you can save the ________________ that it used for doing that.
script
An at sign (@) at the beginning of an identifier indicates
that the identifier is a local variable or parameter
"When a GROUP BY clause contains multiple columns, which grouping is the most major grouping?"
the first column listed in the GROUP BY clause
To how many levels can group functions be nested?
two
This diagram could also be expressed as a supertype/subtype construction. True or False?
False
Where in MS SQL Server is '100' equal to '0'?
Fill-factor settings of 0 and 100 are equal!
A subquery can be coded in a WHERE, FROM, SELECT, or ______________ clause.
HAVING
Which set operator returns only the results of the first query that are not in the second query?
MINUS
Based upon the structure of the ORDERS table, which of the following is a valid SQL statement?
SELECT order#, orderdate DateOrdered FROM orders;
null value in calculation
2 x null = null, null is not the same as 0
examples of column-family stores
-Cassandra -HBase
An outer query is also referred to as a(n) ____ query.
parent query
What type or category of commands is MERGE?
DML
Parentheses can be used to override the order of operations in an arithmetic expression
T
the default optino when creating the table
...hire_date DATE SYSDATE...
"Evaluating MOD(20,3) returns what value?"
2
Relationships always exist between
2 entities (or one entity and itself)
What is the default number of decimal positions stored in a timestamp datatype?
6
What is a Intersect?
A Intersect is the Same as INNER JOIN
\n
Backreference Matches the nth preceding subexpression, where n is an integer from 1 to 9.
CREATE TABLE
Create a table in your user account. Includes ability to ALTER and DROP TABLE. Also includes ability to CREATE, ALTER, and DROP INDEX objects.
confirming granted privileges
Data Dictionary View Description ROLE_SYS_PRIVS System privileges granted to roles ROLE_TAB_PRIVS Table privileges granted to roles USER_ROLE_PRIVS Roles accessible by the user USER_TAB_PRIVS_MADE Object privileges granted on the user's objects USER_TAB_PRIVS_RECD Object privileges granted to the user USER_COL_PRIVS_MADE Object privileges granted on the columns of the user's objects USER_COL_PRIVS_RECD Object privileges granted to the user on specific columns USER_SYS_PRIVS Lists system privileges granted to the user
What is Real-Time Data-Warehousing?
Data warehousing captures business activity data. Real-time data warehousing captures business activity data as it occurs. As soon as the business activity is complete and there is data about it, the completed activity data flows into the data warehouse and becomes available instantly.
How many primary keys can be in any given table?
Either 0 or 1
"If you have two WHEN conditions in an INSERT statement, what keyword will ensure the insertion is completed after evaluating only one WHEN condition?"
FIRST
What happens if you define a CHECK constraint on a table?
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
What is Difference between GETDATE and SYSDATETIME in SQL Server 2008?
In case of GETDATE, the precision is till milliseconds, and in case of SYSDATETIME, the precision is till nanoseconds.
What does the CEILING(x), CEIL(x) return?
Returns the smallest integer value that is greater than or equal to x
Data Type: Date
Stores year, month, and day values date
Data Type: Timestampe
Stores year, month, day, hour, minute, and second values timestamp
How is the type DATE in SQL structured?
The type DATE is formatted like: YYYY-MM-DD
In a Schema what is a Database Username
This username is called the schema owner, or the owner of the related group of objects.
How would you add a column in a table with ALTER?
To add a column in a table, use the following syntax: ALTER TABLE table_name ADD column_name datatype
Which datatype holds variable-length character data?
VARCHAR2
WITH Clause:
WITH dept_costs AS ( SELECT department_name, SUM(salary) AS dept_total FROM employees, departments WHERE employees.department_id = departments.department_id GROUP BY department_name), avg_cost AS (SELECT SUM(dept_total)/COUNT(*) AS dept_avg FROM ) SELECT * FROM dept_costs WHERE dept_total > (SELECT FROM dept_avg) ORDER BY department_name;
How to Find Out the List Schema Name and Table Name for the Database?
We can use following script: SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable FROM sys.tables
?
Zero or One -- Question Mark Quantifier Matches zero or one occurrence of the preceding subexpression
What is produced when a join condition is not specified in a multiple-table query?
a Cartesian product or cross join
Which type of outer join includes all matched rows and all unmatched rows in the second table listed?
a right outer join
If introduced as follows, the subquery can return which of the values listed below? SELECT (subquery)
a single value
When you use weekday with the DATEPART function, it returns an integer that indicates the day of the week where
1=Sunday, 2=Monday, etc.
Which group function calculates the average of a group of values?
AVG
Which functions perform a calculation on the values of a column from selected rows?
Aggregate
CREATE USER
CREATE USER user IDENTIFIED BY password
CREATE DIRECTORY
CREATE [OR REPLACE] DIRECTORY AS 'path_name'
To sort the records that are retrieved by a SELECT statement in descending sequence what keyword do you code at the end of the ORDER BY clause?
DESC (For some reason my professor's key says DISTINCT, but it's most certainly DESC...)
Which keyword when used in a SELECT clause returns only the unique values or combination of values for the column(s) that follow?
DISTINCT
What is a Dimension Table?
Dimensional table contains textual attributes of measurements stored in the facts tables. Dimensional table is a collection of hierarchies, categories and logic which can be used for user to traverse in hierarchical nodes.
To test whether one or more rows are returned by a subquery, you can use which operator?
EXISTS
What is the syntax to return just the month portion of the values stored in column hire_date?
EXTRACT (MONTH FROM hire_date)
The following entity is in 1st normal form: True or False? ENTITY: VEHICLE ATTRIBUTES: REGISTRATION MAKE MODEL COLOR DRIVER PASSENGER 1 PASSENGER 2 PASSENGER 3
F
DML statements
INSERT,UPDATE, DELETE, SELECT, and MERGE
What will be the result of a LEFT JOIN if the ON clause doesn't match any records in the right table?
If the ON clause doesn't match any records in the right table, the LEFT JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table.
automatically creating the index
If you create a constraint on a table that is of type PRIMARY KEY or UNIQUE, then as part of the creation of the constraint, SQL will automatically create an index to support that constraint on the column or columns,
What are the INDEXES used for?
Indexes allow the database application to find data fast; without reading the whole table.
Write an aggregate expression to find the VendorName column that's last in alphabetical order
MAX(VendorName)
MERGE statement
MERGE into table1 AS USING table2 AS ON (join condition) WHEN MATCHED THEN UPDATE SET col1=col_val1 WHEN NOT MATCHED INSERT (column_list) VALUES (columen_values)
What is the default mode for the CREATE VIEW command?
NOFORCE
Arithmetic expressions that include a NULL value always evaluate to what value?
NULL
Is the update time of a table with indexes the same as the update time without indexes?
No, the update time is not the same, Updating a table with indexes takes more time than updating a table without (because the indexes also need an update).
Can you INSERT / UPDATE a new record on a NOT NULL without adding a value to this field?
No, this is the whole point of NOT NULL, This means that you cannot insert a new record, or update a record without adding a value to this field.
If any Stored Procedure is Encrypted, then can we see its definition in Activity Monitor?
No, we can't see definition of encrypted stored procedure in Activity Monitor.
CHECKPOINT option
On large tables you can reduce the amount of undo logs accumulated by using the CHECKPOINT option which forces a checkpoint after the specified number of rows have been processed.
How to Implement One-to-One, One-to-Many and Many-to-Many Relationships while Designing Tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
[=character=]
POSIX Character Equivalence Class Match characters having the same base character as the character you specify
What are the Properties and Different Types of Sub-Queries?
Properties of a Sub-Query A sub-query must be enclosed in the parenthesis. A sub-query must be put on the right hand of the comparison operator, and A sub-query cannot contain an ORDER BY clause, however sub-query can use ORDER BY when used with TOP clause. Read Comment by David Bridge A query can contain more than one sub-query. Types of Sub-query Single-row sub-query, where the sub-query returns only one row. Multiple-row sub-query, where the sub-query returns multiple rows, and Multiple column sub-query, where the sub-query returns multiple columns
Modeling historical data produces efficient ways for a business to operate such as:
Providing valuable information via reports to management.
Which one of the regular expression functions returns a Boolean value?
REGEXP_LIKE
A subquery is a/an ______________ statement that's coded within another SQL statement.
SELECT
querying data before delete period
SELECT * FROM CHAT AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '0 0:01:30' DAY TO SECOND;
Which Command using Query Analyzer will give you the Version of SQL Server and Operating System?
SELECT SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('ProductVersion') AS ProductVersion GO
Based on the contents of the BOOKS table, which of the following SQL statements will return the total profit generated by books provided by publisher 4?
SELECT SUM(retail-cost) FROM books WHERE pubid = 4;
The ____ function is based upon the concept of a normal distribution.
STDDEV
Which group function calculates a standard deviation for a group of values?
STDDEV
EXTRACT2
Syntax: EXTRACT( fm FROM e) Parameters: fm is a format model element from Table 6-8 (required); e is a timestamp expression. Process: Extracts the value indicated by fm from e, where fm is one of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_ABBR; and e is an expression representing a datetime datatype. Output: Character if you extract TIMEZONE_REGION or TIMEZO
Which datatype includes a time zone displacement value that allows Oracle to return data to the client in the user's local session time zone?
TIMESTAMP WITH LOCAL TIME ZONE
When coded within a SELECT clause, which TOP clause will return a result set consisting of the ten largest InvoiceNumbers?
TOP 10 InvoiceNumber
"When using the IS NULL operator, which Boolean value is returned when a NULL value exists?"
TRUE
user_CAtALoG
The USER_CATALOG view displays a summary listing of tables, views, synonyms, and sequences owned by the user
What is a Data type: varchar(size)
Variable-length character string. Max size is specified in parenthesis.
Is a FULL JOIN the same as FULL OUTER JOIN?
Yes, a FULL JOIN is the same as FULL OUTER JOIN
Does SQL Dates come already with some predefined types for defining and storing a date or a date / time value?
Yes, there are some already predefined date types.
Can you use an = equal as a conditional selection for the SELECT clause?
Yes, you can use an = equal as a conditional selection for the SELECT clause
Which SELECT statement clause allows you to limit which groups are displayed?
a HAVING clause
Which SELECT statement clause can be used with the JOIN keyword to perform a natural join but limit the columns for the join condition?
a USING clause
Which SELECT statement clause creates an equijoin by specifying a column name common to both tables?
a USING clause
"In a SELECT statement, subqueries are often used in which clause to return values for an unknown conditional value?"
a WHERE clause
If you define a column as an identity column,
a number is generated for that column whenever a row is added to the table
An index improves performance when SQL Server ______________________.
searches a table
Which of the following types of joins refers to joining a table to itself?
self-join
The highest level at which you can grant permissions is the ________________ level.
server
UNDO_SQL column shows
the necessary SQL code that will undo the transaction
If you want to prevent users from examining the SQL code that defines a procedure, function, or trigger, you code the CREATE statement with the ________________ option
ENCRYPTION
What is ER Diagram?
Entity Relationship (ER) Diagrams are a major data modeling tool and will help organize the data in your project into entities and define the relationships between the entities. This process has enabled the analyst to produce a good database structure so that the data can be stored and retrieved in a most efficient manner. An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database. A type of diagram used in data modeling for relational data bases. These diagrams show the structure of each table and the links between tables.
equijoins
Equijoins connect data in two or more tables by looking for common data among the tables' columns.
\
Escape Character Treat the subsequent metacharacter in the expression as a literal.
Which of the following is a TRUE statement about the diagram below?
Every A is a B
Example of CREATE INDEX syntax:
Example of CREATE INDEX syntax: CREATE INDEX index_name ON table_name (column_name)
Example of FULL OUTER JOIN syntax:
Example of FULL OUTER JOIN syntax: SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;
Example of First Function Syntax:
Example of First Function Syntax: SELECT FIRST(column_name) FROM table_name;
Example of HAVING clause, GROUP BY syntax
Example of HAVING clause, GROUP BY syntax SELECT column1, SUM(column2) FROM "list-of-tables" GROUP BY "column-list" HAVING "condition";
Example of INNER JOIN keyword:
Example of INNER JOIN keyword: SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name; SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name=table2.column_name;
Example of LAST Function Syntax
Example of LAST Function Syntax SELECT LAST(column_name) FROM table_name;
Example of LCASE syntax:
Example of LCASE syntax: SELECT LOWER(column_name) FROM table_name;
Example of NOT NULL Syntax:
Example of NOT NULL Syntax: CREATE TABLE PersonsNotNull ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
Example of NULL syntax:
Example of NULL syntax: SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL
Example of OR operator
Example of OR Syntax: SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');
Example of RIGHT JOIN syntax:
Example of RIGHT JOIN syntax: SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;
Example of Round Function Syntax:
Example of Round Function Syntax: SELECT ROUND(column_name,decimals) FROM table_name;
Example of UDPATE VIEW syntax:
Example of UDPATE VIEW syntax: CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
Example of a PRIMARY KEY syntax:
Example of a PRIMARY KEY syntax: CREATE TABLE Persons ( P_Id int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
Example of a SELECT statement syntax
Example of a SELECT statement syntax select "column1" [,"column2",etc] from "tablename" [where "condition"]; [] = optional
If ROLLBACK is issued without naming a SAVEPOINT,
If ROLLBACK is issued without naming a SAVEPOINT, changes made by the user during the current session are rolled back to the most recent commit event.
If a DDL statement fails during execution
If a DDL statement fails during execution, the implicit commit that preceded it still is in effect, ensuring that the commit at least occurred, whether the DDL statement was successful or not. The same is not true for syntax errors.
What if a column in a table is Optional?
If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.
droping columne limitations
If a column is referenced by a foreign key constraint in another table, then the preceding syntax will trigger a warning message and the attempt to drop the column will fail.
"By default, how is the output of the UNION set operator sorted?"
It is sorted ascending in the order of the columns in the first SELECT list.
Which option of the CREATESEQUENCE or ALTERSEQUENCE statement indicates the largest value that the sequence can generate?
MAXVALUE n
What command will insert rows into a table if they don't already exist and will update rows in that same table if they do already exist?
MERGE
Which option can you use to make SQL Server prompt the user for a new password the first time the login ID is used?
MUST_CHANGE
When resolving a M:M relationship, the new relationships will always be __________ on the many side.
Mandatory
Which statement ends the current transaction by disregarding all pending changes?
ROLLBACK
What is Difference between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE?
ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE: Specifies whether to roll back after a specified number of seconds or immediately if transaction is not complete. NO_WAIT: Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, then the request will fail.
mongo replication
a replica set is a set of mongodb servers maintaining the same data set, divided into primary and secondary servers
priority names (private or public synoniyms)
The priority of referencing objects goes first to local objects.
What is the name for a SELECT statement that is embedded in another SELECT statement?
a subquery
What would you use in an INSERT statement instead of a VALUES clause to copy rows from one table to another?
a subquery
If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery)
a subquery can't be introduced in this way
A subquery that's used in a WHERE or HAVING clause is called what?
a subquery search condition
Which database object is an alternate name for a table or view?
a synonym
A WHERE clause condition that uses < ALL will return all records where the value is ______.
a value less than the minimum returned by the subquery
eventual consistency
system becomes consistent over time
Which function returns the first non-null expression in a list?
the COALESCE function
Outer join conditions CANNOT use which logical operator?
the IN logical operator
schema vs user accounts
the user account houses the objects owned by a user, and the schema is that set of objects housed therein
In many cases, a subquery can be restated as a/an ______________.
join
( ... )
Subexpression or Grouping Treat expression ... as a unit. The subexpression can be a string of literals or a complex expression containing operators
What is the ALTER statement used for?
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
gruping sets
The reserved words GROUPING SETS must follow GROUP BY. n A pair of parentheses follows GROUPING SETS. n Enclosed in the parentheses are a series of lists, each of which specifies one or more groups. These lists are each separated by commas, and all enclosed in parentheses. n Each set specifies separate GROUP BY clause groups.
excluding the branch
connect by manager_id=prior employee_id and manager_id<>124
concatenate sign II
contatenate two or more columns first_name II last_name
"What are two possible literal names that, if assigned to the variable my_name in the following expression, would return a Boolean TRUE?"
"Robert, Bob"
NVL function
- converts a null to actual value - data types can be number, date, char - data type must match e.g. NVL (date, '05-03-97') NVL(text, 'No job') NVL (NUMBER, 56)
What is T-SQL Script to Take Database Offline - Take Database Online?
-- Take the Database Offline ALTER DATABASE [myDB] SET OFFLINE WITH ROLLBACK IMMEDIATE GO -- Take the Database Online ALTER DATABASE [myDB] SET ONLINE GO
MongoDB flexibility
-more flexible than an RDBMS on attributes -different objects in the same collection can have different key-value pairs -fields can have different lengths
NoSQL query types
-no complex queries -no SQL support -CRUD operations via API (create, read, update, delete)
REDIS
-one implementation of a key-value NoSQL DB -it can be thought of as both a data store and a cache -designed for performance -Keys in Redis are strings
failures; what if the primary node goes down?
-secondary nodes notice the lack of heartbeat -secondary nodes elect a new primary -other secondaries now replicate based on that server's oplog -if primary comes back online, its now a secondary node NOTE: this can lose data!!
What two values can the GROUPING function return if the argument is a single column?
0 or 1
"If today is March 12, 2012, at exactly 12 o'clock noon, what will be the result of executing the statement SELECT sysdate - to_date('12-Mar-2012') FROM dual;?"
0.5
What is the Maximum Number of Index per Table?
1 Clustered Index + 249 Nonclustered Index = 250 Index. For SQL Server 2008: 1 Clustered Index + 999 Nonclustered Index = 1000 Index.
When you resolve a M:M by creating an intersection entity, this new entity will always inherit
A relationship to each entity from the original M:M.
Data Type: Aray
A set-length and ordered collection of elements
What is a Subquery?
A subquery is a SQL query nested inside a larger query.
Which of the following format argument elements indicates that the name of the day of the week for the specified date should be displayed?
DAY
"When using the TO_CHAR function to format dates, which format model element will display the numeric day number of the month?"
DD or dd
SQL statements that define the tables in a database are referred to as ________________ statements.
Data Definition Language (DDL)
What is Data Mining?
Data Mining is the process of analyzing data from different perspectives and summarizing it into useful information.
Besides UNION needing to have the same number of columns is there anything else that needs to be similar?
Data TYPES, and Order Yes, The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
Example of creating a table Syntax:
Example of creating a table Syntax: CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... );
Example of mid Syntax:
Example of mid Syntax: SELECT MID(column_name,start,length) AS some_name FROM table_name;
NoSQL databases Summary
NoSQL dbs generally offer: -easier horizontal scaling than relational DBs -perform better for some queries by not needing BUT -often lose some consistency or availability -they may scale well for read-heavy workloads but not so well for work-heavy ones -they often dont support SQL
What is a Data type: number(size)
Number value with a max number of column digits specified in parenthesis.
index (where it can't be used)
SQL can use the INDEX object to speed up querying of WHERE and ORDER BY clauses
SQL Functions
SQL has many built-in functions for performing calculations on data. Aggregate functions and Scalar Functions.
Which of the following is true about supertypes and subtypes?
Subtypes inherit the relationships and attributes of the supertype (*)
As a database designer, you have a responsibility to store data in only one place and in the best possible place. True or False?
T
Attributes become columns in a database table. True or False?
T
What is B-Tree?
The database server uses a B-tree structure to organize index information. B-Tree generally has following types of index pages or nodes: Root node: A root node contains node pointers to only one branch node. Branch nodes: A branch node contains pointers to leaf nodes or other branch nodes, which can be two or more. Leaf nodes: A leaf node contains index items and horizontal pointers to other leaf nodes, which can be many.
Where will the DEFAULT constrain add the values?
The default value will be added to all new records, if no other value is specified.
"If the same column name appears in more than one table in a join condition, by what must the column name be prefixed?"
by the table name or a table alias
What element of the SELECT * FROM emp WHERE salary > ( SELECT 2 * MAX(bonus_amt) FROM bonus); query assures that the subquery will return exactly one value?
The group function MAX in the subquery.
Code example 6-2 WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) When this query is executed, the result table will contain one row for
each vendor in the Top10 table
Which identifier can't be used in a SQL statement?
email addresses
Updates are not allowed through a view if the view definition includes columns that are defined by what?
expressions
Users who are involved in the administration of the server are typically assigned to one of the ________________ roles that are built into SQL Server.
fixed server
"When sorting by multiple columns in an ORDER BY clause, in what sequence are the sorts performed?"
from left to right
What is the XML Datatype?
The xml data type lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that has a missing single top-level element. You can create columns and variables of the xml type and store XML instances in them. The xml data type and associated methods help integrate XML into the relational framework of SQL Server.
When you can't create index (datatypes)
You cannot create an index on columns of LOB or RAW datatypes
What type of subquery returns more than one row of data?
a multiple-row subquery
implicit commit
implicit commit occurs immediately before and after certain events that take place in the database, such as the execution of any valid DDL statement, such as CREATE, ALTER, DROP, GRANT, and REVOKE. Each is preceded and followed by an implicit commit
"When a GROUP BY clause and no ORDER BY clause is used, in what order does the Oracle server implicitly sort the results?"
in ascending order by the first grouping column
Can subqueries contain group functions?
Yes
Is it possible for the main query to contain a greater than sign in its WHERE clause if the subquery returns only one value?
Yes
Can a column contain duplicate Values?
Yes a column can contain Duplicate Values
Does a PRIMARY KEY Constraint automatically has a UNIQUE constraint?
Yes, A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
A user who's granted the EXECUTE object permission can execute what?
a stored procedure or function
A correlated subquery is one that
is executed once for each row in the outer query
Oracle Flashback Technology
let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.
Which of the following is not a reason for using the explicit syntax instead of the implicit syntax for inner joins? The explicit syntax
lets you combine the join and seearch conditions
Which of the following statements about the SPARSE attribute is true?
all of the above (optimizes the storage of null values for a column requires more overhead to retrieve non-null values you should only use it when a column contains a high percentage of null values)
Which SELECT statement clause would you use to override the implicit sorting of a GROUP BY clause?
an ORDER BY clause
Valid column and table names should begin with what?
an alphabetic character
What could you include in a SELECT clause to select all of the columns in a table?
an asterisk (*)
If you have the files for an existing SQL Server database, the easiest way to create the database is to ________________ those files to the database server.
attach
TO_TIMESTAMP_TZ
converts a string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of TIMESTAMP WITH TIME ZONE data type
TO_TIMESTAMP
converts a string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of TIMESTAMP data type
FROM_TZ
converts a time stamp value to a TIMESTAMP WITH TIME ZONE value
function HEXTORAW
converts character data containing hexadecimal notation back into the RAW datatype.
Before you can delete a server role, you must
delete all of its members
ALL_OBJECTS
describes all objects accessible to the current user.
What would you use to force the order of evaluation of set operators in a compound query?
parentheses
The ranking functions make it easy to include a column in a result set that provides the sequential ranking number of each row within a ___________________________.
partition
Stored procedures execute faster than an equivalent SQL script because stored procedures are what?
precompiled
ranged sharding
ranged sharding divides the range of possible values among each server
references or reference in foreign key
references
CURRENT_TIMESTAMP function
returns the current date and time in the session time zone, as a value of the data type TIMESTAMP WITH TIME ZONE
If you define a column with a default value, that value is used whenever a row
that doesn't include a value for that column is added to the table
When you identify the data elements in a new database, you typically subdivide data elements into
the smallest practical components
"When a column is dropped using the DROPCOLUMN clause of the ALTER TABLE statement, which columns are physically dropped?"
the specified column and any other columns of that table that have been marked as UNUSED are dropped
mofifying columns rules
you cannot modify a column to take on properties that conflict with any existing data that is already present in the column
If you delete a stored procedure, function, or trigger and then create it again
you delete the security permissions assigned to the object
What is CHECK Constraint?
A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
What does a FULL JOIN return?
A FULL JOIN Returns all rows for which there is a match in EITHER of the tables.
What is a Cursor?
A cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. In order to work with a cursor, we need to perform some steps in the following order: Declare cursor Open cursor Fetch row from the cursor Process fetched row Close cursor Deallocate cursor (Read more here)
What are Lookup Tables?
A lookup table is the table placed on the target table based upon the primary key of the target; it just updates the table by allowing only modified (new or updated) records based on the lookup condition.
{m,}
Interval--At Least Count Matches at least m occurrences of the preceding subexpression
{m}
Interval--Exact Count Matches exactlym occurrences of the preceding subexpression
Which function returns the string with any leading spaces removed?
LTRIM
Are SQL queries case sensitive?
SQL keywords are NOT case sensitive
The purpose of an ERD is to document the proposed system and facilitate discussion and understanding of the requirements captured by the developer. True or False?
T
"In nested functions, which pairs of parentheses are evaluated first?"
The innermost pair of parentheses will be evaluated first.
naming objects (rules)
The length of the name must be at least one character, and no more than 30 characters. - The first character in a name must be a letter. - After the first letter, names may include letters, numbers, the dollar sign ($), the underscore (_), and the pound sign (#), also known as the hash mark or hash symbol. No other special characters are allowed anywhere in the name. - Names cannot be reserved words that are set aside for use in SQL statements, such as the reserved words SELECT, CREATE, etc
Date Functions
The set of available SQL functions includes powerful features for DATE manipulation. You can obtain the current date and time (SYSDATE, SYSTIMESTAMP), "round" off dates to varying degrees of detail (ROUND), and otherwise abbreviate them (TRUNC). You can calculate the differences between two or more dates in many ways.
How can a subquery make reference to a table in the main query?
Through the use of a table alias.
How do you Load the Time Dimension?
Time dimensions are usually loaded by a program that loops through all possible dates that may appear in the data. 100 years may be represented in a time dimension, with one row per day.
Which clause in a MERGE statement precedes the INSERT statement?
WHEN NOT MATCHED THEN
If you want to filter the result set that's returned by a SELECT statement, you must include which clause?
WHERE
Which of the following clauses is used to restrict the rows returned by a query?
WHERE
Your code will be easier to read if you code the join condition in the ON expression, and the search conditions in the which clause?
WHERE
WHERE clause syntax:
WHERE Syntax: SELECT column_name,column_name FROM table_name WHERE column_name operator value;
You can create a database diagram for
any combination of the tables in a database
In what order are nested functions evaluated?
from the innermost function to the outermost function
primary key generates
index system-named
NoSQL joins
no join support
An entity can be on 2nd Normal Form even if it has repeated values. True or False?
F
grantyng priviliges (syntax)
GRANT SELECT ON PORTS TO LARRY
grant object privileges
GRANT select (column1) ON table TO Dahhman
What does the MAX Return?
Returns the largest value in a given column
COUNT(*) - Aggregate fxn
Returns the number of rows in a table
What does the SIGN(x) return?
Returns the sign of input x as -1,0,1
What does the MIN Return?
Returns the smallest value in a given column
What does the SQRT(x) return?
Returns the square-root value of x
What does the ROUND(x,d) return?
Returns the value of x rounded to the number of decimal places specified by the value d
schema objects
Tables Constraints Indexes Views Sequences Private Synonyms
The transformation from an ER diagram to a physical design involves changing terminology. Entities in the ER diagram become __________ , and attributes become ____________.
Tables, Columns
What is the Length Function used for?
The LEN() function returns the length of the value in a text field.
What is the LIKE Operator used for?
The LIKE operator is used to search for a specified pattern in a column.
What is the Mid Function used for?
The MID() function is used to extract characters from a text field.
What does the NOT NULL Constraint do?
The NOT NULL constraint enforces a column to NOT accept NULL values.
What does the Now Function return?
The NOW() function returns the current system date and time.
What is the Round Function used for?
The ROUND() function is used to round a numeric field to the number of decimals specified.
What does the SELECT INTO do?
The SELECT INTO statement selects data from one table and inserts it into a new table.
What is the SELECT TOP Clause used for?
The SELECT TOP clause is used to specify the number of records to return.
What is the SELECT Statement (def, partial) used for?
The SELECT statement is used to query the database and retrieve.
What is the SELECT Statement for?
The SELECT statement is used to select data from a database.
Which of the following is not a good guideline for deciding when to create an index for a column?
The column is frequently updated.
word form in delete statement
The syntax starts with the reserved words DELETE and the optional FROM, then the name of the target table, then an optional WHERE clause.
When you code a table-level check constraint, the constraint can refer to data in more than one
column
Which of the following describes a type of view that is based on a subquery that retrieves or derives data from one or more tables, and may also contain functions or grouped data?
complex view
column family / SQL equivalents
database == keyspace table == column family row == row column (same per row) == column (diff per row)
"When the DESC keyword is included in an ORDER BY clause, where will NULL values be displayed?"
first
"For most small tables, ______ are faster than index searches."
full table scans
TO_YMINTERVAL
function converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to an INTERVAL YEAR TO MONTH data type
SYS_CONNECT_BY_PATH
function is a useful feature for formatting hierarchical data output. SYS_CONNECT_BY_PATH(TITLE,'/'). The rsult is /CEO/VP/Director 2/Manager 1
"In a SELECT statement, in which clause must you include all columns in the SELECT list that do NOT use a grouping function of some sort?"
in the GROUP BY clause
WITH READ ONLY view
indicate that the table or view cannot be updated
What can you use to combine data from two or more tables into a single result set?
join
Which SQL capability is demonstrated when two or more tables are linked with a WHERE clause?
joining
on delete cascade
kod kreiranja constraint opcija koja omogućava da izbrišemo row koji je foreisgn key za neku drugu tabelu. U tom slučaju je
USER_SYS_PRIVS
lists system privileges granted to the current user. Its columns are the same as those in "DBA_SYS_PRIVS". USERNAME Name of the current user PRIVILEGE System privilege ADMIN_OPTION Grant was with the ADMIN option
The CREATE DATABASE statement creates two files on the hard drive of the server: a data file and a
log file
When the WHERE clause contains multiple types of operators, which of the following is resolved last?
logical operators
To allow users to log on using either type of authentication, you need to set the SQL Server authentication to ________________ mode.
mixed
mongo read/write policy
mongo allows the user to set policies ("concerns") for read/write ops. READ -local, available, majority, linearizable, snapshot WRITE -(number), majority, (tag set)
For each type of action query, a table can have
multiple AFTER triggers and one INSTEAD OF trigger
Subqueries that return more than one column are referred to as what type of subqueries?
multiple-column subqueries
neo4j transactions
neo4j does support ACID transactions
Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) If the LEFT keyword is replaced with the FULL keyword, the total number of rows that are returned must equal
none of the above
Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) If the LEFT keyword is replaced with the RIGHT keyword, the total number of rows that are returned must equal
none of the above
JSON data types
number, string, boolean, array, object, null
key-value store
objects in a key-value store are pairs of keys and values -all access is via primary key -value is an opaque blob
"In an INSERT statement, how can you specify that a NULL value is to be inserted into a column?"
omit the column in the column list OR explicitly include the NULL keyword in the INSERT statement's VALUES clause
When passing a list of parameters to a stored procedure by name, you can omit optional parameters by
omitting the parameter name and value from the list
"For the statement SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE dept_name = 'ACCTG'); to execute properly, how many id values should the subquery return?"
one
To override the order of precedence in an arithmetic expression, you can use
parentheses
What should enclose subqueries?
parentheses
DBA_objects
people who signed to DBA role
Data validation is the process of
preventing errors due to invalid data
The WITH CHECK OPTION clause
prevents a row in a view form being updated if that would cause the row to be excluded from the view
primary servers for replication
primary -exactly one node -always gets write requests
Management Studio allows you to back up a database. Then, if you accidentally modify or delete data, you can easily ________________ it.
restore
Oracle Flashback Version using the VERSIONS BETWEEN clause of the SELECT statement
retrieve the different versions of specific rows that existed during a given time interval returns a table with a row for each version of the row that existed at any time during the specified time interval
LOCALTIMESTAMP
returns the current date and time in the session time zone in a value of data type TIMESTAMP. The difference between this function and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value, while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.
SESSIONTIMEZONE
returns the value of the current session's time zone. The return type is a time zone offset (a character type in the format '[+|]TZH:TZM') or a time zone region name,
ROLLBACK TO SAVEPOINT name
rolls back current transaction to the specified SAVEPOINT
In a cross join, all of the rows in the first table are joined with all of the
rows in the second table
Which clause of the SELECT statement constrains columns that use the GROUPING functions?
the HAVING clause
Which comparison operator is used when matching character patterns?
the LIKE operator
"Which DML statement updates rows conditionally, based on whether a row already exists or not?"
the MERGE statement
Which clause of a SELECT statement sorts the result rows?
the ORDER BY clause
Which statement removes all rows from a table while releasing all or most of the storage space used by the table?
the TRUNCATETABLE statement
"When the MAX group function is used with a NUMBER column, which number is displayed?"
the largest number
What is another name for an outer query?
the main query
Code example 4-2 SELECT VendorName, InvoiceNumber FROM Invoices LEFT JOIN Vendors ON Invoices.VendorID = Vendors.VendorID; (Refer to code example 4-2.) The total number of rows returned by this query must equal
the number of rows in the Invoices table
"If no GROUP BY clause or WHERE clause is included in a simple SELECT statement, what would the COUNT(*) function return?"
the number of rows in the table
"If no column list is specified in an INSERT statement, your VALUES clause must list the values in what order?"
the order in which the columns are specified in the table
text indexes
this allows efficient searching over text with some optimizations: --removing "stop" words (a,the, etc.) --"stemming" words (walks, walking, walked -> walk)
When you use Windows authentication to connect to a database, SQL Server
uses the login name and password that you use for your PC to authorize your connection
How can you write "not equal to" in the WHERE-clause?
!= ^= <>
What are the two operators that are functionally equivalent to the <> operator?
!= and ^=
When you resolve a M-M by creating an intersection entity, this new entity will always inherit:
A relationship to each entity from the original M-M.
binary JSON
-binary variant of JSON used by MongoDB documents --more efficient storage --more efficient scanning --mostly a superset of JSON
available constraints on views
1. View check option constraints 2. View read-only constraints 3. View primary key constraints 4. View unique key constraints 5. View RI constraints
Operator Precedence
1. arithmetic operators 2. concatenation operator 3. comparison condititons 4. is [not] null, like, 5. not between 6. not 7. and 8. or
If ExpirationDate contains a value that's equivalent to June 2, 2016 and the GetDate function returns a value that's equivalent to July 17, 2016, what will the Solution column contain when this code is executed? DATEDIFF(day, ExpirationDate, GetDate()) AS Solution
45
What is the maximum number of decimal positions you can specify for a timestamp datatype?
9
In a Cartesian join, linking a table that contains 10 rows to a table that contains 9 rows will result in ____ rows being displayed in the output
90
What is the 'FILLFACTOR'?
A "FILLFACTOR" is one of the important arguments that can be used while creating an index. According to MSDN, FILLFACTOR specifies a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild. Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage. By setting the fill-factor value, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth. Specifying a fill-factor value of 70 would imply that 30 percent of each page will be left empty, providing space for index expansion as data is added to the underlying table. The fill-factor setting applies only when the index is created or rebuilt.
What is FOREIGN KEY?
A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.
What is NOT NULL Constraint?
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.
What is PRIMARY KEY?
A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row, and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.
RESTORE POINT
A RESTORE POINT is an object in the database you create to represent a given moment in the database
Which comparison operator compares a value to each value returned by a subquery?
ANY
"When a commit occurs, what happens to existing savepoints?"
All savepoints are erased when a commit occurs.
What datatype would be used to store binary data in an external file?
BFILE
Why would we use the AUTO INCREMENT?
Automatically create the value of Primary Key Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.
What is BCP? When is it Used?
BCP or BulkCopy is a tool used to copy huge amounts of data from tables and views. BCP does not copy the complete structures from source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.
The explanation below is an example of which constraint type? A column must contain only values consistent with the defined data format of the column
Column integrity
adding comments
COMMENT ON objectType fullObjectName IS c1;
What two keywords in a hierarchical query establishes the relationship between a parent and a child?
CONNECT BY
All of the aggregate functions ignore null values, except for which function?
COUNT(*)
Write an aggregate expression for the number of entries in the VendorName column, including null values
COUNT(*)
What is a Conformed Fact?
Conformed dimensions are the dimensions which can be used across multiple Data Marts in combination with multiple facts tables accordingly.
What are Conformed Dimensions?
Conformed dimensions mean the exact same thing with every possible fact table to which they are joined. They are common to the cubes.
___________________ names can be used when you want to assign a temporary name to a table.
Correlation
What is a Cube and Linked Cube with Reference to Data Warehouse?
Cubes are logical representation of multidimensional data. The edge of the cube contains dimension members and the body of the cube contains data values. The linking in cube ensures that the data in the cubes remain consistent.
What is Dimensional Modeling?
Dimensional data model concept involves two types of tables and it is different from the 3rd normal form. This concept uses Facts table, which contains the measurements of the business, and Dimension table, which contains the context (dimension of calculation) of the measurements.
V_$
Dynamic performance views display information about current database activity in real time.
Example of a CREATE VIEW syntax:
Example of a CREATE VIEW syntax: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
Which of the following statements calls the stored procedure and passes the values '2015-12-01' and 122 to its input parameters? CREATE PROC spInvoiceTotal1 @DateVar smalldatetime, @VendorID int AS SELECT SUM(InvoiceTotal) FROM Invoices WHERE VendorID = @VendorID AND InvoiceDate >= @DateVar; ______________________________________________
EXEC spInvoiceTotal1 @VendorID = 122, @DateVar = '2015-12-01';
Example of a FOREIGN KEY Syntax
Example of a FOREIGN KEY Syntax CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) )
Describes the Foreign Key Columns in Fact Table and Dimension Table?
Foreign keys of dimension tables are primary keys of entity tables. Foreign keys of facts tables are primary keys of Dimension tables.
What statement can you use to divide a script into multiple batches?
GO
Grant privilege tu user
GRANT privilege; TO user; e.g. GRANT create session TO scot
Which operator would immediately precede a subquery if that subquery can potentially return more than one row?
IN
CHECK Constrain on a Single Column:
If you define a CHECK constraint on a single column it allows only certain values for this column.
How to Stop Log File Growing too Big?
If your Transaction Log file was growing too big and you wanted to manage its size, then instead of truncating transaction log file, you should choose one of the options mentioned below. 1) Convert the Recovery Model to Simple Recovery If you change your recovery model to Simple Recovery Model, then you will not encounter the extraordinary growth of your log file. However, please note if you have one long running transaction it will for sure grow your log file till the transaction is complete. 2) Start Taking Transaction Log Backup In this Full Recovery Model, your transaction log will grow until you take a backup of it. You need to take the T-Log Backup at a regular interval. This way, your log would not grow beyond some limits.
System Change Number (SCN).
It returns committed data that was current at that point in time
Which two sets of join keywords create a join that will include unmatched rows from the first table specified in the SELECT statement?
LEFT OUTER JOIN and FULL OUTER JOIN
What is MDS?
Master Data Services or MDS helps enterprises standardize the data people rely on to make critical business decisions. With Master Data Services, IT organizations can centrally manage critical data assets companywide and across diverse systems, enable more people to securely manage master data directly, and ensure the integrity of information over time.
Mathematical Functions
Mathematical Functions Include: +, -, *, /, %
What are Primary Keys and Foreign Keys?
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental aspect of all keys and constraints. A table can have only one primary key. Foreign keys are a method of ensuring data integrity and manifestation of the relationship between tables.
The six clauses of the SELECT statement must be coded in the following order:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
Which date function returns a DATE datatype representing the current database server date and time?
SYSDATE
Which expression will add five days to the current date?
SYSDATE + 5
Differences between the Star and Snowflake Schema?
Star schema: A single fact table with N number of dimensions; all dimensions will be linked directly with a fact table. This schema is de-normalized and results in simple join and less complex query as well as faster results. Snow schema: Any dimension with extended dimensions is known as snowflake schema; dimensions maybe interlinked or may have one-to-many relationship with other tables. This schema is normalized, and results in complex join leading very complex query (as well as slower results).
Data Type: Boolean
Stores TRUE or FALSE values boolean
What does the Sum Function Return?
The SUM() function returns the SUM of the values of the selected column.
replacyng synonyms
There is no statement that combines ALTER with SYNONYM; not for private synonyms, nor for public synonyms. Instead you can use the OR REPLACE option with CREATE SYNONYM
What is VLDB?
VLDB is abbreviation of Very Large Database. For instance, a one-terabyte database can be considered as a VLDB. Typically, these are decision support systems or transaction processing applications serving a large number of users.
You use data definition language (DDL) to create, modify, and delete the ________________ of a database.
all of the above (objects, sequences, tables)
When I Delete any Data from a Table, does the SQL Server reduce the size of that table?
When data are deleted from any table, the SQL Server does not reduce the size of the table right away; however, it marks those pages as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages. If you wait for sometime, the background process de-allocates the pages, finally reducing the page size.
"When using a LIKE condition to perform pattern matching, what does an underscore represent?"
any single character
You can invoke a table-valued user-defined function
anywhere you'd refer to a table or a view
One way to examine the system objects that define a database is to use which views?
catalog
To store and manage the databases of the client/server system, each server requires what?
database management system (DBMS)
To make a parameter for a stored procedure optional, what do you assign to it?
default value
TZ_OFFSET
returns the time zone offset corresponding to the value entered. The return value is dependent on the date when the statement is executed. For example if the TZ_OFFSET function returns a value -08:00, the return value can be interpreted as the time zone from where the command was executed is eight hours after UTC
You can update or delete rows in a table based on rows in another table by including what within your DML statements?
subqueries
"How many digits to the right of the decimal point are allowed for a column defined as NUMBER(7,3)?"
three
What is the name of the pseudocolumn which indicates the DML performed on a row?
versions_operation
In a join, column names only need to be qualified where?
when the same names are used in both tables
How would you remove a column in a table with ALTER?
To remove a column in a table, use the following syntax: ALTER TABLE table_name DROP COLUMN column_name
CONNECT BY PRIOR col1=col2 direction?
Top_down - col1=parent key col2 = child key Bottom - up col1=child key col2 = parent key
The YR element can be included in a format argument of the TO_CHAR function to spell out the year of the specified date.
F
The overall mission of the Oracle Corporation is to use the internet and fast processing servers to build its own network
F
flashback (syntax)
FLASHBACK TABLE table_name TO ...
The CASCADE CONSTRAINTS option of the REVOKE statement removes any constraints made to the object using which privilege?
REFERENCES (foreign key constraints)
What are the FIELDS in a view?
The fields in a view are fields from one or more real tables in the database.
"If the column order_date has a datatype of date, what would be the value of order_date + 7?"
"Exactly one week after the order date, at the same time of day."
What is the purpose of the START WITH <column name> IS NULL clause in a hierarchical query?
"Hierarchical queries that should begin at the very top of the hierarchy will often have a NULL value for <column name> since that column often will be the name of the parent, and the top row of the query does not have a parent."
Which comparison operators can be used with multiple-row subqueries?
"IN, ANY, and ALL"
Can a group function be included in a HAVING clause?
Yes
views (rules)
if the function is included in the view this column must have alias
"If no schema is explicitly included in a CREATE TABLE statement, where is the table created?"
in the current user's schema
"Which SQL*Plus command displays the structure of a table, view, materialized view, synonym, or the specifications of a specified function or procedure?"
the DESC[RIBE] command
Using compound OR conditions is functionally equivalent to using which operator?
the IN operator (assuming the subject of each of the OR clauses is the same)
Which two SQL statements add rows of data to an existing table?
the INSERT and MERGE statements
Which SELECT statement clause would you use with the JOIN keyword to specify a traditional join predicate?
the ON clause
Outer join conditions CANNOT be linked to another condition by which operator?
the OR operator
Which clause of a SELECT statement does Oracle server evaluate last?
the ORDER BY clause
What constraint(s) is used to create referential integrity?
the REFERENCES constraint or the FOREIGN KEY constraint
What does the IDENTIFIED BY clause of the ALTER USER statement specify?
the assignment of a password for the user
sharding
we can divide documents among multiple servers -improves performance -better availability when some servers fail -big data
indexes
we can index any field or combo of fields -single field -compound index (multiple fields) -multi-key index (arrays)
The statement CREATE VIEW Example4 AS SELECT * FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0;
will fail because the SELECT statement returns two columns named VendorID
The default keyword for group functions is ____.
ALL
automated commit
1. DDL statement is issued 2. DCL statement is issued 3. normal exit from iSQL plus without COMMIT or ROLLBACK
"When using the TO_CHAR function to format numbers, what does a zero in the format model indicate?"
A zero (0) returns a zero in the specified position when there is no number for the position.
Write an aggregate expression to calculate the average value of the InvoiceTotal column, excluding null values
AVG(InvoiceTotal)
NCLOB
Accepts CLOB data in Unicode. Maximum size is calculated in the same manner that it is for the BLOB datatype. Regarding Unicode—it is a character set that serves as an alternative to ASCII and represents a more universal standard that supports all major languages more easily than the other implementations in use today
NEXTVAL
Advances the sequence generator to the next available number
Explain IntelliSense for Query Editing:
After implementing IntelliSense, we will not have to remember all the syntax or browse online references. IntelliSense offers a few additional features besides just completing the keyword.
Data Type: Float
Approximate numerical, mantissa precision 16 float
Data Type: Real
Approximate numerical, mantissa precision 7 real
Each of the outcomes listed below is a result of executing the following script except for one. Which one? CREATE ROLE ExampleRole; ALTER ROLE db_datareader ADD MEMBER ExampleRole; GRANT INSERT,UPDATE ON Vendors TO ExampleRole; DENY INSERT ON Vendors TO ASmith; ALTER ROLE ExampleRole ADD MEMBER ASmith;
By being assigned to the role db_datareader, a user would be granted INSERT and UPDATE permission to the Vendors table and SELECT permission to all user tables.
creating private synonym (syntax)
CREATE OR REPLACE SYNONYM CO FOR CRUISE_ORDERS;
"What type of sort should be done in order to list the 10 highest paid employees in the table, starting with the highest paid person?"
Descending
Example of CREATE DATABASE syntax:
Example of CREATE DATABASE syntax: CREATE DATABASE dbname;
If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed last.
HAVING
A business rule such as "All accounts must be paid in full within 10 days of billing" is best enforced by:
Hiring a programmer to create additional programming code to identify and report accounts past due
What is the default type of join if no specific JOIN type is specified?
Inner Join, This is the default type of join if no specific JOIN type is specified.
[ ... ]
Matching Character List Matches any character in list ...
Which type of constraint can only be specified at the column level?
NOT NULL
Which type of constraint specifies that the column on which the constraint is defined CANNOT contain a NULL value?
NOT NULL
Which of the following functions can be used to substitute another value for a NULL value during calculations?
NVL
Which of the following functions allows for different options, depending upon whether a NULL value exists?
NVL2
Can a column alias be used in a GROUP BY clause?
No
Can a column be referenced in an ON clause before the column's table has been specified?
No
ALL_
Objects owned by any user in the database to which the current user has privileges
Which are the Important Points to Note when Multilanguage Data is Stored in a Table?
There are two things to keep in mind while storing unicode data. First, the column must be of unicode data type (nchar, nvarchar, ntext). Second, the value must be prefixed with N while insertion. For example, INSERT INTO table (Hindi_col) values (N'hindi data')
Which type of queries ask for the n largest or smallest values of a given column?
Top-n queries
USING
USING (EMPLOYEE_ID);
Secondary UID's are
Useful as an alternative means of identifying instances of an entity
Which option must you include in a GRANT statement if you intend for the user to grant this privilege to other users?
WITH GRANT OPTION
order by (sorting by postition)
We can sort the rows with an ORDER BY that identifies the position within the select list of the expression we wish to sort by, like this: 01 SELECT 'Individual', 02 LAST_NAME || ', ' || FIRST_NAME 03 FROM CRUISE_CUSTOMERS 04 UNION 05 SELECT CATEGORY, 06 VENDOR_NAME 07 FROM VENDORS 08 ORDER BY 2; The result:
How to Find the List of Fixed Hard Drive and Free Space on Server?
We can use the following Stored Procedure to figure out the number of fixed drives (hard drive) a system has along with free space on each of those drives. EXEC master..xp_fixeddrives
What is the Difference between Update Lock and Exclusive Lock?
When Exclusive Lock is on any process, no other lock can be placed on that row or table. Every other process have to wait till Exclusive Lock completes its tasks. Update Lock is a type of Exclusive Lock, except that it can be placed on the row which already has Shared Lock on it. Update Lock reads the data of the row which has the Shared Lock as soon as the Update Lock is ready to change the data it converts itself to the Exclusive Lock.
Can SQL delete records from a database?
Yes, SQL can delete records from a database
Which type of join is represented by the use of an operator other than an equality operator (=)?
a non-equijoin
In Management Studio, the Query Editor uses the IntelliSense feature to automatically display ________________ lists that you can use to enter parts of the SQL statement.
completion
A user who's granted the REFERENCES object permission can do what?
create objects that refer to the object
select list
it could include expressions consisting of a combination of column names, arithmetic operators, literal values, and SQL functions, as you'll see later.
Which SQL capability allows you to control the number of columns returned by a query by choosing the columns in the SELECT clause?
projection
Which SQL capability allows you to choose rows in a table to be returned by a query?
selection
What does the INSERT INTO do?
The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table.
What is the INSERT INTO Statement used for?
The INSERT INTO statement is used to insert new records in a table.
What does the LEFT JOIN do?
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2).
What is the ORDER BY Keyword used for?
The ORDER BY keyword is used to sort the result-set by one or more columns. SELECT column_name, column_name FROM table_name ORDER BY column_name ASC|DESC, column_name ASC|DESC;
Can you have a lot of UNIQUE Constraints per table?
YES, that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
Can SQL Constrains be specified when a table is Created?
Yes, Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).
Can SQL create new tables in a database?
Yes, SQL can create new tables in a database
Can SQL create views in a database?
Yes, SQL can create views in a database
Can SQL execute queries against a database?
Yes, SQL can execute queries against a database
Can SQL can insert records in a database?
Yes, SQL can insert records in a database
Can SQL retrieve data from a database?
Yes, SQL can retrieve data from a database
Can SQL set permissions on tables, procedures, and views?
Yes, SQL can set permissions on tables, procedures, and views
Can SQL update records in a database?
Yes, SQL can update records in a database
Is the FULL OUTER JOIN a combination of both LEFT and RIGHT join?
Yes, The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
Does the NOT NULL Constraint always needs to contain a value?
Yes, The NOT NULL constraint enforces a field to always contain a value.
Can you have ORDER by Desc value?
Yes, To sort the records in a descending order, you can use the DESC keyword.
Can you have more than one statement to be executed in the same call to the server?
Yes, but a semicolon is needed to separate each SQL Statement.
Is select is the same as SELECT in SQL?
Yes, select is the same as SELECT since SQL is not case sensitive
Does the SELECT TOP clause has any utility on large tables?
Yes, the SELECT TOP clause can be very useful on large tables with thousands of records.
Does the UNION operator needs to have the same number of columns?
Yes, the UNION operator needs to have the same number of columns. Notice that each SELECT statement within the UNION must have the same number of columns.
What is the default order in the ORDER keyword?
The ORDER BY keyword sorts the records in ascending order by default.
What does the RIGHT JOIN do?
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1).
Example of UPDATE statement
Update Example: UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;
What does the U in CRUD stand for?
Update, U in CRUD, stands for Update and is on of the four basic functions of persistent storage.
COUNT- Aggregate fxn
Returns the total number of values in a given column
schema objects categories
Tables Constraints Indexes Views Sequences Private Synonyms
Which function can be used to determine the number of months between two dates?
"MONTHS_BETWEEN. The syntax of the MONTHS_BETWEEN function is MONTHS_BETWEEN(date1, date2)"
Which of the following are examples of data vs. information:
- Student age vs. average age of all students in class - Bank deposit amount vs. total account balance - Price of a computer vs. total sales of all computers for a company
How to Enable/Disable Indexes?
--Disable Index ALTER INDEX [IndexName] ON TableName DISABLE GO --Enable Index ALTER INDEX [IndexName] ON TableName REBUILD GO
BASE (rather than ACID)
-Basically available -Soft-state -Eventual consistency
BSON vs JSON data types
-bson is mostly a superset of json types -no more generic number type
MongoDB conceptual organization
-database -collection: like a table -document: like a row
namespaces
1. Tables Views Sequences Private Synonyms User-Defined Types 2. Indexes 3. Constraints
What is UNIQUE KEY Constraint?
A UNIQUE constraint enforces the uniqueness of the values in a set of columns; so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
If CustomerAddress contains " 178 E Center Street ", what will the Solution column contain when this code is executed? LEN(LTRIM(RTRIM(CustomerAddress))) AS Solution
19
two ways of adding constraints
1. modifying columns 2. adding constrint
When all attributes are single-valued, the database model is said to conform to:
1st Normal Form
"What value will be returned by this regular expression? REGEXP_COUNT('AEioUIOUAEiOU','Ei',2,i)"
2
Code example 6-2 WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) When this query is executed, there will be how many rows in the result table?
5
If RegistrationDate contains a value that's equivalent to August 10, 2016, what will the Solution column contain when this code is executed? DATEPART(month, RegistrationDate) AS Solution
8
What is used to indicate the end of an SQL statement?
;
Conditional selections used in the where clause for SELECT
= Equal > Greater than < Less than >= Greater than or equal <= Less than or equal <> Not equal to LIKE
Which comparison operator identifies values that are equal to or larger than a particular value?
>= (greater than or equal to)
Which system function can you use to return the value of the most recently assigned identity column?
@@IDENTITY
How is ACID property related to Database?
ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for while evaluating databases and application architectures. For a reliable database, all this four attributes should be achieved. Atomicity is an all-or-none proposition. Consistency guarantees that a transaction never leaves your database in a half-finished state. Isolation keeps transactions separated from each other until they are finished. Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.
Which comparison operator compares a value to every value returned by a subquery?
ALL
Which data dictionary view would you query to display objects you can access either by privileges explicitly granted to you or by privileges granted to PUBLIC?
ALL_COL_PRIVS_RECD
To transfer a database object from one schema to another, you use the which statement?
ALTER SCHEMA
When you create a table using the Management Studio, the table is automatically stored in the default schema. If you want to transfer the table to a different schema, you can use the which statement?
ALTER SCHEMA
"What command could a user issue such that the default format of dates, when displayed as output, are not in the typical dd-Mon-yy format?"
ALTER SESSION
Which statement is used to modify the structure of an existing table?
ALTER TABLE
adding a column
ALTER TABLE CRUISE_ORDERS ADD (ORDER_DATE VARCHAR2(20));
Examples of situations in which you need to drop and recreate a constraint include
Adding or removing columns to the column list of a PRIMARY KEY, FOREIGN KEY, or UNIQUE constraint n Changing the logic of a CHECK constraint n Changing the table that a FOREIGN KEY constraint references
If an intersection entity is formed that contains no attributes of its own, its uniqueness may be modeled by
Barring the relationships to the original entities.
^
Beginning of Line Anchor Match the subsequent expression only when it occurs at the beginning of a line.
Data Type: Binary Object
Binary string. Fixed-length n Syntax: Blob Text Fixed Length Syntax: Binary (fixed up to 8K) Varbinary (<8K) Image (<2GB)
How do you turn "data" into "information"?
By querying it or accessing it
Example of UNION syntax:
Example of UNION syntax: SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
What does the C in CRUD stand for?
Create, C in CRUD, stand for Create and is on of the four basic functions of persistent storage.
Which of the following is not a SQL DML statement?
CreateTable
Single-row functions return one result per group of rows processed.
F
Example of DROP Syntax:
Example of DROP Syntax: DROP INDEX table_name.index_name DROP DATABASE database_name
Example of UNIQUE Constrain Syntax:
Example of UNIQUE Constrain Syntax: CREATE TABLE Persons ( P_Id int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
Example of a COUNT function syntax:
Example of a COUNT function syntax: SELECT COUNT(column_name) FROM table_name;
REMAINDER
Identifies the multiple of n2 that is nearest to n1, and returns the difference between those two values.
"By default, how is the output of the UNION ALL set operator sorted?"
It is not sorted.
What's the advantage of using IN rather than = in the following query?
Multiple values could be returned by the subquery without causing the statement to fail.
When data is stored in more than one place in a database, the database violates the rules of ___________.
Normalization
Once a COMMIT is issued
Once a COMMIT is issued, all existing SAVEPOINTs are erased.
|
Or 'a|b' matches character 'a' or 'b'.
inline views
SELECT * 02 FROM (SELECT * FROM DUAL);
RANK
RANK(c1) WITHIN GROUP (ORDER BY e1)
What is the Basis of SQL?
RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
What is RDBMS?
RDBMS stands for Relational Database Management System.
How to Find Tables without Indexes?
Run the following query in the Query Editor. USE <database_name>; GO SELECT SCHEMA_NAME(schema_id) AS schema_name ,name AS table_name FROM sys.tables WHERE OBJECTPROPERTY(OBJECT_ID,'IsIndexed') = 0 ORDER BY schema_name, table_name; GO
What is a Star Schema?
Star schema is a type of organizing the tables such that we can retrieve the result from the database quickly in the warehouse environment.
The "Arc Implementation" is a synonym for what type of implementation?
Supertype and Subtype Implementation
If introduced as follows, the subquery can return which of the values listed below? WHERE InvoiceTotal > ALL (subquery)
a column of one or more rows
If you try to move a database file that's attached to a server, you'll get an error message that indicates the file is in use. To get around this, you need to ________________ the database from the server.
detach
cross join
makes cartesian product
"To join n tables together using Oracle proprietary join syntax, how many join conditions are needed in the WHERE clause?"
n - 1
Subqueries can be ________________ within other subqueries.
nested
"What does the format of my_string have to be in order for the WHERE clause in the following query to evaluate to TRUE? SELECT * FROM dual WHERE REGEXP_LIKE(my_string,'^[a-z]+$');"
one or more all alphabetic lowercase characters
How many ORDER BY clauses are allowed in a compound query?
only one
When would you use an ORDER BY clause in a subquery?
to perform Top-n Analysis
read preferences
you can also specify your preference of where a read query is delivered in the replica set -primary, primaryPreferred, secondary, secondaryPreferred, nearest
can we join tables without presence of keys
you can join tables without the presence of PRIMARY KEY and FOREIGN KEY constraints
grouping column
detektuje u kojoj koloni nije izvršeno grupisanje
A database ________________ is a schematic drawing that shows you the relationships between the tables you're working with.
diagram
cassandra replication
each keyspace has a replication factor indicating how many copies of a given data item to store
ROLLBACK
ends current transaction discarding all pending changes
commit
ends the current transaction making all pending data changes permanent
case function
SELECT col1, col2 CASE col3 WHEN 'expr1' THEN exprn1 WHEN 'expr2' THEN exprn2 ELSE 'exprn2' END AS col_name FROM table1
When you code a SELECT statement, you must code the four main clauses in the following order
SELECT, FROM, WHERE, ORDER BY
non-schema objects categories
Users Roles Public Synonyms
So if there is a delay on updating a table with index when should we used indexes?
You should only create indexes on columns (and tables) that will be frequently searched against.
* *
Zero or More -- Star Quantifier Matches zero or more occurrences of the preceding subexpression
examples of NoSQL databases
big data, cassandra, dynamo, Hbase, MongoDB, voldemort
(Refer to code example 4-1.) This join is coded using what syntax?
both b and c
mongoDB consistency
mongo provides tunable consistency
can the only one element be modified
yes - datatype default or constraint or
What are ten criteria which indicate that a b-tree index would be appropriate to create on a given column in a table?
"1. the column contains many different values (good cardinality) 2. the table is large (or it contains many rows) 3. the value of the column is seldom NULL 4. the column is used often in a WHERE clause 5. the column is used often in a sort clause (ORDER BY) 6. the column is used often when multiple tables are joined together 7. the column is used often in a GROUP BY clause 8. the column, when used in a WHERE clause, does not have an arithmetic expression or function applied to it before the comparison operation is performed 9. updates to the values in that column are kept to a minimum 10. you know the optimizer is going to choose to use that index as opposed to performing a full table scan"
Which category of SQL statements control access/permission to the data?
"DCL statements, such as GRANT and REVOKE, control access to data."
What must you do to create a column alias containing spaces?
"Enclose the column alias in double quotes ("")"
"What is the benefit to setting a column that you no longer want in your table to UNUSED, rather than just dropping the column?"
"Improved performance. Dropping a column is usually labor intensive, so it is delayed until off hours. If you mark the column unused, Oracle adjusts the data dictionary to logically indicate the column and its data are no longer there. Then, during a period of little activity such as the evening, the physical drop can take place and its effect on the users will be minimal."
What is the primary difference between an index organized table and a regular (heap) table?
"In the index organized table, the rows are physically stored in order by the primary key. "
How do you grant UPDATE privileges on specific columns in a table?
"Include a column list in the GRANT statement. This syntax is only valid with the UPDATE, REFERENCES, and INSERT privileges."
"The NVL2 function accepts three expressions, and returns the second expression if the first expression is ______."
"NOT NULL. If the first expression is null, the third expression is returned."
"According to operator precedence, which of the three logical operators is evaluated first?"
"NOT is evaluated first. The order of precedence for logical operators is NOT, AND, and OR."
Name three functions that can be used with group functions to replace NULL values.
"NVL, NVL2, and COALESCE"
"According to operator precedence, are OR conditions evaluated before AND conditions?"
"No, AND conditions are evaluated before OR conditions."
Must a HAVING clause be preceded by a GROUP BY clause?
"No, a HAVING clause can be listed first, but it is usually placed after the GROUP BY clause."
Does one DML statement constitute a complete database transaction?
"No, one DDL or DCL statement constitutes a complete database transaction, but a transaction can contain more than one DML statement."
"When you create an External Table in your schema, can you specify the tablespace in which this table will be stored?"
"No, the actual rows of an External Table are stored as a file in the Operating System."
Can you modify data through a view that contains a GROUP BY clause?
"No, you also CANNOT modify data if the view contains group functions, the DISTINCT keyword, or the ROWNUM pseudocolumn."
Will the query SELECT * FROM emp WHERE salary = commission; display rows in which the salary and the commission are both NULL?
"No. If two values are both NULL, they are not equal to each other. Because you don't know the values for salary or commission, there is not enough information to tell whether the condition is TRUE or FALSE. Therefore, the condition evaluates to a logical NULL. Rows in which the WHERE clause evaluates to NULL or FALSE are not displayed when you issue a SELECT."
Which character function substitutes one string for another?
"REPLACE. The syntax of the REPLACE function is REPLACE(column|expression, search_string [, 'replacement_string'])"
Which function rounds numeric values to a specified decimal place?
"ROUND. The syntax of the ROUND number function is ROUND(column|expression, n)"
"Which character function returns a portion of a character string, beginning at a specified character position up to a specified length?"
"SUBSTR. The syntax of the SUBSTR function is SUBSTR(column|expression, m [, n])"
Which type of operators should be used with queries that return only one row?
"Single-row operators. Single-row operators expect only one value and include =, >, >=, <, <=, <>, and LIKE."
How do you create a sequence that generates descending sequence values?
"Specify a negative value in the INCREMENT BY clause. For example, INCREMENT BY -1 would generate sequential descending values."
Which function removes leading and/or trailing characters from a character string?
"TRIM. The syntax of the TRIM function is TRIM(leading|trailing|both, trim_character, FROM column|expression)"
What valid character does Oracle recommend you NOT use for table names?
"The $ character. Because data dictionary tables contain a $ character, using $ in a table name makes it hard to distinguish between a data dictionary object and a user-created object."
An UPDATE statement can update multiple columns in one table by using multiple columns in which clause?
"The SET clause. When multiple columns are included in the SET clause, they should be separated by commas."
Which function could be used to return a date without the time portion?
"The TRUNC date function returns a date with the time portion of the day truncated to the specified format unit. If no 'fmt' (format model) is provided, the date is truncated to the nearest day. The syntax of the TRUNC date function is TRUNC(date, 'fmt')."
Which option can you include with the CREATEVIEW statement to prevent DML through the view?
"The WITH READ ONLY option of the CREATE VIEW statement prevents any DML operations such as inserts, updates, and deletes from being performed on the view's underlying or base table(s) when using the view."
"If no ORDER BY clause is included in a SELECT statement, how are the results sorted?"
"The order of the results will be unpredictable. Each time the query is run, the order in which the rows are displayed may be different."
What would be the most common reason for an error message if you attempted to drop a primary key constraint on a table?
"The reason for the error would be that the column that had the primary key constraint on it was also acting as the parent in a parent / child relationship. Consequently there is a column in another table which is acting as the child in that relationship, and is pointing (via a foreign key constraint) to the column containing the primary key constraint. The primary key constraint ensures that the parent column in this parent/child relationship is unique, so Oracle prevents you from removing it. If duplicate values were allowed in the primary key column, there could be multiple parents for the same child, a violation of basic database design. "
Which three functions convert case for character strings?
"UPPER, LOWER, and INITCAP. The UPPER character function converts mixed case or lowercase value to uppercase, LOWER converts mixed or uppercase to lowercase, and INITCAP converts a character string to a value with the first character uppercase and the remaining lowercase."
"When a character column contains data, can you decrease the width of the column?"
"Yes, but only if the existing data does not violate the new size"
Can you sort by a column that is NOT included in the SELECT list?
"Yes, you can include a column in the ORDER BY clause even if it is not included in the SELECT list."
What is the default number of decimal places for the ROUND number function?
"Zero. When the integer representing the number of decimal places is not provided, the ROUND function rounds to zero decimal places (i.e., to the nearest whole number)."
What are functions that operate on sets of rows to give one result per group called?
"group functions, or aggregate functions"
"Assuming you have the appropriate object privileges, how should you access a table owned by another user in a FROM clause?"
"prefix the table name with the owner/schema name, as in ownername.tablename"
"When creating a table, column definitions can be omitted if what is used?"
"the AS subquery clause (CTAS, CREATE TABLE AS SELECT)"
"When using a LIKE operator, if the escape character appears in the pattern before the '%' or '_', how does Oracle interpret the character?"
"the character is interpreted literally in the pattern, rather than as a special pattern-matching character"
"What function and arguments would modify the way the hire_date is displayed in a SELECT statement such that hire_date values appear formatted in this way: January 27, 2006?"
"to_char(hire_date, 'fmMonth dd, yyyy')"
What are the different types of JOIN clauses supported by the ANSI- standard?
(1) Inner Join (2) Left Join (3) Right Join (4) Full Join (5) Cross Join
CAP theorem
(Brewer) it's imposs to satisfy more than 2 of the following: -consistency: reads get the latest value -availability: every request gets a response -partition tolerance: system tolerates network delays/failures --if the network is working, all is well ----satisfy consistency & availability --if there is a partition, you have to choose: ----consistency (don't sell any books until exact qty is known) ----availability (report estimated quantities and hope for the best)
high availability; how do we guarantee availability in the presence of hardware/software failures?
-MBTF -mean time between failures - do reliable components ensure a reliable datacenter?
examples of document stores
-MongoDB
examples of graph database
-Neo4j
examples of key-value stores
-Redis -Riak -Voldemort
define column-family stores
-a column family is like a map -a column family is like a map of maps
define column-family stores
-a single row key maps to multiple collections of data -like a two-level map -row key -> column-family -> column key -> column value
eventual consistency and writes; when multiple writes occur to the same value, what should the DB do?
-assume the latest one is correct (cassandra) -ask the user which to apply (couchDB) -use "vector clocks" to determine the ordering (riak)
cassandra nodes and queries
-cassandra is designed to scale both reads and writes --any node can answer any query --the node that gets a query is the coordinator for that operation
define document-based stores
-database us a collection of documents -often JSON -document often has some unique ID
document stores
-database us a collection of documents -often JSON -document often has some unique ID
NoSQL consistency
-different from ACID -consistency-- reads get the latest value -most NoSQL dbs only provide EVENTUAL CONSISTENCY --reads will *eventually* get the value of the latest write
define key-value stores
-key might be customer number -value is whatever you like -value can be structured data or unstructured
4 NoSQL types
-key-value stores (redis, riak, voldy) -document stores (mongo) -graph database (neo4j) -column-family stores (cassandra, hbase)
why use NoSQL?
-performance and scaling -less impedance mismatch (refers to a range of problems representing data from relational databases in object-oriented programming languages.) -may be easier to develop with
when not to use redis
-relationships among data -search based on value -operations on multiple keys
scaling
-scale up: vertical -scale out: horizontal mongo scales horizontally by *sharding*
when to use redis
-session IDs -user profiled/prefs -shopping cart data (key: userID)
GRAPH DATABASES
-similar to object oriented structures -nodes are entities with properties -edges are relationships -good for graph-like queries (e.g. shortest path)
define graph databases
-similar to object-oriented structures -nodes are entities with properties -edges are relationships -good for graph-like queries (e.g. shortest path)
redis data types
-strings -lists -sets -hashes
aggregates
-the unit of storage, some meaningful chunk of data -ie. student, course names, grades, GPA
cassandra architecture
-there is no master node -there are no slave nodes -all nodes are equal
"SELECT * FROM dual WHERE REGEXP_LIKE(my_name, '^(R|B)ob(ert)?');"
...
"SELECT title, author, pub_date FROM books WHERE author IN (SELECT name FROM classics WHERE genre = 'ROMANCE'); "
...
What is the regular expression that could be used to confirm that a given account code is properly formatted?
...
What are the Different Types of Triggers?
1) DML Trigger There are two types of DML Triggers 1.Instead of Trigger Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete. 2. After Trigger After triggers execute following the triggering action, such as an insert, update, or delete. 2) DDL Trigger This type of trigger is fired against Drop Table, Create Table, Alter Table or Login events. DDL Triggers are always After Triggers.
diference three types of constraints
1. primary key 2. unique 3. different constraint type
What does a CROSS JOIN return?
A CROSS JOIN returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables).
What is PIVOT and UNPIVOT?
A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table. In simpler word UNPIVOT table is reverse of PIVOT Table, however it is not exactly true. UNPIVOTING is for sure reverse operation to PIVOTING but if during PIVOTING process data aggregated the UNPIVOT table does not return to original table.
What is Use of @@ SPID in SQL Server?
A SPID is the returns sessions ID of the current user process. And using that session ID, we can find out that the last query was executed.
What are the Difference between Clustered and a Non-clustered Index?
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore, the table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. (Read more here)
What is a Data Mart?
A data mart (DM) is a specialized version of a data warehouse (DW). Like data warehouses, data marts contain a snapshot of operational data that helps business people to strategize based on analyses of past trends and experiences. The key difference is that the creation of a data mart is predicated on a specific, predefined need for a certain grouping and configuration of select data. A data mart configuration emphasizes easy access to relevant information (Reference: Wiki). Data Marts are designed to help the manager make strategic decisions about their business.
What is Data Warehousing?
A data warehouse is the main repository of an organization's historical data, its corporate memory. It contains the raw material for management's decision support system. The critical factor leading to the use of a data warehouse is that a data analyst can perform complex queries and analysis, such as data mining, on the information without slowing down the operational systems (Ref: Wikipedia). Data warehousing collection of data designed to support management decision making. Data warehouses contain a wide variety of data that present a coherent picture of business conditions at a single point in time. It is a repository of integrated information, available for queries and analysis.
What is Dirty Read?
A dirty read occurs when two operations, say, read and write occur together giving the incorrect or unedited data. Suppose, A changed a row but did not committed the changes. B reads the uncommitted data but his view of the data may be wrong so that is Dirty Read.
What is a Factless Facts Table?
A fact table that does not contain numeric fact columns is called a factless facts table.
Describe an Inner Join
A inner join is a simple join that returns all rows for which there is at least one match in BOTH tables.
What is the Difference between a Local and a Global Temporary Table?
A local temporary table exists only for the duration of a connection, or if defined inside a compound statement, for the duration of the compound statement. A global temporary table remains in the database accessible across the connections. Once the connection where original global table is declared dropped this becomes unavailable.
PRIVATE SYNONYM
A private synonym is owned by the user account that creates it, and by default is only visible within the user account, just as any other object owned by the user. As with any object owned by the user, the user must take steps to grant privileges on owned objects for other user accounts to get access to that object. In other words, a user may choose to make their private synonym visible to other users, but that requires an explicit effort on the user's part to make that happen, as is true for tables, views, and many other objects—but not all objects
What is a Schema?
A schema is a collection of database objects (tables) associated with one particular database username.
What is a View?
A simple view can be thought of as a subset of a table. It can be used for retrieving data as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does the data in the view as views are the way to look at parts of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.
What are Sparse Columns?
A sparse column is another tool used to reduce the amount of physical storage used in a database. They are the ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non-null values.
What is a Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database. e.g. sp_helpdb, sp_renamedb, sp_depends etc.
What is a Surrogate Key?
A surrogate key is a substitution for the natural primary key. It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it should be unique for each row in the table. It is useful because the natural primary key can change and this makes updates more difficult. Surrogated keys are always integer or numeric.
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. (Read more here)
A unique index
A unique index is one that helps ensure that a column in a table will contain unique information.
What is a View?
A view is a virtual table based on the result-set of an SQL statement
What is the Difference between a View and a Materialized View?
A view takes the output of a query and makes it appear like a virtual table; and it can be used in place of tables. A materialized view provides indirect access to table data by storing the results of a query in a separate schema object.
Which statement would you use to add a PRIMARY KEY constraint to a table?
ALTER TABLE ADD CONSTRAINT. The full syntax is ALTER TABLE tablename ADD [CONSTRAINT constraint] type (column);
default position in syntax for adding a column
ALTER TABLE CRUISE_ORDERS ADD (WEATHER_CODE NUMBER(2) DEFAULT 0)
What is Aggregate Functions?
Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values except COUNT function. HAVING clause is used, along with GROUP BY for filtering query using aggregate values. The following functions are aggregate functions. AVG, MIN, CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX, VARP
What happens if you use the UPDATE statement without the WHERE?
All Records are updated.
Which of the following is an example of a structural business rule?
All employees must belong to at least one department
Some database designers write their own SQL statements for creating a database, its tables, and its indexes instead of using the Management Studio. Why?
All of the above (They want to have complete control over how the database is created. The scripts generated by the Management Studio are harder to understand. It's easier to modify your own script if you want to use it to create the same database for another database management system later on.)
HAVING clause, GROUP BY
Allows you to specify conditions on the rows for each group- in other words, which rows should be selected will be based on the conditions you specify needs to follow GROUP BY clause
What happens when the = operator is used with a multiple-row subquery?
An ORA-01427: single-row subquery returns more than one row error occurs.
What is Active Data Warehousing?
An active data warehouse provides information that enables decision-makers within an organization to manage customer relationships nimbly, efficiently and proactively.
What are Aggregate Tables?
An aggregate table contains the summary of existing warehouse data, which is grouped to certain levels of dimensions. It is always easy to retrieve data from aggregated tables than visiting original table which has millions of records. Aggregate tables reduce the load in the database server and improve the performance of the query, and they also can retrieve the result quickly.
What is an Execution Plan? When would you Use it? How would you View the Execution Plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query, and it is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. Within the Query Analyzer, there is an option called "Show Execution Plan" (in the Query drop-down menu). If this option is turned on, it will display query execution plan in a separate window when the query is ran again.
What is an Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan, the SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.
What is the Difference between Index Seek vs. Index Scan?
An index scan means that SQL Server reads all the rows in a table, and then returns only those rows that satisfy the search criteria. When an index scan is performed, all the rows in the leaf level of the index are scanned. This essentially means that all the rows of the index are examined instead of the table directly. This is sometimes compared to a table scan, in which all the table data is read directly. However, there is usually little difference between an index scan and a table scan. An index seek, on the other hand, means that the Query Optimizer relies entirely on the index leaf data to locate rows satisfying the query condition. An index seek will be most beneficial in cases where a small percentage of rows will be returned. An index seek will only affect the rows that satisfy a query condition and the pages that contain these qualifying rows; in terms of performance, this is highly beneficial when a table has a very large number of rows.
METHACARACTER DOT .
Any Character -- Dot, Matches any character
rollback that names non-exits savepoint
Any ROLLBACK that names non-existing SAVEPOINTs will not execute.
To visually represent exclusivity between two or more relationships in an ERD you would most likely use a/an ________.
Arc
What does the AUTO INCREMENT used for?
Auto-increment allows a unique number to be generated when a new record is inserted into a table.
What languages BI uses to achieve the goal?
BI uses following languages for achieve the Goal. MDX - Multidimensional Expressions: This language is used for retrieving data from SSAS cubes. It looks very similar to T-SQL, but it is very different in the areas of conceptualization and implementation. DMX - Data Mining Extensions: This is again used for SSAS, but rather than cubes it is used for data mining structures. This language is more complicated than MDX. Microsoft has provided many wizards in its BI tools, which further reduced number of experts for learning this language, which deals with data mining structures. XMLA - XML for Analysis: This is mainly used for SSAS administrative tasks. It is quite commonly used in administration tasks such as backup or restore database, copy and move database, or for learning Meta data information. Again, MS BI tools provide a lot of wizards for the same. (Read More Here)
What is BUS Schema?
BUS Schema consists of a master suite of confirmed dimension and standardized definition of facts.
"If the statement SELECT * FROM emp WHERE UPPER(last_name) = :x; was executed many times in your application, what would you consider doing to improve performance?"
Build a function based index on UPPER(last_name).
What is Business Intelligence (BI)?
Business Intelligence (BI) refers to technologies, applications and practices for the collection, integration, analysis, and presentation of business information and sometimes to the information itself. The purpose of BI is to support better business decision making. Thus, BI is also described as a decision support system (DSS). BI systems provide historical, current, and predictive views of business operations, most often using data that has been gathered into a data warehouse or a data mart and occasionally working from operational data.
constraint type
C (check constraint on a table) P (primary key) U (unique key) R (referential integrity) V (with check option, on a view) O (with read only, on a view
What is CHECKPOINT Process in the SQL Server?
CHECKPOINT process writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk.
Write an aggregate expression for the number of unique values in the VendorID column
COUNT(DISTINCT VendorID)
What is the CPU Pressure?
CPU pressure is a state wherein the CPU is fully occupied with currently assigned tasks and there are more tasks in the queue that have not yet started.
Before you can pass a table to a stored procedure or a function as a parameter, which statement do you use to create a user-defined table type?
CREATE
index (syntax)
CREATE INDEX IX_INV_INVOICE_DATE ON INVOICES(INVOICE_DATE);
how to create index
CREATE INDEX index_name ON table_name(column_name)
Which of the following statements creates a SQL Server login ID for a user named TomBrown with the password 'abc123XYZ'.
CREATE LOGIN TomBrown WITH PASSWORD = 'abc123XYZ';
Which of the following statements creates a login ID for a Windows user named AliceJackson in a domain named Sales?
CREATE LOGIN [Sales\AliceJackson] FROM WINDOWS;
Which statement can be used to alter a view?
CREATE OR REPLACE VIEW
Create the database link.
CREATE PUBLIC DATABASE LINK hq.acme.com USING 'sales'; SELECT * FROM [email protected];
creating public synonym (syntax)
CREATE PUBLIC SYNONYM CO FOR CRUISE_ORDERS;
RESTORE POINT (syntax)
CREATE RESTORE POINT balance_acct_01
Which statement can you use to create a user-defined database role?
CREATE ROLE
Which system privilege allows a user to connect to the database?
CREATE SESSION
Which of the following statements can be coded in a batch with other statements?
CREATE TABLE
How to Create Primary Key with Specific Name while Creating a Table?
CREATE TABLE [dbo].[TestTable]( [ID] [int] IDENTITY(1,1)NOTNULL, [FirstName] [varchar](100)NULL, CONSTRAINT [PK_TestTable] PRIMARYKEYCLUSTERED ([ID] ASC)) GO
how to add default value when creating the table
CREATE TABLE.... HIRE_DATE date DEFAULT SYSDATE
cube
CUBE goes beyond the functionality of ROLLUP by calculating subtotals for every possible grouping within the columns selected and grouped.
What are Different Types of Collation Sensitivity?
Case sensitivity - A and a, B and b, etc. Accent sensitivity - a and á, o and ó, etc. Kana Sensitivity - When Japanese Kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive. Width sensitivity - When a single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently, it is width sensitive. (Read more here)
What is Catalog Views?
Catalog views return information that is used by the SQL Server Database Engine. Catalog Views are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views.
Schemas:
Collections of objects, such as tables, views, and sequences. A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema
"What command will create a new table which has a similar structure to an existing table, and also include all or some of the rows from that original table?"
Create Table As Select (CTAS)
What should you do to eliminate the need for all database users to qualify an object name with its schema name?
Create a public synonym using CREATEPUBLIC SYNONYM syn_name FOR obj_name;
"The CREATE, ALTER, DROP, RENAME, and TRUNCATE statements are examples of which category of SQL statement?"
DDL
When you use the Management Studio to create a database, including its tables and indexes, the Management Studio actually generates and runs the ____________ statements that are necessary to create the database.
DDL
Code a statement that creates a table variable named @TestTable.
DECLARE @TestTable table;
Which statement removes rows in a table but can be rolled back?
DELETE
Which of the following commands can be used to view the structure of a table?
DESCRIBE
By default, all duplicate values are included in the aggregate calculation, unless you specify which keyword?
DISTINCT
Which keyword used with group functions causes the function to only consider unique values?
DISTINCT
"Which category of SQL statement includes the INSERT, UPDATE, DELETE, and MERGE statements?"
DML
With a MERGE statement, a series of ____ actions can occur with a single SQL statement.
DML
how to drop index
DROP INDEX IX_INV_INVOICE_DATE
Which statement removes an index from the data dictionary?
DROP INDEX indexname;
Which statement removes a sequence from the data dictionary?
DROP SEQUENCE sequence_name;
Which statement would you use to delete a synonym?
DROP SYNONYM synonym_name;
"When using the TO_CHAR function to format dates, which format model element will display the three-letter abbreviation for the day of the week?"
DY
What is the Difference between OLTP and OLAP?
Data Source OLTP: Operational data is from original data source of the data OLAP: Consolidation data is from various sources. Process Goal OLTP: Snapshot of business processes which do fundamental business tasks OLAP: Multi-dimensional views of business activities of planning and decision making Queries and Process Scripts OLTP: Simple quick running queries ran by users. OLAP: Complex long running queries by system to update the aggregated data. Database Design OLTP: Normalized small database. Speed will be not an issue because of a small database, and normalization will not degrade performance. This adopts the entity relationship (ER) model and an application-oriented database design. OLAP: De-normalized large database. Speed is an issue because of a large database and de-normalizing will improve performance as there will be less tables to scan while performing tasks. This adopts star, snowflake or fact constellation mode of subject-oriented database design. Back up and System Administration OLTP: Regular Database backup and system administration can do the job. OLAP: Reloading the OLTP data is considered as a good backup option. What are Normalization Forms? There are different types of normalization forms such as 1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain. 2NF: Eliminate Redundant Data If an attribute depends on only part of a multi-valued key, remove it to a separate table. 3NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key BCNF: Boyce-Codd Normal Form If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables. 4NF: Isolate Independent Multiple Relationships No table may contain two or more 1:n or n:m relationships that are not directly related. 5NF: Isolate Semantically Related Multiple Relationships There may be practical constrains on information that justify separating logically related many-to-many relationships. ONF: Optimal Normal Form A model limited to only simple (elemental) facts, as expressed in Object Role Model notation. DKNF: Domain-Key Normal Form A model free from all modification anomalies. Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.
What is the Difference between OLAP and Data Warehouse?
Data Warehouse is the place where the data is stored for analysis, whereas OLAP is the process of analyzing the data, managing aggregations, partitioning information into cubes for in depth visualization.
"In which specific area of database administration are the keywords CUBE, ROLLUP, and GROUPING SETS are most often used?"
Data Warehousing
Why is Data Modeling Important?
Data modeling is probably the most labor intensive and time consuming part of the development process. The goal of the data model is to make sure that the all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language, it can be reviewed and verified as correct by the end users. In computer science, data modeling is the process of creating a data model by applying a data model theory to create a data model instance. A data model theory is a formal data model description. In data modeling, we are structuring and organizing data. These data structures are then typically implemented in a database management system. In addition to defining and organizing the data, data modeling will impose (implicitly or explicitly) constraints or limitations on the data placed within the structure. Managing large quantities of structured and unstructured data is a primary function of information systems. Data models describe structured data for storage in data management systems such as relational databases. They typically do not describe unstructured data, such as word processing documents, email messages, pictures, digital audio, and video.
What is the Difference between Data Warehousing and Business Intelligence?
Data warehousing deals with all aspects of managing the development, implementation and operation of a data warehouse or data mart, including meta data management, data acquisition, data cleansing, data transformation, storage management, data distribution, data archiving, operational reporting, analytical reporting, security management and backup/recovery planning. Business intelligence, on the other hand, is a set of software tools that enable an organization to analyze measurable aspects of their business such as sales performance, profitability, operational efficiency, effectiveness of marketing campaigns, market penetration among certain customer groups, cost trends, anomalies and exceptions. Typically, the term 'business intelligence' is used to encompass OLAP, data visualization, data mining and query/reporting tools. (Reference: Les Barbusinski)
What is Database Mirroring?
Database mirroring can be used with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients, which is known as the principal database. Updates made by the clients to the principal database are applied to the other copy of the database, known as the mirror database. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database.
What is Normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.
Which of the following is true?
Date values are enclosed in single quotation marks
What is De-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.
What is Difference between DELETE and TRUNCATE Commands?
Delete command removes the rows from a table on the basis of the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table, and there will be no data in the table after we run the truncate command. TRUNCATE TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. (Read all the points below) TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log. TRUNCATE removes all the rows from a table, but the table structure, its columns, constraints, indexes and so on remains. The counter used by an identity for new rows is reset to the seed for the column. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Using T-SQL - TRUNCATE cannot be rolled back unless it is used in TRANSACTION. OR TRUNCATE can be rolled back when used with BEGIN ... END TRANSACTION using T-SQL. TRUNCATE is a DDL Command. TRUNCATE resets the identity of the table. DELETE DELETE removes rows one at a time and records an entry in the transaction log for each deleted row. DELETE does not reset Identity property of the table. DELETE can be used with or without a WHERE clause DELETE activates Triggers if defined on table. DELETE can be rolled back. DELETE is DML Command. DELETE does not reset the identity of the table. (Read more here)
What does the D in CRUD stand for?
Delete, D in CRUD, stands for Delete and is on of the four basic functions of persistent storage.
MONTHS_BETWEEN(d1, d2)
Determines the number of months between the two dates
A non-transferable relationship is represented by which of the following symbols?
Diamond
ALL_COL_COMMENTS
Displays comments for all columns of all tables and views in the database.
ALL_TAB_COMMENTS
Displays comments for all objects in the database.
What is Difference between ER Modeling and Dimensional Modeling?
ER modeling is used for normalizing the OLTP database design. Dimensional modeling is used for de-normalizing the ROLAP/MOLAP design.
What is ETL?
ETL is abbreviation of extract, transform, and load. ETL is software that enables businesses to consolidate their disparate data while moving it from place to place, and it doesn't really matter that that data is in different forms or formats. The data can come from any source. ETL is powerful enough to handle such data disparities. First, the extract function reads data from a specified source database and extracts a desired subset of data. Next, the transform function works with the acquired data - using rules or lookup tables, or creating combinations with other data - to convert it to the desired state. Finally, the load function is used to write the resulting data to a target database.
What is Use of EXCEPT Clause?
EXCEPT clause is similar to MINUS operation in Oracle. The EXCEPT query and MINUS query return all rows in the first query that are not returned in the second query. Each SQL statement within the EXCEPT query and MINUS query must have the same number of fields in the result sets with similar data types.
Which of the following statements executes a stored procedure named spInvoiceCount and stores its return value in a variable named @InvoiceCount? Assume that the @InvoiceCount variable has already been declared and that the stored procedure doesn't accept any parameters.
EXEC @InvoiceCount = spInvoiceCount;
Code example 14-1 USE AP; DECLARE @Date1 smalldatetime; DECLARE @Date2 smalldatetime; SELECT @Date1 = MIN(InvoiceDueDate), @Date2 = MAX(InvoiceDueDate) FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0; IF @Date1 < GETDATE() IF @Date2 < GETDATE() BEGIN PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); PRINT 'Latest past due date: ' + CONVERT(varchar, @Date2, 1); END; ELSE PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); ELSE PRINT 'No invoices past due'; (Refer to code example 14-1.) If the current date is 03/15/16, the earliest invoice due date for invoices with unpaid balances is 02/09/16, and the latest invoice due date for invoices with unpaid balances is 03/20/16, what will be printed by this script?
Earliest past due date: 02/09/16
Code example 14-1 USE AP; DECLARE @Date1 smalldatetime; DECLARE @Date2 smalldatetime; SELECT @Date1 = MIN(InvoiceDueDate), @Date2 = MAX(InvoiceDueDate) FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0; IF @Date1 < GETDATE() IF @Date2 < GETDATE() BEGIN PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); PRINT 'Latest past due date: ' + CONVERT(varchar, @Date2, 1); END; ELSE PRINT 'Earliest past due date: ' + CONVERT(varchar, @Date1, 1); ELSE PRINT 'No invoices past due'; (Refer to code example 14-1.) If the current date is 04/04/16, the earliest invoice due date for invoices with unpaid balances is 02/09/16, and the latest invoice due date for invoices with unpaid balances is 03/20/16, what will be printed by this script?
Earliest past due date: 02/09/16 Latest past due date: 03/20/16
Data Type: Decimal
Exact numerical, precision p, scale s. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal. decimal(6,2)
Example of AUTO INCREMENT syntax:
Example of AUTO INCREMENT syntax: CREATE TABLE Persons ( ID int IDENTITY(1,1) PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
Example of CHECK constraint Syntax:
Example of CHECK constraint Syntax: CREATE TABLE Persons ( P_Id int NOT NULL CHECK (P_Id>0), LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
Example of DEFAULT constraint syntax:
Example of DEFAULT constraint syntax: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' );
Example of DROP VIEW syntax:
Example of DROP VIEW syntax: DROP VIEW view_name
Example of GROUP by Function Syntax:
Example of GROUP by Function Syntax: SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
Example of HAVING Function Syntax:
Example of HAVING Function Syntax: SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value;
Example of Length syntax
Example of Length syntax SELECT LENGTH(column_name) FROM table_name;
Example of MAX Function Syntax:
Example of MAX Function Syntax: SELECT MAX(column_name) FROM table_name;
Example of MIN function Syntax:
Example of MIN function Syntax: SELECT MIN(column_name) FROM table_name;
Example of Sum Function Syntax:
Example of Sum Function Syntax: SELECT MIN(column_name) FROM table_name;
Example of UCASE Function Syntax:
Example of UCASE Function Syntax: SELECT UPPER(column_name) FROM table_name;
Example of a Subquery Syntax
Example of a Subquery Syntax SELECT a.studentid, a.name, b.total_marks FROM student a, marks b WHERE a.studentid = b.studentid AND b.total_marks > (SELECT total_marks FROM marks WHERE studentid = 'V002');
Example of an Average Function Syntax:
Example of an Average Function Syntax: SELECT AVG(column_name) FROM table_name
Example of an INSERT INTO statement
Example of an INSERT INTO statement insert into "tablename" (first_column,...last_column) values (first_value,...last_value);
External Tables
External tables are read-only tables in which the data is stored outside the database in flat files. • The metadata for an external table is created using a CREATE TABLE statement. • With the help of external tables, Oracle data can be stored or unloaded as flat files. • The data can be queried using SQL but you cannot use DML and no indexes can be created.
A foreign key always refers to a primary key in the same table. True or False?
F
A relationship can be both recursive and hierachal at the same time. True or False?
F
A well structured ERD will show only some parts of the finished data model. You should never try to model the entire system in one diagram, no matter how small the diagram might be. True or False?
F
All data models MUST include generic components in order to remain flexible. True or False?
F
Audit trail attributes cannot be placed in the entities they are auditing, they must be placed in separate, new entities, created just for that purpose. True or False?
F
Choosing specific columns to be displayed by a SELECT statement is known as selection
F
Data Modeling is the last step in the database development process. True or False?
F
Each section of a SQL command that begins with a keyword is known as a statement.
F
Entity names are always plural. True or False?
F
Generic models are generally less complex than a specific model. True or False?
F
Group functions return a group of results per row processed.
F
Many-to-Many relationships are perfectly acceptable in a finished ERD. There is no need to do any more work on them. True or False?
F
No database in the world is ever truly on 3rd Normal Form. Everyone always stops after 2nd Normal Form. True or False?
F
No formal rules exist for drawing ERD's. The most important thing is to make sure that all entities, attributes, and relationships are documented on the diagram. The Layout is insignificant. True or False?
F
Non-transferable relationships can only be mandatory, not optional. True or False?
F
Personal computers (PCs) have been in existence since 1950. True or False?
F
Relationships on an ERD can only be transformed into UIDs in the physical model? True or False?
F
Rows cannot be added to a table through a complex view that was created with the ORDER BY clause.
F
Table aliases MUST be used with columns referenced in the JOIN USING clause. True or False?
F
The JOIN keyword is used in the WHERE clause to indicate the tables that should be joined or linked.
F
The SORT BY clause is used for displaying the results of a query in a sorted order.
F
The SORT BY clause is used to present query results in a specific order.
F
The SQL statement ALTER TABLE EMPLOYEES DROP COLUMN SALARY will delete all of the rows in the employees table. True or False?
F
Use MON in the format argument to spell out the specified month.
F
Which type of constraint enforces a relationship between the column and another column in the same or a different table?
FOREIGN KEY
Which clause of the SELECT statement names the table that contains the data to be retrieved?
FROM
What is a Fact Table?
Fact table contains measurements of business process. Fact table contains the foreign keys for the dimension tables. For instance, if your business process is 'paper production', 'average production of paper by one machine' or 'weekly production of paper' will be considered as the measurement of business process.
What is a Filestream?
Filestream allows you to store large objects in the file system and have these files integrated within the database. It enables SQL Server-based applications to store unstructured data such as documents, images, audios and videos in the file system. FILESTREAM basically integrates the SQL Server Database Engine with New Technology File System (NTFS); it basically stores the data in varbinary (max) data type. Using this data type, the unstructured data is stored in the NTFS file system, and the SQL Server Database Engine manages the link between the Filestream column and the actual file located in the NTFS. Using Transact-SQL statements users can insert, update, delete and select the data stored in FILESTREAM-enabled tables.
Adding NOT NULL Columns
For example, if there are already rows in a given table, then you cannot alter that table by adding a new column with the NOT NULL constraint if the data already exists
The transformation from an ER diagram to a physical design involves changing terminology. Relationships in the ER diagram become __________ , and primary unique identifiers become ____________.
Foreign keys, Primary keys
Which of the following isn't a common error when entering and executing SQL statements?
Forgetting to attach the required database
When you create a script for creating a database, which keyword do you use to signal the end of a batch and cause all the statements in the batch to be executed?
GO
Which clause in a SQL expression will enable a single statement to calculate all possible combinations of aggregations?
GROUP BY CUBE
group_by; rollup cube and aliases
GROUP BY clause is processed by SQL before the select list. Therefore it doesn't recognize column aliases created in the select list—this applies to ROLLUP and CUBE as well.
What keyword(s) in a SQL SELECT can be used instead of the keyword CUBE in order to calculate just the aggregations you need from the GROUP BY clause?
GROUPING SETS
What is the name of the function which allows you to determine the exact GROUP BY level when using any of the GROUPING functions?
GROUPING_ID
How do group functions handle NULL values?
Group functions ignore NULL values.
What is a Hint?
Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query.(Read more here) There are three different types of hints. Let us understand the basics of each of them separately. Join Hint This hint is used when more than one table is used in a query. Two or more tables can be joined using different types of joins. This hint forces the type of join algorithm that is used. Joins can be used in SELECT, UPDATE and DELETE statements. Query Hint This hint is used when certain kind of logic has to be applied to a whole query. Any hint used in the query is applied to the complete query as opposed to a part of it. There is no way to specify that only a certain part of a query should be used with the hint. After any query, the OPTION clause is specified to apply the logic to this query. A query always has any of the following statements: SELECT, UPDATE, DELETE, INSERT or MERGE (SQL 2K8); and this hint can be applied to all of them. Table Hint This hint is used when certain kind of locking mechanism of tables has to be controlled. SQL Server query optimizer always puts the appropriate kind of lock on tables, when any of the Transact SQL operations SELECT, UPDATE, DELETE, INSERT or MERGE is used. There are certain cases when the developer knows when and where to override the default behavior of the locking algorithm, and these hints are useful in those scenarios. (Read more here)
Which DML statement only adds rows to a table?
INSERT
conditional insert ALL
INSERT ALL WHEN SAL > 10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL) WHEN MGR > 200 THEN INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id ,hire_date , salary , manager_id FROM employees WHERE employee_id > 200;
conditional insert FIRST
INSERT FIRST WHEN SAL > 25000 THEN INTO special_sal VALUES(DEPTID, SAL) WHEN HIREDATE like ('%00%') THEN INTO hiredate_history_00 VALUES(DEPTID,HIREDATE) WHEN HIREDATE like ('%99%') THEN INTO hiredate_history_99 VALUES(DEPTID, HIREDATE) ELSE INTO hiredate_history VALUES(DEPTID, HIREDATE) SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE FROM employees GROUP BY department_id;
What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?
INSERT TOP (N) INTO Table is faster than Using Top with INSERT but when we use INSERT TOP (N) INTO Table, the ORDER BY clause is totally ignored.
What form of the INSERT statement will insert all rows from a table into two or more other tables?
INSERTALL
How would you code the INSTEAD OF clause for a trigger that's fired whenever a view is deleted?
INSTEAD OF DROP_VIEW
Which set operator returns only results that are common to two queries?
INTERSECT
"Which datatype stores lengths of time represented in days, hours, minutes, and seconds?"
INTERVAL DAY TO SECOND
DROP USER username;
If a user account owns any database objects, the preceding statement won't work, and you'll need to use this: DROP USER username CASCADE
compile view
If a view is invalid, it will require recompilation ALTER VIEW VW_EMPLOYEES COMPILE
sign function
If number < 0, then sign returns -1. If number = 0, then sign returns 0. If number > 0, then sign returns 1.
What will the RIGHT JOIN give if the ON clause doesn't match any records in the left table?
If the ON clause doesn't match any records in the left table, the RIGHT JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.
How can I Get Data from a Database on Another Server?
If you want to import data only through T-SQL query, then use OPENDATASOURCE function. To repeatedly get data from another server, create a linked server and then use the OPENQUERY function or use 4-part naming. If you are not adhered with T-SQL, then it is better to use import/export wizard, and you can save it as a SSIS package for future use.
Scalar subquery expressions cannot be used in the following locations:
In CHECK constraints n In GROUP BY clauses n In HAVING clauses n In a function-based index (which is coming up in Chapter 11) n As a DEFAULT value for a column n In the RETURNING clause of any DML statement n In the WHEN conditions of CASE n In the START WITH and CONNECT BY clauses, which we discuss in
What is OLTP (Online Transaction Processing)?
In OLTP -(online transaction processing) systems, relational database design uses the discipline of data modeling and generally follows the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules, complex information is broken down into its most simple structures (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.
Explain Object Explorer Enhancements:
In Object Explorer Detail, the new feature is Object Search. Enter any object name in the object search box and the searched result will be displayed in the same window as Object Explorer Detail. Additionally, there are new wizards which help you perform several tasks, from policy management to disk monitoring. One cool thing is that everything displayed in the object explorer details screen can be right away copied and pasted to Excel without any formatting issue.
What is Data Compression?
In SQL SERVE 2008, Data Compression comes in two flavors: Row Compression Page Compression Row Compression Row compression changes the format of physical storage of data. It minimize the metadata (column information, length, offsets etc) associated with each record. Numeric data types and fixed-length strings are stored in variable-length storage format, just like Varchar. (Read more here) Page Compression Page compression allows common data to be shared between rows for a given page. It uses the following techniques to compress data: Row compression. Prefix Compression. For every column in a page, duplicate prefixes are identified. These prefixes are saved in compression information headers which resides after the page header. A reference number is assigned to these prefixes and that reference number is replaced where ever those prefixes are being used. Dictionary Compression Dictionary compression searches for duplicate values throughout the page and stores them in CI. The main difference between prefix and dictionary compression is that the former is only restricted to one column while the latter is applicable to the complete page.
How can I Track the Changes or Identify the Latest Insert-Update-Delete from a Table?
In SQL Server 2005 and earlier versions, there is no inbuilt functionality to know which row was recently changed and what the changes were. However, in SQL Server 2008, a new feature known as Change Data Capture (CDC) has been introduced to capture the changed data.
What is CLR?
In SQL Server 2008, SQL Server objects such as user-defined functions can be created using such CLR languages. This CLR language support extends not only to user-defined functions, but also to stored procedures and triggers. You can develop such CLR add-ons to SQL Server using Visual Studio 2008.
What Snow Flake Schema?
In Snowflake Schema, each dimension has a primary dimension table, to which one or more additional dimensions can join. The primary dimension table is the only table that can join to the fact table.
What is the DROP statement used for?
Indexes, tables, and databases can easily be deleted/removed with the DROP statement.
set unused columns
Instead of dropping a table column you are no longer using, you may elect to declare it unused and leave it in place. Once you set a column as UNUSED, it is never again available; it is as though it has been dropped. As with dropped columns, any constraints or indices on the column will also be dropped. You will never be able to recover a column that is set to UNUSED. A ROLLBACK statement will have no effect—an UNUSED column will not be recovered in a ROLLBACK operation. Once a column is set to UNUSED, you can add new columns that have the same name as any unused columns for the table.
Data Type: Integer
Integer numerical (no decimal). Precision p int
Which of the following expressions will not compute 10% of the balance due if balance due is the invoice total minus the credit total minus the payment total?
InvoiceTotal - CreditTotal - PaymentTotal / 10
When coded in a WHERE clause, which of the following search conditions will not return a result set that includes all invoices with an InvoiceTotal value of $1000 or less?
InvoiceTotal IN (0,1000)
Why is it important to identify and document business rules?
It allows you to create your data model, then check for accuracy.
How do you know when to use the different types of time in your design?
It depends on the functional needs of the system.
with clause
It is used only with SELECT statements. • A query name is visible to all WITH element query blocks (including their subquery blocks) defined after it and the main query block itself (including its subquery blocks). • When the query name is the same as an existing table name, the parser searches from the inside out, the query block name takes precedence over the table name. • The WITH clause can hold more than one query. Each query is then separated by a comma.
What is the function of the NOCYCLE keyword in a hierarchical query?
It prevents the query from aborting at run time due to an infinite loop
Which statements are true about the ROUND function?
It returns the number rounded to the specified precision
Reversing the order of the SELECT statements when using the INTERSECT operator has what effect on the results?
It will not affect the results.
What is Data type: char(size)
It's the Fixed-length character string. Size specified in parentheses. 255
With the following data in Employees (last_name, commission_pct, manager_id) what is the result of the following statement? DATA: King, null, null Kochhar, null, 100 Vargas, null, 124 Zlotkey, .2, 100
King, -1 Kochhar, 100 Vargas, 124 Zlotkey, .2
LAST_DAY
LAST_DAY(d) Returns the last day of the month in which d falls
Which date function returns the ending date of the month containing a given date?
LAST_DAY. The syntax of the LAST_DAY function is LAST_DAY(date)
Which function would you use to retrieve data from a subsequent row in a result set? And which function would you use to retrieve data from a previous row?
LEAD, LAG
After locating the characters that separate the components of a string you wish to parse, you can use which functions to extract the individual components?
LEFT, RIGHT, SUBSTRING, and LEN
Which function returns the number of characters in a column or character string?
LENGTH. The syntax of the LENGTH function is LENGTH(column|expression)
What pseudocolumn name is available for use in a hierarchical query which permits you to control the number of generations in the hierarchy which should be selected for output?
LEVEL
Which keyword can you use to retrieve rows in which an expression matches a string pattern called a mask?
LIKE
What is LINQ?
Language Integrated Query (LINQ) adds the ability to query objects using .NET languages. The LINQ to SQL object/relational mapping (O/RM) framework provides the following basic features: Tools to create classes (usually called entities) mapped to database tables Compatibility with LINQ's standard query operations The DataContext class with features such as entity record monitoring, automatic SQL statement generation, record concurrency detection, and much more
What is a Level of Granularity of a Fact Table?
Level of granularity means the level of detail that you put into the fact table in a data warehouse. Level of granularity implies the detail you are willing to put for each transactional fact.
What is Difference between Line Feed (\n) and Carriage Return (\r)?
Line Feed - LF - \n - 0x0a - 10 (decimal) Carriage Return - CR - \r - 0x0D - 13 (decimal) DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10) PRINT ('SELECT FirstLine AS FL ' +@NewLineChar + 'SELECT SecondLine AS SL' )
What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server databases using T-SQL Statements. With a linked server, you can create very clean, easy-to-follow SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedures sp_addlinkedserver, sp_addlinkedsrvlogin will be used to add new Linked Server. (Read more here)
Write an aggregate expression to find the latest date in the InvoiceDate column
MAX(InvoiceDate)
How to Rebuild the Master Database?
Master database is system database and it contains information about running server's configuration. When SQL Server 2005 is installed, it usually creates master, model, msdb, tempdb, resourcedb and the distribution system database by default. Only the Master database is the one which is absolutely a must-have database. Without the Master database, the SQL Server cannot be started. This is the reason why it is extremely important to backup the Master database. To rebuild the Master database, run Setup.exe, verify, and repair a SQL Server instance, and rebuild the system databases. This procedure is most often used to rebuild the master database for a corrupted installation of SQL Server.
Which pseudocolumn retrieves the next available sequence value?
NEXTVAL
If you omit both NULL and NOT NULL from the list of column attributes in a CREATE TABLE statement, which is the default setting?
NULL
When sorting the results in ascending order, which of the following values will be presented last in the output?
NULL
What happens if there is no match on the left side with the RIGHT JOIN?
NULL - The result is NULL in the left side when there is no match.
What happens on the LEFT JOIN if there is no match in the right side?
NULL - The result is NULL in the right side when there is no match.
If it is possible for a subquery to return a NULL value to the outer query for comparison, the ____ function should be used to substitute an actual value for the NULL.
NVL
Which function replaces a NULL value with a specified value?
NVL
What are the three properties that every relationship should have?
Name, optionality, cardinality
"In a hierarchical query, is it possible for a row to have more than one immediate ancestor?"
No
Can a row be both the child and parent of a given row?
No
Which of the following is the definition for Third Normal Form?
No non-UID attribute can be dependent on another non-UID attribute
In an INSERT INTO are all rows affected?
No, Any existing rows in the target table are unaffected.
Is the SELECT TOP supported by all database systems?
No, Not all database systems support the SELECT TOP clause.
implicit schema
NoSQL dbs are sometimes called schema-less -denormalized, not 1NF!
[^ ... ]
Non-Matching Character List Matches any character not in list ...
USER_
Objects owned by the current user accessing the view.
preventing view for update insert delete
Omission of any required columns in that underlying table n GROUP BY or any other aggregation, such as set operators (which we discuss in Chapter 12) or hierarchical queries (discussed in Chapter 16) n DISTINCT n A FROM clause that references more than one table—that is, subqueries in the SELECT, or most joins Regarding that last item—it is technically
VERSIONS_OPERATION
Operation performed by the transaction: I for insertion, D for deletion, or U for update. The version is that of the row that was inserted, deleted, or updated; that is, the row after an INSERT operation, the row before a DELETE operation, or the row affected by an UPDATE operation. For user updates of an index key, Oracle Flashback Version Query might treat an UPDATE operation as two operations, DELETE plus INSERT, represented as two version rows with a D followed by an I
"What two constraints, when created, also cause an index to be created?"
PRIMARY KEY and UNIQUE
"What keyword must be included in the equality condition in a hierarchical query, on one side or the other of the equal sign?"
PRIOR
When coded in a WHERE clause, which search condition will return invoices when PaymentDate is not null and InvoiceTotal is greater than or equal to $500?
PaymentDate IS NOT NULL AND InvoiceTotal >= 500
"What is the advantage of writing a multitable INSERT command that will insert rows from table_a into either tablex, tabley, or tablez based upon some condition, as opposed to executing three separate INSERT statements to insert rows one table at a time?"
Performance. The multitable INSERT needs to make only one pass of the data in table_a to get the job done.
What is Policy Management?
Policy Management in SQL SERVER 2008 allows you to define and enforce policies for configuring and managing SQL Server across the enterprise. Policy-Based Management is configured in SQL Server Management Studio (SSMS). Navigate to the Object Explorer and expand the Management node and the Policy Management node; you will see the Policies, Conditions, and Facets nodes.
Within the Management Studio, you can build a SQL statement without having to write your own code by using the ________________.
Query Designer
Which two older datatypes hold raw binary data?
RAW and LONG RAW
Which regular expression function can be used with a CHECK constraint on a column of a table in order to insure the specific format of the data entered for that column?
REGEXP_LIKE
DML operations are not allowed on a view that includes the pseudo column ____.
ROWNUM
What is ROW_NUMBER()?
ROW_NUMBER() returns a column as an expression that contains the row's number within the result set. This is only a number used in the context of the result set; if the result changes, the ROW_NUMBER() will change.
What is RAISEERROR?
RaiseError generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY...CATCH construct.
What are Ranking Functions?
Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic. The different Ranking functions are as follows: ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>) Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. RANK () OVER ([<partition_by_clause>] <order_by_clause>) Returns the rank of each row within the partition of a result set. DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>) Returns the rank of rows within the partition of a result set, without any gaps in the ranking.
What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage. (Read more here)
What are the Properties of the Relational Tables?
Relational tables have the following six properties: Values are atomic. Column values are of the same kind. Each row is unique. The sequence of columns is insignificant. The sequence of rows is insignificant. Each column must have a unique name.
In Management Studio, if a statement returns data, that data is displayed in the __________ tab.
Results tab
What does the ABS(x) return?
Returns the absolute value of x
What does the FLOOR(x) return?
Returns the largest integer value that is less than or equal to x
What does the POWER(x,y) return?
Returns the value of x raised to the power of y
What does the ROUND(x) return?
Returns the value of x rounded to the nearest whole integer
Why can't I use Outer Join in an Indexed View?
Rows can logically disappear from an indexed view based on OUTER JOIN when you insert data into a base table. This makes incrementally updating OUTER JOIN views relatively complex to implement, and the performance of the implementation would be slower than for views based on standard (INNER) JOIN.(Read More Here)
Which statement creates a marker in the current transaction to allow you to rollback only a portion of the changes within the transaction?
SAVEPOINT. The syntax is SAVEPOINT name;
What are Slowly Changing Dimensions (SCD)?
SCD is the abbreviation of slowly changing dimensions. SCD applies to cases where the attribute for a record varies over time. There are three different types of SCD. SCD1: The new record replaces the original record. Only one record exists in database - current data. SCD2: A new record is added into the customer dimension table. Two records exist in the database - current data and previous history data. SCD3: The original data is modified to include new data. One record exists in database - new information is attached with old information in same row.
Example of TABLE JOIN syntax
SELECT "list-of-columns" FROM table1,table2 WHERE "search-condition(s)"
order by (sorting by reference)
SELECT 'Individual' CONTACT_CATEGORY, 02 LAST_NAME || ', ' || FIRST_NAME POINT_OF_CONTACT 03 FROM CRUISE_CUSTOMERS 04 UNION 05 SELECT CATEGORY, 06 VENDOR_NAME 07 FROM VENDORS 08 ORDER BY POINT_OF_CONTACT;
Flashback Query
SELECT * FROM CHAT AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '0 0:01:30' DAY TO SECOND; CHAT_ID
Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will retrieve all books published in 2001?
SELECT * FROM books WHERE pubdate BETWEEN '01-JAN-01' AND '31-DEC-01'; SELECT * FROM books WHERE pubdate LIKE '%01'; SELECT * FROM books WHERE pubdate >= '01-JAN-01' AND pubdate <= '31-DEC-01';
Name 3 ways to get an Accurate Count of the Number of Records in a Table?
SELECT * FROM table1 SELECT COUNT(*) FROM table1 SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
Flashback Version Query (syntax)
SELECT * FROM tablename VERSIONS BETWEEN TIMESTAMP timestamp_expression1AND timestamp_expression2
use the RAWTOHEX conversion
SELECT CHAT_ID, VERSIONS_OPERATION, RAWTOHEX(VERSIONS_XID) FROM CHAT VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE WHERE CHAT_ID = 1 ORDER BY VERSIONS_OPERATION DESC
Based upon the contents of the BOOKS tables, which of the following SQL statements will return the number of different publishers represented in the table?
SELECT COUNT(DISTINCT pubid) FROM books;
how to get current scn from database
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL
Based upon the contents of the BOOKS table, which of the following SQL statements will display five rows in its results?
SELECT DISTINCT pubid FROM books;
extract
SELECT EXTRACT ([YEAR] [MONTH][DAY] [HOUR] [MINUTE][SECOND] [TIMEZONE_HOUR] [TIMEZONE_MINUTE] [TIMEZONE_REGION] [TIMEZONE_ABBR] FROM [datetime_value_expression] [interval_value_expression]);
Based on the contents of the CUSTOMERS table, which SQL statement will display the customers residing in the same state as customer#1013?
SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE customer#=1013);
Which of the following SQL statements will display the gift that should be sent to any customer who orders the book titled SHORTEST POEMS?
SELECT gift FROM promotion, books WHERE retail BETWEEN minretail AND maxretail AND title = 'SHORTEST POEMS';
Which of the following SQL statements will display the title and cost of each book stored in the BOOKS table, as well as the name of the contact person and the phone number to call to reorder the book?
SELECT title, cost, contact, phone FROM publisher JOIN books USING (pubid);
Which option of the ALTER TABLE statement would you use to mark one or more columns in a table as being obsolete so that they may be dropped later?
SET UNUSED. The syntax is ALTER TABLE tablename SET UNUSED (column);
Which of the following format model elements can be used to spell out a number?
SP
What is an SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing very slowly. Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.
Explain Few of the New Features of SQL Server 2008 Management Studio
SQL Server 2008 Microsoft has upgraded SSMS with many new features as well as added tons of new functionalities requested by DBAs for long time. A few of the important new features are as follows: IntelliSense for Query Editing Multi Server Query Query Editor Regions Object Explorer Enhancements Activity Monitors
A graphical tool that you can use to start and stop the database server is called what?
SQL Server Configuration Manager
What is the Maximum Size per Database for SQL Server Express?
SQL Server Express supports a maximum size of 4 GB per database, which excludes all the log files. 4 GB is not a very large size; however, if the database is properly designed and the tables are properly arranged in a separate database, this limitation can be resolved to a certain extent.
Can SQL Servers Linked to other Servers like Oracle?
SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link, e.g. Oracle has an OLE-DB provider that Microsoft provides to add it as a linked server to the SQL Server group
How will you Handle Error in SQL SERVER 2008?
SQL Server now supports the use of TRY...CATCH constructs for providing rich error handling. TRY...CATCH lets us build error handling at the level we need, in the way we need to by setting a region where if any error occurs, it will break out of the region and head to an error handler. The basic structure is as follows: BEGIN TRY <code> END TRY BEGIN CATCH <code> END CATCH So if any error occurs in the TRY block, then execution is diverted to the CATCH block, and the error can be resolved.
Common Questions Asked Which TCP/IP port does the SQL Server run on? How can it be Changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties -> Port number, both on client and the server.
What is SQL Injection? How to Protect Against SQL Injection Attack?
SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker. Here are few methods which can be used to protect again SQL Injection attack: Use Type-Safe SQL Parameters Use Parameterized Input with Stored Procedures Use the Parameters Collection with Dynamic SQL Filtering Input parameters Use the escape character in LIKE clause Wrapping Parameters with QUOTENAME() and REPLACE()
To view the code that's generated for the view, you would use what?
SQL pane
When using the Query Designer, where is the generated SQL statement displayed?
SQL pane
What are Basics of Policy Management?
SQL server 2008 has introduced a policy management framework, which is the latest technique for SQL server database engine. SQL policy administrator uses SQL Server Management Studio to create policies that can handle entities on the server side like the SQL Server objects and the instance of SQL Server databases. It consists of three components: policy administrators (who create policies), policy management, and explicit administration. Policy-based management in SQL Server assists the database administrators in defining and enforcing policies that tie to database objects and instances. These policies allow the administrator to configure and manage SQL server across the enterprise.
Explain MultiServer Query:
SSMS 2008 has a feature to run a query on different servers from one query editor window. First of all, make sure that you registered all the servers under your registered server. Once they are registered, right click on server group name and click New Query. e.g. for server version information, SELECT SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('ProductLevel') AS ProductLevel, SERVERPROPERTY('ProductVersion') AS ProductVersion
Which clause in a hierarchical query specifies the root row of the hierarchy?
START WITH
Which clause of a CREATESEQUENCE statement specifies the starting value for the sequence?
START WITH n
Which of the following would best be represented by an arc?
STUDENT ( University, Trade School)
What is the STUFF Function and How Does it Differ from the REPLACE Function?
STUFF function is used to overwrite existing characters using this syntax: STUFF (string_expression, start, length, replacement_characters), where string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string. REPLACE function is used to replace existing characters of all occurrences. Using the syntax REPLACE (string_expression, search_string, replacement_string), every incidence of search_string found in the string_expression will be replaced with replacement_string.
Which function returns the specified number of characters from the string starting at the specified position?
SUBSTRING
Which group function computes a total for a group of rows?
SUM
What is a Scheduled Job or What is a Scheduled Task?
Scheduled tasks let user automate processes that run on regular or predictable cycles. User can schedule administrative tasks, such as cube processing, to run during times of slow business activity. User can also determine the order in which tasks run by creating job steps within a SQL Server Agent job, e.g. back up database and update statistics of the tables. Job steps give user control over flow of execution. If one job fails, then the user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.
REGEXP_LIKE
Searches a character column for a pattern. Use this function in the WHERE clause of a query to return rows matching a regular expression. The condition is also valid in a constraint or as a PL/SQL function returning a boolean. The following WHERE clause filters employees with a first name of Steven or Stephen: WHERE REGEXP_LIKE(first_name, '^Ste(v|ph)en$')
REGEXP_INSTR
Searches a string for a given occurrence of a regular expression pattern and returns an integer indicating the position in the string where the match is found. You specify which occurrence you want to find and the start position. For example, the following performs a boolean test for a valid email address in the email column: REGEXP_INSTR(email, '\w+@\w+(\.\w+)+') > 0
REGEXP_REPLACE
Searches for a pattern in a character column and replaces each occurrence of that pattern with the specified string. The following function puts a space after each character in the country_name column: REGEXP_REPLACE(country_name, '(.)', '\1 ')
What is the Difference between Seek Predicate and Predicate?
Seek Predicate is the operation that describes the b-tree portion of the Seek. Predicate is the operation that describes the additional filter using non-key columns. Based on the description, it is very clear that Seek Predicate is better than Predicate as it searches indexes, whereas in Predicate, the search is on non-key a column - which implies that the search is on the data in page, files itself.
What is Standby Servers? Explain Types of Standby Servers.
Standby Server is a type of server that can be brought online in a situation when Primary Server goes offline and application needs continuous (high) availability of the server. There is always a need to set up a mechanism where data and objects from primary server are moved to secondary (standby) server. This mechanism usually involves the process of moving backup from the primary server to the secondary server using T-SQL scripts. Often, database wizards are used to set up this process. Different types of standby servers are given as follows: 1) Hot Standby: Hot Standby can be achieved in the SQL Server using SQL Server 2005 Enterprise Edition and the later enterprise versions. SQL Server 2005 has introduced Mirroring of database that can be configured for automatic failover in a disaster situation. In the case of synchronous mirroring, the database is replicated to both the servers simultaneously. This is a little expensive but provides the best high availability. In this case, both primary and standby servers have same data all the time. 2) Warm Standby: In Warm Standby, automatic failover is not configured. This is usually set up using Log Shipping or asynchronous mirroring. Sometimes warm standby is lagging by a few minutes or seconds, which results into loss of few latest updates when the primary server fails and secondary server needs to come online. Sometimes a warm standby server that is lagging by a few transactions is brought back to the current state by applying the recent transaction log. 3) Cold Standby: Code Standby servers need to be switched manually, and sometimes all the backups as well as the required OS need to be applied. Cold Standby just physically replaces the previous server. (Read more here)
What are the Advantages of Using Stored Procedures?
Stored procedure can reduced network traffic and latency, boosting application performance. Stored procedure execution plans can be reused; they staying cached in SQL Server's memory, reducing server overhead. Stored procedures help promote code reuse. Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients. Stored procedures provide better security to your data.
Data Type: Time
Stores hour, minute, and second values time
What is subquery? Explain the Properties of a Subquery?
Subqueries are often referred to as sub-selects as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A subquery is executed by enclosing it in a set of parentheses. Subqueries are generally used to return a single row as an atomic value although they may be used to compare values against multiple rows with the IN keyword. A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a resultset. This implies that a subquery SELECT statement can stand alone, and it does not depend on the statement in which it is nested. A subquery SELECT statement can return any number of values and can be found in the column list of a SELECT statement, and FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A subquery can also be used as a parameter to a function call. Basically, a subquery can be used anywhere an expression can be used. (Read more here)
What are Synonyms?
Synonyms give you the ability to provide alternate names for database objects. You can alias object names; for example, using the Employee table as Emp. You can also shorten names. This is especially useful when dealing with three and four part names; for example, shortening server.database.owner.object to object.
CAST
Syntax: CAST(e AS d) Parameters: e is an expression; d is a datatype. Process: Converts e to d. Particularly useful for converting text representations of datetime information into datetime formats, particularly TIMESTAMP WITH LOCAL TIME ZONE. Output: A value in the d datatype.
A table does not have to have a primary key. True or False?
T
A(n) non-equality join is used when there are no equivalent rows of data in the tables that are being joined.
T
All attributes that are part of the UID are mandatory. True or False?
T
All instances of a subtype must be an instance of the supertype. True or False?
T
Attributes are written inside the entity to which they belong. True or False?
T
If subtypes are listed, a supertype should have at least two subtypes. True or False?
T
In Oracle11g, a MERGE statement compares data between two tables and can perform a series of DML actions to assist in synchronizing the data of the two tables.
T
In a payroll system, it is desirable to have an entity called DAY with a holiday attribute when you want to track special holidays. True or False?
T
In an ERD, it is a good idea to group your entities according to the expected volumes. By grouping high volume entities together, the diagrams could become easier to read. True or False?
T
Modeling historical data can produce a unique identifier that includes a date. True or False?
T
Multiple-row subqueries are nested queries that can return more than one row of results to the parent query.
T
Oracle was one of the first relational database systems available commercially. True or False?
T
Rows cannot be added to a table through a complex view that is based on a group function.
T
The AVG function only includes non-NULL values in its calculations.
T
The percent sign (%) and underscore (_) symbols can be used with the LIKE comparison operator to create a search pattern.
T
Example of Creating an External Table (syntax)
TABLE oldemp ( empno NUMBER, empname CHAR(20), birthdate DATE) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY emp_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE BADFILE 'bad_emp' LOGFILE 'log_emp' FIELDS TERMINATED BY ',' (empno CHAR, empname CHAR, birthdate CHAR date_format date mask "dd-mon-yyyy")) LOCATION ('emp1.txt')) PARALLEL 5 REJECT LIMIT 200;
What do you mean by TABLESAMPLE?
TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set.
Examine the following entity and decide which attribute breaks the 2nd Normal Form rule: ENTITY: CLASS ATTRIBUTES: CLASS ID DURATION SUBJECT TEACHER NAME AND ADDRESS
TEACHER NAME AND ADDRESS
Which statement can you use to manually raise an error within a stored procedure?
THROW (Technically that should be 'raise an exception'...)
Which datatype is an extension of the DATE datatype and stores a fractional second value?
TIMESTAMP
LOCAL_TIMESTAMP (TIMESTAMP_precision)
TIMESTAMP_precision is an optional argument that specifies the fractional second precision of the TIMESTAMP value returned.
Which option of the ROLLBACK statement should you include to roll back only the changes after a particular savepoint?
TO SAVEPOINT. The syntax is ROLLBACK TO SAVEPOINT name;
Which value will the NOT logical condition return when the condition following it is false?
TRUE
What does an Average Function return?
The AVG() function returns the average value of a numeric column.
"When using the LIKE operator, which option must you include to use the percent (%) and underscore (_) characters as literal values?"
The ESCAPE option identifies a special escape character that when preceding these characters causes them to be interpreted literally.
What does the Last Function Return?
The LAST() function returns the last value of the selected column. Only in MS Access
What does the Lower Case Function do?
The LCASE() function converts the value of a field to lowercase.
What does the Max Function Return?
The MAX() function returns the largest value of the selected column.
What does the Min Function Return?
The MIN() function returns the smallest value of the selected column.
A barrred Relationship will result in a Foreign Key column that also is part of:
The Primary Key
What is an SQL Server Agent?
The SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts.
What does TOP Operator Do?
The TOP operator is used to specify the number of rows to be returned by a query. The TOP operator has new addition in SQL SERVER 2008 that it accepts variables as well as literal values and can be used with INSERT, UPDATE, and DELETE statements.
What is Use of DBCC Commands?
The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server. DBCC commands are used to perform the following tasks. Maintenance tasks on database, index, or filegroup. Tasks that gather and display various types of information. Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages. Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
What does the Upper Case Function do?
The UCASE() function converts the value of a field to uppercase.
What is the UNION operator used for?
The UNION operator is used to combine the result-set of two or more SELECT statements.
What does the UNIQUE Constraint do?
The UNIQUE constraint uniquely identifies each record in a database table.
What is the WHERE Clause used for?
The WHERE clause is used to extract only those records that fulfill a specified criterion.
combination of several set operators which includes parentheses
The code enclosed is a standalone query and does not include an ORDER BY clause. n The enclosed code is placed into the outer query as though it were a single, standalone SELECT statement, without the ORDER BY clause. n If an ORDER BY is desired, it must be the final clause in the entire series of statements.
Code example 5-1 SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty, SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName; (Please refer to code example 5-1.) Although this query runs as coded, it contains this logical error:
The column name for the fifth column in the result set doesn't match the data
What are the Advantages of Policy Management?
The following advantages can be achieved by appropriate administration of policy management system. It interacts with various policies for successful system configuration. It handles the changes in the systems that are the result of configuration against authoring policies. It reduces the cost of ownership with simple elaboration of administration tasks. It detects various compliance issues in SQL Server Management Studio.
clob datatype
The name "CLOB" is an abbreviation for Character Large OBject. CLOB accepts large text data elements. Declaration is made without precision or scale. Maximum size is calculated in the same manner that it is for the BLOB datatype
set operators (rules)
The number of expressions selected in the select lists must be identical ineach SELECT statement. - The datatypes of each expression must match, so that each SELECT statement's first expression shares the same datatype group with the other first expressions, and each second expression shares the same datatype group with the other second expressions, etc. By datatype group, we mean datatypes that are either identical or can be made to be identical by SQL through automatic datatype conversion. - Large datatypes such as BLOB and CLOB cannot be used. - The ORDER BY clause cannot be included in the SELECT statements—except for the final SELECT statement
Optimizer
The optimizer processes all SQL statements to determine the best course of action in determining the result.
How do you recognize a correlated subquery?
The outer query makes reference to the table name in the inner query.
What is the result of a FULL JOIN?
The result of a FULL JOIN is equivalent to performing a UNION of the results of left and right outer queries. ex: Produces the set of all records in Table A and Table B, with matching records from both sides if available. If no match, missing side will contain null.
What will be the results of a RIGHT JOIN
The results of a RIGHT JOIN will contain all records from the right table, even if the JOIN condition doesn't find any matching records in the left table.
What is the default order of precedence for set operators?
The set operators have equal precedence and thus are evaluated from left to right.
"What is the effect of using the GROUP BY and ROLLUP keywords in the statement GROUP BY expr1, ROLLUP(expr2, expr3)?"
The subtotals calculated will be all the various combinations of expr2 and expr3 while expr1 stays fixed.
Which of the statements below best describes the result set returned by this SELECT statement? SELECT VendorID, SUM(InvoiceTotal - PaymentTotal - CreditTotal) AS Column2 FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 GROUP BY VendorID;
The total unpaid balance due for each VendorID
How is the type DATETIME in SQL structured?
The type DATETIME is formatted like: YYYY-MM-DD HH:MI:SS
How is the type SMALLDATETIME in SQL structured?
The type SMALLDATETIME is formatted like: YYYY-MM-DD HH:MI:SS
How is the type TIMESTAMP in SQL structured?
The type TIMESTAMP is formatted like: A Unique Number
What does limit the user access to the Database?
The user access is limited by the user's privileges within the database, the user has control over objects that are created, manipulated, and deleted.
What is Difference between Commit and Rollback when Used in Transactions?
The usual structure of the TRANSACTION is as follows: BEGIN TRANSACTION Operations COMMIT TRANSACTION or ROLLBACK TRANSACTION When Commit is executed, every statement between BEGIN and COMMIT becomes persistent to database. When Rollback is executed, every statement between BEGIN and ROLLBACK are reverted to the state when BEGIN was executed.
What are the Fundamental Stages of Data Warehousing?
There are four different fundamental stages of Data Warehousing. Offline Operational Databases: Data warehouses in this initial stage are developed by simply copying the database of an operational system to an off-line server where the processing load of reporting does not impact on the operational system's performance. Offline Data Warehouse: Data warehouses in this stage of evolution are updated on a regular time cycle (usually daily, weekly or monthly) from the operational systems, and the data is stored in an integrated reporting-oriented data structure Real Time Data Warehouse: Data warehouses at this stage are updated on a transaction or event basis, every time an operational system performs a transaction (e.g. an order or a delivery or a booking) Integrated Data Warehouse: Data warehouses at this stage are used to generate activity or transactions that are passed back into the operational systems for use in the daily activity of the organization.
Explain Activity Monitors:
There are four graphs percent; Processor Time, Waiting Tasks, Database I/O, Batch Requests/Sec All the four tabs provide very important information; however, the one which I refer most is "Recent Expensive Queries." Whenever I find my server running slow or having any performance-related issues, my first reaction is to open this tab and see which query is running slow. I usually look at the query with the highest number for Average Duration. The Recent Expensive Queries monitors only show queries which are in the SQL Server cache at that moment.
How to Optimize Stored Procedure Optimization?
There are many tips and tricks for the same. Here are few: Include SET NOCOUNT ON statement. Use schema name with object name. Do not use the prefix "sp_" in the stored procedure name. Use IF EXISTS (SELECT 1) instead of (SELECT *). Use the sp_executesql stored procedure instead of the EXECUTE statement. Try to avoid using SQL Server cursors whenever possible. Keep the Transaction as short as possible. Use TRY-Catch for error handling.
How to Optimize Stored Procedure Optimization?
There are many tips and tricks for the same. Here are few: Include SET NOCOUNT ON statement. Use schema name with object name. Do not use the prefix "sp_" in the stored procedure name. Use IF EXISTS (SELECT 1) instead of (SELECT *). Use the sp_executesql stored procedure instead of the EXECUTE statement. Try to avoid using SQL Server cursors whenever possible. Keep the Transaction as short as possible. Use TRY-Catch for error handling.
How to Copy the Tables, Schema and Views from one SQL Server to Another?
There are multiple ways to do this. "Detach Database" from one server and "Attach Database" to another server. Manually script all the objects using SSMS and run the script on a new server. Use Wizard of SSMS.
How to Copy Data from One Table to Another Table?
There are multiple ways to do this. 1) INSERT INTO SELECT This method is used when table is already created in the database earlier and data have to be inserted into this table from another table. If columns listed in the INSERT clause and SELECT clause are same, listing them is not required. 2) SELECT INTO This method is used when table is not created earlier and it needs to be created when data from one table must be inserted into a newly created table from another table. The new table is created using the same data types as those in selected columns.
What are the types of Data types for storing a date or a date / time value in a database?
There are some types for storing a date or date / time value in the database: DATE - format YYYY-MM-DD DATETIME - format: YYYY-MM-DD HH:MI:SS SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS TIMESTAMP - format: a unique number
What are Wait Types?
There are three types of wait types, namely, Resource Waits. Resource waits occur when a worker requests access to a resource that is not available because that resource is either currently used by another worker or it's not yet available. Queue Waits. Queue waits occur when a worker is idle, waiting for work to be assigned. External Waits. External waits occur when an SQL Server worker is waiting for an external event.
What are the Authentication Modes in SQL Server? How can it be Changed?
There are two authentication modes in SQL Server. Windows Mode Mixed Mode - SQL and Windows To change authentication mode in SQL Server, go to Start -> Programs- > Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server; then from the Tools menu, select SQL Server Configuration Properties and choose the Security page.
What are the Different Methods of Loading Dimension tables?
There are two different ways to load data in dimension tables. Conventional (Slow): All the constraints and keys are validated against the data before it is loaded; this way data integrity is maintained. Direct (Fast): All the constraints and keys are disabled before the data is loaded. Once data is loaded, it is validated against all the constraints and keys. If data is found invalid or dirty, it is not included in index, and all future processes on this data are skipped.
Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table)?
There is no performance difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table).
name rules (roles usser account)
Therefore, you can create roles with names that are the same as objects within a user account, such as tables and views
Where are SQL server Usernames and Passwords Stored in the SQL server?
They get stored in System Catalog Views, sys.server_principals and sys.sql_logins. However, you will not find password stored in plain text.
When is the use of UPDATE_STATISTICS command?
This command is basically used when a large amount of data is processed. If a large amount of deletions, modifications or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
What SQL dialect does Microsoft SQL Server use?
Transact-SQL
If a relationship can be moved between instances of the entities it connects, it is said to be:
Transferable
NUMTOYMINTERVAL (n, interval_unit)
Transform the number n into a value that represents the interval_unit amount of time.
What is the Difference between a Function and a Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF's can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.
Which set operator returns all the results from two queries after eliminating any duplicates?
UNION
In which set operator are duplicate rows included in the result set?
UNION ALL
Which set operator returns all the results from two queries without eliminating any duplicates?
UNION ALL
What is the difference between UNION and UNION ALL?
UNION The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected. UNION ALL The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. The difference between UNION and UNION ALL is that UNION ALL will not eliminate duplicate rows, instead it just pulls all rows from all the tables fitting your query specifics and combines them into a table.
What does UNION do?
UNION merges the contents of two structurally-compatible tables into a single combined table.
Which type of constraint specifies that every value in the column must be unique or have a NULL value?
UNIQUE
Code a statement that changes the database context to a database named TestDB.
USE TestDB;
object names rules
USER, ROLE, and PUBLIC SYNONYM objects are in their own collective namespace. - TABLE, VIEW, SEQUENCE, PRIVATE SYNONYM, and user-defined TYPE objects have their own unique namespace within a given schema. - INDEX objects have their own namespace within a given schema. - CONSTRAINT objects have their own namespace within a given schema. Objects that share a namespace must have unique names within that namespace. Objects in different namespaces are allowed to have identical names.
Checking Privileges
USER_SYS_PRIVS System privileges granted to the current user n USER_TAB_PRIVS Granted privileges on objects for which the user is the owner, grantor, or grantee n USER_ROLE_PRIVS Roles granted to the current user n DBA_SYS_PRIVS System privileges granted to users and roles n DBA_TAB_PRIVS All grants on objects in the database n DBA_ROLE_PRIVS Roles granted to users and roles n ROLE_SYS_PRIVS System privileges granted to roles n ROLE_TAB_PRIVS Table privileges granted to roles n SESSION_PRIVS Session privileges which the user currently has set
What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it Used for?
Unindexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data. Many times, it is better to drop all the indexes from table and then do bulk of INSERTs and restore those indexes after that.
What does a UNIQUE Constraint provide?
Uniqueness - The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
All of the following statements about application roles are true except for one. Which one?
Unlike a standard database role, an application role can contain only one member.
"How do you insure that values entered in the salary column of the employee table must always be greater than $30,000?"
Use a check constraint indicating salary > 30000.
When do you want to use an Intersect?
Use an Intersect to return only values that are in the first query AND also in the second query.
Which of the following recommendations won't improve the readability of your SQL statements?
Use comments to describe what each statement does.
What are sp_configure Commands and SET Commands?
Use sp_configure to display or change server-level settings. To change the database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement. e.g. sp_CONFIGURE 'show advanced', 0 GO RECONFIGURE GO sp_CONFIGURE GO You can run the following command and check the advanced global configuration settings. sp_CONFIGURE 'show advanced', 1 GO RECONFIGURE GO sp_CONFIGURE GO
Escape
Used in like expresions like '%a%\b' escape \'
What is User-defined Functions? What are the types of User-defined Functions that can be created?
User-defined Functions allow defining its own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type. Different Types of User-Defined Functions created are as follows: Scalar User-defined Function A scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. Inline Table-Value User-defined Function An Inline table-value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables. Multi-Statement Table-Value User-defined Function A multi-statement table-value user-defined function returns a table, and it is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command, you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior encountered while using a stored procedure which can also return record sets. (Read here for example)
What are the Advantages of Using CTE?
Using CTE improves the readability and enables easy maintenance of complex queries. The query can be divided into separate, simple, and logical building blocks, which can be then used to build more complex CTEs until the final result set is generated. CTE can be defined in functions, stored procedures, triggers or even views. After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data.
What is NOLOCK?
Using the NOLOCK query optimizer hint is generally considered a good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken on data when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay (blocking).
What is Difference between Table Aliases and Column Aliases? Do they Affect Performance?
Usually, when the name of the table or column is very long or complicated to write, aliases are used to refer them. e.g. SELECT VeryLongColumnName col1 FROM VeryLongTableName tab1 In the above example, col1 and tab1 are the column alias and table alias, respectively. They do not affect the performance at all.
What is the Difference between VARCHAR and VARCHAR(MAX) Datatypes?
VARCHAR stores variable-length character data whose range varies up to 8000 bytes; varchar(MAX) stores variable-length character data whose range may vary beyond 8000 bytes and till 2 GB. TEXT datatype is going to be deprecated in future versions, and the usage of VARCHAR(MAX) is strongly recommended instead of TEXT datatypes.
What is the difference between CHAR and VARCHAR Datatypes?
VARCHARS are variable length strings with a specified maximum length. If a string is less than the maximum length, then it is stored verbatim without any extra characters, e.g. names and emails. CHARS are fixed-length strings with a specified set length. If a string is less than the set length, then it is padded with extra characters, e.g. phone number and zip codes. For instance, for a column which is declared as VARCHAR(30) and populated with the word 'SQL Server,' only 10 bytes will be stored in it. However, if we have declared the column as CHAR(30) and populated with the word 'SQL Server,' it will still occupy 30 bytes in database.
"When testing each row in the emp table to determine whether the employee has an email, how should the WHERE clause be written?"
WHERE email is NULL
Which clause can you include in a CREATEVIEW statement to ensure that DML operations that would change the result of the view are not allowed?
WITH CHECK OPTION
How to Recompile Stored Procedure at Run Time?
We can Recompile Stored Procedure in two ways. Option 1: CREATE PROCEDURE dbo.PersonAge(@MinAge INT, @MaxAge INT) WITH RECOMPILE AS SELECT* FROM dbo.tblPerson WHERE Age <= @MinAge AND Age >= @MaxAge GO Option 2: EXEC dbo.PersonAge65, 70 WITHRECOMPILE We can use RECOMPILE hint with a query and recompile only that particular query. However, if the parameters are used in many statements in the stored procedure and we want to recompile all the statements, then instead of using the RECOMPILE option with all the queries, we have one better option that uses WITH RECOMPILE during stored procedure creation or execution. This method is not recommended for large stored procedures because the recompilation of so many statements may outweigh the benefit of a better execution plan. (Read more here)
How to Delete Duplicate Rows?
We can delete duplicate rows using CTE and ROW_NUMBER () feature of SQL Server 2005 and SQL Server 2008. e.g. WITH CTE (COl1,Col2, DuplicateCount) AS ( SELECT COl1,Col2, ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount FROM DuplicateRcordTable ) DELETE FROM CTE WHERE DuplicateCount >1
How to Find Index Size for Each Index on Table?
We can use the following query to find the size of index. SELECT * FROM sys.indexes WHERE OBJECT_ID=OBJECT_ID('HumanResources.Shift')
What does it mean to have QUOTED_IDENTIFIER ON? What are the Implications of having it OFF?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all T-SQL rules for identifiers.
What is the Bookmark Lookup and RID Lookup?
When a small number of rows are requested by a query, the SQL Server optimizer will try to use a non-clustered index on the column or columns contained in the WHERE clause to retrieve the data requested by the query. If the query requests data from columns not present in the non-clustered index, then the SQL Server must go back to the data pages to get the data in those columns. Even if the table has a clustered index or not, the query will still have to return to the table or clustered index to retrieve the data. In the above scenario, if table has clustered index, it is called bookmark lookup (or key lookup); if the table does not have clustered index, but a non-clustered index, it is called RID lookup.
"If today is March 12, 2012, under what conditions will the statement SELECT to_date('12-Mar-2012') sysdate FROM dual; produce the number 0 for its output?"
When it was executed at exactly midnight.
Code example 14-2 USE AP; SELECT * INTO #InvoiceCopy FROM Invoices; DECLARE @InvoiceID int, @InvoiceTotal money; DECLARE @Total money; SET @Total = 0; WHILE @Total + (SELECT TOP 1 InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC) <= 200000 BEGIN SELECT TOP 1 @InvoiceID = InvoiceID, @InvoiceTotal = InvoiceTotal FROM #InvoiceCopy ORDER BY InvoiceTotal DESC; IF @InvoiceTotal < 1000 BREAK; ELSE BEGIN SET @Total = @Total + @InvoiceTotal; DELETE FROM #InvoiceCopy WHERE InvoiceID = @InvoiceID; END; END; PRINT 'Total: $' + CONVERT(varchar, @Total, 1); (Refer to code example 14-2.) When does the expression on the WHILE statement in this script cause the loop to end?
When the value of the @Total variable plus the value of the largest invoice total in the #InvoiceCopy table becomes greater than 200,000
To log on to SQL Server using your Windows login ID, you use ________________ authentication.
Windows
Flashback Version Query
With FVQ, you can display rows from multiple committed versions of the database over a range of time
DESC FLASHBACK_TRANSACTION_QUERY
XID RAW(8) START_SCN NUMBER START_TIMESTAMP DATE COMMIT_SCN NUMBER COMMIT_TIMESTAMP DATE LOGON_USER VARCHAR2(30) UNDO_CHANGE# NUMBER OPERATION VARCHAR2(32) TABLE_NAME VARCHAR2(256) TABLE_OWNER VARCHAR2(32) ROW_ID VARCHAR2(19) UNDO_SQL VARCHAR2(4000)
What is XPath?
XPath uses a set of expressions to select nodes to be processed. The most common expression that you'll use is the location path expression, which returns back a set of nodes called a node set. XPath can use both an unabbreviated and abbreviated syntax. The following is the unabbreviated syntax for a location path: /axisName::nodeTest[predicate]/axisName::nodeTest[predicate]
Which element would be used in a format model to return the spelled-out year?
YEAR or SYEAR
Changes in computing have affected many of our day-to-day activities. Are all of the following activities examples of this change? Yes or No?In the past you used to use the phone system to call directory assistance to get a phone number. Today you can use your PC to look up a phone number online.In the past you used to have to go to the shoe store to buy shoes. Today you can use your PC to order shoes online.In the past you had to use your PC to send a person an email. Today you can use your phone to send a text message.
YES
"Does the MERGE statement allow you to insert, update, and delete rows from a table?"
Yes
"If an UPDATE statement contains a SELECT statement as a subquery, can the subquery contain both a GROUP BY and a HAVING clause?"
Yes
"To insert data through a simple view, must you include all NOT_NULL columns that do not have a default value assigned in the view definition?"
Yes
"When implementing a natural join, must columns with the same names have compatible datatypes?"
Yes
"When using the TO_CHAR function to format a date value, are the use of upper and lower case characters significant in the format mask?"
Yes
Can any user who owns an object grant object privileges for that object?
Yes
Can more than one row be inserted into the table at a time if the INSERT statement has a subquery instead of a VALUES clause?
Yes
Is a RIGHT JOIN the same as RIGHT OUTER JOIN?
Yes, A RIGHT JOIN is the same as RIGHT OUTER JOIN
When a user creates an Object did he just created his own Schema?
Yes, Basically, any user who creates an object has just created his or her own schema.
Could you say that a FULL JOIN is a combination of the LEFT AND RIGHT JOIN?
Yes, Conceptually, a FULL JOIN combines the effect of applying both a LEFT JOIN and a RIGHT JOIN;
Should most tables contain a Primary Key?
Yes, Most tables should have a primary key, and each table can have only ONE primary key.
Do PRIMARY KEYS must contain UNIQUE values?
Yes, Primary keys must contain UNIQUE values.
Can SQL create new databases?
Yes, SQL can create new databases
Can SQL create stored procedures in a database?
Yes, SQL can create stored procedures in a database
Can you use an < less than as a conditional selection for the SELECT clause?
Yes, you can use an < less than as a conditional selection for the SELECT clause
Can you use an <= less than as a conditional selection for the SELECT clause?
Yes, you can use an <= less than as a conditional selection for the SELECT clause
Can you use an <> not equal as a conditional selection for the SELECT clause?
Yes, you can use an <> not equal as a conditional selection for the SELECT clause
Can you use an > greater than as a conditional selection for the SELECT clause?
Yes, you can use an > greater than as a conditional selection for the SELECT clause
Can you use an >= greater or equal than as a conditional selection for the SELECT clause?
Yes, you can use an >= greater or equal than as a conditional selection for the SELECT clause
Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible?
Yes. As T-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures up to 32 levels. Any reference to managed code from a Transact-SQL stored procedure counts as one level against the 32-level nesting limit. Methods invoked from within managed code do not count against this limit.
How can we Rewrite Sub-Queries into Simple Select Statements or with Joins?
Yes. We can rewrite sub-queries using the Common Table Expression (CTE). A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. e.g. USE AdventureWorks GO WITH EmployeeDepartment_CTE AS ( SELECT EmployeeID,DepartmentID,ShiftID FROM HumanResources.EmployeeDepartmentHistory ) SELECT ecte.EmployeeId,ed.DepartmentID, ed.Name,ecte.ShiftID FROM HumanResources.Department ed INNER JOIN EmployeeDepartment_CTE ecte ON ecte.DepartmentID = ed.DepartmentID GO
Which type of join joins all rows in one table to all rows of another table?
a Cartesian product or cross join
"When adding a DATE datatype and a NUMBER datatype, which datatype is the result?"
a DATE datatype
An index created on multiple columns in a table is called what?
a composite or concatenated index
Which type of outer join will include all rows from both tables being joined?
a full outer join
What is the term for a subquery in which two or more values from the main query are matched to two or more values of the subquery?
a multicolumn subquery
Which type of join joins two tables by all columns that have the same name in both tables?
a natural join
Which type of join joins two tables when the column in one table does not directly correspond to a column in the second table?
a non-equijoin
A join that joins a table with itself is called
a self-join
What can you use to generate a series of integer values that can be used by more than one table?
a sequence
sharding
a way to partition database tables across servers
vector clocks
a way to provide partial ordering to events
Code example 5-1 SELECT VendorState, VendorCity, VendorName, COUNT(*) AS InvoiceQty, SUM(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID WHERE VendorState < 'e' GROUP BY VendorState, VendorCity, VendorName HAVING SUM(InvoiceTotal) > 500 ORDER BY VendorState, VendorCity, VendorName; The GROUPING SETS operator works like the ROLLUP and CUBE operators, but it
all of the above (includes summary rows adds summary rows for specified groups allows you to use additional sets of parentheses to create composite groups)
The dbcreator role
all of the above (is intended for those users who need to be able to work with database objects lets members create, alter, and drop databases allows new members to be added to the role)
The WITH SCHEMABINDING clause of the CREATE VIEW statement
all of the above (prevents the tables that the view is based on from being modified in a way that affects the view prevents the tables that the view is based on from being deleted protects the view by binding it to the database schema)
When you use Transact-SQL, you can store procedural code in
all of the above (stored procedures user-defined functions scripts)
You can use the GRANT statement to give users permission to use each of the following items except for one. Which one?
all the objects in a database
rename columns syntax
alter table rename column col1 to col2
add column syntax
alter table tbl1 add col1
modify column syntax
alter table tbl1 modify col1
rename column syntax
alter table tbl1 rename column to
What value will always be returned in comparisons between nulls and other values?
an unknown value (or NULL)
constraints in create table by queries
any CONSTRAINT or INDEX objects, or any other supporting objects that might exist for the source table or tables, are not replicated but need to be created individually if desired for the new table, with one exception: any explicitly created NOT NULL constraints on the queried table are copied into the new table, are assigned a system-generated name, and form part of the new table's definition. NOT NULL constraints that were created implicitly—for example, as part of a PRIMARY KEY constraint—are not included
operation log
any modifications are maintained in an operation log (oplog) -used for replication by secondaries -can be used for recovery --what if you accidentally drop a collection?
At which two levels can constraints be defined?
at the column or table level
"When using an ORDER BY clause in a compound query, where should you place the clause?"
at the end of the statement
(Refer to code example 4-1.) The name "v" is known as a?
both b and c
How can you override operator precedence?
by including parentheses in the expression
Unless you assign a ________________, the column name in the result set is the same as the column name in the base table.
column alias
When you code an ORDER BY clause, you can specify a
column name, alias, expression, or column number
To work with the data in a SQL Server database from a .NET application, you can use ADO.NET objects like
commands, connections, and data readers
Code example 6-2 WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) In this query, the table named Top5 is coded as a
common table expression (CTE)
Combining the contents of two or more columns is known as ____.
concatenation
The IIF function determines the value it returns based on what type of expression?
conditional
When you use the CREATE TABLE statement to create a table, you can also define the attributes and ______________ for the columns.
constraints
The CREATE TABLE statement
creates a new table in the current database
ADD_MONTHS
d is a date, required; n is a whole number, required. Adds n months to d, and returns a valid date value for the result.
USER_OBJECTS
describes all objects owned by the current user. Its columns are the same as those in "ALL_OBJECTS". OWNER Owner of the object; OBJECT_NAME Name of the object; SUBOBJECT_NAME VARCHAR2(30) Name OBJECT_ID Dictionary object number of the object; DATA_OBJECT_ID Dictionary object number of the segment that contains the object Note: OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. OBJECT_TYPE Type of the object (such as TABLE, INDEX); CREATED Timestamp for the creation of the object; LAST_DDL_TIME Timestamp for the last modification of the object resulting from a DDL statement (including grants and revokes) TIMESTAMP Timestamp for the specification of the object (character data) STATUS Status of the object (VALID, INVALID, or N/A) TEMPORARY Whether the object is temporary (the current session can see only data that it placed in this object itself) GENERATEDIndicates whether the name of this object was system generated (Y) or not (N) SECONDARY Whether this is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y | N)
USER_TAB_PRIVS
describes the object grants for which the current user is the object owner, grantor, or grantee. Its columns are the same as those in DBA_TAB_PRIVS.GRANTEE Name of the user to whom access was granted OWNER Owner of the object TABLE_NAME Name of the object GRANTOR Name of the user who performed the grant PRIVILEGE Privilege on the object GRANTABLE Indicates whether the privilege was granted with the GRANT OPTION (YES) or not (NO) HIERARCHY Indicates whether the privilege was granted with the HIERARCHY OPTION (YES) or not (NO)
Although the American National Standards Institute publishes the specifications for a standard SQL language, each DBMS vendor has its own ________________ of SQL.
dialect/variant
BSON data types
double, string, object, array, binary, (undefined), objectId, boolean, date, null, regular expressions, (dbpointer), Javascript, (symbol), JS (with scope), 32-bit integer, timestamp, 64-bit integer, decimal128, min key, max key
If FirstName contains "Edward" and LastName contains "Williams", what will the Solution column contain when this code is executed? LOWER(LEFT(FirstName,1) + LEFT(LastName,7)) AS Solution
ewilliam
What is a type of nonclustered index that includes a WHERE clause?
filtered index
A ______ value must either be null or match an existing value in the primary key column of the parent table.
foreign key
To relate one table to another, a/an ________________ in one table is used to point to the primary key in another table.
foreign key
How do you create an inline view?
include a subquery as a data source in a FROM clause
You can use the OVER clause with an aggregate function to
include the rows used to calculate the summary in the result set
When you code a union with the INTERSECT keyword to combine two result sets, the union
includes only rows that occur in both result sets
Code example 6-2 WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) In this query, the table named Top5 is used as part of a
join
MongoDB vs. Relational database
mongo: when querying outer object, mongo is convenient and efficient. but when querying a nested object, mongo is not efficient-- it loads all information for outer objects and cannot do joins directly relational db: to query a compound object, relational db needs to find different parts of the object from different tables. but when querying publications from different students, a relational db can be more straightforward
NATURAL Joins
natural join forbids such table prefixes on join column names. natural join is an inner join
The three main hardware components of a client/server system are the clients, the server, and the ________________.
network
NoSQL transactions
no transaction support
By default, what kind of index does the CREATE INDEX statement create?
non clustered
To normalize a data structure, what do you apply in sequence?
normal forms
What is the most common type of relationship between two tables?
one-to-many
The most common type of relationship between two tables is called what?
one-to-many relationship
"If within a transaction, a single DML statement fails, what is rolled back?"
only the work of the offending DML statement
What privilege(s) is required to drop a view?
ownership of the view or the DROPANY VIEW system privilege
What do you typically use to relate two tables that have a one-to-one relationship?
primary keys
The users, groups, logins, and roles that have access to a server are called ________________.
principals
WITH CHECK OPTION view
prohibits any changes to the table or view that would produce rows that are not included in the subquery.
After you create a schema, you can create any object within that schema by
qualifying the object name with the schema name
What is the name of the concept that guarantees that users have a consistent view of the data?
read consistency
transactions in mongo
recent versions (4.0+) do provide ACID transactions -operations on single documents are atomic -for multi-document operations, use transactions when needed
redis persistence; how do we avoid data loss?
redis generally stores the entire dataset in memory -persist dataset -newer versions use an append-only file (AOF) -can be disabled for performance
redis partitioning
redis is single-threaded (except for persistence). one way to improve performance is partitioning (sharding) data -range or hash partitioning -some features dont support partitions --operations on multiple keys --multi-key transactions
redis pub/sub
redis supports a message system called publish/subscribe --PUBLISH key message --SUBSCRIBE key
redis transactions
redis supports transactions -MULTI, EXEC, DISCARD
To maintain ________________, if you delete a row in a primary key table, you must also delete any related rows in foreign key tables.
referential integrity
When you use the Management Studio to create a foreign key constraint, you specify the relationship between two tables as well as the rules for enforcing what?
referential integrity
revoke privileges (CASCADE CONSTRAINTS)
remove any referential integrity constraints made to the object by means of the REFERENCES privilege
redis replication
replication is achieved using a master and slave (replica) nodes -master sends commands to slave instances -on temporary disconnect, slave re-synchronizes with master -slaves can promoted to master --manually - useful for upgrades --automatically - redis sentinel
CURRENT_DATE function
returns the current date in the session's time zone.
Unlike most database objects, when you invoke a user-defined function, you must always preface the name of the function with the
schema name
secondary servers for replication
secondary -replicates operations performed on the primary
The entities that can be secured on a server are called ________________.
securables
referncing anoutehr user table
select * from user_b.table
shard key
sharding is done based on a shard key - the shard key is some field every document must contain (or set of fields) -once we have a shard key, we can divide documents --hashed sharding, --ranged sharding
connect_by_root
shows the root of the tree. the level 1 as column
Which of the following types of views cannot contain grouped data?
simple
Which of the following types of views cannot include a group function?
simple view
"In a SELECT list, what character should enclose date and character literal values?"
single quotation marks (')
The operators =, <, >, <=, >=, and <> are referred to as ____ operators.
single-row
he > operator is referred to as a(n) ____ operator.
single-row
ORDER SIBLINGS BY
sorts rows in hierarhical report within each given level, not across levels, thus retaining the hierarchical relationship across rows of output. This syntax is what you want to use if you are trying to sort rows in a hierarchical query
What is SQLCMD?
sqlcmd is enhanced version of the isql and osql, and it provides way more functionality than other two options. In other words, sqlcmd is better replacement of isql (which will be deprecated eventually) and osql (not included in SQL Server 2005 RTM). sqlcmd can work in two modes - i) BATCH and ii) interactive modes.
n this simple diagram, what comprises the unique identifier for the student class entity?
student id and course id
A common table expression (CTE) creates a temporary _____________ that can be used by a query that follows.
table
When you use the Check Constraints dialog box, all of the constraints are at the which level so they can refer to any of the columns in the table?
table
"When performing a self join, what must be used to qualify table names?"
table aliases
Which privilege must a user be granted to be able to change another user's password?
the ALTER USER privilege
"In order to issue a TRUNCATETABLE statement successfully, you must either own the table or have which privilege?"
the DROPANYTABLE system privilege
Which option of a CREATEVIEW statement creates the view regardless of whether the underlying tables exist?
the FORCE option
Multiple-column subqueries are often used as data sources in which clause of an outer SELECT statement?
the FROM clause
Which clause of a SELECT statement specifies the name of the table or tables being queried?
the FROM clause
"Which function compares two expressions, returning NULL if the expressions are equal and returning the first expression if the expressions are not equal?"
the NULLIF function
Which statement do you use to remove previously granted privileges?
the REVOKE statement
Which two functions can be used to pad a character string to the right or left with a specified character?
the RPAD and LPAD functions
"In the SELECT clause of a SQL statement, what is the term for the list of one or more columns to be returned by the query?"
the SELECT list
Code example 6-2 WITH Top5 AS (SELECT TOP 5 VendorID, AVG(InvoiceTotal) AS AvgInvoice FROM Invoices GROUP BY VendorID ORDER BY AvgInvoice DESC) SELECT Invoices.VendorID, MAX(Invoices.InvoiceTotal) AS LargestInvoice FROM Invoices JOIN Top10 ON Invoices.VendorID = Top10.VendorID GROUP BY Invoices.VendorID ORDER BY LargestInvoice DESC; (Please refer to code example 6-2.) When this query is executed, each row in the result table will show
the largest invoice amount related to that row
"When performing a multitable INSERT, what two properties about the columns in the subquery and the columns in the outer query must match?"
they must be equal in number and in datatype
When you use the Management Studio to create a check constraint, you can specify whether you want the constraint enforced for insert or ________________ operations.
update
Which of the following is not a valid way to avoid search problems when you want to search for rows that have a specific date in a column that's defined with the datetime data type and which might include time values?
use the DatePart function to extract just the date from each datetime value
not null cosntraint rule
you cannot declare a NOT NULL constraint with the out-ofline syntax; it can only be created with the in-line syntax
When you create a script for creating a database,
you need to create the referred to tables before you create the tables that refer to them
Which operator combines two character strings yielding one combined character string?
|| (the concatenation operator)
What is the result of 3 + 2 * 5?
"13. Multiplication has higher precedence than addition, so 2 * 5 is evaluated first."
Explain the difference between disabling and dropping a constraint.
"A constraint than has been disabled still exists but is not enforced. It can be reenabled a later time. If a constraint is dropped, it will need to be recreated should you need it again."
Valid table and column names can only contain which characters?
"the characters A-Z, a-z, 0-9, _ (underscore), $, and #. (Normally table names with lower case alphabetics are treated the same as a table with upper case alphabetics. However, you can make case significant if you surround the table name by double quotes at the time it is created.) "
What type of information is stored in the database regarding an External Table and where is that information stored?
"the metadata, which is stored in the data dictionary"
removeing constraints three ways
1. drop primary key 2. drop unique 3. drop constraint
redoslijed local objest, public provate synonym
1. local object 2. private synonym 3. public synoynm
What are the Different Normalization Forms?
1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain. 2NF: Eliminate Redundant Data If an attribute depends on only part of a multi-valued key, then remove it to a separate table. 3NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key. (Read more here) BCNF: Boyce-Codd Normal Form If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables. 4NF: Isolate Independent Multiple Relationships No table may contain two or more 1:n or n:m relationships that are not directly related. 5NF: Isolate Semantically Related Multiple Relationships There may be practical constrains on information that justify separating logically related many-to-many relationships. ONF: Optimal Normal Form A model limited to only simple (elemental) facts, as expressed in Object Role Model notation. DKNF: Domain-Key Normal Form A model free from all modification anomalies is said to be in DKNF. Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.
If InvoiceTotal contains a value of 250.00, what will the Solution column contain when this code is executed? CASE WHEN InvoiceTotal > 500 THEN InvoiceTotal - ROUND(InvoiceTotal * .20, 2) WHEN InvoiceTotal >= 250 THEN InvoiceTotal - ROUND(InvoiceTotal * .10, 2) ELSE 0 END AS Solution
225.00
What does a RIGHT JOIN Return?
A RIGHT JOIN Returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN;
In which clause of a SELECT statement can a column alias NOT be used?
A column alias CANNOT be used in a WHERE clause.
A composite index
A composite index is an index that is built on two or more columns of a table, like this: CREATE INDEX IX_INV_INVOICE_VENDOR_ID ON INVOICES(VENDOR_ID, INVOICE_DATE);
How does Using a Separate Hard Drive for Several Database Objects Improves Performance Right Away?
A non-clustered index and tempdb can be created on a separate disk to improve performance.
Which of the following is the best scenario for using supertype/subtype entities:
A vehicle dealership that sells boats on trailers, cars, and trucks
What does a VIEW contain?
A view contains rows and columns, just like a real table.
modifying columns (syntax)
ALTER TABLE CRUISE_ORDERS MODIFY (ORDER_DATE DATE); You can include multiple elements in any one MODIFY clause provided you define them in the proper order: datatype, then DEFAULT, then constraint— recognizing that you don't have to specify them all. you cannot modify a column to take on properties that conflict with any existing data that is already present in the column
Renaming Columns
ALTER TABLE CRUISE_ORDERS RENAME COLUMN SALES_REP_ID TO SALES_AGENT_ID
brisanje kolona i povezanih kolona
ALTER TABLE ORDER_RETURNS DROP COLUMN CRUISE_ORDER_DATE CASCADE CONSTRAINTS; This statement will successfully drop the CRUISE_ORDER_DATE column in ORDER_RETURNS. It will also drop the associated FOREIGN KEY constraint.
Changing passwords of the user
ALTER USER Dehhan IDENTIFIED BY password2;
alter view
ALTER VIEW statement is used to accomplish any of the following tasks: n Create, modify, or drop constraints on a view. n Recompile an invalid view.
Which logical condition returns TRUE when both of the conditions are met and returns FALSE if either condition is FALSE?
AND
number rules
Accepts numeric data, including zero, negative, and positive numbers, where n specifies the "precision", which is the maximum number of significant digits (on either side of the decimal point), and m is the "scale", meaning the total number of digits to the right of the decimal point. Both n and m are optional; n defaults to the maximum value, m defaults to zero. The value for n can range from 1 to 38; the value for m can range from -84 to 127. Note that these are not the largest values you can have, but rather the largest (and smallest) specifications for values you can have—Oracle's SQL Language Reference Manual carefully states that the values accepted for a NUMBER datatype range from 1.0 × 10-130 up to "but not including" 1.0 × 10126. If a value entered into a NUMBER column has a precision greater than the specified value, an error message will result and the value will be rejected. On the other hand, if a value is entered that exceeds the declared scale, the entered value will be rounded off (.5 is rounded up) and accepted. Also, a negative value for m identifies how many significant digits to the left of the decimal point will be rounded off.
Which of the following represents the correct sequence of steps in the Database Development Process?
Analyze, Design, Build
Many to many relationships between entities usually hide what? Mark for Review
Another entity
What's the Difference between a Primary Key and a Unique Key?
Both primary key and unique key enforce uniqueness of the column on which they are defined. But by default, the primary key creates a clustered index on the column, whereas unique key creates a non-clustered index by default. Another major difference is that primary key doesn't allow NULLs, but unique key allows one NULL only. (Read more here)
If a string consists of one or more components, you can parse it into its individual components. To locate the characters that separate the components, you would use which function?
CHARINDEX
To locate the index of the first character of the first occurence of a substring within another string, you would use which function?
CHARINDEX
What is Change Data Capture (CDC) in SQL Server 2008?
Change Data Capture (CDC) records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables and makes a record available of what changed, where, and when, in simple relational 'change tables' rather than in an esoteric chopped salad of XML. These change tables contain columns that reflect the column structure of the source table you have chosen to track along with the metadata needed to understand the changes that have been made.
"Valid account codes are seven characters and follow this format:
Character 1: Upper or lowercase A Character 2: Any alpha, but always lowercase Characters 3 - 6: digits (0 thru 9) Character 7: an uppercase M or another digit " '^[Aa][[:lower:]][[:digit:]]{4}[M[:digit:]]$'
Character Functions
Character functions are used to manipulate text. They can be used to perform many jobs on a given string: analyze its length (LENGTH), pad it with extra characters (RPAD, LPAD), trim off unwanted characters (RTRIM, LTRIM, TRIM), locate a given string within a larger string (INSTR), extract a smaller string from a larger string (SUBSTR), and replace text within a string (REPLACE). It's even possible to search for strings that aren't necessarily spelled the same but that sound alike (SOUNDEX), and more. When these are combined together, the possibilities are theoretically endless.
Data Type: Character
Character string. Fixed-length n char(32)
Data Type: Variable Character
Character string. Variable length. Maximum length n varchar(64)
Why can there be only one Clustered Index and not more than one?
Cluster Index physically stores data, or arranges data in one order (depends on which column(s) you have defined Clustered index and in which order). As a fact, we all know that a set of data can be only stored in only one order; that is why only one clustered index is possible.
What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence with options for specifying case sensitivity, accent marks, Kana character types, and character width. (Read more here)
What are Different Types of Join?
Cross Join A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price. Inner Join A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer. Outer Join A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included: Left Outer Join: In Left Outer Join, all the rows in the first-named table, i.e. "left" table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear. Right Outer Join: In Right Outer Join, all the rows in the second-named table, i.e. "right" table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included. Full Outer Join: In Full Outer Join, all the rows in all joined tables are included, whether they are matched or not. Self Join This is a particular case when one table joins to itself with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join. (Read more here)
neo4j uses what query language
Cypher query language (CQL) -Create, delete, set, remove, match
Which datatype stores a length of time in months and years?
INTERVAL YEAR TO MONTH
Which of the following statements calls the following stored procedure, passes the value '2015-12-01' to its input parameter, and stores the value of its output parameter in a variable named @MyInvoiceTotal? CREATE PROC spInvoiceTotal2 @DateVar smalldatetime, @InvoiceTotal money OUTPUT AS SELECT @InvoiceTotal = SUM(InvoiceTotal) FROM Invoices WHERE InvoiceDate >= @DateVar; (Assume that the @MyInvoiceTotal variable has already been declared, and pass the parameters by position.)
EXEC spInvoiceTotal2 '2015-12-01', @MyInvoiceTotal OUTPUT;
XID
Each transaction that is executed within the database is tracked and assigned a global transaction identifier, which is essentially a transaction identification number. The XID value is of the RAW datatype, which is a binary value, and is not interpreted by the Oracle database. But it can be converted into readable form using the RAWTOHEX function, which converts RAW data into character data that represents the hexadecimal equivalent of the RAW data, which is binary
$
End of Line Anchor Match the preceding expression only when it occurs at the end of a line.
Example of Aliases (AS) syntax:
Example of Aliases (AS) syntax: SELECT column_name AS alias_name FROM table_name; SELECT column_name(s) FROM table_name AS alias_name;
What is Filtered Index?
Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduces index maintenance costs, and reduces index storage costs when compared with full-table indexes. When we see an Index created with a WHERE clause, then that is actually a FILTERED INDEX.
compiling views
For example, if a table's structure is altered, such as by a change to a column's datatype, or perhaps if a column is dropped from the table altogether—a column that is used by the view—then it may change the status of the view to 'INVALID'. You can check the data dictionary's USER_OBJECTS view to determine the
usage of index
For best results, the indexed column should be specified in a comparison of equality. n A "greater than" or some other comparison may work. n A "not equals" will not invoke an index. n The LIKE comparison may invoke an index as long as the wildcard character is not in the leading position—in other words, the expression LIKE '%SMITH' will not invoke an index, but LIKE 'SMITH%' may invoke an index. n A function on a column will prevent the use of an index—unless the index is a function-based index, which we'll discuss in detail in Chapter 11. Furthermore, certain types of automated datatype conversions will eliminate the
adding a not null column in existing table
For example, if there are already rows in a given table, then you cannot alter that table by adding a new column with the NOT NULL constraint. we can add default value, and that would be sintacticaly correct.
Flashback Transaction Query
For querying the data dictionary and obtaining a variety of metadata about historical rows in the database— including information about the transaction that caused the most recent changes Note that flashback operations are limited; you cannot recover any data from any
In a physical data model, a relationship is represented as a:
Foreign Key
What is Hybrid Slowly Changing Dimension?
Hybrid SCDs are combination of both SCD 1 and SCD 2. It may happen that in a table, some columns are important and we need to track changes for them, i.e. capture the historical data for them, whereas in some columns even if the data changes, we do not care.
How is the Round Function processed? is there any rule to be aware of?
IEEE 754, Many database systems have adopted the IEEE 754 standard for arithmetic operations, according to which the default rounding behavior is "round half to even." In this scheme, .5 is rounded to the nearest even integer. So, both 11.5 and 12.5 would be rounded to 12.
INSERT ALL - unconditional
INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL) select employee_id empid ... from table1
PIVOTING INSERT
INSERT ALL INTO sales_info VALUES (employee_id,week_id,sales_MON) INTO sales_info VALUES (employee_id,week_id,sales_TUE) INTO sales_info VALUES (employee_id,week_id,sales_WED) INTO sales_info VALUES (employee_id,week_id,sales_THUR) INTO sales_info VALUES (employee_id,week_id, sales_FRI) SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI FROM sales_source_data;
What is an Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBAs leave these at 1. A GUID column also generates unique keys. Updated based on the comment of Aaron Bertrand. (Blog)
How to get @@ERROR and @@ROWCOUNT at the Same Time?
If @@Rowcount is checked after Error checking statement, then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement, then @@Error would get reset. To get @@error and @@rowcount at the same time, include both in same statement and store them in a local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
default values
If a row is added that does not include a value for that column, then the value will be automatically assigned
What is Degenerate Dimension Table?
If a table contains values, which are neither dimension nor measures, then it is called a degenerate dimension table.
What are Points to Remember while Using the FILLFACTOR Argument?
If fill-factor is set to 100 or 0, the Database Engine fills pages to their capacity while creating indexes. The server-wide default FILLFACTOR is set to 0. To modify the server-wide default value, use the sp_configure system stored procedure. To view the fill-factor value of one or more indexes, use sys.indexes. To modify or set the fill-factor value for individual indexes, use CREATE INDEX or ALTER INDEX statements. Creating a clustered index with a FILLFACTOR < 100 may significantly increase the amount of space the data occupies because the Database Engine physically reallocates the data while building the clustered index.
Example of a JOIN syntax
If you have a Table A and a Table B SELECT * FROM TableA INNER JOIN TableB ON TableA."col" = TableB."col" produces only the set of records that match in both A and B
What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?
In case of only GRANT, the username cannot grant the same permission to other users. On the other hand, with the option WITH GRANT, the username will be able to give the permission after receiving requests from other users.
How do We Know if Any Query is Retrieving a Large Amount of Data or very little data?
In one way, it is quite easy to figure this out by just looking at the result set; however, this method cannot be relied upon every time as it is difficult to reach a conclusion when there are many columns and many rows. It is easy to measure how much data is retrieved from server to client side. The SQL Server Management Studio has feature that can measure client statistics.
What is the Difference between VARCHAR and NVARCHAR datatypes?
In principle, they are the same and are handled in the same way by your application. The only difference is that NVARCHAR can handle unicode characters, allowing you to use multiple languages in the database (Arabian, Chinese, etc.). NVARCHAR takes twice as much space when compared to VARCHAR. Use NVARCHAR only if you are using foreign languages.
{m,n}
Interval--Between Count Matches at least m, but not more than n occurrences of the preceding subexpression
What is a Covered index?
It is an index that can satisfy a query just by its index keys without having needed to touch the data pages. It means that when a query is fired, SQL Server doesn't need to go to the table to retrieve the rows, but can produce the results directly from the index as the index covers all the columns used in query.
What is Log Shipping?
Log shipping is the process of automating the backup of database and transaction log files on a production SQL server and then restoring them onto a standby server. All Editions (except Express Edition) supports log shipping. In log shipping, the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined intervals.
What is MERGE Statement?
MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement, we can include the logic of such data modifications in one statement that even checks when the data is matched, then just update it, and when unmatched, insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once.
What uncommon relationship is described by the statements: "Each LINE must consist of many POINTS and each POINT must be a part of many LINES"
Many to Many Mandatory
SAVEPOINT name
Marks a savepoint within the current transaction
What does the MOD(x,y) return?
Modulo- returns the integer remainder of x divided by y (same as x%y)
Is It Possible to have Clustered Index on Separate Drive From Original Table Location?
No! It is not possible.
Can a PRIMARY KEY contain NULL VALUES?
No, A primary key column cannot contain NULL values.
Does the Order of Columns in UPDATE statements Matter?
No, the order of columns in UPDATE statement does not matter for results updated. Both the below options produce the same results. Option 1: UPDATE TableName SET Col1 ='Value', Col2 ='Value2' Option 2: UPDATE TableName SET Col2 ='Value2', Col1 ='Value'
Does a Schema has a limit to the number of objects it may contain?
No, unless it's restricted by implementation, Schema does not have a limit to it's containing objects. A schema can consist of a single table and has no limits to the number of objects that it may contain, unless restricted by a specific database implementation.
Can we Insert Data if Clustered Index is Disabled?
No, we cannot insert data if Clustered Index is disabled because Clustered Indexes are in fact original tables which are physically ordered according to one or more keys (Columns).
Can a multitable INSERT insert rows into multiple tables and views in a single pass?
No. A multitable INSERT cannot insert rows into views.
Is OLTP Database is Design Optimal for Data Warehouse?
No. OLTP database tables are normalized, and it will add additional time to queries to return results. Additionally, the OLTP database is small; it does not contain data from a long period (many years), which needs to be analyzed. A OLTP system is basically an ER model and not a Dimensional Model. If a complex query is executed on an OLTP system, it may lead to heavy overhead on the OLTP server that will affect the normal business processes.
Are the names of the columns in SELECT lists in a compound query required to be the same?
No. The names of columns can be different. The number and datatypes must be compatible.
What are Non-Additive Facts?
Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table. However, they are not considered as useless. If there are changes in dimensions, the same facts can be useful.
versions between and creating views
Note that you cannot use the VERSIONS clause when querying a view. But you can use SELECT with a VERSIONS clause to create a view, meaning that VERSIONS can be included in the subquery of a CREATE VIEW statement.
Number Functions
Number functions can perform mathematical analysis. SQL comes with many functions for determining sin (SIN, ASIN, SINH), cosine (COS, ACOS, COSH), and tangent (TAN, ATAN, ATAN2, TANH). You can determine absolute value (ABS), or determine if a given number is positive or negative (SIGN). A function can round off values (ROUND) and otherwise abbreviate numbers (TRUNC). Number functions can be incorporated into expressions, and as you've already seen, expressions provide support for standard arithmetic operations of addition (+), subtraction (-), multiplication (*), and division (/). These operators are not functions but are, well, operators. The point here is that you can combine the operators with number functions to produce powerful SQL statements
Data type: number (size,d)
Number value with a maximum number of digits of "size" total, with a max number of "d" digits to the right of the decimal.
What is ODS?
ODS is the abbreviation of Operational Data Store ‑ a database structure that is a repository for near real-time operational data rather than long-term trend data. The ODS may further become the enterprise-shared operational database, allowing operational systems that are being re-engineered to use the ODS as their operation databases.
Which clause specifies the number of rows that should be skipped before rows are returned from the result set?
OFFSET
What is OLAP?
OLAP is abbreviation of Online Analytical Processing. This system is an application that collects, manages, processes and presents multidimensional data for analysis and management purposes.
What is OLTP?
OLTP is abbreviation of On-Line Transaction Processing. This system is an application that modifies data At the very instant it is received and has a large number of concurrent users.
How would you code the ON clause for a trigger that's fired after a table is deleted from the current database?
ON DATABASE
Which logical operator separating two conditions will return TRUE when at least one of two conditions is true?
OR
What are Various Limitations of the Views?
ORDER BY clause does not work in View. Regular queries or Stored Procedures give us flexibility when we need another column; we can add a column to regular queries right away. If we want to do the same with Views, then we will have to modify them first. Index created on view not used often. Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed. One of the most prominent limitations of the View it is that it does not support COUNT (*); however, it can support COUNT_BIG (*). UNION Operation is now allowed in Indexed View. We cannot create an Index on a nested View situation means we cannot create index on a view which is built from another view. SELF JOIN Not Allowed in Indexed View. Outer Join Not Allowed in Indexed Views. Cross Database Queries Not Allowed in Indexed View.
Which keyword can you use to pass parameter from a stored procedure back to the calling program?
OUTPUT
objekt privileges
Object Privilege Table View Sequence Procedure ALTER TS DELETE TV EXECUTE P INDEX T INSERT TV REFERENCES TV SELECT TVS UPDATE TV
CHECKPOINT
On large tables you can reduce the amount of undo logs accumulated by using the CHECKPOINT option which forces a checkpoint after the specified number of rows have been processed.
+
One or More -- Plus Quantifier Matches one or more occurrences of the preceding subexpression
What are Pessimistic Lock and Optimistic Lock?
Optimistic Locking is a strategy where you read a record, take note of a version number and check that the version hasn't changed before you write the record back. If the record is dirty (i.e. different version to yours), then you abort the transaction and the user can re-start it. Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks.
Figure 10-1 ORDERS (OrderID, OrderDate) • | ^ ORDERLINEITEMS (OrderID, OrderSequence, ProductID) v | • PRODUCTS (ProductID, ProductName) (Refer to figure 10-1.) Which column or columns in each table should be defined as the primary key?
Orders: OrderID OrderLineItems: OrderID and OrderSequence Products: ProductID
Which function would you use to calculate the rank of the values in a sorted set of values as a percent?
PERCENT_RANK
[:class:]
POSIX Character Class Match any character belonging to the specified character class. Can be used inside any list expression.
[.element.]
POSIX Collating Sequence Specifies a collating sequence to use in the regular expression. The element you use must be a defined collating sequence, in the current locale.
An owner of an object can grant access to all users using which keyword?
PUBLIC
What is a ROLLUP Clause?
ROLLUP clause is used to do aggregate operation on multiple levels in hierarchy. If we want sum on different levels without adding any new column, then we can do it easily using ROLLUP. We have to just add the WITH ROLLUP Clause in group by clause.
RULLUP
ROLLUP returns a single summary row for each grouped set of records within a SELECT statement that uses the GROUP BY clause. ZAMOTA ZBIR
Which datatype is a hexadecimal string representing the unique address of a row in its table?
ROWID
To log on to SQL Server using your SQL Server login ID, you use ________________ authentication.
SQL Server
Which of the following types of statements isn't an action query?
Select
What is Service Broker?
Service Broker is a message-queuing technology in SQL Server that allows developers to integrate SQL Server fully into distributed applications. Service Broker is a feature which provides facility to SQL Server to send an asynchronous, transactional message. It allows a database to send a message to another database without waiting for the response; so the application will continue to function if the remote database is temporarily unavailable.
What are Different Types of Locks?
Shared Locks: Used for operations that do not change or update data (read-only operations), such as a SELECT statement. Update Locks: Used on resources that can be updated. It prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later. Exclusive Locks: Used for data-modification operations, such as INSERT, UPDATE, or DELETE. It ensures that multiple updates cannot be made to the same resource at the same time. Intent Locks: Used to establish a lock hierarchy. The types of intent locks are as follows: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). Schema Locks: Used when an operation dependent on the schema of a table is executing. The types of schema locks are schema modification (Sch-M) and schema stability (Sch-S). Bulk Update Locks: Used when bulk-copying data into a table and the TABLOCK hint is specified.
What is DataWarehousing?
Subject-oriented, which means that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together; Time-variant, which means that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time; Non-volatile, which means that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting. Integrated, which means that the database contains data from most or all of an organization's operational applications, and that this data is made consistent.
A subtype can have a relationship not shared by the supertype. True or False?
T
Skip Scanning
Thanks to skip scanning, a WHERE clause that references any columns within a composite index may invoke the index in its processing.
Consider the recommended drawing conventions for ERD's. Indicate which of the following accurately describes diagramming conventions for entities and attributes:
The * means that an attribute is mandatory or required; the entity name should be singular
What is the CHECK Constraint used for?
The CHECK constraint is used to limit the value range that can be placed in a column.
What does a Count Function Return?
The COUNT() function returns the number of rows that matches a specified criteria.
What does a COUNT(column_name) function Return?
The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column
What is the CREATE DATABASE statement used for?
The CREATE DATABASE statement is used to create a database.
What is the CREAT INDEX used for?
The CREATE INDEX statement is used to create indexes in tables.
What is the CREATE TABLE statement used for?
The CREATE TABLE statement is used to create a table in a database.
What is the DEFAULT Constraint used for?
The DEFAULT constraint is used to insert a default value into a column.
What it the DELETE Statement used for?
The DELETE statement is used to delete
What is the DELETE statement used for?
The DELETE statement is used to delete records or rows from the table
dictionary view
The DICTIONARY view is a great starting point for any investigation of the data dictionary
"To remove a constraint from a table, which clause of the ALTER TABLE statement should you use?"
The DROP clause. The syntax is ALTER TABLE tablename DROP PRIMARY KEY | UNIQUE (column) | CONSTRAINT constraintname [CASCADE];
What does the First Function Return?
The FIRST() function returns the first value of the selected column. Only in MS Access
What is the Format Function used for?
The FORMAT() function is used to format how a field is to be displayed. SELECT FORMAT(column_name,format) FROM table_name;
What does the FULL OUTER JOIN keyword return?
The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).
Which are the New Data Types Introduced in SQL SERVER 2008?
The GEOMETRY Type: The GEOMETRY datatype is a system .NET common language runtime (CLR) datatype in SQL Server. This type represents data in a two-dimensional Euclidean coordinate system. The GEOGRAPHY Type: The GEOGRAPHY datatype's functions are the same as with GEOMETRY. The difference between the two is that when you specify GEOGRAPHY, you are usually specifying points in terms of latitude and longitude. New Date and Time Data types: SQL Server 2008 introduces four new data types related to date and time: DATE, TIME, DATETIMEOFFSET, and DATETIME2. DATE: The new DATE data type just stores the date itself. It is based on the Gregorian calendar and handles years from 1 to 9999. TIME: The new TIME (n) type stores time with a range of 00:00:00.0000000 through 23:59:59.9999999. The precision is allowed with this type. TIME supports seconds down to 100 nanoseconds. The n in TIME(n) defines this level of fractional second precision from 0 to 7 digits of precision. The DATETIMEOFFSET Type: DATETIMEOFFSET (n) is the time-zone-aware version of a datetime datatype. The name will appear less odd when you consider what it really is: a date + time + time-zone offset. The offset is based on how far behind or ahead you are from Coordinated Universal Time (UTC) time. The DATETIME2 Type: It is an extension of the datetime type in earlier versions of SQL Server. This new datatype has a date range covering dates from January 1 of year 1 through December 31 of year 9999. This is a definite improvement over the lower boundary of 1753 of the datetime datatype. DATETIME2 not only includes the larger date range, but also has a timestamp and the same fractional precision that TIME type provides.
Where is the Group By Function used?
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
Why was the HAVING Function added to SQL?
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
What is the Correct Order of the Logical Query Processing Phases?
The correct order of the Logical Query Processing Phases is as follows: 1. FROM 2. ON 3. OUTER 4. WHERE 5. GROUP BY 6. CUBE | ROLLUP 7. HAVING 8. SELECT 9. DISTINCT 10. TOP 11. ORDER BY (Read more here)
DBTIMEZONE
The default database time zone is the same as the operating system's time zone. You set the database's default time zone by specifying the SET TIME_ZONE clause of the CREATE DATABASE statement.
undo Retention Period
The duration of the undo retention period depends upon the configuration of your database and its undo management, which is under the control of the database administrator (DBA).
How can I Check that whether Automatic Statistic Update is Enabled or not?
The following query can be used to know if Automatic Statistic Update: SELECT is_auto_create_stats_on,is_auto_update_stats_on FROM sys.databases WHERE name ='YOUR DATABASE NAME
If denormalizing improves Data Warehouse Processes, then why is the Fact Table is in the Normal Form?
The foreign keys of facts tables are primary keys of Dimension tables. It is clear that the fact table contains columns which are a primary key to another table that itself make a normal form table.
Questions on SQL SERVER 2008 What are the basic functions for master, msdb, model, tempdb and resource databases?
The master database holds information for all the databases located on the SQL Server instance, and it is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care. The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping. The tempdb holds temporary objects such as global and local temporary tables and stored procedures. The model is essentially a template database used in the creation of any new user database created in the instance. The resource Database is a read-only database that contains all the system objects that are included in the SQL Server. SQL Server system objects such as sys.objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.
What is the most difficult part of working with SQL dates?
The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database.
"If an INSERT statement has a subquery instead of a VALUES clause, what criterion will allow the statement to execute successfully relative to the INSERT and the SELECT?"
The number and datatype of the columns returned by the SELECT must match the number and datatype of columns the INSERT is expecting.
What is the relationship between the number of groups created by the ROLLUP operation and the number of grouping columns specified in the SELECT clause?
The number of groups created by the ROLLUP operation is one more than the number of grouping columns specified.
USER_objects (tables etc)
These views contain information about objects owned by user
What is the Difference between a HAVING clause and a WHERE clause?
They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query, whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. (Read more here)
What Command do we Use to Rename a db, a Table and a Column?
To Rename db sp_renamedb 'oldname' , 'newname If someone is using db it will not accept sp_renmaedb. In that case, first bring db to single user mode using sp_dboptions. Use sp_renamedb to rename the database. Use sp_dboptions to bring the database to multi-user mode. e.g. USE MASTER; GO EXEC sp_dboption AdventureWorks, 'Single User', True GO EXEC sp_renamedb 'AdventureWorks', 'AdventureWorks_New' GO EXEC sp_dboption AdventureWorks, 'Single User', False GO To Rename Table We can change the table name using sp_rename as follows: sp_rename 'oldTableName' 'newTableName' e.g. sp_RENAME 'Table_First', 'Table_Last' GO To rename Column The script for renaming any column is as follows: sp_rename 'TableName.[OldcolumnName]', 'NewColumnName', 'Column' e.g. sp_RENAME 'Table_First.Name', 'NameChange' , 'COLUMN' GO
What are Policy Management Terms?
To have a better grip on the concept of Policy-based management, there are some key terms you need to understand. Target - A type of entity that is appropriately managed by Policy-based management. For example, a table, database and index, to name a few. Facet -A property that can be managed in policy-based management. A clear example of facet is the name of Trigger or the Auto Shrink Property of database. Conditions - Criteria that specifies the state of facet to true or false. For example, you can adjust the state of a facet that gives you clear specifications of all stored procedures in the Schema 'Banking'. Policy - A set of rules specified for the server objects or the properties of database.
What is Connection Pooling and why it is Used?
To minimize the cost of opening and closing connections, ADO.NET uses an optimization technique called connection pooling. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.
The basic code structure for many SQL statements and objects can be found in which section of the SQL Server Management Studio?
Transact-SQL snippets
What are Isolation Levels?
Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed. Transaction isolation levels control the following: Whether locks are taken when data is read, and what type of locks are requested. How long the read locks are held. Whether a read operation referencing rows modified by another transaction blocks until the exclusive lock on the row is freed, retrieves the committed version of the row that existed at the time the statement or transaction started, and reads the uncommitted data modification.
kolekcija unused kolona
USER_UNUSED_COL_TABS
creating a public synonym
Users who have been granted the appropriate system privilege may create a public synonym.
Which statement can you use to repeatedly execute a statement or set of statements?
WHILE
The ____ constraint ensures that any DML operations performed on the view (e.g., adding rows, changing data) will not prevent the row from being accessed by the view because it no longer meets the condition in the WHERE clause.
WITH CHECK OPTION
Explain Query Editor Regions:
When the T-SQL code is more than hundreds of lines, after a while, it becomes more and more confusing. The regions are defined by the following hierarchy: From first GO command to the next GO command. Statements between BEGIN - END, BEGIN TRY - END TRY, BEGIN CATCH - END CATCH
When are relationships unnecessary?
When you can derive the relationship from other relationships in the model
creatyng the synonyms
When you create a synonym for another object, that object does not necessarily have to exist already. If it does exist, you are not required to have privileges on the object in order to successfully create a synonym for it. Obviously, however, for the synonym to eventually work, the object must eventually be created, and privileges must eventually be granted—but that can come after the synonym's creation.
Is there more than one way to specify a CROSS JOIN?
Yes, a CROSS JOIN can either be specified using the CROSS JOIN syntax ("explicit join notation") or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria ("implicit join notation").
Is a LEFT JOIN the same as LEFT OUTER JOIN?
Yes, a left join can also be referred as a left outer join
If you are tracking employment dates for an employee, do you need to have an "End Date" attribute?
Yes, if the company wants to track multiple start dates and insure that multiple employment dates for an employee do not overlap
synonyms
You can create synonyms for tables, views, sequences, and other synonyms, as well as a number of other database objects that are included on the exam.
What is Snapshot with Reference to Data Warehouse?
You can disconnect the report from the catalog to which it is attached by saving the report with a snapshot of the data.
check constraints
You can set constraints to validate (VALIDATE) or not validate (NOVALIDATE) existing data. If VALIDATE is specified, then existing data must conform to the constraint. For example, enabling a NOT NULL constraint on employees.department_id and setting it to VALIDATE checks that every existing row has a department ID. If NOVALIDATE is specified, then existing data need not conform to the constraint.
external table (rules)
You cannot create a column with a LOB datatype—no CLOB, BLOB, NCLOB, etc. - You cannot add a constraint to an external table. - You cannot change the column of an external table to UNUSED. If you try, SQL will process the statement but will actually drop the column. - External tables can be queried but cannot receive data input via INSERT, UPDATE, or DELETE
first time call currval
You cannot invoke CURRVAL in your first reference to a sequence within a given session. NEXTVAL must be the first reference. n If you attempt to execute a statement, such as an INSERT, that includes the sequence reference NEXTVAL, the sequence generator will advance to the next number even if the INSERT statement fails. n You cannot invoke CURRVAL or NEXTVAL in the DEFAULT clause of a CREATE TABLE or ALTER TABLE statement. n You cannot invoke CURRVAL or NEXTVAL in the subquery of a CREATE VIEW statement, nor of a SELECT, UPDATE, or DELETE statement. n In a SELECT statement, you cannot combine CURRVAL or NEXTVAL with a DISTINCT operator. n You cannot invoke CURRVAL or NEXTVAL in the WHERE clause of a SELECT statement. n You cannot use CURRVAL or NEXTVAL in a CHECK constraint. n You cannot combine CURRVAL or NEXTVAL with the set operators UNION, INTERSECT, or MINUS. n You can call a sequence pseudocolumn from anywhere within a SQL statement that you can use any expression. That last point is important—a reference to a sequence must include its
Which SELECT statement clause restricts included rows prior to grouping?
a WHERE clause
What type of index would be most appropriate to use in a data warehousing application on a column where the cardinality may be poor?
a bitmap index
Which construct specifies a different heading for a column in a SELECT list?
a column alias
Cassandra
a column family NoSQL database, originally developed at Facebook
If introduced as follows, the subquery can return which of the values listed below? WHERE VendorID NOT IN (subquery)
a column of one or more rows
If introduced as follows, the subquery can return which of the values listed below? WHERE 2 < (subquery)
a single value
geospatial indexes
a special kind of 2d index that uses planar geometry, this allows us to do searches based on geographic proximity
automated rollback
abnormal termination of iSQL plus or system failure
Insert, Update, and Delete statements can be referred to as ________________ queries.
action
The ADD clause of the ALTER TABLE statement is used to do what?
add a column or constraint to a table
COLUMN-FAMILY STORES
aka. wide column store
The MAX function can be used with which type of columns?
all of the above
Which keyword lets you control the number of rows that are returned by a query?
all of the above (ALL, DISTINCT, TOP)
System stored procedures
all of the above (are stored in the Master database perform standard tasks on the current database can change with each version of SQL Server)
A view
all of the above (doesn't store any data itself consists only of the rows and columns specified in its CREATE VIEW statement is like a virtual table)
With the ALTER LOGIN statement, you can
all of the above (enable or disable a login ID change the name for a login ID change the default database or language)
When you identify a column as the primary key, the column
all of the above (has a clustered index created automatically for the column is forced to contain a unique value for each row is forced to be NOT NULL)
"When no WHERE clause is specified in a DELETE statement, which rows are deleted?"
all the rows in the table
Which type of join containing an equality operator joins two tables by a column that contains a matching value?
an equijoin
A combination of column names and operators that evaluate to a single value is called
an expression
The first character of an identifier must be
any of the above
What is the default sort order when using an ORDER BY clause?
ascending
What kind of constraint enforces referential integrity between tables?
both a and b (reference constraint, foreign key constraint)
An identifier
can contain a number sign (#)
cassandra consistency
cassandra allows tunable consistency via read and write consistency levels -quorum = replication factor/2 + 1 Q = N/2 + 1 --cassandra provides strong consistency when W + R > N where W = write replica count R = read replica count N = replication factor
cassandra limitations
cassandra does not support joins or subqueries -create redundant copies of required data via denormalization cassandra does not support transactions -use an external transaction library
when not to use cassandra
cassandra may NOT be so good for things like: -applications that require transactions -aggregate queries -prototyping where column families may change
when to use cassandra
cassandra may be good for stroing objects in things like: --event logging --content management systems --web analytics
One limitation of the Query Designer is that you can't use it for
certain types of complex queries
What kind of constraint limits the values that can be stored in a column?
check constraint
A SELECT statement that includes aggregate functions is often called a/an ________________ query.
summary
basically available
system guarantees availability
This is typically modeled after a real-world entity, such as an invoice or a vendor.
table
When you define a foreign key constraint, you can specify all but one of the following. Which one is it?
that the insertion of a row in a foreign key table that has a foreign key that isn't matched in the primary key table should be cascaded up to the primary key table
Which comparison operator allows you to compare a value to a range of values?
the BETWEEN... AND operator
Which statement explicitly makes all pending data modifications permanent?
the COMMIT statement
Which statement would you use to create a nonunique index on a table?
the CREATE INDEX statement
"If you are not the DBA, which privilege must you have to create a public synonym on another user's table?"
the CREATE PUBLIC SYNONYM privilege
Which system privilege allows a user to connect to the database?
the CREATE SESSION system privilege
Which option of the CREATESEQUENCE statement allows the sequence to continue to generate values after the maximum sequence value has already been generated?
the CYCLE option
Which two constructs can be used to emulate an IF-THEN-ELSE condition within a SELECT statement?
the DECODE function and the CASE expression
Which option for a column in a CREATETABLE statement indicates the value that the column should be given if no value is explicitly specified on an INSERT statement?
the DEFAULT option
"Which statement permanently removes all the rows in a table, the structure of the table, all constraints, all triggers on the table, and any associated indexes?"
the DROPTABLE statement
Which clause of a MERGE statement modifies existing records?
the UPDATE clause
When using Oracle proprietary join syntax which clause of the SELECT statement represents the join criteria?
the WHERE clause
Which clause in an UPDATE statement specifies the rows that will be updated?
the WHERE clause
Which clause of a SELECT statement includes a condition to restrict the rows returned by the query?
the WHERE clause
"When the MIN group function is used with a DATE column, which date is displayed?"
the earliest date
What are the two requirements of expressions in a SELECT list in a compound query?
the expressions must match in number and in datatype
What will be the results of a LEFT JOIN?
the results of a LEFT JOIN, will contain all records from the left table, even if the JOIN condition doesn't find any matching records in the right table.
"When using the TO_CHAR function to format dates, what does MON in a format model display?"
the three-letter month abbreviation using three capital letters
check constraint
•A check constraint can NOT be defined on a SQL View. •The check constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables. •A check constraint can NOT include a SQL Subquery. the check constraint can be enabled and disabled
What is the JOIN clause used for?
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
Example of BETWEEN operator syntax:
BETWEEN operator syntax: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
What is CRUD?
Create, Read/Retrieve (Select), Update and Delete are the four basic functions of persistent storage.
Example of INSERT INTO syntax:
Example of INSERT INTO syntax: INSERT INTO table2 SELECT * FROM table1; INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1;
Example of LEFT JOIN syntax:
Example of LEFT JOIN syntax: SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;
Example of SELECT INTO syntax:
Example of SELECT INTO syntax: SELECT * INTO newtable [IN externaldb] FROM table1; SELECT column_name(s) INTO newtable [IN externaldb] FROM table1;
Example of a SQL Constrain syntax:
Example of a SQL Constrain syntax: CREATE TABLE table_name ( column_name1 data_type(size) constraint_name, column_name2 data_type(size) constraint_name, column_name3 data_type(size) constraint_name, .... );
Example of INSERT INTO statement
INSERT INTO Example: INSERT INTO table_name VALUES (value1,value2,value3,...); INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);
What happens on a SQL constrain if there is a violation between the constrain and the data action?
If there is any violation between the constraint and the data action, the action is aborted by the constraint.
What happens if you omit the WHERE clause in the DELETE statement?
If you omit the WHERE clause, all records will be deleted!
Example of LIKE operator syntax
LIKE operator syntax example: SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...); SELECT * FROM Customers WHERE City IN ('Paris','London');
What are the values that BETWEEN operator can handle?
Numbers, Text, or Dates. The values handled by the BETWEEN operator can be numbers, text, or dates.
What does the R in CRUD stand for?
Read/Retrieve (Select), R in CRUD, stand for Read/Retrieve (Select) and is on of the four basic functions of persistent storage.
What are the Aliases (AS) main purpose?
Readability - Basically, aliases are created to make column names more readable.
Example syntax of SELECT TOP
SELECT TOP example syntax: SELECT TOP number|percent column_name(s) FROM table_name;
What is the SELECT Statement Syntax?
SELECT column_name, column_name FROM table_name; SELECT * FROM table_name;
What are the Aliases (AS) used for?
SQL aliases are used to give a database table, or a column in a table, a temporary name.
What can SQL do?
SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views
What are SQL constraints used for?
SQL constraints are used to specify rules for the data in a table.
What is the function of the Semicolon in SQL?
Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
When creating a table how are tables organized?
Tables are organized into rows and columns; and each table must have a name.
What are the AND/OR Operators used for?
The AND & OR operators are used to filter records based on more than one condition.
What is the BETWEEN operator do?
The BETWEEN operator selects values within a range.
IN Clause
The IN operator allows you to specify multiple values in a WHERE clause.
What is the INNER JOIN keyword do?
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.
What is the UPDATE Statement used for?
The UPDATE statement is used to update existing records in a table.
What is the purpose of WHERE in the DELETE statement?
The WHERE clause specifies which record or records that should be deleted.
Why are all Records updated if you omit the WHERE in the UPDATE Statement?
The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!
Where is the data stored in RDBMS?
The data in RDBMS is stored in database objects called tables.
What are the four basic functions of persistent storage?
The four basic functions of persistent storage can be defined by acronym CRUD
How do you separate the duplicate Values from a Column?
You can list only the different (distinct) values, using the DISTINCT Statement
