Question

1. Consider the following data. (It is not necessary to run the code and upload the output)                                                                                                                [10 Marks]

STUDENT
StudentID	SName	Gender	Age	ClubID
3234	Alfred Smith	Male	20	BSK
2244	McJohnson Robert	Male	22
2389	Jessica Low	Female	20	JPA
4211	Roland Devingo	Male	24
4383	Jane Usa Khan	Female	21	BKY
4450	Elaine Fong	Female	20	JPA

CLUB
ClubID	CName	Founded	Budget
BKY	Bakery Club	2010	2546
PDC	Photomedia and Design	2005	1345
JPA	Japanese Anime	2009	3453
BSK	Basketball	2011	6744

Answer the following:

a) Write an SQL statement to display student name and club name in ascending order of the student name. Include all clubs even if they do not have any members.

b)What is the value returned by this statement?

SELECT COUNT( CLUBID )
FROM STUDENT

c) Re-write the statement to produce the correct output.

d)Since autocommit is off, how do you make changes permanent in the Student table?

e) Write the SQL statements that would DROP both tables. (Note: Order is significant)

Answer 1

a) We need to select data from two tables so we will use join for that

select s.SName,c.Cname from student s full outer join club c on c.ClubId=s.ClubId order by s.sname asc

b)This will generate count as 4 because it won't count null values

c)select count(ClubId) from student where ClubId is null union all select count(ClubId) from student where ClubId is not null

d)you can explicitly use commit for example:

1)I am doing an insertion like Insert into students values('1122','John','Male','25','JPA');

2)write commit; this will commit the changes permanently in database.

e)Drop table if exist  student,club; This will drop the table in an order simultaneously.