Question

1) Create a table “college” that has as attributes name, city name, state/province/region name, country name, and year in which the college was established as well as an ID as primary key. Insert records for 5 colleges of your choice, including one or more you have attended.

2) Create a table “student” that has as attributes first name, last names, and college ID for students, and insert the names of yourself and friends who attended one or more colleges together with you (if you have only attended one college, the name can be the same for all). Note that the student names can be fictitious but not the college name.

3) Add a foreign key to the appropriate table above, using “on delete cascade” as referentially triggered action, and demonstrate that insertion for a student record with a non-existing college ID fails.

4) Do a query that shows all students together with their respective college information. For colleges, that no students have attended, list all student information as null (i.e. OUTER JOIN).

5) Do a query that lists college names together with the number of students in the database that have attended that college, using the GROUP BY statement.

6) Do a query of catalog information.

7) Do a deletion of a college that was referenced and redo the query from question 4.

Answer 1

1)

command to create table , college:

CREATE TABLE college(name varchar(255), city_name varchar(255), state varchar(255), country varchar(255), year_of_establishment int(4), ID varchar(255) NOT NULL PRIMARY KEY);

command to insert record to college table:

INSERT INTO college VALUES("Om puri kori", "chinston","Ugoslavia", "UK","2003", "OPK123");

2)

command to create table student:

CREATE TABLE student(fname varchar(255), lname varchar(255), college_ID varchar(255));

command to insert student into table:

INSERT INTO student VALUES("abc", "xyz", "RTG123");

3)

Adding a foreign key to student table on column, coolege_ID:

ALTER TABLE student ADD FOREIGN KEY (college_ID) REFERENCES college(ID);

You can see below that when we tried to insert a student with non-existing college ID in the table, the query fails.

4)

We will do RIGHT OUTER JOIN as it will include every college even if it has no students.

SELECT fname, lname, name FROM student RIGHT OUTER JOIN college ON student.college_ID = college.ID;

5)

SELECT name, count(fname) FROM student RIGHT OUTER JOIN college ON student.college_ID = college.ID GROUP BY college.ID;

6)

To query cayalog information, just show the table content of student and college tables.

SELECT * FROM student;

SELECT * FROM college;

7)

To delete a record with college ID = "SM123":

DELETE FROM college WHERE ID = "SM123";

And then redoing the query from part 4 gives following output:

If it helps you, do upvote as it motivates us a lot!