ISDS Database Exam Prep - SQL and DB Design
T/F When designing a database and a many to many relationship is formed between two tables, an accompanying third table MUST be created.
True
T/F When using the attribute "SELECT" , you select what you which table you want to use.
True
T/F When using the attribute Update, it adds data to an existing table.
True
T/F When writing query statements it is required that you use the matching camel casing used in the database.
True
T/F: A Not Null Status indicates that the field must be entered?
True
T/F: A recursive relationship can be N:M.
True
T/F: If a primary key does not meet the criteria for an ideal primary key, a DBMS-generated surrogate key should be used
True
T/F: Recursive relationships adhere to the same rules as binary relationships
True
T/F: SQL has built-in functions
True
T/F: SQL is not a standard programming language but is a data sub-language
True
T/F: The condition in WHERE clauses can refer to a set of values by using the IN operator
True
T/F: There are situations where denormalized relations are preferred
True
T/F: When using a DELETE function the table structure remains intact.
True
T/F: When using a DROP TABLE function the table structure does not remain intact
True
T/F: You can identify a Primary Key or Foreign Key by using the CONSTRAINT function
True
T/F? The order of the column names after the keyword SELECT determines the order of the columns in the resulting table.
True
TRUE or FALSE: A VarChar is a character data type that holds a variable-length amount of characters.
True
The main purpose of a resource lock is to maintain database integrity by ensuring all transactions adhere to ACID. True or False?
True
The maximum cardinality determines how a relationship is represented. (True or False)
True
The order of the COLUMNS in your results follows the order you listed them following your SELECT keyword. True/False?
True
There are two type of subtype (exclusive and inclusive)
True
To end a query statement, a semi colon is added at the end of the statement? (T/F)?
True
True or False: Each database design must be created for a particular DBMS product?
True
True or False: If ID-Dependent, then you must add a primary key of the parent entity?
True
True or False: Most DBMSs do not allow updating a view.
True
True or False: The 'Parse' button in SQL is used to determine if there are any errors in the statement.
True
True or False: When joining tables, the words inner and outer are optional.
True
True or False: a Phone Number is an example of a Text Data Type?
True
True or False? You cannot use a WHERE clause with SQL built-in functions.
True
True or false. If there are a maximum of 100 characters for VarChar, only the number of characters inputted will be stored.
True
Use left join when you only want to keep the unmatched record from the left table
True
When a lengthy text field is present we should consider using surrogate key. True/False
True
You can have an Id-dependent entity to be Id-dependent on another Id-dependent entity
True
(T/F) using the "create table" commands a primary key can be defined in two places
True, it can be created with the column constraints or the table constraints
(T/F) The following statements will DROP Table A and Display contents of Table B Drop TABLE A Drop TABLE B Select * FROM TABLE A Select * FROM TABLE B
True, it will drop one table but will not complete the second drop statement.
T/F: Recursive relationships adhere to the same rules as binary relationships
True, they can be 1:1, 1:N, and N:M
A DROP operation cannot be rolled back.
True.
You need to include the word VALUES when inserting new data to a table. (T/F)
True.
To change the data values in an existing row (or set of rows) use the ______ statement.
UPDATE
What is the proper syntax for updating a value?
UPDATE (attribute) SET (attribute) = value 2 WHERE attribute = value 1
To use the UPDATE function in SQL, The statement starts by:
UPDATE Table_Name SET Column=Value2 WHERE Column=Value1;
To change the data values in an existing row (or set of rows) use which statement?
UPDATE.
How do you change data values in existing rows for tables?
Use the Update Statement
What are Aggregate Functions?
Used in SQL to group together multiple rows for a given column to form a single value. Examples of aggregate functions used include COUNT, SUM, MIN, MAX, and AVG.
What is Data Definition Language (DDL)?
Used to change the schema of a database by creating tables, relationships, and other structures.
Define intersection table
Used when creating an N:M relationship. Has a composite key consisting of the keys of each of the tables it connects. Has no attributes besides the primary key.
How do you sort in descending order in SQL?
Using ORDER BY ColumnName DESC (DESC comes after the column name)
Denormalization can introduce modification anomalies, what are some preventative measures we can use to reduce them when creating/modifying our database?
Using database constraints or implementing validity checks.
What does UNIQUE constraint mean?
Values of a column must not use repeated values
Which takes longer to process, VarChar or Char?
VarChar
Why would you use Char instead of VarChar?
VarChar requires extra processing, so is less efficient than Char, particularly when each record is likely to have values of similar length.
Is varchar faster or slower than char?
Varchar is 50% slower
What kind of diagram is the standard method of visualizing sets of data and their overlap when considering the effect of joins?
Venn diagram
What is an intersection table with additional attributes called?
What is an intersection table with additional attributes called?
What is it called when an intersection table has columns beyond those in the primary key?
When an intersection table has columns beyond those in the primary key, the relationship is called an association relationship.
What is an association relationship?
When an intersection table has columns beyond those in the primary key. Every N:M requires an intersection table
What is deadlock?
When each user is awaiting a resource that the other person has locked, preventing the process from proceeding.
What do the IN and NOT keywords do?
When entering a query, the IN and NOT IN as a phrase will help filter your results. For example SELECT FirstName FROM EMPLOYEE WHERE Department IN ('Accounting'); This will show the first names of all employees registered in the Accounting department. SELECT FirstName FROM EMPLOYEE WHERE Department NOT IN ('Accounting'); This will show the first names of all employees NOT registered in the Accounting department.
When is a relation considered normalized
When every determinant is a candidate key
What is denormalization?
When normalization is taken too far we join back the attributes that were split.
When would you use a char over varchar?
When the data stored is always a set length
When are data constraints usually specified?
When the table is actually created in the database
When do we use the GROUP BY statement?
When we want to group a row by common values
When do you use GROUP BY clause?
When you use a combination of "aggregation" attributes and numeric functions.
When is the GROUP BY clause used?
With aggregate functions
What is the default format for Date in SQL?
YYYY-MM-DD
Can Identity columns be setup in Oracle?
Yes, but they cannot auto increment on INSERT lines. Special incrementation functions must be used.
Can ORDER BY clause sorts data by multiple columns?
Yes, it can.
Can you apply more than one condition for the WHERE clause statement?
Yes, you can create multiple conditions using the AND keyword.
The WHERE clause may include the IN keyword to specify that.....
a particular column value must be included in a list of values.
What needs to be entered at the end of every one of your SQL queries?
a semicolon
An intersection table containing additional attributes is called _________. a. Sub-query Table b. Association Table c. N:M Table d. Foreign Key Table
a. Association Table
Which SQL command allows you to group a set of records for aggregation? Select one: a. GROUP BY b. SORT BY c. CLUSTER BY d. ORDER BY
a. GROUP BY
When was SQL developed?
a. Originally developed by IBM as the SEQUEL language in the 1970s
What is the main function of DISTINCT keyword?
added to the SELECT statement to suppress the display of duplicate rows
The GROUP BY clause is often used with _________
aggregate functions (e.g. SUM, AVG)
What are some synonyms for an intersection table?
association table, bridge table, cross-reference table, crosswalk, intermediary table, join table, junction table, link table, linking table, many-to-many resolver, map table, mapping table, pairing table, or transition table
This button in SQL Server Management Studio will check the validity of the syntax of your SQL query, but only the syntax. a) Execute b) Parse c) Validate d) Run
b) Parse
Statements which are used for creating tables, relationships, and other structures. a. DML (Data Manipulation Language) b. DDL (Data Definition Language) c. DCL (Data Control Language) d. TCL (Transaction Control Language)
b. DDL (Data Definition Language)
Which statement permanently removes a database? a. Delete b. Drop c. Remove d. Obliterate
b. Drop
What data type should ZIPCODE use? Assume that you are only storing 5 digit zipcode (92645). a. Char(5) b. VarChar(5) c. Int(5) d. Double(5)
b. VarChar(5)
Which SQL keyword is used to calculate the subtotals? Select one: a. WHERE b. GROUP BY c. ORDER BY d. SELECT e. FROM
b.GROUP BY
Purpose of "Parse" button.
blue checkmark on toolbar; CTRL + F5; checks for errors in command without running data; "Parse" first in order to check for errors
The identifier for the supertype becomes which of the following for the subtype? a. primary key b. foreign key c. both primary and foreign key d. none of the above
c
Which of the following is not a valid SQL keyword? a) BETWEEN b) ORDER BY c) ENABLE d) DISTINCT
c) ENABLE
What is the proper format for inserting a comment? a. //* comment //* b. */* comment */* c. /* comment */ d. // comment //
c. /* comment */
In SQL server, keywords show up in different colors. SQL commands show up as: a. Pink b. Green c. Blue d. Gray
c. Blue
Which of one of these is a SQL data definition statement? a. Insert b. Delete c. Create d. Update
c. Create
SELECT Correct answer(s): Which of these not SQL data definition language? a. Delete b. Merge c. Create d. Update
c.Delete, Merge, and update
SQL SELECT clause specifies which ___ are to be listed in the query results.
columns
What are DDL major statement
create, alter, drop, select
_____________ are like skyscraper window washers traveling up and down the skyscraper to stop at each window. a) Indexes b) Locks c) Transactions d) Cursors
d) Cursors
Which SQL command allows you to select data from multiple tables? Select one: a. RUN ... FROM b. GROUP BY c. CONNECT d. JOIN ... ON
d. JOIN ... ON
Which SQL command sorts the results of a query? Select one: a. SORT IN (optionally using ASC or DESC) b. SORT BY (optionally using NUM or TEXT) c. ORDER IN (optionally using NUM or TEXT) d. ORDER BY (optionally using ASC or DESC)
d. ORDER BY (optionally using ASC or DESC)
SELECT * means: a. show all column values for rows that do not match specific criteria b. delete all column values for rows that match specific criteria c. delete all row values for rows that match specific criteria d. show all column values for rows that match specific criteria
d. show all column values for rows that match specific criteria
What does the DROP function do?
deletes the table and data within the table permanently
What prevents the SQL DROP TABLE command from dropping the table's structure?
does not work if table contains or could contain values needed to fulfill referential integrity constraint
The DISTINCT keyword may be added to the SELECT statement to......
inhibit duplicate rows from displaying.
What does SQL AS keyword do?
it assigns new names to columns in query
What does the "Parse" button (checkmark button) in SQL Server Management Studio do?
it determines if there are syntax errors before executing the query.
Most database management systems provide a mechanism to record activities into a/an _____________________
log file
What is intersection table or associative entity?
new table with primary key is comprised from primary keys of 2 parents
What is the difference between a varchar and nvarchar data types?
nvarchar stores data in Unicode format allow values for non-latin text like Asian languages, accent marks, and other non ASCII characters. varchar only allows ASCII (basic latin characters).
4 minimum cardinalities?
parent optional and child optional (O-O), parent mandatory and child optional (M-O), parent optional and child mandatory (O-M), or parent mandatory and child mandatory (M-M).
OUTER JOIN
record included? all additional, even if no matching found!
INNER JOIN
record included? only if match is found!
SQL WHERE clause specifies which ___ are to be listed in the query results.
rows
SQL FROM clause specifies which ___ are to be used in the query.
tables
What would be the ideal primary key?
the ideal primary key is short, numeric, and fixed.
Denormalization
the process of intentionally designing a relation that is not normalized. Done to improve performance or security.
VARCHAR(n)
variable-length character string having a maximum length of (n)
What is a SQL View?
•It is a virtual table created by DMBS-stored SELECT statement that can combine access to data in multiple tables and even in other views. •We can run a query against a view in the same way that you run a query against a table
What single character can we add to the following statement to make it functional: SELECT OwnerLastName, PetType FROM PET JOIN PET_OWNER On PET.OwnerID=PET_OWNER.OwnerID WHERE PET.PetType Like 'Do_'
"This statement is missing a semicolon(;) after the 'Do_'" The semicolon is optional.
What is the wildcard character that represents characters of text?
%
What are the two wild cards used with LIKE and how are they different?
% is a wildcard for multiple characters and _ is a wildcard for a single character
What is the symbol associated with SQL LIKE wildcard characters?
%(Percent for multiple characters) and _ (Underscore for single character)
Correlated Subquery
(INNER run many times!) **think empSalary, --> to compare AVG based on department.**
Non-correlated Subquery
(INNER run only once!) **think deptID, --> 'Account%' to generate list of employees who work in a department that begins with 'Account'.**
(T/F)In sql 'Outer' always needs to be specified
(T/F)In sql 'Outer' always needs to be specified
What does the "*" symbol mean after a SELECT statement?
* is a variable usually placed after the SELECT statement and is short for "All Columns"
How do you use the DROP/DELETE syntax?
*DELETE = getting rid of row DELETE FROM table_name WHERE condition; *DROP = getting rid of a table DROP TABLE table_name;
How are weak entities represented?
-If not ID-dependent, use same techniques as for strong entities -If ID-dependent, then must add primary key of the parent entity
What must be considered for each entity and attribute?
-Specify primary key -Specify properties for each column Data Type Null status Default value Specify data constraints
What do the basic forms of SQL entail?
-The basic form of SQL queries uses the SQL SELECT/FROM/WHERE framework. In this framework: 1. The SQL SELECT clause specifies which columns are to be listed in the query results 2. SQL FROM clause specifies which tables are to be used . 3. SQL WHERE clause specifies which rows to be listed in query. SQL comments are enclosed in symbols /* and */ . -SQL has a shorthand notation for querying all columns. It is the SQL asterisk (*) wildcard character.
What is the difference between /* */ and -- ?
/* */ is for a comment that has multiple lines while comment with -- can only has one line
What is the format for an SQL comment?
/*TEXT*/
SQL statements are divided into five categories, what are they?
1- Data Definition Language (DDL) : statements used for creating tables, relationships and other structures. 2- Data Manipulation Language (DML) : statements used for querying, inserting modifying and deleting. 3- SQL/ Presistent Stored Modules (SQL/PSM): statements, which extend SQL by adding procedural programming capabilities. 4- Transaction Control Language (TCL) : statements used to mark transaction boundaries. 5- Data Control Language (DCL): statements used to grant database permissions to users and groups.
Why is it better to define primary keys using constraints?
1- You can use constraint to define composite keys. 2- You can choose the name of the constraint that defines the primary key, controlling the name of the constraint has advantages for administrating the database. 3- It allows us to easily define surrogate keys.
Which keywords are used to place a primary key constraint when creating a table?
1. CONSTRAINT 2. TABLENAME_PK 3. PRIMARY KEY ({PrimaryKeyColumns})
The SQL data definition statements include:
1. CREATE 2. ALTER 3. DROP
What are most critical database administration functions?
1. Concurrency control 2. Security 3. Backup and recovery
What is process transitioning from relations to entities to tables
1. Create table for each entity 2. Specify primary key 3. Specify column properties (type, null status (null value allowed), default values if any, data constraints if any ) 4. Re-evaluate structure of table.
What column properties need to be considered when creating a new table?
1. Data type 2. Null status 3. Default value 4. Data constraints should be specified
There are 4 Data Constraints, please explain and give an example
1. Domain Constraint: Specify a set of values that a column may have. Ex.: a domain of day-of-week (Monday, Tuesday,... Sunday) 2. Range Constraint: Specify an interval of allowed values. Ex.: The age of the employees must be greater than 18 and less than 65 3. Intrarelation Constraint: Involve comparison among columns in the same table Ex.: The review date would be at least three months after hire date 4. Interrelation constraint: Involve comparison among columns in different tables (known as Inferential Integrity) Ex.: The workers assignment date in specific project should be later than the project start date.
What are match criteria, and multiple match criteria used in the WHERE clause?
1. Equals " = " 2. Not Equals " < > " 3. Greater than " > " 4. Less than " < " 5. Great than or equal to " > = " 6. Less than or equal to " < = "
Two Disadvantages of Surrogate Key
1. Foreign keys that are based on surrogate keys have no meaning to the users. 2. When data is shared among different databases, it is possible for two different rows, in two different databases, to have the same value of surrogate key.
Specify logic for enforcing minimum cardinality:
1. O-O relationship (No action to apply) 2. M-O relationship (easily enforced by define referential integrity constraint and make FK not null 3. O-M relationship (difficult to enforce. Requires use of triggers or other application code) 4. M-M relationship (very difficult to enforce. Triggers can lock each other out and create many problem)
List four uses of ID-dependent entities
1. Representing N:M relationships 2. Representing association relationships 3. Storing multivalued attributes 4. Representing archetype/instance relationships
Name four uses for triggers
1. Setting default values 2. Enforcing interrelation data constraints 3. Updating views 4. Enforcing referential integrity actions
Describe two disadvantages of surrogate keys?
1. The Values are no meaning to users 2. Need to set up the starting values in different databases to avoid duplicate surrogate key values. However, this is not a problem until we merged data from different databases.
What are the options for setting up primary keys for ID-dependent entities?
1. Use a composite of foreign keys of associated tables if unique 2. Use a composite of foreign keys and a qualifying column 3. Create a new simple primary key
What are the rules of representing recursive relationships?
1:1 and 1:M relationships are saved using foreign keys. M:N relationships are saved by creating an intersecting relation.
ID-Dependent relationships are always?
1:N
What relationship is considered a "parent and child" relationship?
1:N Relationship
How many character is char limited to?
255
What is a different way to express the BETWEEN operator?
<= AND >=
what is the symbol representing not equal?
<>
Which keyword is not used to delete records? A. ALTER B. INSERT C. DROP D. DELETE
A
Which should be used if you want to remove a whole table? a) DROP b) DELETE c) Both a and b d) Neither
A - DROP
In SQL Management Studio, which button will allow you run the program. a) Execute b) Parse c) Both a and b d) Neither
A - Execute
Each attribute of an entity becomes___of a table. A column B primary key C foreign key D Surrogate Key
A Column
What is a GROUP BY clause?
A GROUP BY clause can be used to retrieve categorized results. It uses other functions (such as COUNT) and groups results into columns.
What is SQL view?
A SQL View is a virtual table created by a DBMS-stored SELECT statement which can combine access to data in multiple tables and even in other views.
Which SQL keyword is used to change one or more rows in a table? A UPDATE B INSERT C SELECT D DELETE
A UPDATE
______ is used to represent a single unspecified character in SQL query. a) __ b) % c) * d) #
A ___
What's the difference between a Non-Correlated and Correlated Sub-query?
A correlated query is one where the sub-query is dependent on the value of the parent query. Hence, it cannot be ran independently. A sub-query that does not reference the objects in the parent query is called a non-correlated sub-query.
What type of language is SQL?
A data sub-language.
When do you use a GROUP BY clause?
A group by clause will gather all the rows together that has data contained in a specified column and allows aggregate function to be performed on one or more columns.
What is a data sublanguage?
A language for defining and processing a database intended to be embedded in programs witten in another language.
In an ER diagram, what does "N:M" stand for?
A many to many relationship
What is a recursive relationship
A recursive relationship is a relationship between a relation and itself.
What is a recursive relationship?
A recursive relationship occurs when a table is related to itself
Database design
A set of database specifications that can actually be implemented as a database in a DBMS.
What is Entity type?
A set of entities that have the same characteristics.
What is database design?
A set of specifications that can actually be implemented as a database in a DBMS.
What is a stored procedure?
A stored procedure is a named SQL statement that is stored in the database management system that includes one or more queries, updates, inserts, deletes, or other SQL statements.
What is Data Manipulation Language (DML) ?
A sub-language of SQL. Statements used to query, insert, modify, and delete data.
Define subtype
A subtype is an entity that contains the specialized, noncommon attributes from the several entities
When should you use a Surrogate Key
A surrogate key is used when a table does not have a unique key, or when a unique key is too long, non-numeric and may change.
What is an intersection table?
A table in many-to-many relationship that has a composite key consisting of the keys from each of the tables that it connects.
What is a trigger?
A trigger is a stored program that is executed by the DBMS whenever a specific event occurs.
What is a default value?
A value that is assigned to an attribute if there is none assigned when a new row is created in a table.
How minimum cardinalities are reflected?
A) By having foreign keys listed as "NULL" B) By having foreign keys listed as "NOT NULL" C) By having foreign keys listed as "NOT NULL" or "NULL" D) By having foreign keys listed as "NOT NULL" and "NULL"
Each attribute of an entity becomes a(n) ________ of a table. A) column B) primary key C) foreign key D) alternate key
A) Column
In a 1:N relationship, the foreign key is always set up such that the primary key of the _________ entity becomes the foreign key in the _________ entity.
A) parent, child B) child, parent C) primary, secondary D) secondary, primary
Intersection tables are used when there are which types of relationships?
A. 1:1 B. 1:N C. M:N
Multiple matching criteria may be specified using: A. AND, OR B. WITH, OR C. AND, WITH D. Neither
A. AND, OR
Which format is correct when creating tables? A. Column name, Data Type, Column Constraints B. Column Constraint, Data Type, Column Name C. Data Type, Column Name, Column Constraint
A. Column name, Data Type, Column Constraints
Which of the following creates an ID variable starting at 10000 and increasing by 1? A. CustomerID Int NOT NULL IDENTITY(10000,1), B. CustomerID VarChar(10000) NOT NULL IDENTITY(1), C. CustomerID Int NOT NULL INCREMENT(10000,1), D. CustomerID Int NOT NULL INCREMENT(10000+1),
A. CustomerID Int NOT NULL IDENTITY(10000,1),
If you want to add a new constraint to a table, which is the SQL command you should use?
ALTER
Which SQL keyword is used to change the structure, properties or constraints of a table? A) CHANGE B) CREATE C) SELECT D) ALTER E) UPDATE
ALTER
Write the statement to add the field "Breed' to the table "DOG"
ALTER TABLE DOG ADD Breed Char(20) NULL;
What are logical operations?
AND, OR, NOT
What standard is SQL based on?
ANSI/ISO Standard
You can assign a column name to the result by using the
AS Keyword
What is the default sort order when using the ORDER BY clause?
ASC
What is ASP.Net?
ASP.NET , the follow up to microsoft Active sever pages is used in web pages to create web-based database applications.
What will be the result of the following statement: DELETE FROM MyTable; ?
All rows in the table will be deleted
Choose the correct Answer The SQL data manipulation statements include: 1. Insert 2. Update 3. Delete 4. All the Above
All the Above
Explain the meaning of the expression ACID transaction
An ACID transaction is one that is Atomic, Consistent, Isolated, and Durable
Define an INNER JOIN.
An INNER JOIN combines two tables where the join on column is the same
What is an SQL view?
An SQL view is a virtual table that is constructed from other tables or views
How does an association table differ from an intersection table?
An association table has additional attributes that pertain to the relationship itself
What is the difference between an entity and a table?
An entity is a database design concept while a table is the implementation of that entity in an actual database.
What is the difference between an INNER and OUTER JOIN?
An inner join display results with only rows that have matching results in the joined tables whereas an outer join includes all rows from one of the tables in the join regardless if it matches.
What is the difference between inner and outer joins?
An inner join only displays data from the rows that match based on join conditions. An outer join shows all rows of a table in the relation whether or not they match the join condition.
What is an intersection table?
An intersection table (associative entity) is a table that is created to implement a N:M relationship.
What is an intersection table?
An intersection table has a composite key consisting of the keys from each of the tables that it connects.
What is an intersection table?
An intersection table is a table that defines many to many relationships. It acts as an intersection between two tables.
The SQL keyword(s) ________ is used with wildcards. A. LIKE only B. IN only C. NOT IN only D. IN and NOT IN
Ans. Option A
Which of the following functions can be used on both numeric as well as non-numeric data? A - COUNT B - AVG C - STDDEV D - VARIANCE
Ans. Option A
The SQL WHERE clause: A. limits the column data that are returned. B. limits the row data are returned. C. Both A and B are correct. D. Neither A nor B are correct.
Ans. option B
Which of the following is not true about removing rows from a table? A - You can use a subquery in a DELETE statement. B - Specific rows are deleted based on the WHERE clause condition. C - A statement like, DELETE , would cause deletion of the table from the database. D - All of the above.
Ans. option C
Which one is the default function in SQl server? a. On DELETE CASCADE b. ON DELETE No action c. None of the above
Answer B
Which one is correct: A. Although primary keys can be used as a column constraint, we prefer to always use it as a table constraint B. CHECK constraint is used to define referential integrity constraints C. FOREIGN KEY constraint is used to define data constraints. D. If you do not specify the null status using NULL or NOT NULL, then NOT NULL is assumed.
Answer: A
Which of the following is not true about removing rows from a table? A - You can use a subquery in a DELETE statement. B - Specific rows are deleted based on the WHERE clause condition. C - A statement like, DELETE , would cause deletion of the table from the database. D - All of the above.
Answer: C
Which of the following is true when representing a 1:1 binary relationship using the relational model? A) The key of the entity with the highest minimum cardinality must be placed in the other entity as a foreign key. B) The key of each entity must be placed in the other as a foreign key. C) The key of either entity is placed in the other as a foreign key. D) The key of the entity with the most attributes must be placed in the other entity as a foreign key. E) Both entities must have the same primary key.
Answer: C
Which one is incorrect: A.The ON UPDATE action would need to be specified for nonsurrogate data keys B.ON UPDATE NO ACTION is the default. C.ON UPDATE NO ACTION indicates that updates to the primary key for a table that has present should be prohibited D.You should define parent tables before child tables and DROP a child before a parent
Answer: C
When inserting data, you can omit the column list on the situation that: A. Providing data for all of the columns B. Data is in the same order as the columns in the table C. Have no surrogate keys D. All of them
Answer: D
Which one is not a column constraint: A. PRIMARY KEY B. NOT NULL C. CHECK D. DEFAULT Edited: Which ones is not necessarily a column constraint ?
Answer: D; Edited: Correct Answer is A/C; DEFAULT is a column constraint that applies a default value to a column when a Insert statement does not explicitly assign a particular value; CHECK can be a column level constraint if it involves single column or it can be applied to multiple columns at the table level; Same with PRIMARY KEY can be applied as a column constraint but most of the times defined as a table constraint. NOT NULL is column constraint. Also question need to be modified as Which ones are not necessarily a column constraint ?
(T/F) Char should be used when the sizes of the data entries vary considerably.
Answer: False, Char should be used when fields always have text of same length.
In a N:M relationship of two tables, an_______________need to be created, the new table has composite key consisting of the_____________from the two tables, and the relationship is known as _______________________ .
Answers: Intersection table, primary keys, association relationship
When can you use the AS clause
Anytime you are introducing a new column or database to the SQL Query
When an intersection table has columns beyond those of the primary key, what is this relationship called?
Association Relationship
What is the different between association and intersection tables? When we use an intersection table?
Association tables have user data; but intersection table do not We use intersection table when there is N:M Relationship between strong entities. Intersection table only holds the primary keys of the two tables as foreign keys and this combination keys serves as the composite primary key of the intersection table itself.
What is another term for "Associative Table"
Associative Entity
In a relational database design, all relationships are expressed by creating a/an ________. A) primary key B) foreign key C) supertype D) alternate key
B
Which should be used if you want to have minimal storage space? a) VarChar b) Char c) Both a and b d) Neither
B - Char
To which of the following actions are referential integrity constraints not applied? A Update B Create C Insert D delete
B Create
An SQL virtual table is called a ____________. a) vtable b) view c) index d) triggers
B View
1. The SQL functions SUM, AVG, MIN, MAX, and COUNT, are called: a. arithmetic b. agregate c. compound d. mathematical
B, Agregate
SQL comments are enclosed in the following symbols: A. $* and *$ B. /* and */ C. ( and ) D. %* and *%
B. /* and */
What SQL operation is used to display data obtained from more than one table? A. Subquery B. JOIN C. GROUP BY D. IN
B. JOIN
What is a casual relationship? A. Two attributes that can be merged, despite having no true relationship e.g. CarCost and Income. B. When a primary key of one table is inserted into another, without formally declaring it a foreign key, to allow for missing data. C. When either primary key is equally likely to be a foreign key candidate. D. A physical and emotional relationship between two tables without necessarily demanding or expecting the extra commitments of a more formal Primary-Foreign key relationship.
B. When a primary key of one table is inserted into another, without formally declaring it a foreign key, to allow for missing data.
What keyword did SQL provide to allow users to specify a minimum and maximum value on one line?
BETWEEN
In SQL server, SQL commands are displayed in what color?
Blue
In SQL Server, keywords show up in different colors: SQL commands are ______ Advanced functions are ____ Comments are ___ Other keywords in ____
Blue, Pink, Green, Grey
How is 1:N relationships represented with foreign keys between strong entities?
By Placing the primary key of the parent in the child as a foreign key.
How would the user define surrogate key in the create table statement?
By using IDENTITY keyword
4) Which SQL keyword is used to impose restrictions on a table, data or relationship? A Set B Create C Constraint D Alter
C Constraint
Which of the following is not true about primary keys? A) Primary keys cannot be null. B) Primary keys must be unique. C) Primary keys must be a single attribute. D) Primary keys are used to represent relationships. E) Primary keys can be defined using an SQL CONSTRAINT phrase.
C) Primary keys must be a single attribute.
Which is not a SQL built-in function? A. MIN B. MAX C. DIV D. SUM
C. DIV
Which statement would delete the STUDENT table and all its data? A. DELETE FROM STUDENT B. DELETE FROM STUDENT; C. DROP TABLE STUDENT;
C. DROP TABLE STUDENT;
Which keyword is not used to modify data in SQL? A. UPDATE B. INSERT C. JOIN D. DELETE
C. JOIN
In a query such as: WHERE C.Name LIKE 'J[^oa]n'; Which answer below best describes the result? A. Jon B. Jan c. Jen D. Both A and B
C. Jen, The carrot symbol means you are searching for things not similar to what is in the bracket
Which of the following interfaces we did not use it to write SQL in class? A. Oracle Live SQL online B. SQL*Plus C. SQLite D. SQL Developer
C. SQLite
What would the following query do: SELECT S.Major, AVG(S.GPA) AS AvgGPA FROM Student S WHERE S.Major LIKE 'B%' GROUP BY S.Major HAVING AVG(S.GPA)>3.5; A. Show Majors with an average GPA above 3.5 B. Show Majors with an average GPA above 3.5 and the Major name ends in a B C. Show Majors with an average GPA above 3.5 and the Major name starts with a B D. Show Majors that start and end with a B and with an average GPA above 3.5 E. None of the above
C. Show Majors with an average GPA above 3.5 and the Major name starts with a B
What should you do when you want to delete a table from the database? A. Use the DELETE statement B. Use the ALTER statement C. Use the DROP statement D. Use the CREATE statement
C. Use the DROP statement
When is a default value inserted into a table? A. Only when a current value is deleted. B. When a current value is deleted, or a record is inserting with NULL for that attribute. C. When a record is inserted with NULL for that attribute. D. Never, the all values for the attribute must be the default value.
C. When a record is inserted with NULL for that attribute.
Which is used for a fixed length string, CHAR or VARCHAR?
CHAR
What is the difference between CHAR and VARCHAR?
CHAR is a fixed length VARCHAR is variable-length
What is the primary difference between CHAR and VARCHAR?
CHAR is a fixed length data type while VARCHAR is a variable length datatype.
3. The _________ Constraint is used to create restriction on values that are allowed to appear in a column?
CHECK Note: FOREIGN KEY constraint also creates restriction on values that are allowed to appear in a column. However, the difference is that CHECK constraint enforces domain integrity whereas FOREIGN KEY enforces referential integrity. The difference is how database determine values are valid and allow them - FOREIGN KEY constraints obtain the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression.
Which keyword is used to impose restrictions on columns?
CONSTRAINT
Syntax of primary key constraint.
CONSTRAINT TABLENAME_PK PRIMARY KEY ({PrimaryKeyColumns})
What are the five aggregate functions in SQL for data retrieval?
COUNT, MIN, MAX, SUM, AVG
SQL Built-In functions are:
COUNT, SUM, AVG, MAX, MIN which are placed in SELECT
What are some built-in- number functions?
COUNT: Counts the number of rows that match the specified criteria. MIN: Finds the minimum value for a specific column for those rows matching the criteria. MAX: Finds the maximum value for a specific column for those rows matching the criteria. SUM: Calculates the sum for a specific column for those rows matching the criteria. AVG: Calculates the numerical average (mean) of a specific column for those rows matching the criteria. STDEV: Calculates the standard deviation of the values in a numeric column whose rows match the criteria.
What are the SQL statements used for data definition language?
CREATE - create database objects ALTER - modify the structure/characteristics of database objects DROP - delete database objects
How we can update the view?
CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
What are the elements of SQL Data Definition Language (DDL)?
CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE
How would you write SQL code created a query
CREATE VIEW query_name AS SELECT table
What are the SQL data define statements?
CREATE, ALTER, DROP
AVG
Calculates the numerical average of a specific column for those rows matching the criteria
SUM
Calculates the sum for a specific column for those rows matching the criteria
What data type is this an example of 'MN'?
Char
What is the difference between char and varchar? And Which One is Faster?
Char is a fixed-length data type. The storage size of the char value is equal to the maximum size for this column. Because varchar is a variable-length data type, the storage size of the varchar value is the actual length of the data entered, not the maximum size for this column. Char runs faster than varchar.
What is the difference between Char and Varchar?
Char is of a fixed length while varchar is a variable length data type.
What is the difference between char and varchar?
Char stores character strings of a fixed length while varchar stores a variable number of characters.
What is the difference between char and varchar?
Char(n) is fixed-length character string of length n. VarChar(n) is variable length character with maximum string up to n.
Name any four data types used in SQL?
Char(n), Nchar(n), Varchar(n), Nvarchar(n), Date, Time, Integer, Decimal(m,n), Numeric(m,n)
What is the difference between Char and VarChar?
Char: Fixed length, Saves processing power. Used for fields where we always have text of same length. VarChar: Variable-length character data type, Saves storage space. Used for fields having vast difference of text length.
What is the difference between char and varchar?
Char: n-byte fixed length string data. Varchar: n-bye variable length string data. Extra processing is required for Varchar columns.
What kind of constraint is on a table where it limits the value in one column based off the values in another column in the same row?
Check Constraint
In 1 : N relationship, the entity that is on the many side of the relationship is called ________ entity.
Child
When defining attributes, what do the commas tell us?
Commas tell us that the attribute is done being defined, and we can now move on to defining the next one.
COUNT
Counts the number of rows that match the specified criteria
What is the first step of transitioning from a data model to a database?
Create a table for each entity.
What are the 3 steps to transform a data model into a database design?
Create table for each entity, Place foreign Keys, Specify logic for minimum cardinality.
Which SQL keyword is used to change the structure, properties or constraints of a table? A) DROP B) CREATE C) DISPOSE D) ALTER
D
Referential integrity constraints are not applied to ______ of the listed actions. a) modify b) insert c) delete d) create
D Create
In what cases is denormalization used for? A) Sometimes normalization is taken too far, consider the modification problems B) Used for security or performance reasons C) Used if cost of modification problems is low D) All of the above
D) All of the above
Referential integrity constraints do not affect which of the following SQL keywords?. a) modify b) insert c) delete d) create
D) Create
Which SQL keyword is used to eliminate duplicate rows in the results of an SQL SELECT query? A) UNIQUE B) SORT C) ORDER BY D) DISTINCT E) GROUP BY
D) Distinct
Which of the following cannot be done using the CONSTRAINT phrase? a. Create a single attribute primary key. b. Define a foreign key. c. Establish a referential integrity constraint. d. All of these can be done using the CONSTRAINT phrase. e. None of these can be done using the CONSTRAINT phrase.
D. All of these can be done using the CONSTRAINT phrase.
Which minimum cardinality enforcement action is the most difficult to reinforce? A. O-O relationship B. M-O relationship C. O-M relationship D. M-M relationship
D. M-M relationship
Which of the following is NOT a step for transforming a data model into a relational database design? A. Create relationships by placing foreign keys B. Create a table for each entity C. Verify appropriate normalization D. Making sure the relational database design can be queried in Oracle Live SQL.
D. Making sure the relational database design can be queried in Oracle Live SQL.
Certain data types are allowed/given access by each: BCNF Constraint DBMS AIP
DBMS
The DROP keyword belongs to which set of functions in SQL?
DDL
What is DDL?
DDL is data definition language, which is used to create, edit, and delete database structures. For example, data definition statements include: - Create database objects, like create a table - Modify structure and characteristics of database objects, like alter a table - Delete database objects, like drop a table
What is the difference between DDL and DML?
DDL statements are used for creating, defining and managing the Database structure. DML statements are used for managing data within Database.
Which command is used to remove rows from table EMPLOYEE?
DELETE FROM EMPLOYEE;
DELETE
DELETE = getting rid of row: DELETE FROM table_name WHERE condition;
What is the difference between DROP and DELETE?
DELETE deletes a row or set of rows from a table; DROP drops the table structure along with all of the table's data.
What is the main difference between DELETE and DROP in SQL?
DELETE statements will delete the data within the table while the DROP statement deletes the whole table
What is the difference between DROP and DELETE?
DELETE, removes a row or set of rows from a table DROP, removes unwanted database objects from a database
To delete a row or set of rows from a table use which statement?
DELETE.
Which SQL keywords in a SELECT statement eliminates duplicate rows in a result set?
DISTINCT
Which keyword may be added to the SELECT statement to inhibit duplicate rows from displaying?
DISTINCT
Which keyword should be used to remove duplicate rows from displaying?
DISTINCT
What keyword can be added to the SELECT statement to get rid of duplicate rows from displaying?
DISTINCT Example: SELECT DISTINCT ColumnName FROM TABLE_NAME WHERE ColumnName IN (4, 6, 9);
Are Insert, Update, and Delete DML statements or DDL statements?
DML Statements as they manipulate the data
What is DML?
DML is data manipulation language, which is used to create, edit, and delete data. For example, data manipulation statement include: - Insert, to add new data to an existing database - Update, to modify data in an existing database - Delete, to delete data from the an existing database
If you want to delete a view, which command would you use to do the task?
DROP
What SQL Keyword is used to delete a database object?
DROP
Which SQL command will remove a table from a database?
DROP
Which command will remove a table from the database?
DROP
__________ will delete the database objects.
DROP
Write the statment for dropping the table titled "CUSTOMER"
DROP TABLE CUSTOMER;
What is the difference between DROP and DELETE
DROP deletes existing database object permanently DELETE deletes the rows in a table that match the specified criteria
What is the difference between the SQL DROP statement and the SQL DELETE statement?
DROP removes the entire table, while DELETE removes only the data in the table.
What is the difference between DROP and DELETE?
DROP will delete all data and the Table from the database DELETE will delete the data from the specified Table
What is the difference between DROP and TRUNCATE tables?
DROP will delete the entire table, while TRUNCATE will only delete the information in the table.
To remove unwanted database objects from the database use which statement?
DROP.
What Does DDL Stand for? Distributed Data Language Definitional Data Language Data Definition Language
Data Definition Language
What does DDL Stand for?
Data Definition Language
What does DDL stand for
Data Definition Language
What is the difference between DROP and DELETE?
Delete: delete all the data but leave the table structure DELETE FROM PROJECT WHERE Department = 'Marketing'; Drop: delete the table's structure along with all of the table data. The DROP statement will permanently remove the object and all data.
What is the difference between DROP & DELETE?
Delete: delete all the data but leave the table structure. Drop: delete the table's structure along with all of the table data. The DROP statement will permanently remove the object and all data.
What does DELETE FROM do?
Deletes specific data from the table
What is denormalization?
Denormalization occurs when you take a fully normalized entity and combine some of the separated tables. This can be useful for displaying data that would've been distributed among multiple tables.
What are concurrency reading issues?
Dirty reads, Inconsistent reads, Phantom reads
Who invented SQL?
Donald Chamberlin and Raymond Boyce invented SQL while working at IBM in the early 1970s.
What is the difference between Drop and Delete?
Drop is used to alter the table structure where Delete is used to change data.
The SQL Statement below will show which of the following results: SELECT * FROM COURSES C LEFT JOIN PROFESSOR P ON C.PID=P.PID; A. Courses that are assigned to a Professor B. Professors who are not assigned to a course C. Courses that are not assigned to a Professor D. All of the Above E. A & C
E. A & C
Which of the following is NOT a DML Command? A. UPDATE B. DELETE C. MERGE D. INSERT E. All of the Above are DML
E. All of the Above are DML
The ALTER DDL Command can NOT do which of the following? A. Add a Foreign Key Constraint to a Table B. Add a Primary Key Constraint to a Table C. Add another Attribute to a Table D. Change an Attributes Data Type E. Delete a table
E. Delete a table
Why can primary keys never be null?
Each row must have a unique identifier
What is concurrency control's role?
Ensures that one user's actions do not adversely impact another user's actions
A relation is considered normalized when:
Every determinant is a candidate key
What is the difference between implicit and explicit locks?
Explicit locks are placed by command, implicit locks are placed by the DBMS
What does XML stand for?
Extensible Markup Language
What is XML?
Extensible Markup Language
(T/F) An ascending sort by date lists items from Newest to Oldest.
F
(T/F) The % symbol represents the single character wildcard.
F
(T/F) The AND keyword represents union of data sets whereas the OR keyword represents intersection of data sets.
F
(T/F) If the DATE entity is placed on the leftmost position on the FROM clause and SALE entity is placed on the rightmost position and both entities are joined together, a RIGHT INNER JOIN will display all dates, regardless if there was a sale on that date.
FALSE
(T/F) The SQL keyword DELETE is used to delete a table's structure.
FALSE
(T/F) The primary key of the subtype tables is always different from the primary key of the supertype table.
FALSE
A DROP statement is used to change the order of the rows.
FALSE
A view definition can include an ORDER BY clause. T/F
FALSE
Functions such as AVG and SUM work on text data types. TRUE or FALSE
FALSE
In Data Modeling, N:M relationships must always always include an intersection entity.
FALSE
Maximum cardinalities determine whether a foreign key is required or not required in the database design. (True/False)
FALSE
Only two tables can be queried by using a subquery.
FALSE
T/F A nested SQL function may return multiple values
FALSE
T/F The * in SELECT * clause of an SQL statement is not a wildcard for returning all columns in a table.
FALSE
T/F The SQL function GROUP BY should always be specified after the ORDER BY function
FALSE
TRUE/FALSE is it a good practice when inserting NULL values to put them into quotes?
FALSE
True or False: The following statement is valid; TRUNCATE TABLE table_name WHERE column_name=value;
FALSE
True or False: The format to add a column is ALTER TABLE tablename ADD COLUMN column_name new_data_type;
FALSE
T/F ORDER BY clause enables user to order data in ascending or descending order on one or more columns. It comes before GROUP BY clause.
FALSE It comes after GROUP BY clause.
Columns can be added or dropped without using the ALTER TABLE statement first (TRUE or FALSE)
FALSE, ALTER TABLE TableName is first required.
The ON UPDATE CASCADE statement is used in EMPLOYEESKILL table to ensure that the deletion of employee in EMPLOYEE table is reflected in order to maintain data integrity? 1.True 2. False
FALSE, ON DELETE CASCADE
Is this the correct use of the DELETE statement? T/F DELETE TABLE;
FALSE, correct format is: DELETE FROM TABLE;
TRUE/FALSE: An explicit lock is a lock set automatically by the DBMS.
FALSE. This would be an implicit lock.
What's the difference between FULL OUTER JOIN and JOIN...ON?
FULL OUTER JOIN: Including records both of the right and left table regardless whether a match a exist. If a match exist, the record will be match together. The JOIN... ON syntax can be also be used to perform a join. It has advantage from moving the JOIN syntax into the FROM clause
(T/F) An ID-Dependent entity does not share any part of its primary key with any other entity.
False
(T/F) DISTINCT keyword is used if user wishes to see duplicate columns.
False
(T/F) In 1:1 relationships, it matters which table receives the foreign key.
False
(T/F) Primary Key constrains can only be added while the create query is run.
False
(T/F)Drop Table deletes only one row
False
(T/F)SQL Queries are default in descending order
False
Cascade deletion of records causes database inconsistency and must be avoided. (True/False)
False
Deadlock can occur when applications lock only a single resource. True or False?
False
If you want to delete the data in one single cell of a table, you can use 'delete' syntax.
False
In SQL keyword WHERE is used to specify the tables that contain the data to be retrieved. True/False
False
Left Join is different from Left Outer Join
False
Recursive relations must be eliminated by creating separate tables. (True/False)
False
SQL DROP TABLE statement will drop the table but not all of its data. True or False
False
SQL is a programming language. True or False?
False
SQL wildcard character "%" indicates a single, unspecified character in a specific location in an SQL query. (T/F)
False
T/F SQL stands for Sorted Query Language
False
T/F When a column name is italicized and not underlined, that means it is part of the primary and foreign key.
False
T/F When using the attribute " WHERE" , you input what category the information is in
False
T/F When using the attribute DROP, it only deletes part of the table
False
T/F a subquery is the same thing as a JOIN?
False
T/F: Drop keyword is used to delete a record from the table
False
True or False a Surrogate Key is a constraint
False
True or False, JOIN indicates an outer join
False
True or False: A Char data type is a variable-length character string having maximum length
False
True or False: A Null Status indicates that the field must be entered?
False
True or False: A maximum of only two subqueries may be used in an SQL statement.
False
True or False: You can use built-in aggregate function in SQL WHERE.
False
True/False: If not ID-dependent, then must add primary key of the parent entity.
False
Views are temporary tables created to store data for future analysis. (True/False)
False
You can not join more than two tables together using the JOIN function.
False
It is required to specify column names when using INSERT INTO (T/F)
False - defining column names is not required when adding values to all columns of the table.
True or False; Data can be inserted into a table before the type is specified, as long as the column has a name.
False, A table requires both a column name and type before it is initialized, and before data can be inserted.
True or False: SQL is a programing language.
False, SQL is a data sublanguage.
T/F Aggregate functions can be used in a WHERE clause
False, aggregate functions should be used with a HAVING clause
True or False: In the SELECT statement framework, most queries follow a common pattern with three keywords used together. It is SELECT/FROM/GROUP BY
False, it should be SELECT/FROM/WHERE
(True/False) The ORDER BY clause will default as descending unless specified otherwise?
False, it will sort in ascending order. The ASC keyword and DESC keyword can be use to specify ascending and descending order.
This is an accurate statement into CONTACT table: SELECT INTO CONTACT VALUES (6, 1,'Peter')
False, no semicolon ;
True or False: In the Where clause, text and integers need quotation marks around them?
False, only text requires quotation marks
(True/False) The SQL DELETE statement deletes table data AND table structure.
False, the DELETE statement only removes data from the table. The structure remains intact.
(True/False) The SQL DROP TABLE segment deletes table data, but NOT the table structure.
False, the DROP statement, deletes the table data AND the table structure when executed.
SQL is a programming language. T or F
False. It is a data sub-language.
T/F The SELECT clause, is intended to define the name of the table.
False. SELECT identifies the column name, FROM a specific table.
T/F ORDER BY sorts Descending by default?
False. Sorts Ascending
T/F In SQL, the '*' character and '%' character are wild cards for single character and strings, respectively.
False. The '_' is the single character wildcard.
T/F When using the DROP and DELETE clauses, neither of these can be undone or rolled back.
False. The DELETE clause is exempt from this and can be undone or "rolled back"
True or False? The identifier of the subtype becomes the primary and foreign key of the supertype.
False. The identifier of the SUPERTYPE becomes the primary and foreign key of the SUBTYPE.
A Dirty Read occurs when a transaction re-reads a data set and finds that the data have changed. T/F?
False. This describes an Inconsistent Read.
T/F COUNT is used to get a total for numeric columns?
False. Use SUM() to get the total of a numeric column
T/F After writing query statements you MUST select the parse option before executing.
False. While its always a good idea to check the syntax of your statements, selecting the parse button is optional before execution.
You use the Sort By clause to order result by a certain field
False. You use Order By
True or false. A strong entity and has its own unique primary key is called an ID-relationship.
False. it is a non-identifying relationship.
True or False: Microsoft Access in default mode uses ANSI-92
False: Microsoft Access in default mode uses ANSI-89, but you can change it to 92 if you go to the options menu.
T/F: Subqueries can display data from more than 1 table
False: They can only display data from the top level table. You should use a JOIN
T/F HAVING clause applies conditions before groups are formed
False; HAVING clause acts as a filter after groups are formed.
T/F Inserting values into a table can be done with double quotes " "
False; SQL only uses single quotes ' '
MIN/MAX
Finds the minimum/maximum value for a specific column for those rows matching the criteria
Where does a foreign key in a 1:N relationship go?
Foreign Key always goes on the many side.
Which type of Outer Joins shows all values from both the tables?
Full Outer Join
Subtotals may be calculated by using the ______ clause.
GROUP BY
You can use the ________ clause to group rows by common values.
GROUP BY
How does GROUP BY function work?
GROUP BY clause can retrieved results that are categorized. Example: SELECT deptId, COUNT (*) AS numberOFEmployees (result of the count function to be called as numberOfEmployees) FROM Employee GROUP BY deptId;
When do you use a GROUP BY clause? When do you use the HAVING clause?
GROUP BY clause: group row by common values. The HAVING clause may be used to restrict which data is displayed.
When do you use a GROUP BY clause? When do you use the HAVING clause?
GROUP BY: when you want to group rows together in your query results. HAVING: when you want to specify which rows are in the groupings in a GROUP BY clause.
In order to join a table by itself what must you do to it?
Give the table two different aliases
When using calculations in combination of regular attributes you must use the _______ clause or you will get an error
Group By
WHERE is to individual rows as HAVING is to _____________
Groups/aggregated data
4. This keyword is used to filter and add restriction to the results from GROUP BY clause. 1.CHECK 2.HAVING 3.WHERE 4.ORDER BY
HAVING
The _____ clause may be used to restrict which data is displayed
HAVING
When using aggregate function, which keyword that is used to replace WHERE clause?
HAVING clause is used in aggregation function to replace WHERE clause..
How is HAVING different from WHERE?
HAVING is used with groups or aggregates of rows. Also, HAVING is used instead of WHERE when you use an aggregate function (Count, Sum, etc.) with a GROUP BY clause.
Difference between having and where clause
HAVING return rows where aggregate values meet the specified conditions while WHERE applies the condition
7.40, With respect to HTTP, what does stateless mean?
HTTP is stateless. This means that it does not maintain any record of connection or session between the client and the server. Thus, if same client at a browser make simultaneous request of a Web Server using HTTP, the Web Server will treat it like a new request from a new client. No data about the past connection with the client is maintained. The serer processes the client request and sends back the results.
Which company made SQL commercially available in the early 1980s?
IBM made SQL commercially available with its IBM DB2 product.
What are the rules concerning primary keys for ID-dependent entities?
ID-dependent must add primary key of the parent entity.
In Microsoft SQL Server ___ is used to specify surrogate keys.
IDENTITY ({StartValue}, {Increment})
what is an equivalent SQL keyword used to replace multiple OR conditions in a SQL Statement? 1. IN 2. NOT IN 3. BETWEEN
IN
What is the difference between INNER JOIN and OUTER JOIN?
INNER JOIN combines the data between two tables where data exists while OUTER JOIN includes everything including null values.
What are the three types of joins?
INNER, LEFT, and RIGHT
To add a row to an existing table, use the __________ statement.
INSERT
What statement should we use to add a row to an existing table?
INSERT
You can add records to the database using which SQL statement?
INSERT
What are the SQL statements used for data manipulation language?
INSERT - add data to the database UPDATE - modify data in the database DELETE - delete data from the database
How do you insert data into a table?
INSERT INTO table_name ( Attribute (don't say data type, just name) VALUES (MatchAttribute);
How do you insert data in SQL?
INSERT INTO tablename (Attribute1, Attribute2, Attribute3) VALUES('value1', 'value2')
What are the three main SQL data manipulation statements?
INSERT, UPDATE, and DELETE
What are the 4 statements of SQL DML and what do each of them do?
INSERT: used to add rows of data to a table. Statement has a number of different options. UPDATE: is used to change values of existing rows. DBMS will enforce all referential integrity constraints when processing UPDATE command. DELETE: Delete's records from a table. MERGE: essentially combines the SQL INSERT and SQL UPDATE statements into one that can either insert or update data depending upon whether some condition is met.
What is the difference between data type INT and DOUBLE?
INT does not allow decimal, while DOUBLE does since it is a large number with a floating decimal point.
An ---- constraint limits a column's values in comparison with other columns in the same table.
INTRARELATION
The __ becomes the primary key and foreign key of subtype?
Identifier
How do you create a surrogate key in SQL?
Identity(n,n)
What is the disadvantage of optimistic locking?
If a lot of activity in a given row occurs, the transaction might have to be repeated many times until it proceeds without error. Transactions like purchasing a popular stock are poorly suited for this type of locking.
Where does a foreign key in a 1:1 relationship go?
If both sides of the relationship are optional, it does not matter where the foreign key goes. If only one side is optional, the foreign key goes in the optional side.
In what situation would a bulk INSERT of data be appropriate?
If many values found in Table A need to be added into Table B.
What are the rules concerning primary keys for ID-dependent entities? o
If not ID-dependent, use the same techniques as for strong entities. o If ID-dependent, then must add primary key of the parent entity.
What is a Full Outer Join?
In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause.
What is the difference between a correlated and uncorrelated subquery?
In a correlated subquery, the inner query must be run repeatedly whereas in an uncorrelated subquery it is only run once.
Data Types
In each column you indicate exactly what type of data will be stored in that column.
What are the two different types of Joins that can be used to combine data?
Inner Join (Joins only if a match occurs) Outer Join: including Left, Right, and Full Outer Joins (Joins even if data between tables does not match)
What is the difference between an INNER & OUTER JOIN?
Inner join: only displays data from the rows that matched based on join conditions, and data can be lost or at least appear to be lost. Outer join: avoid the data lost from inner join.
What is the difference between an INNER and OUTER JOIN?
Inner join: only displays data from the rows that matched based on join conditions, and data can be lost or at least appear to be lost. Outer join: avoid the data lost from inner join. SELECT FirstName, LastName, HoursWorked FROM EMPLOYEE JOIN ASSIGNMENT ON EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber ORDER BY EMPLOYEE.EmployeeNumber, ProjectID;
What are the SQL statements that we use to manipulate data?
Insert, update, delete
Single quote is not used in one of the following data types: 1- Char 2- VarChar 3-Date 4-Int
Int is the correct answer
To create an N:M relationship, a new table is created. What is this table called?
Intersection Table
What is the name of the table that is created to replace a N:M relationship in database design?
Intersection Table
What type of table is needed to when an N:M relationship exists between 2 entities?
Intersection Table
What is the difference between Intersection Table and Association Table?
Intersection Table holds the primary keys of the parent tables as foreign keys, and this combination of keys serves as the composite primary key of the intersection table itself whereas in an Association Table along with the key data; it also contains other user data. That is it also has one or more attributes that pertain to the relationship.
To create an N:M relationship, a new table is created. What is that table called?
Intersection table
What is the meaning of a reserved word in SQL
It is a special word that is part of a SQL command.
What does the SQL TRUNCATE TABLE statement do?
It is used to remove all data from the table while leaving the table structure itself in the database while also resetting any surrogate primary key values back to the starting point.
What does the Parse button do in SQL management studio?
It validates the syntax prior to execution.
What is an SQL view ?
It's a virtual table that is constructed from other tables or views.
What are the 4 types of join?
JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
What are the three types of outer join?
LEFT OUTER JOIN RIGHT OUTER JOIN FULL JOIN
_____ can be paired with wildcards to find rows matching a string value
LIKE
What's a data constraint
Limitations on Data values
For the IDENITY (M,N) property, what do you replace M & N with?
M is the surrogate key that will be the value for the first row created. N is the increment each additional row is increased by.
In IDENTITY(M,N), what does the M and N used for?
M is where the value will start when the first row is created and N is how much each row will increase.
•How are 1:1/1:N/M:N relationships represented with foreign keys? o
M:N: The intersection table will receive the primary keys of the two parent table and make these key (foreign keys) as it primary keys. o 1:N: By Placing the primary key of the parent in the child as a foreign key. o For the representation with foreign keys,in 1:1 relationship, if either of the entities has an optional side, then the entity having optional side contains the foreign key.
A SQL built-in function that finds the maximum value for a specific column for those rows matching the criteria.
MAX
Which statement combines INSERT and UPDATE into one statement?
MERGE statement
Which statement combines INSERT and UPDATE into one statement?
MERGE.
Which of the following is a SQL Aggregate function? MIN GROUP BY ORDER BY SELECT
MIN
What is the difference between maximum and minimum cardinalities?
Maximum cardinalities are reflected by foreign keys Minimum cardinalities reflected by having foreign keys listed as Null or NOT NULL
The ________________ determines how a relationship is represented.
Minimum and maximum cardinality
What is included in a final database design?
Modified E-R diagram, now showing primary keys, foreign keys, and all necessary intersection tables Separate table listing data types, NULL/NOT NULL, default values, and constraints List of tables in parenthetical format
What are the two wildcard's that can be paired with SQL LIKE keyword?
Multiple character wildcard character is a percent sign (%). Single character wildcard character is an underscore(_).
True or False - A 1:1 relationship is when you should create an intersection table?
N:M relationship
Intersections tables support what kind of relationships?
N:M; the intersection table contains the keys of the relationship in the relationship.
What can a criteria statement may be preceded by to say that all information will be shown except that information matching the specified criteria?
NOT
When creating a table in SQL, what is needed in the create statement to ensure that the value is required?
NOT NULL
When altering a table to add a column you cannot add?
NOT NULL. because there is no data at the time you create the column so it would violate the constraint. you need to add the data them change it to NULL.
What happens if you don't specify null status?
NULL is assumed
What attributes are needed to create a table?
Name, Data Type, Constraint (Optional)
What is netbeans IDE?
NetBeans is an integrated development environment for Java. NetBeans allows applications to be developed from a set of modular software components called modules.
Does the SQL view table have its own data?
No, it obtains data from tables or other views
When creating a new table, can we define a "ON UPDATE NO ACTION" as a constraint in Oracle?
No. It is by default NO ACTION. Oracle will throw an error.
Subqueries are divided into what two categories?
Non-Correlated and Correlated
Actions needed to be taken for optional (O-O)?
None
Name the components of the ODBC standard
ODBC Application Program: It issues requests to create and terminate a connection with a data source. It calls the Application Programming Interface (API) function which issue SQL statements. It receives the results and also processes the errors for the statements issues to the data source. ODBC driver manager: A driver manager is the intermediary between the application program and the ODBC DBMS drivers. ODBC DBMS driver: The ODBC DBMS driver plays the role of intermediary between the ODBC driver manager and various data sources. Each data source type requires different DBMS driver. Data source: A data source is the database. It also includes operating system, network protocols and DBMS that is associated with the given database.
In a 1:N relationship, the term "parent" refers to the table on which side of the relationship.
ONE
To sort the date from new to old. What query framework should be used?
ORDER BY ...DESC
What is the statement to order the by highest value to lowest?
ORDER BY FIELDNAME DESC;
The _________ syntax can be used to obtain data that exists in one table without matching data in the other table.
OUTER JOIN
In a 1:1 binary relationship where the min cardinality on one side is zero, and the min cardinality on the other side is 1, where should the foreign key go?
On the optional side. An example would be a person and a house. Its not required that person needs a house, therefore the foreign key will be places on the house, going to the person.
How do you represent Primary and foreign keys in a subtype-supertype relationship in a database design?
PK and FK are indicated with a key symbol right next to the attribute.
What are the possible "column constraints" for SQL CREATE TABLE statement?
PRIMARY KEY, NOT NULL, NULL, UNIQUE, IDENTITY, DEFAULT
What must you define before child tables?
Parent tables
Denormalization is done to improve _________ or ___________.
Performance; Security
A Constraint Key can identify a:
Primary Key or Foreign Key
What does the "Execute" button in SQL Server Management Studio do?
Produces the query results. In other words, SQL Server Management Studio will run the query against the selected database, and once processed, it will confirm whether or not the query was run successfully. (in chapter 1)
What are the pros and cons of denormalization?
Pros: • Simplicity of design • Allows to extract data without performing a join operation with another table. • Increases query performance. • SQL commands are easier to write. Cons: • Introduces modification anomalies. • Redundant data is possible. • More storage space is required.
_____ pulls information from one or more relations and create a new relation(temporarily)
Query
How do you sort rows in SQL Select Statement?
Query results may be sorted using the ORDER BY clause. Default is ascending (ASC) but can also specify explicitly or specify DESC, by including ASC or DESC keyword after the column name. SELECT * FROM EMPLOYEE ORDER BY EmpName; SELECT * FROM EMPLOYEE ORDER BY EmpName ASC;
A relationship between two attributes within the same entity is called a _________ relationship
Recursive
How are recursive relationships represented with foreign keys and intersection tables?
Recursive relationships adhere to the same rules as binary relationships. -1:1 and 1:M relationships are saved using foreign keys. -M:N relationships are saved by creating an intersecting relation.
Entities can be associated with one another in __________.
Relationships
What does SQL TRUNCATE DO?
Removes all the data from the table while leaving the table structure itself in the database.
What are the four uses for ID-Dependent entities?
Representing N:M Relationships Association Relationships Multi-valued Attributes Archetype/Instance Relationships
What does the "Execute" button in SQL Server Management Studio do?
Run the query on actual database.
Which SQL statement is used to extract data from a database?
SELECT
SQL statement is best known for retrieving information from the database that matches specified criteria.
SELECT Example: SELECT empName FROM Employee WHERE empID = 33;
What is the SQL format for selecting data?
SELECT FROM (Optional)WHERE (Optional)Order By
What SELECT statement shows all the column values for the rows?
SELECT * FROM TABLE_NAME;
What is the syntax for counting the number of rows?
SELECT COUNT(ColumnName) FROM TABLE
How do you sort columns in SQL?
SELECT ColumnName1, ColumnName2--> This sorts Column.
How do you sort rows?
SELECT CustomerID, CustomerName--> This sorts Column FROM CUSTOMER ORDER BY CustomerID --> This would sort column from ascending order if you want reverse use desc
How would you write SQL code for finding an employee who makes between 75000 and 100000
SELECT EmpName FROM EMPLOYEE WHERE Salary BETWEEN 75000 AND 100000;
Give an example of sub-query.
SELECT LastName, FirstName, Phone FROM CUSTOMER WHERE CustomerID IN (SELECT SaleID FROM SALE WHERE SaleID = 1)
How do you select a column named "Total" from a table named "INVOICE"?
SELECT Total FROM INVOICE;
Which statement will retrieve information from the database that matches the specified criteria?
SELECT statement
Which statement is an SQL DDL statement that is used to change the structure of an existing table. a. SQL CREATE b. SQL ALTER c. SQL MODIFY
SQL ALTER
What do you use to add, modify, and drop columns and constraints?
SQL ALTER TABLE statement
The _________ statements used to construct tables, define columns and column constraints, and create relationships.
SQL CREATE TABLE
What is the difference between SQL LEFT JOIN and SQL RIGHT JOIN?
SQL LEFT JOIN: read data from the first table in the query SQL RIGHT JOIN: read data from the second table in the query
SQL inner join can also be referred as?
SQL equijoin
What kind of language is SQL considered to be?
SQL is a domain-specific sub language used for managing data within a relational database. It differs from a general-purpose languages like Python or Java.
A plain text file containing SQL commands is called a ___ and usually uses a file name extension of ___.
SQL script, .sql
A ____ is a virtual table that is constructed from other tables or views.
SQL view
ANSI adopted the _____ as the national standard for Structured Query Language. 1. SQL-92 2. SQL-89 3. SQL-Server 4. SQL-Standard89
SQL-92
In the ANSI/ISO SQL standard, these procedural programming language extensions are known as __________________________________________________________
SQL/Persistent Stored Modules (SQL/PSM).
When added to an SQL SELECT statement, WHERE allows us to ___________
Select rows in a table by specifying rules. The WHERE keyword can also be used to insert, update and delete data from tables.
SELECT will retrieve information from the database that matches the specified criteria using the _________ framework.
Select/From/Where
When creating a statement, what it needed at the end of the statement?
Semi colon (;)
1:N relationship, parent required
Specify FOREIGN KEY Set foreign key NOT NULL
1:N relationship, parent optional
Specify FOREIGN KEY Set foreign key NULL
1:1 relationship, parent required
Specify FOREIGN KEY Specify foreign key UNIQUE Set foreign key NOT NULL
1:1 relationship, parent optional
Specify FOREIGN KEY Specify foreign key UNIQUE Set foreign key NULL
In a 1:N relationship where the parent is optional, what are the CREATE TABLE constraints?
Specify FOREIGN KEY constraint . . . Set foreign key NULL.
What CONSTRAINT should be enforced while creating table that 1:N relationship with parent optional
Specify FOREIGN KEY constraint. Set Foreign Key Column as NULL
In 1:1 relationship with parent table required, what are the constraints to be specified on the create table?
Specify FOREIGN KEY constraint. Specify foreign key UNIQUE constraint. Set foreign key NOT NULL.
What does SQL stand for?
Structured Query Language
What are the two ways to retrieve information from multiple tables?
Subqueries and Joins
A result of a query fed to another query is also referred as?
Subquery
It is not recommended to put CASCADE phrases when an attribute is a: Surrogate key Foreign key Gold key Last key
Surrogate key
What does SDLC represent?
Systems development life cycle
(T/F) For the representation with foreign keys,in 1:1 relationship, if either of the entities has an optional side, then the entity having optional side contains the foreign key.
TRUE
(T/F) Four properties need to be specified for each table column: null status, data type, default value, and data constraints.
TRUE
(T/F) SQL includes five built-in locations: COUNT, SUM, AVG, MAX, MIN.
TRUE
(TRUE/FALSE) The ORDER BY Clause will default as ascending?
TRUE
A constraint defining the primary key must be included when creating a table (TRUE or FALSE)
TRUE
An associative entity is an intersection table containing additional attributes. TRUE or FALSE
TRUE
Is this the correct use of a right join connecting multiple tables? T/F SELECT Attribute FROM TABLE RIGHT JOIN SALE ON CUSTOMER.CustomerID = SALE.Customer ID RIGHT JOIN SALE_ITEM ON SALE.SaleID = SALE_ITEM.SaleID RIGHT JOIN ITEM ON SALE_ITEM.ItemID = ITEM.ItemID ;
TRUE
Maximum Cardinalities are reflected by foreign keys and sometimes intersection tables. T/F
TRUE
SQL is not a programming language.
TRUE
T/F SQL is the programming language used to manipulate data and data objects in a relational database management system.
TRUE
T/F The DROP statement will completely remove the object and all data
TRUE
T/F The SQL function GROUP BY is really a DISTINCT ASC sort function
TRUE
T/F The result of a SQL statement is always a table
TRUE
TRUE or FALSE The correct identifier of a subtype will always act as its primary key AND foreign key.
TRUE
When two conditions must both be true for the rows to be selected, the conditions are separated by the SQL AND keyword.
TRUE
T/F Can you add a new column to a certain table with this ALTER TABLE statement? ALTER TABLE table_name ADD column_name datatype;
TRUE This syntax is correct ALTER TABLE table_name ADD column_name datatype;
What SQL Keyword is used to delete data from a table but leave the structure intact?
TRUNCATE
What is the difference between DROP and TRUNCATE?
TRUNCATE removes all rows from the table which cannot be retrieved back, DROP removes the entire table from the database and it cannot be retrieved back.
Is it better to define PRIMARY KEY as a table constraint or a column constraint?
Table constraint
What does NOT NULL constraint indicate?
That values are required in that column
What is the CHECK constraint and what keywords can be used?
The CHECK constraint can be used to create restrictions on the values that are allowed to appear in a column. If a value satisfies the CHECK constraint, it will be allowed to enter into the database, otherwise it will be restricted -contains keywords IN, NOT IN, LIKE, <,>
What is the Check Constraint used for?
The CHECK constraint can be used to create sets of values to restrict the values that can be used in a column.
When do we used CHECK Constraint?
The CHECK constraint is used to limit the value range that can be placed in a column.
If a column is not specified with Null status when creating the table, what does the DBMS interpret as?
The DBMS will interpret as the column value as Null value
When do you use a GROUP BY clause?
The GROUP BY clause use when there is a mix of built-in function and column named.
What is the example of the JOIN ... ON approach to inner join in comparison to the WHERE ___=____ approach?
The JOIN...ON approach moves the joining to the FROM part of the statement
Which keywords are used to place a primary key constraint when creating a table?
The Primary Key keywords
When do you use the HAVING clause?
The SQL HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE. -Essentially like the WHERE keyword but for build-in functions
What is the SQL WHERE clause is used for? And what can it prevent?
The SQL WHERE clause is used to filter the results and apply conditions in a SELECT, INSERT, UPDATE, or DELETE statement. -It specifies which rows are to be listed. It prevents the command from filtering out more/all data where there is only the specific data targeted for filtering.
How do the TRUNCATE and DELETE statements treat surrogate keys differently?
The TRUNCATE statement will reset surrogate keys back to the initial value, while the DELETE statement does not.
How can you add a constraint to a column that will not let any two entries be the same?
The UNIQUE constraint enforces uniqueness on either a single column or a combination of different columns.
Find what is wrong with the SQL Query SELECT subject_code, AVG (marks) FROM students WHERE AVG(marks) > 75 GROUP BY subject_code;
The WHERE clause cannot be used to restrict groups. Instead, the HAVING clause should be used. SELECT subject_code, AVG (marks) FROM students HAVING AVG(marks) > 75 GROUP BY subject_code;
Name the components of the ODBC standard.
The components of the ODBS standard are the application program, the ODBC driver manager, the ODBC DBMS driver, and a data source.
When an SQL command of deleting a table is used what happens to the data in that table?
The data in that table is also deleted.
What is E-R model?
The data model which is based on the real time entities and the information and also the relationship among those entities.
What are the rules concerning primary keys for supertypes/subtypes?
The identifier of the supertype becomes the primary key and the foreign key of each subtype.
How are M:N relationships represented with foreign keys and intersection tables?
The intersection table will receive the primary keys of the two parent table and make these key (foreign keys) as it primary keys.
How is 1:1 relationship represented with foreign key?
The key from one relation is placed in the other as a foreign key. It does not matter which table receives the foreign key. . the specified foreign key must be constrained as UNIQUE.
What is the difference between left join and right join.
The left join returns all rows from the left table with matching rows from the right table. While the right join returns all rows from the right table with matching rows from the left table.
What is the difference between the HAVING clause and WHERE clause?
The main difference between the WHERE clause, and Having clause is that conditions specified in WHERE clause is used while fetching data (rows) from the table. The rows that do not meet the criteria in the WHERE clause will not be fetched. The HAVING clause is later used to filter summarized or grouped data.
What is the meaning of Unique constraint in SQL?
The meaning of UNIQUE Constraint in SQL is to ensure that all values in a column are different.
what is the difference between the parse button and the execute button in sql management studio?
The parse button checks if the sql statement is syntactically correct without executing it while the execute button runs the sql statement.
What was the first attempt at a relational database language called?
The precursor to SEQUEL/SQL was called Square, but it was insufficient, so Donald Chamberlin and Raymond Boyce (of Boyce-Codd normal form) invented SQL in 1973.
What are the rules concerning primary keys for ID-dependent entities?
The primary key for ID-dependent weak entities is always a composite primary key that includes the primary key of the entity that the ID-dependent entity depends on for its existence.
What are the rules concerning primary keys and foreign keys for supertypes/subtypes?
The primary key of the supertype as the primary key and foreign key of the subtype.
•What are the rules concerning primary keys for supertypes/subtypes? o
The primary key of the supertype as the primary key and foreign key of the subtype.
What are the rules concerning primary keys for supertypes/subtypes?
The primary key of the supertype will be the primary key (and a foreign key) for the subtypes
What SQL term/condition refers to the business rules (If an EMPLOYEE row is connected to any ASSIGNMENT, the EMPLOYEE row cannot be deleted. and If a PROJECT row is deleted, all ASSIGNMENT rows connected to the deleted PROJECT are also deleted)
The referential integrity constraint
When using RIGHT JOIN clause to return all records from two tables, what is the result from the left side if there is no record?
The result is NULL.
7.41, Under what circumstances does stateless pose a problem for a database processing?
The statelessness of HTTP does not create any problem for serving contents, like static Web pages, or processing the queries of a database, but while working with applications, online services, etc, where there are multiple interactions with the same client and the multiple database actions are grouped into a single transaction, this statelessness of HTTP pose a problem for database processing.
How are minimum cardinalities reflected in a database design ?
They are reflected by having the foreign keys listed as "NULL" or "NOT NULL".
What does the % and _ do?
They are wildcard characters. The underscore symbol (_) represents a single, unspecified character. The percent sign (%) is used to represent a series of one or more unspecified characters. These characters are used with the LIKE keyword.
What is a SQL reserved word?
This is a word that has a special meaning as part of a SQL command. Generally reserved words cannot be used as table or column names.
What is the purpose of keyword DISTINCT?
This keyword may be added to the SELECT statement to suppress the display of duplicate rows EX: SELECT DISTINCT DeptID FROM Employee;
What does the "Parse" button (checkmark button) in SQL Server Management Studio do?
This only validates syntax, and doesn't check that the objects you've referenced exist, that joins are valid, etc. It tests the syntax of what you write before actually run it on the real database.
What does /* (before text) and */ (after text) do?
This turns the text between /* and */ into an SQL comment.
•What is an intersection table and when is it used? Does an intersection table require attributes besides primary keys? What is an intersection table with additional attributes called?
To create an N:M relationship, a new table is created. This table is called an intersection table. o An intersection table has a composite key consisting of the keys from each of the tables that it connects.
What is the CHECK constraint used for?
To define data constraints
When do you use a GROUP BY clause?
To groups rows by common values. You can apply functions the grouped rows. Example: SELECT Apple, Cereal, Count(*) AS ShoppingGoods, TABLE STORE GROUP BY Apple, Cereal
What is the benefit of the delete/update cascade in the database?
To maintain equity and quality of the data in the database
How can you select all the even number records from a table? All the odd number records?
To select all the even number records from a table: Select * from table where id % 2 = 0 To select all the odd number records from a table: Select * from table where id % 2 != 0
BEGIN, ROLLBACK, COMMIT are what type of SQL commands?
Transaction Control Language (TCL)
'Drop' syntax is DDL language used to modify data structure, 'Delete' is DML language to delete data but not structure of a table.
True
(T/F) A CHECK constraint can be used to create sets of values to restrict the values that can be used in a column
True
(T/F) SELECT is the best known SQL statement.
True
(T/F) Supertypes share primary keys with their subtypes
True
(T/F) The primary key and foreign key of a subtype is the primary key of the supertype.
True
(T/F) Varchar should be used when the sizes of the data entries vary considerably.
True
(True or False) Like a 1:1 relationship a 1:N relationship is saved by placing the key from one table into another as a foreign key
True
(True/False) An intersection table is a child table that has two 1:N relationships that have been manufactured to replace a single N:M relationship in the data model.
True
(True/False) The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed.
True
(True/False) When using a SUM() function, you need to use GROUP BY instead of ORDER BY.
True
A cascading update occurs when a change to the parent's primary key is applied to the child's foreign key.
True
A query is a relation
True
A surrogate is auto-generated.
True
An intersection table is always ID-dependent on both of its parent tables. T/F
True
An intersection table typically has a composite key comprised of the keys from each of the table to which it is connected. (True or False)
True
Entities that are used in extended E-R model are shown as a table in the relational database design. True/False
True
If we use SQL language, we would be able to work with any database product or vendor? T or F
True
In a Venn diagram, a subset of a set is found completely within that set.
True
In every database system, every constraint will have a unique name. T or F
True
It's entirely reasonable for an intersection table to have additional attributes except composite primary keys. (T/F)
True
Null status refers to whether the column can have a null value. True OR False
True
Recursive relationships in a many to many relationships require an intersection table.
True
SQL is not a programming Language but rather than a data sublanguage
True
T/F An intersection table is used when there is a N:M relationship
True
T/F Database design occurs in the component design step of the systems development life cycle.
True
T/F In LEFT and RIGHT JOIN operations, all the rows from at least one table are always included in the result set.
True
T/F SQL is not a complete programming language
True
T/F An '*' is used in the SELECT clause when the user wants all of the columns from the table in the output.
True
T/F If you do not specify the type of JOIN in your SQL command (i.e. INNER, LEFT, RIGHT, FULL OUTER), you will perform an INNER JOIN by default.
True
T/F More than two tables can be queried by using a subquery.
True
T/F The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed.
True
T/F The primary key of the super type is used as the key of a sub type?
True
