Monthly Archives: January 2014

Code samples for my book on Database Fundamentals (MTA 98-364), chapter 3

Posted on January 27, 2014 by Robert

Chapter 3, Using Multiple Tables.

CREATE TABLE Customers (
CustomerID int IDENTITY
,[First name] varchar(100)
,[Last name] varchar(100)
,[Address] varchar(100)
,PRIMARY KEY (CustomerID))

CREATE TABLE Orders (
OrderID int PRIMARY KEY
,CustomerID int NULL
,OrderDate datetime
,CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));

CREATE TABLE Orderlines (
OrderID int references orders(orderid)
,[Order line number] int
,Item varchar(100)
,quantity int
,price decimal(18,2)
,primary key (orderid, [Order line number]))

CREATE TABLE Employees (
EmployeeID int IDENTITY
,[First name] varchar(100) 
,[Last name] varchar(100)
,[Address] varchar(100)
,PRIMARY KEY (EmployeeID))

--DROP TABLE orderlines
--DROP TABLE Orders
--DROP TABLE customers
--DROP TABLE Employees

INSERT Customers values ('John','Smith','First Street 1, Washington');
INSERT Customers values ('Peter','Jones','Second Street 2, London');
INSERT Customers values ('Frank','James','Champs-Elysees , Paris');
INSERT Customers values ('Jim','Gray','Main Street 7, New York');

INSERT Customers VALUES 
('Peter', 'Jones', 'Second Street 2, London')
,('Frank', 'James', 'Champs-Elysees , Paris')
,('Jim', 'Gray', 'Main Street 7, New York');

INSERT Orders VALUES ('1','1', '2013-01-01');
INSERT Orders VALUES ('2','2', '2012-07-01');
INSERT Orders VALUES ('3','3', '2012-08-15');
INSERT Orders VALUES ('4','4', '2012-12-31');

INSERT orderlines values (1,1,'A Brief History of Time – S. Hawking',1,9.99);
INSERT orderlines values (2,1,'The Prince – Macchiavelli',10,59.90);
INSERT orderlines values (3,1,'Lord of the Rings – J.R.R. Tolkien',2,17.98);
INSERT orderlines values (4,1,'The Prince – Macchiavelli',1,5.99);

INSERT INTO Employees values ('Barry','St. John','Broadway 5, New York');
INSERT Employees values ('Jim','Gray','Main Street 7, New York');

--outer join
SELECT *
FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN Orderlines ol on ol.OrderID = o.OrderID;

--subqueries 
SELECT *
FROM Customers
WHERE [First name] in ( SELECT [First name]
FROM Employees);


--subquery
SELECT 'Address' = [Street name] + [House number] + ', ' + City
FROM Shipments
--table subquery example
CREATE TABLE Shipments (
 ShipmentID int identity
 ,Shipmentdate datetime
 ,[Street name] varchar(100)
 ,[House number] varchar(10)
 ,[City] varchar(100));
--outer query
SELECT *
FROM Customers c 
 INNER JOIN (...) s on c.Address = s.Address
 
--Total
SELECT *
FROM Customers c 
 INNER JOIN (SELECT 'Address' = [Street name] + [House number] + ', ' + City
 FROM Shipments
 ) s on c.Address = s.Address;

CREATE TABLE Customers (

CustomerID int IDENTITY

,[First name] varchar(100)

,[Last name] varchar(100)

,[Address] varchar(100)

,PRIMARY KEY (CustomerID))

CREATE TABLE Orders (

OrderID int PRIMARY KEY

,CustomerID int NULL

,OrderDate datetime

,CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));

CREATE TABLE Orderlines (

OrderID int references orders(orderid)

,[Order line number] int

,Item varchar(100)

,quantity int

,price decimal(18,2)

,primary key (orderid, [Order line number]))

CREATE TABLE Employees (

EmployeeID int IDENTITY

,[First name] varchar(100)

,[Last name] varchar(100)

,[Address] varchar(100)

,PRIMARY KEY (EmployeeID))

--DROP TABLE orderlines

--DROP TABLE Orders

--DROP TABLE customers

--DROP TABLE Employees

INSERT Customers values ('John','Smith','First Street 1, Washington');

INSERT Customers values ('Peter','Jones','Second Street 2, London');

INSERT Customers values ('Frank','James','Champs-Elysees , Paris');

