Skip to main content

MYSQL important commands

DBMS Queries :

//for fedora-19 or 20 make sure u have to log in as a root 
//if no then type su in terminal then enter password
//installed mysql and  then....direct use following commands
//NOTE "<-" this sign is use for enter....
 
To open MYSQL :

systemctl start mysqld <-

mysql <-

To create database:

show databases <-
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)
create database vishakha; <-
show databases <-

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| vishakha           |
+--------------------+
5 rows in set (0.00 sec)

use vishakha;<-

show tables; <-

o/p-----empty set

create table vt(id int,nm varchar(10)); <-

o/p:
Query OK, 0 rows affected (0.05 sec)
 
describe table:

desc vt; <-
or
show columns from vt;<-

o/p:

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| nm    | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

select * from vt; <-

Output:  
Empty set....(0.00sec) 

 insert into vt values(1,"abc");  <-

Output:  

 Query OK, 1 row affected (0.01 sec)

 MariaDB [vishakha]> select * from vt;
+-------+------+
 |   id  | nm  |
+------ +------+
 |    1  | abc |
+------ +------+
1 row in set (0.00 sec)

insert into vt values(2,'xyz');  <-

 o/p:
+------+------+
| id   | nm   |
+------+------+
|    1 | abc  |
|    2 | xyz  |
+------+------+
 


alter table vt add column marks int after nm; <-
//here we are adding one new column named as marks.
Output: 

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

MariaDB [vishakha]> select * from vt;

+------+------+-------+
| id   | nm   | marks |
+------+------+-------+
|    1 | abc  |  NULL |
|    2 | xyz  |  NULL |
+------+------+-------+

2 rows in set (0.00 sec)

insert into vt values(3,'vishakha',89);  <-

 select * from vt;o/p:
+------+----------+-------+
| id   | nm       | marks |
+------+----------+-------+
|    1 | abc      |  NULL |
|    2 | xyz      |  NULL |
|    3 | vishakha |    89 |
+------+----------+-------+
3 rows in set (0.00 sec)
update  vt set marks=65 where id=1; <-

update  vt set marks=85 where id=2; <-

select * from vt;
o/p:
+------+----------+-------+
| id   | nm       | marks |
+------+----------+-------+
|    1 | abc      |    65 |
|    2 | xyz      |    85 |
|    3 | vishakha |    89 |
+------+----------+-------+
3 rows in set (0.00 sec)
insert into vt values(3,'aaa',88);  <-

select * from vt;

o/p:

+------+----------+-------+
| id   | nm       | marks |
+------+----------+-------+
|    1 | abc      |    65 |
|    2 | xyz      |    85 |
|    3 | vishakha |    89 |
|    3 | aaa      |    88 |
+------+----------+-------+
4 rows in set (0.00 sec)

 delete from vt1 where id=3;

select * from vt;

o/p:
+------+------+-------+
| id   | nm   | marks |
+------+------+-------+
|    1 | abc  |  NULL |
|    2 | xyz  |  NULL |
+------+------+-------+
2 rows in set (0.00 sec)

drop table vt; <-
(this use for delete view)

VIEW:

create view vt1 as select * from vt;
 o/p:
Query OK, 0 rows affected (0.04 sec)

select * from vt;
o/p:
+------+------+-------+
| id   | nm   | marks |
+------+------+-------+
|    1 | abc  |  NULL |
|    2 | xyz  |  NULL |
+------+------+-------+
2 rows in set (0.00 sec)

drop view view_nm;

eg. drop view vt1;

Query OK, 0 rows affected (0.00 sec)

RENAME:

rename table table_nm to new_table_nm;

or

alter table tbl_nm rename new_table_nm;

eg:

rename table vt to t;

ORDER BY:

select * from vt order by id;

o/p:
+------+------+-------+
| id   | nm   | marks |
+------+------+-------+
|    1 | abc  |   100 |
|    2 | xyz  |    90 |
+------+------+-------+
2 rows in set (0.00 sec)



select * from vt order by nm asc;

o/p:
+------+------+-------+
| id   | nm   | marks |
+------+------+-------+
|    1 | abc  |   100 |
|    2 | xyz  |    90 |
+------+------+-------+
2 rows in set (0.00 sec)

select * from vt order by nm desc;

o/p:

+------+------+-------+
| id   | nm   | marks |
+------+------+-------+
|    2 | xyz  |    90 |
|    1 | abc  |   100 |
+------+------+-------+
2 rows in set (0.00 sec)

AND:

select * from vt where nm='abc' and marks=100;
o/p:
+------+------+-------+
| id   | nm   | marks |
+------+------+-------+
|    1 | abc  |   100 |
+------+------+-------+
1 row in set (0.00 sec)


OR:

 select * from vt where nm='abc' or  marks=90;


o/p:
+------+------+-------+
| id   | nm   | marks |
+------+------+-------+
|    2 | xyz  |    90 |
|    1 | abc  |   100 |
+------+------+-------+
2 rows in set (0.00 sec)

NOT:

select * from vt where not  marks=90;


o/p:

+------+------+-------+
| id   | nm   | marks |
+------+------+-------+
|    1 | abc  |   100 |
+------+------+-------+
1 row in set (0.00 sec)

UNION Syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2; 

table 1:

+------+------+-------+
| id   | nm   | marks |
+------+------+-------+
|    1 | abc  |   100 |
|    2 | vish |    44 |
+------+------+-------+

table 2:

