referring to the Formatting SQL Queries, using oracle
Microsoft Word - DBM-D SQL#1 F2022.docx 95 – 703 D: Database Management SQL Assignment #1 (hardcopy due on November 3, in class before the lecture starts) Turn in a well formatted printout of your SQL statements as they were executed in the SQL*Plus environment and the results of running each statement. Set the LINESIZE and PAGESIZE values optimally to ensure that there are no wrapping lines or repeated column headers in the results – refer to the “Formatting SQL Queries” document available on the class blackboard for tips. Identify each answer clearly. Each question should be answered by a single SQL statement unless requested otherwise. Use only the SQL concepts covered in class so far (by October 27). The script needed to create the Computer Loan database is provided on the class website. Download the script, create the database, and answer the questions below. The script is titled ‘Comp_Loan.txt’. A University’s Information Systems Department loans computers to their doctoral students. Each computer is located within the department premises and is not re-located with each loan, but the assigned students have dedicated access to the specified computers for their research for the duration of the loan. The department keeps track of the computers and their users using a simple database. Entity-Relationship Diagram and Schema are provided below. Entity-Relationship Diagram: Schema: STUDENT (St_ID, St_LName, St_FName, Email, Prog_ID@) LOCATION (Location_ID, Loc_Bldg, Loc_Room) ITEM (Item_ID, Item_Manuf, Item_Model, Comments) COMPUTER (Comp_ID, Comp_Name, Purchase_Date, Purchase_Price, Location_ID@, Item_ID@, Vendor_Name) LOAN (Loan_ID, St_ID@, Comp_ID@, Start_Date, Date_Returned) PROGRAM (Prog_ID, Name) COMPUTER ITEM (0, N) (1,1) (1,1) (0, N) references LOCATIONis at (1, N) (1,1) (0, N) (1,1)LOAN has includes PROGRAM (1, N) (1,1) is inSTUDENT JTW Part I 1. In preparation for new computers that are to be ordered for the coming semester, the IS Department would like you to find the newest computer models offered by the manufactures already represented in the table. Research to find two current computer models that are not currently represented in the data and add the two records to the data set. Provide two complete records (each INSERT must have complete information including detailed comments). 2. After you complete the above tasks, list the content of the ITEM table. Part II 1. For computers that cost more than $1500, list their ID, name, date when they were purchased, purchase price, and manufacturer’s name. Format the purchase price with dollar sign and commas for thousands. 2. List the ID and name (concatenate the ID and name) of every doctoral student who had an Apple computer on loan that was purchased in 2022. Order the list by student’s last name. 3. For each computer that has never been used, provide the computer ID, computer name, manufacturer, model, and the purchase date. 4. List the ID, name, and model of Apple computers that were loaned within 2022 (borrowed and returned within 2022). Include the location of those computers. 5. Create a list of students who have never borrowed any computers. List student ID, name, and email concatenated together into one string. Sort the list by student last name. 6. List students (their IDs and names concatenated together) from Information Systems program who currently have a computer on loan that was initiated in August 2022. Also, provide the loan ID and start date of the loan. Sort the list by student’s last name. 7. List the ID and name of every doctoral student who have had only HP computers on loan in the past (but not currently). 8. List item models of the computers that were on loan to Fred Couples but not to Joan Nimoy. List the computers’ models located only in HBH building (for the computers loaned to Fred Couples but not to Joan Nimoy). 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',