Question

1. (5pts) Populate your "student" table with 20 or more students, such that: At least 4 of them (but not all!) have the last name "Jones" At least 3 students must have the first and last name 'Jessica Jones' At least 2 rows has a student with the GPA of 3.7 and 3.84, respectively At least 2 rows have a GPA of 2.7 and 3.69, respectively At least 2 rows have a GPA of 1.7 and 2.69, respectively At least 2 rows have a GPA of 1.0 and 1.69, respectively At least 1 have GPAs less than 1.0 At least 1 have GPAs greater than 3.85 At least 3 students must live at '485 W 46th Street, New York City, NY At least 6 students have to live on a street other than '485 W 46th Street' At least 7 of them are from state NJ More than 7 are from NY More than 2 are from DE (Delaware) At least 6 students must come from states other than NJ, NY, or DE At least 3 have more than 96 credits There must be at least one student with credits of 32, 33, 64, 65, and 96, respectively (i.e. 5 different entries) At least 5 of them has less than32 credits • At least 5 have between 65-96 credits At least 2 have between 33-64 credits To accomplish this, you can use the "insert" command (multiple times) via the command-line interface. Recall how you inserted Sally Smith into your table in the lab: insert into student values ('1', 'Sally', 'Smith', '10 Main St.', 'NJ', '3.92', '98'); Note that the value of the field "id," while arbitrary, must be unique for all students. (That's why it is designated as the "primary key.") I recommend that you simply assign the id's sequentially, that is, the first student is 1, the second is 2, etc.

Answer 1

Here are the queries:

To create table student with necessary attributes:

CREATE TABLE student(id INT PRIMARY KEY, firstName VARCHAR2(50), lastName VARCHAR2(50), address VARCHAR2(50), state VARCHAR2(4), gpa FLOAT, credits INT);

Inserting Values in the Table:

INSERT INTO student values( 1, 'Jessica', 'Jones', '23, High Street', 'IN', 3.95, 31);
INSERT INTO student values( 2, 'Jessica', 'Jones', 'Ghar ke piche', 'NJ', 3.7, 23);
INSERT INTO student values( 3, 'Jessica', 'Jones', 'Mohalle no 231', 'GH', 3.84, 30);
INSERT INTO student values( 4, 'Jessica', 'Jones', '23, High Street', 'IN', 2.7, 28);
INSERT INTO student values( 5, 'Vijay', 'Kholiya', 'Tigri chauraha', 'NJ', 3.69, 31);
INSERT INTO student values( 6, 'Vijay', 'James', '485 W 46th Street, New York City', 'NY', 1.0, 97);
INSERT INTO student values( 7, 'Rahul', 'James', '485 W 46th Street, New York City', 'NY', 1.69, 99);
INSERT INTO student values( 8, 'Jessica', 'James', '485 W 46th Street, New York City', 'NY', 3.7, 66);
INSERT INTO student values( 9, 'Jessica', 'Jones', '485 W 46th Street, New York City', 'NY', 3.84, 97);
INSERT INTO student values( 10, 'Jessica', 'Jones', '485 W 46th Street, New York City', 'NY', 3.69, 69);
INSERT INTO student values( 11, 'Shiwani', 'Kholiya', 'Tigri chauraha', 'NY', 2.7, 89);
INSERT INTO student values( 12, 'Ram', 'Verma', 'Tigri chauraha', 'NJ', 1.69, 82);
INSERT INTO student values( 13, 'Sheetal', 'Verma', 'Kuch Bhi Daalo', 'DE', 1.0, 78);
INSERT INTO student values( 14, 'Kala', 'Mera', 'Kahen Bhi Chale Jao', 'NJ', 3.89, 76);
INSERT INTO student values( 15, 'Ladka', 'Kutta', 'Tigri chauraha', 'NJ', 3.7, 28);
INSERT INTO student values( 16, 'Harami', 'beta', '45, Pankhe K niche', 'DE', 3.69, 37);
INSERT INTO student values( 17, 'Chutiya', 'Ladka', '45, Love street', 'NJ', 0.6, 43);
INSERT INTO student values( 18, 'Bahut', 'Bada', '23, Gone street', 'DE', 0.92, 49);
INSERT INTO student values( 19, 'Bada', 'Kuita', 'Upar wali gali', 'NJ', 3.92, 98);
INSERT INTO student values( 20, 'Kamina', 'hai', 'Ghar k niche', 'NJ', 3.3, 67);
INSERT INTO student values( 21, 'Kanu', 'Vashisth', '29, Duplec xity', 'GF', 3.7, 28);
INSERT INTO student values( 22, 'Priya', 'Samant', '34, Parathe Wali Gali', 'RE', 2.8, 20);
INSERT INTO student values( 23, 'Nicky', 'Singh', '899, No where', 'AU', 2.56, 34);
INSERT INTO student values( 24, 'Shiwani', 'Joshi', '344, Chandni CHowk', 'GF', 1.75, 88);
INSERT INTO student values( 25, 'Arti', 'Bhandari', '321, Muzzafarpur', 'YT', 0.7, 68);

Displaying the table's content:

SELECT * FROM student;

Output:

ID FIRSTNAME LASTNAME ADDRESS STATE GPA CREDITS Jessica Jones 23, High Street IN i 2 Jessica Jones Ghar ke piche 3 Jessica Jones Mohalle no 231 Jessica Jones 23, High Street Vijay Kholiya Vijay James 7 Rahul James 8 James 9 Jones Jessica Jessica Jessica Shiwani Tigri chauraha 485 W 46th Street, New York City NY 485 W 46th Street, New York City NY 485 W 46th Street, New York City NY 485 W 46th Street, New York City NY 485 W 46th Street, New York City NY Tigri chauraha NY Tigri chauraha Kuch Bhi Daalo Jones 10 11 Kholiya 3.95 31 3.7 23 3.84 30 2.7 28 3.69 31 i 97 1.6999 3.7 66 3.84 97 3.69 69 2.7 89 1.69 82 i 78 3.89 76 3.7 28 3.6937 .6 43 .92 49 3.92 3.3 67 3.7 28 Ram Verma NJ 12 13 Sheetal Verma 14 Kala Mera Kahen Bhi Chale Jao Ladka Kutta Tigri chauraha 15 16 Harami beta 45, Pankhe K niche 17 Chutiya Ladka 45, Love Street 23, Gone street 18 Bahut Bada 19 Bada Kuita Upar wali gali 20 Kamina hai Ghar k niche 21 Kanu Vashisth 29, Duplec xity 34, Parathe Wali Gali Samant 2.8 899, No where Priya 23 Nicky 24 Shiwani 25 Arti Singh Joshi Bhandari 2.56 1.75 .7 34 88 344, Chandni CHowk 321, Muzzafarpur