0% found this document useful (0 votes)

6 views38 pages

Programs & Index

The document provides a comprehensive guide on various MySQL operations including creating databases and tables, performing insertions, deletions, and selections, as well as executing set operations like union and intersection. It also covers aggregate functions such as sum, average, minimum, maximum, and count, along with join operations between tables. Each section includes example queries and their expected outputs, making it a practical reference for MySQL users.

Uploaded by

Showkath Ali

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as DOCX, PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

6 views38 pages

Programs & Index

Uploaded by

Showkath Ali

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as DOCX, PDF, TXT or read online on Scribd

INDEX

[Link] DATE TITLE Page NO SIGN

1 10-01-2023 Mysql operations

2 20-01-2023 Set operations

3 28-01-2023 Aggregate function

4 04-02-2023 Join operations

5 11-02-2023 Nested sub-queries

6 27-02-2023 Views

7 06-03-2023 String operations

8 13-03-2023 BANKING DATABASE

Mysql operations
Creating a Database:
mysql> create database store;
Query OK, 1 row affected (0.02 sec)

mysql> use store;

Database changed

Creating a Table (using primary key):

mysql> create table Employee(Emp_id int, Emp_name varchar(10), Age int,
primary key (Emp_id));
Query Ok, 0 rows affected(0.13 sec)

mysql> desc employee;

+---------------+---------------+-------+-------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------+-------+-------+---------+-------+
| Emp_id | int(11) | NO | PRI | NULL | |
| Emp_name | varchar(10) | YES | | NULL | |
| Age | int(11) | YES | | NULL | |
+--------------+---------------+-------+-------+---------+-------+

3 rows in set (0.00 sec)

Altering the Table Structure:

