Name : Sunil Jadhav
Roll No. : TECOA145
Assignment No. :1
-------------------------------------------------------------------------------------------------------------------
student@ubuntu:~$ mysql -h 10.12.0.84 -u root -pEnter
password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 64
Server version: 5.5.47-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All
rights reserved.
Oracle is a registered trademark of Oracle Corporation
and/or its
affiliates. Other names may be trademarks of their
respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the
current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| E_Shoppi |
| Electronics_Shop |
| Mggie |
| PCP |
| RAjjo |
| Raj |
| TEA102 |
| TEB232 |
| TECOA1011 |
| TECOA105 |
| TECOA110 |
| TECOA121 |
| TECOA131 |
| TECOA134 |
| TECOA142 |
| TEOA131 |
| abhiserver |
| aniket |
| bramha |
| emp |
| employee |
| m |
| madhura1 |
| mysql |
| performance_schema |
| purvi |
| sam |
| sankhe |
| secoa142 |
| shweta |
| teco177 |
| tecoa117 |
| tecoa126 |
| tecoa128 |
| tecoa133 |
| tecoa135 |
| tecoa136 |
| tecoa139 |
| tecoa144 |
| tecoa178 |
| tecoc356 |
| tecoc3xx |
+--------------------+
43 rows in set (0.00 sec)
mysql> create user TE162 @'%';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to TE162;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
student@ubuntu:~$ mysql -h 10.12.0.84 -u TE162;
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 67
Server version: 5.5.47-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All
rights reserved.
Oracle is a registered trademark of Oracle Corporation
and/or its
affiliates. Other names may be trademarks of their
respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the
current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| E_Shoppi |
| Electronics_Shop |
| Mggie |
| PCP |
| RAjjo |
| Raj |
| TEA102 |
| TEB232 |
| TECOA1011 |
| TECOA105 |
| TECOA110 |
| TECOA121 |
| TECOA131 |
| TECOA134 |
| TECOA142 |
| TEOA131 |
| abhiserver |
| aniket |
| bramha |
| emp |
| employee |
| m |
| madhura1 |
| mysql |
| performance_schema |
| purvi |
| sam |
| sankhe |
| secoa142 |
| shweta |
| teco177 |
| tecoa117 |
| tecoa126 |
| tecoa128 |
| tecoa133 |
| tecoa135 |
| tecoa136 |
| tecoa139 |
| tecoa144 |
| tecoa178 |
| tecoc356 |
| tecoc3xx |
+--------------------+
43 rows in set (0.00 sec)
mysql> create database TE162;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| E_Shoppi |
| Electronics_Shop |
| Mggie |
| PCP |
| RAjjo |
| Raj |
| TE162 |
| TEA102 |
| TEA159 |
| TEA163 |
| TEB232 |
| TECOA1011 |
| TECOA105 |
| TECOA110 |
| TECOA121 |
| TECOA131 |
| TECOA134 |
| TECOA142 |
| TECOA151 |
| TECOA155 |
| TEOA131 |
| abhiserver |
| aniket |
| bramha |
| emp |
| employee |
| m |
| madhura1 |
| mysql |
| performance_schema |
| purvi |
| sam |
| sankhe |
| secoa142 |
| shweta |
| teco177 |
| tecoa117 |
| tecoa126 |
| tecoa128 |
| tecoa133 |
| tecoa135 |
| tecoa136 |
| tecoa139 |
| tecoa144 |
| tecoa153 |
| tecoa178 |
| tecoc356 |
| tecoc3xx |
+--------------------+
49 rows in set (0.00 sec)
mysql> use TE162;
Database changed
mysql> create table employee (emp_id int primary
key,emp_name varchar (20),salary int, dept_name varchar(20),age int);
Query OK, 0 rows affected (0.06 sec)
mysql> desc employee;
+-----------+-------------+------+-----+---------+-------+
| Field |
Type | Null | Key | Default |
Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_id |
int(11) | NO | PRI | NULL |
|
| emp_name |
varchar(20) | YES | | NULL
| |
| salary |
int(11) | YES | |
NULL | |
| dept_name | varchar(20) | YES | |
NULL | |
| age |
int(11) | YES | |
NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table employee add phone_no int;
Query OK, 0 rows affected (0.13 sec)
Records: 0
Duplicates: 0 Warnings: 0
mysql> desc employee;
+-----------+-------------+------+-----+---------+-------+
| Field |
Type | Null | Key | Default |
Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_id |
int(11) | NO | PRI | NULL |
|
| emp_name |
varchar(20) | YES | | NULL
| |
| salary |
int(11) | YES | |
NULL | |
| dept_name | varchar(20) | YES | |
NULL | |
| age |
int(11) | YES | |
NULL | |
| phone_no |
int(11) | YES | |
NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table employee drop phone_no;
Query OK, 0 rows affected (0.13 sec)
Records: 0
Duplicates: 0 Warnings: 0
mysql> desc employee;
+-----------+-------------+------+-----+---------+-------+
| Field |
Type | Null | Key | Default |
Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_id |
int(11) | NO | PRI | NULL |
|
| emp_name |
varchar(20) | YES | | NULL
| |
| salary |
int(11) | YES | |
NULL | |
| dept_name | varchar(20) | YES | |
NULL | |
| age |
int(11) | YES | |
NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table employee change emp_name employee_name
varchar(20);
Query OK, 0 rows affected (0.13 sec)
Records: 0
Duplicates: 0 Warnings: 0
mysql> desc employee;
+---------------+-------------+------+-----+---------+-------+
| Field |
Type | Null | Key | Default |
Extra |
+---------------+-------------+------+-----+---------+-------+
| emp_id |
int(11) | NO | PRI | NULL |
|
| employee_name | varchar(20) | YES | |
NULL | |
| salary |
int(11) | YES | |
NULL | |
| dept_name |
varchar(20) | YES | | NULL
| |
| age |
int(11) | YES | |
NULL | |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table employee modify employee_name
varchar(50);
Query OK, 0 rows affected (0.13 sec)
Records: 0
Duplicates: 0 Warnings: 0
mysql> desc employee;
+---------------+-------------+------+-----+---------+-------+
| Field |
Type | Null | Key | Default |
Extra |
+---------------+-------------+------+-----+---------+-------+
| emp_id |
int(11) | NO | PRI | NULL |
|
| employee_name | varchar(50) | YES | |
NULL | |
| salary |
int(11) | YES | |
NULL | |
| dept_name |
varchar(20) | YES | | NULL
| |
| age |
int(11) | YES | |
NULL | |
+---------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> insert into employee
values(101,'Aarti',40000,'Comp',22);
Query OK, 1 row affected (0.03 sec)
mysql>
mysql> insert into employee
values(101,'Aarti',40,000,Comp,22);
ERROR 1054 (42S22): Unknown column 'Comp' in 'field list'
mysql> insert into employee
values(101,'Aarti',40000,'Comp',22);
Query OK, 1 row affected (0.03 sec)
mysql> insert into employee
values(101,'Shital',50000,'Comp',22);
ERROR 1062 (23000): Duplicate entry '101' for key 'PRIMARY'
mysql> insert into employee
values(102,'Shital',50000,'Comp',22);
Query OK, 1 row affected (0.03 sec)
mysql> insert into employee
values(103,'Neha',45000,'Comp',23);
Query OK, 1 row affected (0.03 sec)
mysql> insert into employee
values(104,'Mohini',60000,'Comp',22);
Query OK, 1 row affected (0.04 sec)
mysql> insert into employee
values(105,'Manali',40000,'IT',23);
Query OK, 1 row affected (0.03 sec)
mysql> insert into employee
values(106,'Nandini',35000,'IT',22);
Query OK, 1 row affected (0.03 sec)
mysql> insert into employee
values(107,'Uday',40000,'Mech',23);
Query OK, 1 row affected (0.03 sec)
mysql> insert into employee
values(108,'Pushkar',40000,'Civil',22);
Query OK, 1 row affected (0.03 sec)
mysql> insert into employee
values(109,'Keval',30000,'Civil',23);
Query OK, 1 row affected (0.03 sec)
mysql> insert into employee
values(110,'Sunil',40000,'Comp',22);
Query OK, 1 row affected (0.03 sec)
mysql> select * from employee;
+--------+---------------+--------+-----------+------+
| emp_id | employee_name | salary | dept_name | age |
+--------+---------------+--------+-----------+------+
| 101 | Aarti |
40000 | Comp | 22 |
| 102 | Shital |
50000 | Comp | 22 |
| 103 | Neha |
45000 | Comp | 23 |
| 104 | Mohini |
60000 | Comp | 22 |
| 105 | Manali |
40000 | IT | 23 |
| 106 |
Nandini | 35000 | IT |
22 |
| 107 | Uday |
40000 | Mech | 23 |
| 108 |
Pushkar | 40000 | Civil |
22 |
| 109 | Keval |
30000 | Civil | 23 |
| 110 | Sunil |
40000 | Comp | 22 |
+--------+---------------+--------+-----------+------+
10 rows in set (0.00 sec)
mysql> select emp_id,employee_name from employee;
+--------+---------------+
| emp_id | employee_name |
+--------+---------------+
| 101 | Aarti |
| 102 | Shital |
| 103 | Neha |
| 104 | Mohini |
| 105 | Manali |
| 106 |
Nandini |
| 107 | Uday |
| 108 |
Pushkar |
| 109 | Keval |
| 110 | Sunil |
+--------+---------------+
10 rows in set (0.00 sec)
mysql> select * from employee where emp_id=105;
+--------+---------------+--------+-----------+------+
| emp_id | employee_name | salary | dept_name | age |
+--------+---------------+--------+-----------+------+
| 105 | Manali |
40000 | IT | 23 |
+--------+---------------+--------+-----------+------+
1 row in set (0.00 sec)
mysql> select * from employee where salary>45000;
+--------+---------------+--------+-----------+------+
| emp_id | employee_name | salary | dept_name | age |
+--------+---------------+--------+-----------+------+
| 102 | Shital |
50000 | Comp | 22 |
| 104 | Mohini |
60000 | Comp | 22 |
+--------+---------------+--------+-----------+------+
2 rows in set (0.00 sec)
mysql> select * from employee where salary<40000;
+--------+---------------+--------+-----------+------+
| emp_id | employee_name | salary | dept_name | age |
+--------+---------------+--------+-----------+------+
| 106 |
Nandini | 35000 | IT |
22 |
| 109 | Keval |
30000 | Civil | 23 |
+--------+---------------+--------+-----------+------+
2 rows in set (0.01 sec)
mysql> update employee set age=25 where emp_id=102;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1
Changed: 1 Warnings: 0
mysql> select * from employee;
+--------+---------------+--------+-----------+------+
| emp_id | employee_name | salary | dept_name | age |
+--------+---------------+--------+-----------+------+
| 101 | Aarti |
40000 | Comp | 22 |
| 102 | Shital |
50000 | Comp | 25 |
| 103 | Neha |
45000 | Comp | 23 |
| 104 | Mohini |
60000 | Comp | 22 |
| 105 | Manali |
40000 | IT | 23 |
| 106 |
Nandini | 35000 | IT |
22 |
| 107 | Uday |
40000 | Mech | 23
|
| 108 |
Pushkar | 40000 | Civil |
22 |
| 109 | Keval |
30000 | Civil | 23 |
| 110 | Sunil |
40000 | Comp | 22 |
+--------+---------------+--------+-----------+------+
10 rows in set (0.00 sec)
mysql> update employee set dept_name='Comp_Sci' where
dept_name='Comp';
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5
Changed: 5 Warnings: 0
mysql> select * from employee;
+--------+---------------+--------+-----------+------+
| emp_id | employee_name | salary | dept_name | age |
+--------+---------------+--------+-----------+------+
| 101 | Aarti |
40000 | Comp_Sci | 22 |
| 102 | Shital |
50000 | Comp_Sci | 25 |
| 103 | Neha |
45000 | Comp_Sci | 23 |
| 104 | Mohini |
60000 | Comp_Sci | 22 |
| 105 | Manali |
40000 | IT | 23 |
| 106 |
Nandini | 35000 | IT |
22 |
| 107 | Uday |
40000 | Mech | 23 |
| 108 |
Pushkar | 40000 | Civil |
22 |
| 109 | Keval |
30000 | Civil | 23 |
| 110 | Sunil |
40000 | Comp_Sci | 22 |
+--------+---------------+--------+-----------+------+
10 rows in set (0.00 sec)
mysql> delete from employee where emp_id=110;
Query OK, 1 row affected (0.02 sec)
mysql> select * from employee;
+--------+---------------+--------+-----------+------+
| emp_id | employee_name | salary | dept_name | age |
+--------+---------------+--------+-----------+------+
| 101 | Aarti |
40000 | Comp_Sci | 22 |
| 102 | Shital |
50000 | Comp_Sci | 25 |
| 103 | Neha |
45000 | Comp_Sci | 23 |
| 104 | Mohini |
60000 | Comp_Sci | 22 |
| 105 | Manali |
40000 | IT | 23 |
| 106 |
Nandini | 35000 | IT |
22 |
| 107 | Uday |
40000 | Mech | 23 |
| 108 |
Pushkar | 40000 | Civil |
22 |
| 109 | Keval |
30000 | Civil | 23 |
+--------+---------------+--------+-----------+------+
9 rows in set (0.00 sec)
mysql> create index simple on employee(emp_id);
Query OK, 0 rows affected (0.14 sec)
Records: 0
Duplicates: 0 Warnings: 0
mysql> show index from employee;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table |
Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employee |
0 | PRIMARY | 1 | emp_id | A | 2 | NULL | NULL |
| BTREE | | |
| employee |
1 | simple | 1 | emp_id | A
| 9 | NULL | NULL |
| BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> drop index simple on employee;
Query OK, 0 rows affected (0.10 sec)
Records: 0
Duplicates: 0 Warnings: 0
mysql> create view view_emp as select
emp_id,employee_name,age from employee;
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
+-----------------+
| Tables_in_TE162 |
+-----------------+
| employee |
| view_emp |
+-----------------+
2 rows in set (0.00 sec)
------------------------------------------------------------------------------------------------------
Name : Sunil Jadhav
Roll No. : TECOA145
Assignment No.2 :DBMS using connections(Client-Data
sever, two tier) M(ODBC/JDBC), SQL prompt to create data base tables insert,
update data values, delete table, use table,select queries with/without where
clause.
----------------------------------------------------------------------------------------------------------------------
Q.A Account(Acc_no, branch_name,balance)
branch(branch_name,branch_city,assets)
customer(cust_name,cust_street,cust_city)
Depositor(cust_name,acc_no)
Loan(loan_no,branch_name,amount)
Borrower(cust_name,loan_no)
student@ubuntu:~$
mysql -h 10.12.0.61 -u TECOA
Welcome
to the MySQL monitor. Commands end with
; or \g.
Your
MySQL connection id is 95
Server
version: 5.5.47-0ubuntu0.14.04.1 (Ubuntu)
Copyright
(c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle
is a registered trademark of Oracle Corporation and/or its
affiliates.
Other names may be trademarks of their respective
owners.
Type
'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
use TECOA;
Database
changed
mysql>
create table Branch(br_name varchar(20) primary key, b_city varchar(20), assets
varchar(20));
Query
OK, 0 rows affected (0.08 sec)
mysql>
create table Account(acc_no int primary
key,br_name varchar(20),foreign key(br_name) references Branch(br_name),
balance int);
Query
OK, 0 rows affected (0.08 sec)
mysql>
create table Customer(cust_name varchar(40) primary key, street varchar(20),
c_city varchar(20));
Query
OK, 0 rows affected (0.06 sec)
mysql>create
table Depositor(cust_name varchar(40), acc_no int, foreign key(cust_name)
references Customer(cust_name), foreign key(acc_no) references Account(acc_no));
Query
OK, 0 rows affected (0.09 sec)
mysql>
mysql> create table Loan(loan_no int
primary key,br_name varchar(20),foreign key(br_name) references
Branch(br_name), amount int);
Query
OK, 0 rows affected (0.08 sec)
mysql>
mysql> create table Borrower(cust_name varchar(40),loan_no int,foreign
key(cust_name) references Customer(cust_name), foreign key(loan_no) references
Loan(loan_no));
Query
OK, 0 rows affected (0.08 sec)
mysql>
insert into Customer values('Sunil','Pimpri','Pune');
Query
OK, 1 row affected (0.05 sec)
mysql>
insert into Customer values('Kalpesh','Yamunanagar','Pune');
Query
OK, 1 row affected (0.05 sec)
mysql>
insert into Customer values('Ashwin','Dattawadi','Pune');
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Customer values('Vaibhav','Akurdi','Pune');
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Customer values('Jay','Shagun Chowk','Nashik');
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Customer values('Ankur','Pradhikaran','Pune');
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Customer values('Kaushal','Pradhikaran','Pune');
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Customer values('Uday','Bhel Chowk','Pune');
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Customer values('Vipul Gujar','Sambhaji Chowk','Satara');
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Customer values('Harsh Shahade','Big India','Pune');
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Branch values('Uno','Akurdi',125000);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Branch values('Dos','Nigdi',634000);
Query
OK, 1 row affected (0.06 sec)
mysql>
insert into Branch values('Tres','Pimpri',278000);
Query
OK, 1 row affected (0.05 sec)
mysql>
insert into Branch values('Cuatro','Nashik',793000);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Branch values('Cinco','Satara',793000);
Query
OK, 1 row affected (0.04 sec)
mysql>
mysql> insert into Account values(4321,'Uno',12000);
Query
OK, 1 row affected (0.03 sec)
mysql>
insert into Account values(4322,'Uno',22000);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Account values(4323,'Tres',45000);
Query
OK, 1 row affected (0.03 sec)
mysql>
insert into Account values(4324,'Tres',54000);
Query
OK, 1 row affected (0.03 sec)
mysql>
insert into Account values(4324,'Dos',41000);
ERROR
1062 (23000): Duplicate entry '4324' for key 'PRIMARY'
mysql>
insert into Account values(4325,'Dos',41000);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Account values(4326,'Dos',49000);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Account values(4327,'Cuatro',34000);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Account values(4328,'Cuatro',78000);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Depositor values('Jay',4321);
Query
OK, 1 row affected (0.03 sec)
mysql>
insert into Depositor values('Sunil',4322);
Query
OK, 1 row affected (0.05 sec)
mysql>
insert into Depositor values('Kalpesh',4323);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Depositor values('Ashwin',4324);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Depositor values('Harsh Shahade',4325);
Query
OK, 1 row affected (0.03 sec)
mysql>
insert into Depositor values('Ankur',4326);
Query
OK, 1 row affected (0.03 sec)
mysql>
insert into Depositor values('Kaushal',4327);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Depositor values('Vaibhav',4328);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Loan values(4561,'Uno',125000);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Loan values(4562,'Uno',322000);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Loan values(4563,'Cinco',223000);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Loan values(4564,'Cinco',723000);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Loan values(4565,'Cuatro',523000);
Query
OK, 1 row affected (0.03 sec)
mysql>
insert into Loan values(4566,'Cuatro',576000);
Query
OK, 1 row affected (0.03 sec)
mysql>
insert into Borrower values('Kalpesh',4561);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Borrower values('Ashwin',4562);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Borrower values('Vipul Gujar',4563);
Query
OK, 1 row affected (0.05 sec)
mysql>
insert into Borrower values('Uday',4564);
Query OK,
1 row affected (0.04 sec)
mysql>
insert into Borrower values('Ankur',4565);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Borrower values('Harsh Shahade',4566);
Query
OK, 1 row affected (0.03 sec)
Query 1
mysql>
select distinct(br_name) from Loan;
+---------+
|
br_name |
+---------+
|
Cinco |
|
Cuatro |
|
Dos |
|
Tres |
|
Uno |
+---------+
5 rows
in set (0.00 sec)
Query 2
mysql>
select Loan_No from Loan where Amount>40000 and Br_Name='First Branch';
+---------+
|
Loan_No |
+---------+
| 111 |
| 114 |
+---------+
2 rows
in set (0.00 sec)
Query 3
mysql>
select Cus_Name,Loan.Loan_No,Amount from Borrower,Loan where
Borrower.Loan_no=Loan.Loan_No;
+----------+---------+--------+
|
Cus_Name | Loan_No | Amount |
+----------+---------+--------+
| Vaibhav |
111 | 68000 |
|
Sam | 112 |
60000 |
|
Rajnish | 113 |
20000 |
| Ashwin |
114 | 98000 |
| Sunil
| 115 | 48000 |
+----------+---------+--------+
5 rows
in set (0.00 sec)
Query 4
mysql>
select cust_name from Borrower,Loan where Borrower.loan_no=Loan.loan_no and
br_name='Uno' order by cust_name;
+----------------+
|
cust_name |
+----------------+
| Ashwin
|
| Kalpesh |
+----------------+
2 rows
in set (0.02 sec)
Query 5
mysql>
select cust_name from Depositor union select cust_name from Borrower;
+-------------------+
|
cust_name |
+-------------------+
| Ankur |
| Kaushal |
| Harsh
Shahade |
| Jay |
| Sunil
|
| Ashwin |
| Kalpesh |
| Vaibhav |
| Uday |
| Vipul
Gujar |
+-------------------+
10 rows
in set (0.00 sec)
Query 6
mysql>
select Depositor.cust_name from Depositor,Borrower where
Borrower.cust_name=Depositor.cust_name;
+----------------+
|
cust_name |
+----------------+
| Ankur |
| Harsh
Shahade |
| Ashwin
|
| Kalpesh |
+----------------+
4 rows
in set (0.00 sec)
Query 7
mysql>
select cust_name from Depositor where cust_name not in(select cust_name from
Borrower);
+-------------------+
|
cust_name |
+-------------------+
| Kaushal |
| Jay |
| Sunil
|
| Vaibhav |
+-------------------+
4 rows
in set (0.00 sec)
Query 8
mysql>
select avg(balance) from Account where br_name='Uno';
+--------------+
|
avg(balance) |
+--------------+
| 17000.0000 |
+--------------+
1 row in
set (0.01 sec)
Query 9
mysql>
select br_name,avg(balance) from Account group by br_name;
+---------+--------------+
|
br_name | avg(balance) |
+---------+--------------+
|
Cuatro | 56000.0000 |
|
Dos | 45000.0000 |
|
Tres | 49500.0000 |
|
Uno | 17000.0000 |
+---------+--------------+
4 rows
in set (0.00 sec)
Query 10
mysql>
select br_name, count(*) from Account,Depositor where
Account.acc_no=Depositor.acc_no group by br_name;
+---------+----------+
|
br_name | count(*) |
+---------+----------+
|
Cuatro | 2 |
|
Dos | 2 |
|
Tres | 2 |
|
Uno | 2 |
+---------+----------+
4 rows
in set (0.00 sec)
Query 11
mysql>
select br_name from Account group by br_name having avg(balance)>12000;
+---------+
|
br_name |
+---------+
|
Cuatro |
|
Dos |
|
Tres |
|
Uno |
+---------+
4 rows
in set (0.00 sec)
Query 12
mysql>
select count(*) from Customer;
+----------+
|
count(*) |
+----------+
| 10 |
+----------+
1 row in
set (0.01 sec)
Query 13
mysql>
select sum(amount) from Loan;
+-------------+
|
sum(amount) |
+-------------+
| 2492000 |
+-------------+
1 row in
set (0.00 sec)
Query 14
mysql>
delete from Loan where amount between 130000 and 250000;
ERROR
1451 (23000): Cannot delete or update a parent row: a foreign key constraint
fails (`TECOA`.`Borrower`, CONSTRAINT `Borrower_ibfk_2` FOREIGN KEY (`loan_no`)
REFERENCES `Loan` (`loan_no`))
This is
because we are trying to delete a tuple containing a primary key which is
foreign key in another table.
Query 15
mysql>
select * from Customer where c_city like '%a%';
+--------------+----------------+--------+
|
cust_name | street | c_city |
+--------------+----------------+--------+
| Jay |
Shagun Chowk | Nashik |
| Vipul
Gujar | Sambhaji Chowk | Satara |
+--------------+----------------+--------+
2 rows
in set (0.00 sec)
Q.C_1
emp_mstr(e_mpno,f_name,l_name,m_name,dept,desg,branch_no)
branch_mstr(name,b_no)
mysql>
create table branch_mstr(name varchar(15),br_no int primary key);
Query
OK, 0 rows affected (0.07 sec)
mysql>
create table emp_mstr(e_mpno int, f_name varchar(10), l_name varchar(10),
m_name varchar(10), dept varchar(10),desg varchar(10), br_no int, foreign
key(br_no) references branch_mstr(br_no));
Query
OK, 0 rows affected (0.07 sec)
mysql>
insert into branch_mstr values('branch1',1);
Query
OK, 1 row affected (0.03 sec)
mysql>
insert into branch_mstr values('branch2',2);
Query
OK, 1 row affected (0.12 sec)
mysql>
insert into branch_mstr values('branch3',3);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into branch_mstr values('branch4',4);
Query
OK, 1 row affected (0.03 sec)
mysql>
insert into branch_mstr values('branch5',5);
Query
OK, 1 row affected (0.05 sec)
mysql>
insert into branch_mstr values('branch6',6);
Query
OK, 1 row affected (0.02 sec)
mysql>
insert into emp_mstr values();
Query
OK, 1 row affected (0.03 sec)
mysql>
select * from emp_mstr;
+--------+--------+--------+--------+------+------+-------+
| e_mpno
| f_name | l_name | m_name | dept | desg | br_no |
+--------+--------+--------+--------+------+------+-------+
| NULL | NULL
| NULL | NULL | NULL | NULL | NULL |
+--------+--------+--------+--------+------+------+-------+
1 row in
set (0.00 sec)
mysql>
delete from emp_mstr;
Query
OK, 1 row affected (0.03 sec)
mysql>
select * from emp_mstr;
Empty
set (0.00 sec)
mysql>
desc emp_mstr;
+--------+-------------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| e_mpno
| int(11) | YES | |
NULL | |
| f_name
| varchar(10) | YES | | NULL
| |
| l_name
| varchar(10) | YES | | NULL
| |
| m_name
| varchar(10) | YES | | NULL
| |
|
dept | varchar(10) | YES | |
NULL | |
|
desg | varchar(10) | YES | |
NULL | |
|
br_no | int(11) | YES
| MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
7 rows
in set (0.00 sec)
mysql>
insert into emp_mstr values(1,'Ritvik','Bhavan','Santosh','COMPUTER','A',1);
Query
OK, 1 row affected (0.05 sec)
mysql>
insert into emp_mstr values(1,'Jay','Bhale','Sunil','COMPUTER','A',2);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into emp_mstr values(1,'Vaibhav','Bhalerao','Sanjay','MECHANICAL','B',3);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into emp_mstr values(1,'Ashwin','Bhange','Balbhim','ENTC','B',4);
Query
OK, 1 row affected (0.02 sec)
mysql>
insert into emp_mstr values(1,'Sunil','Dandekar','Deepak','CIVIL','B',5);
Query
OK, 1 row affected (0.05 sec)
mysql>
select f_name,dept from emp_mstr inner join branch_mstr on dept=;
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 '' at
line 1
mysql>
select * from branch_mstr;
+---------+-------+
|
name | br_no |
+---------+-------+
|
branch1 | 1 |
|
branch2 | 2 |
|
branch3 | 3 |
|
branch4 | 4 |
|
branch5 | 5 |
|
branch6 | 6 |
+---------+-------+
6 rows
in set (0.00 sec)
mysql>
select f_name,name from emp_mstr inner join branch_mstr on
emp_mstr.br_no=branch_mstr.br_no;
+----------+---------+
|
f_name | name |
+----------+---------+
|
Ritvik | branch1 |
| Jay | branch2 |
| Vaibhav | branch3 |
| Ashwin | branch4 |
| Sunil
| branch5 |
+----------+---------+
5 rows
in set (0.00 sec)
mysql>
Q.C_2
emp_mstr(emp_no,f_name,l_name,m_name,dept)
cntc_dets(cod_eno,cntc_type,cntc_data)
mysql>
create table emp_mstr(emp_no int primary key,f_name varchar(20),l_name
varchar(20),m_name varchar(20),dept varchar(20));
Query
OK, 0 rows affected (0.22 sec)
mysql>
create table cntc_dets(cod_eno int,cntc_type varchar(20),cntc_data
varchar(20),foreign key (cod_eno) references emp_mstr(emp_no));
Query
OK, 0 rows affected (0.23 sec)
mysql>
insert into emp_mstr values (12,'a','b','c','coding');
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into emp_mstr values (98,'s','t','u','gamming');
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into emp_mstr values
(56,'l','m','n','manufatering');
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into cntc_dets values (56,'home','9856235815');
Query
OK, 1 row affected (0.05 sec)
mysql>
insert into cntc_dets values (12,'personal','7845961254');
Query
OK, 1 row affected (0.05 sec)
mysql>
insert into cntc_dets values (98,'office','9562314875');
Query
OK, 1 row affected (0.05 sec)
mysql>
select f_name,cntc_type,cntc_data from emp_mstr left outer join cntc_dets on
emp_mstr.emp_no=cntc_dets.cod_eno;
+--------+-----------+------------+
| f_name
| cntc_type | cntc_data |
+--------+-----------+------------+
| l | home | 9856235815 |
| a | personal | 7845961254 |
| s | office | 9562314875 |
+--------+-----------+------------+
3 rows
in set (0.00 sec)
Q.D Employee(empno,ename,deptno,salary)
department(deptno,dname)
mysql>
create table department(deptno int primary key,dname varchar(20));
Query
OK, 0 rows affected (0.21 sec)
mysql>
create table Employee(emp_no int,ename varchar(20),deptno int,salary int,
foreign key (deptno) references department (deptno));
Query
OK, 0 rows affected (0.31 sec)
mysql>
insert into department values(2478,'MECH');
Query
OK, 1 row affected (0.03 sec)
mysql>
insert into department values(3451,'E&TC');
Query
OK, 1 row affected (0.03 sec)
mysql>
insert into department values(1452,'COMP');
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into department values(6521,'CIVIL');
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Employee values (15,'ADITYA',1452,45000);
Query
OK, 1 row affected (0.04 sec)
mysql>
insert into Employee values (81,'RIYA',3451,50000);
Query
OK, 1 row affected (0.05 sec)
mysql>
insert into Employee values (23,'ROY',2478,30000);
Query
OK, 1 row affected (0.02 sec)
mysql>
insert into Employee values (12,'AMIT',1452,64000);
Query
OK, 1 row affected (0.02 sec)
mysql>
insert into Employee values (19,'RUTUJA',6521,61000);
Query
OK, 1 row affected (0.02 sec)
mysql>
insert into Employee values (42,'RAHUL',3451,51000);
Query
OK, 1 row affected (0.03 sec)
mysql>
insert into Employee values (54,'KOMAL',1452,31000);
Query
OK, 1 row affected (0.01 sec)
mysql>
insert into Employee values (61,'SACHIN',2478,39000);
Query
OK, 1 row affected (0.04 sec)
mysql>
select * from Employee;
+--------+--------+--------+--------+
| emp_no
| ename | deptno | salary |
+--------+--------+--------+--------+
| 15 | ADITYA | 1452 |
45000 |
| 81 | RIYA
| 3451 | 50000 |
| 23 | ROY
| 2478 | 30000 |
| 12 | AMIT
| 1452 | 64000 |
| 19 | RUTUJA | 6521 |
61000 |
| 42 | RAHUL
| 3451 | 51000 |
| 54 | KOMAL
| 1452 | 31000 |
| 61 | SACHIN | 2478 |
39000 |
+--------+--------+--------+--------+
8 rows
in set (0.00 sec)
mysql>
select * from department;
+--------+-------+
| deptno
| dname |
+--------+-------+
| 1452 | COMP
|
| 2478 | MECH
|
| 3451 | E&TC |
| 6521 | CIVIL |
+--------+-------+
4 rows
in set (0.00 sec)
mysql>
select ename from Employee,department where Employee.deptno=department.deptno
and dname='COMP';
+--------+
|
ename |
+--------+
| ADITYA
|
|
AMIT |
|
KOMAL |
+--------+
3 rows
in set (0.00 sec)
mysql>
select dname,avg(salary) from Employee,department where
Employee.deptno=department.deptno group by department.dname;
+-------+-------------+
| dname
| avg(salary) |
+-------+-------------+
| CIVIL
| 61000.0000 |
|
COMP |
46666.6667 |
|
E&TC | 50500.0000 |
|
MECH |
34500.0000 |
+-------+-------------+
4 rows
in set (0.00 sec)
mysql>
select ename,dname from Employee,department where
Employee.deptno=department.deptno and employee.ename='AMIT';
+-------+-------+
| ename
| dname |
+-------+-------+
| AMIT | COMP
|
+-------+-------+
1 row in
set (0.00 sec)
----------------------------------------------------------------------------------------------------------
Name :Sunil Jadhav
Roll No. : TECOA145
Assignment No.3 :Implement database with suitable
example using MongoDB and implement all basic operations and administration
commands using two tier architecture.
--------------------------------------------------------------------------------------------------------------------------------------------
student@ubuntu:~$
mongo 10.12.0.139:1027
MongoDB
shell version: 2.6.12
connecting
to: 10.12.0.139:1027/test
>
db.createCollection("Teacherinfo")
{
"ok" : 1 }
>
db.Teacherinfo.insert({Teacher_id:"001",Teacher_name:"Sunil",Dept_name:"IT",sal:100000,status:"A"})
WriteResult({
"nInserted" : 1 })
>
db.Teacherinfo.insert({Teacher_id:"002",Teacher_name:"Kalpesh",Dept_name:"comp",sal:50000,status:"A"})
WriteResult({
"nInserted" : 1 })
>
db.Teacherinfo.insert({Teacher_id:"003",Teacher_name:"Ashwin",Dept_name:"comp",sal:100000,status:"A"})
WriteResult({
"nInserted" : 1 })
>
db.Teacherinfo.find()
{
"_id" : ObjectId("57972ad972b84dd64c4cd0f0"),
"Teacher_id" : "001", "Teacher_name" : "Sunil",
"Dept_name" : "IT", "sal" : 100000,
"status" : "A" }
{
"_id" : ObjectId("57972af572b84dd64c4cd0f1"),
"Teacher_id" : "002", "Teacher_name" : "Kalpesh",
"Dept_name" : "comp", "sal" : 50000,
"status" : "A" }
{
"_id" : ObjectId("57972b0c72b84dd64c4cd0f2"),
"Teacher_id" : "003", "Teacher_name" : "Ashwin",
"Dept_name" : "comp", "sal" : 100000,
"status" : "A" }
>
db.Teacherinfo.find({sal:50000})
{
"_id" : ObjectId("57972af572b84dd64c4cd0f1"),
"Teacher_id" : "002", "Teacher_name" : "Kalpesh",
"Dept_name" : "comp", "sal" : 50000,
"status" : "A" }
>
>
db.Teacherinfo.find({Teacher_id:"001"})
{
"_id" : ObjectId("57972ad972b84dd64c4cd0f0"),
"Teacher_id" : "001", "Teacher_name" : "Sunil",
"Dept_name" : "IT", "sal" : 100000,
"status" : "A" }
>
>
db.Teacherinfo.find().pretty();
{
"_id" :
ObjectId("57972ad972b84dd64c4cd0f0"),
"Teacher_id" :
"001",
"Teacher_name" : "Sunil",
"Dept_name" :
"IT",
"sal" : 100000,
"status" : "A"
}
{
"_id" :
ObjectId("57972af572b84dd64c4cd0f1"),
"Teacher_id" :
"002",
"Teacher_name" : "Kalpesh",
"Dept_name" :
"comp",
"sal" : 50000,
"status" : "A"
}
{
"_id" :
ObjectId("57972b0c72b84dd64c4cd0f2"),
"Teacher_id" :
"003",
"Teacher_name" : "Ashwin",
"Dept_name" :
"comp",
"sal" : 100000,
"status" : "A"
}
>
db.Teacherinfo.find({status:{$ne:"A"}})
>
db.Teacherinfo.find({status:{$ne:"B"}})
{
"_id" : ObjectId("57972ad972b84dd64c4cd0f0"),
"Teacher_id" : "001", "Teacher_name" : "Sunil",
"Dept_name" : "IT", "sal" : 100000,
"status" : "A" }
{
"_id" : ObjectId("57972af572b84dd64c4cd0f1"),
"Teacher_id" : "002", "Teacher_name" : "Kalpesh",
"Dept_name" : "comp", "sal" : 50000,
"status" : "A" }
{
"_id" : ObjectId("57972b0c72b84dd64c4cd0f2"),
"Teacher_id" : "003", "Teacher_name" : "Ashwin",
"Dept_name" : "comp", "sal" : 100000,
"status" : "A" }
>
>
db.Teacherinfo.find({status:"A", sal:100000})
{
"_id" : ObjectId("57972ad972b84dd64c4cd0f0"),
"Teacher_id" : "001", "Teacher_name" : "Sunil",
"Dept_name" : "IT", "sal" : 100000,
"status" : "A" }
{
"_id" : ObjectId("57972b0c72b84dd64c4cd0f2"),
"Teacher_id" : "003", "Teacher_name" : "Ashwin",
"Dept_name" : "comp", "sal" : 100000,
"status" : "A" }
>
>
db.Teacherinfo.find({$or:[{status:"A"},{sal:50000}]})
{
"_id" : ObjectId("57972ad972b84dd64c4cd0f0"),
"Teacher_id" : "001", "Teacher_name" : "Sunil",
"Dept_name" : "IT", "sal" : 100000,
"status" : "A" }
{
"_id" : ObjectId("57972af572b84dd64c4cd0f1"),
"Teacher_id" : "002", "Teacher_name" : "Kalpesh",
"Dept_name" : "comp", "sal" : 50000,
"status" : "A" }
{
"_id" : ObjectId("57972b0c72b84dd64c4cd0f2"),
"Teacher_id" : "003", "Teacher_name" : "Ashwin",
"Dept_name" : "comp", "sal" : 100000,
"status" : "A" }
>
>
db.Teacherinfo.find( {sal:{$gt:50000}})
{
"_id" : ObjectId("57972ad972b84dd64c4cd0f0"),
"Teacher_id" : "001", "Teacher_name" : "Sunil",
"Dept_name" : "IT", "sal" : 100000,
"status" : "A" }
{
"_id" : ObjectId("57972b0c72b84dd64c4cd0f2"),
"Teacher_id" : "003", "Teacher_name" : "Ashwin",
"Dept_name" : "comp", "sal" : 100000,
"status" : "A" }
>
>
db.Teacherinfo.find({sal:{$gt:30000}})
{
"_id" : ObjectId("57972ad972b84dd64c4cd0f0"),
"Teacher_id" : "001", "Teacher_name" : "Sunil",
"Dept_name" : "IT", "sal" : 100000,
"status" : "A" }
{
"_id" : ObjectId("57972af572b84dd64c4cd0f1"),
"Teacher_id" : "002", "Teacher_name" : "Kalpesh",
"Dept_name" : "comp", "sal" : 50000,
"status" : "A" }
{
"_id" : ObjectId("57972b0c72b84dd64c4cd0f2"),
"Teacher_id" : "003", "Teacher_name" : "Ashwin",
"Dept_name" : "comp", "sal" : 100000,
"status" : "A" }
>
>
db.Teacherinfo.find({status:"A"}).sort({sal:1})
{
"_id" : ObjectId("57972af572b84dd64c4cd0f1"),
"Teacher_id" : "002", "Teacher_name" : "Kalpesh",
"Dept_name" : "comp", "sal" : 50000,
"status" : "A" }
{
"_id" : ObjectId("57972ad972b84dd64c4cd0f0"),
"Teacher_id" : "001", "Teacher_name" : "Sunil",
"Dept_name" : "IT", "sal" : 100000,
"status" : "A" }
{
"_id" : ObjectId("57972b0c72b84dd64c4cd0f2"),
"Teacher_id" : "003", "Teacher_name" : "Ashwin",
"Dept_name" : "comp", "sal" : 100000,
"status" : "A" }
>
>
db.Teacherinfo.find({status:"A"}).sort({sal:-1})
{
"_id" : ObjectId("57972ad972b84dd64c4cd0f0"),
"Teacher_id" : "001", "Teacher_name" : "Sunil",
"Dept_name" : "IT", "sal" : 100000,
"status" : "A" }
{
"_id" : ObjectId("57972b0c72b84dd64c4cd0f2"),
"Teacher_id" : "003", "Teacher_name" : "Ashwin",
"Dept_name" : "comp", "sal" : 100000,
"status" : "A" }
{
"_id" : ObjectId("57972af572b84dd64c4cd0f1"),
"Teacher_id" : "002", "Teacher_name" : "Kalpesh",
"Dept_name" : "comp", "sal" : 50000,
"status" : "A" }
>
>
db.Teacherinfo.find().count()
3
>
db.Teacherinfo.distinct("Dept_name")
[
"IT", "comp" ]
>
UPDATE
RECORDS
>
db.Teacherinfo.update({sal:{$gt:45000}},{$set:{Dept_name:"E&TC"}},{multi:true})
WriteResult({
"nMatched" : 2, "nUpserted" : 0, "nModified" : 2
})
>
db.Teacherinfo.update({status:"A"},{$inc:{sal:10000}},{multi:true})
WriteResult({
"nMatched" : 3, "nUpserted" : 0, "nModified" : 3
})
ALTER
TABLE
>
db.Teacherinfo.update({},{$set:{join_date:new Date()}},{multi:true})
WriteResult({
"nMatched" : 3, "nUpserted" : 0, "nModified" : 3
})
INDEXING
>
db.Teacherinfo.ensureIndex({"Teacher_id":001})
{
"createdCollectionAutomatically"
: false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
>
AGGREGATION
>
db.Teacherinfo.aggregate([{$group:{_id:"$DeptName", Total_sal :
{$sum: "$sal"}}}])
{
"_id" : null, "Total_sal" : 170000 }
>
DROP
>
db.Teacherinfo.drop();
true
>
-----------------------------------------------------------------------------------------------
Name : Sunil Jadhav
Roll No. : TECOA145
Assignment No.4 :Indexing and querying with MongoDB
using suitable example.
--------------------------------------------------------------------------------------------------------------------------------------------
student@ubuntu:~$
mongo
MongoDB
shell version: 2.6.12
connecting
to: test
1.Create Database PCCOE
>
use PCCOE
switched
to db PCCOE
2. Create following
Collections
Teachers(Tname,dno,dname,experience,salary,date_of_joining
)
Students(Sname,roll_no,class)
>
db.createCollection("Teacher")
{
"ok" : 1 }
>
db.Teacher.insert({tname: "Madhura",Dno: 1,Dname:
"Comp",Experience_yrs: 10,salary: 10000,date_of_joining:
"10-03-14"})
WriteResult({
"nInserted" : 1 })
>
db.Teacher.insert({tname: "Alka",Dno: 1,Dname:
"Comp",Experience_yrs: 10,salary: 10000,date_of_joining: new Date()})
WriteResult({
"nInserted" : 1 })
>
db.Teacher.insert({tname: "Sagar",Dno: 2,Dname:
"IT",Experience_yrs: 15,salary: 20000,date_of_joining:
"20-06-04"})
WriteResult({
"nInserted" : 1 })
>
db.Teacher.insert({tname: "Ganesh",Dno: 3,Dname:
"ENTC",Experience_yrs: 15,salary: 30000,date_of_joining:
"20-06-04"})
WriteResult({
"nInserted" : 1 })
>
db.Teacher.insert({tname: "Atul",Dno: 4,Dname:
"Mech",Experience_yrs: 4,salary: 40000,date_of_joining: new Date()})
WriteResult({
"nInserted" : 1 })
>
db.Teacher.insert({tname: "Sonali",Dno: 4,Dname:
"Mech",Experience_yrs: 4,salary: 50000,date_of_joining: new Date()})
WriteResult({
"nInserted" : 1 })
>
db.Teacher.insert({tname: "Harshdha",Dno: 1,Dname:
"Comp",Experience_yrs: 19,salary: 50000,date_of_joining: new Date()})
WriteResult({
"nInserted" : 1 })
>
db.Teacher.insert({tname: "Mira",Dno: 1,Dname:
"Comp",Experience_yrs: 7,salary: 30000,date_of_joining: new Date()})
WriteResult({
"nInserted" : 1 })
>
db.Teacher.insert({tname: "Harshad",Dno: 2,Dname:
"IT",Experience_yrs: 8,salary: 35000,date_of_joining: new Date()})
WriteResult({
"nInserted" : 1 })
>
db.Teacher.insert({tname: "Raj",Dno: 2,Dname: "IT",Experience_yrs:
9,salary: 45000,date_of_joining: new Date()})
WriteResult({
"nInserted" : 1 })
3. Find the information
about all teachers
>
db.Teacher.find().pretty()
{
"_id" :
ObjectId("57a05f1e68314a7dba4facab"),
"tname" : "Madhura",
"Dno" : 1,
"Dname" : "Comp",
"Experience_yrs" : 10,
"salary" : 10000,
"date_of_joining" :
"10-03-14"
}
{
"_id" :
ObjectId("57a05f4d68314a7dba4facac"),
"tname" : "Alka",
"Dno" : 1,
"Dname" : "Comp",
"Experience_yrs" : 10,
"salary" : 10000,
"date_of_joining" : ISODate("2016-08-02T08:52:29.178Z")
}
{
"_id" :
ObjectId("57a05fb468314a7dba4facad"),
"tname" : "Sagar",
"Dno" : 2,
"Dname" : "IT",
"Experience_yrs" : 15,
"salary" : 20000,
"date_of_joining" :
"20-06-04"
}
{
"_id" :
ObjectId("57a05fd068314a7dba4facae"),
"tname" : "Ganesh",
"Dno" : 3,
"Dname" : "ENTC",
"Experience_yrs" : 15,
"salary" : 30000,
"date_of_joining" :
"20-06-04"
}
{
"_id" :
ObjectId("57a060bc2e8f4ad716cfca91"),
"tname" : "Atul",
"Dno" : 4,
"Dname" : "Mech",
"Experience_yrs" : 4,
"salary" : 40000,
"date_of_joining" :
ISODate("2016-08-02T08:58:36.160Z")
}
{
"_id" :
ObjectId("57a060cf2e8f4ad716cfca92"),
"tname" : "Sonali",
"Dno" : 4,
"Dname" : "Mech",
"Experience_yrs" : 4,
"salary" : 50000,
"date_of_joining" : ISODate("2016-08-02T08:58:55.463Z")
}
{
"_id" :
ObjectId("57a060e72e8f4ad716cfca93"),
"tname" : "Harshdha",
"Dno" : 1,
"Dname" : "Comp",
"Experience_yrs" : 19,
"salary" : 50000,
"date_of_joining" :
ISODate("2016-08-02T08:59:19.535Z")
}
{
"_id" : ObjectId("57a061012e8f4ad716cfca94"),
"tname" : "Mira",
"Dno" : 1,
"Dname" : "Comp",
"Experience_yrs" : 7,
"salary" : 30000,
"date_of_joining" :
ISODate("2016-08-02T08:59:45.607Z")
}
{
"_id" :
ObjectId("57a0611d2e8f4ad716cfca95"),
"tname" : "Harshad",
"Dno" : 2,
"Dname" : "IT",
"Experience_yrs" : 8,
"salary" : 35000,
"date_of_joining" :
ISODate("2016-08-02T09:00:13.599Z")
}
{
"_id" :
ObjectId("57a0612f2e8f4ad716cfca96"),
"tname" : "Raj",
"Dno" : 2,
"Dname" : "IT",
"Experience_yrs" : 9,
"salary" : 45000,
"date_of_joining" :
ISODate("2016-08-02T09:00:31.679Z")
}
>
db.Teacher.find()
{
"_id" : ObjectId("57a05f1e68314a7dba4facab"),
"tname" : "Madhura", "Dno" : 1, "Dname"
: "Comp", "Experience_yrs" : 10, "salary" :
10000, "date_of_joining" : "10-03-14" }
{
"_id" : ObjectId("57a05f4d68314a7dba4facac"),
"tname" : "Alka", "Dno" : 1, "Dname" :
"Comp", "Experience_yrs" : 10, "salary" : 10000,
"date_of_joining" : ISODate("2016-08-02T08:52:29.178Z") }
{
"_id" : ObjectId("57a05fb468314a7dba4facad"),
"tname" : "Sagar", "Dno" : 2, "Dname" :
"IT", "Experience_yrs" : 15, "salary" : 20000,
"date_of_joining" : "20-06-04" }
{
"_id" : ObjectId("57a05fd068314a7dba4facae"),
"tname" : "Ganesh", "Dno" : 3, "Dname"
: "ENTC", "Experience_yrs" : 15, "salary" :
30000, "date_of_joining" : "20-06-04" }
{
"_id" : ObjectId("57a060bc2e8f4ad716cfca91"),
"tname" : "Atul", "Dno" : 4, "Dname" :
"Mech", "Experience_yrs" : 4, "salary" : 40000,
"date_of_joining" : ISODate("2016-08-02T08:58:36.160Z") }
{
"_id" : ObjectId("57a060cf2e8f4ad716cfca92"),
"tname" : "Sonali", "Dno" : 4, "Dname"
: "Mech", "Experience_yrs" : 4, "salary" : 50000,
"date_of_joining" : ISODate("2016-08-02T08:58:55.463Z") }
{
"_id" : ObjectId("57a060e72e8f4ad716cfca93"),
"tname" : "Harshdha", "Dno" : 1,
"Dname" : "Comp", "Experience_yrs" : 19,
"salary" : 50000, "date_of_joining" :
ISODate("2016-08-02T08:59:19.535Z") }
{
"_id" : ObjectId("57a061012e8f4ad716cfca94"),
"tname" : "Mira", "Dno" : 1, "Dname" :
"Comp", "Experience_yrs" : 7, "salary" : 30000,
"date_of_joining" : ISODate("2016-08-02T08:59:45.607Z") }
{
"_id" : ObjectId("57a0611d2e8f4ad716cfca95"),
"tname" : "Harshad", "Dno" : 2, "Dname"
: "IT", "Experience_yrs" : 8, "salary" : 35000,
"date_of_joining" : ISODate("2016-08-02T09:00:13.599Z") }
{
"_id" : ObjectId("57a0612f2e8f4ad716cfca96"),
"tname" : "Raj", "Dno" : 2, "Dname" :
"IT", "Experience_yrs" : 9, "salary" : 45000,
"date_of_joining" : ISODate("2016-08-02T09:00:31.679Z") }
4. Find the information
about all teachers of computer department
>
db.Teacher.find({Dname: "Comp"})
{
"_id" : ObjectId("57a05f1e68314a7dba4facab"),
"tname" : "Madhura", "Dno" : 1, "Dname"
: "Comp", "Experience_yrs" : 10, "salary" :
10000, "date_of_joining" : "10-03-14" }
{
"_id" : ObjectId("57a05f4d68314a7dba4facac"),
"tname" : "Alka", "Dno" : 1, "Dname" :
"Comp", "Experience_yrs" : 10, "salary" : 10000,
"date_of_joining" : ISODate("2016-08-02T08:52:29.178Z") }
{
"_id" : ObjectId("57a060e72e8f4ad716cfca93"),
"tname" : "Harshdha", "Dno" : 1,
"Dname" : "Comp", "Experience_yrs" : 19,
"salary" : 50000, "date_of_joining" :
ISODate("2016-08-02T08:59:19.535Z") }
{
"_id" : ObjectId("57a061012e8f4ad716cfca94"),
"tname" : "Mira", "Dno" : 1, "Dname" :
"Comp", "Experience_yrs" : 7, "salary" : 30000,
"date_of_joining" : ISODate("2016-08-02T08:59:45.607Z") }
5. Find the information
about all teachers of computer,IT,and e&TC department
>
db.Teacher.find({'Dname':{$in:["Comp","IT","ENTC"]}}).pretty()
{
"_id" :
ObjectId("57a05f1e68314a7dba4facab"),
"tname" : "Madhura",
"Dno" : 1,
"Dname" : "Comp",
"Experience_yrs" : 10,
"salary" : 10000,
"date_of_joining" :
"10-03-14"
}
{
"_id" :
ObjectId("57a05f4d68314a7dba4facac"),
"tname" : "Alka",
"Dno" : 1,
"Dname" : "Comp",
"Experience_yrs" : 10,
"salary" : 10000,
"date_of_joining" :
ISODate("2016-08-02T08:52:29.178Z")
}
{
"_id" : ObjectId("57a05fb468314a7dba4facad"),
"tname" : "Sagar",
"Dno" : 2,
"Dname" : "IT",
"Experience_yrs" : 15,
"salary" : 20000,
"date_of_joining" :
"20-06-04"
}
{
"_id" :
ObjectId("57a05fd068314a7dba4facae"),
"tname" : "Ganesh",
"Dno" : 3,
"Dname" : "ENTC",
"Experience_yrs" : 15,
"salary" : 30000,
"date_of_joining" :
"20-06-04"
}
{
"_id" :
ObjectId("57a060e72e8f4ad716cfca93"),
"tname" : "Harshdha",
"Dno" : 1,
"Dname" : "Comp",
"Experience_yrs" : 19,
"salary" : 50000,
"date_of_joining" :
ISODate("2016-08-02T08:59:19.535Z")
}
{
"_id" :
ObjectId("57a061012e8f4ad716cfca94"),
"tname" : "Mira",
"Dno" : 1,
"Dname" : "Comp",
"Experience_yrs" : 7,
"salary" : 30000,
"date_of_joining" :
ISODate("2016-08-02T08:59:45.607Z")
}
{
"_id" :
ObjectId("57a0611d2e8f4ad716cfca95"),
"tname" :
"Harshad",
"Dno" : 2,
"Dname" : "IT",
"Experience_yrs" : 8,
"salary" : 35000,
"date_of_joining" :
ISODate("2016-08-02T09:00:13.599Z")
}
{
"_id" :
ObjectId("57a0612f2e8f4ad716cfca96"),
"tname" : "Raj",
"Dno" : 2,
"Dname" : "IT",
"Experience_yrs" : 9,
"salary" : 45000,
"date_of_joining" :
ISODate("2016-08-02T09:00:31.679Z")
}
6. Find the information
about all teachers of computer,IT,and E&TC department having salary greate
than or equl to 10000/-
>
db.Teacher.find({'Dname':{$in:["Comp","IT","ENTC"]},salary
:{$gt:10000}}).pretty()
{
"_id" :
ObjectId("57a05fb468314a7dba4facad"),
"tname" : "Sagar",
"Dno" : 2,
"Dname" : "IT",
"Experience_yrs" : 15,
"salary" : 20000,
"date_of_joining" :
"20-06-04"
}
{
"_id" :
ObjectId("57a05fd068314a7dba4facae"),
"tname" : "Ganesh",
"Dno" : 3,
"Dname" : "ENTC",
"Experience_yrs" : 15,
"salary" : 30000,
"date_of_joining" :
"20-06-04"
}
{
"_id" :
ObjectId("57a060e72e8f4ad716cfca93"),
"tname" : "Harshdha",
"Dno" : 1,
"Dname" : "Comp",
"Experience_yrs" : 19,
"salary" : 50000,
"date_of_joining" :
ISODate("2016-08-02T08:59:19.535Z")
}
{
"_id" :
ObjectId("57a061012e8f4ad716cfca94"),
"tname" : "Mira",
"Dno" : 1,
"Dname" : "Comp",
"Experience_yrs" : 7,
"salary" : 30000,
"date_of_joining" :
ISODate("2016-08-02T08:59:45.607Z")
}
{
"_id" :
ObjectId("57a0611d2e8f4ad716cfca95"),
"tname" :
"Harshad",
"Dno" : 2,
"Dname" : "IT",
"Experience_yrs" : 8,
"salary" : 35000,
"date_of_joining" :
ISODate("2016-08-02T09:00:13.599Z")
}
{
"_id" :
ObjectId("57a0612f2e8f4ad716cfca96"),
"tname" : "Raj",
"Dno" : 2,
"Dname" : "IT",
"Experience_yrs" : 9,
"salary" : 45000,
"date_of_joining" :
ISODate("2016-08-02T09:00:31.679Z")
}
7. Find the student
information having roll_no = 1 or Sname=Rupali
>
db.Teacher.find({$or:[{roll_no:1},{sname:"Rupali"}]})
{
"_id" : ObjectId("57a068172e8f4ad716cfca97"),
"sname" : "Preeti", "roll_no" : 1,
"class" : "SE" }
{
"_id" : ObjectId("57a068552e8f4ad716cfca9b"),
"sname" : "Rupali", "roll_no" : 1,
"class" : "BE" }
8. Update the
experience of teacher-praveen to 10years, if the entry is not available in
database consider the entry as new entry.
>
db.Teacher.update({tname:"Harshad"},{$set:{Experience_yrs:10}})
WriteResult({
"nMatched" : 1, "nUpserted" : 0, "nModified" : 1
})
9. Update the deparment
of all the teachers working in IT deprtment to COMP
>
db.Teacher.update({Dname:"IT"},{$set:{Dname:"COMP"}},{multi:true})
WriteResult({
"nMatched" : 0, "nUpserted" : 0, "nModified" : 0
})
> db.Teacher.find()
{ "_id" :
ObjectId("57a05f4d68314a7dba4facac"), "tname" : "Alka",
"Dno" : 1, "Dname" : "Comp",
"Experience_yrs" : 10, "salary" : 10000,
"date_of_joining" : ISODate("2016-08-02T08:52:29.178Z") }
{ "_id" :
ObjectId("57a05fb468314a7dba4facad"), "tname" : "Sagar",
"Dno" : 2, "Dname" : "Comp",
"Experience_yrs" : 15, "salary" : 20000,
"date_of_joining" : "20-06-04" }
{ "_id" :
ObjectId("57a05fd068314a7dba4facae"), "tname" : "Ganesh",
"Dno" : 3, "Dname" : "ENTC",
"Experience_yrs" : 15, "salary" : 30000,
"date_of_joining" : "20-06-04" }
{ "_id" :
ObjectId("57a060bc2e8f4ad716cfca91"), "tname" : "Atul",
"Dno" : 4, "Dname" : "Mech",
"Experience_yrs" : 4, "salary" : 40000,
"date_of_joining" : ISODate("2016-08-02T08:58:36.160Z") }
{ "_id" :
ObjectId("57a060cf2e8f4ad716cfca92"), "tname" : "Sonali",
"Dno" : 4, "Dname" : "Mech",
"Experience_yrs" : 4, "salary" : 50000,
"date_of_joining" : ISODate("2016-08-02T08:58:55.463Z") }
{ "_id" :
ObjectId("57a060e72e8f4ad716cfca93"), "tname" : "Harshdha",
"Dno" : 1, "Dname" : "Comp",
"Experience_yrs" : 19, "salary" : 50000,
"date_of_joining" : ISODate("2016-08-02T08:59:19.535Z") }
{ "_id" :
ObjectId("57a061012e8f4ad716cfca94"), "tname" : "Mira",
"Dno" : 1, "Dname" : "Comp",
"Experience_yrs" : 7, "salary" : 30000, "date_of_joining"
: ISODate("2016-08-02T08:59:45.607Z") }
{ "_id" :
ObjectId("57a0611d2e8f4ad716cfca95"), "tname" :
"Harshad", "Dno" : 2, "Dname" : "Comp",
"Experience_yrs" : 10, "salary" : 35000,
"date_of_joining" : ISODate("2016-08-02T09:00:13.599Z") }
{ "_id" :
ObjectId("57a0612f2e8f4ad716cfca96"), "tname" : "Raj",
"Dno" : 2, "Dname" : "Comp",
"Experience_yrs" : 9, "salary" : 45000,
"date_of_joining" : ISODate("2016-08-02T09:00:31.679Z") }
10. find the teachers
name and their experience from teachers collection
>
db.Teacher.find({},{tname:2,Experience_yrs:2})
{
"_id" : ObjectId("57a05f1e68314a7dba4facab"),
"tname" : "Madhura", "Experience_yrs" : 10 }
{
"_id" : ObjectId("57a05f4d68314a7dba4facac"),
"tname" : "Alka", "Experience_yrs" : 10 }
{
"_id" : ObjectId("57a05fb468314a7dba4facad"),
"tname" : "Sagar", "Experience_yrs" : 15 }
{
"_id" : ObjectId("57a05fd068314a7dba4facae"),
"tname" : "Ganesh", "Experience_yrs" : 15 }
{
"_id" : ObjectId("57a060bc2e8f4ad716cfca91"),
"tname" : "Atul", "Experience_yrs" : 4 }
{
"_id" : ObjectId("57a060cf2e8f4ad716cfca92"),
"tname" : "Sonali", "Experience_yrs" : 4 }
{
"_id" : ObjectId("57a060e72e8f4ad716cfca93"),
"tname" : "Harshdha", "Experience_yrs" : 19 }
{
"_id" : ObjectId("57a061012e8f4ad716cfca94"),
"tname" : "Mira", "Experience_yrs" : 7 }
{
"_id" : ObjectId("57a0611d2e8f4ad716cfca95"),
"tname" : "Harshad", "Experience_yrs" : 10 }
{
"_id" : ObjectId("57a0612f2e8f4ad716cfca96"),
"tname" : "Raj", "Experience_yrs" : 9 }
11. Using Save() method
insert one entry in department collection
>
db.Teacher.save({tname:"kapil",Dname:"IT"})
WriteResult({
"nInserted" : 1 })
12. Using Save() method change the dept of
teacher praveen to IT
>
db.Teacher.save({_id:
ObjectId("57a05f1e68314a7dba4facab"),Dname:"IT"})
WriteResult({
"nMatched" : 1, "nUpserted" : 0, "nModified" : 1
})
13. Delete all the
doccuments from teachers collection having IT dept.
>
db.Teacher.remove({Dname:"IT"})
WriteResult({
"nRemoved" : 2 })
14. display with
pretty() method, the first 3 doccuments
in teachers collection in ascending order
>
db.Teacher.find().limit(3).pretty().sort({tname:1})
{
"_id" :
ObjectId("57a068172e8f4ad716cfca97"),
"sname" :
"Preeti",
"roll_no" : 1,
"class" : "SE"
}
{
"_id" :
ObjectId("57a0682a2e8f4ad716cfca98"),
"sname" :
"Akanksha",
"roll_no" : 3,
"class" : "TE"
}
{
"_id" :
ObjectId("57a068362e8f4ad716cfca99"),
"sname" : "Sham",
"roll_no" : 2,
"class" : "TE"
}
15. Consider each document in the zipcode collection has the
following form:
{
"_id":
"10280",
"city":
"NEW YORK",
"state":
"NY",
"pop": 5574,
}
Return
States with Populations above 10 Million
>
db.createCollection("zipcode")
{
"ok" : 1 }
>
db.zipcode.insert({id:1,city:"Pune",state:"Maharashtra",pop:19081})
WriteResult({
"nInserted" : 1 })
>
db.zipcode.insert({id:2,city:"Ooti",state:"Kerela",pop:1908})
WriteResult({
"nInserted" : 1 })
>
db.zipcode.insert({id:3,city:"Ahmedabad",state:"Gujrat",pop:2390})
WriteResult({
"nInserted" : 1 })
>
db.zipcode.insert({id:4,city:"Gandhinagar",state:"Gujrat",pop:1141})
WriteResult({
"nInserted" : 1 })
>
db.zipcode.aggregate({$group:{_id:"$state",Total_pop:{$sum:"$pop"}}},
{$match: {Total_pop:{$gte:2000}}}).pretty()
{
"_id" : "Gujrat", "Total_pop" : 3531 }
{
"_id" : "Maharashtra", "Total_pop" : 19081 }
16. Display
the departmentwise average salary
>
db.Teacher.aggregate({$group:{_id:"$Dname",Avg:{$avg:"$salary"}}}).pretty()
{
"_id" : null, "Avg" : 0 }
{
"_id" : "Mech", "Avg" : 45000 }
{
"_id" : "ENTC", "Avg" : 30000 }
{
"_id" : "Comp", "Avg" : 31666.666666666668 }
17. display
the no. Of employees working in each department
>
db.Teacher.aggregate({$group:{_id:"$Dname",no_of_emp:{$sum:1}}}).pretty()
{
"_id" : null, "no_of_emp" : 5 }
{
"_id" : "Mech", "no_of_emp" : 2 }
{
"_id" : "ENTC", "no_of_emp" : 1 }
{
"_id" : "Comp", "no_of_emp" : 6 }
18. Display
the department wise total salary of departments having total salary greater
than or equals to 2500/-
>
db.Teacher.aggregate({$group:{_id:"$Dname",Total_sal:{$sum:"$salary"}}},{$match:{Total_sal:{$gte:2500}}}).pretty()
{
"_id" : "Mech", "Total_sal" : 90000 }
{
"_id" : "ENTC", "Total_sal" : 30000 }
{
"_id" : "Comp", "Total_sal" : 190000 }
20. Create
the simple index on roll_no field
>
for(i=0;i<10000;i++){db.Teacher1.insert({sid:i,name:"sname"+i,class:"TE"});}
WriteResult({
"nInserted" : 1 })
>
db.Teacher1.find({name: "sname9999"}).explain();
{
"cursor" :
"BasicCursor",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 10000,
"nscanned" : 10000,
"nscannedObjectsAllPlans" :
10000,
"nscannedAllPlans" : 10000,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 78,
"nChunkSkips" : 0,
"millis" : 6,
"server" :
"ubuntu:27017",
"filterSet" : false
}
>
db.Teacher1.ensureIndex({name:1})
{
"createdCollectionAutomatically"
: false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
>
db.Teacher1.find({name: "sname9999"}).explain();
{
"cursor" : "BtreeCursor
name_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" :
1,
"nscannedAllPlans" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"name" : [
[
"sname9999",
"sname9999"
]
]
},
"server" :
"ubuntu:27017",
"filterSet" : false
}
21. create
unique index on any field for above given collections
>
for(i=0;i<10000;i++){db.Student.insert({name:"name"+i,id:i})}
WriteResult({
"nInserted" : 1 })
>
db.Student.find({name:"name999"}).explain()
{
"cursor" :
"BasicCursor",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 10000,
"nscanned" : 10000,
"nscannedObjectsAllPlans" :
10000,
"nscannedAllPlans" : 10000,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 78,
"nChunkSkips" : 0,
"millis" : 6,
"server" :
"ubuntu:27017",
"filterSet" : false
}
>
db.Student.ensureIndex({name:1},{unique:true})
{
"createdCollectionAutomatically"
: false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
>
db.Student.find({name:"name999"}).explain()
{
"cursor" : "BtreeCursor
name_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" :
1,
"nscannedAllPlans" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"name" : [
[
"name999",
"name999"
]
]
},
"server" :
"ubuntu:27017",
"filterSet" : false
}
>
db.Student.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" :
"_id_",
"ns" : "PCCOE.Student"
},
{
"v" : 1,
"unique" : true,
"key" : {
"name" : 1
},
"name" :
"name_1",
"ns" : "PCCOE.Student"
}
]
22. create
compound index on any fields for above given collections
>
db.Student.ensureIndex({name:1,id:1})
{
"createdCollectionAutomatically"
: false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
23. Show
all the indexes created in the database PCCOE
>
db.getCollectionNames().forEach(function(collection){indexes=db[collection].getIndexes();print("Indexes
for "+collection+":");printjson(indexes);});
Indexes for
Student:
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" :
"_id_",
"ns" : "PCCOE.Student"
},
{
"v" : 1,
"key" : {
"name" :
1,
"id" : 1
},
"name" :
"name_1_id_1",
"ns" : "PCCOE.Student"
}
]
Indexes for
Teacher:
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" :
"_id_",
"ns" : "PCCOE.Teacher"
}
]
Indexes for
Teacher1:
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" :
"_id_",
"ns" : "PCCOE.Teacher1"
},
{
"v" : 1,
"key" : {
"name" :
1
},
"name" : "name_1",
"ns" : "PCCOE.Teacher1"
}
]
Indexes for
system.indexes:
[ ]
Indexes for
zipcode:
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" :
"_id_",
"ns" : "PCCOE.zipcode"
}
]
24. Show
all the indexes created in above collections.
> db.Student.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" :
"_id_",
"ns" : "PCCOE.Student"
},
{
"v" : 1,
"key" : {
"name" :
1,
"id" : 1
},
"name" :
"name_1_id_1",
"ns" : "PCCOE.Student"
}
]
----------------------------------------------------------------------------------------------------
NAME :Sunil
Jadhav
ROLL NO :
TECOA145
ASSIGNMENT-5 :
Map reduce operation with suitable example using MongoDB.
----------------------------------------------------------------------------------------------------------------------
Using
Map-Reduce function comply the following requirements
1. Display the salary of employees department wise
>
var mapfu=function(){emit(this.Dname,this.salary)}
>
var reducefun=function(key,values){return(Array.sum(values))}
>
db.Teacher.mapReduce(mapfu,reducefun,{"out":"result"})
{
"result" :
"result",
"timeMillis" : 6,
"counts" : {
"input" : 8,
"emit" : 8,
"reduce" : 2,
"output" : 4
},
"ok" : 1,
}
>
db.result.find();
{
"_id" : "Comp", "value" : 100000 }
{
"_id" : "ENTC", "value" : 30000 }
{
"_id" : "IT", "value" : 20000 }
{
"_id" : "Mech", "value" : 90000 }
------------------------------------------------------------------
2. Display the count of employees departmentwise
>
var mapfu=function(){emit(this.Dname,1)}
>
var reducefun=function(key,values){return(Array.sum(values))}
>
db.Teacher.mapReduce(mapfu,reducefun,{"out":"result"})
{
"result" :
"result",
"timeMillis" : 6,
"counts" : {
"input" : 8,
"emit" : 8,
"reduce" : 2,
"output" : 4
},
"ok" : 1,
}
>
db.result.find();
{
"_id" : "Comp", "value" : 4 }
{
"_id" : "ENTC", "value" : 1 }
{
"_id" : "IT", "value" : 1 }
{
"_id" : "Mech", "value" : 2 }
------------------------------------------------------------------
3.Consider the collection “Orders “ with the
following types of doccuments
{
_id: ObjectId("50a8240b927d5d8b5891743c"),
cust_id: "abc123",
status: 'A',
price: 25,
}
Return the Total Price Per Customer
>
db.createCollection("Orders")
{
"ok" : 1 }
>
db.Orders.insert({cust_id:"abc123",status:'A',price:25})
>
db.Orders.insert({cust_id:"efg456",status:'A',price:30})
>
db.Orders.insert({cust_id:"hij789",status:'A',price:50})
>
db.Orders.insert({cust_id:"abc123",status:'A',price:55})
>
db.Orders.insert({cust_id:"hij789",status:'A',price:40})
>
db.Orders.insert({cust_id:"efg456",status:'A',price:20})
>
var mapfu=function(){emit(this.cust_id,this.price)}
>
var reducefun=function(key,values){return(Array.sum(values))}
>
db.Teacher.mapReduce(mapfu,reducefun,{"out":"result"})
{
"result" :
"result",
"timeMillis" : 32,
"counts" : {
"input" : 8,
"emit" : 8,
"reduce" : 1,
"output" : 1
},
"ok" : 1,
}
>
db.result.find()
{
"_id" : "abc123", "value" : 80 }
{
"_id" : "efg456", "value" : 50 }
{
"_id" : "hij789", "value" : 90 }
------------------------------------------------------------------
4. Display the total salatry of employees working
in computer department.
>
var mapfu=function(){emit(this.Dname,this.salary)}
>
var reducefun=function(key,values){return(Array.sum(values))}
>
db.Teacher.mapReduce(mapfu,reducefun,{"out":"result",query:{Dname:"Comp"}})
{
"result" :
"result",
"timeMillis" : 27,
"counts" : {
"input" : 4,
"emit" : 4,
"reduce" : 1,
"output" : 1
},
"ok" : 1,
}
>
db.result.find();
{
"_id" : "Comp", "value" : 100000 }
------------------------------------------------------------------
5. Consider each document in the zipcode collection has the
following form:
{
"_id":
"10280",
"city":
"NEW YORK",
"state":
"NY",
"pop":
5574,
}
Return the city wise
population of the cities in the NY state.
>
db.createCollection("Zipcode")
{
"ok" : 1 }
>
db.Zipcode.insert({city:"New York",state:"NY",popu:5574})
>
db.Zipcode.insert({city:"Albany",state:"NY",popu:6000})
>
db.Zipcode.insert({city:"Albany",state:"NY",popu:4000})
>
db.Zipcode.insert({city:"New York",state:"NY",popu:4500})
>
db.Zipcode.insert({city:"Anchorage",state:"Alaska",popu:4500})
>
db.Zipcode.insert({city:"Anchorage",state:"Alaska",popu:2300})
>
db.Zipcode.insert({city:"Juneau",state:"Alaska",popu:5000})
>
db.Zipcode.insert({city:"Juneau",state:"Alaska",popu:6500})
>
var mapfu=function(){emit(this.city,this.popu)}
>
var reducefun=function(key,values){return(Array.sum(values))}
>
db.Zipcode.mapReduce(mapfu,reducefun,{"out":"result",query:{state:"NY"}})
{
"result" :
"result",
"timeMillis" : 6,
"counts" : {
"input" : 4,
"emit" : 4,
"reduce" : 2,
"output" : 2
},
"ok" : 1,
}
>
db.result.find()
{
"_id" : "Albany", "value" : 10000 }
{
"_id" : "New York", "value" : 10074 }
-------------------------------------------------------------------------------
ROLL
NO : TECOA145
ASSIGNMENT-6 :
Connectivity with MySQL using any Java
application.
---------------------------------------------------------------------------------------------------------------------
PROGRAM
import
java.sql.*;
import
java.util.*;
import
java.lang.*;
import
java.io.*;
public
class jdbc_mysql
{
static final String
JDBC_Driver="com.mysql.driver.JDBC";
static final String
DB_URL="jdbc:mysql://localhost/";
static final String
USER="root";
static final String
PASS="mysql";
public static void main(String args[])
{
Connection conn=null;
Statement stmt=null;
String sql0="USE
DB;";
try
{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting
to database...");
conn=DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("Creating
database...");
stmt=conn.createStatement();
BufferedReader buf
= new BufferedReader(new InputStreamReader(System.in));
Scanner sc = new
Scanner(System.in);
int c=0;
do
{
System.out.println("-------------JDBC_MYSQL-------------");
System.out.println("1.
Create database");
System.out.println("2.
Create table");
System.out.println("3.
Insert value");
System.out.println("4.
Display table");
System.out.println("5.
Update");
System.out.println("6.
Delete");
System.out.println("7.
Exit");
System.out.println("Enter
a choice:");
int
ch=Integer.parseInt(buf.readLine());
switch(ch)
{
case 1:
System.out.println("Creating
database...");
stmt=conn.createStatement();
String
sql="CREATE DATABASE DB;";
stmt.executeUpdate(sql);
System.out.println("Database
created!!!");
break;
case 2:
System.out.println("Connecting
to selected database...");
stmt=conn.createStatement();
stmt.executeUpdate(sql0);
System.out.println("Connection
to selected database is successful!");
System.out.println("Creating
selected table...");
sql="CREATE
TABLE STUDENT(roll int primary key, first varchar(12), last varchar(12), marks
int);";
stmt.executeUpdate(sql);
System.out.println("Selected
table is created!");
break;
case 3:
System.out.println("Inserting
values...");
stmt=conn.createStatement();
sql="INSERT
INTO STUDENT VALUES(1,'Sunil','Jadhav',75);";
stmt.executeUpdate(sql);
sql="INSERT
INTO STUDENT VALUES(2,'Ashwin','Kasbe',85);";
stmt.executeUpdate(sql);
sql="INSERT
INTO STUDENT VALUES(3,'Kalpesh','Jadhav',80);";
stmt.executeUpdate(sql);
sql="INSERT
INTO STUDENT VALUES(4,'Vaibhav','Kakade',70);";
stmt.executeUpdate(sql);
sql="INSERT
INTO STUDENT VALUES(5,'Jay','Lohane',87);";
stmt.executeUpdate(sql);
System.out.println("Values
inserted!");
break;
case 4:
System.out.println("Displaying
values...");
stmt=conn.createStatement();
sql="SELECT
* FROM STUDENT";
ResultSet
rs = stmt.executeQuery(sql);
while(rs.next())
{
int
roll=rs.getInt("roll");
String
first=rs.getString("first");
String
last=rs.getString("last");
int
marks=rs.getInt("marks");
System.out.println("Roll
number: " + roll);
System.out.println("First
name: " + first);
System.out.println("Last
name: " + last);
System.out.println("Marks:
" + marks);
}
break;
case 5:
System.out.println("Updating
values...");
stmt=conn.createStatement();
sql
= "UPDATE STUDENT SET marks=70 WHERE roll=1";
stmt.executeUpdate(sql);
System.out.println("Values
updated!");
break;
case 6:
System.out.println("Deleting
values...");
stmt=conn.createStatement();
sql
= "DELETE FROM STUDENT WHERE roll=2";
stmt.executeUpdate(sql);
System.out.println("Values
deleted!");
break;
case 7:
System.out.println("Do
you want to quit? \n1. Yes\n2. No");
c=
sc.nextInt();
}
}while(c!=1);
sc.close();
}
catch(SQLException se)
{
se.printStackTrace(); //Handle
errors for JDBC
}
catch(Exception e)
{
e.printStackTrace();
//Handle errors for Class.forName
}
finally
{ //STEP 5: finally block used to close resources
try
{
if(stmt!=null)
stmt.close();
}
catch(SQLException se2)
{
}// nothing we can do
try
{
if(conn!=null)
conn.close();
}
catch(SQLException se)
{
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
}//end
JDBC
OUTPUT
pccoe@ubuntu:~$
javac jdbc_mysql.java
pccoe@ubuntu:~$
java -classpath ".:mysql-connector-java-5.1.18-bin.jar" jdbc_mysql
Connecting
to database...
Creating
database...
-------------JDBC_MYSQL-------------
1.
Create database
2.
Create table
3.
Insert value
4.
Display table
5.
Update
6.
Delete
7.
Exit
Enter
a choice:
1
Creating
database...
Database
created!!!
-------------JDBC_MYSQL-------------
1.
Create database
2.
Create table
3.
Insert value
4.
Display table
5.
Update
6.
Delete
7.
Exit
Enter
a choice:
2
Connecting
to selected database...
Connection
to selected database is successful!
Creating
selected table...
Selected
table is created!
-------------JDBC_MYSQL-------------
1.
Create database
2.
Create table
3.
Insert value
4.
Display table
5.
Update
6.
Delete
7.
Exit
Enter
a choice:
3
Inserting
values...
Values
inserted!
-------------JDBC_MYSQL-------------
1.
Create database
2.
Create table
3.
Insert value
4.
Display table
5.
Update
6.
Delete
7.
Exit
Enter
a choice:
4
Displaying
values...
Roll
number: 1
First
name: Sunil
Last
name: Jadhav
Marks:
75
Roll
number: 2
First
name: Ashwin
Last
name: Kasbe
Marks:
85
Roll
number: 3
First
name: Kalpesh
Last
name: Jadhav
Marks:
80
Roll
number: 4
First
name: Vaibhav
Last
name: Kakade
Marks:
70
Roll
number: 5
First
name: Jay
Last
name: Lohane
Marks:
87
-------------JDBC_MYSQL-------------
1.
Create database
2.
Create table
3.
Insert value
4.
Display table
5.
Update
6.
Delete
7.
Exit
Enter
a choice:
5
Updating
values...
Values
updated!
-------------JDBC_MYSQL-------------
1.
Create database
2.
Create table
3.
Insert value
4.
Display table
5.
Update
6.
Delete
7.
Exit
Enter
a choice:
4
Displaying
values...
Roll
number: 1
First
name: Sunil
Last
name: Jadhav
Marks:
70
Roll
number: 2
First
name: Ashwin
Last
name: Kasbe
Marks:
85
Roll
number: 3
First
name: Kalpesh
Last
name: Jadhav
Marks:
80
Roll
number: 4
First
name: Vaibhav
Last
name: Kakade
Marks:
70
Roll
number: 5
First
name: Jay
Last
name: Lohane
Marks:
87
-------------JDBC_MYSQL-------------
1.
Create database
2.
Create table
3.
Insert value
4.
Display table
5.
Update
6.
Delete
7.
Exit
Enter
a choice:
6
Deleting
values...
Values
deleted!
-------------JDBC_MYSQL-------------
1.
Create database
2.
Create table
3.
Insert value
4.
Display table
5.
Update
6.
Delete
7.
Exit
Enter
a choice:
4
Displaying
values...
Roll
number: 1
First
name: Sunil
Last
name: Jadhav
Marks:
70
Roll
number: 3
First
name: Kalpesh
Last
name: Jadhav
Marks:
80
Roll
number: 4
First
name: Vaibhav
Last
name: Kakade
Marks:
70
Roll
number: 5
First
name: Jay
Last
name: Lohane
Marks:
87
-------------JDBC_MYSQL-------------
1.
Create database
2.
Create table
3.
Insert value
4.
Display table
5.
Update
6.
Delete
7.
Exit
Enter
a choice:
7
Do
you want to quit?
1.
Yes
2.
No
1
Goodbye!
pccoe@ubuntu:~$
----------------------------------------------------------------------------
NAME :
Sunil Jadhav
ROLL NO :
TECOA145
ASSIGNMENT-7 :
Connectivity with MongoDB using any Java application.
----------------------------------------------------------------------------------------------------------------------
PROGRAM
import
com.mongodb.MongoClient;
import
com.mongodb.DB;
import
com.mongodb.DBCollection;
import
com.mongodb.BasicDBObject;
import
com.mongodb.DBObject;
import
com.mongodb.DBCursor;
import
java.util.*;
public
class mongojdbc
{
public static void main( String args[]
)
{
try
{
// To connect to mongodb server
MongoClient mongoClient = new MongoClient(
"localhost" , 27017 );
// Now connect to your databases
DB db = mongoClient.getDB( "test"
);
System.out.println("Connect to database
successfully");
System.out.println("Enter Name Of
Collection");
Scanner s=new Scanner(System.in);
String colname=s.nextLine();
DBCollection coll =
db.createCollection(colname,null);
System.out.println("Collection created
successfully");
DBCollection col = db.getCollection(colname);
System.out.println("Collection
"+colname+" selected successfully");
int a;
do
{
System.out.println("--------MongoDB-JDBC--------\n1.Insert\n2.Retrive\n3.Update\n4.Delete\nSelect
an operation: ");
int ch=s.nextInt();
switch(ch)
{
case
1:
System.out.println("Inserting
Into Collection\nInserted");
BasicDBObject doc = new
BasicDBObject("title", "MongoDB1").
append("description",
"database1").
append("likes",
1001).
append("url",
"http://www.mongodb.com").
append("by",
"MongoDB");
coll.insert(doc);
System.out.println("Document
inserted successfully");
break;
case
2:
System.out.println("Retriving
data...\nRetrived");
DBCursor
cursor = coll.find();
int i=1;
while (cursor.hasNext())
{
System.out.println("Inserted Document: "+i);
System.out.println(cursor.next());
i++;
}
break;
case
3:
System.out.println("Update
Collection\n");
BasicDBObject
update = new BasicDBObject();
update.append("$set",new
BasicDBObject().append("likes",200));
BasicDBObject search = new BasicDBObject();
search.append("by","MongoDB");
coll.updateMulti(search,update);
//coll.update(search,update,false,true);
System.out.println("Document updated successfully");
cursor = coll.find();
int j=1;
while (cursor.hasNext())
{
System.out.println("Updated Document: "+j);
System.out.println(cursor.next());
j++;
}
break;
case
4:
DBObject
myDoc = coll.findOne();
coll.remove(myDoc);
DBCursor cursor2 =
coll.find();
int k=1;
while (cursor2.hasNext())
{
System.out.println("Inserted Document: "+k);
System.out.println(cursor2.next());
k++;
}
System.out.println("Document deleted successfully");
break;
}
System.out.println("Do you want to
continue?\n1. Yes\n2. No\n");
a = s.nextInt();
if(a==2)
System.out.println("Goodbye");
}while(a!=2);
s.close();
}catch(Exception e){
System.err.println(
e.getClass().getName() + ": " + e.getMessage() );
}
}}
OUTPUT
pccoe@pccoe-H81M-S1:~/Desktop$
javac -classpath ".:mongo-java-driver-2.10.1.jar" mongojdbc.java
pccoe@pccoe-H81M-S1:~/Desktop$
java -classpath ".:mongo-java-driver-2.10.1.jar" mongojdbc
Connect
to database successfully
Enter
Name Of Collection
data
Collection
created successfully
Collection
data selected successfully
--------MongoDB-JDBC--------
1.Insert
2.Retrive
3.Update
4.Delete
Select
an operation:
1
Inserting
Into Collection
Inserted
Document
inserted successfully
Do
you want to continue?
1.
Yes
2.
No
1
--------MongoDB-JDBC--------
1.Insert
2.Retrive
3.Update
4.Delete
Select
an operation:
1
Inserting
Into Collection
Inserted
Document
inserted successfully
Do
you want to continue?
1.
Yes
2.
No
1
--------MongoDB-JDBC--------
1.Insert
2.Retrive
3.Update
4.Delete
Select
an operation:
1
Inserting
Into Collection
Inserted
Document
inserted successfully
Do
you want to continue?
1.
Yes
2.
No
1
--------MongoDB-JDBC--------
1.Insert
2.Retrive
3.Update
4.Delete
Select
an operation:
2
Retriving
data...
Retrived
Inserted
Document: 1
{
"_id" : { "$oid" : "57e2b99781c62a209b0fcbaf"} ,
"title" : "MongoDB1" , "description" :
"database1" , "likes" : 1001 , "url" :
"http://www.mongodb.com" , "by" : "MongoDB"}
Inserted
Document: 2
{
"_id" : { "$oid" : "57e2b99881c62a209b0fcbb0"} ,
"title" : "MongoDB1" , "description" :
"database1" , "likes" : 1001 , "url" :
"http://www.mongodb.com" , "by" : "MongoDB"}
Inserted
Document: 3
{
"_id" : { "$oid" : "57e2b99a81c62a209b0fcbb1"} ,
"title" : "MongoDB1" , "description" :
"database1" , "likes" : 1001 , "url" :
"http://www.mongodb.com" , "by" : "MongoDB"}
Do
you want to continue?
1.
Yes
2.
No
1
--------MongoDB-JDBC--------
1.Insert
2.Retrive
3.Update
4.Delete
Select
an operation:
3
Update
Collection
Document
updated successfully
Updated
Document: 1
{
"_id" : { "$oid" : "57e2b99781c62a209b0fcbaf"} ,
"title" : "MongoDB1" , "description" :
"database1" , "likes" : 200 , "url" :
"http://www.mongodb.com" , "by" : "MongoDB"}
Updated
Document: 2
{
"_id" : { "$oid" : "57e2b99881c62a209b0fcbb0"} ,
"title" : "MongoDB1" , "description" :
"database1" , "likes" : 200 , "url" :
"http://www.mongodb.com" , "by" : "MongoDB"}
Updated
Document: 3
{
"_id" : { "$oid" : "57e2b99a81c62a209b0fcbb1"} ,
"title" : "MongoDB1" , "description" : "database1"
, "likes" : 200 , "url" :
"http://www.mongodb.com" , "by" : "MongoDB"}
Do
you want to continue?
1.
Yes
2.
No
1
--------MongoDB-JDBC--------
1.Insert
2.Retrive
3.Update
4.Delete
Select
an operation:
4
Inserted
Document: 1
{
"_id" : { "$oid" : "57e2b99881c62a209b0fcbb0"} ,
"title" : "MongoDB1" , "description" :
"database1" , "likes" : 200 , "url" :
"http://www.mongodb.com" , "by" : "MongoDB"}
Inserted
Document: 2
{
"_id" : { "$oid" : "57e2b99a81c62a209b0fcbb1"} ,
"title" : "MongoDB1" , "description" :
"database1" , "likes" : 200 , "url" :
"http://www.mongodb.com" , "by" : "MongoDB"}
Document
deleted successfully
Do
you want to continue?
1.
Yes
2.
No
2
Goodbye
pccoe@pccoe-H81M-S1:~/Desktop$
-------------------------------------------------------------------------
ROLL
NO : TECOA145
ASSIGNMENT-8 : Implement basic operations and indexing
with suitable example using Cassandra
----------------------------------------------------------------------------------------------------------------------
INITIALIZE
SERVER
pccoe@ubuntu:~$
cd apache-cassandra-2.1.15/
pccoe@ubuntu:~/apache-cassandra-2.1.15$
cd bin
pccoe@ubuntu:~/apache-cassandra-2.1.15/bin$
sudo ./cassandra
[sudo]
password for pccoe:
pccoe@ubuntu:~/apache-cassandra-2.1.15/bin$
CompilerOracle: inline org/apache/cassandra/db/AbstractNativeCell.compareTo
(Lorg/apache/cassandra/db/composites/Composite;)I
CompilerOracle:
inline org/apache/cassandra/db/composites/AbstractSimpleCellNameType.compareUnsigned
(Lorg/apache/cassandra/db/composites/Composite;Lorg/apache/cassandra/db/composites/Composite;)I
CompilerOracle:
inline org/apache/cassandra/io/util/Memory.checkBounds (JJ)V
org/apache/cassandra/utils/FastByteOperations$UnsafeOperations.compareTo
(Ljava/nio/ByteBuffer;Ljava/nio/ByteBuffer;)I
INFO 08:52:55 Hostname: ubuntu.ubuntu-domain
INFO 08:52:55 Loading settings from
file:/home/pccoe/apache-cassandra-2.1.15/conf/cassandra.yaml
INFO 08:52:55 Node configuration:[authenticator=AllowAllAuthenticator;
authorizer=AllowAllAuthorizer; auto_snapshot=true;
INFO 08:53:00 Node localhost/127.0.0.1 state jump
to NORMAL
QUERY
EXECUTION
pccoe@ubuntu:~$
cd apache-cassandra-2.1.15/
pccoe@ubuntu:~/apache-cassandra-2.1.15$
cd bin/
pccoe@ubuntu:~/apache-cassandra-2.1.15/bin$
./cqlsh
Connected
to Test Cluster at 127.0.0.1:9042.
[cqlsh
5.0.1 | Cassandra 2.1.15 | CQL spec 3.2.1 | Native protocol v3]
Use
HELP for help.
cqlsh>
CREATE KEYSPACE TECOA145 WITH REPLICATION =
{'class':'SimpleStrategy','replication_factor':'3'};
cqlsh>
USE TECOA145;
cqlsh:TECOA145>
CREATE TABLE DATA(id int primary key, name text, sname text, marks int);
cqlsh:TECOA145>
INSERT INTO DATA(id,name,sname,marks) values(1,'A','B',80);
cqlsh:TECOA145>
INSERT INTO DATA(id,name,sname,marks) values(2,'C','D',70);
cqlsh:TECOA145>
INSERT INTO DATA(id,name,sname,marks) values(2,'E','F',75);
cqlsh:TECOA145>
INSERT INTO DATA(id,name,sname,marks) values(4,'G','H',72);
cqlsh:TECOA145>
delete from DATA where id=2;
cqlsh:TECOA145>
INSERT INTO DATA(id,name,sname,marks) values(2,'C','D',70);
cqlsh:TECOA145>
INSERT INTO DATA(id,name,sname,marks) values(3,'E','F',75);
cqlsh:TECOA145>
INSERT INTO DATA(id,name,sname,marks) values(5,'I','J',73);
cqlsh:TECOA145>
INSERT INTO DATA(id,name,sname,marks) values(6,'K','L',87);
cqlsh:TECOA145>
INSERT INTO DATA(id,name,sname,marks) values(7,'M','N',85);
cqlsh:TECOA145>
INSERT INTO DATA(id,name,sname,marks) values(8,'O','P',83);
cqlsh:TECOA145>
INSERT INTO DATA(id,name,sname,marks) values(9,'Q','R',81);
cqlsh:TECOA145>
INSERT INTO DATA(id,name,sname,marks) values(10,'S','T',91);
cqlsh:TECOA145>
select * from DATA;
id | marks | name | sname
----+-------+------+-------
5 |
73 | I | J
10 |
91 | S | T
1 |
80 | A | B
8 |
83 | O | P
2 |
70 | C | D
4 |
72 | G | H
7 |
85 | M | N
6 |
87 | K | L
9 |
81 | Q | R
3 |
75 | E | F
(10
rows)
cqlsh:TECOA145>
UPDATE DATA set marks=93 WHERE id=10;
cqlsh:TECOA145>
select * from DATA;
id | marks | name | sname
----+-------+------+-------
5 |
73 | I | J
10 |
93 | S | T
1 |
80 | A | B
8 |
83 | O | P
2 |
70 | C | D
4 |
72 | G | H
7 |
85 | M | N
6 |
87 | K | L
9 |
81 | Q | R
3 |
75 | E | F
(10
rows)
cqlsh:TECOA145>
CREATE INDEX ON DATA(name);
cqlsh:TECOA145>
ALTER TABLE DATA ADD valid text;
cqlsh:TECOA145>
select * from DATA;
id | marks | name | sname | valid
----+-------+------+-------+-------
5 |
73 | I | J |
null
10 |
91 | S | T |
null
1 |
80 | A | B |
null
8 |
83 | O | P |
null
2 |
70 | C | D |
null
4 |
72 | G | H |
null
7 |
66 | M | N |
null
6 |
87 | K | L |
null
9 |
81 | Q | R |
null
3 |
75 | E | F |
null
(10
rows)
cqlsh:TECOA145>
ALTER TABLE DATA DROP valid;
cqlsh:TECOA145>
select * from DATA;
id | marks | name | sname
----+-------+------+-------
5 |
73 | I | J
10 |
91 | S | T
1 |
80 | A | B
8 |
83 | O | P
2 |
70 | C | D
4 |
72 | G | H
7 |
66 | M | N
6 |
87 | K | L
9 |
81 | Q | R
3 |
75 | E | F
(10
rows)
cqlsh:TECOA145>
select * from DATA WHERE NAME='I';
id | marks | name | sname
----+-------+------+-------
5 |
73 | I | J
(1
rows)
cqlsh:TECOA145>
DESCRIBE TABLE DATA;
CREATE
TABLE TECOA145.data (
id int PRIMARY KEY,
marks int,
name text,
sname text
)
WITH bloom_filter_fp_chance = 0.01
AND caching =
'{"keys":"ALL",
"rows_per_partition":"NONE"}'
AND comment = ''
AND compaction = {'class':
'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy'}
AND compression = {'sstable_compression':
'org.apache.cassandra.io.compress.LZ4Compressor'}
AND dclocal_read_repair_chance = 0.1
AND default_time_to_live = 0
AND gc_grace_seconds = 864000
AND max_index_interval = 2048
AND memtable_flush_period_in_ms = 0
AND min_index_interval = 128
AND read_repair_chance = 0.0
AND speculative_retry = '99.0PERCENTILE';
CREATE
INDEX data_name_idx ON TECOA145.data (name);
cqlsh:TECOA145>
ALTER TABLE DATA RENAME id TO identity;
cqlsh:TECOA145>
select * from DATA;
identity | marks | name | sname
----------+-------+------+-------
5 |
73 | I | J
10 |
91 | S | T
1 |
80 | A | B
8 |
83 | O | P
2 |
70 | C | D
4 |
72 | G | H
7 |
66 | M | N
6 |
87 | K | L
9 |
81 | Q | R
3 |
75 | E | F
(10
rows)
cqlsh:TECOA145>
DROP TABLE DATA;
cqlsh:TECOA145>
select * from DATA;
InvalidRequest:
code=2200 [Invalid query] message="unconfigured columnfamily data"
cqlsh:TECOA145>