SQL

Ace your homework & exams now with Quizwiz!

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."

{ }


Related study sets

Psychology of Drugs and Behaviour Final Content

View Set

Physiology Exam 3 Week 7 (Questions)

View Set

CHAPTER 3: INTERESTS AND ESTATES

View Set

VIVA 1 MODULE 5 5.En la cafetería

View Set