Write sql statement COSC4336: Database Systems 3. Execute a SQL statement to dis

Question

Write sql statement

COSC4336: Database Systems

3. Execute a SQL statement to display all the columns in the table CLUB for

the membership fee between $60 and $200.

4. Execute a SQL statement to display the mentor name(s) with SkillId of

either 2 or 4.

5. Execute a SQL statement to display the first name(s) and last name(s) of

the student(s) with the last name starting with A or B or C.

6. Execute a SQL statement to display the student id(s) of the student(s) who

is (are) neither a member of the Book Club nor a member of the Poetry

Club.

7. Execute a SQL statement to display the last names of the students whose

ids are between 11000 and 12000.

8. Execute a SQL statement to display the names of the clubs that charge

less than half the membership fee of the Soccer Club.

9. Execute a SQL statement to display, in descending order, the names of

the students who are enrolled in the Soccer Club.

10. Execute a SQL statement to display the student’s last name and his or her

mentor’s name for the student whose id is 12153 with a UNION statement.

11. Execute a SQL statement to display the mentor name(s) of the student(s)

who has (have) the last name Smith.

12. Execute a SQL statement to display the first name(s) and last name(s) of

the student(s) whose mentor is Smith and who is (are) enrolled in the

Book Club.

13. Execute a nested query statement to display the student ids and club names

of the students who are recruiters and pay less than $100 for membership

fees.

14. By using the EXISTS operator, execute a SQL statement to display the

first and last names of the students who are enrolled in the Book Club.

15. By using the NOT EXISTS operator, execute a SQL statement to display

the names of the skills not required by the duty President.

16. Create a table NEW_CLUB with the same structure as CLUB. Execute a SQL

statement to copy the records from CLUB to NEW_CLUB.

17. By using an inner join, display the students’ names along with their mentors’

names.

18. By using the LEFT OUTER JOIN operator, execute a SQL statement to

display the names of the skills from the table SKILL and the names of

mentors who provide training for these skills from the table MENTOR.

19. By using the RIGHT OUTER JOIN operator, execute a SQL statement to

display the names of the mentors who provide training for the skills from

the table MENTOR and the names of the skills from the table SKILL.

20. By using a self join, execute a SQL statement to display the mentors’ names

and the pairs of the last names of the students who are mentored by the

same mentors. Order your result by the mentors’ names.

Figure 6.1 Tables in Student_Club database: (a)CLUB, (b)SKILL, (c)SERVICE, (d)MENTOR, (e)STUDENT, (f)STUDENT_MENTOR, (g)STUDENT_CLUB, and (h)STUDENT_SERVICE

Explanation / Answer

This question has mutliple sub-parts. Please post 2 more questions for the same.

3)

Select * from CLUB where (( fees > 60) AND (fees < 200))

4)

select mentor_names from CLUB where ((skill_id == 2) OR (skill_id == 4))

5)

select first_name,last_name from students where (last_name like A% OR last_name like B% OR last_name like C%)

6)

select student_id from students where (book_club = yes OR poetry_club = yes)

7)

select last_name from students where (id > 11,000 AND id<12,0000)

8)

select club_names from club where (membership < 0.5 * (select fees from club where name = soccer_club))

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.