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