Lab 2 Demo

Question 2

mysql> use dc2;
Database changed
mysql> create table student
    -> (roll_no int primary key
    -> ,
    -> fname varchar(50),
    -> mname varchar(50),
    -> sname varchar(50),
    -> dept_id int,
    -> semester int,
    -> contact_no int,
    -> gender varchar(1)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> desc student;
+------------+-------------+--

----+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| roll_no    | int(11)     | NO   | PRI | NULL    |       |
| fname      | varchar(50) | YES  |     | NULL    |       |
| mname      | varchar(50) | YES  |     | NULL    |       |
| sname      | varchar(50) | YES  |     | NULL    |       |
| dept_id    | int(11)     | YES  |     | NULL    |       |
| semester   | int(11)     | YES  |     | NULL    |       |
| contact_no | int(11)     | YES  |     | NULL    |       |
| gender     | varchar(1)  | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)mysql> create table department(
    -> dept_id int,
    -> dept_name varchar(50));
Query OK, 0 rows affected (0.06 sec)mysql> alter department add primary key(dept_id);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'department add primary key(dept_id)' at line 1
mysql> alter table department add primary key(dept_id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> alter table student  add foreign key(dept_id) reference department(dept_id);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'reference department(dept_id)' at line 1
mysql> alter table student  add foreign key(dept_id) references department(dept_id);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc student;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| roll_no    | int(11)     | NO   | PRI | NULL    |       |
| fname      | varchar(50) | YES  |     | NULL    |       |
| mname      | varchar(50) | YES  |     | NULL    |       |
| sname      | varchar(50) | YES  |     | NULL    |       |
| dept_id    | int(11)     | YES  | MUL | NULL    |       |
| semester   | int(11)     | YES  |     | NULL    |       |
| contact_no | int(11)     | YES  |     | NULL    |       |
| gender     | varchar(1)  | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)mysql> desc department;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id   | int(11)     | NO   | PRI | 0       |       |
| dept_name | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> insert into department values(1,"Information Technology");
Query OK, 1 row affected (0.00 sec)mysql> insert into department values(2,"Electrical");
Query OK, 1 row affected (0.00 sec)mysql> insert into department values(3,"Civil");
Query OK, 1 row affected (0.00 sec)mysql> insert into department values(4,"Mechanical");
Query OK, 1 row affected (0.00 sec)mysql> insert into department values(5,"Chemical");
Query OK, 1 row affected (0.00 sec)mysql> select * from department;
+---------+------------------------+
| dept_id | dept_name              |
+---------+------------------------+
|       1 | Information Technology |
|       2 | Electrical             |
|       3 | Civil                  |
|       4 | Mechanical             |
|       5 | Chemical               |
+---------+------------------------+
5 rows in set (0.00 sec)mysql> insert into student values(1,"ankur", "samir","kahar",1,1,272121,"M");
Query OK, 1 row affected (0.00 sec)mysql> insert into student values(2,"dhaval", "dhiren","joshi",1,1,232122,"M");
Query OK, 1 row affected (0.00 sec)mysql> insert into student values(3,"ankita", "biren","shah",1,1,112121,"F");
Query OK, 1 row affected (0.00 sec)mysql> insert into student values(10,"komal", "maheshkumar","pandya",2,3,123123,"F");
Query OK, 1 row affected (0.00 sec)mysql> insert into student values(13,"amit", "jitenkumar","mehta",3,3,453667,"M");
Query OK, 1 row affected (0.00 sec)mysql> insert into student values(23,"Jinal", "Ashish","Gandhi",2,1,323232,"M");
Query OK, 1 row affected (0.00 sec)mysql> select * from students;
ERROR 1146 (42S02): Table 'dc2.students' doesn't exist
mysql> select * from student;
+---------+--------+-------------+--------+---------+----------+------------+--------+
| roll_no | fname  | mname       | sname  | dept_id | semester | contact_no | gender |
+---------+--------+-------------+--------+---------+----------+------------+--------+
|       1 | ankur  | samir       | kahar  |       1 |        1 |     272121 | M      |
|       2 | dhaval | dhiren      | joshi  |       1 |        1 |     232122 | M      |
|       3 | ankita | biren       | shah   |       1 |        1 |     112121 | F      |
|      10 | komal  | maheshkumar | pandya |       2 |        3 |     123123 | F      |
|      13 | amit   | jitenkumar  | mehta  |       3 |        3 |     453667 | M      |
|      23 | Jinal  | Ashish      | Gandhi |       2 |        1 |     323232 | M      |
+---------+--------+-------------+--------+---------+----------+------------+--------+
6 rows in set (0.00 sec)mysql> insert into student values(22,"Ganesh", "Asha","Patel",2,3,124244,"M");Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(4,"Shweta", "Mihir","Patel",3,1,646342,"F");
Query OK, 1 row affected (0.00 sec)mysql> insert into student values(7,"Pooja", "Mayank","Desai",3,3,328656,"F");
Query OK, 1 row affected (0.00 sec)mysql> insert into student values(8,"Komal", "Krishnaraj","Bhatia",2,3,257411,"F");
Query OK, 1 row affected (0.00 sec)mysql> insert into student values(43,"Kiran", "Viraj","Shah",1,1,754124,"F");Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+---------+--------+-------------+--------+---------+----------+------------+--------+
| roll_no | fname  | mname       | sname  | dept_id | semester | contact_no | gender |
+---------+--------+-------------+--------+---------+----------+------------+--------+
|       1 | ankur  | samir       | kahar  |       1 |        1 |     272121 | M      |
|       2 | dhaval | dhiren      | joshi  |       1 |        1 |     232122 | M      |
|       3 | ankita | biren       | shah   |       1 |        1 |     112121 | F      |
|      10 | komal  | maheshkumar | pandya |       2 |        3 |     123123 | F      |
|      13 | amit   | jitenkumar  | mehta  |       3 |        3 |     453667 | M      |
|      23 | Jinal  | Ashish      | Gandhi |       2 |        1 |     323232 | M      |
|      22 | Ganesh | Asha        | Patel  |       2 |        3 |     124244 | M      |
|       4 | Shweta | Mihir       | Patel  |       3 |        1 |     646342 | F      |
|       7 | Pooja  | Mayank      | Desai  |       3 |        3 |     328656 | F      |
|       8 | Komal  | Krishnaraj  | Bhatia |       2 |        3 |     257411 | F      |
|      43 | Kiran  | Viraj       | Shah   |       1 |        1 |     754124 | F      |
+---------+--------+-------------+--------+---------+----------+------------+--------+
11 rows in set (0.00 sec)

