SQL
The modulus or remainder of dividing two values can be found using which operator?
%
Which of the following statements about Primary Keys is not accurate?
-A table may have more than one Primary Key Is not required for a table to have a Primary Key A Primary Key must be unique A Primary Key cannot be NULL
All of the following are true statements regarding the "AS" keyword, except?
-Applies a permanent alias Applies a temporary alias Allows renaming of database columns Allows renaming of database tables
All of the following are aggregate functions, except
-RAND COUNT MAX AVG
If the table PRODUCT has a column PRICE that has the data type Numeric (8,2), the value 12345 will be displayed by the DBMS as
123.45
Recursive relationships can have which of the following maximum cardinalities?
1:N
The clause WHERE ACCOUNTNUM LIKE '_5%3' will match all of the following, except
2533483 -45434 85103 953
Which of the following represents the set that would be returned by the SQL clause? WHERE Quantity BETWEEN 30 AND 35
30,31,32,33,34,35
The maximum number of characters for a SQL Server VARCHAR() column is
8000
SQL Server user variables and parameters start with the symbol
@
Which of the following is a SQL trigger Microsoft SQL Server supports?
AFTER & INSTEAD OF
Which SQL keyword is used to change the structure, properties or constraints of a table?
ALTER
The SQL statement ALTER TABLE CUSTOMERS ADD DATEOFBIRTH VCHAR(20) results in which of the following?
Adds a new column named DATEOFBIRTH to the CUSTOMERS table
What would be the result of executing the following SQL statement? UPDATE Members SET Email='[email protected]', State='Michigan'
All records in the Members table would be updated & The values in the Email and State columns would be the same for all records in the Members table.
Which statement is true about the AUTO_INCREMENT keyword?
Allows a unique number to be generated when a new record is inserted into a table
Anomalies caused by functional dependencies can be eliminated by putting tables into
BCNF
Which SQL keyword is used to impose restrictions on a table, data or relationship?
CONSTRAINT
Which SQL function would you use to obtain the number of rows that match specified criteria?
COUNT
Which of the following are valid Data Definition Language (DDL) commands?
CREATE DROP ALTER
Which of the following is not a way of defining a primary key?
CREATE TABLE CUSTOMER ( CustomerID Integer Primary Key LastName Char(35) Not Null First Name Char(25) Null ); -------------------------------------- CREATE TABLE CUSTOMER ( CustomerID Integer Not Null LastName Char(35) Not Null First Name Char(25) Null ); ALTER TABLE CUSTOMER ADD CONSTRAINT CustomerPK PRIMARY KEY (CustomerID); ------------------------------------------ CREATE TABLE CUSTOMER ( CustomerID Integer Not Null LastName Char(35) Not Null First Name Char(25) Null CONSTRAINT CustomerPK PRIMARY KEY (CustomerID) ); ------------------------------------------------ * CREATE TABLE CUSTOMER ( CustomerID Integer Not Null LastName Char(35) Not Null First Name Char(25) Null DEFINE CustomerPK PRIMARY KEY (CustomerID)
What does the following SQL statement do? UPDATE Product_T SET Unit_Price = 775 WHERE Product_ID = 7
Changes the unit price of Product 7 to 775
There are three parameters for the SQL MID() function.,. Which of the following is not required?
Column name Start -Length All parameters are required
SQL queries that use EXISTS and NOT EXISTS are
Correlated subqueries
CRUD =
Create, Read, Update, Delete
Which type of constraint is used to insert a default value into a column?
DEFAULT
Which of the following SQL statements deletes all rows in table called CustomerOrders?
DELETE FROM CustomerOrders
Which of the following SQL commands would you use to identify all of the non-duplicate values for a table column?
DISTINCT
Deleting a table definition including all data, indexes, triggers, constraints, and permission for that table is accomplished using which command?
DROP
Which SQL keyword is used to delete a table's structure?
DROP
Which of the following is true about making changes to the database structure?
Documentation of when the change was made, how it was made, and why it was made must be created.
Which of the following accurately depicts that a value must be present in the EMAIL column for a record (assume this is part of a CREATE statement)?
EMAIL VARCHAR (255) NOT NULL
What will be the result of the following SQL statement? SELECT LEFT('Enjoy the little things', 5)
Enjoy
What category of data integrity ensures there are no duplicate rows in a table?
Entity Integrity
A Foreign Key in one table points to a Domestic Key in another table.
False
Constraints can only be applied at the time a table is created.
False
Creating database queries is part of the Data Definition Language.
False
In SQL syntax AVG and avg have different meanings.
False
SQL contains an SQL command RENAME TABLENAME that can be used to change table names.
False
The WHERE clause is required for a SQL UPDATE statement.
False
The acronym DBMS stands for Database Management Service.
False (System)
Which keyword selects all rows from both tables as long as there is a match between the columns in both tables?
INNER JOIN
Which type of JOIN results in the selection of all rows from two tables as long as there is a match between the columns in each tables?
INNER JOIN
Which SQL keyword is used to add one or more rows of data to a table?
INSERT
Which of the following would you use to add a new record to a database table?
INSERT INTO
What statement would you use to copy data from one table and insert it into an existing table?
INSERT INTO SELECT
Relational databases offer what advantages in comparison to a flat file?
Increased integrity and Data redundancy
Which logical operator compares a value to similar values using wildcard operators?
LIKE
Which operator is true if the operand matches a pattern?
LIKE
Which of the following SQL clauses is used to sort a result set?
ORDER BY
A secured database object has a fully qualified name. Which of the following is not part of the fully qualified name?
Principal
Examples of SQL Scalar functions include all of the following, except
ROUND() LEN() FORMAT() -SUM()
In which of the following would the results include displaying the Last Name from A to Z?
SELECT * FROM Customers ORDER BY OrderID ASC, Last Name ASC
Which of the following is true about indexes in SQL Server?
SQL Server does not automatically create indexes for foreign keys.
Stored procedures have the advantage of
SQL optimized by the DBMS compiler, greater security, and decreased network traffic
The industry standard supported by all major DBMSs that allows tables to be joined together is called
Structured Query Language (SQL)
The following SQL statement demonstrates an example of _____. SELECT a.studentid, a.name, b.gpa FROM student a, grades b WHERE a.studentid = b.studentid AND b.gpa> (SELECT gpa FROM grades WHERE studentid = 'G1023');
Sub query
Which of the following is not a valid SQL clause?
TOP ORDER BY WHERE -BOTTOM
The language available in SQL Server that adds programming constructs to the SQL language is known as
TRANSACT-SQL
In a correlated subquery of a database that has tables TableOne and TableTwo, if table TableOne is used in the upper SELECT statements, then which table is used in the lower SELECT statement?
TableOne
All of the following are situations you would avoid using a database index, except?
Tables that frequently have large batch updates. Columns that are frequently manipulated. Columns that contain a high number of NULL values. -Tables that have large data sets.
A SQL JOIN clause is used to combine data from two or more tables using on a common field between them.
True
In the example below, does the SELECT clause has a computed value? SELECT CustomerName, UnitPrice * NumberofUnits FROM Orders
True
RDBMS is the basis for SQL.
True
SQL contains an SQL command TRUNCATE TABLENAME that can be used to remove all rows from the table.
True
The Boolean data type stores TRUE or FALSE values.
True
The DEFAULT constraint is used to insert a default value into a column.
True
The SQL CREATE TABLE statement is used to name a new table and describe the table's columns.
True
Which of the following is a valid SQL constraint?
UNIQUE
Which SQL keyword is used to change one or more rows in a table?
UPDATE
Based on the tables below, which of the following SQL statements would increase the balance of the Gonzales account by $100 to a total of $450?
UPDATE CUSTOMER SET Balance = 450 WHERE CustName = 'Gonzales';
Of the following data types, in which one could you not store the value 123456789?
VARCHAR(20) INT NCHAR -The value can be stored in all of the above datatypes
In which SQL clause will the results include any rows where an employee is over age 25 that have a salary more than $40,000 or employees that have a salary of at least $35,000?
WHERE (AGE > 25 AND SALARY > 40000) OR SALARY > = 35000
Anytime you want to use an SQL Server reserved word as a user identifier, enclose it in
[ ]
If a DEFAULT constraint is included when a new column is added to a table, the default value is applied to
all new rows
When running an SQL query that uses NOT EXISTS, the NOT EXISTS keyword will be true if
all rows in the subquery fail to meet the condition
Each attribute of an entity becomes a(n) ________ of a table.
column
Measures that are taken to prevent one user's work from inappropriately influencing another user's work are called:
concurrency control
To change a table name,
create a new table, move the data, and drop the old table
Which of the following is not a step in the database design process?
create constraints and triggers
Using only SQL Server tools, you can enter data into a table by
entering it into a table grid in the Microsoft SQL Server Management Studio or using INSERT statements through the Microsoft SQL Server Management Studio
Which of the following is a component of a computer-based information system?
hardware, data, and software
What would be the effect of this UPDATE statement? UPDATE Customer SET CustomerLastName ='Smith'
it would change every customer's last name to Smith
A SQL CHECK constraint
limits the values that can be entered in a column
The term set refers to all records that have the same:
nonunique secondary key
Which of the following is not a valid format for the DATEPART function?
ns qq dy -mh
What does the SQL clause ORDER BY CustomerLastName DESC do?
orders the customers by last name Z-A
Which of the following are difficulties when changing the maximum cardinality from 1:1 to 1:N?
preserving the existing relationships
The identifier of the entity becomes the ________ of the corresponding table.
primary key
Recovering a database via reprocessing involves:
restoring the database from the save and reprocessing all the transactions since the save
The ideal primary key is
short, numeric, and fixed
To access a database View, a user must have permission to
the View
When representing a 1:1 relationship in a relational database design,
the key of either table may be placed as a foreign key into the other
In many-to-many relationships in a relational database design,
the key of the child is placed as a foreign key into the parent
To represent a one-to-many relationship in a relational database design,
the key of the parent is placed as a foreign key into the child
Database redesign is fairly easy when
there is no data in the database
SQL views are used
to show results of computed columns, to hide columns, and to hide complicated SQL statements
A series of actions to be taken on the database such that either all actions are completed successfully, or none of them can be completed, is known as a:
transaction
A stored program that is attached to a table or view is called a
trigger
Keys may include:
unique secondary keys, primary keys, and nonunique secondary keys
In UML, an attribute preceded by a(n) ________ is "protected."
{ }