+------+------+
| rn   | nm   |
+------+------+
|    1 | vish |
|    2 | vish |
+------+------+

 

select id from vt   union  select nm from aaa;    ->
o/p:

+------+
| id   |
+------+
| 1    |
| 2    |
| vish |
+------+
3 rows in set (0.00 sec)

select avg(marks) from vt;->

+------------+
| avg(marks) |
+------------+
|    72.0000 |
+------------+

select sum(marks) from vt;->

+------------+
| sum(marks) |
+------------+
|        144 |
+------------+
1 row in set (0.00 sec)

union all:
select id,nm from vt union all select rn,nm from aaa;->
o/p:
+------+------+
| id   | nm   |
+------+------+
|    1 | abc  |
|    2 | vish |
|    1 | vish |
|    2 | vish |
+------+------+
4 rows in set (0.00 sec)

limit:

select  * from vt limit 1;->
o/p:
+------+------+-------+
| id   | nm   | marks |
+------+------+-------+
|    1 | abc  |   100 |
+------+------+-------+
1 row in set (0.00 sec)

HAVING:

 select marks,nm from vt  having marks<50;
o/p:
+-------+------+
| marks | nm   |
+-------+------+
|    44 | vish |
+-------+------+
1 row in set (0.00 sec)

 select marks,nm from vt  having marks>50;

o/p:
+-------+------------+
| marks | nm         |
+-------+------------+
|   100 | abc        |
|    56 | abcd       |
|    86 | efgh       |
|    86 | iggh       |
|    76 | cderf      |
|    76 | olderf     |
|    76 | gu joon pi |
|    76 | gaum jandi |
+-------+------------+
8 rows in set (0.00 sec)

between:

select * from vt where marks between 50 and 90;
o/p:
+------+------------+-------+
| id   | nm         | marks |
+------+------------+-------+
|    3 | abcd       |    56 |
|    4 | efgh       |    86 |
|    5 | iggh       |    86 |
|    6 | cderf      |    76 |
|    7 | olderf     |    76 |
|    8 | gu joon pi |    76 |
|    9 | gaum jandi |    76 |
+------+------------+-------+
7 rows in set (0.00 sec)

Aliases: 
(use name instead of column nm just for simplification of user)
(this is use only for current use...)
select marks from vt as mks;
 
o/p:
+-------+
| marks |
+-------+
|   100 |
|    44 |
|    56 |
|    86 |
|    86 |
|    76 |
|    76 |
|    76 |
|    76 |
+-------+
9 rows in set (0.00 sec)


Comments in sql:

-- select all

 select * from vt;-- where id=10
o/p:
+------+------------+-------+
| id   | nm         | marks |
+------+------------+-------+
|    1 | abc        |   100 |
|    2 | vish       |    44 |
|    3 | abcd       |    56 |
|    4 | efgh       |    86 |
|    5 | iggh       |    86 |
|    6 | cderf      |    76 |
|    7 | olderf     |    76 |
|    8 | gu joon pi |    76 |
|    9 | gaum jandi |    76 |
+------+------------+-------+
9 rows in set (0.00 sec)

If we want to change column name then:

alter table tbl_nm change column old_colmn_nm   new_column_nm data_type(size);

alter table vt2 change column id id2 varchar(10);

Query OK, 1 row affected (0.09 sec)               
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [vishakha]> select * from vt2;
+------+------+------+
| id2  | nm   | clg  |
+------+------+------+
| 1    | abc  | viit |
+------+------+------+
1 row in set (0.00 sec)



 Primary Key:

alter table vt add primary key(id);

o/p:
Query OK, 5 rows affected (0.09 sec)              
Records: 5  Duplicates: 0  Warnings: 0


or

Example:
CREATE TABLE VT (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
); 

Temporary table: 

 create temporary table temp(tid int,tnm char(5));

Query OK, 0 rows affected (0.02 sec)
 insert into temp values(1,'vish');
Query OK, 1 row affected (0.02 sec)

MariaDB [vishakha]> select * from temp;
+------+------+
| tid  | tnm  |
+------+------+
|    1 | vish |
+------+------+
1 row in set (0.01 sec)  


Indexes :

Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries.

Syntax:
create index index_nm on table_name;

Example:
create index index1 on student; <-

show index from student; <-
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          1 | index1   |            1 | id          | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
drop index index1 on student; <-

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
show index from student;<-
Empty set (0.00 sec)

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Aditya             |
| Bank               |
| ESSS               |
| Mohan              |
| abc                |
| backup             |
| customer           |
| employee           |
| g                  |
| info               |
| main               |
| mysql              |
| performance_schema |
| record             |
| stud               |
| student            |
| teb                |
| test               |
+--------------------+
19 rows in set (0.05 sec)

MariaDB [(none)]> create database vishakha;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use vishakha;
Database changed
MariaDB [vishakha]> create table Account(acc_no int,br_nm varchar(15),bal float(10,2));
Query OK, 0 rows affected (0.09 sec)

MariaDB [vishakha]> show tables;
+--------------------+
| Tables_in_vishakha |
+--------------------+
| Account            |
+--------------------+
1 row in set (0.00 sec)

MariaDB [vishakha]> desc Account;
+--------+-------------+------
+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| acc_no | int(11)     | YES  |     | NULL    |       |
| br_nm  | varchar(15) | YES  |     | NULL    |       |
| bal    | float(10,2) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

MariaDB [vishakha]> select * from Account;
Empty set (0.00 sec)

MariaDB [vishakha]> create table branch(br_nm varchar(10),br_ct varchar(15),assets mediumint);
Query OK, 0 rows affected (0.05 sec)

MariaDB [vishakha]> create table customer(cust_nm varchar(10),cust_street varchar(15),cust_ct varchar(10));
Query OK, 0 rows affected (0.05 sec)

MariaDB [vishakha]> create table depositor(cust_nm varchar(10),acc_no bigint);Query OK, 0 rows affected (0.06 sec)

MariaDB [vishakha]> create table loan(loan_no bigint,br_nm varchar(10),amt float(10,2));
Query OK, 0 rows affected (0.04 sec)

MariaDB [vishakha]> create table borrower(cust_nm varchar(10),loan_no bigint);Query OK, 0 rows affected (0.05 sec)

MariaDB [vishakha]> insert into Account values(32600782748,"sbi",10000);Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [vishakha]> select * from Account;
+------------+-------+----------+
| acc_no     | br_nm | bal      |
+------------+-------+----------+
| 2147483647 | sbi   | 10000.00 |
+------------+-------+----------+
1 row in set (0.00 sec)

MariaDB [vishakha]> insert into Account values(32600,"sbi",10000);
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> insert into Account values(32600,"sbi",10000);
Query OK, 1 row affected (0.02 sec)

MariaDB [vishakha]> insert into Account values(37600,"icic",10000);
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> insert into Account values(7800,"icic",40000);
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> insert into Account values(45800,"idbi",80000);
Query OK, 1 row affected (0.02 sec)

MariaDB [vishakha]> insert into Account values(45800,"idbi",10000);
Query OK, 1 row affected (0.02 sec)

MariaDB [vishakha]> insert into Account values(85800,"idbi",10000);
Query OK, 1 row affected (0.02 sec)

MariaDB [vishakha]> insert into Account values(25800,"rbi",30000);
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> select * from Account;
+------------+-------+----------+
| acc_no     | br_nm | bal      |
+------------+-------+----------+
| 2147483647 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      37600 | icic  | 10000.00 |
|       7800 | icic  | 40000.00 |
|      45800 | idbi  | 80000.00 |
|      45800 | idbi  | 10000.00 |
|      85800 | idbi  | 10000.00 |
|      25800 | rbi   | 30000.00 |
+------------+-------+----------+
9 rows in set (0.00 sec)

MariaDB [vishakha]> insert into borrower values("Sankalp XYZ",1000000000);Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [vishakha]> insert into borrower values("ABCd EFG",20000000);
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> insert into borrower values("hgds",50000000);
Query OK, 1 row affected (0.02 sec)

MariaDB [vishakha]> insert into borrower values("hhhh",85000000);
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> insert into borrower values("jjjj",5600000);
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> create table loan(1000000,"sbi",8000);ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1000000,"sbi",8000)' at line 1
MariaDB [vishakha]> insert into loan values(1000000,"sbi",8000);
Query OK, 1 row affected (0.02 sec)

MariaDB [vishakha]> insert into loan values(2000000,"sbi",7000);
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> insert into loan values(3000000,"icici",3000);
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> insert into loan values(4000000,"rbi",5000);
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> select * from loan;
+---------+-------+---------+
| loan_no | br_nm | amt     |
+---------+-------+---------+
| 1000000 | sbi   | 8000.00 |
| 2000000 | sbi   | 7000.00 |
| 3000000 | icici | 3000.00 |
| 4000000 | rbi   | 5000.00 |
+---------+-------+---------+
4 rows in set (0.00 sec)

MariaDB [vishakha]> select * from borrower;
+------------+------------+
| cust_nm    | loan_no    |
+------------+------------+
| Sankalp XY | 1000000000 |
| ABCd EFG   |   20000000 |
| hgds       |   50000000 |
| hhhh       |   85000000 |
| jjjj       |    5600000 |
+------------+------------+
5 rows in set (0.00 sec)

MariaDB [vishakha]> select * from account;
ERROR 1146 (42S02): Table 'vishakha.account' doesn't exist
MariaDB [vishakha]> select * from Account;
+------------+-------+----------+
| acc_no     | br_nm | bal      |
+------------+-------+----------+
| 2147483647 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      37600 | icic  | 10000.00 |
|       7800 | icic  | 40000.00 |
|      45800 | idbi  | 80000.00 |
|      45800 | idbi  | 10000.00 |
|      85800 | idbi  | 10000.00 |
|      25800 | rbi   | 30000.00 |
+------------+-------+----------+
9 rows in set (0.00 sec)

MariaDB [vishakha]> create table depositor("vishakha umale",21000);ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"vishakha umale",21000)' at line 1
MariaDB [vishakha]> insert into depositor values("vishakha umale",21000);
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [vishakha]> insert into depositor values("ghjhjk hg",31000);
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> insert into depositor values("uhiyierjk hg",81000);
Query OK, 1 row affected, 1 warning (0.19 sec)

MariaDB [vishakha]> insert into depositor values("eiruoyir eqewt",657000);
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [vishakha]> insert into depositor values("tayal eqewt",857000);
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [vishakha]> insert into depositor values("payal eqewt",557000);
Query OK, 1 row affected, 1 warning (0.22 sec)

MariaDB [vishakha]> select * from depositor;;
+------------+--------+
| cust_nm    | acc_no |
+------------+--------+
| vishakha u |  21000 |
| ghjhjk hg  |  31000 |
| uhiyierjk  |  81000 |
| eiruoyir e | 657000 |
| tayal eqew | 857000 |
| payal eqew | 557000 |
+------------+--------+
6 rows in set (0.00 sec)