mysql> select fname,contact_no from student;
+--------+------------+
| fname  | contact_no |
+--------+------------+
| ankur  |     272121 |
| dhaval |     232122 |
| ankita |     112121 |
| komal  |     123123 |
| amit   |     453667 |
| Jinal  |     323232 |
| Ganesh |     124244 |
| Shweta |     646342 |
| Pooja  |     328656 |
| Komal  |     257411 |
| Kiran  |     754124 |
+--------+------------+
11 rows in set (0.00 sec)

mysql> select fname,contact_no from student where dept_name="Information Technology";
ERROR 1054 (42S22): Unknown column 'dept_name' in 'where clause'
mysql> select fname,contact_no from student s, department d,  where s.dept_id=d.dept_id and d.dept_name="Information Technology";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where s.dept_id=d.dept_id and d.dept_name="Information Technology"' at line 1
mysql> select fname,contact_no from student s, department d  where s.dept_id=d.dept_id and d.dept_name="Information Technology";
+--------+------------+
| fname  | contact_no |
+--------+------------+
| ankur  |     272121 |
| dhaval |     232122 |
| ankita |     112121 |
| Kiran  |     754124 |
+--------+------------+
4 rows in set (0.00 sec)mysql> select dept_name from department, student where department.dept_id=student.dept_id;
+------------------------+
| dept_name              |
+------------------------+
| Information Technology |
| Information Technology |
| Information Technology |
| Information Technology |
| Electrical             |
| Electrical             |
| Electrical             |
| Electrical             |
| Civil                  |
| Civil                  |
| Civil                  |
+------------------------+
11 rows in set (0.00 sec)

mysql> select dept_name from department, student where department.dept_id=student.dept_id distinct;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct' at line 1
mysql> select distinct dept_name from department, student where department.dept_id=student.dept_id;
+------------------------+
| dept_name              |
+------------------------+
| Information Technology |
| Electrical             |
| Civil                  |
+------------------------+
3 rows in set (0.00 sec)mysql> select dept_name from department where dept_name not in (select distinct dept_name from department, student where department.dept_id=student.dept_id);
+------------+
| dept_name  |
+------------+
| Mechanical |
| Chemical   |
+------------+
2 rows in set (0.02 sec)

