Question

In: Computer Science

Please convert this to work in Oracle SQL: CREATE TABLE GUEST ( SSN number(10), name varchar2(20),...

Please convert this to work in Oracle SQL:

CREATE TABLE GUEST
(
SSN number(10),
name varchar2(20),
DOB date,
roomnumber number(10)
);
INSERT INTO guest VALUES (1,'Lucy','2019-1-8',301);
INSERT INTO guest VALUES (2,'John','2019-11-18',302);
INSERT INTO guest VALUES (3,'Smith','2019-2-6',303);
INSERT INTO guest VALUES (4,'Tom','2019-2-7',304);
INSERT INTO guest VALUES (5,'Harry','2019-10-9',305);
)

CREATE TABLE INVENTORY
(
itemnumber number(10),
itemneeded number(10),
iteminstock number(10),
dateofpurchase date
);
INSERT INTO INVENTORY VALUES (21,10,60,'2019-1-8');
INSERT INTO INVENTORY VALUES (22,20,70,'2019-1-11');
INSERT INTO INVENTORY VALUES (23,30,75,'2019-2-4');
INSERT INTO INVENTORY VALUES (24,20,55,'2019-3-7');
INSERT INTO INVENTORY VALUES (25,40,65,'2019-5-6');
)

CREATE TABLE VOLUNTEER
(
ssn number(10),
name varchar2(20),
dob date,
skills varchar2(20)
);
INSERT INTO VOLUNTEER VALUES (31,'Tim','2019-2-7','Beginner');
INSERT INTO VOLUNTEER VALUES (32,'Lucy','2019-3-11','Advanced');
INSERT INTO VOLUNTEER VALUES (33,'Jack','2019-1-2','Beginner');
INSERT INTO VOLUNTEER VALUES (34,'Alice','2019-4-18','Advanced');
INSERT INTO VOLUNTEER VALUES (35,'Bob','2019-3-6','Beginner');

CREATE TABLE SCHEDULE
(
volunteername varchar2(20),
volunteerdaysofwork number(10),
volunteerhours number(10),
guestname varchar2(20),
gueststaydate date
);

INSERT INTO SCHEDULE VALUES ('John',10,5,'Alex','2019-11-5');
INSERT INTO SCHEDULE VALUES ('Lucy',15,4,'Carolin','2019-11-6');
INSERT INTO SCHEDULE VALUES ('Alice',20,3,'Lisa','2019-11-7');
INSERT INTO SCHEDULE VALUES ('Bob',30,4,'Hayden','2019-11-7');
INSERT INTO SCHEDULE VALUES ('TIm',5,2,'Mathew','2019-11-9');

)

CREATE TABLE DONOR
(
donationnumber number(10),
donationamount number(10),
donationdate date,
donorsname varchar2(20)
);

INSERT INTO DONOR VALUES (41,500,'2019-10-3','Alex');
INSERT INTO DONOR VALUES (42,2500,'2019-9-15','Alex');
INSERT INTO DONOR VALUES (43,5200,'2019-7-25','Alex');
INSERT INTO DONOR VALUES (44,700,'2019-11-5','Alex');
INSERT INTO DONOR VALUES (45,2300,'2019-10-6','Alex');


)

Solutions

Expert Solution

Note: I have created above tables in Oracle and inserted the rows into table using SQL queries. The execution screenshots are as follows:


Related Solutions