ERROR: No query specified

MariaDB [vishakha]> select * from Account;
+------------+-------+----------+
| acc_no     | br_nm | bal      |
+------------+-------+----------+
| 2147483647 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      37600 | icic  | 10000.00 |
|       7800 | icic  | 40000.00 |
|      45800 | idbi  | 80000.00 |
|      45800 | idbi  | 10000.00 |
|      85800 | idbi  | 10000.00 |
|      25800 | rbi   | 30000.00 |
+------------+-------+----------+
9 rows in set (0.00 sec)

MariaDB [vishakha]> select * from Account;
+------------+-------+----------+
| acc_no     | br_nm | bal      |
+------------+-------+----------+
| 2147483647 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      37600 | icic  | 10000.00 |
|       7800 | icic  | 40000.00 |
|      45800 | idbi  | 80000.00 |
|      45800 | idbi  | 10000.00 |
|      85800 | idbi  | 10000.00 |
|      25800 | rbi   | 30000.00 |
+------------+-------+----------+
9 rows in set (0.00 sec)

MariaDB [vishakha]> select * from branch;
Empty set (0.00 sec)

MariaDB [vishakha]> select * from customer;
Empty set (0.00 sec)

MariaDB [vishakha]> select * from depositor;
+------------+--------+
| cust_nm    | acc_no |
+------------+--------+
| vishakha u |  21000 |
| ghjhjk hg  |  31000 |
| uhiyierjk  |  81000 |
| eiruoyir e | 657000 |
| tayal eqew | 857000 |
| payal eqew | 557000 |
+------------+--------+
6 rows in set (0.00 sec)

MariaDB [vishakha]> select * from loan;
+---------+-------+---------+
| loan_no | br_nm | amt     |
+---------+-------+---------+
| 1000000 | sbi   | 8000.00 |
| 2000000 | sbi   | 7000.00 |
| 3000000 | icici | 3000.00 |
| 4000000 | rbi   | 5000.00 |
+---------+-------+---------+
4 rows in set (0.00 sec)

MariaDB [vishakha]> select * from borrower;
+------------+------------+
| cust_nm    | loan_no    |
+------------+------------+
| Sankalp XY | 1000000000 |
| ABCd EFG   |   20000000 |
| hgds       |   50000000 |
| hhhh       |   85000000 |
| jjjj       |    5600000 |
+------------+------------+
5 rows in set (0.00 sec)

MariaDB [vishakha]> create table branch("icici","PUNE",100000);ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"icici","PUNE",100000)' at line 1
MariaDB [vishakha]> insert into branch values("icici","PUNE",100000);
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> insert into branch values("rbi","PUNE",200000);
Query OK, 1 row affected (0.02 sec)

MariaDB [vishakha]> insert into branch values("icici","PUNE",300000);
Query OK, 1 row affected (0.02 sec)

MariaDB [vishakha]> insert into branch values("icici","AKOLA",300000);
Query OK, 1 row affected (0.02 sec)

MariaDB [vishakha]> insert into branch values("rbi","MUMBAI",400000);
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> insert into branch values("rbi","MUMBAI",500000);
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> insert into branch values("rbi","MUMBAI",600000);
Query OK, 1 row affected (0.02 sec)

MariaDB [vishakha]> create table customer("vishakha","kondhva road","PUNE");ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"vishakha","kondhva road","PUNE")' at line 1
MariaDB [vishakha]> insert into  customer values("vishakha","kondhva road","PUNE");
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> select * from customer;
+----------+--------------+---------+
| cust_nm  | cust_street  | cust_ct |
+----------+--------------+---------+
| vishakha | kondhva road | PUNE    |
+----------+--------------+---------+
1 row in set (0.00 sec)

MariaDB [vishakha]> insert into  customer values("ritesh","katraj road","MUMBAI");
Query OK, 1 row affected (0.02 sec)

MariaDB [vishakha]> insert into  customer values("abcds","abcd road","MUMBAI");Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> insert into  customer values("oocds","aaacd road","MUMBAI");
Query OK, 1 row affected (0.01 sec)

MariaDB [vishakha]> select * from customer;+----------+--------------+---------+
| cust_nm  | cust_street  | cust_ct |
+----------+--------------+---------+
| vishakha | kondhva road | PUNE    |
| ritesh   | katraj road  | MUMBAI  |
| abcds    | abcd road    | MUMBAI  |
| oocds    | aaacd road   | MUMBAI  |
+----------+--------------+---------+
4 rows in set (0.00 sec)

MariaDB [vishakha]> select * from Account;
+------------+-------+----------+
| acc_no     | br_nm | bal      |
+------------+-------+----------+
| 2147483647 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      37600 | icic  | 10000.00 |
|       7800 | icic  | 40000.00 |
|      45800 | idbi  | 80000.00 |
|      45800 | idbi  | 10000.00 |
|      85800 | idbi  | 10000.00 |
|      25800 | rbi   | 30000.00 |
+------------+-------+----------+
9 rows in set (0.00 sec)

MariaDB [vishakha]> select * from branch;
+-------+--------+--------+
| br_nm | br_ct  | assets |
+-------+--------+--------+
| icici | PUNE   | 100000 |
| rbi   | PUNE   | 200000 |
| icici | PUNE   | 300000 |
| icici | AKOLA  | 300000 |
| rbi   | MUMBAI | 400000 |
| rbi   | MUMBAI | 500000 |
| rbi   | MUMBAI | 600000 |
+-------+--------+--------+
7 rows in set (0.00 sec)

MariaDB [vishakha]> select * from Account;
+------------+-------+----------+
| acc_no     | br_nm | bal      |
+------------+-------+----------+
| 2147483647 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      37600 | icic  | 10000.00 |
|       7800 | icic  | 40000.00 |
|      45800 | idbi  | 80000.00 |
|      45800 | idbi  | 10000.00 |
|      85800 | idbi  | 10000.00 |
|      25800 | rbi   | 30000.00 |
+------------+-------+----------+
9 rows in set (0.00 sec)

MariaDB [vishakha]> select * from depositor;
+------------+--------+
| cust_nm    | acc_no |
+------------+--------+
| vishakha u |  21000 |
| ghjhjk hg  |  31000 |
| uhiyierjk  |  81000 |
| eiruoyir e | 657000 |
| tayal eqew | 857000 |
| payal eqew | 557000 |
+------------+--------+
6 rows in set (0.00 sec)

MariaDB [vishakha]> select * from loan;
+---------+-------+---------+
| loan_no | br_nm | amt     |
+---------+-------+---------+
| 1000000 | sbi   | 8000.00 |
| 2000000 | sbi   | 7000.00 |
| 3000000 | icici | 3000.00 |
| 4000000 | rbi   | 5000.00 |
+---------+-------+---------+
4 rows in set (0.00 sec)

MariaDB [vishakha]> select * from borrower;
+------------+------------+
| cust_nm    | loan_no    |
+------------+------------+
| Sankalp XY | 1000000000 |
| ABCd EFG   |   20000000 |
| hgds       |   50000000 |
| hhhh       |   85000000 |
| jjjj       |    5600000 |
+------------+------------+
5 rows in set (0.00 sec)

MariaDB [vishakha]> select * from borrower;
+------------+------------+
| cust_nm    | loan_no    |
+------------+------------+
| Sankalp XY | 1000000000 |
| ABCd EFG   |   20000000 |
| hgds       |   50000000 |
| hhhh       |   85000000 |
| jjjj       |    5600000 |
+------------+------------+
5 rows in set (0.00 sec)

MariaDB [vishakha]> select * from loan;
+---------+-------+---------+
| loan_no | br_nm | amt     |
+---------+-------+---------+
| 1000000 | sbi   | 8000.00 |
| 2000000 | sbi   | 7000.00 |
| 3000000 | icici | 3000.00 |
| 4000000 | rbi   | 5000.00 |
+---------+-------+---------+
4 rows in set (0.01 sec)

MariaDB [vishakha]> select * from customer;
+----------+--------------+---------+
| cust_nm  | cust_street  | cust_ct |
+----------+--------------+---------+
| vishakha | kondhva road | PUNE    |
| ritesh   | katraj road  | MUMBAI  |
| abcds    | abcd road    | MUMBAI  |
| oocds    | aaacd road   | MUMBAI  |
+----------+--------------+---------+
4 rows in set (0.00 sec)

MariaDB [vishakha]> select cust_nm from customer;
+----------+
| cust_nm  |
+----------+
| vishakha |
| ritesh   |
| abcds    |
| oocds    |
+----------+
4 rows in set (0.00 sec)

MariaDB [vishakha]> select br_nm from loan;
+-------+
| br_nm |
+-------+
| sbi   |
| sbi   |
| icici |
| rbi   |
+-------+
4 rows in set (0.00 sec)

MariaDB [vishakha]> select sum(br_nm) from loan;
+------------+
| sum(br_nm) |
+------------+
|          0 |
+------------+
1 row in set, 4 warnings (0.02 sec)

MariaDB [vishakha]> select count(br_nm) from loan;
+--------------+
| count(br_nm) |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

MariaDB [vishakha]> select * from loan;
+---------+-------+---------+
| loan_no | br_nm | amt     |
+---------+-------+---------+
| 1000000 | sbi   | 8000.00 |
| 2000000 | sbi   | 7000.00 |
| 3000000 | icici | 3000.00 |
| 4000000 | rbi   | 5000.00 |
+---------+-------+---------+
4 rows in set (0.00 sec)

MariaDB [vishakha]> select loan_no from customer
    -> ;
ERROR 1054 (42S22): Unknown column 'loan_no' in 'field list'
MariaDB [vishakha]> select loan_no from customer;
ERROR 1054 (42S22): Unknown column 'loan_no' in 'field list'
MariaDB [vishakha]> select loan_no from loan;
+---------+
| loan_no |
+---------+
| 1000000 |
| 2000000 |
| 3000000 |
| 4000000 |
+---------+
4 rows in set (0.01 sec)

MariaDB [vishakha]> select loan_no from loan where branch="akurdi";
ERROR 1054 (42S22): Unknown column 'branch' in 'where clause'
MariaDB [vishakha]> select loan_no from loan where loan_br="akurdi";
ERROR 1054 (42S22): Unknown column 'loan_br' in 'where clause'
MariaDB [vishakha]> select loan_no from loan where br_nm="akurdi";
Empty set (0.00 sec)

MariaDB [vishakha]> insert into loan values(100000,"akurdi",10000);
Query OK, 1 row affected (0.02 sec)

MariaDB [vishakha]> insert into loan values(100000,"nigdi",20000);
Query OK, 1 row affected (0.02 sec)

