ZuoTi.Pro
Question

Provide the syntax answers for the following script, doesn't matter what table or columns.

1) select* from one table 2) select *with a where clause from one table 3) select looking for null values 4) select looking for non-null values 5) select counting the number of rows in a table 6) select count of a specific column 7) select using a single sort key 8) select using multiple sort keys 9) select the numerical average for an entire table 10) select the numerical average for all groups of data within a table.


CREATE TABLE customer
(c_id NUMBER(5),
c_last VARCHAR2(30),
c_first VARCHAR2(30),
c_mi CHAR(1),
c_birthdate DATE,
c_address VARCHAR2(30),
c_city VARCHAR2(30),
c_state CHAR(2),
c_zip VARCHAR2(10),
c_dphone VARCHAR2(10),
c_ephone VARCHAR2(10),
c_userid VARCHAR2(50),
c_password VARCHAR2(15),
CONSTRAINT customer_c_id_pk PRIMARY KEY (c_id));

CREATE TABLE order_source
(os_id NUMBER(3),
os_desc VARCHAR2(30),
CONSTRAINT order_source_os_id_pk PRIMARY KEY(os_id));

CREATE TABLE orders
(o_id NUMBER(8),
o_date DATE,
o_methpmt VARCHAR2(10),
c_id NUMBER(5),
os_id NUMBER(3),
CONSTRAINT orders_o_id_pk PRIMARY KEY (o_id),
CONSTRAINT orders_c_id_fk FOREIGN KEY (c_id) REFERENCES customer(c_id),
CONSTRAINT orders_os_id_fk FOREIGN KEY (os_id) REFERENCES order_source(os_id));

CREATE TABLE category
(cat_id NUMBER(2),
cat_desc VARCHAR2(20),
CONSTRAINT category_cat_id_pk PRIMARY KEY (cat_id));

CREATE TABLE item
(item_id NUMBER(8),
item_desc VARCHAR2(30),
cat_id NUMBER(2),
item_image BLOB,
CONSTRAINT item_item_id_pk PRIMARY KEY (item_id),
CONSTRAINT item_cat_id_fk FOREIGN KEY (cat_id) REFERENCES category(cat_id));

CREATE TABLE color
(color VARCHAR2(20),
CONSTRAINT color_color_pk PRIMARY KEY (color));

CREATE TABLE inventory
(inv_id NUMBER(10),
item_id NUMBER(8),
color VARCHAR2(20),
inv_size VARCHAR2(10),
inv_price NUMBER(6,2),
inv_qoh NUMBER(4),
CONSTRAINT inventory_inv_id_pk PRIMARY KEY (inv_id),
CONSTRAINT inventory_item_id_fk FOREIGN KEY (item_id) REFERENCES item(item_id),
CONSTRAINT inventory_color_fk FOREIGN KEY (color) REFERENCES color(color));

CREATE TABLE shipment
(ship_id NUMBER(10),
ship_date_expected DATE,
CONSTRAINT shipment_ship_id_pk PRIMARY KEY (ship_id));

CREATE TABLE shipment_line
(ship_id NUMBER(10),
inv_id NUMBER(10),
sl_quantity NUMBER(4),
sl_date_received DATE,
CONSTRAINT shipment_line_ship_id_fk FOREIGN KEY (ship_id) REFERENCES shipment(ship_id),
CONSTRAINT shipment_line_inv_id_fk FOREIGN KEY (inv_id) REFERENCES inventory(inv_id),
CONSTRAINT shipment_line_shipid_invid_pk PRIMARY KEY(ship_id, inv_id));

CREATE TABLE order_line
(o_id NUMBER(8),
inv_id NUMBER(10),
ol_quantity NUMBER(4) NOT NULL,
CONSTRAINT order_line_o_id_fk FOREIGN KEY (o_id) REFERENCES orders(o_id),
CONSTRAINT order_line_inv_id_fk FOREIGN KEY (inv_id) REFERENCES inventory(inv_id),
CONSTRAINT order_line_oid_invid_pk PRIMARY KEY (o_id, inv_id));


--- inserting records into CUSTOMER
INSERT INTO CUSTOMER VALUES
(1, 'Harris', 'Paula', 'E', to_date('04/09/1953', 'mm/dd/yyyy'), '1156 Water Street, Apt. #3', 'Osseo', 'WI',
'54705', '7155558943', '7155559035', 'harrispe', 'asdfjk');