INSERT Customers values ('Jim','Gray','Main Street 7, New York');

INSERT Customers VALUES

('Peter', 'Jones', 'Second Street 2, London')

,('Frank', 'James', 'Champs-Elysees , Paris')

,('Jim', 'Gray', 'Main Street 7, New York');

INSERT Orders VALUES ('1','1', '2013-01-01');

INSERT Orders VALUES ('2','2', '2012-07-01');

INSERT Orders VALUES ('3','3', '2012-08-15');

INSERT Orders VALUES ('4','4', '2012-12-31');

INSERT orderlines values (1,1,'A Brief History of Time – S. Hawking',1,9.99);

INSERT orderlines values (2,1,'The Prince – Macchiavelli',10,59.90);

INSERT orderlines values (3,1,'Lord of the Rings – J.R.R. Tolkien',2,17.98);

INSERT orderlines values (4,1,'The Prince – Macchiavelli',1,5.99);

INSERT INTO Employees values ('Barry','St. John','Broadway 5, New York');

INSERT Employees values ('Jim','Gray','Main Street 7, New York');

--outer join

SELECT *

FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID

JOIN Orderlines ol on ol.OrderID = o.OrderID;

--subqueries

SELECT *

FROM Customers

WHERE [First name] in ( SELECT [First name]

FROM Employees);

--subquery

SELECT 'Address' = [Street name] + [House number] + ', ' + City

FROM Shipments

--table subquery example

CREATE TABLE Shipments (

ShipmentID int identity

,Shipmentdate datetime

,[Street name] varchar(100)

,[House number] varchar(10)

,[City] varchar(100));

--outer query

SELECT *

FROM Customers c

INNER JOIN (...) s on c.Address = s.Address

--Total

SELECT *

FROM Customers c

INNER JOIN (SELECT 'Address' = [Street name] + [House number] + ', ' + City

FROM Shipments

) s on c.Address = s.Address;

Posted in Code samples, MTA 98-364 | Leave a comment

Code samples for my book on Database Fundamentals (MTA 98-364), chapter 2

Posted on January 27, 2014 by Robert

Code samples for chapter 2, using a Single Table.

-- create table dbo.Persons, scripted by SSMS
USE [testdatabase]
GO

/****** Object: Table [dbo].[Persons] Script Date: 01/02/2013 20:37:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Persons](
[Firstname] [varchar](100) NOT NULL,
[Lastname] [varchar](100) NOT NULL,
[Address] [varchar](100) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

-- create table dbo.Persons
DROP TABLE Persons
GO
CREATE TABLE [Persons] (
Firstname varchar(100) NOT NULL,
Lastname varchar(100) NOT NULL,
Address varchar(100) NULL DEFAULT '1600 Pennsylvania Avenue, Washington D.C.')

-- insert records into table dbo.Persons
INSERT Persons (Firstname, Lastname)VALUES ('John', 'Smith' );
INSERT Persons VALUES ('Peter', 'Jones', 'Second Street 2, London');
INSERT Persons VALUES ('Frank', 'James', 'Champs-Elysees, Paris');
INSERT Persons VALUES ('Elvis', 'Presley', NULL);

-- select records from table dbo.Persons
SELECT *
FROM Persons

SELECT Firstname
,Lastname
,Address
FROM Persons;

SELECT Firstname,Lastname,Address FROM Persons;

SELECT *
FROM Persons
ORDER BY Lastname;

SELECT *
FROM Persons
ORDER BY Lastname, Firstname;

SELECT *
FROM Persons
ORDER BY Lastname DESC;

SELECT *
FROM Persons
WHERE Lastname = 'Jones';

SELECT *
FROM Persons
WHERE Lastname = 'James'
or Lastname = 'Jones' ;

SELECT *
FROM Persons
WHERE Lastname IN ('James', 'Jones') ;

SELECT *
FROM Persons
WHERE Lastname LIKE 'Jam%';

SELECT *
FROM Persons
WHERE Lastname IS NULL;

SELECT *
FROM Persons
WHERE Lastname = 'James'
and Lastname = 'Jones' ;

SELECT TOP 2 *
FROM Persons;

SELECT DISTINCT [Lastname]
FROM Persons;

SELECT 'Achternaam' = Lastname
FROM Persons

SELECT Lastname AS 'Achternaam'
FROM Persons

SELECT Lastname + ', ' + Firstname AS 'Name'
FROM Persons

SET CONCAT_NULL_YIELDS_NULL OFF

SET CONCAT_NULL_YIELDS_NULL ON

SELECT 'any string'
FROM Persons

-- update records in table dbo.Persons
UPDATE Persons
SET Firstname = 'Frank jr.'
, Address = 'Unter den Linden, Berlin'
WHERE Firstname = 'Frank jr.';

UPDATE Persons
SET Firstname = 'Frank'
, Address = 'Unter den Linden, Berlin'
WHERE Firstname = 'Frank jr.';

-- delete 1 record (in case of duplicates)
DELETE
FROM Persons
WHERE Lastname = 'James';

DELETE * /*comment: this delete statement will produce a syntax error*/
FROM Persons
WHERE Lastname = 'James'

