eNotebook

Escape key should Cancel all dialog boxes
"Database Schema Generation Options" default button is "Help." Should be "OK" so can Enter through the box instead of clicking.

Issues Submitted

12-15-04 Optional End-of-Generation DlgBox "Done! ... Open the database schema file now?"

Other

Tuesday, December 14, 2004

Thursday, December 09, 2004

TY SQL Server 2000 in 21 Days Stuff 2

(The HTML in TY SQL Server 2000 in 21 Days Stuff was messing up.)
*Code Begin*

*Code End*
The End

Tuesday, December 07, 2004

TY SQL Server 2000 in 21 Days Stuff

EXEC sp_helpdb ty_croak

create table test
(
bytEmp_id tinyint IDENTITY NOT NULL,
strFName char(15),
strLName char(20) NOT NULL,
strAdd1 varchar(30),
strAdd2 varchar(30),
strCity varchar(30),
strState char(2),
strZipCode char(10),
dtStartDate datetime
)

/* select * from employee */
/* select 'Employee ID:' [Emp ID String], emp_id as [Employee ID], lname as 'Last Name' from employee order by lname */
select pub_id from employee order by pub_id
select distinct pub_id from employee order by pub_id
select count(distinct pub_id) [Count of Distinct pub_id] from employee
select lname +', ' + substring(fname, 1, 1) + '.' as name, emp_id as [Employee ID],
'Has been employed for ', datediff(year, hire_date, getdate()), ' years.', pub_id
from employee
where pub_id not IN ('0877', '9999')
order by pub_id, lname
/* select sqrt(2) as [Square Root]
select sqrt($2) as [Square Root] */

select title_id, title from titles where title like '%comp%'
select count(title_id) from titles where title like '%comp%'

select au_id, au_lname, au_fname
from authors
where au_lname like '[bm]%'

select * from titles

select title_id, title from titles where ytd_sales is null

select avg(ytd_sales) from titles
select avg(ytd_sales) from titles where ytd_sales is not null

select title_id, au_id from titleauthor order by title_id

select title_id, count(title_id) as NumAuthors
from titleauthor
group by title_id
having count(title_id) > 1

select * /*pub_id, pub_name, pr_info*/
from publishers

select * from pub_info

select publishers.pub_id, pub_name, pub_info.pr_info
from publishers
inner join pub_info on publishers.pub_id = pub_info.pub_id
select publishers.*, pub_info.* /*pub_id, pub_name, pub_info.pr_info*/
from publishers
inner join pub_info on publishers.pub_id = pub_info.pub_id

select * from titles order by title_id
select * from sales order by title_id

/*select titles.title_id, titles.title, sales.qty
from titles left outer join sales
on titles.title_id = sales.title_id */
select t.title_id, t.title, s.qty
from titles t left outer join sales s
on t.title_id = s.title_id

select * from authors order by city, zip

select a1.au_id, a1.au_lname, a1.au_fname, a1.city, a1.zip, a2.au_id, a2.au_lname, a2.au_fname, a2.city, a2.zip
from authors a1
inner join authors a2
on a1.city = a2.city and a1.zip = a2.zip
where a1.au_id <>
order by a1.city, a1.zip

select * from authors order by state
select * from stores order by state
select distinct state from stores

select distinct au_fname, au_lname, state
from authors
where state in (select state from stores)

/*select distinct au_fname, au_lname, state
from authors
where exists (select * from stores where state = authors.state) */

select a1.au_fname, a1.au_lname, a1.city, a1.zip
from authors a1
where a1.zip in
(select zip from authors a2
where a1.city=a2.city and a1.au_id <> a2.au_id)
order by a1.city, a1.zip

select title_id, title, price
into #tmpTitles
from titles
go
select * from #tmpTitles
go

use pubs
go
exec sp_dboption 'pubs', 'Select into/bulkcopy', true
select * into tmpPublishers from publishers
select * into tmpStores from stores
select * into tmpTitles from titles
select * into tmpSales from sales
go
exec sp_dboption 'pubs', 'Select into/bulkcopy', false
go

/* p. 377-8 */
use pubs
go
exec sp_help tmpPublishers
go
select * from information_schema.columns where table_name = 'publishers'
go

/* p. 378 INSERT VALUES */
use pubs
go
/* Can do this mult times. No pub_id constraint. */
insert into tmpPublishers
values('0001', 'OMJ Publishing', 'Bowie', 'MD', 'USA')
go
select * from tmpPublishers order by pub_id
go

/* p. 379 INSERT VALUES (Sparse) */
INSERT INTO tmpPublishers(state, pub_id)
VALUES('AK', '0003')
GO
SELECT * FROM tmpPublishers ORDER BY pub_id
GO

