Please upload before the ddl
using oracle
DROP TABLE LOAN; DROP TABLE STUDENT; DROP TABLE PROGRAM; DROP TABLE COMPUTER; DROP TABLE LOCATION; DROP TABLE ITEM; CREATE TABLE Program (Prog_ID CHAR(7), Name VARCHAR2(25), CONSTRAINT Program_ProgID_PK PRIMARY KEY (Prog_ID)); CREATE TABLE Student (St_ID CHAR(4), St_LName VARCHAR2(20), St_FName VARCHAR2(20), Email VARCHAR2(25), Prog_ID CHAR(7), CONSTRAINT Student_StID_PK PRIMARY KEY (St_ID), CONSTRAINT Student_ProgID_FK FOREIGN KEY (Prog_ID) REFERENCES Program (Prog_ID)); CREATE TABLE Location (Location_ID CHAR(2), Loc_Bldg VARCHAR2(20), Loc_Room CHAR(7), CONSTRAINT Location_Location_ID_PK PRIMARY KEY (Location_ID)); CREATE TABLE Item (Item_ID CHAR(4), Item_Manuf VARCHAR2(30), Item_Model VARCHAR2(30), Comments VARCHAR2(300), CONSTRAINT Item_Item_ID_PK PRIMARY KEY (Item_ID)); CREATE TABLE Computer (Comp_ID CHAR(4), Comp_Name VARCHAR2(20), Purchase_Date Date, Purchase_Price Number(7,2), Location_ID CHAR(2), Item_ID CHAR(4), Vendor_Name VARCHAR2(20), CONSTRAINT Computer_CompID_PK PRIMARY KEY (Comp_ID), CONSTRAINT Computer_locationID_FK FOREIGN KEY (Location_ID) REFERENCES Location (Location_ID), CONSTRAINT Computer_ItemID_FK FOREIGN KEY (Item_ID) REFERENCES Item (Item_ID)); CREATE TABLE Loan (Loan_ID Number(6), St_ID CHAR(4), Comp_ID CHAR(4), Start_Date DATE DEFAULT SYSDATE, Date_Returned DATE, CONSTRAINT Loan_LoanID_PK PRIMARY KEY (Loan_ID), CONSTRAINT Loan_StID_FK FOREIGN KEY (St_ID) REFERENCES student (St_ID), CONSTRAINT Loan_compID_FK FOREIGN KEY (Comp_ID) REFERENCES computer (Comp_ID)); ------------------------------------------------------------------- INSERT INTO Program VALUES ('COMPFIN', 'Computational Finance'); INSERT INTO Program VALUES ('HNZIS', 'Information Systems'); INSERT INTO Program VALUES ('HNZECON', 'Economics of IS'); INSERT INTO student VALUES ('241', 'Singh', 'Vijay', '
[email protected]', 'COMPFIN'); INSERT INTO student VALUES ('207', 'Guerra', 'Richard', '
[email protected]', 'COMPFIN'); INSERT INTO student VALUES ('213', 'Miller', 'Sandra', '
[email protected]', 'HNZIS'); INSERT INTO student VALUES ('231', 'Holin', 'John', '
[email protected]', 'HNZIS'); INSERT INTO student VALUES ('238', 'Johnson', 'Richard', '
[email protected]', 'COMPFIN'); INSERT INTO student VALUES ('323', 'Nimoy', 'Joan', '
[email protected]', 'COMPFIN'); INSERT INTO student VALUES ('320', 'Tavernier', 'Anna', '
[email protected]', 'HNZIS'); INSERT INTO student VALUES ('360', 'Bevens', 'John', '
[email protected]', 'HNZIS'); INSERT INTO student VALUES ('362', 'Couples', 'Fred', '
[email protected]', 'COMPFIN'); INSERT INTO student VALUES ('425', 'Pepper', 'Angela', '
[email protected]', 'HNZIS'); INSERT INTO student VALUES ('407', 'Lee', 'Bing', '
[email protected]', 'HNZIS'); INSERT INTO student VALUES ('416', 'Price', 'Michael', '
[email protected]', 'COMPFIN'); INSERT INTO student VALUES ('437', 'Young', 'James', '
[email protected]', 'HNZECON'); INSERT INTO student VALUES ('450', 'Sanchez', 'Ruben', '
[email protected]', 'HNZECON'); INSERT INTO location VALUES ('1', 'HBH', '112'); INSERT INTO location VALUES ('2', 'HBH', '116'); INSERT INTO location VALUES ('3', 'NSH', '116'); INSERT INTO location VALUES ('4', 'HBH', '253'); INSERT INTO location VALUES ('5', 'NSH', '305'); INSERT INTO location VALUES ('6', 'HBH', '3015'); INSERT INTO item VALUES (15, 'Dell', 'XPS 13', 'Touchscreen Laptop 13.4 inch FHD+ Thin and Light. Intel Core i7-1195G7, 16GB LPDDR4x RAM, 512GB SSD, Intel Iris Xe Graphics'); INSERT INTO item VALUES (17, 'Apple', 'MacBook Pro', '14-inch, Apple M1 Pro chip with 10 core CPU and 16 core GPU, 16GB RAM, 1TB SSD'); INSERT INTO item VALUES (19, 'HP', 'Pavilion', 'Business Laptop, 15.6" Full HD Display, Intel Core i7-1195G7 Processor, 32GB RAM, 1TB SSD, Fingerprint Reader, HDMI, Webcam, Windows 11 Pro, Silver'); INSERT INTO item VALUES (21, 'HP', 'Envy', 'X360 2-in-1 15.6" FHD IPS Touch-Screen Laptop, Intel Quad Core i7-1165G7, 16GB DDR4 RAM, 1TB SSD'); INSERT INTO item VALUES (23, 'Apple', 'MacBook Air', 'Laptop with M2 chip: 13.6-inch Liquid Retina Display, 8GB RAM, 256GB SSD Storage, Backlit Keyboard, 1080p FaceTime HD Camera. Works with iPhone and iPad'); INSERT INTO item VALUES (25, 'Dell', 'Inspiron', 'Inspiron 3000 Touchscreen Laptop, 15.6" FHD (1920 x1080) Display, Intel Core i7 (Quad-Core), 32GB RAM, 1TB PCIe SSD, HDMI, WiFi, Webcam, SD Card Reader'); INSERT INTO computer VALUES ('C8', 'Newell', '07-JUN-2021', 1700, '3', '15', 'Best Buy'); INSERT INTO computer VALUES ('C10', 'Caracas', '25-JUN-2021', 1700, '2', '15', 'Dell'); INSERT INTO computer VALUES ('C18', 'Bell', '25-JUN-2021', 1209, '2', '19', 'HP'); INSERT INTO computer VALUES ('C19', 'Simon', '25-JUN-2021', 1209, '1', '19', 'HP'); INSERT INTO computer VALUES ('C29', 'Goldeneye','25-JUN-2021', 2500, '1', '17', 'Apple Store'); INSERT INTO computer VALUES ('C30', 'Barnaby', '25-JUN-2021', 1700, '2', '15', 'Dell'); INSERT INTO computer VALUES ('C22', 'Azkaban', '02-NOV-2021', 1209, '3', '19', 'HP'); INSERT INTO computer VALUES ('C25', 'Avatar', '02-NOV-2021', 2199, '3', '17', 'Campus Store'); INSERT INTO computer VALUES ('C21', 'Hobbit', '05-APR-2022', 2199, '1', '17', 'Campus Store'); INSERT INTO computer VALUES ('C28', 'Dinar', '05-APR-2022', 1209, '3', '19', 'HP'); INSERT INTO computer VALUES ('C31', 'Phoenix', '16-AUG-2022', 1129, '4', '21', 'Amazon.com'); INSERT INTO computer VALUES ('C32', 'Stargate','16-AUG-2022', 1199, '5', '23', 'Apple Store'); INSERT INTO computer VALUES ('C33', 'Cloud', '16-AUG-2022', 1129, '6', '21', 'Amazon.com'); INSERT INTO computer VALUES ('C34', 'Koala', '16-AUG-2022', 1199, '4', '23', 'Apple Store'); INSERT INTO loan VALUES (1, '241', 'C8', to_date('15-JUN-21 3:09pm', 'dd-mon-yy hh:miam'), to_date('03-SEP-21 10:40am', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (2, '207', 'C30', to_date('20-AUG-21 3:43pm', 'dd-mon-yy hh:miam'), to_date('21-JAN-22 2:25pm', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (3, '238', 'C10', to_date('21-AUG-21 9:06am', 'dd-mon-yy hh:miam'), to_date('21-MAY-22 1:30pm', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (4, '231', 'C18', to_date('21-AUG-21 9:11am', 'dd-mon-yy hh:miam'), to_date('21-AUG-21 2:11pm', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (5, '207', 'C18', to_date('21-AUG-21 3:43pm', 'dd-mon-yy hh:miam'), to_date('12-APR-22 2:25pm', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (6, '213', 'C19', to_date('21-AUG-21 3:15pm', 'dd-mon-yy hh:miam'), to_date('11-SEP-21 4:06pm', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (7, '231', 'C29', to_date('21-AUG-21 10:20am', 'dd-mon-yy hh:miam'), to_date('08-FEB-22 11:51am', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (8, '213', 'C19', to_date('11-SEP-21 9:45am', 'dd-mon-yy hh:miam'), to_date('17-MAY-22 10:23am', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (9, '323', 'C28', to_date('11-SEP-21 11:30am', 'dd-mon-yy hh:miam'), to_date('25-APR-22 3:00pm', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (10, '320', 'C8', to_date('28-SEP-21 10:32am', 'dd-mon-yy hh:miam'), to_date('09-OCT-21 9:49am', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (11, '360', 'C8', to_date('09-OCT-21 4:16pm', 'dd-mon-yy hh:miam'), to_date('23-APR-22 9:33am', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (12, '362', 'C22', to_date('19-NOV-21 9:23am', 'dd-mon-yy hh:miam'), to_date('23-NOV-21 9:55am', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (13, '241', 'C22', to_date('30-NOV-21 10:45am', 'dd-mon-yy hh:miam'), NULL); INSERT INTO loan VALUES (14, '238', 'C25', to_date('10-DEC-21 10:58am', 'dd-mon-yy hh:miam'), to_date('20-MAY-22 10:06am', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (15, '320', 'C29', to_date('22-FEB-22 10:00am', 'dd-mon-yy hh:miam'), to_date('25-MAR-22 3:00pm', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (16, '323', 'C30', to_date('12-APR-22 12:45pm', 'dd-mon-yy hh:miam'), to_date('25-AUG-22 1:00pm', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (17, '360', 'C21', to_date('22-APR-22 9:31am', 'dd-mon-yy hh:miam'), to_date('17-MAY-22 9:37am', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (18, '207', 'C28', to_date('26-APR-22 4:24pm', 'dd-mon-yy hh:miam'), null); INSERT INTO loan VALUES (19, '362', 'C8', to_date('03-MAY-22 9:01am', 'dd-mon-yy hh:miam'), to_date('10-MAY-22 12:34pm', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (20, '360', 'C29', to_date('06-MAY-22 11:16am', 'dd-mon-yy hh:miam'), to_date('16-SEP-22 10:10am', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (21, '238', 'C10', to_date('24-MAY-22 10:12am', 'dd-mon-yy hh:miam'), NULL); INSERT INTO loan VALUES (22, '362', 'C21', to_date('14-JUN-22 9:12am', 'dd-mon-yy hh:miam'), to_date('16-SEP-22 11:20am', 'dd-mon-yy hh:miam')); INSERT INTO loan VALUES (23, '231', 'C25', to_date('23-AUG-22 9:12am', 'dd-mon-yy hh:miam'), null); INSERT INTO loan VALUES (24, '213', 'C19', to_date('26-AUG-22 9:31am', 'dd-mon-yy hh:miam'), null); INSERT INTO loan VALUES (25, '425', 'C30', to_date('30-AUG-22 11:02am', 'dd-mon-yy hh:miam'), null); INSERT INTO loan VALUES (26, '407', 'C31', to_date('30-AUG-22 11:07am', 'dd-mon-yy hh:miam'), null); INSERT INTO loan VALUES (27, '416', 'C32', to_date('30-AUG-22 11:13am', 'dd-mon-yy hh:miam'), null); INSERT INTO loan VALUES (28, '320', 'C8', to_date('30-AUG-22 1:47pm', 'dd-mon-yy hh:miam'), null); INSERT INTO loan VALUES (29, '360', 'C21', to_date('16-SEP-22 12:42pm', 'dd-mon-yy hh:miam'), null); INSERT INTO loan VALUES (30, '362', 'C18', to_date('17-SEP-22 1:47pm', 'dd-mon-yy hh:miam'), null); Commit; -- Additions based on the first SQL assignment: INSERT INTO item VALUES (31, 'HP', 'Envy', 'HP Envy 17 Laptop, 11th Gen Intel Core i7-1165G7, 12 GB RAM, 1 TB SATA Hard Drive and 256 GB SSD Storage, 17.3-inch Full HD IPS Touchscreen'); INSERT INTO item VALUES (32, 'Dell', 'Inspiron', 'Inspiron 7000 2-in-1 17" QHD+ Touchscreen Laptop Computer, 11th Intel Core i7-1165G7, 32GB RAM, 1TB PCIe SSD'); COMMIT; Microsoft Word - Formatting SQL Queries.docx Page 1 of 7 Formatting Queries in SQL*Plus SQL*Plus has a number of ways for formatting results of queries. Consider them in your SQL Homework and Implementation Project. The following is the initial output from a query that lists content of a table (all computer books we have in JustLeeBooks Database): Note that each row of results wraps – using two lines. Header of the table is printed twice (second time in the middle of the result). There is also extra “wasted” space that makes the output difficult to read. Page 2 of 7 The following are ways you can use to better format the output. 1. Find the current SQL*Plus settings for “Linesize” and “Pagesize”. You can retrieve these settings with SHOW LINESIZE and SHOW PAGESIZE commands. These commands will reveal that each line is set to 80 characters long and each page is set to include only 14 rows of output (before repeating the table header). Changing the Linesize setting to a bigger number increases the number of characters that SQL*Plus will fit on each line before wrapping the string to the next line (if the line is still longer than 130 characters). Each row is on a single line and it looks fine. We can further improve the presentation of each column using “COLUMN” command. Page 3 of 7 2. The SQL*Plus “COLUMN” command enables you to define the width of any column. The syntax for the command is: COLUMN [column_name] FORMAT [format description] For text or date columns, the format description to be used is A[number]. The A indicates that the values are strings and the number specifies the width of the column. For “numeric” columns, the format description can be defined using the number formatting elements such as ‘9’ symbols, comma, period, and dollar sign (formatting is also covered in the “Selected Single-Row Functions” lecture). In this example, we want to slightly increase the width for the first three column of the output (i.e., ISBN, Title, and PubDate columns): The formatting made the first three columns wider & more readable. Before applying COLUMN commands, be sure to run the command “CLEAR COLUMNS” to ensure that all previous COLUMN settings are removed/cleared. If you give an alias to a column, the alias becomes the column name (the header for the column in a table that represents output for a query). Page 4 of 7 Now, format the three numerical columns