eNotebook: 2004

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 ***

Sired, Squired, Hired, RETIRED.
---
Gunner

"I mean, when's the last time you heard of a college where the Young Republicans staged a "Sit In" to close down the Humanities building? On the flip side, how many sit in's were staged to close the ROTC building back in the '60's? Liberals stage protests, do civil disobedience, etc. Conservatives talk politely and try to work out a solution to problems through discourse until they believe that talking won't work... they they go home and open the gun cabinets. Pray things never get to the point where the conservatives decide that "civil disobedience" is the next step, because that's a very short route to "voting from the rooftops"
Jeffrey Swartz, Misc.Survivalism
---
"The artist is nothing without the gift, but the gift is nothing without work" - Emile Zola
---
The plural of anecdote isn't data.
---
The Titanic. The Hindenburg. The Clintons.
http://www.diversify.com
---

eNotebook

Wednesday, December 22, 2004

101 VB Apps

Wednesday, December 15, 2004

VUML Stuff

Tuesday, December 14, 2004

Thunderbird Stuff

Thursday, December 09, 2004

TY SQL Server 2000 in 21 Days Stuff 2

Tuesday, December 07, 2004

TY SQL Server 2000 in 21 Days Stuff

Monday, December 06, 2004

Row-Level Version Control

Thursday, December 02, 2004

M1 Comments Samples (INCITS Format)

Misc Web Stuff

Favorite Sigs

ToDo's

Visual Studio / Office 2003 Stuff

Blog Archive

Labels