This gallery contains 5 photos.
It appears that on some Kindle devices, it is hard to read the tables. Therefore, on request, I’ve put the screenshots of the tables here. Tip: you can also read the eBook on Amazon Cloud Reader (https://read.amazon.com)
This video demonstrates the installation for SQL Server 2008 R2. It is supporting material for my eBook on Database Fundamentals, which covers the Microsoft MTA 98-364 exam. In this video, I explain the installation process step by step. The purpose of … Continue reading
Finally, I’ve finished my book on the Microsoft Technology Associate 98-364 exam: Database Fundamentals. It’s available in the iTunes book store: https://itunes.apple.com/us/book/id1043228558 I decided to make it an iBook, since this format provides a combination of text, screenshots, screencasts and interactive test … Continue reading
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
SELECT 'Average' = AVG(quantity) ,'Average #2' = AVG(CAST(quantity as decimal(5,2))) ,'Number' = COUNT(*) ,'Maximum' = MAX(quantity) ,'Minimum' = MIN(quantity) ,'Total' = SUM(quantity) FROM Orderlines; SELECT OrderDate ,'Payment due' = DATEADD(m,1,OrderDate) ,'Days ago' = DATEDIFF(dd,OrderDate,GETDATE()) ,'Today' = GETDATE() FROM Orders; SELECT 'Left 3' = LEFT('ABCDE', 3) ,'Length' = LEN('ABCDE') ,'Lower case' = LOWER('Abcde') ,'No leading spaces' = (LTRIM(' ABCDE')) ,'Right 3' = RIGHT('ABCDE', 3) ,'No trailing spaces' = RTRIM('ABCDE ') ,'Upper case' = UPPER('Abcde'); --create function CREATE FUNCTION fnDoubler (@input INT) RETURNS INT AS BEGIN SET @input = @input * 2; RETURN @input END --call function SELECT '16 * 2 = ' = dbo.fndoubler(16); -- Create the Logging table CREATE TABLE [dbo].[Logging] ( LogTime smalldatetime , LogMessage varchar(1000)); |
A while ago, I’ve published my book on Database Fundamentals for SQL Server 2008. The book covers everything you need to know to pass the Microsoft exam on this topic, the MTA 98-364. This exam is part of the Microsoft … Continue reading
Chapter 3, Using Multiple Tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
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; |
Code samples for chapter 2, using a Single Table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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] 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'; |
