SQL Basics + Manipulation Flashcards
define table
collection of data organized into rows and columns.
relation
another word for a table: collection of data organized into rows and columns.
define column
a set of data values of a particular type
define row
A row is a single record in a table.
“*” character
Wildcard character. It allows you to select every column in a table without having to name each one individually.
most common data types (4)
INTEGER, a positive or negative whole number
TEXT, a text string
DATE, the date formatted as YYYY-MM-DD
REAL, a decimal value
define statement
text that the database recognizes as a valid command.
what do statements end with?
SEMICOLON ;
What does the SELECT statement return?
a new table called the result set.
3 components of a statement
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
CREATE statements
allow us to create a new table in the database
SELECT statements
fetch data from a database
INSERT statement
inserts a new row into a table.
ALTER TABLE statement
add, delete, or modify columns in an existing table.
also used to add and drop various constraints on an existing table.
ADD COLUMN statement
lets you add a column to the table
syntax for adding a column
ALTER TABLE [talename]
ADD COLUMN [columnname] COLUMN DATA TYPE;
what is NULL?
Represents missing or unknown data
can you add a column at a specific position to a table?
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.
UPDATE statement
edits a row in a table.
SET command
indicates the column to edit, takes the =
WHERE command
indicates which row(s) to update with the new column value
how is ALTER different from UPDATE?
ALTER modifies columns. Can add, delete, or modify.
UPDATE modifies rows. Can only change them, not add or remove.
UPDATE syntax
UPDATE table name
SET column name = new data
WHERE row name = row specifier
DELETE FROM statement
deletes one or more existing rows from a table
DELETE FROM syntax
DELETE FROM table name
WHERE row or rows IS or = PARAMETERS
how do we delete a specific number of rows? what is the syntax?
using the LIMIT statement: syntax:
DELETE FROM tablename
WHERE condition
LIMIT x
where x is the number of items you want to delete
define constraint
add information about how a column can be used
at what point during the command do we invoke constraints? How do we separate constraints?
after specifying the data type for a column
separated by a comma ,
what is a PRIMARY KEY column
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
UNIQUE columns
must have a different value for every row. similar to PRIMARY KEY except a table can have many different UNIQUE columns
NOT NULL columns
require a value to be present, otherwise constraint violation
DEFAULT columns
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.
what are 3 reasons we might want to apply constraints to a table?
- preventing invalid data and therefore incorrect calculations
- prevent missing data
- uniqueness
DROP TABLE command
permanently removes a table from a database. not frequently used
ANALYZE command
obtains statistics about a table
what is SQLite and what distinguishes it from others of its kind?
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
what are drawbacks to sqlite?
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
