Manipulation Flashcards
What does SQL stand for?
Structured Query Language
What is SQL?
A programming language designed to manipulate data stored in relational databases.
SQL operates through _______ statements.
declarative
What are some advantages of SQL?
- accuracy
- security
- integrity
What is a Relational Database?
A database that organizes information into one or more tables.
What is a Table?
A collection of data organized into rows and columns.
What are Tables referred to as?
Relations
What is a column in a Table?
A set of data values of a particular type.
What kind of types would be in a Table column?
id TEXT
name TEXT
age INTEGER
What is a row in a Table?
A single record in a table.
T or F: All data stored in a relational database is of a certain data type.
True
What are some common data types?
INTEGER
TEXT
DATE
REAL
What type of datatype is INTEGER?
A positive or negative whole number
What type of datatype is TEXT?
A text string
What type of datatype is DATE?
The date formatted as YYYY-MM-DD
What type of datatype is REAL?
A decimal value
What is a Statement?
Text that the database recogonize as a valid command.
What do Statements always end in?
A semicolon ( ; )
CREATE TABLE table_name ( column_1 datatype, column_2 datatype, column_3 datatype ) ;
This is an example of a statement.
What is CREATE TABLE?
A clause.
What do Clauses do?
Perform specific tasks in SQL.
How are Clauses written and why?
By convention, clauses are written in capital letters.
What are Clauses also referred to as?
Commands
What does table_name refer to in the CREATE TABLE Statement?
The name of the table that the command is applied to.
What does column_1 datatype, column_2 datatype, column_3 datatype refer to in the CREATE TABLE Statement?
A parameter list of column names and the associated data type.
What is a Parameter?
A list of columns, data types, or values that are passed to a clause as an argument.
T or F: The number of lines used in a SQL statement does not matter.
True
How can a Statement be written?
1) Written all in one line
2) Split up across multiple lines (easier to read)
What type of Statement helps us create a new Table?
CREATE
What type of Statement inserts a new row into a Table?
INSERT
The INSERT Statement adds what to the Table?
Rows
What two clauses go with the INSERT Statement?
INSERT INTO __ () VALUES()
What does the INSERT INTO ___ ( ) clause do?
Adds the specified row or rows
What does the VALUES clause do?
Indicates the data being inserted
INSERT INTO celebs (id, name, age) VALUES (1, ‘Justin Bieber’, 22);
An INSERT CLAUSE
What type of Statement fetches data from the database?
SELECT
What does a SELECT Statement do?
It returns all data in the selected column of the given Table.
What does this SELECT statement do?
SELECT name
FROM celebs;
It returns all data in the name column
Describe what each part of this SELECT statement does
SELECT name
FROM celebs;
- SELECT is the clause
- name is the column to query data from
- FROM celebs specifies the name of the table to query data from
What is SELECT?
A clause that indicates the statement is a query.
What type of clause do you use every time you want to query data from a database?
SELECT
What special wildcard character lets you query data from all columns in a Table with SELECT?
*
What does this SELECT statement do
SELECT * FROM celebs;
This queries all columns in the celebs Table.
What is the name for the new Table that is returned from a SELECT statement?
The result set
What type of Statement adds a new column to the Table?
ALTER TABLE
What does the clause ALTER TABLE do?
Lets you make specified changes.
What are the two clauses in the ALTER TABLE statement?
ALTER TABLE _____
ADD COLUMN ____ ____ TEXT;
What does the clause ADD COLUMN do?
Add a new column or table.
What must be included in the clause ADD COLUMN?
ADD COLUMN ______ ______;
The name of the new column and the data type for the new column.
What is NULL? What does it represent?
A special value that represents missing or unknown data?
What is the symbol for NULL?
∅
What type of Statement edits a row in a Table?
UPDATE
What does the clause UPDATE do?
Edits a row in the Table.
What are the three clauses in the UPDATE statement?
UPDATE _______
SET _____________ = ‘ __________ ‘
WHERE ______ = ______;
(UPDATE, SET, and WHERE)
Explain what each part in this UPDATE statement does
UPDATE celebs
SET twitter_handle = ‘@taylorswift’
WHERE id = 4;
UPDATE is the clause that edits a row in the table
celebs is the name of the table
SET is a clause that indicates the column to edit
twitter_handle is the name of the column that is going to be updated
@taylorswift13 is the new value that is going to be inserted into the twitter_handle column
WHERE is a clause that indicates which rows to update with the new column.
the row with a 4 and the id column is the row that will have the update occur
What type of Statement deletes one or more rows from a table
DELETE
What does the clause DELETE FROM do?
Deletes rows from a Table.
Explain each part of this DELETE FROM statement?
DELETE FROM celebs
WHERE twitter_handle IS NULL;
DELETE FROM is the clause that deletes rows from a table
celebs is the name of the table we want to delete rows from
WHERE is a clause that lets you select which rows to delete. Here all rows with a NULL twitter_handle will be deleted.
IS NULL is a condition that returns true when the value is NULL and false otherwise.
What two clauses are in a DELETE FROM statement?
DELETE FROM ______
WHERE _______
(DELETE FROM and WHERE)
What are Constraints?
Add information about how a column can be used or invoked after specifying the data type for a column.
How are Constraints used?
Used to tell the database to reject inserted data that does not adhere to a certain restriction.
What does this Statement do?
CREATE TABLE celebs (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE,
date_of_birth TEXT NOT NULL,
date_of_death TEXT DEFAULT ‘NOT APPLICABLE’
);
This statement sets constraints on the celebs table.
What are PRIMARY KEY columns?
Uniquely identifies the row(s).
What would result in a constraint violation? What does it prevent?
Attempting to insert a row with an identical value to a row already in the table.
Thus resulting in a constraint violation and not allowing you to insert the new row.
What are UNIQUE columns?
Different value for every row
True or False: A Table can have many different UNIQUE columns.
True
What are NOT NULL columns?
Columns that must have a value.
Attempting to insert a row without a value for NOT NULL column will result in a __________ violation. The new ______ will not be inserted.
Constraint, row
What are DEFAULT columns?
Takes an additional argument that will be the assumed value for an inserted row if the new row does not specify a value for that column.
A statement is a ______ of _______ that the database recognizes as a valid command.
string of characters