MariaDB [vishakha]> select * from depositer;
ERROR 1146 (42S02): Table 'vishakha.depositer' doesn't exist
MariaDB [vishakha]> select * from depositor;
+------------+--------+
| cust_nm    | acc_no |
+------------+--------+
| vishakha u |  21000 |
| ghjhjk hg  |  31000 |
| uhiyierjk  |  81000 |
| eiruoyir e | 657000 |
| tayal eqew | 857000 |
| payal eqew | 557000 |
+------------+--------+
6 rows in set (0.00 sec)

MariaDB [vishakha]> select * from depositer;
ERROR 1146 (42S02): Table 'vishakha.depositer' doesn't exist
MariaDB [vishakha]> select * from depositor;
+------------+--------+
| cust_nm    | acc_no |
+------------+--------+
| vishakha u |  21000 |
| ghjhjk hg  |  31000 |
| uhiyierjk  |  81000 |
| eiruoyir e | 657000 |
| tayal eqew | 857000 |
| payal eqew | 557000 |
+------------+--------+
6 rows in set (0.00 sec)

MariaDB [vishakha]> select * from depositor;
+------------+--------+
| cust_nm    | acc_no |
+------------+--------+
| vishakha u |  21000 |
| ghjhjk hg  |  31000 |
| uhiyierjk  |  81000 |
| eiruoyir e | 657000 |
| tayal eqew | 857000 |
| payal eqew | 557000 |
+------------+--------+
6 rows in set (0.00 sec)

MariaDB [vishakha]> select * from customer;
+----------+--------------+---------+
| cust_nm  | cust_street  | cust_ct |
+----------+--------------+---------+
| vishakha | kondhva road | PUNE    |
| ritesh   | katraj road  | MUMBAI  |
| abcds    | abcd road    | MUMBAI  |
| oocds    | aaacd road   | MUMBAI  |
+----------+--------------+---------+
4 rows in set (0.00 sec)

MariaDB [vishakha]> select * from Account;
+------------+-------+----------+
| acc_no     | br_nm | bal      |
+------------+-------+----------+
| 2147483647 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      37600 | icic  | 10000.00 |
|       7800 | icic  | 40000.00 |
|      45800 | idbi  | 80000.00 |
|      45800 | idbi  | 10000.00 |
|      85800 | idbi  | 10000.00 |
|      25800 | rbi   | 30000.00 |
+------------+-------+----------+
9 rows in set (0.01 sec)

MariaDB [vishakha]> select br_nm from Account
    -> where bal>12000;
+-------+
| br_nm |
+-------+
| icic  |
| idbi  |
| rbi   |
+-------+
3 rows in set (0.00 sec)

MariaDB [vishakha]> select br_nm,avg(bal) from Account where bal>12000;
+-------+--------------+
| br_nm | avg(bal)     |
+-------+--------------+
| icic  | 50000.000000 |
+-------+--------------+
1 row in set (0.00 sec)

MariaDB [vishakha]> select * from customer;
+----------+--------------+---------+
| cust_nm  | cust_street  | cust_ct |
+----------+--------------+---------+
| vishakha | kondhva road | PUNE    |
| ritesh   | katraj road  | MUMBAI  |
| abcds    | abcd road    | MUMBAI  |
| oocds    | aaacd road   | MUMBAI  |
+----------+--------------+---------+
4 rows in set (0.00 sec)

MariaDB [vishakha]> select count(cust_nm) from customer;
+----------------+
| count(cust_nm) |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

MariaDB [vishakha]> select * from loan;
+---------+--------+----------+
| loan_no | br_nm  | amt      |
+---------+--------+----------+
| 1000000 | sbi    |  8000.00 |
| 2000000 | sbi    |  7000.00 |
| 3000000 | icici  |  3000.00 |
| 4000000 | rbi    |  5000.00 |
|  100000 | akurdi | 10000.00 |
|  100000 | nigdi  | 20000.00 |
+---------+--------+----------+
6 rows in set (0.00 sec)

MariaDB [vishakha]> select sum(amt) from loan;
+----------+
| sum(amt) |
+----------+
| 53000.00 |
+----------+
1 row in set (0.00 sec)

MariaDB [vishakha]> delete from loan where amt between(1300,1500);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
MariaDB [vishakha]> delete from loan where amt between  1300 and1500;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'and1500' at line 1
MariaDB [vishakha]> delete from loan where amt between  1300 and 1500;
Query OK, 0 rows affected (0.00 sec)

MariaDB [vishakha]> select * from loan;
+---------+--------+----------+
| loan_no | br_nm  | amt      |
+---------+--------+----------+
| 1000000 | sbi    |  8000.00 |
| 2000000 | sbi    |  7000.00 |
| 3000000 | icici  |  3000.00 |
| 4000000 | rbi    |  5000.00 |
|  100000 | akurdi | 10000.00 |
|  100000 | nigdi  | 20000.00 |
+---------+--------+----------+
6 rows in set (0.00 sec)

MariaDB [vishakha]> delete from loan where br_nm="nigdi";;
Query OK, 1 row affected (0.01 sec)

ERROR: No query specified

MariaDB [vishakha]> select * from loan;
+---------+--------+----------+
| loan_no | br_nm  | amt      |
+---------+--------+----------+
| 1000000 | sbi    |  8000.00 |
| 2000000 | sbi    |  7000.00 |
| 3000000 | icici  |  3000.00 |
| 4000000 | rbi    |  5000.00 |
|  100000 | akurdi | 10000.00 |
+---------+--------+----------+
5 rows in set (0.00 sec)
 
