SQL
Structured Query Language
First used in System R by IBM
This is a standard language and not proprietary to RDBMS products
ANSI and ISO define the
... [Show More] standard
Standard first published in 1986 (SQL/86)
Two major standards today are SQL-92 and SQL-99
SQL Advantages:
Reduced training costs
Productivity
Portability
Longevity
Reduce dependence on single vendor
Cross-system communication
SQL Disadvantages
Can reduce creativity and innovation
Difficult to change (vested interest)
Using features from specific vendors reduce benefits of the standard
Potential to consume excessive computing resources if you are not careful
Potential for damage to database if you are not careful
The SQL Environment
Everything is contained in a schema
Schema is a collection of related:
Tables
Views
Domains
Constraints
Roles
Triggers
and other items
SQL Command Categories
Data Manipulation Language (DML)
CRUD
Data Definition Language (DDL)
Used to create, modify, and remove tables
Data Control Language (DCL)
Assist the database administrator (DBA) in controlling user privileges and transactions in the database
Our focus is on DML
DDL
Building, modifying, and removing base tables
Defining dynamic views
Constructing materialized views
Creating data integrity constraints
Create indexes
DML
Commands used to perform CRUD operations
These functions may occur within a single table or across several tables
DML
INSERT
UPDATE
DELETE
SELECT
Insert Statement Notation
INSERT INTO table_name
VALUES (value_list_for_all_columns);
INSERT INTO table_name (column_list)
VALUES (value_list_for_column_list);
INSERT INTO table_name
SELECT column_list FROM table_name;
Insert Statement Notation
UPDATE table_name
SET column_name = value
[, column_name = value]
WHERE conditional_expression
Delete Statement Notation
DELETE FROM table_name WHERE conditional_expression;
DELETE FROM table_name;
Basic SELECT concepts
SELECT: List the column(s) from table(s) to be included in the result table
FROM: Identifies table(s) from which column(s) will be drawn to appear in the result. Incorporates tables/views needed to 'join' tables to process the query
WHERE: Conditional parameters for row selection within a single table or across tables/views in join operations
Select Statement Notation
SELECT [ALL DISTINCT] column_list
FROM table_list
WHERE conditional_expresstion
GROUP BY grouping_column_list
HAVING conditional_expression
ORDER BY order_column_list;
What do you get from SELECT?
The SELECT statement is used to retrieve data from the database
Data is returned as a result table (aka set of rows or result set)
Often denormalized data is returned
Aliases
You can refer to existing tables, columns, expressions via aliases
Syntax is "AS new_name"
Example:
SELECT p.f_name || p.l_name AS NAME
FROM PATIENT P;
Expressions
You can also perform calculations and manipulations of data in SQL
May use mathematical expressions or stored functions
Example: SELECT COUNT(*) FROM PATIENT
Some stored procedures: COUNT, MAX, MIN, SUM, AVG
Comparison Operators
Allows the comparison of two data values
Typical comparison operators
= Equal
>Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
!= Not equal to
Wildcards
The asterisk (*) in SQL indicates the selection of all columns in a SELECT statements
The percent symbol (%) is used to match any number of characters in a text value
The underscore (_) represents only one character place
LIKE
The LIKE comparator is specific to text values
Represent equality for text
Often used with wildcards to perform matching
Example: SELECT l_name FROM patient WHERE l_name LIKE '*smith';
Value is case-sensitive [Show Less]