mysql> Alter table Employee add Salary decimal(10,2);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> Alter table Employee modify Emp_name varchar(35);
Query OK, 0 rows affected (0.11 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(10) | YES | | NULL | |
| Age | int(11) | YES | | NULL | |
| Salary | decimal(10,2)| YES | | NULL | |
+--------------+---------------+-------+-------+---------+-------+
4 rows in set (0.00 sec)

Inserting a values:

mysql> insert into Employee values(301, 'Ajitha', 24, 15000);

Query OK, 1 row affected (0.00 sec)
//(Error Statement)
mysql> insert into Employee values(301, 'Deepthi', 23, 20000);
ERROR 1062 (23000): Duplicate entry '301' for key 'PRIMARY'

mysql> insert into Employee values(302, 'Deepthi', 23, 20000), (303, 'Farheen', 23, 18000),
(304, 'Gowri', 25, 16000);
Query OK, 3 row affected (0.00 sec)

mysql> select *from Employee;

+----------+--------------+--------+-------------+
| Emp_id | Emp_name | Age | Salary |
+----------+--------------+--------+-------------+
| 301 | Ajitha | 24 | 15000.00 |
| 302 | Deepthi | 23 | 20000.00 |
| 303 | Farheen | 23 | 18000.00 |
| 304 | Gowri | 25 | 16000.00 |
+---------+--------------+---------+------------+
4 rows in set (0.00 sec)

Delete Queries:
mysql> delete from Employee where Emp_id=303;
Query OK, 1 row affected (0.00 sec)

mysql> select *From employee;

+--------+-----------------+----------+
| Emp_id | Emp_name | Salary |
+--------+-----------------+----------+
| 301 | Ajitha | 15000.00 |
| 302 | Deepthi | 20000.00 |
| 304 | Gowri | 16000.00 |
+--------+-----------------+------------+
3 rows in set (0.01 sec)

Selection Queries:
mysql> select Emp_name from employee where salary=16000;
+----------+
| Emp_name |
+----------+
| Gowri |
+----------+
1 row in set (0.02 sec)

mysql> select *from employee where emp_id=302;

+----------+--------------+-----------+
| Emp_id | Emp_name | Salary |
+----------+---------------+----------+
| 302 | Deepthi | 20000.00 |
+---------+---------------+------------+
1 row in set (0.00 sec)
mysql> select Emp_id, Salary from employee;
+--------+----------+
| Emp_id | Salary |
+--------+----------+
| 301 | 15000.00 |
| 302 | 20000.00 |
| 304 | 16000.00 |
+--------+----------+
3 rows in set (0.00 sec)

Drop the Table:

Mysql> drop table Employee;
Query Ok, 0 rows affected (0.00 sec)

Mysql> select *from Employee;

ERROR 1146 (42S02):Table ‘ Store. Employee’ doesn’t exists

Inserting a value for first table (Aathibakery):

mysql> insert into Aathibakery values(1, 'Cake', 100), (2, 'Burger', 40), (3, 'Chips', 60);
Query OK, 3 row affected (0.00 sec)

Select the First table (Aathibakery):

mysql> select *From Aathibakery;
+------+--------+------+
| Sno | Items | Cost |
+------+--------+------+
| 1 | Cake | 100 |
| 2 | Burger | 40 |
| 3 | Chips | 60 |
+------+--------+------+
3 rows in set (0.00 sec)

Creating a Second Table (Balubakery):

Inserting a value for Second table (Balu bakery):

mysql> insert into balubakery values(1, 'Cake', 100), (2, 'Pudding', 25), (3, 'Chips', 60),
(4, 'Waffers', 40);
Query OK, 1 row affected (0.00 sec)

Select the Second table (Balubakery):

mysql> select *from Balubakery;
+------+---------+------+
| Sno | Items | Cost |
+------+---------+------+
| 1 | Cake | 100 |
| 2 | Pudding | 25 |
| 3 | Chips | 60 |
| 4 | Waffers | 40 |
+------+---------+------+
4 rows in set (0.00 sec)

Union Operation Query:

mysql> select *from aathibakery union select *from balubakery;
+------+---------+------+

| Sno | Items | Cost |

+------+---------+------+

| 1 | Cake | 100 |

| 2 | Burger | 40 |

| 3 | Chips | 60 |

| 2 | Pudding | 25 |

| 4 | Waffers | 40 |

+------+---------+------+

5 rows in set (0.00 sec)

Union all Operation Query:

mysql> select *from aathibakery union all select *from balubakery;

+------+---------+------+

| Sno | Items | Cost |

+------+---------+------+

| 1 | Cake | 100 |

| 2 | Burger | 40 |

| 3 | Chips | 60 |

| 1 | Cake | 100 |

| 2 | Pudding | 25 |

| 3 | Chips | 60 |

| 4 | Waffers | 40 |

+------+---------+------+
7 rows in set (0.00 sec)

Intersect Operation Query:

mysql> select *from aathibakery intersect select *from balubakery;
+------+-------+------+
| Sno | Items | Cost |
+------+-------+------+
| 1 | Cake | 100 |
| 3 | Chips | 60 |
+------+-------+------+
2 rows in set (0.00 sec)

Intersect all Operation Query:

mysql> select from aathibakery intersect all select from balubakery;

+------+----------+--------+
| Sno | Items | Cost |
+------+----------+--------+
| 1 | Cake | 100 |
| 3 | Chips | 60 |
| 1 | Cake | 100 |
| 3 | Chips | 60 |
+------+---------+---------+
2 rows in set (0.00 sec)
Aggregate function
Create a Database(Aggregate):
mysql> create database aggregate;
Query OK, 1 row affected (0.00 sec)
mysql> use aggregate;
Database changed

Create a Table(Student)
mysql> create table student (Sid int, Sname varchar(10), Tam_Mark int, Eng_Mark int,
Maj_Mark int);
Query OK, 0 rows affected (0.07 sec)
mysql> desc student;
+----------------+---------------+-------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+-------+-----+-----------+-------+
| Sid | int(11) | YES | | NULL | |
| Sname | varchar(10) | YES | | NULL | |
| Tam_Mark | int(11) | YES | | NULL | |
| End_Mark | int(11) | YES | | NULL | |
| Maj_Mark | int(11) | YES | | NULL | |
+---------------+----------------+-------+-----+-----------+-------+
5 rows in set (0.00 sec)

Inserting a value for Student Table:

mysql> insert into student values(101, 'Akalya', 89,79,90);
Query OK, 1rows affected (0.00 sec)
mysql> insert into student values(102, 'Brindha', 80,79,98);
Query OK, 1 rows affected (0.00 sec)
mysql> insert into student values(103, 'Chitra', 56,87,65);
Query OK, 1 rows affected (0.00 sec)
mysql> insert into student values(104, 'Raji', 87, 67, 90);
Query OK, 1 rows affected (0.00 sec)
Select a table Student:
mysql> select *from student;
+------+-------------+--------------+-------------+-------------+
| Sid | Sname | Tam_Mark | Eng_Mark | Maj_Mark |
+------+------------+--------------+-------------+--------------+
| 101 | Akalya | 89 | 79 | 90 |
| 102 | Brindha | 80 | 79 | 98 |
| 103 | Chitra | 56 | 87 | 65 |
| 104 | Raji | 87 | 67 | 90 |
+------+-----------+--------------+--------------+--------------+
4 rows in set (0.00 sec)

Total (Sum):
mysql> select sum(Tam_Mark) from Student;
+---------------------+
| sum(Tam_Mark) |
+---------------------+
| 312 |
+---------------------+
1 row in set (0.00 sec)

Average(avg):
mysql> select avg(Eng_Mark) from student;
+--------------------+
| avg(Eng_Mark) |
+--------------------+
| 78.0000 |
+--------------------+
1 row in set (0.00 sec)

Minimum(min):
mysql> select min(Maj_Mark) from student;
+--------------------+
| min(Maj_Mark) |
+--------------------+
| 65 |
+--------------------+
1 row in set (0.00 sec)
Maximum(max):
mysql> select max(Maj_Mark) from student;
+---------------------+
| max(Maj_Mark) |
+---------------------+
| 98 |
+---------------------+
1 row in set (0.00 sec)

Count:
mysql> select count(sname) from student;
+-----------------+
| count(sname) |
+-----------------+
| 4 |
+-----------------+
1 row in set (0.00 sec)

Group by Clause & Having Clause:

Join operations
Create a Database(Aggregate):
mysql> create database laptop;

Query OK, 1 row affected (0.00 sec)

mysql> use laptop;

Database changed

Create a First Table(brand):

mysql> create table brand(Id int, Brand varchar(10));

Query OK, 0 rows affected (0.07 sec)

Insert a value for brand table:

mysql> insert into brand values(1, 'Dell');

Query OK, 1 row affected (0.00 sec)

mysql> insert into brand values(2, 'HP');

Query OK, 1 row affected (0.00 sec)

mysql> insert into brand values(3, 'Apple');

Query OK, 1 row affected (0.00 sec)

Select a brand table:

mysql> select *from brand;

+------+-------+

| Id | Brand |

+------+-------+

| 1 | Dell |

| 2 | HP |

| 3 | Apple |

+------+-------+

3 rows in set (0.00 sec)

Create a Second Table(brand):

mysql> create table color(Id int, color varchar(10));

Query OK, 0 rows affected (0.08 sec)

Insert a value for color table:

mysql> insert into color values(1, 'Black');
Query OK, 1 row affected (0.00 sec)

mysql> insert into color values(2, 'Pink');

Query OK, 1 row affected (0.00 sec)

mysql> insert into color values(4, 'Blue');

Query OK, 1 row affected (0.00 sec)

Select a color table :

mysql> select *from color;

+------+-------+

| Id | color |

+------+-------+

| 1 | Black |

| 2 | Pink |

| 4 | Blue |

+------+-------+

3 rows in set (0.00 sec)

Natural join:
mysql> select *from brand natural join color;

+------+-------+-------+

| Id | Brand | color |

+------+-------+-------+

| 1 | Dell | Black |

| 2 | HP | Pink |

+------+-------+-------+

2 rows in set (0.00 sec)

Inner join:
mysql> select * from brand inner join color on [Link]= [Link];
+------+-------+------+-------+
| Id | Brand | Id | color |
+------+-------+------+-------+
| 1 | Dell | 1 | Black |
| 2 | HP | 2 | Pink |
+------+-------+------+-------+
2 rows in set (0.00 sec)

Left Outer join:

mysql> select * from brand left join color on [Link]= [Link];

+------+-------+------+-------+
| Id | Brand | Id | color |
+------+-------+------+-------+
| 1 | Dell | 1 | Black |
| 2 | HP | 2 | Pink |
| 3 | Apple | NULL | NULL |
+------+-------+------+-------+
3 rows in set (0.00 sec)

Right Outer join:

mysql> select * from brand right join color on [Link]= [Link];

+------+-------+------+-------+

| Id | Brand | Id | color |

+------+-------+------+-------+

| 1 | Dell | 1 | Black |

| 2 | HP | 2 | Pink |

| NULL | NULL | 4 | Blue |

+------+-------+------+-------+

3 rows in set (0.00 sec)

Full Outer join:

mysql> select *from brand full join color;

+------+-------+------+-------+

| Id | Brand | Id | color |
+------+-------+------+-------+

| 1 | Dell | 1 | Black |

| 2 | HP | 1 | Black |

| 3 | Apple | 1 | Black |

| 1 | Dell | 2 | Pink |

| 2 | HP | 2 | Pink |

| 3 | Apple | 2 | Pink |

| 1 | Dell | 4 | Blue |

| 2 | HP | 4 | Blue |

| 3 | Apple | 4 | Blue |

+------+-------+------+-------+

9 rows in set (0.00 sec)

Join Conditions:
mysql> select *from brand natural join color where [Link]='dell';

+------+-------+-------+
| Id | Brand | color |
+------+-------+-------+
| 1 | Dell | Black |
+------+-------+-------+
1 row in set (0.00 sec)

mysql> select *from brand natural join color where [Link]='pink';

+------+-------+-------+
| Id | Brand | color |
+------+-------+-------+
| 2 | HP | Pink |
+------+-------+-------+
1 row in set (0.00 sec)

BANKING DATABASE
mysql> create database banking;
Query OK, 1 row affected (0.04 sec)

mysql> use banking;

Database changed

mysql> create table branch(branch_name char(23),branch_city char(23),assets int,

primary key(branch_name));

Query OK, 0 rows affected (0.25 sec)

mysql> describe branch;

+-----------------+-----------+--------+--------+-----------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------------+-----------+--------+--------+-----------+-------+

| branch_name | char(23) | NO | PRI | | |

| branch_city | char(23) | YES | | NULL | |

| assets | int(11) | YES | | NULL | |

+----------------+------------+---------+-------+----------+--------+

3 rows in set (0.03 sec)

mysql> create table account(accno int,branch_name char(23),balance int,primary key(accno),

foreign key(branch_name) references branch (branch_name));

Query OK, 0 rows affected (0.23 sec)

mysql> describe account;

+-----------------+-----------+--------+-------+----------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------------+-----------+--------+-------+----------+-------+

| accno | int(11) | NO | PRI |0 | |

| branch_name | char(23) | YES | MUL | NULL | |

| balance | int(11) | YES | | NULL | |

+-----------------+-----------+--------+--------+----------+-------+

3 rows in set (0.00 sec)

mysql> create table customer(customer_name char(23),customer_street char(23),customer_city char(23),

primary key(customer_name));

Query OK, 0 rows affected (0.16 sec)

mysql> describe customer;

+--------------------+------------+---------+-------+-----------+--------+

| Field | Type | Null | Key | Default | Extra |

+--------------------+------------+---------+----- +-----------+--------+

| customer_name | char(23) | NO | PRI | | |

| customer_street | char(23) | YES | | NULL | |

| customer_city | char(23) | YES | | NULL | |

+--------------------+------------+----------+-------+-----------+--------+

3 rows in set (0.02 sec)

mysql> create table despositer(customer_name char(24), accno int, foreign key(customer_name)

references customer(customer_name), foreign key(accno)references account(accno));

Query OK, 0 rows affected (0.33 sec)

mysql> describe despositer;

+-------------------+-----------+--------+--------+-----------+--------+

| Field | Type | Null | Key | Default | Extra |

+-------------------+-----------+--------+--------+-----------+--------+

| customer_name | char(24) | YES | MUL | NULL | |

| accno | int(11) | YES | MUL | NULL | |

+-------------------+-----------+--------+--------+------------+--------+

2 rows in set (0.00 sec)

mysql> create table loan(loan_number int,branch_name char(23),amouny real,

primary key(loan_number),foreign key(branch_name)references branch(branch_name));

Query OK, 0 rows affected (0.22 sec)

mysql> describe loan;

+----------------+------------+--------+-------+---------+--------+

| Field | Type | Null | Key | Default | Extra |

+----------------+------------+--------+-------+----------+-------+
| loan_number | int(11) |NO | PRI |0 | |

| branch_name | char(23) | YES | MUL | NULL | |

| amount | double | YES | | NULL | |

+-----------------+-----------+--------+-------+----------+--------+

3 rows in set (0.00 sec)

mysql> create table borrower(customer_name char(23),loan_number int,foreign key (customer_name)

references customer(customer_name),foreign key(loan_number)references loan(loan_number));

Query OK, 0 rows affected (0.31 sec)

mysql> describe borrower;

+-------------------+------------+--------+-------+----------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------------------+------------+--------+-------+----------+-------+

| customer_name | char(23) | YES | MUL | NULL | |

| loan_number | int(11) | YES | MUL | NULL | |

+--------------------+-----------+--------+-------+----------+--------+

2 rows in set (0.00 sec)

mysql> insert into branch values('iob','chennai',650000),('kvb','karur',500000),

('lvb','salem',700000),('pnb','karur',800000),('sbi','karur',900000);

Query OK, 5 rows affected (0.05 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from branch;

+----------------+---------------+----------+

| branch_name | branch_city | assets |

+----------------+--------------- +---------+
| iob | chennai | 650000 |

| kvb | karur | 500000 |

| lvb | salem | 700000 |

| pnb | karur | 800000 |

| sbi | karur | 900000 |

+----------------+---------------+-----------+

5 rows in set (0.00 sec)

mysql> insert into account values(201,'kvb',250000),(202,'sbi',350000),(203,'sbi',750000),

(204,'lvb',100000),(205,'iob',150000);

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from account;

+---------+----------------+----------+

| accno | branch_name | balance |

+---------+----------------+----------+

| 201 | kvb | 250000 |

| 202 | sbi | 350000 |

| 203 | sbi | 750000 |

| 204 | lvb | 100000 |

| 205 | iob | 150000 |

+---------+-------------+--------------+

5 rows in set (0.00 sec)

mysql> insert into customer values('anitha','greenstreet','cbe'),('joe','carstreet','delhi'),

('john','newstreet','chennai'), ('priya','redstreet','karur'),('ramya','ironstreet','salem');

Query OK, 5 rows affected (0.02 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from customer;

+-------------------+--------------------+----------------+

| customer_name | customer_street | customer_city |

+-------------------+--------------------+----------------+
| anitha | greenstreet | cbe |

| joe | carstreet | delhi |

| john | newstreet | chennai |

| priya | redstreet | karur |

| ramya | ironstreet | salem |

+-------------------+---------------------+----------------+

5 rows in set (0.00 sec)

mysql> insert into despositer values('joe',201),('john',202),('priya',203),('ramya',204),('anitha',205);

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from despositer;

+-------------------+-------+

| customer_name | accno |

+-------------------+-------+

| joe | 201 |

| john | 202 |

| priya | 203 |

| ramya | 204 |

| anitha | 205 |

+-------------------+-------+

5 rows in set (0.00 sec)

mysql> insert into loan values(301,'lvb',50000),(302,'lvb',15500),(303,'pnb',10000), (304,'sbi',30000),

(305,'pnb',15000);

Query OK, 5 rows affected (0.02 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from loan;

+----------------+----------------+----------+

| loan_number | branch_name | amount |

+----------------+----------------+----------+

| 301 | lvb | 50000 |

| 302 | lvb | 15500 |

| 303 | pnb | 10000 |

| 304 | sbi | 30000 |

| 305 | pnb | 15000 |

+----------------+-----------------+---------+

5 rows in set (0.01 sec)

mysql> insert into borrower values('john',301),('priya',302),('joe',303),('anitha',304),('ramya',305);

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from borrower;

+-------------------+----------------+

| customer_name | loan_number |

+-------------------+----------------+

| john | 301 |

| priya | 302 |

| joe | 303 |

| anitha | 304 |

| ramya | 305 |

+-------------------+----------------+

5 rows in set (0.00 sec)

QUERIES
mysql> select customer_name from account a,despositer d

where [Link]=[Link] and branch_name='sbi' group by customer_name having count(*)>=1;

+-------------------+

| customer_name |

+-------------------+

| john |

| priya |

+-------------------+

2 rows in set (0.03 sec)

mysql> select distinct customer_name

from account a,despositer d

where [Link]=d..accno and branch_name in (select branch_name from branch where

branch_city='karur');

+-------------------+

| customer_name |

+-------------------+

| joe |

| john |

| priya |

+-------------------+

3 rows in set (0.00 sec)

mysql> delete from account where branch_name in(select branch_name from branch

where branch_city='cbe');

Query OK, 0 rows affected (0.01 sec)

mysql> select * from branch;

+-----------------+---------------+----------+

| branch_name | branch_city | assets |

+-----------------+--------------+-----------+

| iob | chennai | 650000 |

| kvb | karur | 500000 |

| lvb | salem | 700000 |

| pnb | karur | 800000 |

| sbi | karur | 900000 |

+-----------------+---------------+----------+

5 rows in set (0.00 sec)

REPORT GENERATION USING PHP
<?php

$db=mysql_connect("localhost","root","");

echo" Localhost connected";

$s=mysql_select_db("pg5",$db);

echo " Databaseconnected";

if($s)

echo" ";

echo "<center><fontface='monotype corsiva' size=10'>BANKING DATABASE

REPORT</center> ";

$sql="select distinct [Link],l.loan_number,b.branch_name,b.branch_city,

c.customer_name,c.customer_street,c.customer_city

from account a,loan l,branch b,despositer d,borrower b1,customer c

where b.branch_name=a.branch_name and [Link]=[Link] and

l.loan_number=b1.loan_number and b1.customer_name=d.customer_name and

d.customer_name=c.customer_name";

$res=mysql_query($sql);

echo"<table bgcolor='violet';fontcolor:'black' border='2' align='center'>";

echo "<tr>";

echo"<th>accno</th> <th>loan_number</th><th>branch_name</th><th>branch_city</th>

<th>customer_name</th><th>customer_street</th><th>customer_city</th>";

while ($dbfield=mysql_fetch_array($res))

echo "<tr>";

echo"<td>",$dbfield['accno'],"</td>";

echo"<td>",$dbfield['loan_number'],"</td>";

echo"<td>",$dbfield['branch_name'],"</td>";

echo"<td>",$dbfield['branch_city'],"</td>";
echo"<td>",$dbfield['customer_name'],"</td>";

echo"<td>",$dbfield['customer_street'],"</td>";

echo"<td>",$dbfield['customer_city'],"</td>";

print"</tr>";

mysql_close($db);

else

echo"not";

mysql_close($db);

?>
views
Create Database (Views):
mysql> create database views;

Query OK, 1 row affected (0.00 sec)

mysql> use views;

Database changed

Create a Table (Book):

mysql> create table Book (Book_Id int, Book_Name varchar(30), Author varchar(30), Price int);

Query OK, 0 rows affected (0.29 sec)

mysql> Desc Book;

Insert a value of Book table:

mysql> Insert into book values(2318, 'Programming in C', 'Balagurusamy', 385);

Query OK, 1 row affected (0.00 sec)

mysql> Insert into book values(2507, 'Unix Programming', 'Eric Raymond', 265);

Query OK, 1 row affected (0.00 sec)

mysql> Insert into book values(3412, 'Html EasySteps', 'Mike McGrath', 175);

Query OK, 1 row affected (0.00 sec)

mysql> Insert into book values(3814, 'Software Engineering', 'Lan Sommerville',299);

Query OK, 1 row affected (0.00 sec)

mysql> Insert into book values(2810, 'Java Programming', 'D.S. Malik', 349);

Query OK, 1 row affected (0.00 sec)

Select a table Book:

mysql> select *from book;

| 2507 | Unix Programming | Eric Raymond | 265 |

| 3412 | Html EasySteps | Mike McGrath | 175 |

| 3814 | Software Engineering | Lan Sommerville | 299 |

| 2810 | Java Programming | D.S. Malik | 349 |

+-----------+---------------------------+----------------------+-------+

5 rows in set (0.00 sec)

Create a View (V_Book):

mysql> create view V_Book as select Book_Name, Price from book;

Query OK, 0 rows affected (0.28 sec)

Select a View query:

mysql> select *from V_Book;

+----------------------------+-------+

| Book_Name | Price |

+---------------------------+-------+

| Programming in C | 385 |

| Unix Programming | 265 |

| Html EasySteps | 175 |

| Software Engineering | 299 |

| Java Programming | 349 |

+----------------------+-------+

5 rows in set (0.00 sec)

Update a view query:

mysql> update V_Book set price=260 where Book_name='Software Engineering';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select *from book;

| 2507 | Unix Programming | Eric Raymond | 265 |

| 3412 | Html EasySteps | Mike McGrath | 175 |

| 3814 | Software Engineering | Lan Sommerville | 260 |

| 2810 | Java Programming | D.S. Malik | 349 |

+-----------+---------------------------+----------------------+-------+

5 rows in set (0.00 sec)

Delete a view query:

mysql> delete from V_book where Book_name='Html EasySteps';

Query OK, 1 row affected (0.00 sec)

mysql> select *from book;

| 2507 | Unix Programming | Eric Raymond | 265 |

| 3814 | Software Engineering | Lan Sommerville | 260 |

| 2810 | Java Programming | D.S. Malik | 349 |

+-----------+---------------------------+----------------------+-------+

4 rows in set (0.00 sec)

String operations
Creating a Database (String):
mysql> create database String;
Query OK, 1 row affected (0.02 sec)

mysql> use String;

Database changed

Creating a Table Employee:

mysql> create a table Employee(Eid int, Ename varchar(10), Age int, Salary int);
Query Ok, 0 rows affected(0.13 sec)

Inserting a values for Employee Table:

mysql> insert into employee values(201, 'Dharshini', 24, 30000);

Query OK, 1 row affected (0.00 sec)
mysql> insert into employee values(202, 'Indhu', 22, 20000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employee values(203, 'Kavi', 25, 25000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employee values(204, 'Varsha', 23, 28000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employee values(205, 'Kamala', 22, 18000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into employee values(206, 'Jeni', 24, 25000);
Query OK, 1 row affected (0.00 sec)

Select Employee Table:

mysql> select *from employee;
+------+-----------+------+--------+
| Eid | Ename | Age | Salary |
+------+-----------+------+--------+
| 201 | Dharshini | 24 | 30000 |
| 202 | Indhu | 22 | 20000 |
| 203 | Kavi | 25 | 25000 |
| 204 | Varsha | 23 | 28000 |
| 205 | Kamala | 22 | 18000 |
| 206 | Jeni | 24 | 25000 |
+------+-----------+------+--------+
6 rows in set (0.00 sec)

String Operations using ‘ % ‘ :

mysql> select *from employee where ename like 'ka%';
+------+--------+------+--------+
| Eid | Ename | Age | Salary |
+------+--------+------+--------+
| 203 | Kavi | 25 | 25000 |
| 205 | Kamala | 22 | 18000 |
+------+--------+------+--------+
2 rows in set (0.00 sec)
mysql> select *from employee where ename like '%i';
+------+-----------+------+--------+
| Eid | Ename | Age | Salary |
+------+-----------+------+--------+
| 201 | Dharshini | 24 | 30000 |
| 203 | Kavi | 25 | 25000 |
| 206 | Jeni | 24 | 25000 |
+------+-----------+------+--------+
3 rows in set (0.00 sec)

String Operations using ‘ _ ’:

mysql> select *from employee where ename like '_a%';

+------+--------+------+--------+
| Eid | Ename | Age | Salary |
+------+--------+------+--------+
| 203 | Kavi | 25 | 25000 |
| 204 | Varsha | 23 | 28000 |
| 205 | Kamala | 22 | 18000 |
+------+--------+------+--------+
3 rows in set (0.00 sec)

Sorting Query(Ascending):
mysql> select ename, salary from employee order by ename asc;
+---------------+--------+
| ename | salary |
+---------------+--------+
| Dharshini | 30000 |
| Indhu | 20000 |
| Jeni | 25000 |
| Kamala | 18000 |
| Kavi | 25000 |
| Varsha | 28000 |
+---------------+--------+
6 rows in set (0.00 sec)

Sorting Query(Descending):
mysql> select ename, salary from employee order by salary desc;
+---------------+--------+
| ename | salary |
+---------------+--------+
| Dharshini | 30000 |
| Varsha | 28000 |
| Kavi | 25000 |
| Jeni | 25000 |
| Indhu | 20000 |
| Kamala | 18000 |
+--------------+--------+
6 rows in set (0.00 sec)

mysql> select ename, salary from employee order by ename desc;

+-----------+--------+
| ename | salary |
+-----------+--------+
| Varsha | 28000 |
| Kavi | 25000 |
| Kamala | 18000 |
| Jeni | 25000 |
| Indhu | 20000 |
| Dharshini | 30000 |
+-----------+--------+
6 rows in set (0.04 sec)

Nested Subqueries
Create a Database Information:
mysql> create database Information;

Query OK, 1 row affected (0.00 sec)

mysql> use Information;

Database changed

Create a table StoreInformation:

mysql> create table Store_information (store_name varchar(15), sales int, txn_date date);

Query OK, 0 rows affected (0.25 sec)

Insert a value for store information table:

mysql> insert into store_information values('Los Angeles', 1500, '2018-01-05');

Query OK, 1 row affected (0.00 sec)

mysql> insert into store_information values('San Diego', 250, '2018-01-07');

Query OK, 1 row affected (0.00 sec)

mysql> insert into store_information values('Los Angeles', 300, '2018-01-08');

Query OK, 1 row affected (0.00 sec)

mysql> insert into store_information values('Boston', 700, '2018-01-08');

Query OK, 1 row affected (0.00 sec)

Select the storeinformation table:

mysql> select *from store_information;

+------------------+-------+------------+
| store_name | sales | txn_date |
+-----------------+--------+------------+
| Los Angeles | 1500 | 2018-01-05 |
| San Diego | 250 | 2018-01-07 |

| Los Angeles | 300 | 2018-01-08 |

| Boston | 700 | 2018-01-08 |

+-------------+-------+------------+
4 rows in set (0.00 sec)

Create a table Geography:

mysql> create table geography(Region_name varchar(20), Store_name varchar(20), Sales int);

Query OK, 0 rows affected (0.28 sec)

Insert a value for Geography table:

mysql> insert into geography values('East', 'Boston', 700);

Query OK, 1 row affected (0.00 sec)

mysql> insert into geography values('East', 'New York', 1400);

Query OK, 1 row affected (0.00 sec)

mysql> insert into geography values('West', 'Los Angeles',300);

Query OK, 1 row affected (0.00 sec)

mysql> insert into geography values('West', 'San Diego', 1300);

Query OK, 1 row affected (0.00 sec)

mysql> select *from Geography;

| East | New York | 1400 |

| West | Los Angeles | 300 |

| West | San Diego | 1300 |

+-----------------+-----------------+----------------+
4 rows in set (0.00 sec)

Set Membership (in & not in) queries:

Using in:

mysql> select sum(sales) from Store_information where store_name in (Select Store_name from
Geography where region_name='west');

+--------------+
| sum(sales) |
+--------------+
| 2050 |
+--------------+
1 row in set (0.00 sec)

Using not in:

mysql> select sum(sales) from Store_information where store_name not in (Select

Store_name from Geography where region_name='west');

+---------------+
| sum(sales) |
+---------------+
| 700 |
+---------------+
1 row in set (0.00 sec)

Set Comparison (Some & All):

mysql> select * from store_information where sales> (select min(sales) from geography);

+-----------------+-------+------------+

| store_name | sales | txn_date |

+-----------------+-------+------------+

| Los Angeles | 1500 | 2018-01-05 |

| Boston | 700 | 2018-01-08 |

+----------------+--------+------------+

2 rows in set (0.00 sec)

Using Some:

mysql> select Store_Name, Sales from store_information where sales > some(select sales from
geography where store_name='san diego');

+-------------------+-------+

| Store_Name | Sales |

+------------------+-------+

| Los Angeles | 1500 |

+------------------+-------+

1 row in set (0.00 sec)

Using All:

mysql> select Store_Name from store_information where sales > all( select sales from
geography where store_name='los angeles');

+-----------------+
| Store_Name |

+-----------------+

| Los Angeles |

| Boston |

+---------------+

2 rows in set (0.00 sec)

Empty Relations(Exists & Not Exists):

Using exists:

mysql> select Min(sales) from Store_information where Exists (Select *from geography where
region_name='west');

+--------------+
| Min(sales) |
+--------------+
| 250 |
+--------------+
1 row in set (0.00 sec)

Using not exists:

mysql> select Min(sales) from Store_information where not Exists (Select *from geography
where region_name='west');

+--------------+

| Min(sales) |

+-------------+

| NULL |

+-------------+

1 row in set (0.00 sec)

MySQL Table Creation and Management
No ratings yet
MySQL Table Creation and Management
6 pages
MySQL Database and Table Management
No ratings yet
MySQL Database and Table Management
25 pages
Computer Science Practical Record
No ratings yet
Computer Science Practical Record
23 pages
MySQL Database and Table Management
No ratings yet
MySQL Database and Table Management
17 pages
V
No ratings yet
V
4 pages
Student-Teacher Database Setup
No ratings yet
Student-Teacher Database Setup
6 pages
MySQL Student and Department Tables
No ratings yet
MySQL Student and Department Tables
17 pages
SQL DDL, DML, DCL Practical Guide
No ratings yet
SQL DDL, DML, DCL Practical Guide
63 pages
SQL DDL and DML Examples in MySQL
No ratings yet
SQL DDL and DML Examples in MySQL
6 pages
DBMS Practical Programs Overview
No ratings yet
DBMS Practical Programs Overview
65 pages
MySQL Commands and Operations Guide
No ratings yet
MySQL Commands and Operations Guide
17 pages
Yashrdbms 2
No ratings yet
Yashrdbms 2
10 pages
MySQL Database and Table Operations
No ratings yet
MySQL Database and Table Operations
9 pages
MySQL Database and Table Operations
No ratings yet
MySQL Database and Table Operations
53 pages
MySQL Database and Table Operations
No ratings yet
MySQL Database and Table Operations
16 pages
SQL DDL and DML Practical Guide
No ratings yet
SQL DDL and DML Practical Guide
60 pages
DBMS Practical Programs Overview
No ratings yet
DBMS Practical Programs Overview
62 pages
MySQL Database and Table Operations
No ratings yet
MySQL Database and Table Operations
13 pages
Creating and Managing DBMS Tables
No ratings yet
Creating and Managing DBMS Tables
2 pages
SQL Commands for Database Creation
No ratings yet
SQL Commands for Database Creation
5 pages
Student Sales Data Analysis
No ratings yet
Student Sales Data Analysis
3 pages
Create and Manage Student Database
No ratings yet
Create and Manage Student Database
9 pages
MySQL Transactions and Views Explained
No ratings yet
MySQL Transactions and Views Explained
5 pages
MySQL Employee Table Operations
No ratings yet
MySQL Employee Table Operations
2 pages
SQL File
No ratings yet
SQL File
17 pages
Student Database Management Queries
No ratings yet
Student Database Management Queries
12 pages
Database and Table Management Guide
No ratings yet
Database and Table Management Guide
35 pages
MySQL Database Assignment Queries
No ratings yet
MySQL Database Assignment Queries
11 pages
MySQL Database Operations and Queries
No ratings yet
MySQL Database Operations and Queries
5 pages
MySQL Student Database Operations
No ratings yet
MySQL Student Database Operations
21 pages
SQL DDL Statements and Operations Guide
No ratings yet
SQL DDL Statements and Operations Guide
10 pages
MySQL Database and Query Examples
No ratings yet
MySQL Database and Query Examples
6 pages
MySQL Employee and Department Management
No ratings yet
MySQL Employee and Department Management
6 pages
MySQL Database for Restaurant Management
No ratings yet
MySQL Database for Restaurant Management
12 pages
DBMS 250107 173005
No ratings yet
DBMS 250107 173005
40 pages
MySQL Database and Table Creation Guide
No ratings yet
MySQL Database and Table Creation Guide
10 pages
MySQL Basic Commands for Databases
No ratings yet
MySQL Basic Commands for Databases
4 pages
MySQL Database and Table Operations
No ratings yet
MySQL Database and Table Operations
19 pages
SQL Commands for Database Management
No ratings yet
SQL Commands for Database Management
11 pages
MySQL Employee and Shopping Database
No ratings yet
MySQL Employee and Shopping Database
13 pages
MySQL Student Database Management
No ratings yet
MySQL Student Database Management
12 pages
DBMS PR03
No ratings yet
DBMS PR03
5 pages
DBMS Practical: Constraints and Views
No ratings yet
DBMS Practical: Constraints and Views
15 pages
Database Table Creation Errors
No ratings yet
Database Table Creation Errors
8 pages
MySQL Employee Database Management
No ratings yet
MySQL Employee Database Management
5 pages
MySQL Database Management Guide
No ratings yet
MySQL Database Management Guide
7 pages
MySQL Database and Table Operations
No ratings yet
MySQL Database and Table Operations
11 pages
DBMS Lab Manual for BCS403
No ratings yet
DBMS Lab Manual for BCS403
27 pages
MySQL Database Operations Guide
No ratings yet
MySQL Database Operations Guide
12 pages
MySQL Batch Queries and Errors
No ratings yet
MySQL Batch Queries and Errors
3 pages
SQL Error 1136: Column Count Mismatch
No ratings yet
SQL Error 1136: Column Count Mismatch
3 pages
SQL DDL and DML Query Examples
No ratings yet
SQL DDL and DML Query Examples
19 pages
DBMS Practical 1
No ratings yet
DBMS Practical 1
2 pages
SQL Queries for Employee Database
No ratings yet
SQL Queries for Employee Database
4 pages
MySQL Trigger and View Examples
No ratings yet
MySQL Trigger and View Examples
3 pages
MySQL Commands and Error Handling Guide
No ratings yet
MySQL Commands and Error Handling Guide
4 pages
SQL Operations on Products and Students
No ratings yet
SQL Operations on Products and Students
11 pages
Multimedia Technologies Exam Paper
No ratings yet
Multimedia Technologies Exam Paper
5 pages
Cross Section of Leaf Diagram for Class 10
100% (1)
Cross Section of Leaf Diagram for Class 10
7 pages
Final Quiz on Combustion Engineering
No ratings yet
Final Quiz on Combustion Engineering
2 pages
Fashion Show Rubric for Philippine Arts
No ratings yet
Fashion Show Rubric for Philippine Arts
1 page
MATH 37-H Course Guide Overview
No ratings yet
MATH 37-H Course Guide Overview
4 pages
HXT500 5g Servo Specifications
No ratings yet
HXT500 5g Servo Specifications
3 pages
Understanding the Solar Nebula Theory
No ratings yet
Understanding the Solar Nebula Theory
29 pages
Labor Rate Variance Analysis Guide
No ratings yet
Labor Rate Variance Analysis Guide
21 pages
Understanding Traction in Physiotherapy
No ratings yet
Understanding Traction in Physiotherapy
33 pages
Chemical Composition of Crude Oil
No ratings yet
Chemical Composition of Crude Oil
36 pages
SV6301A Handheld VNA Specifications
No ratings yet
SV6301A Handheld VNA Specifications
8 pages
Alkenes: Reactions, Synthesis, and Isomerism
No ratings yet
Alkenes: Reactions, Synthesis, and Isomerism
43 pages
STAT3003 Applied Statistics Overview
No ratings yet
STAT3003 Applied Statistics Overview
9 pages
Chemistry Sheger First Model Exam Ethiopian
No ratings yet
Chemistry Sheger First Model Exam Ethiopian
8 pages
FX Enclosure Series Overview and Specs
No ratings yet
FX Enclosure Series Overview and Specs
46 pages
Gravity and Magnetics in Geothermal Exploration
No ratings yet
Gravity and Magnetics in Geothermal Exploration
8 pages
Electric Power Technology Training Systems
No ratings yet
Electric Power Technology Training Systems
300 pages
Lonnec: Shedding On What Surgeons Need
No ratings yet
Lonnec: Shedding On What Surgeons Need
5 pages
Stock Market Analysis & Prediction
No ratings yet
Stock Market Analysis & Prediction
80 pages
Computer Networks Lab Syllabus
No ratings yet
Computer Networks Lab Syllabus
87 pages
MOS Capacitor C-V Profiling Guide
No ratings yet
MOS Capacitor C-V Profiling Guide
7 pages
GameCenter Initialization Log Report
No ratings yet
GameCenter Initialization Log Report
56 pages
Overview of Generative Adversarial Networks
100% (1)
Overview of Generative Adversarial Networks
14 pages
Internship in Oil & Gas Engineering
No ratings yet
Internship in Oil & Gas Engineering
22 pages
Hybrid Solar PV and Biogas Power Plant
No ratings yet
Hybrid Solar PV and Biogas Power Plant
6 pages
Sample and Hold Circuit Basics
No ratings yet
Sample and Hold Circuit Basics
63 pages
IHSDM for New Zealand Highway Safety
No ratings yet
IHSDM for New Zealand Highway Safety
33 pages
Functions of Several Variables Overview
No ratings yet
Functions of Several Variables Overview
8 pages
Partner Selection: Contextual Influences
No ratings yet
Partner Selection: Contextual Influences
22 pages
07 - Chapter 3
No ratings yet
07 - Chapter 3
58 pages

Programs & Index

Uploaded by

Programs & Index

Uploaded by

INDEX

[Link] DATE TITLE Page NO SIGN

1 10-01-2023 Mysql operations

2 20-01-2023 Set operations

3 28-01-2023 Aggregate function

4 04-02-2023 Join operations

5 11-02-2023 Nested sub-queries

7 06-03-2023 String operations

8 13-03-2023 BANKING DATABASE

mysql> use store;

Creating a Table (using primary key):

mysql> desc employee;

3 rows in set (0.00 sec)

Altering the Table Structure:

mysql> desc employee;

mysql> insert into Employee values(301, 'Ajitha', 24, 15000);

mysql> select *from Employee;

mysql> select *From employee;

mysql> select *from employee where emp_id=302;

Drop the Table:

Mysql> select *from Employee;

Inserting a value for first table (Aathibakery):

Select the First table (Aathibakery):

Creating a Second Table (Balubakery):

Inserting a value for Second table (Balu bakery):

Select the Second table (Balubakery):

Union Operation Query:

| Sno | Items | Cost |

5 rows in set (0.00 sec)

Union all Operation Query:

| Sno | Items | Cost |

Intersect Operation Query:

Intersect all Operation Query:

mysql> select *from aathibakery intersect all select *from balubakery;

Inserting a value for Student Table:

Group by Clause & Having Clause:

Query OK, 1 row affected (0.00 sec)

mysql> use laptop;

Create a First Table(brand):

Query OK, 0 rows affected (0.07 sec)

Insert a value for brand table:

Query OK, 1 row affected (0.00 sec)

mysql> insert into brand values(2, 'HP');

Query OK, 1 row affected (0.00 sec)

mysql> insert into brand values(3, 'Apple');

Query OK, 1 row affected (0.00 sec)

Select a brand table:

3 rows in set (0.00 sec)

Create a Second Table(brand):

Query OK, 0 rows affected (0.08 sec)

Insert a value for color table:

mysql> insert into color values(2, 'Pink');

Query OK, 1 row affected (0.00 sec)

mysql> insert into color values(4, 'Blue');

Query OK, 1 row affected (0.00 sec)

Select a color table :

3 rows in set (0.00 sec)

2 rows in set (0.00 sec)

Left Outer join:

Right Outer join:

| NULL | NULL | 4 | Blue |

3 rows in set (0.00 sec)

Full Outer join:

9 rows in set (0.00 sec)

mysql> select *from brand natural join color where [Link]='pink';

mysql> use banking;

mysql> create table branch(branch_name char(23),branch_city char(23),assets int,

Query OK, 0 rows affected (0.25 sec)

mysql> describe branch;

| Field | Type | Null | Key | Default | Extra |

| branch_name | char(23) | NO | PRI | | |

| branch_city | char(23) | YES | | NULL | |

| assets | int(11) | YES | | NULL | |

3 rows in set (0.03 sec)

mysql> create table account(accno int,branch_name char(23),balance int,primary key(accno),

Query OK, 0 rows affected (0.23 sec)

mysql> select from aathibakery intersect all select from balubakery;