hillford exam 2 chapter1

Only one of the queries does not change the database contents

True

Given the data in the Bank database, a select query for accounts with negative balances would return nothing.

TRue

The insert, select, update, and delete queries are the only types of commands necessary to interact with a database system.

False

An update query cannot update data that isn't in the database.

True

Account

ID Name Balance

831 Raul Lopez 3300

572Mai Shiraishi 2500

290 Ethan Carr 5000

What is Braden Smith's balance in the following INSERT statement?

INSERT INTO Account VALUES (800, 'Braden Smith', 200);

200

Account

ID Name Balance

831 Raul Lopez 3300

572Mai Shiraishi 2500

290 Ethan Carr 5000

Which name is retrieved by the following SELECT statement?

SELECT Name FROM Account WHERE Balance < 3000;

Mai Shiraishi

Whose balance does the following UPDATE statement change?

UPDATE Account

SET Balance = 850

WHERE ID = 290;

Raul Lopez

Mai Shiraishi

Ethan Carr

Ethan Carr

Whose balance does the following UPDATE statement change?

UPDATE Account

SET Balance = 850

WHERE ID = 290;

Who is deleted by the following DELETE statement?

DELETE FROM Account WHERE ID = 999;

No one

INT

integer values

DECIMAL

fractional numeric values.

VARCHAR

textual values

DATE

year, month, and day

The BirthDate column stores only a date and no time.

True

CountryCode Name Continent

ABW Aruba SouthAmerica

AFG Afghanistan Asia

AGO Angola Africa

ALB Albania Europe

AND Andorra Europe

SELECT Name

FROM Country

WHERE Continent IN ('Asia', 'Europe', 'North America');

No results

Afghanistan, Albania, Aruba

Afghanistan, Albania, Andorra

Afghanistan, Albania, Andorra

CountryCode Name Continent

ABW Aruba SouthAmerica

AFG Afghanistan Asia

AGO Angola Africa

ALB Albania Europe

AND Andorra Europe

SELECT Name FROM Country WHERE Code IN ('AGO', 'Aruba', 'Europe', NULL);

No results

Angola

Aruba

Angola

SELECT Name FROM Country WHERE Continent NOT IN ('Asia', 'Antarctica', 'Europe');

Aruba, Angola

The BETWEEN

operator provides an alternative way to determine if a value is between two other values

The expression Age BETWEEN 13 AND 19 is true when Age is 19.

TRUE

If the Name column has data type VARCHAR(20), then the expression Name BETWEEN 'Anele' AND 'Jose' is valid.

True

A query containing BETWEEN expression runs faster than a query containing an equivalent expression written with comparison operators.

False

LIKE

used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _.

CountryLanguage

CountryCode Language IsOfficial Percentage

COK English F 0.0

COK Maori T 0.0

COL Arawakan F 0.1

COL Caribbean F 0.1

COL Chibcha F 0.4

Arawakan and Caribbean

LIKE '%r%n'

CountryLanguage

CountryCode Language IsOfficial Percentage

COK English F 0.0

COK Maori T 0.0

COL Arawakan F 0.1

COL Caribbean F 0.1

COL Chibcha F 0.4

Chibcha

LIKE %cha

CountryLanguage

CountryCode Language IsOfficial Percentage

COK English F 0.0

COK Maori T 0.0

COL Arawakan F 0.1

COL Caribbean F 0.1

COL Chibcha F 0.4

LIKE _cha

no matches

CountryLanguage

CountryCode Language IsOfficial Percentage

COK English F 0.0

COK Maori T 0.0

COL Arawakan F 0.1

COL Caribbean F 0.1

COL Chibcha F 0.4

LIKE %m_o%'

Maori

CountryLanguage

CountryCode Language IsOfficial Percentage

COK English F 0.0

COK Maori T 0.0

COL Arawakan F 0.1

COL Caribbean F 0.1

COL Chibcha F 0.4

LIKE BINARY '%E%'

English

SELECT Name, District, Population FROM City

City

ID Name CountryCode District Population

301EmbuBRASão Paulo2222233

02MossoróBRARio Grande do Norte214901

303VárzeaGrandeBRAMatoGrosso214435

304PetrolinaBRAPernambuco210540

305BarueriBRASão Paulo208426

Embu São Paulo 222223 Barueri São Paulo 208426 Mossoró Rio Grande do Norte 214901 Petrolina Pernambuco 210540 Várzea Grande Mato Grosso 214435

)RDER BY District DESC

Várzea Grande Mato Grosso 214435 Petrolina Pernambuco 210540 Mossoró Rio Grande do Norte 214901 Barueri São Paulo 208426 Embu São Paulo 222223

ORDER BY District, Population

Data Definition Language

CREATE TABLE City ( ID INTEGER, Name VARCHAR(15), Population INTEGER );

Data Definition Statements

Data Query Language

SELECT Name

FROM City

WHERE Population > 15000;

Data Manipulation Language

inserts, updates, and deletes data.

Data Transaction Language

manages transactions.

Data Control Language

specifies user access to data

DML

Insert a data row into table product.

DCL

Grant all permissions to user 'tester'.

DTL

Rollback database changes.

DDL

Create table Product.

DQL

Select all rows from table Product.

