Lecture 7 Flashcards

1
Q

three types of SQL keys

A
  • Primary key
  • Foreign key
  • Composite key
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Auto Increment Field

A

often used primary key field

To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

Syntax:

ALTER TABLE Table_name AUTO_INCREMENT=amount;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

different possible inputs to tables

A
  • direct input - one record at a time using
  • existing file - in bulk by uploading structured data from an existing source file
  • another table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Adding a row

A

INSERT INTO Table_name VALUES (“value-1”, …”value-n”);

(values must match number and type of table definition, although NULL values can be included (no quotes))

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Importing data in SQL

A

LOAD DATA LOCAL INFILE “sourcefilename

INTO TABLE Table_name

FIELDS TERMINATED BY “delimiter” ;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

INSERT INTO

A

used to insert new records in a table

  • Syntax 1 :
    • INSERT INTO table_name (column1, column2, column3, …)
    • VALUES (value1, value2, value3, …);
  • Syntax 2 :
    • INSERT INTO table_name
    • VALUES (value1, value2, value3, …);
      • if the column list is omitted, SQL assumes a list of all columns in their original CREATE TABLE order
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

INSERT & required matching

A

The list of values provided after the VALUE command must match columnList as follows:

  • number of items in each list must be same;
  • must be direct correspondence in position of items in two lists;
  • data type of each item in dataValueList must be compatible with data type of corresponding column.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

SQL DEFAULT constraint

A

provides a default value

City varchar(255) DEFAULT 'Sandnes‘

Adding default values retrospectively:

ALTER TABLE tableName;

ALTER City SET DEFAULT ‘Sandnes’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

INSERT INTO … SELECT

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

DELETE FROM table_name;

A

Deletes all rows, but leaves table structure, attributes, and indexes intact

How well did you know this?
1
Not at all
2
3
4
5
Perfectly