USE ORACLE - SQL List the name and salary of employees who work for division 3....
USE ORACLE - SQL List the name and salary of employees who work for division 3. List the name of project whose budget is between 5000-7000 List the total number of employee whose initial of name is 's'. (hint, using LIKE operator and wildcard character) List the total number of employee whose initial of name is NOT 's' for each division, including division ID List the total project budget for each division, including division ID. List the ID of the...
Create following table. CREATE TABLE Registration (Reg_ID number(5), Name Varchar2(20), Address Varchar2(20), create_date date, created_by varchar2(10)...
Create following table. CREATE TABLE Registration (Reg_ID number(5), Name Varchar2(20), Address Varchar2(20), create_date date, created_by varchar2(10) ); Create an audit trial report on Employee table for all insert, update and delete operations on given table. You have to create audit table first with Current Date, Operation and User to record the information.
Create a table ‘StudentInfo’ with following fields: ID First Name Last Name SSN Date of Birth...
Create a table ‘StudentInfo’ with following fields: ID First Name Last Name SSN Date of Birth Create a table ‘ClassInfo’ table: ID Class Name Class Description Create a table ‘RegisteredClasses’ table: StudentID ClassID The RegisteredClasses table should have a foreign key relationship to StudentInfo and ClassInfo tables for the respective IDs. Also the IDs in StudentInfo and ClassInfo need to be primary keys. When you submit the file your email should also contain the following SQL Queries: Query to show...
IN SQL Create a table called product containing a product no, company name, model_no,product name. What...
IN SQL Create a table called product containing a product no, company name, model_no,product name. What is my primary key? Which datatypes should I use?Pleasesubmit a printout of the commands used for creating the above table and the resultsof your queries/commands
USE SQL CREATE TABLE IF NOT EXISTS football_games ( visitor_name VARCHAR(30),       /* Name of the visiting...
USE SQL CREATE TABLE IF NOT EXISTS football_games ( visitor_name VARCHAR(30),       /* Name of the visiting team                     */ home_score SMALLINT NOT NULL,   /* Final score of the game for the Buffs         */ visitor_score SMALLINT NOT NULL,/* Final score of the game for the visiting team */ game_date DATE NOT NULL,        /* Date of the game                              */ players INT[] NOT NULL,         /* This array consists of the football player ids (basically a foreign key to the football_player.id) */ PRIMARY KEY(visitor_name, game_date)...
-- Creating table ProjDept: create table ProjDept ( ProjDeptID NUMBER(10) primary key, ProjDeptName varchar2(20), OfficeLocation varchar2(20),...
-- Creating table ProjDept: create table ProjDept ( ProjDeptID NUMBER(10) primary key, ProjDeptName varchar2(20), OfficeLocation varchar2(20), PhoneNumber varchar2(20) ); INSERT INTO ProjDept (ProjDeptID, ProjDeptName, OfficeLocation, PhoneNumber) VALUES (1001, 'Accounting','ITCC01-400','888-285-8100'); (2001, 'Human Resources','ITCC01-200','888-285-8100'); (3001, 'Marketing','ITCC02-300','888-285-8100'); (4001, 'Information Techn','ITCC02-100','888-285-8100'); (5001, 'Legal','ITCC01-100','888-285-8100'); -- Creating table Employee: create table Employee( EmployeeID NUMBER(10) primary key, FirstName varchar2(20), LastName varchar2(20), ProjDeptID NUMBER(10), PhoneNumber varchar2(20) ); INSERT INTO Employee (EmployeeID, FirstName, LastName, ProjDeptID, PhoneNumber, Email) VALUES (10, 'Mark','Columbus',1001, '888-285-8101', '[email protected]'); (29, 'Elvin','Wahl', 2001, '888-285-8201', '[email protected]'); (38, 'Taylor','Noel',...
(SQL Coding) Create a view based on the Job History table. Name the view: view_job_history. Select...
(SQL Coding) Create a view based on the Job History table. Name the view: view_job_history. Select all columns to be included in the view. Add a WHERE clause to restrict the employee_id to be greater than 150. Add the WITH READ ONLY option. Show me the code used to create the view and the results of a select statement on the view.
ORACLE TASK 2-2 USING A FOR LOOP Create a PL/SQL block using a FOR loop to...
ORACLE TASK 2-2 USING A FOR LOOP Create a PL/SQL block using a FOR loop to generate a payment schedule for a donor’s pledge, which is to be paid monthly in equal increments. Values variable for the block are starting payment due date, monthly payment amount and number of total monthly payments for the pledge. The list that’s generated should display a line for each monthly payment showing payment number, date due, payment amount, and donation balance (remaining amount of...
Please implement this in Oracle sql 2.) Write a SELECT statement that answers this question: What...
Please implement this in Oracle sql 2.) Write a SELECT statement that answers this question: What is the total amount ordered for each product? Return these columns: The product name from the Products table The total amount for each product in the Order_Items (Hint: You can calculate the total amount by subtracting the discount amount from the item price and then multiplying it by the quantity) Use the ROLLUP operator to include a row that gives the grand total.
Oracle - Create a procedure that accepts product ID as a parameter and returns the name...
Oracle - Create a procedure that accepts product ID as a parameter and returns the name of the product from ProductTable table. Add exception handling to catch if product ID is not in the table. Table to use: CREATE TABLE ProductTable(     ProductID INTEGER NOT NULL primary key,     ProductName VARCHAR(50) NOT NULL,     ListPrice NUMBER(10,2),     Category INTEGER NOT NULL ); / INSERT INTO ProductTable VALUES(299,'Chest',99.99,10); INSERT INTO ProductTable VALUES(300,'Wave Cruiser',49.99,11); INSERT INTO ProductTable VALUES(301,'Megaland Play Tent',59.99,11); INSERT INTO...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT