create a new table that contains the list of all the students and class_descript

ID: 3696568 • Letter: C

Question

create a new table that contains the list of all the students and class_descriptions. Include In this table the list of all students who are not enrolled in any classes (display no classes). If there are no class descriptions then display ‘no description’ (Use combination of inner join, union and minus) (Note: minus will deal with the students who are not enrolled in any classes)

create new_table as (SELECT fname, lname, NVL(class_description, 'No Description') AS "Class Description" FROM STUDENT st, CLASS cl, STUDENT_CLASS sc WHERE st.ssn = sc.ssn AND sc.class_code = cl.class_code)

UNION

(SELECT ssn FROM STUDENT MINUS SELECT ssn FROM STUDENT_CLASS);

Explanation / Answer

SQL JOIN

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN returns all rows from multiple tables where the join condition is met.

Let's look at a selection from the "Orders" table:

Then, have a look at a selection from the "Customers" table:

Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

Then, if we run the following SQL statement (that contains an INNER JOIN):

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

Try it Yourself »

it will produce something like this:

Different SQL JOINs

Before we continue with examples, we will list the types of the different SQL JOINs you can use: