CS 292 Chapter 3

SQL is the most commercially marketed relational query language and it only has one specific part in the SQL language.

False

SQL offers three standard built-in aggregate functions.

False

The "when" clause allows us to select only those rows in the result relation of the from clause that satisfy a specified predicate.

False

The basic structure of an SQL query consists of two clauses: select and form.

False

The basic structure of an SQL query consists of two clauses: select and from.

False

The form clause is a list of the relations to be accessed in the evaluation of the query .

False

The where clause is used to list the attributes desired in the result of query

False

The where clause is used to select the table from which data will be gathered

False

You cannot change the order of the columns to be displayed in query results.

False

Data Manipulation Language (DML) provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tupils in the database.

True

If the group by clause is absent, the entire set of tuples satisfying the where predicate is treated as being in one group.

True

It is possible to place one query inside another.

True

Its common to use data flow diagrams, sequence diagram, and etc. in software designs.

True

Null values present special problems in relational operations, including arithmetic operations, comparison operations, and set operations.

True

SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schemas.

True

SQL allows the use of the logical connectives "and", "or", and "not" in the where clause.

True

SQL applies predicates in the having clause after groups have been formed.

True

SQL does not allow the use of distinct with count (*).

True

SQL supports aggregation

True

Smallint is a built in type that supports SQL standard.

True

The Data Definition Language (DDL) provides commands for defining relation schemas, deleting relations, and modifying relation schemas.

True

The ER model describes data as entities, relationship, and their attributes.

True

The SQL DML provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database.

True

The as clause is particularly useful in renaming relations.

True

The char data type stores fixed-length strings.

True

The select from where statement is evaluated fully before any of its results are inserted into the relation.

True

The semicolon shown at the end of the create table statements, as well as at the end of other SQL statements is optional in many SQL implementations.

True

The symbol "*", or asterisk, can be used to denote "all attributes" when used in the select clause of a query.

True

There are 3 clauses in the basic structure of SQL.

True

To insert data into a relation, we either specify a tuple to be inserted or write a query

True

To insert data into a relation, we either specify a tuple to be inserted or write a query whose result is a set of tuples to be inserted.

True

the basic structure of an SQL query consist of three clause: select, from and where.

True

SQL provides constructs for updating, inserting and which other information it does?

it also provides deleting information.

Which clause causes the tuples in the result of a query to appear in stored order ?

order by

Aggregate functions are functions that

take a group of values as input and uses that to produce a single value

Name at least four variety of built-in types that SQL supports

: char(n):Aï¬xed-length character string with user-speciï¬ed length(n).The full form, character, can be used instead. 2. varchar (n):A variable-length character string with user-speciï¬ed maximum length (n). The full form, character varying, is equivalent. 3. int: An integer (a ï¬nite subset of the integers that is machine dependent). The full form, integer, is equivalent. •4. Smallint: A small integer (a machine-dependent subset of the integer type). • numeric(p,d): A ï¬xed-point number with user-speciï¬ed precision. The number consists of p digits(plus a sign),and d of the p digits are to the right of the decimal point. Thus, numeric(3,1) allows 44.5 to be stored exactly, but neither 444.5 nor 0.32 can be stored exactly in a ï¬eld of this type.

Is a list of the relations to be accessed in the evaluation of the query. A. From clause B. Select clause C. Where clause D. Insert clause

A

By default, the order by clause lists items not in descending order.

True

What are aggregate functions? A. are functions that take a collection of a set or multiset of values as input and return a single value. B. are functions that help the user get a result quicker when coding. C. are functions that can be used to obtain the same result as the string operation. D. none of the above.

A

What are tables? A. They have ROWS and COLUMNS -Rows: like records. -Columns: like fields B. Projection: The query criteria. C. Single Occurrence of an entity D. COMMIT saves all changes

A

What are the three basic structure clause of SQL A. Select, From,Where B. Select, To, How C. Select, Who, When D. Select, Where, How

A

What does SQL stand for? A. Structured Query Language B. Sequel Query Language C. Standard Query Language D. Several Query Language

A

What does SQL stand for? A. Structured Query Language B. Strong Question Language C. Structured Question Language D. None of the above

A

What does a multi-valued sub query return? A. Multiple values as a single column set to the outer query B. A single value to the outer query. C. Nested queries

A

What is DDL in sql? A. It provides command for relation schema deleting relation and modifying relation schema. B. It truncate. C. It drop D. It make data

A

