Chapter 7 Notes

Slippery Rock University

Dr. Deborah Whitfield

Chapter 7 -SQL for DB construction

SQL, DDL, DML, and Joins

DDL
data definition language
Create table
alter table
drop table
DML
Data manipulation language
Insert
Update
Delete
Joins
different syntax
new join -- outer join
CREATE TABLE tablename (Slide 7
column name
data type
not null/null
identity
constraint (PRIMARY KEY not null, UNIQUE, FOREIGN KEY, CHECK)
Create Relationships (Slide 7-10)
Constraints (Slide 7-13)
ALTER stmt (Slide 7-14)
Modify table after creation
ADD CONSTRAINT
Add column
Drop column
Remove Tables
drop constraints using alter
then DROP TABLE tablename(slide 7-17)
Slides

DML

INSERT (Slide 7-18)
INSERT INTO table (columnNameList) VALUES (CorrespondingValues)
Can use select to insert data from another table
INSERT INTO table (ColumnNameList) SELECT ColumnList FROM otherTable
Slides

UPDATE
UPDATE table SET columnName= value WHERE cond
DELETE
DELETE FROM table WHERE cond
no cond -- delete all rows
Part 2 Slides

More Joins

Previously,
SELECT * FROM tbl1, tbl2 WHERE tbl1.col = tbl2.col
The word JOIN could also be used:
SELECT * FROM tbl1 JOIN tbl2 ON tbl1.col = tbl2.col
Replace , with JOIN and WHERE with ON
Table aliases could also be used. Recall the AS keyword used to give computed columns a name
SELECT * FROM long_table_name AS Tb1 JOIN another_long_table_name as Tb2
ON Tb1.col = Tb2.col
Part 2 Slides

OUTER JOINS
Outer joins include all rows of one table even if there isn't a match in the other table
There are left outer joins and right outer joins
Consider your project where you want all the name info about all of the people in TBL_NAMES. What if one of the people didn't have any entries in the additional info table? Inner joins that you know, won't get data unless there is a match between the two IDs.
When using multiple tables, it is necessary to qualify the col names
SELECT * FROM TBL_NAMES LEFT JOIN TBL_ADDITIONAL_INFO ON TBL_NAMES.ID = TBL_ADDITIONAL_INFO.ID
SELECT * FROM TBL_NAMES AS Nm LEFT JOIN TBL_ADDITIONAL_INFO AS Addl ON Nm.ID = Addl.ID
Change LEFT to RIGHT, what happens?
Use a RIGHT join to get all the data from TBL_NAMES
SELECT * FROM TBL_NAMES AS Nm LEFT JOIN TBL_ADDITIONAL_INFO AS Addl ON Nm.ID = Addl.ID
See examples on Power Point slides
Part 2 Slides

SQL views

SQL view is a virtual table created for easier queries
Created using a SELECT, but cannot contain ORDER BY (sometimes permitted in SQL Server)
CREATE VIEW Name_of_View AS
SELECT Last_Name from TBL_NAMES
Now, can use "Name_of_View" as though it was a TABLE in any SQL Query.
SELECT * FROM Name_of_View ORDER BY Last_Name
Used to hide columns and rows of data from the user
Views are used to display results of computations
Frequently used to hide complicate SQL Syntax
Some views can be updated

Embedding SQL in code

Can be embedded in applications, stored procedures and triggers
See Chapter 12...