mysql> select dept_name from department;
+------------------------+
| dept_name              |
+------------------------+
| Information Technology |
| Electrical             |
| Civil                  |
| Mechanical             |
| Chemical               |
+------------------------+
5 rows in set (0.00 sec)mysql> select count (fname from student,department where department.dept_name="Electrical" and student.dept_id=department.dept_id);ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from student,department where department.dept_name="Electrical" and student.dept' at line 1
mysql> select count (select fname from student,department where department.dept_name="Electrical" and student.dept_id=department.dept_id);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select fname from student,department where department.dept_name="Electrical" and' at line 1
mysql> select count(roll_no) from student,department where department.dept_name="Electrical" and student.dept_id=department.dept_id;
+----------------+
| count(roll_no) |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)mysql> select * from student where name like "a%";
ERROR 1054 (42S22): Unknown column 'name' in 'where clause'
mysql> 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 | M      |
|       3 | ankita | biren      | shah  |       1 |        1 |     112121 | F      |
|      13 | amit   | jitenkumar | mehta |       3 |        3 |     453667 | M      |
+---------+--------+------------+-------+---------+----------+------------+--------+
3 rows in set (0.00 sec)mysql> select * from student where gender="M";
+---------+--------+------------+--------+---------+----------+------------+--------+
| roll_no | fname  | mname      | sname  | dept_id | semester | contact_no | gender |
+---------+--------+------------+--------+---------+----------+------------+--------+
|       1 | ankur  | samir      | kahar  |       1 |        1 |     272121 | M      |
|       2 | dhaval | dhiren     | joshi  |       1 |        1 |     232122 | M      |
|      13 | amit   | jitenkumar | mehta  |       3 |        3 |     453667 | M      |
|      23 | Jinal  | Ashish     | Gandhi |       2 |        1 |     323232 | M      |
|      22 | Ganesh | Asha       | Patel  |       2 |        3 |     124244 | M      |
+---------+--------+------------+--------+---------+----------+------------+--------+
5 rows in set (0.00 sec)mysql> select * from student where gender="m";
+---------+--------+------------+--------+---------+----------+------------+--------+
| roll_no | fname  | mname      | sname  | dept_id | semester | contact_no | gender |
+---------+--------+------------+--------+---------+----------+------------+--------+
|       1 | ankur  | samir      | kahar  |       1 |        1 |     272121 | M      |
|       2 | dhaval | dhiren     | joshi  |       1 |        1 |     232122 | M      |
|      13 | amit   | jitenkumar | mehta  |       3 |        3 |     453667 | M      |
|      23 | Jinal  | Ashish     | Gandhi |       2 |        1 |     323232 | M      |
|      22 | Ganesh | Asha       | Patel  |       2 |        3 |     124244 | M      |
+---------+--------+------------+--------+---------+----------+------------+--------+
5 rows in set (0.00 sec)mysql> select * from student where semester=3;
+---------+--------+-------------+--------+---------+----------+------------+--------+
| roll_no | fname  | mname       | sname  | dept_id | semester | contact_no | gender |
+---------+--------+-------------+--------+---------+----------+------------+--------+
|      10 | komal  | maheshkumar | pandya |       2 |        3 |     123123 | F      |
|      13 | amit   | jitenkumar  | mehta  |       3 |        3 |     453667 | M      |
|      22 | Ganesh | Asha        | Patel  |       2 |        3 |     124244 | M      |
|       7 | Pooja  | Mayank      | Desai  |       3 |        3 |     328656 | F      |
|       8 | Komal  | Krishnaraj  | Bhatia |       2 |        3 |     257411 | F      |
+---------+--------+-------------+--------+---------+----------+------------+--------+
5 rows in set (0.00 sec)mysql> select * from student,department where semester=3;
+---------+--------+-------------+--------+---------+----------+------------+--------+---------+------------------------+
| roll_no | fname  | mname       | sname  | dept_id | semester | contact_no | gender | dept_id | dept_name              |
+---------+--------+-------------+--------+---------+----------+------------+--------+---------+------------------------+
|      10 | komal  | maheshkumar | pandya |       2 |        3 |     123123 | F      |       1 | Information Technology |
|      10 | komal  | maheshkumar | pandya |       2 |        3 |     123123 | F      |       2 | Electrical             |
|      10 | komal  | maheshkumar | pandya |       2 |        3 |     123123 | F      |       3 | Civil                  |
|      10 | komal  | maheshkumar | pandya |       2 |        3 |     123123 | F      |       4 | Mechanical             |
|      10 | komal  | maheshkumar | pandya |       2 |        3 |     123123 | F      |       5 | Chemical               |
|      13 | amit   | jitenkumar  | mehta  |       3 |        3 |     453667 | M      |       1 | Information Technology |
|      13 | amit   | jitenkumar  | mehta  |       3 |        3 |     453667 | M      |       2 | Electrical             |
|      13 | amit   | jitenkumar  | mehta  |       3 |        3 |     453667 | M      |       3 | Civil                  |
|      13 | amit   | jitenkumar  | mehta  |       3 |        3 |     453667 | M      |       4 | Mechanical             |
|      13 | amit   | jitenkumar  | mehta  |       3 |        3 |     453667 | M      |       5 | Chemical               |
|      22 | Ganesh | Asha        | Patel  |       2 |        3 |     124244 | M      |       1 | Information Technology |
|      22 | Ganesh | Asha        | Patel  |       2 |        3 |     124244 | M      |       2 | Electrical             |
|      22 | Ganesh | Asha        | Patel  |       2 |        3 |     124244 | M      |       3 | Civil                  |
|      22 | Ganesh | Asha        | Patel  |       2 |        3 |     124244 | M      |       4 | Mechanical             |
|      22 | Ganesh | Asha        | Patel  |       2 |        3 |     124244 | M      |       5 | Chemical               |
|       7 | Pooja  | Mayank      | Desai  |       3 |        3 |     328656 | F      |       1 | Information Technology |
|       7 | Pooja  | Mayank      | Desai  |       3 |        3 |     328656 | F      |       2 | Electrical             |
|       7 | Pooja  | Mayank      | Desai  |       3 |        3 |     328656 | F      |       3 | Civil                  |
|       7 | Pooja  | Mayank      | Desai  |       3 |        3 |     328656 | F      |       4 | Mechanical             |
|       7 | Pooja  | Mayank      | Desai  |       3 |        3 |     328656 | F      |       5 | Chemical               |
|       8 | Komal  | Krishnaraj  | Bhatia |       2 |        3 |     257411 | F      |       1 | Information Technology |
|       8 | Komal  | Krishnaraj  | Bhatia |       2 |        3 |     257411 | F      |       2 | Electrical             |
|       8 | Komal  | Krishnaraj  | Bhatia |       2 |        3 |     257411 | F      |       3 | Civil                  |
|       8 | Komal  | Krishnaraj  | Bhatia |       2 |        3 |     257411 | F      |       4 | Mechanical             |
|       8 | Komal  | Krishnaraj  | Bhatia |       2 |        3 |     257411 | F      |       5 | Chemical               |
+---------+--------+-------------+--------+---------+----------+------------+--------+---------+------------------------+
25 rows in set (0.00 sec)