/* p. 381-2 INSERT using SELECT */
CREATE TABLE tblAddressList
(
strName varchar(50) not null,
strAddress varchar(50) not null,
strCity varchar(20) not null,
strState char(2)
)
GO
INSERT INTO tblAddressList
SELECT stor_name, stor_address, city, state
FROM tmpStores
GO
INSERT INTO tblAddressList
SELECT au_lname + ', ' + au_fname, address, city, state
FROM authors
GO
CREATE TABLE tblPublisherList
(
strPubName varchar(40) NULL,
strTitle varchar(80) NULL
)
GO
INSERT INTO tblPublisherList
SELECT pub_name, title
FROM tmpPublishers LEFT OUTER JOIN tmpTitles
ON tmpPublishers.pub_id = tmpTitles.pub_id
GO
SELECT * FROM tblAddressList
SELECT * FROM tblPublisherList
GO

/* p. 382 INSERT with SPs */
EXEC sp_spaceused tmpPublishers
CREATE TABLE tblSpaceUsage
(
strTableName varchar(30) NOT NULL,
strRows varchar(9),
strReserved varchar(10),
strData varchar(10),
strIndexSize varchar(10),
strUnused varchar(10)
)
GO
INSERT INTO tblSpaceUsage
EXEC sp_spaceused 'tmpPublishers'
GO
SELECT * FROM tblSpaceUsage
GO

/* p. 384 Deleting Data */
DELETE tmpSales
GO
DELETE tmpPublishers
WHERE pub_name = 'New Moon Books'
GO
DELETE FROM tblPublisherList
WHERE strPubName =
(SELECT DISTINCT pub_name FROM tmpPublishers
WHERE pub_id = '0001')
GO

/* p. 385 Delete with lookup table */
USE pubs
GO
DROP TABLE tmpSales
GO
SELECT * INTO tmpSales FROM sales
GO
SELECT * FROM tmpSales ORDER BY title_id
GO
SELECT * FROM tmpTitles ORDER BY title_id
GO
DELETE tmpSales
WHERE title_id IN
(SELECT title_id FROM tmpTitles
WHERE type = 'business')
GO
SELECT * FROM tmpSales ORDER BY title_id
GO

/* p. 387 Refresh tmp tables */
USE pubs
GO
DROP TABLE tmpSales
DROP TABLE tmpPublishers
DROP TABLE tmpStores
DROP TABLE tmpTitles
GO
SELECT * INTO tmpSales FROM sales
SELECT * INTO tmpPublishers FROM publishers
SELECT * INTO tmpStores FROM stores
SELECT * INTO tmpTitles FROM titles
GO

/* p. 387 UPDATE */
SELECT * FROM tmpTitles
GO
UPDATE tmpTitles SET ytd_sales = 0
GO
SELECT * FROM tmpTitles
GO

/* p. 388 UPDATE via Lookup Table */
SELECT * FROM tmpTitles ORDER BY pub_id
GO
SELECT * FROM tmpPublishers ORDER BY pub_id
GO
UPDATE tmpTitles
SET pub_id =
(SELECT pub_id FROM tmpPublishers
WHERE pub_name = 'New Moon Books')
WHERE type = 'business'
GO
SELECT * FROM tmpTitles ORDER BY pub_id
GO

/* p. 412 CASE */
SELECT title, title_id, type,
[Full Type] = CASE type
WHEN 'business' THEN 'Business Book'
WHEN 'mod_cook' THEN 'Modern Cooking Book'
ELSE 'Other'
END
, price,
cost = CASE
WHEN price BETWEEN 10 AND 20 THEN 'Midrange'
WHEN price < 10 THEN 'Cheap'
WHEN price IS NULL THEN 'Not Priced'
ELSE 'EXPENSIVE'
END
/* INTO zzzTempTitle */
FROM titles
ORDER BY title DESC

*** More goes here ***

Monday, December 06, 2004

Row-Level Version Control

Google Groups
MSDN Article
Patterns for versioning database entities?
Google Groups 2

Thursday, December 02, 2004

M1 Comments Samples (INCITS Format)

M1 Doc Register
M1/04-0469 (TBF Comments on Hand Geometry)
606 (Proposed Disposition of Comments POS AP)
550 (Approved Response of comments Hand Geometry)
549 (Approved comments Signature/Sign IF)
457 (Authenti-Corp comments on 413)
492 (Editor response to 457)

Tuesday, August 16, 2005

Friday, August 05, 2005

Tuesday, July 26, 2005

Tuesday, July 19, 2005

Wednesday, July 06, 2005

Friday, July 01, 2005

Wednesday, June 29, 2005

Thursday, May 26, 2005

Wednesday, March 02, 2005

Friday, February 11, 2005

Wednesday, February 02, 2005

Thursday, January 27, 2005

Wednesday, December 22, 2004

Wednesday, December 15, 2004

Tuesday, December 14, 2004

Thursday, December 09, 2004

Tuesday, December 07, 2004

Monday, December 06, 2004

Thursday, December 02, 2004

Blog Archive

Labels