Related questions
Please help with the following: Using oracle sql live or sql developer
what sql statements could be use to find the following: A simple
-
- List number of patients that do not have a phone number.
-
How many drugs with price < 40$?
sql doc below
Begin
for c in (select table_name from user_tables) loop
execute immediate ('drop table '||c.table_name||' cascade constraints');
end loop;
End;
/
/* */
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';
CREATE TABLE DOCTOR (
DOC_ID varchar2(5) PRIMARY KEY,
DOC_LNAME varchar2(15),
DOC_FNAME varchar2(15),
DOC_INITIAL varchar2(1),
DOC_SPECIALTY varchar2(15)
);
INSERT INTO DOCTOR VALUES('29827','Sanchez','Julio','J','Dermatology');
INSERT INTO DOCTOR VALUES('32445','Jorgensen','Annelise','G','Neurology');
INSERT INTO DOCTOR VALUES('33456','Korenski','Anatoly','A','Urology');
INSERT INTO DOCTOR VALUES('33989','LeGrande','George','','Pediatrics');
INSERT INTO DOCTOR VALUES('34409','Washington','Dennis','F','Orthopaedics');
INSERT INTO DOCTOR VALUES('36221','McPherson','Katye','H','Dermatology');
INSERT INTO DOCTOR VALUES('36712','Dreifag','Herman','G','Psychiatry');
INSERT INTO DOCTOR VALUES('38995','Minh','Tran','','Neurology');
INSERT INTO DOCTOR VALUES('40004','Chin','Ming','D','Orthopaedics');
INSERT INTO DOCTOR VALUES('40028','Feinstein','Denise','L','Gynecology');
/* -- */
CREATE TABLE DRUG (
DRUG_CODE varchar2(5) PRIMARY KEY,
DRUG_NAME varchar2(25),
DRUG_PRICE number,
DRUG_MANU_DATE DATE
);
INSERT INTO DRUG VALUES('AF15','Afgapan-15','25','10/12/2019');
INSERT INTO DRUG VALUES('AF25','Afgapan-25','35','12/12/2018');
INSERT INTO DRUG VALUES('DRO','Droalene Chloride','111.89','10/12/2017');
INSERT INTO DRUG VALUES('DRZ','Druzocholar Cryptolene','18.99','11/12/2019');
INSERT INTO DRUG VALUES('KO15','Koliabar Oxyhexalene','65.75','11/12/2020');
INSERT INTO DRUG VALUES('OLE','Oleander-Drizapan','123.95','11/12/2020');
INSERT INTO DRUG VALUES('TRYP','Tryptolac Heptadimetric','79.45','11/12/2020');
/* -- */
CREATE TABLE PATIENT (
PAT_NUM varchar2(5)PRIMARY KEY,
PAT_TITLE varchar2(4),
PAT_LNAME varchar2(15),
PAT_FNAME varchar2(15),
PAT_INITIAL varchar2(1),
PAT_DOB date,
PAT_AREACODE varchar2(3),
PAT_PHONE varchar2(8)
);
INSERT INTO PATIENT VALUES('100','Mr.','Kolmycz','George','D','6/15/1942','615','324-5456');
INSERT INTO PATIENT VALUES('101','Ms.','Lewis','Rhonda','G','3/19/2005','615','324-4472');
INSERT INTO PATIENT VALUES('102','Mr.','VanDam','Rhett','','11/14/1958','901',null);
INSERT INTO PATIENT VALUES('103','Ms.','Jones','Anne','M','10/16/1974','615','898-3456');
INSERT INTO PATIENT VALUES('104','Mr.','Lange','John','P','11/8/1971','901',null);
INSERT INTO PATIENT VALUES('105','Mr.','Williams','Robert','D','3/14/1975','615','890-3220');
INSERT INTO PATIENT VALUES('106','Mrs.','Smith','Jeanine','K','2/12/2003','615','324-7883');
INSERT INTO PATIENT VALUES('107','Mr.','Diante','Jorge','D','8/21/1974','615','890-4567');
INSERT INTO PATIENT VALUES('108','Mr.','Wiesenbach','Paul','R','2/14/1966','615','897-4358');
INSERT INTO PATIENT VALUES('109','Mr.','Smith','George','K','6/18/1961','901','504-3339');
INSERT INTO PATIENT VALUES('110','Mrs.','Genkazi','Leighla','W','5/19/1970','901','569-0093');
INSERT INTO PATIENT VALUES('111','Mr.','Washington','Rupert','E','1/3/1966','615','890-4925');
INSERT INTO PATIENT VALUES('112','Mr.','Johnson','Edward','E','5/14/1961','615','898-4387');
INSERT INTO PATIENT VALUES('113','Ms.','Smythe','Melanie','P','9/15/1970','615','324-9006');
INSERT INTO PATIENT VALUES('114','Ms.','Brandon','Marie','G','11/2/1932','901','882-0845');
INSERT INTO PATIENT VALUES('115','Mrs.','Saranda','Hermine','R','7/25/1972','615',null);
INSERT INTO PATIENT VALUES('116','Mr.','Smith','George','A','11/8/1965','615','890-2984');
/* -- */
CREATE TABLE PRESCRIPTION (
DOC_ID varchar2(5),
PAT_NUM varchar2(5),
DRUG_CODE varchar2(5),
PRES_DOSAGE varchar2(50),
PRES_DATE date,
PRIMARY KEY (DOC_ID , PAT_NUM, DRUG_CODE)
);
INSERT INTO PRESCRIPTION VALUES('32445','102','DRZ','2 tablets every four hours -- 50 tablets total','11/12/2012');
INSERT INTO PRESCRIPTION VALUES('34409','101','KO15','1 tablet every six hours -- 30 tablets total','11/15/2012');
INSERT INTO PRESCRIPTION VALUES('32445','113','OLE','1 teaspoon with each meal -- 250 ml total','11/14/2012');
INSERT INTO PRESCRIPTION VALUES('36221','109','DRO','2 tablets with every meal -- 60 tablets total','11/14/2018');
INSERT INTO PRESCRIPTION VALUES('38995','107','KO15','1 tablet every six hours -- 30 tablets total','11/10/2019');
INSERT INTO PRESCRIPTION VALUES('40028','107','OLE','2 tablet every six hours -- 30 tablets total','11/10/2018');
INSERT INTO PRESCRIPTION VALUES('32445','116','KO15','2 tablets every four hours -- 50 tablets total','11/01/2012');
Trending nowThis is a popular solution!
Step by stepSolved in 2 steps with 2 images
- T-SQL procedure for MICROSOFT SQL SERVER A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these values in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT. HINT use cursor instructions as a template for the problem. Instructions goes as follows CREATE PROCEDURE usp_DISP_REP_CUST @repnum char(2) AS DECLARE@custnum char(3) DECLARE@custname char(35) DECLARE mycursor CURSOR READ_ONLY FOR SELECT CUSTOMER_NUM, CUSTOMER_NAME FROM CUSTOMER WHERE REP_NUM = @repnum OPEN mycursor FETCH NEXT FROM mycursor INTO @custnum, @custname WHILE @@FETCH_STATUS=0 BEGIN PRINT@custnum+' '+@custname FETCH NEXT FROM mycursor INTO @custnum, @custname END CLOSE mycursor DEALLOCATE mycursorarrow_forwardSQL CODE FOR For the players who show up in Batting, Bowling, and Fielding tables, create a list that shows their names, runs they have scored, wickets they have taken, and catches they have taken? table is in picture (bowling table is same as batting and fielding )arrow_forwardSQL: Dog DataIn each question below, you will define a new table based on the following tables.CREATE TABLE parents ASSELECT "abraham" AS parent, "barack" AS child UNIONSELECT "abraham" , "clinton" UNIONSELECT "delano" , "herbert" UNIONSELECT "fillmore" , "abraham" UNIONSELECT "fillmore" , "delano" UNIONSELECT "fillmore" , "grover" UNIONSELECT "eisenhower" , "fillmore";CREATE TABLE dogs ASSELECT "abraham" AS name, "long" AS fur, 26 AS height UNIONSELECT "barack" , "short" , 52 UNIONSELECT "clinton" , "long" , 47 UNIONSELECT "delano" , "long" , 46 UNIONSELECT "eisenhower" , "short" , 35 UNIONSELECT "fillmore" , "curly" , 32 UNIONSELECT "grover" , "short" , 28 UNIONSELECT "herbert" , "curly" , 31;CREATE TABLE sizes ASSELECT "toy" AS size, 24 AS min, 28 AS max UNIONSELECT "mini" , 28 , 35 UNIONSELECT "medium" , 35 , 45 UNIONSELECT "standard" , 45 , 60; Q2: By Parent HeightCreate a table by_parent_height that has a column of the names of all dogs that have a parent,ordered by the height…arrow_forward
- Trying to write SQL statements for these shown here and having issues with them working. Explanation on this would be appreciated.arrow_forwardPlease help with the following: Using oracle sql live or sql developer what sql statements could be use to find the following: A simple database composed of the following tables: PATIENT, DOCTOR, DRUG, and PRESCRIPTION. Please explore the database and then write the appropriate SQL command. List doctor’s details where speciality is either Dermatology or Neurology. (Use INoperator). List the results by the first name in ascending order and the last name indescending order. Find all patients rows whose first names include ‘George’ (Assume case sensitivity). sql doc below Beginfor c in (select table_name from user_tables) loopexecute immediate ('drop table '||c.table_name||' cascade constraints');end loop;End;//* */ ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';CREATE TABLE DOCTOR (DOC_ID varchar2(5) PRIMARY KEY,DOC_LNAME varchar2(15),DOC_FNAME varchar2(15),DOC_INITIAL varchar2(1),DOC_SPECIALTY varchar2(15));INSERT INTO DOCTOR…arrow_forwardAdjust the following information so that1) all users on the system are able to run the SQL Executable and 2) multiple users (such as a team of software engineers) have the power to modify the SQL Executable Introduce any new groups that are needed and assign them group IDs USERS SQL Manager UID: 3 GROUPS SQL Users GID: 5 FILESDatabase File Owner 3 Group NA owner: rw group: - world: - setUID: 0 setGID: 0 sticky: 0 SQL Executable Owner 3 Group 5 owner: rwx group: r-x world: - setUID: 1 setGID: 0 sticky: 0arrow_forward
- You have the following tables: MANAGERS (MANAGER_ID, LAST_NAME, FIRST_NAME, DEPARTMENT) ACCOUNTS (ACCOUNT_NUMBER, MANAGER_ID, CUST_ID, BALANCE) CUSTOMERS (CUST_ID, LAST_NAME, FIRST_NAME) Write a SQL statement that lists account number, balance, MANAGER’s last name, CUSTOMER ID, and CUSTOMER’s last name for every account in the ACCOUNTS table.arrow_forwardIn what situations might the SOUNDEX function be useful in SQL when querying text data?arrow_forwardUsing DML SQL, write the appropriate SQL query for each of the following:a) Insert A new customer with the following information:(Name: Ali, SSN: 1111, Address: Riyadh, Phone: 05555)b) Delete all the loans for customer with SSN: 2222c) Select all customer who got loans more than 250000 SAR, print the customers SSN, named) Print all the Accounts in Bank: ALBILAD and in branch no: 5arrow_forward
- Computer Networking: A Top-Down Approach (7th Edi...Computer EngineeringISBN:9780133594140Author:James Kurose, Keith RossPublisher:PEARSON
Computer Organization and Design MIPS Edition, Fi...Computer EngineeringISBN:9780124077263Author:David A. Patterson, John L. HennessyPublisher:Elsevier Science
Network+ Guide to Networks (MindTap Course List)Computer EngineeringISBN:9781337569330Author:Jill West, Tamara Dean, Jean AndrewsPublisher:Cengage Learning 
Concepts of Database ManagementComputer EngineeringISBN:9781337093422Author:Joy L. Starks, Philip J. Pratt, Mary Z. LastPublisher:Cengage Learning
Prelude to ProgrammingComputer EngineeringISBN:9780133750423Author:VENIT, StewartPublisher:Pearson Education
Sc Business Data Communications and Networking, T...Computer EngineeringISBN:9781119368830Author:FITZGERALDPublisher:WILEY