Home
/
SQL

SQL Data mods

Data mods

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

Related Snippets