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