SQL part 1

Question 1

SQL is a database language for:

Answer 1

• creating database and table structures
• performing data manipulation and administration
• querying the database to extract useful information

Question 2

t/f: SQL is a nonprocedural language

Answer 2

true

Question 3

what does “SQL is a nonprocedural language” mean

Answer 3

user specifies what must be done, but not how

Question 4

with SQL, ______ isn’t important, only ______ matter

Answer 4

where the data is actually stored, the specified relations and relationships

Question 5

SQL is an ______ standard

Answer 5

international

Question 6

2 organizations that define the standard for SQL

Answer 6

• American National Standards Institute (ANSI)
• International Organization for Standardization (ISO)

Question 7

ISO is a consortium of how many countries

Answer 7

150

Question 8

t/f: all relational DBMS software thus supports SQL

Answer 8

true

Question 9

Different DBMS software that supports SQL

Answer 9

• Oracle
• MySQL
• SQL Server
• DB2
• MS Access

Question 10

many database vendors have developed

Answer 10

extensions

Question 11

sql extensions

Answer 11

functions and commands
- basic, simple vocabulary of <100 “words”
- different software may have “dialects” with minor differences

Question 12

categories of sql commands

Answer 12

data definition language (DDL) and data manipulation language (DML)

Question 13

data definition language

Answer 13

commands that define a database, including creating, altering, dropping tables and stored procedures, and establishing constraints
(creating database structures)

Question 14

data manipulation language

Answer 14

commands that are used to manipulate data and extract information

Question 15

DML can be used for the commands

Answer 15

select, update, insert, delete

Question 16

DDL primary commands

Answer 16

• create table/create procedure
• alter table
• drop table (drop procedure)

Question 17

alter table command impacts

Answer 17

attributes or constraints

Question 18

3 command types within alter table

Answer 18

• add (new attribute, primary key, NULL, etc)
• modify (data type, etc)
• drop (column)

Question 19

example of DDL syntax shows

Answer 19

SQL command keywords and user-provided parameters
**slide 7

Question 20

DDL data types

Answer 20

• ANSI/ISO SQL data types
• MySQL data types

Question 21

ANSI/ISO SQL data types

Answer 21

• integer/smallint
• decimal(precision, scale)
• char(n) and varchar(n)
• date (julian date format)

Question 22

mysql numeric data types

Answer 22

tinyint, smallint, mediumint, int, bigint, decimal
**slide 10 for ranges

Question 23

mysql date and time data types

Answer 23

date, datetime

Question 24

mysql string data types

Answer 24

char(n), varchar(n), blob, text, enum

Question 25

decimal(M,D) means

Answer 25

M total digits, D digits after decimal

Question 26

mysql date format

Answer 26

YYYY-MM-DD

Question 27

invalid dates and times in mysql are converted to

Answer 27

zero values

Question 28

built-in mysql date and time functions

Answer 28

now(), timestampdiff(), interval, date_format(), date(), time(), day(), year(), month()

Question 29

char(n)

Answer 29

always allocates n bytes of storage

Question 30

with varchar(n), values that exceed n characters in length are

Answer 30

truncated

Question 31

varchar(n)

Answer 31

only allocates used space plus 1 byte

Question 32

blob

Answer 32

used for large binary strings of data

Question 33

text

Answer 33

used for large character strings of data

Question 34

enum

Answer 34

string object with a value chosen from a list of permitted values that is specified at table creation time