CS401A's Pre-Finals: Info Management Module 06

Question 1

Fundamentals

is a specific request for data manipulation issued by the end-user or the application to the DBMS.

Answer 1

A query

Question 2

Fundamentals

• SQL:
  ○ Stands for

Answer 2

Structured Query Language

Question 3

Fundamentals

• SQL:
  ○ Pronounced as

Answer 3

S-Q-L or “sequel”

Question 4

Fundamentals

• SQL:
  ○ Consists of commands that:

Answer 4

• Create database and table structures
• Perform various types of data manipulation and data administration
• Query the database to extract useful information

Question 5

Fundamentals

• Popular Database Management Tools

Answer 5

○ Microsoft SQL Server
○ MySQL
○ Oracle RDBMS
○ Microsoft Access

Question 6

Fundamentals * SQL Data Types:

Category
Exact numeric

Answer 6

Common Data Types
bigint, bit, decimal, int, money, numeric

Question 7

Fundamentals * SQL Data Types:

Category
Appropriate numeric

Answer 7

Common Data Types
float, real

Question 8

Fundamentals * SQL Data Types:

Category
Date and time

Answer 8

Common Data Types
date, datetime, time

Question 9

Fundamentals * SQL Data Types:

Category
Character strings

Answer 9

Common Data Types
char, text, varchar

Question 10

Fundamentals * SQL Data Types:

Category
Unicode character strings

Answer 10

Common Data Types
nchar, ntext, nvarchar

Question 11

Fundamentals * SQL Data Types:

Category
Binary strings

Answer 11

Common Data Types
binary, image, varbinary

Question 12

Fundamentals * SQL Data Types:

Category
Other data types

Answer 12

Common Data Types
cursor, sql_variant, table, uniqueidentifier, xml

Question 13

Fundamentals * SQL Operators:

Category
Arithmetic

Answer 13

Common Data Types
+, -, *, /, %

Question 14

Fundamentals * SQL Operators:

Category
Comparison

Answer 14

Common Data Types
=, >, <, >=, <=, <>

Question 15

Fundamentals * SQL Operators:

Category
Compound

Answer 15

Common Data Types
+=, -=, *=, /=, %=

Question 16

Fundamentals * SQL Operators:

Category
Logical

Answer 16

Common Data Types
AND, OR, NOT, LIKE, IN, BETWEEN, EXISTS, ANY, ALL

Question 17

SQL Data Definition Commands

— creates a new database

Answer 17

• CREATE DATABASE

Question 18

SQL Data Definition Commands

— deletes an existing database

Answer 18

• DROP DATABASE

Question 19

SQL Data Definition Commands

○ Example: WHAT DATABASE myDB;

Answer 19

CREATE
DROP

Question 20

SQL Data Definition Commands

— creates a new table in a database

Answer 20

• CREATE TABLE

Question 21

SQL Data Definition Commands

○ Example: WHAT TABLE Students (StudentID varchar(11), LastName varchar(99), FirstName varchar(99), Section varchar(5));

Answer 21

CREATE

Question 22

SQL Data Definition Commands

— deletes an existing table in a database

Answer 22

• DROP TABLE

Question 23

SQL Data Definition Commands

— WHAT an existing table in a database
○ Example: WHAT TABLE Students;

Answer 23

deletes
DROP

Question 24

SQL Data Definition Commands

○ To delete only the table’s data:

Answer 24

TRUNCATE TABLE

Question 25

# **SQL Data Definition Commands** ○ To WHAT only the table's data: ▪ *Example*: TRUNCATE TABLE Students;

Answer 25

delete

Question 26

# **SQL Data Definition Commands** — WHAT, WHAT, or WHAT columns in an existing table

Answer 26

* **ALTER TABLE** Adds deletes modifies

Question 27

# **SQL Data Definition Commands** Adds, columns in an existing table ○ *Syntax to add*: **WHAT TABLE** *table_name* **WHAT** *column datatype*;

