Ch. 7: SQL

SQL constraints

NOT NULL, UNIQUE, DEFAULT, CHECK

copying parts of tables

SQL permits copying contents of selected table columns data need not be reentered manually into newly created table(s) table structure is created rows are added to new table using rows from another table

alias

alternate name given to a column or table in any SQL statement to improve the readability computed column, an alias, and date arithmetic can be used in a single query

composite index

based on two or more attributes prevents data duplication

changing column's data characteristics

changes in column's characteristics are permitted if changes do not alter the existing data type ALTER TABLE tablename MODIFY (columnname(characteristic));

Data Manipulation Language (DML)

commands to insert, update, delete, and retrieve data within the database tables

tasks to be completed before using a new RDBMS

create database structure authentication

rule of precedence

establish the order in which computations are completed PEMDAS (parentheses, exponents, multiplication/division, addition/subtraction)

Data Definition Language (DDL)

includes commands to create database objects such as tables, indexes, and views, as well as commands to define access rights to those database objects

database schema

logical group of database objects related to each other

cascading order sequence

multilevel ordered sequence created by listing several attributes after the ORDER BY clause

common SQL data types

numeric, character, date need to know data type when doing queries

data manipulation

once you have the data, how can you work the data to get valuable information? create the table structure, then insert the records

joining database tables

performed when data are retrieved from more than one table at a time equality comparison between foreign key and primary key of related tables

Primary Key and Foreign Key

primary key attributes contain both a NOT NULL and a UNIQUE specification RDBMS will automatically enforce referential integrity for foreign keys

authentication

process DBMS uses to verify that only registered users access the data required for the creation tables user should log on to RDBMS using user ID and password created by database administrator

subquery

query embedded/nested inside another query used when you don't know the value you need

recursive query

table is joined to itself using alias use aliases to differentiate the table from itself

creating table structures

use one line per column definition use spaces to line up attribute characteristics and constraints table & attribute names are capitalized features of table creating command sequence (NOT NULL and/or UNIQUE)

special operators

used in conditional expressions

INSERT INTO tablename SELECT columnlist FROM tablename

used to add multiple rows using another table as source

aggregate functions

used with SELECT to return mathematical summaries of columns

SQL indexes

when primary key is declared, DBMS automatically creates unique index

EXISTS

DML - special operator checks whether a subquery returns any rows

IS NULL

DML - special operator checks whether an attribute value is null

BETWEEN

DML - special operator checks whether an attribute value is within a range

LIKE

DML - special operator checks whether an attribute value matches a given string pattern

IN

DML - special operator checks whether an attribute value matches any value within a value list

DISTINCT

DML - special operator limits values to unique values produces list of values that are unique

Categories of SQL Function

Data Definition Language (DDL) Data Manipulation Language (DML)

changing column's data type

ALTER TABLE tablename MODIFY (columnname(datatype)); some RDBMSs do not permit changes to data types unless column is empty

CREATE SCHEMA AUTHORIZATION

DDL creates a database schema

CREATE VIEW

DDL creates a dynamic subset of rows and columns from one or more tables

CREATE TABLE AS

DDL creates a new table based on a query in the user's database schema

CREATE TABLE

DDL creates a new table in the user's database schema

CREATE INDEX

DDL creates an index for a table

DEFAULT

DDL defines a default value for a column (when no value is given)

FOREIGN KEY

DDL defines a foreign key for a table

PRIMARY KEY

DDL defines a primary key for a table

UNIQUE

DDL ensures that a column will not have duplicate values

NOT NULL

DDL ensures that a column will not have null values

ALTER TABLE

DDL modifies a table's definition (adds, modifies, or deletes attributes or constraints) makes changes in table structure keywords used with this command: ADD, MODIFY, DROP

DROP TABLE

DDL permanently deletes a table (and its data) can only drop a table if it is not the one side of any relationship

DROP VIEW

DDL permanently deletes a view

DROP INDEX

DDL permanently deletes an index

CHECK

DDL validates data in an attribute

FROM

DML "what table(s) will be used?" required

comparison operators

DML =, <, >, <=, >=, <> used in conditional expressions adds conditional restrictions on selected table contents

logical operators

DML AND/OR/NOT used in conditional expressions

DELETE

DML deletes one or more rows from a table

GROUP BY

DML groups the selected rows based on one or more attributes

INSERT

DML inserts row(s) into a table

UPDATE

DML modifies an attribute's values in one or more table's rows

ORDER BY

DML orders the selected rows based on one or more attributes ascending is default; you must order by something that is in the SELECT line

COMMIT

DML permanently saves data changes

ROLLBACK

DML restores data to their original values

HAVING

DML restricts the selection of grouped rows based on a condition applied to output of GROUP BY operation

WHERE

DML restricts the selection of rows based on a conditional expression "condition(s) such as >, <, =, <>" adds conditional restrictions to the SELECT statement

SELECT

DML selects attributes from rows in one or more tables or views "what attributes do you want to see?" required used to select partial table contents by placing restrictions on the rows

AVG

DML - aggregate function returns the average of all values for a given column

MAX

DML - aggregate function returns the maximum attribute value found in a given column

MIN

DML - aggregate function returns the minimum attribute value found in a given column

COUNT

DML - aggregate function returns the number of rows with non-null values for a given column

SUM

DML - aggregate function returns the sum of all values for a given column

joining tables with an alias

alias identifies the source table from which data are taken any legal table name can be used as alias add alias after table name in FROM clause