Chapter 3 SQL

null

A data value used to represent situations in which an actual value is unknown, unavailable or not applicable

script

A file containing SQL commands is called a ____________________ file.

script file

A file that contains one or more SQL commands

Structured Query Language

A language used for retrieving and manipulating database data

statement history

A special area in which the most recently entered command is stored

MySQL

An open source database management system that supports the SQL language

default database

Database to which all subsequent commands pertain

CHAR(n)

Fixed-length character data

single quotation marks

For any column whose data type is CHAR, values must be enclosed in ____.

auto_increment

If the word ____________________ follows the word INT, a column is created for which MySQL will automatically generate a new sequence number each time a new row is added.

False

If you added a row that should not be in a table, use a REMOVE command to remove it.

YYYY-MM-DD

In MySQL, dates have the form ____.

18

In MySQL, table names cannot exceed ____ characters.

False

In MySQL, the data type STRING(n) stores a character string n characters long.

statement history

In MySQL, the most recent command entered is stored in a special area of memory called the ____.

Backspace

In MySQL, to delete the previous character, you need to press the ____ key(s).

CTRL + A

In MySQL, to move to the beginning of the current line, you need to press the ____ key(s).

CTRL + E

In MySQL, to move to the end of a line, you need to press the ____ key(s).

True

In MySQL, you can display the data vertically rather than horizontally.

prompt

In MySQL, you type commands at the mysql> ____.`

use

In MySQL, you use the ____________________ command to change the database to the one you want to use.

False

In SQL, there are special rules that specify that a particular word must begin in a particular position on the line.

True

In SQL, you use the NOT NULL clause in a CREATE TABLE command to indicate columns that cannot contain null values.

Reserved words

SQL command words

query

SQL is Structured ____________________ Language.

1970's

SQL was developed in the mid-____.

.txt

Script files must have a ____________________ file name extension.

SQL

Structured Query Language

True

The CREATE DATABASE command is executed only once for a database.

USE

The SQL command to change the default database

CREATE DATABASE

The SQL command to create a database

INSERT

The SQL command used to add rows to a table

UPDATE

The SQL command used to change a value in a table

DELETE

The SQL command used to delete a row in a table

DROP TABLE

The SQL command used to drop an entire table

VALUES

The SQL command used to insert values in a table

SHOW COLUMNS

The SQL command used to list all the columns in a table

DESCRIBE

The SQL command used to list all the columns in a table and their corresponding data types

SELECT

The SQL command used to view data in a table

use

The ____ command activates the default database.

create table

The ____ command defines a table's structure by listing its columns, data types, and column lengths

int

The ____ data type is used to store integers.

varchar

The ____ data type stores only the actual character string.

insert

The ____________________ command adds rows to a table.

NOT NULL

The clause in a CREATE TABLE command used to indicate which columns cannot contain null values

decimal

The correct data type for the BALANCE column in the CUSTOMER table is ____.

command line

The mysql prompt displays in the ____ Client window.

True

The shortcut command to quit MySQL is \q.

\h

The simplest way to obtain help in MySQL is to type ____ at the mysql> prompt.

Data Definition Language (DDL)

These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.

Transaction Control Language (TCL)

These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT

Data Control Language (DCL)

These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE

Data Manipulation Language (DML)

These SQL commands are used for storing, retrieving, modifying, and deleting data. These Data Manipulation Language commands are: SELECT, INSERT, UPDATE, and DELETE

create database

To create a new database, execute the ____________________ command.

asterisk

To display all the rows and columns in a table, type the word SELECT, followed by a(n) ____, followed by the word FROM and then the name of the table containing the data you want to view.

\G

To display data vertically rather than horizontally, replace the semicolon at the end of the command with ____.

False

To edit an SQL command, type EDIT at the mysql prompt..

False

To enter a null value into a table, you use a special format of the NULL command

show columns

To examine the structure of a table, use the ____________________ command.

help union

To get more information about the union command, type ____________________ at the command prompt.

not null

To indicate columns that cannot contain null values, use the ____________________ clause in a CREATE TABLE statement.

up arrow

To move up a line in the statement history, use the ____________________ key(s).

\.

To run a script file, type either SOURCE or ____________________ followed by the name of the file.

select

To view the data in a table, you use the ____________________ command.

data type

Type of data that a column can contain as well as the maximum number of characters or digits that the column can stor haracter

VARCHAR(n)

Variable-length character data

sequel

What is the original name of SQL?

semicolon

When a script file contains more than one command, each command must end with a ____.

False

When adding rows to character columns, make sure you enclose the values in double quotation marks.

update

Which of the following commands changes the value in a column?

TBL_1

Which of the following is a valid name for a table?

drop

You can delete the entire table using the ____________________ TABLE command.

update

You can use the ____________________ command to update a value in a table.

semicolon

You indicate the end of a command by typing a(n) ____.

create table

You use the ____________________ command to describe the layout of a table.

notepad

____ is an example of a text editor.

CREATE DATABASE

creates a new database

CREATE TABLE

creates a new table

CREATE INDEX

creates an index (search key)

DROP TABLE

deletes a table

DROP INDEX

deletes an index

DELETE

deletes data from a database

SELECT

extracts data from a database

INSERT INTO

inserts new data into a database

Database schema

logical container for the database objects

ALTER DATABASE

modifies a database

ALTER TABLE

modifies a table

SQL/Persistent stored modules (SQL/PSM)

statements which extend SQL by adding procedural programming capabilities, such as variables and flow-of-control statements, that provide some programmability within the SQL framework

UPDATE

updates data in a database

DECIMAL(p,q)

used to represent decimal numbers

INT

used to represent integer values