ITEC36 FINALS Flashcards
It is a computer language for storing, manipulating and retrieving data stored in a relational database.
SQL
It the standard language for Relational Database System.
SQL
It consists of the SQL commands that can be used to define the database schema.
DDL or Data Manipulation Language
Creates a new table, a view of a table, or other object in the database.
Create
Modifies an existing database object, such as a table.
Alter
Deletes an entire table, a view of a table or other objects in the database.
Drop
It deals with the manipulation of data present in the database.
DML or Data Manipulation Language
Retrieves certain records from one or more tables.
Select
Creates a record.
Insert
Modifies records.
Update
Delete records.
Delete
It deals with the rights, permissions and other controls of the database system.
DCL or Data Control Language
Gives a privilege to user.
Grant
Takes back privileges granted from user.
Revoke
It deals with the transaction within the database.
TCL or Transaction Control Language
commits a Transaction.
Commit
rollbacks a transaction in case of any error occurs.
Rollback
sets a savepoint within a transaction.
Savepoint
specify characteristics for the transaction.
Set Transaction
It is an attribute that specifies the type of data of any object.
Data type
Specifies character type data of length n where n could be any value from 0 to 255.
CHAR (n)
Specifies character type data of length ānā where n could be any value from 0 to 65535
VARCHAR (n)
specifies an integer value.
INT
Holds numbers with decimal points. Each _____ value occupies 4 bytes.
FLOAT
is used for dates in āYYYY-MM-DDā format. YYYY is the 4 digit year, MM is the 2 digit month and DD is the 2 digit date. The supported range is ā1000-01-01ā to ā9999-12-31ā.
DATE
These are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table.
Constraints
Ensures that a column cannot have a NULL
value.
NOT NULL
Provides a default value for a column when
none is specified.
DEFAULT
Ensures that all the values in a column are
different.
UNIQUE
Uniquely identifies each row/record in a
database table.
PRIMARY key
Uniquely identifies a row/record in any another database table.
FOREIGN key
It is used to create a new SQL database.
SQL CREATE DATABASE
It is used to drop an existing database in SQL schema.
SQL DROP DATABASE
is the keyword telling the database system what you want to do.
CREATE TABLE
is used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for that table.
DROP TABLE
These are sets of instruction written in a language that can be understood by the database system.
Queries
What the purpose of the query is.
Action
Which table or tables the action will be performed on.
Location
What columns rows and data will be affected.
WHERE
It is used to fetch the data from a database table which returns this data in the form of a result table.
SQL SELECT
specifies the columns to be displayed.
SELECT Clause
specifies the table containing the columns that are listed in the SELECT Clause
FROM Clause
It can be used to select all the available columns and records from a table.
Asterisk (*)
It is used to specify a condition while fetching the data from a single table or by joining with multiple tables.
SQL WHERE Clause
It is used to modify the existing records in a table.
SQL UPDATE Query
It is used to delete the existing records from a table.
SQL DELETE Query
It can perform arithmetical operations on numeric operands involved.
Arithmetic Operators
It is used to override the default precedence or to clarify the statement.
Parenthesis ()
Renames a column heading
Alias
These are used in conditions that compare one expression to another or expression.
Comparison Conditions
Search for a pattern.
LIKE
If you know to exact value you want to return for at least one of the columns.
IN
Contain NULL value.
IS NULL
Return columns with value.
IS NOT NULL
It combines the result of two component conditions to produce a single result based on those conditions, or it inverts the result of a single condition.
Logical Conditions
A keyword used to sort the result-set in ascending or descending order.
ORDER BY
It is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies.
Normalization