DELETE TOP (1)
FROM Persons
WHERE Firstname = 'John';

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

-- create table dbo.Persons, scripted by SSMS

USE [testdatabase]

/****** Object: Table [dbo].[Persons] Script Date: 01/02/2013 20:37:06 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[Persons](

[Firstname] [varchar](100) NOT NULL,

[Lastname] [varchar](100) NOT NULL,

[Address] [varchar](100) NOT NULL

) ON [PRIMARY]

SET ANSI_PADDING OFF

-- create table dbo.Persons

DROP TABLE Persons

CREATE TABLE [Persons] (

Firstname varchar(100) NOT NULL,

Lastname varchar(100) NOT NULL,

Address varchar(100) NULL DEFAULT '1600 Pennsylvania Avenue, Washington D.C.')

-- insert records into table dbo.Persons

INSERT Persons (Firstname, Lastname)VALUES ('John', 'Smith' );

INSERT Persons VALUES ('Peter', 'Jones', 'Second Street 2, London');

INSERT Persons VALUES ('Frank', 'James', 'Champs-Elysees, Paris');

INSERT Persons VALUES ('Elvis', 'Presley', NULL);

-- select records from table dbo.Persons

SELECT *

FROM Persons

SELECT Firstname

,Lastname

,Address

FROM Persons;

SELECT Firstname,Lastname,Address FROM Persons;

SELECT *

FROM Persons

ORDER BY Lastname;

SELECT *

FROM Persons

ORDER BY Lastname, Firstname;

SELECT *

FROM Persons

ORDER BY Lastname DESC;

SELECT *

FROM Persons

WHERE Lastname = 'Jones';

SELECT *

FROM Persons

WHERE Lastname = 'James'

or Lastname = 'Jones' ;

SELECT *

FROM Persons

WHERE Lastname IN ('James', 'Jones') ;

SELECT *

FROM Persons

WHERE Lastname LIKE 'Jam%';

SELECT *

FROM Persons

WHERE Lastname IS NULL;

SELECT *

FROM Persons

WHERE Lastname = 'James'

and Lastname = 'Jones' ;

SELECT TOP 2 *

FROM Persons;

SELECT DISTINCT [Lastname]

FROM Persons;

SELECT 'Achternaam' = Lastname

FROM Persons

SELECT Lastname AS 'Achternaam'

FROM Persons

SELECT Lastname + ', ' + Firstname AS 'Name'

FROM Persons

SET CONCAT_NULL_YIELDS_NULL OFF

SET CONCAT_NULL_YIELDS_NULL ON

SELECT 'any string'

FROM Persons

-- update records in table dbo.Persons

UPDATE Persons

SET Firstname = 'Frank jr.'

, Address = 'Unter den Linden, Berlin'

WHERE Firstname = 'Frank jr.';

UPDATE Persons

SET Firstname = 'Frank'

, Address = 'Unter den Linden, Berlin'

WHERE Firstname = 'Frank jr.';

-- delete 1 record (in case of duplicates)

DELETE

FROM Persons

WHERE Lastname = 'James';

DELETE * /*comment: this delete statement will produce a syntax error*/

FROM Persons

WHERE Lastname = 'James'

DELETE TOP (1)

FROM Persons

WHERE Firstname = 'John';

Posted in Code samples, MTA 98-364 | Leave a comment

Monthly Archives: January 2014

Code samples for my book on Database Fundamentals (MTA 98-364), chapter 3

Code samples for my book on Database Fundamentals (MTA 98-364), chapter 2

Recent Posts

Archives

Categories