CS401A's Pre-Finals: Info Management Module 07

Question 1

SQL Data Manipulation Commands

• The basic data manipulation commands are:

Answer 1

INSERT, SELECT, UPDATE, and DELETE.

Question 2

SQL Data Manipulation Commands

— \_\_\_\_ \_\_\_ \_\_\_\_\_\_\_\_\_\_\_\_ to a table
○ Syntax: INSERT INTO table name (columns) VALUES (values);

Answer 2

• INSERT INTO
  adds new rows/records

Question 3

SQL Data Manipulation Commands

— adds new rows/records to a table
○ Example: \_\_\_\_\_\_ \_\_\_\_ Students (LastName, Section) \_\_\_\_\_\_ (‘Reyes’, ‘IT102’);

Answer 3

• INSERT INTO
  INSERT INTO
  VALUES

Question 4

SQL Data Manipulation Commands

○ To \_\_\_ \_\_\_ \_\_\_\_\_\_\_ to \_\_\_ columns of a table
■ Syntax: INSERT INTO table_name VALUES (values);

Answer 4

• INSERT INTO
  add new records
  all

Question 5

SQL Data Manipulation Commands

○ To add new records to all columns of a table
■ Example: \_\_\_\_\_\_ \_\_\_\_ Students \_\_\_\_\_\_ (‘Reyes’, ‘IT102’);

Answer 5

• INSERT INTO
  INSERT INTO
  VALUES

Question 6

SQL Data Manipulation Commands

— \_\_\_\_\_\_\_\_\_ \_\_\_\_\_\_ of \_\_\_ \_\_\_\_ or a \_\_\_\_\_\_ \_\_ \_\_\_\_ in a table
○ Syntax: SELECT columns FROM table_name;

Answer 6

• SELECT
  retrieves values
  all rows
  subset of rows

Question 7

SQL Data Manipulation Commands

— retrieves values of all rows or a subset of rows in a table
○ Example: \_\_\_\_\_\_ LastName, Section \_\_\_\_ Students;

Answer 7

• SELECT
  SELECT
  FROM

Question 8

SQL Data Manipulation Commands

○ To \_\_\_\_\_\_ \_\_\_ columns:
■ Syntax: SELECT * FROM table_name;

Answer 8

• SELECT
  ________select all________

Question 9

SQL Data Manipulation Commands

○ To select all columns:
■ Example: ` ______ _ ____` Students;

Answer 9

• SELECT
  SELECT * FROM

Question 10

SQL Data Manipulation Commands

— an operator used with \_\_\_\_\_\_ to \_\_\_\_\_\_\_\_ \_\_\_\_\_\_ \_\_\_\_\_\_ from \_\_\_\_\_\_\_ in a table
○ Syntax: SELECT DISTINCT columns FROM table_name;

Answer 10

• DISTINCT
  SELECT
  retrieve unique values
  columns

Question 11

SQL Data Manipulation Commands

— an operator used with SELECT to retrieve unique values from columns in a table
○ Example: \_\_\_\_\_\_ \_\_\_\_\_\_\_\_ Section \_\_\_\_ Students;

Answer 11

• DISTINCT
  SELECT DISTINCT
  FROM

Question 12

SQL Data Manipulation Commands

— an option used with \_\_\_\_\_\_ to \_\_\_\_\_\_ the ` ____ __ ____ based on ________ ________`
○ Syntax: SELECT columns FROM table_name WHERE condition;

Answer 12

• WHERE
  SELECT
  filter
  rows of data
  provided criteria

Question 13

SQL Data Manipulation Commands

— an option used with SELECT to filter the rows of data based on provided criteria
○ Example: \_\_\_\_\_\_ _ \_\_\_\_ Students \_\_\_\_\_ Section = ‘IT101’;

Answer 13

• WHERE
  SELECT * FROM
  WHERE

Question 14

SQL Data Manipulation Commands

— an option used with SELECT to filter the rows of data based on provided criteria
○ To select numeric fields, do not \_\_\_\_\_\_\_ in ` _________ _____`.
Example: SELECT * FROM Students WHERE Age >= 18;

Answer 14

• WHERE
  enclose
  quotation marks

Question 15

SQL Data Manipulation Commands

— an operator used with \_\_\_\_\_\_ to \_\_\_\_\_\_\_\_\_ whether a field is \_\_\_\_\_ \_\_ \_\_\_
○ Syntax: SELECT columns FROM table_name WHERE column IS NULL;

Answer 15

• IS NULL
  SELECT
  determine
  empty or not

Question 16

SQL Data Manipulation Commands

— an operator used with SELECT to determine whether a field is empty or not
○ Example: \_\_\_\_\_\_ LastName, Section \_\_\_\_ Students \_\_\_\_\_ Section \_\_ \_\_\_\_;

Answer 16

• IS NULL
  SELECT
  FROM
  WHERE
  IS NULL