INSERT INTO CUSTOMER VALUES
(2, 'Garcia', 'Maria', 'H', to_date('07/14/1958', 'mm/dd/yyyy'), '2211 Pine Drive', 'Radisson', 'WI',
'54867', '7155558332', '7155558332', 'garciamm', '12345');

INSERT INTO CUSTOMER VALUES
(3, 'Miller', 'Lee', NULL, to_date('01/05/1936', 'mm/dd/yyyy'), '699 Pluto St. NW', 'Silver Lake', 'WI',
'53821', '7155554978', '7155559002', 'millerl', 'zxcvb');

INSERT INTO CUSTOMER VALUES
(4, 'Chang', 'Alissa', 'R', to_date('10/01/1976', 'mm/dd/yyyy'), '987 Durham Rd.', 'Apple Valley', 'MN',
'55712', '7155557651', '7155550087', 'changar', 'qwerui');

INSERT INTO CUSTOMER VALUES
(5, 'Edwards', 'Mitch', 'M', to_date('11/20/1986', 'mm/dd/yyyy'), '4204 Garner Street', 'Washburn', 'WI',
'54891', '7155558243', '7155556975', 'edwardsmm', 'qwerty');

INSERT INTO CUSTOMER VALUES
(6, 'Nelson', 'Kyle', 'E', to_date('12/04/1984', 'mm/dd/yyyy'), '232 Echo Rd.', 'Minnetonka', 'MN',
'55438', '7151113333', '7155552222', 'nelsonke', 'clever');

--- inserting records into ORDER_SOURCE
INSERT INTO order_source VALUES (1, 'Winter 2005');
INSERT INTO order_source VALUES (2, 'Spring 2006');
INSERT INTO order_source VALUES (3, 'Summer 2006');
INSERT INTO order_source VALUES (4, 'Outdoor 2006');
INSERT INTO order_source VALUES (5, 'Children''s 2006');
INSERT INTO order_source VALUES (6, 'Web Site');

--- inserting records into orders
INSERT INTO orders VALUES
(1, TO_DATE('05/29/2006', 'MM/DD/YYYY'), 'CC', 1, 2);

INSERT INTO orders VALUES
(2, TO_DATE('05/29/2006', 'MM/DD/YYYY'), 'CC', 5, 6);

INSERT INTO orders VALUES
(3, TO_DATE('05/31/2006', 'MM/DD/YYYY'), 'CHECK', 2, 2);

INSERT INTO orders VALUES
(4, TO_DATE('05/31/2006', 'MM/DD/YYYY'), 'CC', 3, 3);

INSERT INTO orders VALUES
(5, TO_DATE('06/01/2006', 'MM/DD/YYYY'), 'CC', 4, 6);

INSERT INTO orders VALUES
(6, TO_DATE('06/01/2006', 'MM/DD/YYYY'), 'CC', 4, 3);

--- inserting records into CATEGORY
INSERT INTO category VALUES (1, 'Women''s Clothing');
INSERT INTO category VALUES (2, 'Children''s Clothing');
INSERT INTO category VALUES (3, 'Men''s Clothing');
INSERT INTO category VALUES (4, 'Outdoor Gear');

--- inserting records into ITEM
INSERT INTO item VALUES
(1, 'Men''s Expedition Parka', 3, EMPTY_BLOB());

INSERT INTO item VALUES
(2, '3-Season Tent', 4, EMPTY_BLOB());

INSERT INTO item VALUES
(3, 'Women''s Hiking Shorts', 1, EMPTY_BLOB());

INSERT INTO item VALUES
(4, 'Women''s Fleece Pullover', 1, EMPTY_BLOB());

INSERT INTO item VALUES
(5, 'Children''s Beachcomber Sandals', 2, EMPTY_BLOB());

INSERT INTO item VALUES
(6, 'Boy''s Surf Shorts', 2, EMPTY_BLOB());

INSERT INTO item VALUES
(7, 'Girl''s Soccer Tee', 2, EMPTY_BLOB());

