Section 1
Preview this deck
Structured Query Language (SQL) §
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (58)
Section 1
(50 cards)
Structured Query Language (SQL) §
Nonprocedural language with basic command vocabulary set of less than 100 words § Differences in SQL dialects are minor
SELECT command
Acts as a subquery and is executed first
Subquery
Query embedded/nested inside another query
Command §
CREATE SCHEMA AUTHORIZATION {creator}; § Seldom used directly
Tasks to be Completed Before Using a New RDBMS Create database structure
RDBMS creates physical files that will hold database § Differs from one RDBMS to another §
Optional WHERE clause §
Adds conditional restrictions to the SELECT statement
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
Syntax §
INSERT INTO tablename SELECT columnlist FROM tablename §
Comparison Operators §
Add conditional restrictions on selected table contents § Used on: § Character attributes § Dates
ALTER TABLE command §
Followed by a keyword that produces the specific change one wants to make § Options include ADD, MODIFY, and DROP §
Selecting Rows Using Conditional Restrictions
Used to select partial table contents by placing restrictions on the rows §
Following syntax enables to specify which rows to select
SELECT columnlist § FROM tablelist § [WHERE conditionlist ];
Advanced Data Updates §
UPDATE command updates only data in existing rows § If a relationship is established between entries and existing columns, the relationship can assign values to appropriate slots § Arithmetic operators are useful in data updates § In Oracle, ROLLBACK command undoes changes made by last two UPDATE statements
WHERE condition •
Specifies the rows to be selected
Special Operators
BETWEEN • Checks whether attribute value is within a range IS NULL • Checks whether attribute value is null LIKE • Checks whether attribute value matches given string pattern IN • Checks whether attribute value matches any value within a value list EXISTS • Checks if subquery returns any rows
COMMIT: Command to save changes •
Syntax - COMMIT [WORK]; • Ensures database update integrity
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
Deleting a Table from the Database
Can drop a table only if it is not the one side of any relationship § RDBMS generates a foreign key integrity violation error message if the table is dropped
Features of table creating command sequence §
NOT NULL specification § UNIQUE specification §
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 § Command sequence ends with semicolon §
ANSI SQL allows use of following clauses to cover CASCADE, SET NULL, or SET DEFAULT §
ON DELETE and ON UPDATE
Advanced Data Definition Commands §
Keywords use with the command § ADD - Adds a column § MODIFY - Changes column characteristics § DROP - Deletes a column § Used to: § Add table constraints § Remove table constraints
Syntax to delete an index § DROP INDEX
indexname;
Syntax to create SQL indexes § CREATE INDEX
indexname ON tablename(); §
Adding Primary and Foreign Key Designations §
Syntax to add or modify columns § ALTER TABLE tablename § {ADD | MODIFY} ( columnname datatype [ {ADD | MODIFY} columnname datatype ] ) ; § ALTER TABLE tablename § ADD constraint [ ADD constraint ] ;
Changing Column's Data Characteristics §
Use ALTER to change data characteristics § Changes in column's characteristics are permitted if changes do not alter the existing data type § Syntax § ALTER TABLE tablename MODIFY (columnname(characterstic)) ;
ROLLBACK: Command to restore the database •
Syntax - ROLLBACK; • Undoes the changes since last COMMIT command
Comparison Operators: Computed Columns and Column Aliases §
SQL accepts any valid expressions/formulas in the computed columns § Computed column, an alias, and date arithmetic can be used in a single query
Adding a column §
Use ALTER and ADD § Do not include the NOT NULL clause for new column §
Alias
Alternate name given to a column or table in any SQL statement to improve the readability
SQL Indexes §
When primary key is declared, DBMS automatically creates unique index §
Arithmetic operators §
Perform: § Operations within parentheses § Power operations § Multiplications and divisions § Additions and subtractions
Creating Table Structures §
Use one line per column (attribute) definition § Use spaces to line up attribute characteristics and constraints § Table and attribute names are capitalized §
§
Inserting Table Rows with a SELECT Subquery
Used to add multiple rows using another table as source §
Dropping a column §
Use ALTER and DROP § Some RDBMSs impose restrictions on the deletion of an attribute
Common SQL Data Types
Numeric NUMBER(L,D) or NUMERIC(L,D) Character CHAR(L) • VARCHAR(L) or VARCHAR2(L) •DATE Date
Data Manipulation Commands INSERT: Command to insert data into table •
Syntax - INSERT INTO tablename VALUES(); • Used to add table rows with NULL and NOT NULL attributes
SQL Constraints •
Ensures that column does not accept nulls NOT NULL • Ensures that all values in column are unique UNIQUE • Assigns value to attribute when a new row is added to table DEFAULT • Validates data when attribute value is entered CHECK
Categories of SQL function §
Data definition language (DDL) § Data manipulation language (DML) §
Syntax to create table §
CREATE TABLE tablename();
UPDATE: Command to modify data •
Syntax - UPDATE tablename SET columnname = expression [, columnname = expression ] [WHERE conditionlist ];
Composite index: §
Is based on two or more attributes § Prevents data duplication §
Changing Column's Data Type §
ALTER can be used to change data type § Some RDBMSs do not permit changes to data types unless column is empty § Syntax - § ALTER TABLE tablename MODIFY (columnname(datatype)) ;
DROP TABLE
Deletes table from database § Syntax - DROP TABLE tablename ;
SELECT: Command to list the contents •
Syntax - SELECT columnlist FROM tablename ; • Wildcard character (*): Substitute for other characters/command
The Rule of Precedence
Establish the order in which computations are completed
ALTER TABLE
command: To make changes in the table structure
DELETE: Command to delete •
Syntax - DELETE FROM tablename • [WHERE conditionlist ];
The Database Schema §
Logical group of database objects related to each other §
Section 2
(8 cards)