ake VARCHAR2(20), ppID NUMBER(3), CID NUMBER(3), lue NUMBER(6,2), te_purchased DATE, ONSTRAINT computer_complD_PK PRI EY (complD), ONSTRAINT computer_supplD FK FOR EY (suppID) REFERENCES supplier, ONSTRAINT computer_loclD_FK FOREI
ake VARCHAR2(20), ppID NUMBER(3), CID NUMBER(3), lue NUMBER(6,2), te_purchased DATE, ONSTRAINT computer_complD_PK PRI EY (complD), ONSTRAINT computer_supplD FK FOR EY (suppID) REFERENCES supplier, ONSTRAINT computer_loclD_FK FOREI
Database Systems: Design, Implementation, & Management
12th Edition
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Carlos Coronel, Steven Morris
Chapter11: Database Performance Tuning And Query Optimization
Section: Chapter Questions
Problem 32P: Problems 2932 are based on the following query: SELECT CUS_CODE, MAX(LINE_UNITSLINE_PRICE) FROM...
Related questions
Question
Transcribed Image Text:CREATE TABLE computer
(ComplD NUMBER(5),
SNumber VARCHAR2(15),
Make VARCHAR2(20),
SuppID NUMBER(3),
LocID NUMBER(3).
Value NUMBER(6,2),
Date_purchased DATE,
CONSTRAINT computer_complD_PK PRIMARY
KEY (compID),
CONSTRAINT computer_suppID FK FOREIGN
KEY (suppiD) REFERENCES supplier,
CONSTRAINT computer_locID_FK FOREIGN KEY
(locID) REFERENCES location);
INSERT INTO supplier VALUES (122, 'Compaq
Sdn.Bhd', '10 Jalan Duta', '03-989898');
INSERT INTO supplier VALUES (123, 'ASUS
Sdn.Bhd', '12 Jalan Duta', '03-232323');
INSERT INTO supplier VALUES (124, 'IBM
Sdn.Bhd', '15 Jalan Duta', '03-565656');
INSERT INTO supplier VALUES (125, 'Apple
Sdn.Bhd', '18 Jalan Duta', '03-785676');
INSERT INTO location VALUES (222, 'LAW', 343);
INSERT INTO location VALUES (333, 'IRK', 343);
INSERT INTO location VALUES (444, ICT', 343);
INSERT INTO computer VALUES (13321,
'XDX12345', 'Compaq', 122, 222, 2300.50,
TO DATE('12-JAN-2008', 'DD-MON-YYYY');
INSERT INTO computer VALUES (13322,
"XDX12344', 'ASUS PC', 123, 222, 3000.80,
TO DATE('17-JAN-2008', 'DD-MON-YYYY');
INSERT INTO computer VALUES (13323,
"XDX12343', 'IBM', 124, 333, 2800.50,
TO DATE('12-DEC-2008', 'DD-MON-YYYY');
INSERT INTO computer VALUES (13324,
"XDX12342', 'Apple', 125, 444, 5300.50,
TO DATE('12-JUN-2009', 'DD-MON-YYYY');
INSERT INTO computer VALUES (13325,
"XDX12341', 'Apple', 125, 444, 6300.50,
TO DATE('12-JUN-2009', 'DD-MON-YYYY");
INSERT INTO computer VALUES (13326,
"XDX12346', 'Apple', 125, 444, 6000.85,
TO_DATE('12-JUN-2009', 'DD-MON-YYYY");
COMMIT;
Transcribed Image Text:For this assignment, please use the SUPPLIER and
COMPUTER table. The script is available in both
iTaleemC and GC. Script name: computer.sql.
Note: Use Subqueries
1. Display the name of supplier(s) that supplied
the most expensive computer?
2. Display the name of supplier(s) that supplied
more expensive computer than that being
supplied by IBM.
3. Display the supplier that supplied the most
computers.
4. Display the building location that has the most
computers.
Considering the tables are:
DROP TABLE computer CASCADE
CONSTRAINTS;
DROP TABLE location CASCADE CONSTRAINTS;
DROP TABLE supplier CASCADE CONSTRAINTS;
CREATE TABLE supplier
(SupplD NUMBER(3),
SName VARCHAR2(15),
SAddress VARCHAR2(20),
SPhone VARCHAR2(12),
CONSTRAINT supplier supplD_PK PRIMARY KEY
(suppID));
CREATE TABLE location
(LocID NUMBER(3),
Bldg code CHAR(4).
RoomNo NUMBER(3),
CONSTRAINT Iocation_locid_PK PRIMARY KEY
(LocID));
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 2 steps
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr