Transcript Intro to SQL

Structured Query Language
SQL
IST359 M005
Yang Wang
[email protected]
342 Hinds
http://blackboard.syr.edu
Acknowledgements and caveat
These slides draw liberally, with permission, from the following
sources:
• IST359 materials by Prof. Michael Fudge Jr.
• Slides from Murach’s SQL Server 2008 book
Caveat (beware): At best, PowerPoint slides are only a pale
limitation of the entirety of a class meeting. In IST359 in
particular, the lectures will cover topics beyond what appears in
these slides. Don’t rely on them as a substitute for attending
class.
Learning Objectives
• Describe and use basic SQL commands
• Explain how and why SQL is used, and
why it’s important
• Compare and contrast DML and DDL
• Use SQL commands to create metadata
structures and perform CRUD operations.
WHAT IS SQL?
A computer programming language
designed for managing data and
structures (metadata) in relational
database management systems.
Brief History of SQL
• 1970– E. Codd develops relational database
concept
• 1974-1979–System R with Sequel (later SQL)
created at IBM Research Lab
• 1979–Oracle markets first relational DB with
SQL
• 1986–ANSI SQL standard released
• 1989, 1992, 1999, 2003, 2007–Major ANSI
standard updates
• Current–SQL is supported by most major
database vendors at 1999, 2003 and 2007
The SQL Server Environment
Server
Instance
Catalog (db)
Schema
Catalog (db)
Schema
Objects
Instance
Catalog (db)
Schema
Objects
Catalog (db)
Schema
In this class we use:
•SQL Server ist-s-students.syr.edu
•Instance 
. (default)
•Catalog 
ist359yournetid#
•Schema 
dbo (default)
SQL Server 2008 R2: ist-s-students
• Other Information:
–
–
–
–
SQL Dialect: T-SQL / Transact-SQL
Case Sensitive Collation?: Yes
System tables used to represent meta data.
Client / Server over TCP/IP Connect to database using:
• host/IP, port number, logon, password
• Client software used to manage data/meta data on server
– Everything can be expressed in SQL!
• Naming Rules for Objects in SQL Server:
–
–
–
–
Up to 128 characters long
Must begin with a letter or _, @, # (cannot be a digit)
Can contain digits, letters, _, .
Spaces can be used but should be avoided.
My Naming Conventions
What
Mike’s Convention
Rationale for use
All object names
Use all lower case letters.
Disambiguation: eg. Employee
vs. employee for example
All object names
Use underscore in place of
SPACE
Avoid the need to place brackets
around identifiers.
employee_phone_number vs.
[employee phone number]
Tables
Pluralize
Should be employees table,
since it contains more than one
employee 
Tables
Qualify with logical schema
Disambiguation of objects
within the same database: eg.
kmart_employees vs.
netflix_employees
Column names
Qualify with table name
Helps define scope of object.
Eg. employee_zipcode .vs
vendor_zipcode
Constraints
pk= primary key
fk=foreign key
u=unique
ck=check
i=index
Disambiguation of constraints,
for example:
ck_vendor_zipcode .vs.
fk_vendor_zipcode
SQL: Language Breakdown
Domain
SQL Commands
Objects
Metadata
(DDL)
CREATE
ALTER
DROP
Tables, functions,
views, procedures, etc
DATA
(DML)
C - INSERT
R - SELECT
U - UPDATE
D - DELETE
Tables (as a target)
Security
(DCL)
GRANT
REVOKE
Tables, functions,
views, procedures, etc.
Transactions
(DTL)
BEGIN TRANS
COMMIT
ROLLBACK
Controls DML
statements
Workflow in SQL Mgmt Studio
1. Type in SQL
2. Highlight it
3. Check it
(For syntax errors)
4. Wreck it
(Execute on Server)
5. Once you execute “the program has run and
the damage is done.” There is no undo, only
redo, meaning if you delete something to get
it back you must start over again.
DDL 1
•
•
•
•
•
•
Create students table w/Defaults
ID
Name
Email
GPA
Year
(Fr, So, Jr, Sr)
• Are they iSchool?
• DOB?
Murach’s SQL Server 2008, C10
© 2008, Mike Murach & Associates, Inc.
Common column attributes
Attribute
NULL|NOT NULL
PRIMARY KEY|UNIQUE
IDENTITY
DEFAULT default_value
SPARSE
Murach’s SQL Server 2008, C10
Description
Indicates whether or not the column
can accept null values. NULL is the
default unless PRIMARY KEY is
specified.
Identifies the primary key or a unique
key for the table. If PRIMARY KEY
is specified, the NULL attribute isn’t
allowed.
Identifies an identity column. Only
one identity column can be created
per table.
Specifies a default value for the
column.
Optimizes storage of null values for
the column. This attribute was
introduced with SQL Server 2008.
© 2008, Mike Murach & Associates, Inc.
DDL2
• Students table
add constraints
• Primary Key
• Unique emails
• Valid GPA’s
Column and table constraints
Constraint
NOT NULL
PRIMARY KEY
UNIQUE
At the column level
Prevents null values
from being stored in
the column.
Requires that each row
in the table have a
unique value in the
column. Null values
are not allowed.
At the table level
n/a
Requires that each row
in the table have a
unique set of values
over one or more
columns. Null values
are not allowed.
Requires that each row Requires that each row
in the table have a
in the table have a
unique value in the
unique set of values
column.
over one or more
columns.
Murach’s SQL Server 2008, C10
© 2008, Mike Murach & Associates, Inc.
Column and table constraints (continued)
Constraint
CHECK
[FOREIGN KEY]
REFERENCES
At the column level
Limits the values for a
column.
Enforces referential
integrity between a
column in the new
table and a column in a
related table.
Murach’s SQL Server 2008, C10
At the table level
Limits the values for
one or more columns.
Enforces referential
integrity between one
or more columns in the
new table and one or
more columns in the
related table.
© 2008, Mike Murach & Associates, Inc.
DML
•
•
•
•
•
Add students
List students
Find one student
Update students
Update one student
The syntax of the INSERT statement
INSERT [INTO] table_name [(column_list)]
[DEFAULT] VALUES (expression_1 [, expression_2]...)
[, (expression_1 [, expression_2]...) ...]
How to insert a single row into a table
 You use the INSERT statement to add a new row to a table.
 In the INSERT clause, you specify the name of the table that you
want to add a row to, along with an optional column list.
 You specify the values to be inserted in the VALUES clause. The
values you specify depend on whether you include a column list.
 If you don’t include a column list: You must code a value for every
column, in the same order as they appear in the table. The only
exception is an identity column, which must be omitted.
Murach’s SQL Server 2008, C10
© 2008, Mike Murach & Associates, Inc.
The basic syntax of the SELECT statement
SELECT select_list
FROM table_source
[WHERE search_condition]
[ORDER BY order_by_list]
The four clauses of the SELECT statement
Clause
SELECT
FROM
WHERE
ORDER BY
Description
Describes the columns that will be included in the
result set.
Names the table from which the query will retrieve
the data.
Specifies the conditions that must be met for a row to
be included in the result set. This clause is optional.
Specifies how the rows in the result set will be sorted.
This clause is optional.
Murach’s SQL Server 2008, C10
© 2008, Mike Murach & Associates, Inc.
The syntax of the UPDATE statement
UPDATE table_name
SET column_name_1 = expression_1
[, column_name_2 = expression_2]...
[FROM table_source [[AS] table_alias]
[WHERE search_condition]
How to perform a basic update operation
 You use the UPDATE statement to modify one or more rows in
the table named in the UPDATE clause.
 You name the columns to be modified and the value to be assigned
to each column in the SET clause.
 You can specify the value for a column by using a literal, an
expression, or the DEFAULT or NULL keyword when
appropriate.
 Warning: If you omit the WHERE clause, all the rows in the table
will be updated.
Murach’s SQL Server 2008, C10
© 2008, Mike Murach & Associates, Inc.
The syntax of the DELETE statement
DELETE [FROM] table_name
[FROM table_source]
[WHERE search_condition]
How to perform a basic delete operation
 You can use the DELETE statement to delete one or more rows
from the table you name in the DELETE clause.
 You specify the conditions that must be met for a row to be deleted
in the WHERE clause.
 You can specify additional criteria for the delete operation in the
FROM clause.
 Warning: If you omit the WHERE clause, all the rows in the table
will be deleted.
Murach’s SQL Server 2008, C10
© 2008, Mike Murach & Associates, Inc.
Lookup table
•
•
•
•
Create table student_years
Add pk to table
Add data to student_years
Alter students add FK
The basic syntax of the ALTER TABLE statement
ALTER TABLE table_name [WITH CHECK|WITH NOCHECK]
{ADD new_column_name data_type [column_attributes] |
DROP COLUMN column_name |
ALTER COLUMN column_name new_data_type [NULL|NOT NULL] |
ADD [CONSTRAINT] new_constraint_definition |
DROP [CONSTRAINT] constraint_name}
How to alter a table
 The ALTER TABLE statement to modifies an existing table.
 You can use this statement to add columns or constraints, drop
columns or constraints, or change the definition of an existing
column, including changing the column’s data type.
 Before SQL Server changes the data type of a column, it checks to
be sure that no data will be lost. If it will, the operation isn’t done.
 You can modify a column to allow null values as long as the
column isn’t defined as the primary key.
Murach’s SQL Server 2008, C10
© 2008, Mike Murach & Associates, Inc.
The syntax of a column-level foreign key
constraint
[FOREIGN KEY] REFERENCES ref_table_name (ref_column_name)
[ON DELETE {CASCADE|NO ACTION}]
[ON UPDATE {CASCADE|NO ACTION}]
The syntax of a table-level foreign key constraint
FOREIGN KEY (column_name_1 [, column_name_2]...)
REFERENCES ref_table_name (ref_column_name_1
[, ref_column_name_2]...)
[ON DELETE {CASCADE|NO ACTION}]
[ON UPDATE {CASCADE|NO ACTION}]
Murach’s SQL Server 2008, C10
© 2008, Mike Murach & Associates, Inc.