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

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';

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

Leave a Reply Cancel reply

Recent Posts

Archives

Categories