Database Project Report
# Database Project - By: Rowaida AlOkka - 220182247
## ER-Diagram
## Q1) Write SQL statement to create the following tables and then after create table, take screenshot for the screen to describe the structure of table and the SQL statement for creation this tables (so we must submit 2 pictures for each table).
## Doctor Table
```sql=
CREATE TABLE `doctor` (
`ssn` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(32) NOT NULL,
`specialty` varchar(64) NOT NULL,
`years_of_experience` int(11) NOT NULL
)
```
## Pharmacy Table
```sql=
CREATE TABLE `pharmacy` (
`name` varchar(32) NOT NULL PRIMARY KEY,
`address` varchar(256) DEFAULT NULL,
`phone-number` varchar(15) DEFAULT NULL
)
```
## Patient Table
```sql=
CREATE TABLE `patient` (
`ssn` int(11) NOT NULL PRIMARY KEY,
`name` varchar(32) NOT NULL,
`address` varchar(256) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`doctor` int(11) NOT NULL,
FOREIGN KEY (doctor) REFERENCES doctor(ssn)
)
```
## Drug Company Table
```sql=
CREATE TABLE `drug_company` (
`name` varchar(100) NOT NULL PRIMARY KEY,
`phone_number` varchar(15) DEFAULT NULL
)
```
## Drug Table
```sql=
CREATE TABLE `drug` (
`name` varchar(100) NOT NULL PRIMARY KEY,
`formula` varchar(256) NOT NULL,
`drug_company` varchar(100) NOT NULL,
FOREIGN KEY (drug_company) REFERENCES drug_company(name)
)
```
## Contract Table
```sql=
CREATE TABLE `contract` (
`start` date NOT NULL,
`end` date NOT NULL,
`text` text NOT NULL,
`drug_company` varchar(100) NOT NULL,
`pharmacy` varchar(32) NOT NULL,
FOREIGN KEY (pharmacy) REFERENCES pharmacy(name),
FOREIGN KEY (drug_company) REFERENCES drug_company(name),
PRIMARY KEY (drug_company, pharmacy)
)
```
## Pharmacy Sells Drugs Table
```sql=
CREATE TABLE `pharmacy_sells_drug` (
`price` int(11) NOT NULL,
`pharmacy` varchar(32) NOT NULL,
`drug` varchar(100) NOT NULL,
FOREIGN KEY (pharmacy) REFERENCES pharmacy(name),
FOREIGN KEY (drug) REFERENCES drug(name),
PRIMARY KEY (drug, pharmacy)
)
```
## Perscreption Table
```sql=
CREATE TABLE `prescription` (
`date` date NOT NULL,
`quantity` int(11) NOT NULL,
`patient` int(11) NOT NULL,
`doctor` int(11) NOT NULL,
`drug` varchar(100) NOT NULL,
FOREIGN KEY (doctor) REFERENCES doctor(ssn),
FOREIGN KEY (patient) REFERENCES patient(ssn),
FOREIGN KEY (drug) REFERENCES drug(name),
PRIMARY KEY (drug, doctor, patient)
)
```
## Q2) Write SQL statements to perform the following tasks (you must submit 2 pictures, one for statement and second for result:
### 1. Modify drugs company name data type.
```sql=
ALTER TABLE contract DROP FOREIGN KEY drug_company_name;
ALTER TABLE drug DROP FOREIGN KEY drug_company_name;
ALTER TABLE drug_company CHANGE name name CHAR(100);
ALTER TABLE contract CHANGE drug_company drug_company CHAR(100);
ALTER TABLE drug CHANGE drug_company drug_company CHAR(100);
ALTER TABLE contract ADD FOREIGN KEY (drug_company) REFERENCES drug_company(name);
ALTER TABLE drug ADD FOREIGN KEY (drug_company) REFERENCES drug_company(name);
```
### 2. Insert five rows to all table.
```sql=
INSERT INTO doctor (name, specialty, years_of_experience) VALUES ('Roze AlOkka', 'Children', '12');
INSERT INTO doctor (name, specialty, years_of_experience) VALUES ('Roze', 'Children', '4');
INSERT INTO doctor (name, specialty, years_of_experience) VALUES ('Roze', 'surgent', '15');
INSERT INTO doctor (name, specialty, years_of_experience) VALUES ('Roza', 'Children', '6');
INSERT INTO doctor (name, specialty, years_of_experience) VALUES ('Rozie', 'surgent', '3');
INSERT INTO patient (name, address, age, doctor) VALUES ('Roze', 'Gaza', 22, 1);
INSERT INTO patient (name, address, age, doctor) VALUES ('RRRR', 'Gaza', 22, 1);
INSERT INTO patient (name, address, age, doctor) VALUES ('Roza', 'Gaza', 22, 1);
INSERT INTO patient (name, address, age, doctor) VALUES ('Rozie', 'Gaza', 22, 1);
INSERT INTO patient (name, address, age, doctor) VALUES ('Roro', 'Gaza', 22, 1);
INSERT INTO drug_company (name, phone_number) VALUES ('Alshefaa', '+970599999999');
INSERT INTO drug_company (name, phone_number) VALUES ('Alwafaa', '+970599999999');
INSERT INTO drug_company (name, phone_number) VALUES ('Life', '+970599999999');
INSERT INTO drug_company (name, phone_number) VALUES ('Love', '+970599999999');
INSERT INTO drug_company (name, phone_number) VALUES ('Happiness', '+970599999999');
INSERT INTO drug (name, formula, drug_company) VALUES ('Paracetamol', 'parecatamol 1ml', 'Alshefaa');
INSERT INTO drug (name, formula, drug_company) VALUES ('Paracetamol2', 'parecatamol 1ml', 'Alwafaa');
INSERT INTO drug (name, formula, drug_company) VALUES ('Paracetamol3', 'parecatamol 1ml', 'Life');
INSERT INTO drug (name, formula, drug_company) VALUES ('Paracetamol4', 'parecatamol 1ml', 'Love');
INSERT INTO drug (name, formula, drug_company) VALUES ('Paracetamol5', 'parecatamol 1ml', 'Happiness');
INSERT INTO pharmacy (name, address, phone-number) VALUES ('Okka Pharm 1', 'Gaza', '+970599999999');
INSERT INTO pharmacy (name, address, phone-number) VALUES ('Okka Pharm 2', 'Gaza', '+970599999999');
INSERT INTO pharmacy (name, address, phone-number) VALUES ('Okka Pharm 3', 'Gaza', '+970599999999');
INSERT INTO pharmacy (name, address, phone-number) VALUES ('Okka Pharm 4', 'Gaza', '+970599999999');
INSERT INTO pharmacy (name, address, phone-number) VALUES ('Okka Pharm 5', 'Gaza', '+970599999999');
INSERT INTO pharmacy_sells_drug (price, pharmacy, drug) VALUES (50, 'Okka Pharm 1', 'Paracetamol');
INSERT INTO pharmacy_sells_drug (price, pharmacy, drug) VALUES (35, 'Okka Pharm 2', 'Paracetamol');
INSERT INTO pharmacy_sells_drug (price, pharmacy, drug) VALUES (50, 'Okka Pharm 3', 'Paracetamol');
INSERT INTO pharmacy_sells_drug (price, pharmacy, drug) VALUES (40, 'Okka Pharm 4', 'Paracetamol');
INSERT INTO pharmacy_sells_drug (price, pharmacy, drug) VALUES (45, 'Okka Pharm 5', 'Paracetamol');
INSERT INTO contract (start, end, text, drug_company, pharmacy) VALUES ('2022-12-15', '2030-12-14', 'For 8 years', 'Alshefaa', 'Okka Pharm 1');
INSERT INTO contract (start, end, text, drug_company, pharmacy) VALUES ('2022-12-15', '2030-12-14', 'For 8 years', 'Alwafaa', 'Okka Pharm 1');
INSERT INTO contract (start, end, text, drug_company, pharmacy) VALUES ('2022-12-15', '2030-12-14', 'For 8 years', 'Love', 'Okka Pharm 2');
INSERT INTO contract (start, end, text, drug_company, pharmacy) VALUES ('2022-12-15', '2030-12-14', 'For 8 years', 'Life', 'Okka Pharm 3');
INSERT INTO contract (start, end, text, drug_company, pharmacy) VALUES ('2022-12-15', '2030-12-14', 'For 8 years', 'Happiness', 'Okka Pharm 4');
INSERT INTO prescription (date, quantity, patient, doctor, drug) VALUES ('2022-12-18', 3, 1, 3, 'Paracetamol');
INSERT INTO prescription (date, quantity, patient, doctor, drug) VALUES ('2022-12-17', 3, 2, 4, 'Paracetamol');
INSERT INTO prescription (date, quantity, patient, doctor, drug) VALUES ('2022-12-15', 3, 1, 5, 'Paracetamol');
INSERT INTO prescription (date, quantity, patient, doctor, drug) VALUES ('2022-12-19', 3, 5, 1, 'Paracetamol');
INSERT INTO prescription (date, quantity, patient, doctor, drug) VALUES ('2022-12-18', 3, 4, 2, 'Paracetamol');
```
### 3. Insert empty cell phone number to pharmacy table, then update the empty cell phone number in pharmacy table and give it a value.
```sql=
INSERT INTO pharmacy (name, address) VALUES ('Okka Pharmacy 8', 'Gaza');
UPDATE pharmacy SET phone-number = '+970599999999' WHERE pharmacy.name = 'Okka Pharmacy 8';
```
### 4. Merge the SSN and name columns from patient table and make them look like this “422526028: Ahmed” and call it “patient_info”.
```sql=
SELECT CONCAT(ssn, ': ', name) AS patient_info FROM patient;
```
### 5. Find drugs name which its price exceeds 50% of their drugs total price.
```sql=
SELECT * FROM drug INNER JOIN `pharmacy_sells_drug` ON drug.name = `pharmacy_sells_drug`.drug AND `pharmacy_sells_drug`.price < (SELECT SUM(`pharmacy_sells_drug`.price) / 2 from `pharmacy_sells_drug`);
```
### 6. Find all patient’s name who take drug called Panadol.
```sql=
SELECT * FROM prescription WHERE drug = 'Panadol'
```
### 7. List formula of drugs which has price is a maximum price for pharmacies that name contains 'el' characters
replaced 'el' with 'Ok' because my pharmacy name is okka
```sql=
SELECT * FROM drug INNER JOIN `pharmacy_sells_drug` ON drug.name = `pharmacy_sells_drug`.drug AND `pharmacy_sells_drug`.`pharmacy` LIKE '%Ok%' INNER JOIN ( SELECT `pharmacy_sells_drug`.drug, MAX(`pharmacy_sells_drug`.price)as maxPrice FROM `pharmacy_sells_drug` ) as highestPriceDrug ON drug.name = highestPriceDrug.drug;
```
### 8. Find all address of pharmacies which make drug’s formula: Tramadol
```sql=
SELECT * FROM pharmacy INNER JOIN `pharmacy_sells_drug` ON pharmacy.name = `pharmacy_sells_drug`.pharmacy INNER JOIN drug ON drug.name = `pharmacy_sells_drug`.drug AND drug.formula = 'Tramadol'
```
### 9. For each company, list names of pharmacies which buy cheapest price of drug.
```sql=
SELECT * FROM drug INNER JOIN `pharmacy_sells_drug` ON drug.name = `pharmacy_sells_drug`.drug INNER JOIN ( SELECT `pharmacy_sells_drug`.drug, MIN(`pharmacy_sells_drug`.price)as minPrice FROM `pharmacy_sells_drug`) as minPriceDrug ON drug.name = minPriceDrug.drug;
```
### 10. Find drugs which price falls within the range of smallest price and 1000
```sql=
SELECT * FROM drug LEFT JOIN `pharmacy_sells_drug` ON drug.name = `pharmacy_sells_drug`.drug WHERE `pharmacy_sells_drug`.price < 1000
```
Sign in with Wallet
Connect another wallet