Answer 27

* **ALTER TABLE** **ALTER** **ADD**

Question 28

# **SQL Data Definition Commands** WHAT, columns in an existing table ○ *Example*: ALTER TABLE Students ADD MiddleName varchar(99);

Answer 28

* **ALTER TABLE** Adds________

Question 29

# **SQL Data Definition Commands** deletes, columns in an existing table ○ *Syntax to delete*: **WHAT TABLE** *table_name* **WHAT** *column*;

Answer 29

* **ALTER TABLE** **ALTER** **DROP**

Question 30

# **SQL Data Definition Commands** WHAT, columns in an existing table ○ *Example*: ALTER TABLE Students DROP COLUMN Section;

Answer 30

* **ALTER TABLE** deletes

Question 31

# **SQL Data Definition Commands** modifies columns in an existing table ○ *Syntax to modify*: **WHAT TABLE** *table_name* **WHAT COLUMN** *column datatype*;

Answer 31

* **ALTER TABLE** **ALTER** **ALTER**

Question 32

# **SQL Data Definition Commands** WHAT columns in an existing table ○ *Example*: ALTER TABLE Students ALTER COLUMN MiddleName nvarchar(99);

Answer 32

* **ALTER TABLE** modifies

Question 33

# **SQL Constraints** — ensures that a column cannot have a NULL value upon creating a table

Answer 33

* **NOT NULL** on CREATE TABLE

Question 34

# **SQL Constraints** — ensures that a column cannot have a WHAT value upon WHAT a table ○ *Example*: CREATE TABLE Students (StudentID varchar(11) NOT NULL, LastName varchar(99) NOT NULL, FirstName varchar(99) NOT NULL, Section varchar(5));

Answer 34

* **NOT NULL** on CREATE TABLE NULL creating

Question 35

# **SQL Constraints** — ensures that a column in an existing table cannot have a NULL value

Answer 35

* **NOT NULL** on ALTER TABLE

Question 36

# **SQL Constraints** — ensures that a column in an WHAT table cannot have a WHAT value ○ *Example*: ALTER TABLE Students ALTER COLUMN Section varchar(5) NOT NULL;

Answer 36

* **NOT NULL** on ALTER TABLE existing NULL

Question 37

# **SQL Constraints** — ensures that all values in a column are different upon creating a table

Answer 37

* **UNIQUE** on CREATE TABLE

Question 38

# **SQL Constraints** — ensures that all values in a column are WHAT upon WHAT a table ○ *Example*: CREATE TABLE Students (StudentID varchar(11) NOT NULL UNIQUE, LastName varchar(99) NOT NULL, FirstName varchar(99) NOT NULL, Section varchar(5));

Answer 38

* **UNIQUE** on CREATE TABLE different creating

Question 39

# **SQL Constraints** — creates a UNIQUE constraint on a column of an existing table ○ *Syntax*: **WHAT TABLE** Students *table_name* **WHAT WHAT** *(column)*;

Answer 39

* **UNIQUE** on ALTER TABLE **CREATE** **ADD UNIQUE**

Question 40

# **SQL Constraints** — creates a WHAT constraint on a column of an WHAT table ○ *Example*: CREATE TABLE Students ADD UNIQUE (StudentID);

Answer 40

* **UNIQUE** on ALTER TABLE UNIQUE existing

Question 41

# **SQL Constraints** — uniquely identifies each row in a table

Answer 41

* **PRIMARY KEY** on CREATE TABLE

Question 42

# **SQL Constraints** — WHAT identifies each row in a table ○ *Example*: CREATE TABLE Students (StudentID varchar(11) NOT NULL PRIMARY KEY, LastName varchar(99) NOT NULL, FirstName varchar(99) NOT NULL, Section varchar(5));

Answer 42

* **PRIMARY KEY** on CREATE TABLE uniquely

Question 43

# **SQL Constraints** — creates a PRIMARY KEY constraint on a column of an existing table ○ *Syntax*: **WHAT TABLE** *table_name* **WHAT WHAT WHAT** *(column)*;

Answer 43

* **PRIMARY KEY** on ALTER TABLE **CREATE** **ADD PRIMARY KEY**

Question 44

# **SQL Constraints** — creates a WHAT WHAT constraint on a column of an WHAT table ○ *Example*: CREATE TABLE Students (StudentID varchar(11) NOT NULL PRIMARY KEY, LastName varchar(99) NOT NULL, FirstName varchar(99) NOT NULL, Section varchar(5));

Answer 44

* **PRIMARY KEY** on ALTER TABLE PRIMARY KEY existing

Question 45

# **SQL Constraints** — uniquely identifies a row in another table ○ *Example*: WHAT TABLE Orders (OrderID int NOT NULL PRIMARY KEY, TableNumber int NOT NULL, CustomerID int WHAT WHAT WHAT Customers (CustomerID));

Answer 45

* **FOREIGN KEY** on CREATE TABLE CREATE FOREIGN KEY REFERENCES

Question 46

# **SQL Constraints** — creates a FOREIGN KEY constraint on a column of an existing table ○ *Syntax*: **WHAT TABLE** *table1_name* **WHAT WHAT WHAT** *(table1_column)* **WHAT** *table2_name* *(table2_column)*;

Answer 46

* **FOREIGN KEY** on ALTER TABLE ALTER ADD FOREIGN KEY REFERENCES

Question 47

# **SQL Constraints** — creates a WHAT WHAT WHAT constraint on a column of an WHAT table ○ *Example*: ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID);

Answer 47

* **FOREIGN KEY** on ALTER TABLE CREATE FOREIGN KEY existing

Question 48

# **SQL Constraints** — ensures that all values in a column satisfy a specific condition upon creating a table ○ *Example*: WHAT TABLE Students (StudentID varchar(11) NOT NULL, LastName varchar(99) NOT NULL, FirstName varchar(99) NOT NULL, Age int WHAT (Age>=15));

Answer 48

* **CHECK** on CREATE TABLE CREATE CHECK

Question 49

# **SQL Constraints** — ensures that all values in a column of an existing table satisfy a specific condition ○ *Syntax*: **WHAT TABLE** *table_name* **WHAT WHAT** *(condition)*;

Answer 49

* **CHECK** on ALTER TABLE **ALTER** **ADD CHECK**

Question 50

# **SQL Constraints** — ensures that all values in a column of an WHAT table WHAT a WHAT WHAT ○ *Example*: ALTER TABLE Students ADD CHECK (Age>=15);

Answer 50

* **CHECK** on ALTER TABLE existing satisfy specific condition

Question 51

# **SQL Constraints** — sets a default value for a column when there is no value specified ○ *Example*: WHAT TABLE Students (StudentID varchar(11) NOT NULL, LastName varchar(99) NOT NULL, FirstName varchar(99) NOT NULL, Section varchar(5) WHAT 'Not yet enrolled');

Answer 51

* **DEFAULT** on CREATE TABLE CREATE DEFAULT

Question 52

# **SQL Constraints** — sets a default value for a column of an existing table when there is no value specified ○ *Syntax*: **WHAT TABLE** *table_name* **WHAT WHAT** *WHAT name* **WHAT** *'value'* **WHAT** *column*;

Answer 52

* **DEFAULT** on ALTER TABLE **ALTER** **ADD CONSTRAINT** *constraint* **DEFAULT** **FOR**

Question 53

# **SQL Constraints** — sets a WHAT value for a column of an existing table when there is no value specified ○ *Example*: ALTER TABLE Students ADD CONSTRAINT df_section DEFAULT 'Not yet enrolled' FOR Section;

Answer 53

* **DEFAULT** on ALTER TABLE default existing