Database
Management System
( Practicals )
Name : Priya Tiwari
Course : Bsc . Computer Science
(Hons) ( Sem – 4) .
Roll no. : 23HCS4138
Submitted to : Ms. Shweta Mam .
Student-Society SQL
Practical Questions and
Solutions .
Create Tables
Output :
Insert Dummy Data
Output :
1. Retrieve names of students
enrolled in any society.
STUDENTNAME
--------------------
Alice
Aaron
Bella
Arjun
Rahul
Sneha
Riya
Karan
Divya
Ankit
Mohit
STUDENTNAME
--------------------
Neha
Vivek
13 rows selected.
2. . Retrieve all society names.
SOCNAME
--------------------
Debating
Dancing
Sanskrit
Sports
Music
Drama
Painting
Literature
Debate
Silent
NSS
11 rows selected.
3. Retrieve students ‘ name
starting with the letter ‘ A’ .
4. retrieve students details
studying in courses “ computer
science” or “ chemistry .
5. Retrieve students’ names
whose roll no. either starts with
“x” or “z” and ends with "9” .
6. Find society details with more
than N Totalseats .
7. Update society table for
mentor name of a specific
society .
8. Find society names in which
more than five students have
enrolled.
9. Find the name of the
youngest student enrolled in
society “NSS” .
10 . Find the name of the most
popular society (on the basis of
enrolled students).
11. Find the name of two least
popular societies (on the basis
of enrolled students ).
12. Find the students names
who are not enrolled in any
society .
13. Find the students names
enrolled in at least two
societies .
STUDENTNAME
--------------------
Alice
Aaron
Bella
Arjun
Rahul
Sneha
Riya
Karan
Divya
Ankit
Mohit
STUDENTNAME
--------------------
Neha
12 rows selected.
14. Find the society in which
maximum students are enrolled .
15. Find the names of all
students who have enrolled in
any society and society names in
which at least one student has
enrolled .
NAME TYPE
-------------------- -------
Arjun Student
Rahul Student
Sneha Student
Riya Student
Karan Student
Divya Student
Ankit Student
Mohit Student
Neha Student
Alice Student
Bella Student
NAME TYPE
-------------------- -------
Aaron Student
Debating Society
Sanskrit Society
Sports Society
Music Society
NSS Society
Dancing Society
Drama Society
Painting Society
Literature Society
Debate Society
NAME TYPE
-------------------- -------
Silent Society
23 rows selected.
16 . Find the society names such
that its mentor has a name with
“ Gupta “ in it .
17. Find the society names in
which the number of enrolments
is only 10% of its capacity .
18. Display the vacant seats for
reach society .
SOCNAME TOTALSEATS ENROLLED VACANTSEATS
-------------------- ---------- ---------- -----------
Debating 10 7 3
Sanskrit 30 4 26
Sports 25 2 23
Music 15 2 13
NSS 20 2 18
Dancing 20 2 18
Drama 12 3 9
Painting 40 3 37
Literature 8 2 6
Debate 5 1 4
Silent 10 1 9
11 rows selected.
19 . Increment Total seats of
each society by 10% .
20 . Add the enrolment fees paid
( yes/no) field in the enrollment
table .
21. Update date of enrolment of
society is “s1” to “2018-01-15
“ , “s2” to the current date and
“s3” to “2018-01-02”.
22. Create a view to keep track
of society names with the total
number of students enrolled in
it.
View created.
SOCNAME TOTALENROLLED
-------------------- -------------
Debating 7
Sanskrit 4
Sports 2
Music 2
NSS 2
Dancing 2
Drama 3
Painting 3
Literature 2
Debate 1
Silent 1
11 rows selected.
23. Find the student names
enrolled in all the societies.
24. Count the number of
societies with more than 5
students in it .
25. Add column mobile number
in student table with default
value “ 9999999999” .
26. Find the total number of
students whose age is > 20
years .
27. Find the names of students
who were born in 2001 and are
enrolled in at least one society .
28. Count all societies whose
name starts with “ S” and ends
with “t” and at least 5 students
are enrolled in the society .
29 . Display the following
information :
Society name , mentor name ,
total capacity , total enrolled ,
unified seats .
SOCNAME MENTORNAME TOTALSEATS TOTALENROLLED
UNFILLEDSEATS
-------------------- --------------- ---------- ------------- -------------
Debating Rakesh Gupta 10 7 3
Sanskrit Anil Sharma 30 4 26
Sports Sunil Gupta 25 2 23
Music Neha Verma 15 2 13
NSS Amit Gupta 20 2 18
Dancing Meena Kumari 20 2 18
Drama Rajeev Singh 12 3 9
Painting Harshita Gupta 40 3 37
Literature Rahul Joshi 8 2 6
Debate Tarun Gupta 5 1 4
Silent Priya Tiwari 10 1 9
11 rows selected.
30 . Do the following database
administration commands :
Create user , create role , grant
privileges to a role , revoke
privileges from a role , create
index .
