· 6 years ago · Jun 12, 2019, 01:46 PM
1USE master;
2
3-- drop and create database
4DROP DATABASE IF EXISTS testDB;
5CREATE DATABASE testDB;
6GO
7
8USE testDB;
9GO
10-- drop and create tables
11DROP TABLE IF EXISTS owners;
12CREATE TABLE owners(
13 id INT PRIMARY KEY IDENTITY(1,1),
14 first_name VARCHAR (70) NOT NULL,
15 adress VARCHAR (70) NOT NULL,
16 city VARCHAR (70) NOT NULL,
17 living_state VARCHAR (70) NOT NULL,
18 zip INT NOT NULL,
19 country VARCHAR (70) NOT NULL,
20);
21GO
22
23
24
25DROP TABLE IF EXISTS pet_types;
26CREATE TABLE pet_types(
27 id INT PRIMARY KEY IDENTITY(1,1),
28 pet VARCHAR (70) NOT NULL,
29
30);
31GO
32
33DROP TABLE IF EXISTS pets;
34CREATE TABLE pets(
35 id INT PRIMARY KEY IDENTITY (1, 1),
36 pet_name VARCHAR (70) NOT NULL,
37 pet_weight INT,
38 age INT NOT NULL,
39 pet_type INT,
40 owner_id INT,
41 color VARCHAR (70) NOT NULL,
42 --terms_agreed_timestamp smalldatetime NOT NULL,
43 created_date VARCHAR (70),
44 FOREIGN KEY (pet_type) REFERENCES pets (id),
45 FOREIGN KEY (owner_id) REFERENCES owners (id),
46);
47GO
48
49
50
51--Inserting value in owners table
52SET IDENTITY_INSERT [testDB].[dbo].[owners] ON
53INSERT INTO [testDB].[dbo].[owners]
54 ([id]
55 ,[first_name]
56 ,[adress]
57 ,[city]
58 ,[living_state]
59 ,[zip]
60 ,[country]
61 )
62VALUES
63 (1
64,'TOM'
65,'123 view'
66,'SAN DIEGO'
67,'CA'
68,933442
69,'USA')
70,(2
71,'MARIE'
72,'347 HARBOR DR'
73,'SAN MATE'
74,'CA'
75,976553
76,'USA')
77,(3
78,'SUE'
79,'234 SUNNY BLV'
80,'SAN CARLOS'
81,'VA'
82,233455
83,'CAN')
84,(4
85,'ROSE'
86,'324 ROSE DE'
87,'SUNNY'
88,'UT'
89,523435
90,'USA');
91GO
92SET IDENTITY_INSERT [dbo].[owners] OFF
93GO
94
95--Inserting value in pet_type table
96SET IDENTITY_INSERT [testDB].[dbo].[pet_types] ON
97INSERT INTO [testDB].[dbo].[pet_types]
98 ([id]
99 ,[pet]
100 )
101VALUES
102 (1
103 ,'SNAKE')
104,(2
105,'CAT')
106,(3
107,'DOG');
108
109GO
110SET IDENTITY_INSERT [dbo].[pet_types] OFF
111GO
112
113--Inserting value in pets table
114SET IDENTITY_INSERT [testDB].[dbo].[pets] ON
115INSERT INTO [testDB].[dbo].[pets]
116 ([id]
117 ,[pet_name]
118 ,[pet_weight]
119 ,[age]
120 ,[pet_type]
121 ,[owner_id]
122 ,[color]
123 ,[created_date]
124 )
125VALUES
126 (1
127,'PENNY'
128,23
129,10
130,3
131,1
132,'WHITE'
133,'1/1/2018')
134, (2
135,'LOLIE'
136,10
137,3
138,2
139,2
140,'BROWN'
141,'3/4/2019')
142, (3
143,'MISO'
144,3
145,15
146,3
147,1
148,'WHITE'
149,'5/6/2019')
150, (4
151,'RATTLE'
152,16
153,8
154,1
155,3
156,'SILVER'
157,'12/8/2017')
158, (5
159,'SHIPIE'
160,7
161,6
162,2
163,2
164,'GRAY'
165,'3/4/2016');
166GO
167SET IDENTITY_INSERT [dbo].[pets] OFF
168GO
169
170
171--pets that are white and plder than 8 years old
172Select pet_name from pets where color='WHITE' AND age>8
173
174--owner that do not have any pet in the system
175Select * FROM owners where id NOT IN (
176 Select owner_id from pets
177)
178
179--owner with the oldest pet
180Select * from owners Where id IN (
181 Select owner_id from pets where age = (Select MAX(Age) from pets)
182)