What is SQL query language menas? A. A. The standard relational database language. B. B. Data definition language. C. C. Data manipulation language D. D. Create query language

A

Which SQL DDL includes commands for specifying access rights to relations and views? A. Authorization B. View definition C. Integrity D. Transaction control

A

What is a primary key?

A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields.

What is a sub-query?

A subquery is a select- from- where expression that is nested within another query.

What are the Aggregate Functions?

Aggregate functions are functions that take a collection ( a set or multiset) of values as input and return a single value.

What are the five aggregate functions built into SQL?

Avg, min, max, sum, and count

............Provides command for defining relation schema, deleting relation and modifying relation schema A. Data-Manipulation Language (DML) B. Data-Definition Language (DDL) C. Basic Structure Queries D. Single Relation Queries

B

: IBM developed the original version of SQL, originally called Sequel, as part of the System R project in the early --------- A. 1960s B. 1970s C. 1980s D. 1990s

B

Char is A. a variable-length character string with user-specified maximum length B. a fixed-length character string with user specified length C. a floating point number D. smallint

B

Fill in the blank: The asterisk symbol “ * â€can be used in the ________ clause to denote “all attributes. A. from B. select C. what D. where

B

SQL handles queries on relations containing null values by adding the value A. "union" B. "unknown" C. "blank" D. "where"

B

SQL supports basic set operations on relations including union, intersect, and A. select B. except C. from D. where

B

To define a SQL relation, use the command "______" A. new table B. create table C. add table D. none of the above

B

To remove a relation from a SQL database we use A. remove table B. drop table C. update table D. B & C

B

What are some SQL set operations that can be utilized in mathematical set operations? A. exponential, trigonometric, minus B. union, intersect, except C. linear, non-linear, inverse D. none of the above

B

What does DML stand for? A. Data Definition Language B. Data Manipulation Language C. Data Multiple Language D. Data Margin Langauge

B

Which construct returns the value true if the argument subquery contains no duplicate tuples? A. Correlation name B. Unique C. Correlated subquery D. Null values

B

Which is not count as set operation on the SQL operation A. union B. cross C. except D. intersect

B

Which one of the SQL language parts is incorrect? A. Data-manipulation language (DML) B. Legalization C. View definition D. Data-definition language (DDL)

B

With a single _____ statement you can instruct SQL to return any set of data from one or more tables. A. FROM B. SELECT C. WHERE D. ORDER BY

B

Present special problems in relational operations, including arithmetic operations, comparison operations, and set operations. A. Abstract Values B. Nullity Problems C. Null Values D. Distinct Algorythms

C

The select clause may also contain arithmatic expression involving the operators A. + , - B. * , / C. A & B D. None of above

C

What is the form of the alter table command? A. alter table r drop A; B. drop table r; C. alter table r add A D; D. all of the above

C

What was the original name of SQL that IBM created? A. SQL B. HTML C. Sequel D. ISO

C

Which Clause causes the tuples in the result of a query to appear in sorted order? A. Select B. From C. Order by D. Where

C

What are some other commonly used names for a "correlation name" in the SQl standard?

Correlation names are also commonly referred to as a "table alias", a "correlation variable", or a "tuple variable".

: SQL allows which logical connective in the where clause? A. or B. not C. and D. all of the above

D

An identifier that is used to rename a relation is reffered to as a correlation name but is also known as A. table alias B. Correlation Variable C. tuple variable D. all of the above

D

Basic Structure of SQL queries clause is: A. Select B. From C. Where D. All of the above

D

Can a SQL relation be defined by using a create table command ?

True

SQL DDL deals with the following information EXCEPT A. the schema of each relation B. the integrity constraints C. the physical storage structure of each relation storage disk D. None of the above

D

Scalar subqueries can occur in which clauses? A. select B. where C. having D. all of the above

D

The SQL standard supports a variety of built-in types, including A. char(n) B. archar(n) C. int D. All of the above

D

The basic structure of an SQL query consists of the following clauses A. select B. from C. where D. All of the Above

D

The basic structure of an SQL query consists of which of these clauses? A. select B. from C. where D. All of the above

D

To remove a relation from an SQL database, use the command "________" A. delete table B. remove table C. lift table D. drop table

D

To remove a relation from an SQL database, which command is used? A. Rename Table Command B. Delete Table Command C. Void Table Command D. Drop Table Command

D

What clauses does the basic structure of an SQL query consist of? A. Select B. From C. Where D. All of the above