--- inserting records into COLOR
INSERT INTO color VALUES ('Sky Blue');
INSERT INTO color VALUES ('Light Grey');
INSERT INTO color VALUES ('Khaki');
INSERT INTO color VALUES ('Navy');
INSERT INTO color VALUES ('Royal');
INSERT INTO color VALUES ('Eggplant');
INSERT INTO color VALUES ('Blue');
INSERT INTO color VALUES ('Red');
INSERT INTO color VALUES ('Spruce');
INSERT INTO color VALUES ('Turquoise');
INSERT INTO color VALUES ('Bright Pink');
INSERT INTO color VALUES ('White');

--- inserting records into INVENTORY
INSERT INTO inventory VALUES
(1, 2, 'Sky Blue', NULL, 259.99, 16);

INSERT INTO inventory VALUES
(2, 2, 'Light Grey', NULL, 259.99, 12);

INSERT INTO inventory VALUES
(3, 3, 'Khaki', 'S', 29.95, 150);

INSERT INTO inventory VALUES
(4, 3, 'Khaki', 'M', 29.95, 147);

INSERT INTO inventory VALUES
(5, 3, 'Khaki', 'L', 29.95, 0);

INSERT INTO inventory VALUES
(6, 3, 'Navy', 'S', 29.95, 139);

INSERT INTO inventory VALUES
(7, 3, 'Navy', 'M', 29.95, 137);

INSERT INTO inventory VALUES
(8, 3, 'Navy', 'L', 29.95, 115);

INSERT INTO inventory VALUES
(9, 4, 'Eggplant', 'S', 59.95, 135);

INSERT INTO inventory VALUES
(10, 4, 'Eggplant', 'M', 59.95, 168);

INSERT INTO inventory VALUES
(11, 4, 'Eggplant', 'L', 59.95, 187);

INSERT INTO inventory VALUES
(12, 4, 'Royal', 'S', 59.95, 0);

INSERT INTO inventory VALUES
(13, 4, 'Royal', 'M', 59.95, 124);

INSERT INTO inventory VALUES
(14, 4, 'Royal', 'L', 59.95, 112);

INSERT INTO inventory VALUES
(15, 5, 'Turquoise', '10', 15.99, 121);

INSERT INTO inventory VALUES
(16, 5, 'Turquoise', '11', 15.99, 111);

INSERT INTO inventory VALUES
(17, 5, 'Turquoise', '12', 15.99, 113);

INSERT INTO inventory VALUES
(18, 5, 'Turquoise', '1', 15.99, 121);

INSERT INTO inventory VALUES
(19, 5, 'Bright Pink', '10', 15.99, 148);

INSERT INTO inventory VALUES
(20, 5, 'Bright Pink', '11', 15.99, 137);

INSERT INTO inventory VALUES
(21, 5, 'Bright Pink', '12', 15.99, 134);

INSERT INTO inventory VALUES
(22, 5, 'Bright Pink', '1', 15.99, 123);

INSERT INTO inventory VALUES
(23, 1, 'Spruce', 'S', 199.95, 114);

INSERT INTO inventory VALUES
(24, 1, 'Spruce', 'M',199.95, 17);

INSERT INTO inventory VALUES
(25, 1, 'Spruce', 'L', 209.95, 0);

INSERT INTO inventory VALUES
(26, 1, 'Spruce', 'XL', 209.95, 12);

INSERT INTO inventory VALUES
(27, 6, 'Blue', 'S', 15.95, 50);

INSERT INTO inventory VALUES
(28, 6, 'Blue', 'M', 15.95, 100);

INSERT INTO inventory VALUES
(29, 6, 'Blue', 'L', 15.95, 100);

INSERT INTO inventory VALUES
(30, 7, 'White', 'S', 19.99, 100);

INSERT INTO inventory VALUES
(31, 7, 'White', 'M', 19.99, 100);

INSERT INTO inventory VALUES
(32, 7, 'White', 'L', 19.99, 100);

--inserting records into SHIPMENT

INSERT INTO shipment VALUES
(1, TO_DATE('09/15/2006', 'MM/DD/YYYY'));

INSERT INTO shipment VALUES
(2, TO_DATE('11/15/2006', 'MM/DD/YYYY'));

INSERT INTO shipment VALUES
(3, TO_DATE('06/25/2006', 'MM/DD/YYYY'));

INSERT INTO shipment VALUES
(4, TO_DATE('06/25/2006', 'MM/DD/YYYY'));

INSERT INTO shipment VALUES
(5, TO_DATE('08/15/2006', 'MM/DD/YYYY'));

