· 5 years ago · Oct 21, 2020, 03:46 PM
1DROP DATABASE IF EXISTS Labb2;
2
3CREATE DATABASE Labb2;
4
5CREATE TABLE Customer (
6 CustomerID varchar(15) NOT NULL,
7 CustName varchar(255),
8 CustType int,
9 Street varchar(255),
10 CurrentState varchar(255),
11 City varchar(255),
12 ZipCode int,
13 PhoneNumber varchar(255),
14 FaxNumber varchar(255)
15
16);
17
18
19INSERT INTO `Customer` (`CustomerID`, `CustName`, `CustType`, `Street`, `CurrentState`, `City`, `ZipCode`, `PhoneNumber`, `FaxNumber`) VALUES
20('AC001', 'All Creatures', '2', '21 Grace St', 'WA', 'Tall Pines', '98746', '(206) 555-6622', '(206) 555-7854'),
21('AD001', 'Jonathan Adams', '1', '66 10th st', 'WA', 'Mountainview', '984101012', '(206) 555-7623', '(206) 555-8855'),
22('AD002', 'William Adams', '1', '1122 10th St', 'OR', 'Lakeville', '97411011', '(503) 555-6187', '(503) 555-7319'),
23('AD003', 'Adams Johnson', '1', '2211 6th St', 'OR', 'Lakeville', '97411011', '(503) 555-6756', '(503) 555-7438'),
24('AK001', 'Animal Kingdom', '2', '15 Marlyn Lane', 'ID', 'Borderville', '834835646', '(208) 555-7108', '');
25
26
27CREATE TABLE Pet (
28 PetID varchar(15) NOT NULL,
29 CustomerID varchar(15),
30 PetName varchar(255),
31 PetType varchar(255),
32 Breed varchar(255),
33 DoB DATE,
34 Gender varchar(255)
35
36);
37
38INSERT INTO `Pet` (`PetID`, `CustomerID`, `PetName`, `PetType`, `Breed`, `DoB`, `Gender`) VALUES
39('AC001-01', 'AC001', 'Bobo', 'Rabbit', 'Long Ear', '1992-04-08', 'M'),
40('AC001-04', 'AC001', 'Fido', 'Dog', 'German Shepherd', '1990-06-01', 'M'),
41('AC001-02', 'AC001', 'Presto Chango', 'Lizard', 'Chameleon', '1992-05-01', 'F'),
42('AC001-03', 'AC001', 'Stinky', 'Skunk', '', '1991-08-01', 'M'),
43('AD001-01', 'AD001', 'Patty', 'Pig', 'Potbelly', '1991-02-15', 'F'),
44('AD001-02', 'AD001', 'Rising Sun', 'Horse', 'Palomino', '1990-04-10', 'M'),
45('AD002-01', 'AD002', 'Dee Dee', 'Dog', 'Mixed', '1991-02-15', 'F'),
46('AD003-01', 'AD003', 'Fi Fi', 'Dog', 'Poodle', '1994-10-30', 'F'),
47('AK001-03', 'AK001', 'Jerry', 'Rat', '', '1988-02-01', 'M'),
48('AK001-07', 'AK001', 'Luigi', 'Dog', 'Beagle', '1992-08-01', 'M');
49
50
51
52
53CREATE TABLE Employee (
54 EmployeeID int NOT NULL,
55 LName varchar(255),
56 FName varchar(255),
57 EduDegree varchar(255),
58 Street varchar(255),show
59 CurrentState varchar(255),
60 City varchar(255),
61 ZipCode int,
62 PhoneNumber varchar(255),
63 HireDate DATE
64);
65
66INSERT INTO `Employee` (`EmployeeID`, `LName`, `FName`, `EduDegree`, `Street`, `CurrentState`, `City`, `ZipCode`, `PhoneNumber`, `HireDate`) VALUES
67('2', 'Becker', 'Todd', 'MS.', '908 W.Capital Way', 'WA', 'Tacoma', '98401', '(206) 555-9482', '1992-08-04'),
68('8', 'Bowie', 'Rosie', 'B.S.', '4726-11th Ave. N.E.', 'WA', 'Seattle', '98105', '(206) 555-1189', '1994-03-15'),
69('3', 'Carrington', 'Maram', 'MS.', '722 Moss Bay Blvd.', 'WA', 'Kirkland', '98033', '(206) 555-3412', '1992-04-01'),
70('6', 'Chiu', 'Liu', 'MS.', 'Coventry House Miner Rd.', 'WA', 'Tacoma', '98402', '(71) 555-7773', '1993-10-17'),
71('9', 'Dennis', 'Anne', 'MS.', '7 Hounds Tooth Rd.', 'WA', 'Bellingham', '98047', '(71) 555-4444', '1994-11-05'),
72('5', 'Peters', 'Peter', 'B.S.', '14 Garrett Hill', 'WA', 'Bellingham', '98047', '(71) 555-4848', '1993-10-17'),
73('1', 'Plotter', 'Mary', 'PhD.', '507-20th Ave. E. Apt.2A', 'WA', 'Seattle', '98122', '(206) 555-9857', '1992-05-01'),
74('7', 'Wally', 'Robert', 'AA.', 'Edgeham Hollow Winchester Way', 'WA', 'Seattle', '98125', '(71) 555-5598', '1994-01-02'),
75('4', 'Walters', 'Margaret', 'MS.', '4110 Old Redmond Rd.', 'WA', 'Redmond', '98052', '(206) 555-8122', '1993-05-03');
76
77
78
79CREATE TABLE Service (
80 ServiceID varchar(5) NOT NULL,
81 ServiceName varchar(255),
82 Price FLOAT
83
84);
85
86INSERT INTO `Service` (`ServiceID`, `ServiceName`, `Price`) VALUES
87('M0202', 'Zinc Oxide - 3 oz', '7.80'),
88('M0500', 'Nyostatine - 1 oz', '11.50'),
89('M0702', 'Xaritain Glyconol - 2 oz', '34.50'),
90('T0300', 'General Exam', '50.00'),
91('T0404', 'Repair complex fracture', '230.00'),
92('T0408', 'Cast affected area', '120.00'),
93('T1001', 'Lab Work - Cerology', '75.00'),
94('T1003', 'Lab Work - Misc', '35.00'),
95('T2003', 'Flea Spray', '25.00');
96
97CREATE TABLE Visit (
98ServiceID varchar(15),
99PetID varchar(15),
100EmployeeID int,
101VisitDate date
102
103);
104
105INSERT INTO `Visit` (`ServiceID`, `PetID`, `EmployeeID`, `VisitDate`) VALUES
106('M0202', 'AD003-01', '4', '1999-09-05'),
107('T0300', 'AD003-01', '4', '1999-09-05'),
108('T1001', 'AD003-01', '4', '1999-09-05'),
109('T1003', 'AD003-01', '4', '1999-09-05'),
110('M0500', 'AC001-04', '3', '1998-04-11'),
111('T0404', 'AC001-04', '3', '1998-04-11'),
112('T0408', 'AC001-04', '3', '1998-04-11'),
113('M0702', 'AC001-04', '3', '1998-04-11'),
114('T2003', 'AC001-04', '3', '1998-04-11');
115
116
117
118ALTER TABLE Customer
119ADD CONSTRAINT PK_Customer PRIMARY KEY (CustomerID);
120
121
122ALTER TABLE Pet
123ADD CONSTRAINT PK_Pet PRIMARY KEY (PetID);
124
125ALTER TABLE Pet
126ADD CONSTRAINT FK_Pet
127FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID);
128
129
130ALTER TABLE Employee
131ADD CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID);
132
133
134ALTER TABLE Service
135ADD PRIMARY KEY (ServiceID);
136
137
138ALTER TABLE Visit ADD CONSTRAINT PK_Visit PRIMARY KEY (ServiceID, PetID);
139
140ALTER TABLE Visit
141ADD FOREIGN KEY (ServiceID) REFERENCES Service(ServiceID);
142
143ALTER TABLE Visit
144ADD FOREIGN KEY (PetID) REFERENCES Pet(PetID);
145
146
147ALTER TABLE Visit
148ADD FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID);
149
150
151
152/*
153------------
154-------SQL QUERYS-------
155
156
1571. inequality, ta fram förnamn på alla anställda förutom den med id=3.
158---SELECT EmployeeID, FName FROM Employee WHERE EmployeeID != 3;
159
1602. LIKE, ta fram alla anställda vars efternamn börjar på W.
161---SELECT * FROM Employee WHERE LName LIKE 'W%';
162
1633. Select on DATE, Kolla vilka som anställdes mellan dessa datum.
164---SELECT * FROM Employee WHERE HireDate BETWEEN '1993-01-01' AND '1994-12-31';
165
1664. GROUP BY, Se hur många behandlingar AC001-04 fått.
167---SELECT COUNT(ServiceID), PetID FROM Visit WHERE PetID = 'AC001-04' GROUP BY PetID;
168
1695. HAVING, Ta fram alla djur som är född före 1991-01-13
170---SELECT Pet.PetName, Pet.DoB FROM Pet GROUP BY Pet.PetName
171 HAVING sum(Pet.DoB < '1991-01-13') < 2 ORDER BY Pet.DoB;
172
1736. subquery, Hämta Services som är dyrare än $60.
174---SELECT ServiceName, Price FROM Service WHERE EXISTS
175 (SELECT ServiceID FROM Visit WHERE
176 Visit.ServiceID=Service.ServiceID AND Price > 60);
177
1787. correlated subquery, Ta fram namn och pris på det dyraste Serviceet.
179---SELECT T1.ServiceName, T1.Price
180 FROM Service T1
181 WHERE 0 = (SELECT COUNT(*)
182 FROM Service T2
183 WHERE T1.Price < T2.price);
184
185
1868. COUNT, Ta reda på från vilka städer de anställda kommer ifrån.
187---SELECT Employee.City, COUNT(Employee.City) FROM Employee GROUP BY Employee.City ORDER BY COUNT(Employee.City);
188
1899. aggregate function, Se totala kostnaden för Fidos veterinärbesök
190---SELECT Customer.CustName, Pet.PetName, SUM(Service.Price), visit.VisitDate
191 FROM Customer
192 INNER JOIN Pet ON Customer.CustomerID = Pet.CustomerID
193 INNER JOIN Visit ON Pet.PetID = Visit.PetID
194 INNER JOIN Service ON Visit.ServiceID = Service.ServiceID
195 WHERE Pet.PetName = 'Fido'
196 GROUP BY Customer.CustName;
197
19810. DELETE, radera den dagliga rapporten med id 001 från databasen.
199---DELETE FROM Employee WHERE Employee.EmployeeID = '5';
200
20111.UPDATE, Ändra Bobos kön till F istället för M.
202---UPDATE Pet SET Gender='F' WHERE PetID='AC001-01';
203
204
205
206------------
207*/
208