Question 17

SQL Data Manipulation Commands

— an operator used with \_\_\_\_\_ to \_\_\_\_\_\_\_\_\_ whether a value \_\_\_\_\_\_\_ a \_\_\_\_\_ \_\_\_\_\_\_ \_\_\_\_\_\_\_
○ Syntax: SELECT columns FROM table_name WHERE column LIKE pattern;

Answer 17

• LIKE
  WHERE
  determine
  matches
  given string pattern

Question 18

SQL Data Manipulation Commands

— an operator used with WHERE to determine whether a value matches a given string pattern
○ Wildcards: % represents \_\_\_\_, \_\_\_, or \_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_\_ while _ represents a \_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_

Answer 18

• LIKE
  zero
  one
  multiple characters
  single character

Question 19

SQL Data Manipulation Commands

— an operator used with WHERE to determine whether a value matches a given string pattern
○ Example: \_\_\_\_\_\_ _ \_\_\_\_ Students \_\_\_\_\_ LastName \_\_\_\_ ‘_b%’;

Answer 19

• LIKE
  SELECT * FROM
  WHERE
  LIKE

Question 20

SQL Data Manipulation Commands

○ Example: SELECT * FROM Students WHERE LastName LIKE ‘_b%’;
○ Meaning: All students with last names have ‘_’ in the \_\_\_\_\_\_ position.

Answer 20

• LIKE
  ________b________
  second

Question 21

SQL Data Manipulation Commands

— an operator used with \_\_\_\_\_ to \_\_\_\_\_ whether a value \_\_\_\_\_\_\_ \_\_\_ \_\_\_\_\_ within a \_\_\_\_\_ \_\_\_\_
○ Syntax: SELECT columns FROM table_name WHERE column IN (values);

Answer 21

• IN
  WHERE
  check
  matches any value
  given list

Question 22

SQL Data Manipulation Commands

— an operator used with WHERE to check whether a value matches any value within a given list
○ Example: \_\_\_\_\_\_ _ \_\_\_\_ Students \_\_\_\_\_ Section \_\_ (‘IT101’, ‘IT102’, ‘IT103’);

Answer 22

• IN
  SELECT * FROM
  WHERE
  IN

Question 23

— an operator used with \_\_\_\_\_ to \_\_\_\_\_ whether a value is \_\_\_\_\_\_ a \_\_\_\_\_
○ Syntax: SELECT column FROM table_name WHERE column BETWEEN value1 AND value2;

Answer 23

• BETWEEN
  WHERE
  check
  within
  range

Question 24

SQL Data Manipulation Commands

— an operator used with WHERE to check whether a value is within a range
○ Example: \_\_\_\_\_\_ _ \_\_\_\_ Students \_\_\_\_\_ Age \_\_\_\_\_\_\_ 13 \_\_\_ 15;

Answer 24

• BETWEEN
  SELECT * FROM
  WHERE
  BETWEEN
  AND

Question 25

— An option used with `______` to `____ _________ ______` in `_________` or `__________` order ○ *Syntax*: **SELECT** *columns* **FROM** *table_name* **ORDER BY** *columns*;

Answer 25

* **ORDER BY** SELECT sort retrieved values ascending descending

Question 26

— An option used with SELECT to sort retrieved values in ascending or descending order ○ *Example*: `______ _ ____` STUDENTS `_____ __` LastName;

Answer 26

* **ORDER BY** SELECT * FROM ORDER BY

Question 27

— An option used with SELECT to sort retrieved values in ascending or descending order ○ To sort values in `__________` order: `______ _ ____` *table_name* `_____ __` *columns* `____`;

Answer 27

* **ORDER BY** descending SELECT * FROM ORDER BY DESC

Question 28

— `________ ________ _______` in a table ○ *Syntax*: **UPDATE** *table_name* **SET** *column1* = *value1*, ... **WHERE** *condition*;

Answer 28

* **UPDATE** modifies existing records

Question 29

# **SQL Data Manipulation Commands** — modifies existing records in a table ○ *Example*: `______` Students `___` Section = 'IT202', Status = 'Irregular' `_____` StudentID = '2018-100013';

Answer 29

* **UPDATE** UPDATE SET WHERE

Question 30

# **SQL Data Manipulation Commands** — `______ ________ _______` in a table ○ *Syntax*: **DELETE FROM** *table_name* **WHERE** *condition*;

Answer 30

* **DELETE** removes existing records

Question 31

# **SQL Data Manipulation Commands** — removes existing records in a table ○ `______ ____` Students `_____` StudentID = '2018-100013';

Answer 31

* **DELETE** DELETE FROM WHERE

Question 32

# **SQL Data Manipulation Commands** — removes existing records in a table ○ To `______ ___` records: DELETE FROM *table_name*;

Answer 32

* **DELETE** ________delete all________