The INSERT statement adds a student to the Student table. How many clauses are in the INSERT statement?

INSERT INTO Student VALUES (888, 'Smith', 'Jim', 3.0);

2 The INSERT INTO clause is followed by the VALUES clause.

The SQL statement below is used to select students with the last name "Smith". What is wrong with the statement?

SELECT FirstName FROM Student WHERE LastName = Smith;

The literal "Smith" must be surrounded by single quotes or double quotes.

What is wrong with the SQL statement below?

SELECT FirstName

from Student

A terminating semicolon is missing.

Refer to the following simplified definition of SELECT syntax:

SELECT select_expr [, select_expr] ...

[FROM table_references]

[WHERE where_condition]

[ORDER BY {col_name | expr | position}]

select_expr is an expression that appears in a SELECT clause, and may not be blank.

1)

SELECT FROM City; is a valid statement.

False

Refer to the following simplified definition of SELECT syntax:

SELECT select_expr [, select_expr] ...

[FROM table_references]

[WHERE where_condition]

[ORDER BY {col_name | expr | position}]

select_expr is an expression that appears in a SELECT clause, and may not be blank.

SELECT 'Hello'; is a valid statement.

True

Refer to the following simplified definition of SELECT syntax:

SELECT select_expr [, select_expr] ...

[FROM table_references]

[WHERE where_condition]

[ORDER BY {col_name | expr | position}]

select_expr is an expression that appears in a SELECT clause, and may not be blank.

)

The FROM clause must contain table names only.

False

Refer to the following simplified definition of SELECT syntax:

SELECT select_expr [, select_expr] ...

[FROM table_references]

[WHERE where_condition]

[ORDER BY {col_name | expr | position}]

select_expr is an expression that appears in a SELECT clause, and may not be blank.

)

The ORDER BY keyword must be followed by a column name.

False

SQL/PSM

Extends SQL with programming elements such as loops and procedures.

Foundation

Covers DDL, DQL, DML, DCL, and DTL.

SQL/Schemata

Defines the information_schema database.

SQL/XML

Specifies the XML data type and related functions.

Framework

Introduces SQL concepts and terminology.

The statement CREATE DATABASE auto; creates a database called 'auto'.

True

The statement CREATE DATABASE university; creates a second university database.

False The university database already exists. Each database is uniquely named, so two databases named 'university' cannot exist.

The statement DROP DATABASE university; deletes the university database and all associated tables.

True

The statement DROP DATABASE nonprofit; deletes the nonprofit database.

non profit not exsit

SHOW DATABASES

lists all databases in the database system instance

SHOW TABLES

lists all tables in the default database. The optional clause FROM DatabaseName lists tables in a named database

SHOW COLUMNS FROM TableName

lists all columns in the TableName table of the default database.

SHOW CREATE TABLE TableName

shows the CREATE TABLE statement for the TableName table of the default database

Which statement shows all databases in a database system?

SHOW DATABASES;

Which statement must precede a SHOW TABLES statement to see the tables from the bikeStore database?

SHOW DATABASES;

USE bikeStore;

SHOW COLUMNS FROM bikeStore;

USE bikeStore;

Which statement shows all the columns in the Country table?

SHOW COLUMNS;

SHOW COLUMNS Country;

SHOW COLUMNS FROM Country;

SHOW COLUMNS FROM Country;

What are the components of a column?

Name and data type

Must a table have at least one row?

No

How does a database administrator specify column names and data types?

With the SQL language

_____ may appear in each cell.

Any number of values

Exactly one value

No values or one value

Exactly one value

here are duplicate column names allowed?

Within a single table.

In different tables.

Never.

In different tables.

What does the principle of data independence state?

The result of a database query is not affected by the physical organization of data on storage devices.

How can driver's license information be added to the Employee table?

By creating another column with a new name, such as ID2 or DriverLicense.

The statement DROP Employee; deletes the Employee table.

False since the keyword TABLE must appear after DROP: DROP TABLE Employee;

The DROP TABLE statement does not delete the table unless the table is empty.

False

ALTER TABLE

adds, deletes, or modifies columns on an existing table

dd a column called Description to the Department table.

ALTER TABLE Department

_______VARCHAR(50);

ADD Description

Rename column Description to ShortDesc.

ALTER TABLE Department

_______VARCHAR(50);

CHANGE Description ShortDesc

Change column ShortDesc to accept up to 80 characters.

ALTER TABLE Department

CHANGE_______

ShortDesc ShortDesc VARCHAR(80)

Delete the column ShortDesc.

ALTER______

TABLE Department

DROP ShortDesc

Using materialized views always improves database performance.

False

The performance of a query on a non-materialized view is identical to the performance of the corresponding merged query on base tables.

True

A view query can reference another view table.

True

In MySQL, two different queries that generate the same result table always have the same execution time.

False

Views can be used to hide rows as well as columns from database users.

True

Select

sigma

Project

II

Product

X

Join

multiplication symbol with vertical bars

Rename

p

Aggregate

gamma

Changing the order of operations can alter the result of an expression

True

Exactly one query execution plan is possible for each SQL query.

False

Query optimizers typically choose an optimal expression based on total number of rows processed.

False

The join operation is a low-level database action.

False

do 8.8 and keep on