D

What entity type would you see in a database for a company? A. DEPARTMENT B. PROJECT C. EMPLOYEE D. All of the above

D

What query should we use to update a database? A. select B. where C. insert D. update

D

Which SQL operations operate on relations and correspond to the mathematical set operations ∪, ∩, and âˆ'? A. Union B. Intersect C. Except D. All of the above

D

Which built-in type The SQL standard doesn't support? A. char(n) B. varchar(n) C. int D. largeint

D

Which is NOT a step in the database design? A. conceptual design B. logical design C. physical design D. visual design

D

Which of the following is NOT a built-in type for SQL? A. char(n) B. int C. float(n) D. slant(n)

D

Which one of the below is the standard aggregate functions? A. Average B. Minimum C. Maximum D. All of the above

D

Who built the original version of SQL, originally called Sequel? A. ANSI B. ISO C. Blackberry D. IBM

D

: If a where clause is present, the predicate in the where clause is applied on the result relation of the having clause

False

Avg is NOT one of the five aggregate functions built into SQL.

False

DDL use only create table.

False

Is the float data type A fixed-length character string with user-specified length n. The full form, character, can be used instead.

False

Min, Max, Tally are aggregate functions

False

Null values are functions that take a collection of values as input and return a single value.

False

SQL DDL is used to find differences with specific schemas.

False

SQL allows us to test whether the result of a comparison is unknown, rather than true or false, by using the clauses is false and is true

False

SQL cannot prevents any update to the database that violates an integrity constraint

False

What is correlated subquery?

It is a subquery that uses a correlation name from an outer query.

How does null values work ?

It is possible for tuples to have a null value, denoted by null, for some of their attributes null signifies an unknown value or that a value does not exist. The result of any arithmetic expression involving null is null.

What does the participation constraint specify?

It specify whether the existence of an entity depends on it's being related to another entity via the relationship type. This constraints specifies the minimum number of relationship instance that each entity can participate in.

Why are queries on multiple relations important?

Queries on multiple relations are important because, they may needed depending on the case to get information. As an example, in a dentist office you want to obtain the names of all the patients as well the Ortho department, including all other departments within a building, you would need to access dept_name, title name of person (ex. Orthodonist).

What can SQL language do besides just query a database?

SQL language can define the structure of data, modify data in the database, and specify security constraints.

What is the role for each of the clause?

Select clause is a list of the attributes desired in the result of a query. From clause is a list of the relation to be assed in the evalution of the query. Where clause is a predicate involving attributes of the relation in the from clause.

What is Data-definition language (DDL).

The SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schemas.

What does the "drop table" command do?

The drop table command deletes all information about the dropped relation from the database.

What are the roles of each clause in a basic structure of an SQL?

The select clause is used to list the attributes desired and the result of a query. The front clause is a list of the relations to be accessed in the evaluation of the query. The where clause is a predicate involving attributes of the relation in the from clause.

what is the union operation?

The union operation automatically eliminates duplicates, unlike the select clause.

What does the with clause do?

The with clause provides a way of defining a temporary relation whose definition is available only to the query in which the with clause occurs.

What is the with clause?

The with clause provides a way of defining a temporary relation whose definition is available only to the query in which the with clause occurs.

Explation Insertion statement

To insert data into a relation, we either specify a tuple to be inserted or write a query whose result is a set of tuples to be inserted

A null value indicates an absent value that many exist but be unknown or that may not exist at all.

True

A null value indicates an absent value that may exist but be unknown or that may not exist at all.

True

Basic schema definition means we define as SQL relation by using the create table command.

True

List the basic types that supports SQL standard?

char(n): A fixed-length character string with user-specified length n. The full form, character, can be used instead. varchar(n): A variable-length character string with user-specified maximum length n. The full form, character varying, is equivalent. int: An integer (a finite subset of the integers that is machine dependent). The full form, integer, is equivalent. smallint: A small integer (a machine-dependent subset of the integer type). numeric(p, d): A fixed-point number with user-specified precision. The number consists of p digits (plus a sign), and d of the p digits are to the right of the decimal point. Thus, numeric(3,1) allows 44.5 to be stored exactly, but neither 444.5 nor 0.32 can be stored exactly in a field of this type. real, double precision: Floating-point and double-precision floating-point numbers with machine-dependent precision. float(n): A floating-point number with precision of at least n digits.

What are Aggregate Functions?

e functions that take a collection (a set or multiset) of values as input and return a single value.