1 Execute a SQL statement to display all the columns in the

1. Execute a SQL statement to display all the columns in the table CLUB for the club named Poetry.

2. Execute a SQL statement to display the columns FirstName and LastName in the table STUDENT, where the student has the first name Liz and last name Chen.

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.

21. Display the name of the club that charges the lowest membership fee.

22. Find out how many students are mentored by Smith.

23. Find out how many students are mentored by each mentor.

24. By using the HAVING clause, display the names of the clubs and the last names of the students who have enrolled in more than one club.

Solution

Answer

1. Execute a SQL statement to display all the columns in the table CLUB for the club named Poetry.

Select * from CLUB where club_name=\"Poetry\"

2. Execute a SQL statement to display the columns FirstName and LastName in the table STUDENT, where the student has the first name Liz and last name Chen.

Select FirstName,LastName from STUDENT where FirstName=\"Liz\" and LastName=\"Chen\"

3. Execute a SQL statement to display all the columns in the table CLUB for the membership fee between $60 and $200.

Select * from CLUB where fee >=60 and fee<=200

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

Select name from mentor where skilled =2 or skilled=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.

Select FirstName,LastName from STUDENT where LastName=\"A%\" or LastName=\"B%\" or LastName=\"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.

select id from student where member != \"Book club\" or member != \"poetry club\"

7. Execute a SQL statement to display the last names of the students whose ids are between 11000 and 12000.

Select Lastname from student where ids >=11000 and ids<=12000