--inserting records into SHIPMENT_LINE
INSERT INTO shipment_line VALUES
(1, 1, 25, TO_DATE('09/10/2006', 'MM/DD/YYYY'));

INSERT INTO shipment_line VALUES
(1, 2, 25, TO_DATE('09/10/2006', 'MM/DD/YYYY'));

INSERT INTO shipment_line VALUES
(2, 2, 25, NULL);

INSERT INTO shipment_line VALUES
(3, 5, 200, NULL);

INSERT INTO shipment_line VALUES
(3, 6, 200, NULL);

INSERT INTO shipment_line VALUES
(3, 7, 200, NULL);

INSERT INTO shipment_line VALUES
(4, 12, 100, TO_DATE('08/15/2006', 'MM/DD/YYYY'));

INSERT INTO shipment_line VALUES
(4, 13, 100, TO_DATE('08/25/2006', 'MM/DD/YYYY'));

INSERT INTO shipment_line VALUES
(5, 23, 50, TO_DATE('08/15/2006', 'MM/DD/YYYY'));

INSERT INTO shipment_line VALUES
(5, 24, 100, TO_DATE('08/15/2006', 'MM/DD/YYYY'));

INSERT INTO shipment_line VALUES
(5, 25, 100, TO_DATE('08/15/2006', 'MM/DD/YYYY'));

--- inserting records into ORDER_LINE
INSERT INTO order_line VALUES (1, 1, 1);
INSERT INTO order_line VALUES (1, 14, 2);
INSERT INTO order_line VALUES (2, 19, 1);
INSERT INTO order_line VALUES (3, 24, 1);
INSERT INTO order_line VALUES (3, 26, 1);
INSERT INTO order_line VALUES (4, 12, 2);
INSERT INTO order_line VALUES (5, 8, 1);
INSERT INTO order_line VALUES (5, 13, 1);
INSERT INTO order_line VALUES (6, 2, 1);
INSERT INTO order_line VALUES (6, 7, 3);

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Request Professional Answer

Request Answer!

We need at least 10 more requests to produce the answer.

0 / 10 have requested this problem solution

The more requests, the faster the answer.

Request! (Login Required)

