Database: 2015

Wednesday, January 14, 2015

Lab 2

Assignment 2

Retrieve the required information using SQL language.

Give a database schema for a library management system as the following picture.

$C:\Users\Rajitha\AppData\Local\Temp\ksohtml\wpsD2CE.tmp.png$

How many copies of the book titled “********” are owned by the library branch whose name is "******"?
How many copies of the book titled "******" are owned by each library branch?
Retrieve the names of all borrowers who do not have any books checked out.
For each book that is loaned out from the "******" branch and whose DueDate is today, retrieve the book title, the borrower's name, and the borrower's address.
For each library branch, retrieve the branch name and the total number of books loaned out from that branch.
Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.

For each book authored (or co-authored) by "******", retrieve the title a

2. Consider the following schema and operate the queries

$C:\Users\Rajitha\AppData\Local\Temp\ksohtml\wps805C.tmp.png$

$C:\Users\Rajitha\AppData\Local\Temp\ksohtml\wps805D.tmp.png$

2.select fname,contact_no from student s, department d where s.dept_id=d.dept_id and d.dept_name="Information Technology";

3.select distinct dept_name from department, student where department.dept_id=student.dept_id;

4.select dept_name from department where dept_name not in (select distinct dept_name from department, student where department.dept_id=student.dept_id);

5.select dept_name from department;

6.select count(roll_no) from student,department where department.dept_name="Electrical" and student.dept_id=department.dept_id;

7.select * from student where fname like "a%";

8.select * from student where gender="M";

9.select * from student,department where semester=3 and student.dept_id=department.dept_id;

10. select * from student where gender="f" order by fname;

$C:\Users\Rajitha\AppData\Local\Temp\ksohtml\wps806D.tmp.png$

$C:\Users\Rajitha\AppData\Local\Temp\ksohtml\wps807E.tmp.png$

1. Display product information which are ordered in the same year of its manufacturing year.

2. Display product information which are ordered in the same year of its manufacturing year where vender is „smith‟.

3. Display total no. of orders placed in each year.

4. Display total no. of orders placed in each year by vender Wills.

5. Display the name of all those persons who are venders and customers both.

6. Display total no. of food items ordered every year.

7. Display total no. of food items ordered every year made from Bread.

8. Display list of product_id whose vender and customer is different.

9. Display all those customers who are ordering products of milk by smith.

10. Display total no. of orders by each vender every year.

11. Write a PL-SQL script to compare three given numbers and display them in ascending order.

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)

Lab 1 Demo

[root@localhost user]# service mysqld restart
Stopping mysqld:                                           [ OK ]
Starting mysqld:                                           [ OK ]

[root@localhost user]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

mysql> create database lab1;
Query OK, 1 row affected (0.00 sec)

mysql> use lab1;
Database changed
mysql> create table friends(name varchar(50) primary key, mobile int);
Query OK, 0 rows affected (0.07 sec)

mysql> desc friends;
+--------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(50) | NO   | PRI | NULL    |       |
| mobile | int(11)     | YES |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into friends values("Supriya",9*********9);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into friends values("Rohit",9415106502);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into friends values("Anurag",9415635651);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into friends values("Nishant",8756145111);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from friends;
+---------+------------+
| name    | mobile     |
+---------+------------+
| Supriya | 2147483647 |
| Rohit   | 2147483647 |
| Anurag | 2147483647 |
| Nishant | 2147483647 |
+---------+------------+
4 rows in set (0.00 sec)

mysql> truncate table friends;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from friends;
Empty set (0.00 sec)

mysql> drop table friends;
Query OK, 0 rows affected (0.00 sec)

mysql> create table friends(name varchar(50) primary key, mobile int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into friends values("Supriya",9********9);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into friends values("Rohit",9415106502);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into friends values("Anurag",9415635651);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into friends values("Nishant",8756145111);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> rename table friends to dost;
Query OK, 0 rows affected (0.00 sec)

----------------------------------------------------------------------------------------------

Exercise 3

mysql> create table item( id int primary key, name varchar(50) not null, price float not null, exp date, quantity int not null );
--------------
create table item( id int primary key, name varchar(50) not null, price float not null, exp date, quantity int not null )
--------------

Query OK, 0 rows affected (0.08 sec)

mysql> insert into item values(1,sauce,15.50,10/11/

2016,10);
--------------
insert into item values(1,sauce,15.50,10/11/2016,10)
--------------

ERROR 1054 (42S22): Unknown column 'sauce' in 'field list'
mysql> insert into item values(1,"sauce",15.50,10/11/2016,10);
--------------
insert into item values(1,"sauce",15.50,10/11/2016,10)
--------------

Query OK, 1 row affected (0.00 sec)

mysql> insert into item values(1,"burger",25.50,10/11/2016,5);
--------------
insert into item values(1,"burger",25.50,10/11/2016,5)
--------------

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into item values(2,"burger",25.50,10/11/2016,5);
--------------
insert into item values(2,"burger",25.50,10/11/2016,5)
--------------

Query OK, 1 row affected (0.00 sec)

mysql> insert into item values(3,"chicken",80,10/11/2016,1);
--------------
insert into item values(3,"chicken",80,10/11/2016,1)
--------------

Query OK, 1 row affected (0.00 sec)

mysql> insert into item values(4,"mutton",80,10/11/2016,1);
--------------
insert into item values(4,"mutton",80,10/11/2016,1)
--------------

Query OK, 1 row affected (0.00 sec)

mysql> Select * from item;
--------------
Select * from item
--------------

+----+---------+-------+------------+----------+
| id | name    | price | exp        | quantity |
+----+---------+-------+------------+----------+
| 1 | sauce   | 15.5 | 0000-00-00 |       10 |
| 2 | burger | 25.5 | 0000-00-00 |        5 |
| 3 | chicken |    80 | 0000-00-00 |        1 |
| 4 | mutton |    80 | 0000-00-00 |        1 |
+----+---------+-------+------------+----------+
4 rows in set (0.00 sec)

mysql> select name,price from item where quantity>10
    -> ;
--------------
select name,price from item where quantity>10
--------------

Empty set (0.00 sec)

mysql> select name,price from item where quantity>5;
--------------
select name,price from item where quantity>5
--------------

+-------+-------+
| name | price |
+-------+-------+
| sauce | 15.5 |
+-------+-------+
1 row in set (0.00 sec)

mysql> select * from items where expiry>curDate;
--------------
select * from items where expiry>curDate
--------------

ERROR 1146 (42S02): Table 'dhruv.items' doesn't exist
mysql> select * from item where expiry>curDate;
--------------
select * from item where expiry>curDate
--------------

ERROR 1054 (42S22): Unknown column 'expiry' in 'where clause'
mysql> select * from item where exp>curDate;
--------------
select * from item where exp>curDate
--------------

ERROR 1054 (42S22): Unknown column 'curDate' in 'where clause'
mysql> select * from item where exp>curdate();
--------------
select * from item where exp>curdate()
--------------

Empty set (0.00 sec)

mysql> select * from item where exp<curdate();
--------------
select * from item where exp<curdate()
--------------

+----+---------+-------+------------+----------+
| id | name    | price | exp        | quantity |
+----+---------+-------+------------+----------+
| 1 | sauce   | 15.5 | 0000-00-00 |       10 |
| 2 | burger | 25.5 | 0000-00-00 |        5 |
| 3 | chicken |    80 | 0000-00-00 |        1 |
| 4 | mutton |    80 | 0000-00-00 |        1 |
+----+---------+-------+------------+----------+
4 rows in set (0.00 sec)

mysql> select * from item;
--------------
select * from item
--------------

+----+---------+-------+------------+----------+
| id | name    | price | exp        | quantity |
+----+---------+-------+------------+----------+
| 1 | sauce   | 15.5 | 0000-00-00 |       10 |
| 2 | burger | 25.5 | 0000-00-00 |        5 |
| 3 | chicken |    80 | 0000-00-00 |        1 |
| 4 | mutton |    80 | 0000-00-00 |        1 |
+----+---------+-------+------------+----------+
4 rows in set (0.00 sec)

mysql> insert into item values(4,"mutton",80,2015-11-10,1);
--------------
insert into item values(4,"mutton",80,2015-11-10,1)
--------------

ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
mysql> insert into item values(5,"mutton",80,2015-11-10,1);
--------------
insert into item values(5,"mutton",80,2015-11-10,1)
--------------

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from item where exp>curdate();
--------------
select * from item where exp>curdate()
--------------

Empty set (0.00 sec)

mysql> select * from item;
--------------
select * from item
--------------

+----+---------+-------+------------+----------+
| id | name    | price | exp        | quantity |
+----+---------+-------+------------+----------+
| 1 | sauce   | 15.5 | 0000-00-00 |       10 |
| 2 | burger | 25.5 | 0000-00-00 |        5 |
| 3 | chicken |    80 | 0000-00-00 |        1 |
| 4 | mutton |    80 | 0000-00-00 |        1 |
| 5 | mutton |    80 | 0000-00-00 |        1 |
+----+---------+-------+------------+----------+
5 rows in set (0.00 sec)

mysql> insert into item values(6,"mutton",80,'2015-11-10',1);
--------------
insert into item values(6,"mutton",80,'2015-11-10',1)
--------------

Query OK, 1 row affected (0.00 sec)

mysql> select * from item;
--------------
select * from item
--------------

+----+---------+-------+------------+----------+
| id | name    | price | exp        | quantity |
+----+---------+-------+------------+----------+
| 1 | sauce   | 15.5 | 0000-00-00 |       10 |
| 2 | burger | 25.5 | 0000-00-00 |        5 |
| 3 | chicken |    80 | 0000-00-00 |        1 |
| 4 | mutton |    80 | 0000-00-00 |        1 |
| 5 | mutton |    80 | 0000-00-00 |        1 |
| 6 | mutton |    80 | 2015-11-10 |        1 |
+----+---------+-------+------------+----------+
6 rows in set (0.00 sec)

mysql> select * from item where order by price desc limit 1,1
    -> ;
--------------
select * from item where order by price desc limit 1,1
--------------

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 'order by price desc limit 1,1' at line 1
mysql> select * from item order by price desc limit 1,1;
--------------
select * from item order by price desc limit 1,1
--------------

+----+--------+-------+------------+----------+
| id | name   | price | exp        | quantity |
+----+--------+-------+------------+----------+
| 4 | mutton |    80 | 0000-00-00 |        1 |
+----+--------+-------+------------+----------+
1 row in set (0.00 sec)

mysql> alter table items add check (price>=0);
--------------
alter table items add check (price>=0)
--------------

ERROR 1146 (42S02): Table 'dhruv.items' doesn't exist
mysql> alter items add check (price>=0);
--------------
alter items add check (price>=0)
--------------

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 'items add check (price>=0)' at line 1
mysql> alter table item add check (price>=0);
--------------
alter table item add check (price>=0)
--------------

Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

Lab 1

Exercise 1 (practice on SQL commands)

a ) DDL commands

Creating a table

create table friends ( id int primary key, first varchar(50) not null , last varchar(50) not null);

Altering a table

alter table friends add constraint chk_id check(id<10);

b) DML commands

Drop a table

drop table friends;

Truncating a table

truncate table friends;

Rename

rename table friends to dost;

Inserting Rows into table

insert into friends values(3,"Aman","Nigam")

Deleting Rows from table

delete from friends where first="ROHIT";

Updating data in a table

update friends set last="momos wala" where first="ayush"

Defining constraints

alter table friends add constraint chk_id check(id<10);
-----------------------------------------------------------------------------------------------------------------------------

Exercise 2( Assignment on DDL and DML commands )

A Suppose that a faculty wants to keep records about the students, the desired records should maintain this information about each student:

(Roll No, Full name, CGPA, Age, and Gender)

a) Create the appropriate Table.

create table student ( roll int primary key, name varchar(50) not null, cgpa float not null, age int not null, gender varchar(10) not null );

insert into student values(20123047,"Dhruv",8.52,20,"male");
insert into student values(20124072,"Arpit Rathi",9.2,21,"male");
insert into student values(20128058,"Aashish Tyagrajan",6.8,20,"male");
insert into student values(20125050,"Aman Mittal",8.17,21,"male");
insert into student values(20120053,"Supriya Singh",8.75,19,"female");

b) Write SQL Queries to answer the following problems:

List the names of all female students.
SELECT * from student where gender="female";
What is the age of the youngest male student?
Select name,age from student where gender like "male" order by age asc limit 1;
List the name and the CGPA for all students who got above 6.5 CGPA ordered by their CGPAs then their names.
Select cgpa,name from student where CGPA>6.5 order by CGPA desc;
What is the Topper name?
Select cgpa,name from student where CGPA>6.5 order by CGPA desc limit 1;

c) Since keeping the age of the student as an attribute requires frequent changes (each year) propose a
solution and implement it.

update student set age=age+1;

----------------------------------------------------------------------------------------------------------------------------

Exercise 3(Assignment on DDL and DML commands)

A supermarket manager likes to keep records about all the items in his store these records should hold the following information about each item :
(the item_id, the item name, the item price, expiration date of the item, quantity in hand)

a) Create the appropriate table.

create table item( id int primary key, name varchar(50) not null, price float not null, exp date, quantity int not null );

insert into item values(1,"sauce",15.50,10/11/2016,10);//date wrong
insert into item values(2,"burger",25.50,10/11/2016,5);//date wrong
insert into item values(3,"chicken",80,10/11/2016,1);//date wrong
insert into item values(4,"mutton",80,10/11/2016,1);//date wrong
insert into item values(6,"mutton",80,'2015-11-10',1);

b) Write SQL queries to answer the following problems:

List the names and prices for all items that have a quantity in hand >20.
select name,price from item where quantity>5;
List the names of expired items.
select * from item where exp<curdate();
Name the second most expensive item.
select * from item order by price desc limit 1,1;
Alter the table structure to make sure that no negative value can be assigned to the price field
alter table item add check (price>=0);

Lab Basics

Start the mysql daemon #service mysqld start

If there is an error , try one of these:-

remove /var/lib/mysql/mysql.conf
rename /var/lib/mysql/mysql.conf as /var/lib/mysql/mysql.conf.bak
run #killall mysql

To start the mysql prompt, type #mysql

Steps:-

Create a Database
Use Database
Create table
Alter/Drop table
Insert Data
Update/Delete Row

The commands would be dealt with in Lab1 & Lab2