MariaDB [vishakha]> select * from depositor;;
+------------+--------+
| cust_nm    | acc_no |
+------------+--------+
| vishakha u |  21000 |
| ghjhjk hg  |  31000 |
| uhiyierjk  |  81000 |
| eiruoyir e | 657000 |
| tayal eqew | 857000 |
| payal eqew | 557000 |
+------------+--------+
6 rows in set (0.00 sec)

ERROR: No query specified

MariaDB [vishakha]> select * from depositor union select * from branch;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
MariaDB [vishakha]> select * from branch;
+-------+--------+--------+
| br_nm | br_ct  | assets |
+-------+--------+--------+
| icici | PUNE   | 100000 |
| rbi   | PUNE   | 200000 |
| icici | PUNE   | 300000 |
| icici | AKOLA  | 300000 |
| rbi   | MUMBAI | 400000 |
| rbi   | MUMBAI | 500000 |
| rbi   | MUMBAI | 600000 |
+-------+--------+--------+
7 rows in set (0.00 sec)

MariaDB [vishakha]> select * from depositor;
+------------+--------+
| cust_nm    | acc_no |
+------------+--------+
| vishakha u |  21000 |
| ghjhjk hg  |  31000 |
| uhiyierjk  |  81000 |
| eiruoyir e | 657000 |
| tayal eqew | 857000 |
| payal eqew | 557000 |
+------------+--------+
6 rows in set (0.00 sec)

MariaDB [vishakha]> select cust_nm from depositor union select br_nm from branch;
+------------+
| cust_nm    |
+------------+
| vishakha u |
| ghjhjk hg  |
| uhiyierjk  |
| eiruoyir e |
| tayal eqew |
| payal eqew |
| icici      |
| rbi        |
+------------+
8 rows in set (0.00 sec)

MariaDB [vishakha]> select * from depositor;
+------------+--------+
| cust_nm    | acc_no |
+------------+--------+
| vishakha u |  21000 |
| ghjhjk hg  |  31000 |
| uhiyierjk  |  81000 |
| eiruoyir e | 657000 |
| tayal eqew | 857000 |
| payal eqew | 557000 |
+------------+--------+
6 rows in set (0.00 sec)

MariaDB [vishakha]> select count(cust_nm) from depositor;
+----------------+
| count(cust_nm) |
+----------------+
|              6 |
+----------------+
1 row in set (0.00 sec)

MariaDB [vishakha]> select count(cust_nm) as noofcust from depositor;

+----------+
| noofcust |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

MariaDB [vishakha]> select *n from branch;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'n from branch' at line 1
MariaDB [vishakha]> select * from branch;
+-------+--------+--------+
| br_nm | br_ct  | assets |
+-------+--------+--------+
| icici | PUNE   | 100000 |
| rbi   | PUNE   | 200000 |
| icici | PUNE   | 300000 |
| icici | AKOLA  | 300000 |
| rbi   | MUMBAI | 400000 |
| rbi   | MUMBAI | 500000 |
| rbi   | MUMBAI | 600000 |
+-------+--------+--------+
7 rows in set (0.00 sec)

MariaDB [vishakha]> select avg(assets) from branch;
+-------------+
| avg(assets) |
+-------------+
| 342857.1429 |
+-------------+
1 row in set (0.00 sec)


MariaDB [vishakha]> select * from branch;
+-------+--------+--------+
| br_nm | br_ct  | assets |
+-------+--------+--------+
| icici | PUNE   | 100000 |
| rbi   | PUNE   | 200000 |
| icici | PUNE   | 300000 |
| icici | AKOLA  | 300000 |
| rbi   | MUMBAI | 400000 |
| rbi   | MUMBAI | 500000 |
| rbi   | MUMBAI | 600000 |
+-------+--------+--------+
7 rows in set (0.00 sec)

MariaDB [vishakha]> select * from account;
ERROR 1146 (42S02): Table 'vishakha.account' doesn't exist
MariaDB [vishakha]> select * from Account;
+------------+-------+--------
--+
| acc_no     | br_nm | bal      |
+------------+-------+----------+
| 2147483647 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      37600 | icic  | 10000.00 |
|       7800 | icic  | 40000.00 |
|      45800 | idbi  | 80000.00 |
|      45800 | idbi  | 10000.00 |
|      85800 | idbi  | 10000.00 |
|      25800 | rbi   | 30000.00 |
+------------+-------+----------+
9 rows in set (0.00 sec)

MariaDB [vishakha]> select br_nm,avg(bal) from Account group by br_nm;

+-------+--------------+
| br_nm | avg(bal)     |
+-------+--------------+
| icic  | 25000.000000 |
| idbi  | 33333.333333 |
| rbi   | 30000.000000 |
| sbi   | 10000.000000 |
+-------+--------------+
4 rows in set (0.00 sec)
 

MariaDB [vishakha]> select * from Account;
+------------+-------+--------
--+
| acc_no     | br_nm | bal      |
+------------+-------+----------+
| 2147483647 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      37600 | icic  | 10000.00 |
|       7800 | icic  | 40000.00 |
|      45800 | idbi  | 80000.00 |
|      45800 | idbi  | 10000.00 |
|      85800 | idbi  | 10000.00 |
|      25800 | rbi   | 30000.00 |
+------------+-------+----------+
9 rows in set (0.00 sec)

