Labb 2 - föreläsningsanteckningar 2 PDF

Preview

Summary

Notes of the second part and labs....

Description

Lab 2 Database implementation and query-creation

TDATG HT15

Mid Sweden University DT076G

SQL-implementation This is the relational database implementation of the Janavar database using MySQL.

Mid Sweden University DT076G

TDATG HT15

Data Input Queries The input queries used to fill the Janavar Database based on the output examples in the lab assignment. INSERT INTO Employee (employeeID, lastName, firstName, educationalDegree, hireDate, address, city, state, zip, homePhone) VALUES ('2', 'Becker', 'Todd', 'MS.', '1992-08-04', '908 W.Capital Way', 'Taoma', 'WA', '98401', '(206)5559482'), ('8', 'Bowie', 'Rosie', 'BS.', '1994-03-15', '4726 - 11th Ave. N.E.', 'Seattle', 'WA', '98105', '(206)555-1189'), ('3', 'Carrington', 'Maram', 'MS.', '1992-04-21', '722 Moss Bay Blvd.', 'Kirkland', 'WA', '94834', '(206)555-2313'); ('6', 'Chiu', 'Lin', 'MS.', '1993-05-23', '722 Moss Bay Blwd.', 'Kirkland', 'WA', '98033', '(206) 5533152'), ('9', 'Dennis', 'Anne', 'MS.', '1982-01-10', '722 Moss Bay Blwd.', 'Bellingham', 'WA', '95233', '(222) 23-123'), ('5', 'Peters', 'Peter', 'BS.', '1994-02-10', '722 Moss Bay Blwd.', 'Bellingham', 'WA', '95233', '(222) 23-123'), ('1', 'Plotter', 'May', 'PhD.', '1966-11-02', '722 Moss Bay Blwd.', 'Seattle', 'WA', '95233', '(222) 23-123'), ('7', 'Wally', 'Robert', 'AA.', '1982-06-02', '722 Moss Bay Blwd.', 'Seatle', 'WA', '95233', '(222) 23123'), ('4', 'Walters', 'Margaret', 'MS.', '1969-12-19', '722 Moss Bay Blwd.', 'Redmond', 'WA', '95233', '(222) 23-123'), ('11', 'Mario', 'Gonzales', 'MS.', '1991-10-29', 'Some Street 7', 'Somecity', 'EN', '12345', '(412) 414467'); INSERT INTO Customer (customerID, typeOfCustomer, customerName, streetApartment, city, state, zip, phoneNumber, faxNumber) VALUES ('AC001', '2', 'All Creatures', '21 Grace St.', 'Tall Pines', 'WA', '98746', '(206)555-6622', '000'), ('AD001', '1', 'Johnathan Adams', '66 10th St', 'Mountain View', 'WA', '984101012', '(206)555-7623', '000'), ('AD002', '1', 'William Adams', '1122 10th St', 'Lakeville', 'OR', '97442132', '(503)555-6187', '000'), ('AK001', '2', 'Animal Kingdom', '15 Marlin Lane', 'Borderville', 'ID', '83485235', '(208)555-7154', '000'); INSERT INTO Pet (petID, petName, typeOfAnimal, breed, dob, gender, Customer_customerID) VALUES ('AC001-01', 'Bobo', 'RABBIT', 'Long Ear', '1992-04-08', 'M', 'AC001'), ('AC001-04', 'Fido', 'DOG', 'German Shepherd', '1990-01-06', 'M', 'AC001'), ('AC001-02', 'Presto Chango', 'LIZARD', 'Chameleon', '1992-05-01', 'F', 'AC001'), ('AC001-03', 'Stinky', 'SKUNK', '', '1992-08-01', 'M', 'AC001'), ('AD001-01', 'Patty', 'PIG', 'Potbelly', '1991-02-15', 'F', 'AD001'), ('AD001-02', 'Rising Sun', 'HORSE', 'Palomino', '1990-04-10', 'M', 'AD001'), ('AD002-01', 'Dee Dee', 'DOG', 'Mixed', '1991-02-15', 'F', 'AD002'), ('AK001-03', 'Jerry', 'RAT', '', '1988-02-01', 'M', 'AK001'), ('AK001-07', 'Luigi', 'DOG', 'Beagle', '1992-08-01', 'M', 'AK001'); INSERT INTO Service (serviceID, VALUES ('T1003', '1999-09-05', ('T1001', '1999-09-05', ('T0300', '1999-09-05', ('M0202', '1999-09-05', ('T2003', '1998-04-11', ('M0500', '1998-04-11', ('M0702', '1998-04-11', ('T0404', '1998-04-11', ('T0408', '1998-04-11',

visitDate, treatment, price, Pet_petID, Employee_employeeID) 'Lab Work - Misc', '35', 'AC001-01', '4'), 'Lab Work - Cerology', '75', 'AC001-01', '4'), 'General Exam', '50', 'AC001-01', '4'), 'Zinc Oxide - 4 oz', '7.80', 'AC001-01', '4'), 'Flea Spray', '25', 'AC001-04', '3'), 'Nyostatine - 1 oz', '11.50', 'AC001-04', '3'), 'Xaritain Glyconol - 2 oz', '34.60', 'AC001-04', '3'), 'Repaur complex fracture', '230', 'AC001-04', '3'), 'Cast affected area', '120', 'AC001-04', '3');

Mid Sweden University DT076G

TDATG HT15

Data Output Queries These queries are designed to output tables that looks like the examples provided in the assignment, rough examples of how the queries could look.

-

Employee Table

SELECT * FROM Employee WHERE HireDate > '1980-04-21' AND lastName != 'Becker' AND state LIKE 'W%' GROUP BY lastName;

-

Daily Visits Report Table

SELECT * FROM Service WHERE Pet_PetID = ( SELECT PetID FROM Pet WHERE Customer_CustomerID = "AC001" HAVING COUNT(*) > 0); SELECT SUM(price) FROM Service WHERE Pet_PetID = ( SELECT PetID FROM Pet WHERE Customer_CustomerID = "AC001" HAVING COUNT(*) > 0);

-

Customer and Pets Table

SELECT SELECT SELECT SELECT

-

* * * *

FROM FROM FROM FROM

Customer Where CustomerID = "AC001"; Pet WHERE Customer_CustomerID = "AC001" ORDER BY petName; Customer WHERE CustomerID = "AD001"; Pet Where Customer_CustomerID = "AC001" ORDER BY petName;

A selective DELETE

Purpose: Remove all services related to the pet with PetID AC001-01. DELETE FROM Service WHERE Pet_PetID = ANY( SELECT PetID FROM Pet WHERE PetID = "AC001-01");

TDATG HT15 -

Mid Sweden University DT076G

A selective UPDATE

Purpose: Change the price of the service M0202 related to the pet with PetID AC001-01 to 10 dollars. UPDATE Service SET price = "10" WHERE Pet_PetID = ANY( SELECT PetID FROM Pet WHERE PetID = "AC001-01" && Service.ServiceID = "M0202");...