UNIT 1-3 Flashcards
SQL includes commands to create database objects such as tables, indexes, and views, as well as commands to define access rights to those database objects.
data definition language (DDL):
SQL includes commands to insert, update, delete, and retrieve data within the database tables.
data manipulation language (DML):
You merely command what is to be done; you don’t have to worry about how it is to be done.
SQL is a nonprocedural language
a group of database objects—such as tables and indexes—that are related to each other.
Schema
They help distinguish the kinds of data that can be stored or processed inside our database objects.
Data Types
Fixed character length data, 1 to 255 characters
CHAR
Variable character length data,1 to 2,000 characters.
VARCHAR
Numeric data. decimal(9,2)is used to specify numbers with two decimal places and up to nine digits long, including the decimal places. Some RDBMSs permit the use of a MONEY or a CURRENCY data type.
Decimal
Integer values only
INT
Small integer values only
SMALLINT
formats vary. Commonly accepted formats are: ’DD-MON-YYYY’, ’DD-MON-YY’, ’MM/DD/YYYY’, and ’MM/DD/YY’
DATE
a collection of related data held in a table format within a database. It consists of columns and rows.
TABLE
In ___________ and flat file databases, a table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows, the cell being the unit where a row and c.
relational databases
Each _____ type has an associated T-SQL statement format with various implementations.
constraint
A column or combination of columns whose values uniquely identify each row in the table
PRIMARY KEY
A column or combination of columns used to establish and enforce a link between the data in two tables
FOREIGN KEY
Ensures that no duplicate values are entered in specific columns that do not participate in a primary key
UNIQUE
Enforces domain integrity by limiting the values that are accepted by a column
CHECK
Defines column values stored when no value has been assigned
DEFAULT
Designates that a column will accept null values
Nullability
_____ can be used to improve the efficiency of searches and to avoid duplicate column values.
indexesCREATE[UNIQUE] INDEX indexname ON tablename(column1[, column2])
is a function that returns the current system date
getdate()
is a TSQL function that add a particular date interval from a given column or value. Its accepts the following parameters :
dateadd()dateadd ([day type such as year, day, hour, minute etc.], [integer value], [column name or date value])
is a MySQL function that subtracts a particular date interval from a given column or value.
date_subdate_sub([column name or date value], INTERVAL [integer value] [day type such as year, day, hour, minute etc.])
Remember the rule of precedence
- Perform operations within parentheses.2. Perform power operations.3. Perform multiplications and divisions.4. Perform additions and subtractions.
We use ___ if both conditions are true, ___ if at least one of the condition and ___ if we want to negate the result of a conditional expression.
AND, OR, NOT
ANSI-standard SQL allows the use of _______ in conjunction with the WHERE clause
Special Operators
Used to check whether an attribute value is within a range
BETWEENselect * from tbl_food where fd_price between 10 and 100;
Used to check whether an attribute value is null
IS NULLselect * from tbl_food where fd_name is null;
Used to check whether an attribute value matches a given string pattern
LIKE
Used to check whether an attribute value matches any value within a value list
INselect * from tbl_food where fd_name = ‘choco’ or fd_name = ‘mocha’; can be handled more efficiently with:/SQL Server and MySQL/select * from tbl_foodwhere fd_name in (‘choco’,’mocha’); Take note however that if you are using a LARGE list, IN operator tends to be slow.)
Used to check whether a subquery returns any rows
EXISTS
means any single character NOT within a range or a set.
REGEXP [^]
means any single character within a range or a set.
REGEXP[]
The _______ command can also be used to add table constraints.
ALTER TABLEALTER TABLE tablenameADD constraint [ ADD constraint] ;
You could also use the ALTER TABLE command to remove a column or table constraint.
ALTER TABLE tablenameDROP{PRIMARY KEY|COLUMN columnname |CONSTRAINT constraintname}
You can alter an existing table by adding one or more columns.
alter table tbl_productadd p_salecode char(1)
We can change a column definition as long as there is either no data or the existing column fits into the new column definition.
// SQL Serveralter table table_namealter column column_name datatype; // MySQLalter table table_namemodify column column_name datatype;
If we want to change the size of column pt_name in table tbl_part from varchar(500) to varchar(250)
// SQL Serveralter table tbl_partalter column pt_name varchar(250);// MySQLalter table tbl_partmodify column pt_name varchar(250);
Occasionally, you might want to modify a table by deleting a column. Suppose that you want to delete the V_ORDER attribute from the VENDOR table.
alter table tbl_vendordrop column v_order
To define the primary key for the table tbl_part, use the following command:
ALTER TABLE [table_name]ADD PRIMARY KEY ([column_name])ALTER TABLE [table_name]ADD FOREIGN KEY ([column_name]) REFERENCES [main_table] ([column_name]) [optional on delete/update cascade]
The_____ clause is especially useful when the listing order is important to you
ORDER BYselect columnlistfrom tablelist[where conditionlist][order by columnlist [asc|desc]]
SQL’s _____ clause produces a list of only those values that are different from one another
DISTINCTselect distinct v_codefrom tbl_product;
Creates a database schema
CREATE SCHEMA AUTHORIZATION
Creates a new table in the user’s database schema
CREATE TABLE
Ensures that a column will not have null values
NOT NULL
Ensures that a column will not have duplicate values
UNIQUE
Defines a default value for a column (when no value is given)
DEFAULT
Validates data in an attribute
CHECK
Creates an index for a table
CREATE INDEX
Creates a dynamic subset of rows/columns from one or more tables
CREATE VIEW
Modifies a table definition (adds, modifies, or deletes attributes or constraints)
ALTER TABLE
Creates a new table based on a query in the user’s database schema
CREATE TABLE AS
Permanently deletes a table (and its data)
DROP TABLE
Permanently deletes an index
DROP INDEX
Permanently deletes a view
DROP VIEW
COMPARISON OPERATORS
= < > <= >= <>
LOGICAL OPERATORS
AND OR NOT
SPECIAL OPERATORS
BETWEENIS NULLLIKEINEXISTSDISTINCT
AGGREGATE FUNCTIONS
COUNTMINMAXSUMAVG
Restricts the selection of grouped rows based on a condition
HAVING
Orders the selected rows based on one or more attributes
ORDER BY
Modifies an attribute’s values in one or more table’s rows
UPDATE
Restricts the selection of rows based on a conditional expression
WHERE
Groups the selected rows based on one or more attributes
GROUP BY
Deletes one or more rows from a table
DELETE
Permanently saves data changes
COMMIT
Restores data to their original values
ROLLBACK
Checks whether an attribute value matches any value within a value list
IN
Returns the number of rows with non-null values for a given column
COUNT
Checks whether a subquery returns any rows
ECISTS
Limits values to unique values
DISTINCT
Checks whether an attribute value matches any given string pattern
LIKE