mysql> select * from student,department where semester=3 and student.dept_id=department.dept_id;
+---------+--------+-------------+--------+---------+----------+------------+--------+---------+------------+
| roll_no | fname  | mname       | sname  | dept_id | semester | contact_no | gender | dept_id | dept_name  |
+---------+--------+-------------+--------+---------+----------+------------+--------+---------+------------+
|      10 | komal  | maheshkumar | pandya |       2 |        3 |     123123 | F      |       2 | Electrical |
|      22 | Ganesh | Asha        | Patel  |       2 |        3 |     124244 | M      |       2 | Electrical |
|       8 | Komal  | Krishnaraj  | Bhatia |       2 |        3 |     257411 | F      |       2 | Electrical |
|      13 | amit   | jitenkumar  | mehta  |       3 |        3 |     453667 | M      |       3 | Civil      |
|       7 | Pooja  | Mayank      | Desai  |       3 |        3 |     328656 | F      |       3 | Civil      |
+---------+--------+-------------+--------+---------+----------+------------+--------+---------+------------+
5 rows in set (0.00 sec)mysql> select * from student where gender="f" order by fname;
+---------+--------+-------------+--------+---------+----------+------------+--------+
| roll_no | fname  | mname       | sname  | dept_id | semester | contact_no | gender |
+---------+--------+-------------+--------+---------+----------+------------+--------+
|       3 | ankita | biren       | shah   |       1 |        1 |     112121 | F      |
|      43 | Kiran  | Viraj       | Shah   |       1 |        1 |     754124 | F      |
|      10 | komal  | maheshkumar | pandya |       2 |        3 |     123123 | F      |
|       8 | Komal  | Krishnaraj  | Bhatia |       2 |        3 |     257411 | F      |
|       7 | Pooja  | Mayank      | Desai  |       3 |        3 |     328656 | F      |
|       4 | Shweta | Mihir       | Patel  |       3 |        1 |     646342 | F      |
+---------+--------+-------------+--------+---------+----------+------------+--------+
6 rows in set (0.00 sec)