SQL Basics + Manipulation

Question 1

define table

Answer 1

collection of data organized into rows and columns.

Question 2

relation

Answer 2

another word for a table: collection of data organized into rows and columns.

Question 3

define column

Answer 3

a set of data values of a particular type

Question 4

define row

Answer 4

A row is a single record in a table.

Question 5

“*” character

Answer 5

Wildcard character. It allows you to select every column in a table without having to name each one individually.

Question 6

most common data types (4)

Answer 6

INTEGER, a positive or negative whole number
TEXT, a text string
DATE, the date formatted as YYYY-MM-DD
REAL, a decimal value

Question 7

define statement

Answer 7

text that the database recognizes as a valid command.

Question 8

what do statements end with?

Answer 8

SEMICOLON ;

Question 9

What does the SELECT statement return?

Answer 9

a new table called the result set.

Question 10

3 components of a statement

Answer 10

1) Clause/Command: performs a specific task
2) Table Name
3) Parameter: list of columns, data types, or values passed to a clause as an argument

Question 11

CREATE statements

Answer 11

allow us to create a new table in the database

Question 12

SELECT statements

Answer 12

fetch data from a database

Question 13

INSERT statement

Answer 13

inserts a new row into a table.

Question 14

ALTER TABLE statement

Answer 14

add, delete, or modify columns in an existing table.

also used to add and drop various constraints on an existing table.

Question 15

ADD COLUMN statement

Answer 15

lets you add a column to the table

Question 16

syntax for adding a column

Answer 16

ALTER TABLE [talename]
ADD COLUMN [columnname] COLUMN DATA TYPE;

Question 17

what is NULL?

Answer 17

Represents missing or unknown data

Question 18

can you add a column at a specific position to a table?

Answer 18

No. by default a new column will always be added to the end of the table.

BUT you can always select columns in any order eg SELECT col3, col5, col2;

an alternative is to reate a new table and add columns in your preferred order.

Question 19

UPDATE statement

Answer 19

edits a row in a table.

Question 20

SET command

Answer 20

indicates the column to edit, takes the =

Question 21

WHERE command

Answer 21

indicates which row(s) to update with the new column value

Question 22

how is ALTER different from UPDATE?

Answer 22

ALTER modifies columns. Can add, delete, or modify.

UPDATE modifies rows. Can only change them, not add or remove.

Question 23

UPDATE syntax

Answer 23

UPDATE table name
SET column name = new data
WHERE row name = row specifier

Question 24

DELETE FROM statement

Answer 24

deletes one or more existing rows from a table

Question 25

DELETE FROM syntax

Answer 25

DELETE FROM table name
WHERE row or rows IS or = PARAMETERS

Question 26

how do we delete a specific number of rows? what is the syntax?

Answer 26

using the LIMIT statement: syntax:

DELETE FROM tablename
WHERE condition
LIMIT x

where x is the number of items you want to delete

Question 27

define constraint

Answer 27

add information about how a column can be used

Question 28

at what point during the command do we invoke constraints? How do we separate constraints?

Answer 28

after specifying the data type for a column

separated by a comma ,

Question 29

what is a PRIMARY KEY column

Answer 29

used to uniquely identify the row. Attempts to insert a row with an identical value to a row already in the table will result in a constraint violation

Question 30

UNIQUE columns

Answer 30

must have a different value for every row. similar to PRIMARY KEY except a table can have many different UNIQUE columns

Question 31

NOT NULL columns

Answer 31

require a value to be present, otherwise constraint violation

Question 32

DEFAULT columns

Answer 32

take an additional argument that will be the assumed value for a new row if the user does not specify a value for that column.

Question 33

what are 3 reasons we might want to apply constraints to a table?

Answer 33

• preventing invalid data and therefore incorrect calculations
• prevent missing data
• uniqueness

Question 34

DROP TABLE command

Answer 34

permanently removes a table from a database. not frequently used

Question 35

ANALYZE command

Answer 35

obtains statistics about a table

Question 36

what is SQLite and what distinguishes it from others of its kind?

Answer 36

it’s a database engine – software that allows users to interact with a relational database

a database is stored in a single file, which makes copying and sharing databases very easy

Question 37

what are drawbacks to sqlite?

Answer 37

1 only one user can update the file at a time
2 security requires more work
3 advanced features are limited
4 creates schemas, but doesn’t enforce them - doesn’t validate data types - any data type can be stored in any column