DBMS Lab Assignment 2
Question No:-2
---------/*Student(Stud_Member table creation*/------------
2.
Consider the following schema and operate the queries
---------/*Student(Stud_Member table creation*/------------
create table Student
-> (
-> Roll_No
int not null primary key,
-> Fname
varchar(255) not null,
-> Mname
varchar(255) not null,
-> Sname
varchar(255) not null,
-> Dept_Id
int,
-> Semester
int not null,
-> Contact_No
int not null,
-> Gender
varchar(10) not null
-> );
Table schema looks like:----
Used Command:--->desc Student;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| Roll_No | int(11) | NO | PRI | NULL | |
| Fname | varchar(255) | NO | | NULL | |
| Mname | varchar(255) | NO | | NULL | |
| Sname | varchar(255) | NO | | NULL | |
| Dept_Id | int(11) | NO | MUL | NULL | |
| Semester | int(11) | NO | | NULL | |
| Contact_No | int(11) | NO | | NULL | |
| Gender | varchar(10) | NO | | NULL | |
+------------+--------------+------+-----+---------+-------+
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| Roll_No | int(11) | NO | PRI | NULL | |
| Fname | varchar(255) | NO | | NULL | |
| Mname | varchar(255) | NO | | NULL | |
| Sname | varchar(255) | NO | | NULL | |
| Dept_Id | int(11) | NO | MUL | NULL | |
| Semester | int(11) | NO | | NULL | |
| Contact_No | int(11) | NO | | NULL | |
| Gender | varchar(10) | NO | | NULL | |
+------------+--------------+------+-----+---------+-------+
-------------------/*Department
Table Creation */---------------------------
create table
Department
-> (
-> Dept_Id
int not null primary key,
-> Dept_Name
varchar(255) not null
-> );
Table Schema looks like:--->
Use Command:-->desc Department;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Dept_Id | int(11) | NO | PRI | NULL | |
| Dept_Name | varchar(255) | NO | | NULL | |
+-----------+--------------+------+-----+---------+-------+
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| Dept_Id | int(11) | NO | PRI | NULL | |
| Dept_Name | varchar(255) | NO | | NULL | |
+-----------+--------------+------+-----+---------+-------+
/*
For setting foreign key as Dept_Id */
alter table
Student add foreign key(Dept_Id) references Department(Dept_Id);
/*Trying
inserting some data to Student but getting error because if a
particular Dept_id
not present in Department table then that Department Id can not be
in Student table*/
/*
So firstly insert the values in Department table */
insert into
Department values(1,'Information Technology'),
->
(2,'Electrical'),
->
(3,'Civil'),
->
(4,'Mechanical'),
->
(5,'Chemical');
/*Table
will be look like as per Question so make that table(Department) as
it is */
/*Now
inserting values in Student table*/
insert into Student
values(1,'Ankur','Samir','Kahar',1,1,272121,'Male');
insert into Student
values(2,'Dhaval','Dhiren','Joshi',1,1,232122,'Male'),
->
(3,'Ankita','Biren','Shah',1,1,112121,'Female');
insert into Student
values(10,'Komal','Maheshkumar','Pandya',2,3,123123,'Female');
insert into Student
values(13,'Amit','Jitenkumar','Mehta',3,3,453667,'Male');
insert into Student
values(23,'Jinal','Ashish','Gandhi',2,1,323232,'Male');
insert into Student
values(22,'Ganesh','Asha','Patel',2,3,124244,'Male');
insert into Student
values(4,'Shweta','Mihir','Patel',3,1,646342,'Female');
insert into Student
values(7,'Pooja','Mayank','Desai',3,3,328656,'Female');
insert into Student
values(8,'Komal','Krishnaraj','Bhatia',2,3,257422,'Female');
insert into Student
values(43,'Kiran','Viraj','Shah',1,1,754124,'Female');
/*Now
table will look like as per in question ascending order according to
Roll_No*/
Questions
Q1.Display
the names and contact numbers of all student members.
--->select
Fname,Mname,Sname from Student;
+--------+-------------+--------+
| Fname | Mname | Sname
|
+--------+-------------+--------+
| Ankur | Samir | Kahar
|
| Dhaval | Dhiren | Joshi
|
| Ankita | Biren | Shah
|
| Shweta | Mihir | Patel
|
| Pooja | Mayank | Desai
|
| Komal | Krishnaraj |
Bhatia |
| Komal | Maheshkumar |
Pandya |
| Amit | Jitenkumar | Mehta
|
| Ganesh | Asha | Patel
|
| Jinal | Ashish |
Gandhi |
| Kiran | Viraj | Shah
|
+--------+-------------+--------+
Q2.Give
the names and roll no of all students of IT who are members.
--->select
distinct Roll_No,S.Fname,S.Mname,S.Sname from Student S,Department D
where S.Dept_Id=(select D.Dept_Id from Department D where
D.Dept_Name='Information Technology');
+---------+--------+--------+-------+
|
Roll_No | Fname | Mname | Sname |
+---------+--------+--------+-------+
|
1 | Ankur | Samir | Kahar |
|
2 | Dhaval | Dhiren | Joshi |
|
3 | Ankita | Biren | Shah |
|
43 | Kiran | Viraj | Shah |
+---------+--------+--------+-------+
Q3.Display
name of Departments whose students are members.
--->select
distinct Dept_Name from Department D,Student S where
D.Dept_id=S.Dept_Id;
+------------------------+
|
Dept_Name |
+------------------------+
|
Information Technology |
|
Electrical |
| Civil
|
+------------------------+
Q4.Display
names of Department for which no student are members
-->select
distinct D.Dept_Name from Department D where D.Dept_Name not
in(select distinct D.Dept_Name from Department D ,Student S where
S.Dept_Id=D.Dept_Id);
+------------+
|
Dept_Name |
+------------+
|
Mechanical |
|
Chemical |
+------------+
Q5.Display
name of all Departments.
-->select
Dept_Name from Department;
+------------------------+
|
Dept_Name |
+------------------------+
|
Information Technology |
|
Electrical |
| Civil
|
|
Mechanical |
|
Chemical |
+------------------------+
Q6.Find
the no of students of EE who are members.
--->select
count(distinct Roll_No) as Total_No from Student S,Department D where
S.Dept_Id=(select D.Dept_Id from Department D where
D.Dept_Name='Electrical');
Or
-->select
count(Roll_No) from Student S,Department D where S.Dept_Id=D.Dept_Id
and D.Dept_Name='Electrical';
+-------------------------+
|
Total_No |
+-------------------------+
|
4 |
+-------------------------+
Q7.Display
Information of student mem. Whose name begins with the letter A.
-->
select * from Student where Fname like "A%";
+---------+--------+------------+-------+---------+----------+------------+--------+
|
Roll_No | Fname | Mname | Sname | Dept_Id | Semester |
Contact_No | Gender |
+---------+--------+------------+-------+---------+----------+------------+--------+
|
1 | Ankur | Samir | Kahar | 1 | 1 | 272121 |
Male |
|
3 | Ankita | Biren | Shah | 1 | 1 | 112121 |
Female |
|
13 | Amit | Jitenkumar | Mehta | 3 | 3 | 453667 |
Male |
+---------+--------+------------+-------+---------+----------+------------+--------+
Q8.Display
all details of male members only.
-->select
* from Student where Gender='Male';
+---------+--------+------------+--------+---------+----------+------------+--------+
|
Roll_No | Fname | Mname | Sname | Dept_Id | Semester |
Contact_No | Gender |
+---------+--------+------------+--------+---------+----------+------------+--------+
|
1 | Ankur | Samir | Kahar | 1 | 1 | 272121 |
Male |
|
2 | Dhaval | Dhiren | Joshi | 1 | 1 | 232122 |
Male |
|
13 | Amit | Jitenkumar | Mehta | 3 | 3 | 453667 |
Male |
|
22 | Ganesh | Asha | Patel | 2 | 3 | 124244 |
Male |
|
23 | Jinal | Ashish | Gandhi | 2 | 1 | 323232 |
Male |
+---------+--------+------------+--------+---------+----------+------------+--------+
Q9.Display
data of students who are currently in semester 3.
--->
select *from Student where Semester=3;
+---------+--------+-------------+--------+---------+----------+------------+--------+
|
Roll_No | Fname | Mname | Sname | Dept_Id | Semester |
Contact_No | Gender |
+---------+--------+-------------+--------+---------+----------+------------+--------+
|
7 | Pooja | Mayank | Desai | 3 | 3 | 328656 |
Female |
|
8 | Komal | Krishnaraj | Bhatia | 2 | 3 | 257422 |
Female |
|
10 | Komal | Maheshkumar | Pandya | 2 | 3 | 123123
| Female |
|
13 | Amit | Jitenkumar | Mehta | 3 | 3 | 453667
| Male |
|
22 | Ganesh | Asha | Patel | 2 | 3 | 124244
| Male |
+---------+--------+-------------+--------+---------+----------+------------+--------+
Q10.Display
data of student female member in alphbetical order.
--->select
* from Student where Gender='Female' order by Fname,Mname,Sname;
+---------+--------+-------------+--------+---------+----------+------------+--------+
|
Roll_No | Fname | Mname | Sname | Dept_Id | Semester |
Contact_No | Gender |
+---------+--------+-------------+--------+---------+----------+------------+--------+
|
3 | Ankita | Biren | Shah | 1 | 1 | 112121 |
Female |
|
43 | Kiran | Viraj | Shah | 1 | 1 | 754124
| Female |
|
8 | Komal | Krishnaraj | Bhatia | 2 | 3 | 257422 |
Female |
|
10 | Komal | Maheshkumar | Pandya | 2 | 3 | 123123
| Female |
|
7 | Pooja | Mayank | Desai | 3 | 3 | 328656 |
Female |
|
4 | Shweta | Mihir | Patel | 3 | 1 | 646342 |
Female |
+---------+--------+-------------+--------+---------+----------+------------+--------+
No comments:
Post a Comment