Create two tables of your choice. Insert 3 to 4 records in it. Then create 4 different…
- User
Create two tables of your choice. Insert 3 to 4 records in it. Then create 4 different views on the four different queries. The queries must consist of the following: A query that uses SQL operators. A query that uses aggregate function(s). A query that uses joins. A query that uses sub-queries. Also, write a query that deletes a view.
remember to use pakistani names when you insert
- Assistant
Table 1: employees
id name department salary 1 Ali Marketing 20000 2 Fatima Finance 25000 3 Hamza IT 18000 4 Ayesha Sales 22000 Table 2: projects
id name status 1 Website In Progress 2 Marketing Completed 3 Product Launch In Progress 4 Sales Campaign Completed SQL Operator Query:
SELECT * FROM employees WHERE salary >= 22000;
Aggregate Function Query:
SELECT department, AVG(salary) as average_salary FROM employees GROUP BY department;
Join Query:
SELECT employees.name, projects.name FROM employees INNER JOIN projects ON employees.id = projects.id;
Sub-query Query:
SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
View 1: employees_above_average_salary
CREATE VIEW employees_above_average_salary AS SELECT * FROM employees WHERE salary >= 22000;
View 2: average_salary_by_department
CREATE VIEW average_salary_by_department AS SELECT department, AVG(salary) as average_salary FROM employees GROUP BY department;
View 3: employee_project_names
CREATE VIEW employee_project_names AS SELECT employees.name, projects.name FROM employees INNER JOIN projects ON employees.id = projects.id;
View 4: employees_above_department_average_salary
CREATE VIEW employees_above_department_average_salary AS SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
Query to delete a view:
DROP VIEW employees_above_department_average_salary;
- User
on second thought, create two tables for a morgue, dont create the tables give me the sql code for it
- Assistant
Here is the SQL code to create two tables for a morgue:
Table 1: bodies
CREATE TABLE bodies ( id INT PRIMARY KEY, name VARCHAR(50), age INT, gender VARCHAR(10), date_of_death DATE, cause_of_death VARCHAR(100), autopsy_report TEXT, storage_location VARCHAR(50) );Table 2: autopsy_examiners
CREATE TABLE autopsy_examiners ( id INT PRIMARY KEY, name VARCHAR(50), specialization VARCHAR(50), date_of_joining DATE, contact_number VARCHAR(20) );The "bodies" table contains details about the bodies brought to the morgue, including the name, age, gender, date of death, cause of death, autopsy report, and storage location.
The "autopsy_examiners" table contains information about the medical examiners who perform autopsies on the bodies, including their name, specialization, date of joining, and contact number.
- User
now generate the queries too
- Assistant
Here are some example queries for the "bodies" and "autopsy_examiners" tables:
Query 1: Retrieve all records from the "bodies" table.
SELECT * FROM bodies;Query 2: Retrieve the name, age, and cause of death for all bodies in the morgue.
SELECT name, age, cause_of_death FROM bodies;Query 3: Retrieve the name and specialization of all autopsy examiners.
SELECT name, specialization FROM autopsy_examiners;Query 4: Retrieve the storage location of all bodies in the morgue that have not yet undergone an autopsy.
SELECT storage_location FROM bodies WHERE autopsy_report IS NULL;Query 5: Retrieve the names of all bodies in the morgue that have been stored for more than 3 months.
SELECT name FROM bodies WHERE storage_location IN ( SELECT storage_location FROM bodies WHERE DATEDIFF(CURDATE(), date_of_death) > 90 );Query 6: Retrieve the name and contact numbers of all autopsy examiners who specialize in forensic pathology.
SELECT name, contact_number FROM autopsy_examiners WHERE specialization = 'Forensic Pathology';Query 7: Retrieve the total number of bodies in the morgue, grouped by gender.
SELECT gender, COUNT(*) as total_bodies FROM bodies GROUP BY gender;Query 8: Retrieve the average age of all bodies in the morgue.
SELECT AVG(age) as average_age FROM bodies;Query 9: Retrieve the names of all bodies in the morgue that have undergone an autopsy and have been determined to have died of natural causes.
SELECT name FROM bodies WHERE autopsy_report LIKE '%natural causes%';Query 10: Retrieve the name and date of death for all bodies in the morgue that have been stored for more than 6 months and have not undergone an autopsy.
SELECT name, date_of_death FROM bodies WHERE storage_location IN ( SELECT storage_location FROM bodies WHERE DATEDIFF(CURDATE(), date_of_death) > 180 AND autopsy_report IS NULL ); - User
Insert 3 to 4 records in it. Then create 4 different views on the four different queries. The queries must consist of the following: A query that uses SQL operators. A query that uses aggregate function(s). A query that uses joins. A query that uses sub-queries. Also, write a query that deletes a view.
remember to use pakistani names when you insert
- Assistant
Sure, here are some sample records and queries for the "bodies" and "autopsy_examiners" tables:
Table 1: bodies
id name age gender date_of_death cause_of_death autopsy_report storage_location 1 Ahmed Ali 34 Male 2021-01-25 Heart Attack Died of natural causes Freezer A 2 Aisha Javed 22 Female 2021-03-03 Car Accident Pending autopsy report Freezer B 3 Sarmad Khan 45 Male 2021-05-08 Gunshot Wound to the Chest Completed autopsy report Freezer C 4 Mehak Shah 55 Female 2021-06-02 Brain Hemorrhage Died of natural causes Freezer D Table 2: autopsy_examiners
id name specialization date_of_joining contact_number 1 Dr. Madiha Ali Forensic Pathology 2021-01-01 <PRESIDIO_ANONYMIZED_PHONE_NUMBER> 2 Dr. Faheem Azam Neuropathology 2021-02-15 <PRESIDIO_ANONYMIZED_PHONE_NUMBER> 3 Dr. Ayesha Aziz Forensic Anthropology 2021-03-20 <PRESIDIO_ANONYMIZED_PHONE_NUMBER> 4 Dr. Kamran Ali Forensic Toxicology 2021-05-10 <PRESIDIO_ANONYMIZED_PHONE_NUMBER> SQL Operator Query:
SELECT * FROM bodies WHERE age >= 45;
Aggregate Function Query:
SELECT AVG(age) as average_age FROM bodies;
Join Query:
SELECT b.name, ae.specialization FROM bodies b INNER JOIN autopsy_examiners ae ON ae.id = 1;
Sub-query Query:
SELECT name, cause_of_death FROM bodies WHERE id IN (SELECT id FROM bodies WHERE age > (SELECT AVG(age) FROM bodies));
View 1: bodies_above_average_age
CREATE VIEW bodies_above_average_age AS SELECT * FROM bodies WHERE age >= (SELECT AVG(age) FROM bodies);
View 2: total_bodies_by_gender
CREATE VIEW total_bodies_by_gender AS SELECT gender, COUNT(*) as total_bodies FROM bodies GROUP BY gender;
View 3: completed_autopsy_reports
CREATE VIEW completed_autopsy_reports AS SELECT name FROM bodies WHERE autopsy_report IS NOT NULL;
View 4: autopsy_examiners_with_contact_number
CREATE VIEW autopsy_examiners_with_contact_number AS SELECT name, contact_number FROM autopsy_examiners WHERE contact_number IS NOT NULL;
Query to delete a view:
DROP VIEW bodies_above_average_age;
- User
give me the sql code for the insert queries, use names like musa,abdu,ahmed,moiz
- Assistant
Sure, here are the SQL code for insert queries:
Insert queries for "bodies" table:
INSERT INTO bodies (id, name, age, gender, date_of_death, cause_of_death, autopsy_report, storage_location) VALUES (1, 'Musa Ali', 20, 'Male', '2021-07-01', 'Drowning', NULL, 'Freezer A'), (2, 'Abdu Rehman', 18, 'Male', '2021-07-05', 'Poisoning', NULL, 'Freezer B'), (3, 'Ahmed Raza', 35, 'Male', '2021-07-10', 'Gunshot Wound to the Head', NULL, 'Freezer C'), (4, 'Moiz Ahmed', 27, 'Male', '2021-07-15', 'Natural Causes', NULL, 'Freezer D');Insert queries for "autopsy_examiners" table:
INSERT INTO autopsy_examiners (id, name, specialization, date_of_joining, contact_number) VALUES (1, 'Dr. Sarah Khan', 'Forensic Pathology', '2021-06-01', '+92-317-1234567'), (2, 'Dr. Farhan Ahmed', 'Forensic Medicine', '2021-06-05', '+92-331-2345678'), (3, 'Dr. Zainab Malik', 'Forensic Psychiatry', '2021-06-10', NULL), (4, 'Dr. Bilal Qureshi', 'Forensic Pathology', '2021-06-15', '+92-335-4567890');I have used Pakistani names as instructed.