MariaDB [vishakha]> select avg(bal) from account where  br_nm="akurdi";
ERROR 1146 (42S02): Table 'vishakha.account' doesn't exist

MariaDB [vishakha]> select avg(bal) from Account where  br_nm="akurdi";
+----------+
| avg(bal) |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

MariaDB [vishakha]> select * from Account;
+------------+-------+----------+
| acc_no     | br_nm | bal      |
+------------+-------+----------+
| 2147483647 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      32600 | sbi   | 10000.00 |
|      37600 | icic  | 10000.00 |
|       7800 | icic  | 40000.00 |
|      45800 | idbi  | 80000.00 |
|      45800 | idbi  | 10000.00 |
|      85800 | idbi  | 10000.00 |
|      25800 | rbi   | 30000.00 |
+------------+-------+----------+
9 rows in set (0.00 sec)

MariaDB [vishakha]> select * from loan;
+---------+--------+----------+
| loan_no | br_nm  | amt      |
+---------+--------+----------+
| 1000000 | sbi    |  8000.00 |
| 2000000 | sbi    |  7000.00 |
| 3000000 | icici  |  3000.00 |
| 4000000 | rbi    |  5000.00 |
|  100000 | akurdi | 10000.00 |
+---------+--------+----------+
5 rows in set (0.00 sec)
MariaDB [vishakha]> select acc_no from Account UNION select loan_no from loan;
+------------+
| acc_no     |
+------------+
| 2147483647 |
|      32600 |
|      37600 |
|       7800 |
|      45800 |
|      85800 |
|      25800 |
|    1000000 |
|    2000000 |
|    3000000 |
|    4000000 |
|     100000 |
+------------+
12 rows in set (0.00 sec)
 
select * from depositor;

+------------+--------+
| cust_nm    | acc_no |
+------------+--------+
| vishakha u |  21000 |
| ghjhjk hg  |  31000 |
| uhiyierjk  |  81000 |
| eiruoyir e | 657000 |
| tayal eqew | 857000 |
| payal eqew | 557000 |
+------------+--------+
6 rows in set (0.00 sec)
MariaDB [vishakha]> select * from borrower;
+------------+------------+
| cust_nm    | loan_no    |
+------------+------------+
| Sankalp XY | 1000000000 |
| ABCd EFG   |   20000000 |
| hgds       |   50000000 |
| hhhh       |   85000000 |
| jjjj       |    5600000 |
+------------+------------+
5 rows in set (0.00 sec)

MariaDB [vishakha]> select cust_nm from borrower;

+------------+
| cust_nm    |
+------------+
| Sankalp XY |
| ABCd EFG   |
| hgds       |
| hhhh       |
| jjjj       |
+------------+
5 rows in set (0.00 sec)
 
 

Comments

Post a Comment

Popular posts from this blog

Naming Convention

  Naming Convention : A naming convention is  a convention for naming things: Please find below the table for naming conventions: Naming Convention Format Example Camel Case camelCase 🐪aBcD Kebab Case kebab-case 🍢a-b-c-d Snake Case snake_case 🐍a_b_c_d Pascal Case PascalCase 🧑‍🦳AbCd Flat Case flatcase 📏abcd Upper Flat Case UPPERFLATCASE ABCD Screaming Snake Case SCREAMING_SNAKE_CASE 🐍A_B_C_D Camel Snake Case camel_Snake_Case 🐪🐍ab_Cd Pascal Snake Case Pascal_Snake_Case Ab_Cd Train Case Train-Case 🚃Ab-Cd Cobol Case COBOL-CASE 🍢AB-CD
Post a Comment
Read more
How to run lex yacc programs on win 10 In short: For Lex steps to run: lex lex7.l gcc lex.yy.c a     For YACC steps to run: lex simple_compound.l yacc -d simple_compound.y gcc lex.yy.c y.tab.c a                          A.Lex 1. download flex for windows http://flex-windows-lex-and-yacc.software.informer.com/2.5/ 2. install it locatin:  C:\Flex Windows\ 3.open cmd 4.You need to change location as follows:  cd C:\Flex Windows\EditPlusPortable 5. Steps to run : lex lex7.l gcc lex.yy.c a Output : Word Count : 13 Char Count : 80 Line Count : 7 codes : lex7.l %{ /* *Program to count no of characters, words and lines */ #include<stdio.h> #include<string.h> int c=0,w=0,l=0; %} %% [\t ]+              /* ignore whitespace */ [a-zA-z]+             {w=w+1; c=c+ yyleng;} [\n ]                           {l=l+1;} ['{','}''(',')',.,",;]          {c=c+1;} %% int main() {     yyin= fopen("file1.txt&qu
1 comment
Read more

Add items in shopping cart using ArrayList

 Add items in shopping cart: We are using ArrayList Code: import java.util.*; public class Main {  public static void main (String[]args)   {     ArrayList < String > items = new ArrayList < String > ();     Scanner sc = new Scanner (System.in);       items.add ("folder");       items.add ("flower");       items.add ("Car");       items.add ("Bottle");       items.add ("Mask");       items.add ("Box");       System.out.println ("Shopping card items");       System.out.print (items);       System.out.println ("Enter item which you want:");       String rmv = sc.nextLine ();       items.remove (rmv);       System.out.println ("Shopping card items");       System.out.print (items);   } } Output: Shopping card items                                                                                                              [folder, flower, Car, Bottle, Mask, Box]Enter item which you want: 
Post a Comment
Read more