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 <-

create database vishakha; <-

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;
+------------+-------+--------

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;

Vishakha Umale

Search This Blog