SQL Flashcards
Structured Query Language
Simple programming language with less than 100 words syntax which is used to manipulate data stored in a database. It gets its name from the specific order in which queries and subqueries are given and executed.
INSERT
Adding table rows. Can violate key constraints, including:
Primary key entered that already exists
Primary key is null
Foreign key entered that does not correspond to a primary key in another table
COMMIT
Saving table changes
SELECT
Listing table rows
UPDATE
Updating table rows, modify data in table using UPDATE SET WHERE
Changes specific data within a table/database (cr: ALTER)
ROLLBACK
Restoring table contents to state prior to changes (since last COMMIT)
DELETE
Deleting table rows
INSERT + SELECT
Inserting table rows with a select subquery, copying rows from one table into another
NULL
Used with INSERT when a cell value is missing
FROM
applies query to specific table within database
WHERE
used to apply conditional restrictions
=
equal to
>
greater than
less than
> =
greater than or equal to
<=
less than or equal to
<> or !=
not equal to
SELECT
FROM
WHERE
Select with: equality comparisons, not equal to, restriction, comparison on characters, date comparison, statement with a computed column, statement with computed column and an alias,
Arithmetic Operators: The Rule of Precedence(which operator goes first ?)
Perform operations within parentheses
Perform power operations
Perform multiplications and divisions
Perform additions and subtractions
OR/AND/NOT
Logical operator. Used in conditional expressions
BETWEEN
special operator. checks whether an attribute value is within a range
IS NULL
special operator. checks whether an attribute value is null
LIKE
special operator. checks whether an attribute value matches a given string pattern
IN
special operator. checks whether an attribute value matches any given value within a list
EXISTS
special operator. checks whether a subquery returns any rows
data definition language
used to create new tables
does not deal with data
in some DBMSs SQL is not needed to create tables
GROUP BY
Groups the selected rows based on one or more attributes.
HAVING
Restricts the selection of grouped rows based on a condition
ORDER BY
Orders the selected rows based on one or more attributes
DISTINCT
limits values to unique values
COUNT
Aggregate function. Returns the number of rows with non-null values for a given column.
MIN
Aggregate function. Returns the minimum attribute value found in a given column
MAX
Aggregate function. Returns the maximum attribute value found in a given column
SUM
Aggregate function. Returns the sum of all values for a given column
AVG
Aggregate function. Returns the average of all the values forgiven column
CREATE SCHEMA AUTHORISATION
Create a database schema
CREATE TABLE
Creates a new table in the user’s database schema
NOT NULL
Ensures that a column will not have null values
UNIQUE
Ensures that a column will not have duplicate values
PRIMARY KEY
Defines a primary key for a table
FOREIGN KEY
Defines a foreign key for a table
DEFAULT
Defines a default value for a column (when no value is given)
CHECK
Validate the data in an attribute
CREATE INDEX
Create an index for a table
CREATE VIEW
Create a dynamic subset of rows and columns from one or more tables
ALTER TABLE
Modifies a table’s definition (ads modifies or delete attributes or constraints)
changes general structure of data (cr: UPDATE)
CREATE TABLE AS
Create a new table based on a query in the user’s database schema
DROP TABLE
Permanently deletes a table (and it’s data)
DROP INDEX
Permanently deletes an index
DROP VIEW
Permanently deletes a view
order #1
INSERT COMMIT SELECT UPDATE ROLLBACK DELETE INSERT + SELECT
use brackets if you want
values checked in a specific order otherwise it’ll be done left to right
if you need to check the same info in different tables (e.g. customer codes) use
T1.CUS_CODE = T2.CUS_CODE structure
when copying parts of a table
create new part table then use INSERT INTO command
order #2
SELECT FROM WHERE GROUP BY HAVING ORDER BY
when you want information from multiple tables
you must use a join operator
