· 8 years ago · May 02, 2018, 04:54 PM
1IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('Device'))
2BEGIN
3 CREATE TABLE [Device] (
4 [DeviceID] INT NOT NULL,
5 [IPAddress] VARCHAR(16) DEFAULT '' NOT NULL,
6 [Name] VARCHAR(512) DEFAULT '' NOT NULL,
7
8 CONSTRAINT [Device_PK] PRIMARY KEY ([DeviceID])
9 )
10END
11
12GO
13
14IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('DeviceCamera'))
15BEGIN
16 CREATE TABLE [DeviceCamera] (
17 [DeviceID] INT NOT NULL,
18 [CameraNumber] INT NOT NULL,
19 [Name] VARCHAR(512) NOT NULL
20
21 CONSTRAINT [DeviceCamera_DeviceID_FK] FOREIGN KEY ([DeviceID]) REFERENCES [Device]([DeviceID])
22 )
23END
24
25GO
26
27INSERT INTO
28 Device (DeviceID, IPAddress, Name)
29VALUES
30 (1, '192.168.0.1', 'Device 1')
31
32INSERT INTO
33 Device (DeviceID, IPAddress, Name)
34VALUES
35 (2, '192.168.2.3', 'Device 2')
36
37INSERT INTO
38 Device (DeviceID, IPAddress, Name)
39VALUES
40 (3, '192.168.5.13', 'Device 3')
41
42INSERT INTO
43 DeviceCamera (DeviceID, CameraNumber, Name)
44VALUES
45 (1, 1, 'Front Door'),
46 (1, 2, 'Back Door'),
47 (2, 1, 'Garden')
48
49GO
50
51-- Query 1
52SELECT
53 *
54FROM
55 DeviceCamera WITH (NOLOCK)
56WHERE
57 DeviceID = 1
58
59-- Query 2
60SELECT
61 Dev.*
62FROM
63 Device Dev WITH (NOLOCK)
64WHERE EXISTS (
65 SELECT * FROM DeviceCamera Cam WHERE Cam.DeviceID = Dev.DeviceID
66)
67
68-- Query 3
69SELECT
70 Dev.*
71FROM
72 Device Dev WITH (NOLOCK)
73JOIN
74 DeviceCamera Cam WITH (NOLOCK)
75ON
76 Dev.DeviceID = Cam.DeviceID
77WHERE
78 Cam.Name LIKE 'Front%'