SQL Assignment. TIA
SQL Table
How to code subqueries Exercises 1. Write a SELECT statement that returns the same result set as this SELECT statement, but don't use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword. SELECT DISTINCT category_name FROM categories c JOIN products p ON c.category_id = p.category_id ORDER BY category_name (508) 2. Write a SELECT statement that answers this question: Which products have a list price that's greater than the average list price for all products? Return the product_name and list_price columns for each product. (45\%) Sort the results by the list price column in descending sequence. (5%) -- This script creates the schema named mgs -- Connect as the user named mgs CONNECT mgs/mgs; 7-- Use an anonymous PL/SQL script to -- drop all tables and sequences in the current schema and -- suppress any error messages that may displayed --- if these objects don't exist BBEGIN EXECUTE IMMEDIATE 'DROP SEQUENCE category_id_seq'; EXECUTE IMMEDIATE 'DROP SEQUENCE product_id_seq'; EXECUTE IMMEDIATE 'DROP SEQUENCE customer_id_seq'; EXECUTE IMMEDIATE 'DROP SEQUENCE address_id_seq'; EXECUTE IMMEDIATE 'DROP SEQUENCE order_id_seq'; EXECUTE IMMEDIATE 'DROP SEQUENCE item_id_seq'; EXECUTE IMMEDIATE 'DROP SEQUENCE admin_id_seq'; EXECUTE IMMEDIATE 'DROP TABLE administrators'; EXECUTE IMMEDIATE 'DROP TABLE order_items'; EXECUTE IMMEDIATE 'DROP TABLE orders'; EXECUTE IMMEDIATE 'DROP TABLE products'; EXECUTE IMMEDIATE 'DROP TABLE categories'; EXECUTE IMMEDIATE 'DROP TABLE addresses'; EXECUTE IMMEDIATE 'DROP TABLE customers'; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ( ' ); -END; -- Create the tables JCREATE TABLE categories ( category_id NUMBER PRIMARY KEY, category_name VARCHAR2 (255) NOT NULL UNIQUE 1; CREATE TABLE products ( product_id NUMBER PRIMARY KEY, category_id NUMBER REFRENCES categories (category_id), product_code VARCHAR2 (10) NOT NULL UNIQUE, product_name VARCHAR2 (255) NOT NULL, description VARCHAR2 (1500) NOT NULL, list_price NUMBER(10,2) NOT NULL, discount_percent NUMBER (10,2) DEFAULT 0.00, date_added DATE DEFAULT NULL CREATE TABLE customers ( customer_id NUMBER PRIMARY KRY, email_address VARCHAR2 (255) NOT NULL UNIQUE, password VARCHAR2 (60) NOT NULL, first_name VARCHAR2 (60) NOT NULL, last_name VARCHAR2 (60) NOT NULL, shipping_address_id NUMBER DEFAULT NULL, billing_address_id NUMBER DEFAULT NULL CREATE TABLE addresses ( address_id NUMBER PRIMARY KEY, customer_id NUMBER REFERENCES customers (customer_id), line1 VARCHAR2 (60) NOT NULL, line2 VARCHAR2 6 DEF) DEAULT NULL, city VARCHAR2 (40) NOT NULL, state VARCHAR2 (2) NOT NULL, zip_code VARCHAR2 (10) NOT NULL, phone VARCHAR2 (12) NOT NULL, disabled NUMBER (1) DEFAULT -- Insert data into the tables INSERT INTO categories (category_id, category_name) VALUES (1, 'Guitars'); INSERT INTO categories (category_id, category_name) VALUES (2, 'Basses'); INSERT INTO categories (category_id, category_name) VALUES (3, ' Drums); INSERT INTO categories (category_id, category_name) VALUBS (4,1 Keyboards ); CREATE SEQUENCE category_id_seq START WITH 5; INSERT INTO products (product_id, category_id, product_code, product_name, description, list_price, discount_percent, date_added) VALUES (1, 1, 'strat', 'Fender Stratocaster', 'The Fender Stratocaster is the electric guitar design that changed the world. New features include a tinted neck, parchment pickguard and control knobs, and a '70s-style logo. Includes select alder body, 21-fret maple neck with your choice of a rosewood or maple fretboard, 3 single-coil pickups, vintage-style tremolo, and die-cast tuning keys. This guitar features a thicker bridge block for increased sustain and a more stable point of contact with the strings. At this low price, why play anything but the real thing? \\\\ Features: New features: Thicker bridge block 3-ply parchment pick guard Tinted neck', '699.00', '30.00', TO_DATE('2011-10-30 09:32:40', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO products (product_id, category_id, product_code, product_name, description, list_price, discount_percent, date_added) VALUES (2, 1, 'les_paul', 'Gibson Les Paul', 'This Les Paul guitar offers a carved top and humbucking pickups. It has a simple yet elegant design. Cutting-yet-rich tone?the hallmark of the Les Paul?pours out of the 490R and 498I Alnico II magnet humbucker pickups, which are mounted on a carved maple top with a mahogany back. The faded finish models are equipped with BurstBucker Pro pickups and a mahogany top. This guitar includes a Gibson hardshell case (Faded and satin finish models come with a gig bag) and a limited lifetime warranty. Features: Carved maple top and mahogany back (Mahogany top on faded finish models) Mahogany neck, "59 Rounded Les Paul Rosewood fingerboard (Ebony on Alpine white) Tune-0-Matic bridge with stopbar Chrome or gold hardware 490R and 498I Alnico 2 magnet humbucker pickups (BurstBucker Pro on faded finish models) 2 volume and 2 tone knobs, 3-way switch', '1199.00', '30.00', -TO_DATE('2011-12-05 16:33:13', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO products (product_id, category_id, product_code, product_name, description, list_price, discount_percent, date_added) VALUES (3, 1, 'sg', 'Gibson SG', 'This Gibson SG electric guitar takes the best of the '62 original and adds the longer and sturdier neck joint of the late "'60s models. All the classic features you' d expect from a historic guitar. Hot humbuckers go from rich, sweet lightning to warm, tingling waves of sustain. A silky-fast rosewood fretboard plays like a dream. The original-style beveled mahogany body looks like a million bucks. Plus, Tune-0-Matic bridge and chrome hardware. Limited lifetime warranty. Includes hardshell case. Features: Double-cutaway beveled mahogany body Set mahogany neck with rounded "'50s profile Bound rosewood fingerboard with trapezoid inlays Tune-0-Matic bridge with stopbar tailpiece Chrome hardware 490R humbucker in the neck position 498I humbucker in the bridge position 2 volume knobs, 2 tone knobs, 3-way switch 243/4 " scale', '2517.00', '52.00', -TO_DATE('2012-02-04 11:04:31', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO products (product_id, category_id, product_code, product_name, description, list_price, discount_percent, date_added) VALUES (4, 1, 'fg700s', 'Yamaha FG700S', 'The Yamaha FG700S solid top acoustic guitar has the ultimate combo for projection and pure tone. The expertly braced spruce top speaks clearly atop the rosewood body. It has a rosewood fingerboard, rosewood bridge, die-cast tuners, body and neck binding, and a tortoise pickguard. Features: Solid Sitka spruce top Rosewood back and sides Rosewood fingerboard Rosewood bridge White/black body and neck binding Die-cast tuners Tortoise pickguard \ Limited lifetime warranty', '489.99', '38.00', -TO_DATE ('2012-06-01 11:12:59', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO products (product_id, category_id, product_code, product_name, description, list_price, discount_percent, date_added) VALUES (5, 1, 'washburn', 'Washburn Dl0S', 'The Washburn DloS acoustic guitar is superbly crafted with a solid spruce top and mahogany back and sides for exceptional tone. A mahogany neck and rosewood fingerboard make fretwork a breeze, while chrome Grover-style machines keep you perfectly tuned. The Washburn Dlos comes with a limited lifetime warranty. Features: Spruce top \\ * Mahogany back, sides * Mahogany neck Rosewood fingerboard \ In Chrome Grover-style machines', '299.00', '0.00', TO DATE ('2012-07-30 13:58:35 ' 'YYYY-MM-DD HH24:MI:SS')): -free from humidity.', '415.00', '39.00', TO_DATE ('2012-07-30 14:12:41', 'YYYY-MM-DD HH24:MI:SS')); lugs Drum heads Accu-tune bass drum hoops Spur brackets Tom holder Tom brackets', '799.99', '15.00', TO_DATE ('2012-07-30 13:14:15', 'YYYY-MM-DD HH24:MI:SS')); CREATE SEQUENCE product_id_seq START WITH 11; INSERT INTO customers (customer_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id) VALUES (1, 'allan. sherwood@yahoo.com', '650215acec746f0e32bdfff387439eefc1358737', 'Allan', 'Sherwood', 1, 2); INSERT INTO customers (customer_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id) VALUES (2, 'barryz@gmail.com', '3f563468d42a448cble56924529f6e7bbe529cc7', 'Barry', 'Zimmer', 3, 3); INSERT INTO customers (customer_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id) VALUES (3, 'christineb@solarone.com', 'edl9f5c0833094026a2fle9e6f08a35d26037066', 'Christine', 'Brown', 4, 4); INSERT INTO customers (customer_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id) VALUES (4, 'david.goldsteinQhotmail. com', 'b444ac06613fc8d63795be9ad0beaf55011936ac', 'David', 'Goldstein', 5, 6); INSERT INTO customers (customer_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id) VALUES (5, 'erinv@gmail.com', '109f4b3c50d7bodf729d299bc6f8e9ef9066971f', 'Erin', 'Valentino', 7, 7); INSERT INTO customers (customer_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id) VALUES (6, 'frankwilson@sbcglobal.net', '3ebfa301dc59196f18593c45e519287a23297589', 'Frank Lee', 'Wilson', 8, 8); INSERT INTO customers (customer_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id) VALUES (7, 'gary_hernandez@yahoo.com', 'lff2b3704aede04eecb51e50ca698efd50al379b', 'Gary', 'Hernandez', 9, 10); INSERT INTO customers (customer_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id) VALUES (8, 'heatheresway@mac.com', '911ddc3b8f9al3b5499b6bc4638a2b4f3f68bf23', 'Heather', 'Esway', 11, 12); CREATE SEQUENCE customer_id_seq START WITH 9; INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES (1, 1, '100 East Ridgewood Ave.', '', 'Paramus', 'NJ', '07652', '201-653-4472', 0); INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES (2, 1, '21 Rosewood Rd.', '', 'Woodcliff Lake', 'NJ', '07677', '201-653-4472', 0); INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES (3, 2, '16285 Wendell St.', '', 'Omaha', 'NE', '68135', '402-896-2576', 0); INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES (4, 3, '19270 1W Cornell Rd.', '', 'Beaverton', 'OR', '97006', '503-654-1291', 0); INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES (5, 4, '186 Vermont St.', 'Apt. 2', 'San Francisco', 'CA', '94110', '415-292-6651', 0); INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES (6, 4, '1374 46th Ave.', '', 'San Francisco', 'CA', '94129', '415-292-6651', 0); INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES (7, 5, '6982 Palm Ave.', '', 'Fresno', 'CA', '93711', '559-431-2398', 0); INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES (8, 6, '23 Mountain View St.', '', 'Denver', 'CO', '80208', '303-912-3852', 0); INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES (9, 7, '7361 N. 41st St.', 'Apt. B', 'New York', 'NY', '10012', '212-335-2093', 0); INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES (10, 7, '3829 Broadway Ave.', 'Suite 2', 'New York', 'NY', '10012', '212-239-1208', 0); INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES (11, 8, '2381 Buena Vista St.', '', 'Los Angeles', 'CA.', '90023', '213-772-5033', 0); INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES (12,8,291 W. Hollywood Blvd.', '', 'Los Angeles', 'CA', '90024', '213-391-2938', 0); CREATE SEQUENCE address_id_seq START WITH 13; INSERT INTO orders (order_id, customer_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, card_expires, billing_address_id) VALUES (1,1, TO_DATE('2012-03-28 09:40:28', 'YYYY-MM-DD HH24:MI:SS'), '5.00', '32.32', TO_DATE('2012-03-30 15:32:51', 'YYYY-MM-DD HH24:MI:SS'), 1, 'Visa', '4111111111111111', '04/2014', 2)' INSERT INTO orders (order_id, customer_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, card_expires, billing_address_id) VALUES (2, 2, TO_DATE('2012-03-28 11:23:20', 'YYYY-MM-DD HH24:MI:SS'), '5.00', '0.00', TO_DATE('2012-03-29 12:52:14', 'YYYY-MM-DD HH24:MI:SS'), 3, 'Visa', '4012888888881881', '08/2016', 3); INSERT INTO orders (order_id, customer_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, -card_expires, billing_address_id) VALUES (3,_1, TO_DATE('2012-03-29 09:44:58', 'YYYY-MM-DD HH24:MI:SS'), '10.00', '89.92', TO_DATE('2012-03-31 9:11:41', 'YYYY-MM-DD HH24:MI:SS'), 1, 'Visa', '4111111111111111', '04/2014', 2)'; INSERT INTO orders corder_id, customer_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, card_expires, billing_address_id) VALUES (4,3, TO_DATE('2012-03-30 15:22:31', 'YYYY-MM-DD HH24:MI:SS'), '5.00', '0.00', TO_DATE ('2012-04-03 16:32:21', 'YYYY-MM-DD HH24:MI:SS'), -4, 'American Express', '378282246310005', '04/2013', 4)'; INSERT INTO orders (order_id, customer_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, -card_expires, billing_address_id) VALUES (5, 4, TO_DATE('2012-03-31 05:43:11', 'YYYY-MM-DD HH24:MI:SS'), '5.00', '0.00', TO_DATE('2012-04-02 14:21:12', 'YYYY-MM-DD HH24:MI:SS'), 5, 'Visa', '411111111111111', '04/2016', 6); INSERT INTO orders (order_id, customer_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, -card_expires, billing_address_id) VALUES (6, 5, TO_DATE('2012-03-31 18:37:22', 'YYYY-MM-DD HH24:MI:SS'), '5.00', ' 0.00 ', NULL, 7, 'Discover', '6011111111111117', '04/2016', 7); INSERT INTO orders corder_id, customer_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, -card_expires, billing_address_id) VALUES (7, , TO_DATE('2012-04-01 23:11:12', 'YYYY-MM-DD HH24:MI:SS'), '15.00', '0.00', TO_DATE('2012-04-03 10:21:35', 'YYYY-MM-DD HH24:MI:SS'), 8, 'MasterCard', '5555555555554444', '04/2016', 8); INSERT INTO orders (order_id, customer_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, Card_expires, billing_address_id) VALUES (8,7, TO_DATE('2012-04-02 11:26:38', 'YYYY-MM-DD HH24:MI:SS'), '5.00', ' 0.00 ', NULL, 9, 'Visa', '4012888888881881', '04/2016', 10) INSERT INTO orders (order_id, customer_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, -card_number, card_expires, billing_address_id) VALUES (9,_4, TO_DATE ('2012-04-03 12:22:31', 'YYYY-MM-DD HH24:MI:SS'), '5.00', ' 0.00, NULL, 5, 'Visa', '4111111111111111', '04/2016', 6) CREATE SEQUENCE order_id_seq INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES (1,1,2,1199.00 ', '359.70', 1); INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES (2,2,4,489.991,186.201,1); INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES (3,3,3,2517.00,1308.84,1); INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES (4,3,6,415.00,161.85,1); INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES (5,4,2,1199.00 ', '359.70', 2); INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES (6,5,5,299.00,10.00,1); INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES (7,6,5,299.00,10.00,1); INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES (8,7,1,699.00 ', '209.70', 1); INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES (9,7,7,1799.991,240.001,1); INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES (11,8,10,1799.991,120.001,1); INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES (12,9,1,699.001,209.701,1); CREATE SEQUENCE item_id_seq START WITH 13; INSERT INTO administrators (admin_id, email_address, password, first_name, last_name) VALUES (1, 'admin@myguitarshop.com', '6a718fbd768c2378b511f8249b54897f940e9022', 'Admin', 'User'); INSERT INTO administrators (admin_id, email_address, password, first_name, last_name) VALUES (2, 'joelemurach.com', '971e95957d3b74d70d79c20c94e9cd91b85f7aae', 'Joel', 'Murach'); INSERT INTO administrators (admin_id, email_address, password, first_name, last_name) VALUES (3, 'mike@murach.com', '3f2975c819cefc686282456aeae3al37bf896ee8', 'Mike', 'Murach'); CREATE SEQUENCE admin_id_seq START WITH 13
