use pubs
SET QUOTED_IDENTIFIER OFF
SET ANSI_DEFAULTS OFF
go
if exists (SELECT * FROM sysobjects where name='publishers2' and type='U')
DROP TABLE publishers2
go
CREATE TABLE publishers2
(
pub_id int NOT NULL PRIMARY KEY IDENTITY,
pub_name varchar(40) NULL DEFAULT ('Anonymous'),
city varchar(20) NULL,
state char(2) NULL,
country varchar(30) NOT NULL DEFAULT('USA')
)
GO
INSERT publishers2 VALUES ('AAA Publishing', 'Vancouver', 'BC',
'Canada')
INSERT INTO publishers2 VALUES ('Best Publishing', 'Mexico City',
NULL, 'Mexico')
INSERT INTO publishers2 (pub_name, city, state, country)
VALUES ('Complete Publishing', 'Washington', 'DC', 'United States')
INSERT publishers2 (state, city) VALUES ('WA', 'Poulsbo')
INSERT publishers2 VALUES (NULL, NULL, NULL, DEFAULT)
INSERT publishers2 VALUES (DEFAULT, NULL, 'WA', DEFAULT)
INSERT publishers2 VALUES (NULL, DEFAULT, DEFAULT, DEFAULT)
INSERT publishers2 DEFAULT VALUES
GO
SELECT * FROM publishers2
go
if exists (SELECT * FROM sysobjects where name='mytable' and type='U')
DROP TABLE mytable
go
CREATE TABLE mytable
(
int_val int,
smallint_val smallint,
numeric_val numeric(8,2),
tiny_const tinyint,
float_val float,
date_val datetime,
char_strng char(10)
)
GO
DECLARE @myvar1 numeric(8,2)
SELECT @myvar1=65.45
INSERT mytable (int_val, smallint_val, numeric_val, tiny_const,
float_val, date_val, char_strng)
VALUES
(OBJECT_ID('mytable'), @@spid, @myvar1 /
10.0, 5, SQRT(144), GETDATE(), REPLICATE('A',3)+REPLICATE('B',3))
SELECT * FROM mytable
go
CREATE TABLE #authors
(
au_id int PRIMARY KEY,
au_fullname varchar(60) NOT NULL,
date_entered smalldatetime NOT NULL,
area_code char(3) NOT NULL,
state char(2) NOT NULL
)
GO
INSERT INTO #authors
SELECT
CONVERT(int, SUBSTRING(au_id, 1, 3)+SUBSTRING(au_id, 5, 2)
+SUBSTRING(au_id, 8, 4)),
au_lname+', '+au_fname,
CONVERT(varchar, GETDATE(), 102),
CONVERT(char(3), phone),
ISNULL(state, 'WA')
FROM authors
SELECT * FROM #authors
DROP TABLE #authors
go
CREATE TABLE #config_out
(
name_col varchar(50),
minval int,
maxval int,
configval int,
runval int
)
INSERT #config_out
EXEC sp_configure
select * from #config_out
drop table #config_out
go
/* Populate #authors with the same rows as before, but use SELECT INTO
** instead of INSERT to copy the rows.
*/
SELECT
CONVERT(int, SUBSTRING(au_id, 1, 3)+SUBSTRING(au_id, 5, 2)
+SUBSTRING(au_id, 8, 4)) AS au_id,
au_lname+', '+au_fname AS au_fullname,
CONVERT(varchar, GETDATE(), 102) AS date_entered,
CONVERT(char(3), phone) AS area_code,
ISNULL(state, 'WA') AS state
INTO #authors
FROM authors
SELECT @@ROWCOUNT
go
SELECT * FROM #authors
go
DROP TABLE #authors
go
BEGIN TRAN -- So that the changes can be undone
/* UPDATE EXAMPLES */
-- Change a specific employee's last name after his marriage
UPDATE employee
SET lname='Thomas-Kemper'
WHERE emp_id='GHT50241M'
-- Raise the price of every title by 12%
-- no WHERE clause so it affects every row in the table
UPDATE titles
SET price=price * 1.12
-- Publisher 1389 was sold, changing its name and location.
-- All the data in other tables relating to pub_id 1389 is
-- still valid; only the name of the publisher and its
-- location have changed.
UPDATE publishers
SET pub_name='O Canada Publications',
city='Victoria',
state='BC',
country='Canada'
WHERE pub_id='1389'
-- Change the phone number of authors living in Gary, IN.
-- back to the DEFAULT value
UPDATE authors
SET phone=DEFAULT
WHERE city='Gary' AND state='IN'
ROLLBACK TRAN
go
/* DELETE EXAMPLES */
BEGIN TRAN
delete discounts
-- Deletes every row from the discounts table, but does not delete the table itself.
-- An empty table remains.
DELETE FROM sales WHERE qty > 5
-- Deletes those rows from the sales table which have value for qty of 6 or more.
delete from WA_stores
-- Attempts to delete all the rows qualified by he WA_stores view, which would in reality
-- delete those rows from the stores table that have state of WA. This
-- delete would fail if the foreign key references were not suspended.
ROLLBACK TRAN
go
if exists (SELECT * FROM sysobjects where name='titles_and_authors' and type='V')
DROP VIEW titles_and_authors
go
CREATE VIEW titles_and_authors
AS
(
SELECT A.au_id, A.au_lname, T.title_id, T.title
FROM
authors AS A
FULL OUTER JOIN titleauthor AS TA ON (A.au_id=TA.au_id)
FULL OUTER JOIN titles AS T ON (TA.title_id=T.title_id)
)
GO
SELECT * FROM titles_and_authors
BEGIN TRAN
-- This update is ok because we only update a column from the underlying authors table
UPDATE titles_and_authors
SET au_lname='DeFrance-Macy'
WHERE au_id='722-51-5454'
GO
-- This update fails with error 4405 because two tables from a view
-- cannot be updated in the same statement
UPDATE titles_and_authors
SET au_lname='DeFrance-Macy', title='The Gourmet Microwave Cookbook'
WHERE au_id='722-51-5454' AND title_id='MC3021'
GO
ALTER TABLE titleauthor -- This disables the constraint
NOCHECK CONSTRAINT ALL
GO
-- With constraint now disabled, the following update succeeds:
-- But the number of rows returned from the view changes.
UPDATE titles_and_authors
SET au_id='111-22-3333'
WHERE au_id='409-56-7008'
GO
SELECT * FROM titles_and_authors
ROLLBACK TRAN
-- Re-enable constraints. Note this does not check their validity
ALTER TABLE titleauthor
CHECK CONSTRAINT ALL
GO
-- Do a "Dummy Update" to check the constraints
UPDATE titleauthor
SET au_id=au_id, title_id=title_id
SELECT A.au_id, TA.au_id
FROM authors AS A RIGHT OUTER JOIN titleauthor
AS TA ON (A.au_id=TA.au_id)
WHERE A.au_id IS NULL
if exists (SELECT * FROM sysobjects where name='WA_stores' and type='V')
DROP VIEW WA_stores
go
-- The view that does not have CHECK OPTION lets rows get updated and disappear
CREATE VIEW WA_stores AS
SELECT * FROM stores
WHERE state='WA'
go
BEGIN TRAN
SELECT stor_id,stor_name,state
FROM WA_stores
UPDATE WA_stores
SET state='CA'
SELECT stor_id,stor_name,state
FROM WA_stores
ROLLBACK TRAN
go
if exists (SELECT * FROM sysobjects where name='WA_stores' and type='V')
DROP VIEW WA_stores
go
--Creating the view WITH CHECK OPTION prevents disappearing rows.
CREATE VIEW WA_stores AS
SELECT * FROM stores
WHERE state='WA'
WITH CHECK OPTION
GO
BEGIN TRAN
SELECT stor_id, stor_name, state
FROM WA_stores
UPDATE WA_stores
SET state='CA'
SELECT stor_id,stor_name,state
FROM WA_stores