All students who have requested the answer will be notified once they are available.
Know the answer?
Add Answer to:
Provide the syntax answers for the following script, doesn't matter what table or columns. CREATE TABLE...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Similar Homework Help Questions

  • Write a pl/sql block of code that will make a reservation for a customer for any...

    Write a pl/sql block of code that will make a reservation for a customer for any destination. The destination id, number of peolpe going and date will be entered by the user. Your code should calculate the cost and enter the data in the sales table. No destination can have more than 10 people going during the same dates. If the number is greater than 10 raise 'Sold_out' exception and print a message 'Sorry we are sold out for ths...

  • SQL: CREATE STATEMENT For patients whose last name matches the name entered by the user, display...

    SQL: CREATE STATEMENT For patients whose last name matches the name entered by the user, display their first name, phone number, and doctor's name. Note: Try Davis. (TEXT FOR DATABASE BELOW) DROP TABLE PATIENT; DROP TABLE BILLING; DROP TABLE DOCTOR; CREATE TABLE DOCTOR( DOC_ID VARCHAR2(10) NOT NULL, DOC_NAME VARCHAR2(20), DATEHIRED DATE, SALPERMON NUMBER(8), AREA VARCHAR2(20), SUPERVISOR_ID NUMBER(8), CHGPERAPPT NUMBER(8), ANNUAL_BONUS NUMBER(8), PRIMARY KEY (DOC_ID) ); INSERT INTO DOCTOR VALUES('432', 'Harrison', to_date('05-DEC-1994','dd-mon-yyyy'), 12000, 'Pediatrics', 100, 75, 4500); INSERT INTO DOCTOR VALUES('509',...

  • Q2. Retrieve the names of all employees from the employee table to produce output on CSV...

    Q2. Retrieve the names of all employees from the employee table to produce output on CSV format or delimited format with a common delimeter, rather than separete columns. Hint:Put the whole row into a string with a semicolon as the seperator(delimeter) between thecolumns: FORMAT:(fname;minit;lname) Example: EMPLOYEES -------------- James;E;Borg Frank;T;Wong Q3. Write a query to show the employees name from the employee table in this format: first letter of the first name, followed by a dot, a blank, and the full...

  • SQL - create statement for query For each patient, display his or her last name, first...

    SQL - create statement for query For each patient, display his or her last name, first name, and the name of his or her doctor. For each pediatrics patient, display his or her ID and his or her doctor's ID and name. For each doctor, display the name and the name of the doctor's supervisor in alphabetic order of supervisor's name. Include column aliases for clarity. Note: Check for accuracy. For each doctor in one of the two areas entered...

  • I am using oracle sql developer to create some queries to generated reports and it is...

    I am using oracle sql developer to create some queries to generated reports and it is not working. I am not sure how to use count, group by, and order by. Help me fix my query and explain to me how you did, so can do it next time. Also, I have to convert this query to a stored procedure, so can you help me do it too? Here is my query: SELECT COUNT(GUEST.FIRSTNAME), GUEST.FIRSTNAME, GUEST.LASTNAME, GUEST.GUESTTYPE, RESERVATION.RESERVATIONDATE, GUEST.EMAIL, FROM...

  • Query #2:       List the name of the project and total number of hours worked on by...

    Query #2:       List the name of the project and total number of hours worked on by all the employees on this project, also report the number of employees working on each project. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null,...

  • Query 1: Retrieve names of all the projects as well as First and Last name of...

    Query 1: Retrieve names of all the projects as well as First and Last name of managers if they are working on any of these projects. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null, Mgr_start_date date, primary key (dnumber)); CREATE...

  • Oracle only database: DROP TABLE ORDERITEMS; DROP TABLE Orders; DROP TABLE BOOKAUTHOR; DRO...

    oracle only database: DROP TABLE ORDERITEMS; DROP TABLE Orders; DROP TABLE BOOKAUTHOR; DROP TABLE BOOKS; DROP TABLE PROMOTION; DROP TABLE AUTHOR; DROP TABLE CUSTOMERS; DROP TABLE PUBLISHER; CREATE TABLE Customers ( Customer# NUMBER(4), LastName VARCHAR2(10) NOT NULL, FirstName VARCHAR2(10) NOT NULL, Email VARCHAR(40), Address VARCHAR2(20), City VARCHAR2(12), State VARCHAR2(2), Zip VARCHAR2(5), Referred NUMBER(4), Region CHAR(2), CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#) ); INSERT INTO CUSTOMERS VALUES (1001, 'MORALES', 'BONITA', '[email protected]', 'P.O. BOX 651', 'EASTPOINT', 'FL', '32328', NULL, 'SE'); INSERT INTO CUSTOMERS VALUES...

  • Oracle 12c SQL Chapter 12 Determine which orders were shipped to the same state as order...

    Oracle 12c SQL Chapter 12 Determine which orders were shipped to the same state as order 1014. CREATE TABLE Orders (Order# NUMBER(4), Customer# NUMBER(4), OrderDate DATE NOT NULL, ShipDate DATE, ShipStreet VARCHAR2(18), ShipCity VARCHAR2(15), ShipState VARCHAR2(2), ShipZip VARCHAR2(5), ShipCost NUMBER(4,2), CONSTRAINT orders_order#_pk PRIMARY KEY(order#), CONSTRAINT orders_customer#_fk FOREIGN KEY (customer#) REFERENCES customers(customer#)); INSERT INTO ORDERS VALUES (1000,1005,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('02-APR-09','DD-MON-YY'),'1201 ORANGE AVE', 'SEATTLE', 'WA', '98114' , 2.00); INSERT INTO ORDERS VALUES (1001,1010,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'), '114 EAST SAVANNAH', 'ATLANTA', 'GA', '30314', 3.00); INSERT INTO ORDERS VALUES (1002,1011,TO_DATE('31-MAR-09','DD-MON-YY'),TO_DATE('01-APR-09','DD-MON-YY'),'58...

  • Create a procedure to update the sales history table following the requirements below. A table creation...

    Create a procedure to update the sales history table following the requirements below. A table creation script is provided below. a) Call the procedure: UPDATE_SALES_HISTORY (The procedure name is important for our scripts to test and grade your code; please do not rename it (otherwise our scripts will not run and your score will be 0). b) The procedure takes 2 parameters: (4-digit-year, 2-digit-month). Both parameters will be numeric, e.g., (2019, 11) will denote 2019 (year) and November (month). The...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT