Oracle 11g (Please be sure to label everything)

The database design should be implemented using VISIO or similar diagraming program, APEX or NCCs oracle database. Use the attached spreadsheet Semester Project Spread Sheet.xlsx(http://www.mediafire.com/file/0d02fjdnkt1b882/Semester%20Project%20Spread%20Sheet%281%29.xlsx) for all diagram creation and DDL and DML.

4. SQL reports (40%) SQL code and copies of the output for each of the following situations: (I just need the command)

Query 1.List the total payout that Brakus Inc. made for the first quarter of 2017.

SQL Statement Goes Here

Screen Shot Goes Here

Query 2.List the total payout by check Brakus Inc. made for 2017.

SQL Statement Goes Here

Screen Shot Goes Here

Query 3.List the patients Dr. Bettlestone treated in 2017.

SQL Statement Goes Here

Screen Shot Goes Here

Query 4.List the patients Dr. Bettlestone treated in 2017 during the morning.

SQL Statement Goes Here

Screen Shot Goes Here

Query 5.List the patients that did not have an appointment but were seen by a doctor.

SQL Statement Goes Here

Screen Shot Goes Here

Query 6.List all the doctors and what they billed out in 2017. HINT: Use the DOCTOR table and the VISIT table.

SQL Statement Goes Here

Screen Shot Goes Here

Query 7.Determine and list what each patients balance is including those with null balance. HINT: Use just the PATIENT table.

SQL Statement Goes Here

Screen Shot Goes Here

Query 8.Determine and list which patients balance is less than the average balance of all the other patients. HINT: Use just the PATIENT table.

SQL Statement Goes Here

Screen Shot Goes Here

Query 9.In ONE SQL command create a new table named NEW_PATIENT, copy over all of the columns except for pat_balance. Drop PATIENT table, rename NEW_PATIENT to PATIENT.

Drop the column BILL_PAY_AMOUNT from the table BILLING.

SQL Statement Goes Here

Do not need a screen shot for this query

Query 10.Who/whom has a diagnostic of asthma?

SQL Statement Goes Here

Screen Shot Goes Here

Database Schema:

IF OBJECT_ID('dbo.viewing') IS NOT NULL DROP TABLE dbo.viewing IF OBJECT_ID('dbo.lease') IS NOT NULL DROP TABLE dbo.lease IF OBJECT_ID('dbo.property_for_rent') IS NOT NULL DROP TABLE dbo.property_for_rent IF OBJECT_ID('dbo.registration') IS NOT NULL DROP TABLE dbo.registration IF OBJECT_ID('dbo.staff') IS NOT NULL DROP TABLE dbo.staff IF OBJECT_ID('dbo.private_owner') IS NOT NULL DROP TABLE dbo.private_owner IF OBJECT_ID('dbo.client') IS NOT NULL DROP TABLE dbo.client IF OBJECT_ID('dbo.branch') IS NOT NULL DROP TABLE dbo.branch

CREATE TABLE branch( branchno varchar(5) NOT NULL, street varchar(100) NULL, city varchar(20) NULL, postcode varchar(10) NULL, PRIMARY KEY ( branchno )) ;

CREATE TABLE client( clientno varchar(5) NOT NULL, fname varchar(8) NULL, lname varchar(8) NULL, address varchar(35) NULL, tel_no varchar(14) NULL, pref_type char(6) NULL, max_rent int NULL, PRIMARY KEY ( clientno )) ;

CREATE TABLE lease( lease_no varchar(6) NOT NULL, property_no varchar(5) NULL, client_no varchar(5) NULL, rent int NULL, payment_method char(7) NULL, deposit int NULL, paid char(1) NULL, rent_start date NULL, rent_finish date NULL, duratn bigint NULL, PRIMARY KEY ( lease_no ));

CREATE TABLE private_owner( ownerno varchar(5) NOT NULL, fname varchar(20) NULL, lname varchar(20) NULL, address varchar(50) NULL, tel_no varchar(14) NULL, PRIMARY KEY ( ownerno ));

CREATE TABLE property_for_rent( propertyno varchar(5) NOT NULL, street varchar(15) NULL, city varchar(10) NULL, postcode varchar(6) NULL, type char(7) NULL, rooms tinyint NULL, rent int NULL, ownerno varchar(5) NULL, staffno varchar(5) NULL, branchno varchar(5) NULL, PRIMARY KEY ( propertyno )) ;

CREATE TABLE registration( clientno varchar(5) NOT NULL, branchno varchar(5) NOT NULL, staffno varchar(5) NOT NULL, datejoined date NULL, PRIMARY KEY ( clientno , branchno , staffno )) ;

CREATE TABLE staff( staffno varchar(5) NOT NULL, fname varchar(15) NULL, lname varchar(15) NULL, position varchar(15) NULL, sex char(1) NULL, dob date NULL, salary decimal(7, 2) NULL, branchno varchar(5) NULL, PRIMARY KEY ( staffno ));

CREATE TABLE viewing( clientno varchar(5) NOT NULL, propertyno varchar(5) NOT NULL, viewdate date NULL, comments varchar(200) NULL, PRIMARY KEY ( clientno , propertyno )) ;

BEGIN TRANSACTION

INSERT INTO branch (branchno, street, city, postcode) VALUES ('B008', '12 Paul St', 'Preston', 'PR30JB'); INSERT INTO branch (branchno, street, city, postcode) VALUES ('B009', '32 Elizabeth St', 'Burmingham', 'BB17JE'); INSERT INTO branch (branchno, street, city, postcode) VALUES ('B010', '24 Koleen Dr', 'Manchester', 'KM41SA');

INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR78', 'George', 'Clinto', '55 St Louis Dr London JW10FR', '0121-773-3325', 'House ', 500); INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR79', 'Muhammad', 'Aslam', '10 Queens Rd London KW40FM', '0171-213-2625', 'House ', 700); INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR80', 'Sea', 'Hampto', '69 Rosco St Burmingham BI21KL', '0565-773-3032', 'Flat ', 300); INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR81', 'Cyrus', 'Azarbod', '6 Skyline Dr London IN15ZZ', '0181-362-9325', 'House ', 800); INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR82', 'Leo', 'Russel', '214 Terrible Dr Glasgow GW18PR', '0891-448-5569', 'Flat ', 450); INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR83', 'Sameul', 'Sutto', '16 Moscut St Preston PC18JK', '0607-524-4532', 'Flat ', 500); INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR84', 'Lyndi', 'Clinto', '50 Ceasar Bld London JZ10LR', '0171-793-7325', 'House ', 750); INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR85', 'Kathree', 'Joshue', '77 Hoffman Dr Manchester MN48NB', '0525-854-4178', 'House ', 900); INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR86', 'Julia', 'Roberts', '707 Rockford Dr London LK51VD', '0420-568-8544', 'House ', 1500); INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR87', 'Michael', 'Jackso', '14 Madona Dr Preston DW11FY', '0414-552-6632', 'House ', 1200); INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR88', 'George', 'Simpso', '25 Charles Dr London YN10BR', '0181-798-7325', 'Flat ', 850); INSERT INTO client (clientno, fname, lname, address, tel_no, pref_type, max_rent) VALUES ('CR89', 'Fredo', 'Sati', '7 Nitro Dr London JK89YE', '0181-507-5151', 'Flat ', 550);

INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10087', 'PG76', 'CR78', 400, 'Cheque ', 800, 'Y','1995-03-02', '1999-10-31', NULL); INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10088', 'PG76', 'CR83', 400, 'Master ', 800, 'Y', '1999-11-01', '2002-09-30',NULL); INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10091', 'PG58', 'CR83', 500, 'Cash ', 900, 'Y', '2002-10-15','2003-01-15',NULL); INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10093', 'PG74', 'CR79', 600, 'Master ', 1200, 'Y', '1998-01-01', '1999-09-30',NULL); INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10095', 'PG15', 'CR84', 700, 'Cheque ', 1400, 'Y', '1995-04-15', '2003-03-15',NULL); INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10096', 'PG57', 'CR81', 750, 'Visa ', 1500, 'Y', '1996-01-01','2001-05-30',NULL); INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10100', 'PG56', 'CR87', 1200, 'Cash ', 2400, '', '2001-12-01', '2003-02-28',NULL); INSERT INTO lease (lease_no, property_no, client_no, rent, payment_method, deposit, paid, rent_start, rent_finish, duratn) VALUES ('10101', 'PG59', 'CR86', 1400, 'Visa ', 3000, 'Y', '1997-11-01', '2001-11-30',NULL);

INSERT INTO private_owner (ownerno, fname, lname, address, tel_no) VALUES ('CO51', 'Blair', 'Steve', '2 Style Dr London JZ43KL', '0181-750-2551'); INSERT INTO private_owner (ownerno, fname, lname, address, tel_no) VALUES ('CO63', 'Kare', 'Salem', '14 Janet St Bristol BR43KL', '0121-750-6513'); INSERT INTO private_owner (ownerno, fname, lname, address, tel_no) VALUES ('CO64', 'Teetz', 'Alle', '2 Lookup Dr Manchester GA43', '0131-730-4002'); INSERT INTO private_owner (ownerno, fname, lname, address, tel_no) VALUES ('CO65', 'Leo', 'Russel', '15 Cicero St Bristol BR21UT', '0121-631-6262');

INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG15', '11 Arago', 'Manchester', 'SA32DC', 'House ', 4, 700, 'CO51', 'SG17', 'B008'); INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG56', '1 Jackson Ht', 'Manchester', 'MA32DC', 'House ', 6, 1200, 'CO51', 'SA81', 'B009'); INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG57', '10 Hamilton DR', 'Manchester', 'LO32VC', 'Flat ', 5, 700, 'CO64', 'SL61', 'B008'); INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG58', '65 Lori St', 'Burmingham', 'BR22DZ', 'Flat ', 3, 500, 'CO63', 'SL51', 'B010'); INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG59', '25 River Dr', 'Londo', 'YZ32DC', 'House ', 8, 1400, 'CO63', 'SA10', 'B009'); INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG71', '23 Lori St', 'Burmingham', 'BR22DZ', 'Flat ', 3, 500, 'CO63', 'SL51', 'B010'); INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG74', '304 Michelle St', 'Burmingham', 'BR29ZS', 'House ', 3, 600, 'CO51', 'SL51', 'B010'); INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG76', '7 Dracula St', 'Londo', 'OP32DC', 'Flat ', 3, 400, 'CO64', 'SA8', 'B008'); INSERT INTO property_for_rent (propertyno, street, city, postcode, type, rooms, rent, ownerno, staffno, branchno) VALUES ('PG78', '9 Thames Dr', 'Londo', 'JA32DC', 'House ', 4, 700, 'CO64', 'SG17', 'B010');

INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SA10', 'Bria', 'Sothe', 'Supervisor', 'M', '1964-12-30', 17000.00, 'B008'); INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SA5', 'Mark', 'Smith', 'Assistant', 'M', '1974-11-23',10000.00, 'B008'); INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SA8', 'Jake', 'Austi', 'Assistant', 'M', '1979-01-03', 9000.00, 'B009'); INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SG15', 'Angila', 'Jolee', 'Assistant', 'F', '1976-09-23', 12000.00, 'B010'); INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SG16', 'Fredo ', 'Satin ', 'Supervisor', 'M', '1973-04-13', 16000.00, 'B010'); INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SG17', 'Gyneth', 'Paltrow', 'Assistant', 'F', '1980-01-07', 11000.00, 'B010'); INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SL51', 'Maria', 'Bria', 'Assistant', 'F', '1976-10-15', 13000.00, 'B009'); INSERT INTO staff (staffno, fname, lname, position, sex, dob, salary, branchno) VALUES ('SL61', 'Sheela', 'Johnso', 'Manager', 'F', '1969-02-20',33000.00, 'B009');

TRUNCATE TABLE viewing ; INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR78', 'PG71', '1993-06-01', NULL); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR78', 'PG57', '1985-02-01', 'We will go for it'); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR79', 'PG57', '2002-01-01', 'Wonderful'); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR79', 'PG76', '2002-10-01', NULL); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR79', 'PG74', '1999-10-01','Wonderful'); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR79', 'PG15', '1995-06-01','Can you change the color'); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR83', 'PG15', '2000-12-01','Wonderful'); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR78', 'PG59', '1999-10-01', 'It is good'); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR78', 'PG76', '1995-03-02', NULL); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR79', 'PG59', '1998-01-01', NULL); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR80', 'PG58', '1979-12-01', 'It is good'); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR80', 'PG15', '1985-01-01', 'Let us think about it'); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR81', 'PG15', '1996-01-01', NULL); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR81', 'PG59', '2001-06-01', 'I want to rent it ASAP'); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR82', 'PG58', '2001-01-15', 'I like the kitchen the most'); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR83', 'PG58', '2002-10-15', NULL); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR83', 'PG56', '1999-11-01', NULL); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR84', 'PG15', '1995-04-15', 'It is good'); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR85', 'PG15', '2002-06-01', 'I like the kitchen the most'); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR85', 'PG59', '2001-11-01', NULL); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR86', 'PG59', '1997-11-01', NULL); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR87', 'PG56', '2001-12-01', 'I want to rent it ASAP'); INSERT INTO viewing (clientno, propertyno, viewdate, comments) VALUES ('CR87', 'PG59', '1999-04-01', 'I like the kitchen the most');

ALTER TABLE LEASE ADD CONSTRAINT FK_LEASE_CLIENT FOREIGN KEY(CLIENT_NO) REFERENCES CLIENT (CLIENTNO) ; ALTER TABLE LEASE ADD CONSTRAINT FK_LEASE_PROPERTY_FOR_RENT FOREIGN KEY(PROPERTY_NO) REFERENCES PROPERTY_FOR_RENT (PROPERTYNO) ; ALTER TABLE PROPERTY_FOR_RENT ADD FOREIGN KEY(BRANCHNO) REFERENCES BRANCH (BRANCHNO);

ALTER TABLE PROPERTY_FOR_RENT ADD CONSTRAINT FK_PROPERTYFORRENTPRIVATEOWNER FOREIGN KEY(OWNERNO) REFERENCES PRIVATE_OWNER (OWNERNO) ; ALTER TABLE REGISTRATION ADD CONSTRAINT FK_REGISTRATION_BRANCH FOREIGN KEY(BRANCHNO) REFERENCES BRANCH (BRANCHNO) ; ALTER TABLE REGISTRATION ADD CONSTRAINT FK_REGISTRATION_CLIENT FOREIGN KEY(CLIENTNO) REFERENCES CLIENT (CLIENTNO) ; ALTER TABLE REGISTRATION ADD CONSTRAINT FK_REGISTRATION_STAFF FOREIGN KEY(STAFFNO) REFERENCES STAFF (STAFFNO) ; ALTER TABLE STAFF ADD FOREIGN KEY(BRANCHNO) REFERENCES BRANCH (BRANCHNO) ; ALTER TABLE VIEWING ADD CONSTRAINT FK_VIEWING_CLIENT FOREIGN KEY(CLIENTNO) REFERENCES CLIENT (CLIENTNO) ;

ALTER TABLE VIEWING ADD CONSTRAINT FK_VIEWING_PROPERTY_FOR_RENT FOREIGN KEY(PROPERTYNO) REFERENCES PROPERTY_FOR_RENT (PROPERTYNO) ;

COMMIT;