Practical No.

Enter password: *******

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
mysql> Create Database GIMR1;
Query OK, 1 row affected (0.01 sec)

mysql> Show Databases;

+--------------------+
| Database |
+--------------------+
| account |
| amaan |
| anam |
| avantika |
| gaurav |
| gimr |
| gimr1 |
| himani |
| hospital |
| information_schema |
| komal |
| mysql |
| naazreen |
| name |
| nazreen |
| performance_schema |
| pooja |
| prem |
| rohit |
| sharfiya |
| student |
| sys |
+--------------------+
22 rows in set (0.01 sec)

mysql> Use GIMR1;

Database changed
mysql> Use GIMR1;
Database changed

mysql> Drop Database GIMR1;

Query OK, 0 rows affected (0.02 sec)
mysql>
 Practical No.2

Enter password: *******

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.

mysql> Use GIMR

Database changed
mysql> Create Table Employee1(Emp_Id int, Name Char(90), Address
Varchar(100), Salary decimal(18,2));
Query OK, 0 rows affected (0.01 sec)
mysql> Insert into Employee1(Emp_Id, Name, Address, Salary) Values
(01,'Aman Varma','254 Pune',18000.98),(02,'Rohit Sharma','465
Mumbai',29800.55),(03,'Priti Deshmukh','654
Banglore',789654.99),(04,'Awesh Maniyar','445
Hyderabad',96867565.67),(05,'Krishna Mali',' 345 Delhi',28654564.98);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> Insert into Employee1 (Emp_Id,Name,Address,Salary) Values

(06,'Nidhi Patil','756 Nasik',98547.00),(07,'Nidhi patil','475
Aurangabad',867568.67),(08,'Sham Chaudhari','856
Jalgaon',546474.75),(09,'Anam Shaikh','756
Nagpur',8574673.89),(10,'Sharfiya Khan','546 Ajmer',98000.99);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> Select * from Employee1;
+--------+----------------+----------------+-------------+
| Emp_Id | Name | Address | Salary |
+--------+----------------+----------------+-------------+
| 1 | Aman Varma | 254 Pune | 18000.98 |
| 2 | Rohit Sharma | 465 Mumbai | 29800.55 |
| 3 | Priti Deshmukh | 654 Banglore | 789654.99 |
| 4 | Awesh Maniyar | 445 Hyderabad | 96867565.67 |
| 5 | Krishna Mali | 345 Delhi | 28654564.98 |
| 6 | Nidhi Patil | 756 Nasik | 98547.00 |
| 7 | Nidhi patil | 475 Aurangabad | 867568.67 |
| 8 | Sham Chaudhari | 856 Jalgaon | 546474.75 |
| 9 | Anam Shaikh | 756 Nagpur | 8574673.89 |
| 10 | Sharfiya Khan | 546 Ajmer | 98000.99 |
+--------+----------------+----------------+-------------+
10 rows in set (0.00 sec)

mysql>
 Practical No.3

INSERT
mysql> Create Table Student1(Roll_No int, Name char(90), Fees
decimal(18,2));
Query OK, 0 rows affected (0.01 sec)

mysql> Insert into Student1 Values (01,'Shital Mahajan',45000.99);

Query OK, 1 row affected (0.01 sec)

UPDATE Record
UPDATE Student SET Name='Uday Bajode', Address='278 Bhusawal' WHERE
Roll_No=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> Select * from Student;

+---------+------------------+--------------+----------+
| Roll_No | Name | Address | Fees |
+---------+------------------+--------------+----------+
| 1 | Rahul Sharma | 345 Pune | 15000.99 |
| 2 | Uday Bajode | 278 Bhusawal | 18000.99 |
| 3 | Atif Khan | 345 Pune | 15000.99 |
| 4 | Girish Chaudhari | 567 Mumbai | 18001.00 |
| 5 | Priti Deshmukh | 675 Jalgaon | 89776.99 |
+---------+------------------+--------------+----------+
5 rows in set (0.00 sec)

DELETE Record
mysql> DELETE from Student WHERE Name='Girish Chaudhari';
Query OK, 1 row affected (0.00 sec)

mysql> Select * from Student;

+---------+----------------+--------------+----------+
| Roll_No | Name | Address | Fees |
+---------+----------------+--------------+----------+
| 1 | Rahul Sharma | 345 Pune | 15000.99 |
| 2 | Uday Bajode | 278 Bhusawal | 18000.99 |
| 3 | Atif Khan | 345 Pune | 15000.99 |
| 5 | Priti Deshmukh | 675 Jalgaon | 89776.99 |
+---------+----------------+--------------+----------+
4 rows in set (0.00 sec)
 Practical No.4

mysql> Select * from Student;

+---------+----------------+--------------+----------+
| Roll_No | Name | Address | Fees |
+---------+----------------+--------------+----------+
| 1 | Rahul Sharma | 345 Pune | 15000.99 |
| 2 | Uday Bajode | 278 Bhusawal | 18000.99 |
| 3 | Atif Khan | 345 Pune | 15000.99 |
| 5 | Priti Deshmukh | 675 Jalgaon | 89776.99 |
+---------+----------------+--------------+----------+
4 rows in set (0.01 sec)

mysql> Select Roll_No,Name,Fees from Student;

+---------+----------------+----------+
| Roll_No | Name | Fees |
+---------+----------------+----------+
| 1 | Rahul Sharma | 15000.99 |
| 2 | Uday Bajode | 18000.99 |
| 3 | Atif Khan | 15000.99 |
| 5 | Priti Deshmukh | 89776.99 |
+---------+----------------+----------+
4 rows in set (0.00 sec)

mysql> Select DISTINCT Fees from Student;

+----------+
| Fees |
+----------+
| 15000.99 |
| 18000.99 |
| 89776.99 |
+----------+
3 rows in set (0.00 sec)

mysql> Select * from Student WHERE Fees>15000;

+---------+----------------+--------------+----------+
| Roll_No | Name | Address | Fees |
+---------+----------------+--------------+----------+
| 1 | Rahul Sharma | 345 Pune | 15000.99 |
| 2 | Uday Bajode | 278 Bhusawal | 18000.99 |
| 3 | Atif Khan | 345 Pune | 15000.99 |
| 5 | Priti Deshmukh | 675 Jalgaon | 89776.99 |
+---------+----------------+--------------+----------+
4 rows in set (0.00 sec)
 Practical No-5

mysql> Use GIMR;

Database changed
mysql> Show tables;
+----------------+
| Tables_in_gimr |
+----------------+
| salary |
| student |
+----------------+
2 rows in set (0.01 sec)

mysql> Select * from Salary;

+--------+------+---------------+------------+
| Emp_Id | Age | Address | Contact_No |
+--------+------+---------------+------------+
| 101 | 45 | 768 Pune | 7854893 |
| 102 | 78 | 675 Mumbai | 4237789 |
| 103 | 67 | 342 Bhausawal | 7854099 |
| 104 | 34 | 668 Nasik | 7854893 |
| 105 | 28 | 556 Indore | 4237789 |
| 106 | 77 | 442 Delhi | 7854099 |
+--------+------+---------------+------------+
6 rows in set (0.01 sec)

mysql> Alter Table Salary ADD Payment int;

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

mysql> Select * from Salary;

+--------+------+---------------+------------+---------+
| Emp_Id | Age | Address | Contact_No | Payment |
+--------+------+---------------+------------+---------+
| 101 | 45 | 768 Pune | 7854893 | NULL |
| 102 | 78 | 675 Mumbai | 4237789 | NULL |
| 103 | 67 | 342 Bhausawal | 7854099 | NULL |
| 104 | 34 | 668 Nasik | 7854893 | NULL |
| 105 | 28 | 556 Indore | 4237789 | NULL |
| 106 | 77 | 442 Delhi | 7854099 | NULL |
+--------+------+---------------+------------+---------+
6 rows in set (0.00 sec)

mysql> Alter Table Salary RENAME Column Contact_No To Mobile_No;

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

mysql> Select * from Salary;

+--------+------+---------------+-----------+---------+
| Emp_Id | Age | Address | Mobile_No | Payment |
+--------+------+---------------+-----------+---------+
| 101 | 45 | 768 Pune | 7854893 | NULL |
| 102 | 78 | 675 Mumbai | 4237789 | NULL |
| 103 | 67 | 342 Bhausawal | 7854099 | NULL |
| 104 | 34 | 668 Nasik | 7854893 | NULL |
| 105 | 28 | 556 Indore | 4237789 | NULL |
| 106 | 77 | 442 Delhi | 7854099 | NULL |
+--------+------+---------------+-----------+---------+
6 rows in set (0.00 sec)
mysql> Alter Table Salary MODIFY Column Age varchar(20);
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> Desc Salary;

+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Emp_Id | int | YES | | NULL | |
| Age | varchar(20) | YES | | NULL | |
| Address | varchar(90) | YES | | NULL | |
| Mobile_No | int | YES | | NULL | |
| Payment | int | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> Select * from Salary;

+--------+------+---------------+-----------+---------+
| Emp_Id | Age | Address | Mobile_No | Payment |
+--------+------+---------------+-----------+---------+
| 101 | 45 | 768 Pune | 7854893 | NULL |
| 102 | 78 | 675 Mumbai | 4237789 | NULL |
| 103 | 67 | 342 Bhausawal | 7854099 | NULL |
| 104 | 34 | 668 Nasik | 7854893 | NULL |
| 105 | 28 | 556 Indore | 4237789 | NULL |
| 106 | 77 | 442 Delhi | 7854099 | NULL |
+--------+------+---------------+-----------+---------+
6 rows in set (0.00 sec)

mysql> Alter Table Salary DROP Column Payment;

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

mysql> Select * from Salary;

+--------+------+---------------+-----------+
| Emp_Id | Age | Address | Mobile_No |
+--------+------+---------------+-----------+
| 101 | 45 | 768 Pune | 7854893 |
| 102 | 78 | 675 Mumbai | 4237789 |
| 103 | 67 | 342 Bhausawal | 7854099 |
| 104 | 34 | 668 Nasik | 7854893 |
| 105 | 28 | 556 Indore | 4237789 |
| 106 | 77 | 442 Delhi | 7854099 |
+--------+------+---------------+-----------+
6 rows in set (0.00 sec)

mysql>
 Practical No.6

mysql> Create table PK (EMp_Id int Primary Key, Name char(90));

Query OK, 0 rows affected (0.04 sec)

mysql> desc PK;

+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| EMp_Id | int | NO | PRI | NULL | |
| Name | char(90) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> Insert into PK(Emp_Id,Name) Values(01,'Sonu');

Query OK, 1 row affected (0.01 sec)

mysql> Select * from PK;

+--------+------+
| EMp_Id | Name |
+--------+------+
| 1 | Sonu |
+--------+------+
1 row in set (0.00 sec)

FOREIGN KEY
mysql> Create Table Parent (Stud_Id int Primary Key);
Query OK, 0 rows affected (0.02 sec)
mysql> Create Table Child (Name Char(90),Age int, Stud_Id int, FOREIGN
KEY (Stud_Id) REF
ERENCES Parent(Stud_Id));
Query OK, 0 rows affected (0.02 sec)

mysql> Insert into Parent Values (01);

Query OK, 1 row affected (0.01 sec)

mysql> Select * from Parent;

+---------+
| Stud_Id |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)

mysql> Insert into Child Values ('Sham',28,01);

Query OK, 1 row affected (0.01 sec)

mysql> Select * from child;

+------+------+---------+
| Name | Age | Stud_Id |
+------+------+---------+
| Sham | 28 | 1 |
+------+------+---------+
1 row in set (0.00 sec)

mysql> Insert into Child Values ('Monu',39,02);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`gimr`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY
(`Stud_Id`) REFERENCES `parent` (`Stud_Id`))
mysql> Insert into Parent Values (02);
Query OK, 1 row affected (0.01 sec)

mysql> Insert into Child Values ('Monu',39,02);

Query OK, 1 row affected (0.00 sec)

mysql> Select * from child;

+------+------+---------+
| Name | Age | Stud_Id |
+------+------+---------+
| Sham | 28 | 1 |
| Monu | 39 | 2 |
+------+------+---------+
2 rows in set (0.00 sec)

CHECK KEY
mysql> Insert into PK(Emp_Id,Name) Values(01,'Monu');
ERROR 1062 (23000): Duplicate entry '1' for key 'pk.PRIMARY'
mysql> Insert into PK(Emp_Id,Name) Values(02,'Sonu');
Query OK, 1 row affected (0.01 sec)

mysql> Create Table DemoCheck(RollNo int Primary key,Age int NOT NULL
CHECK(Age<=40));
Query OK, 0 rows affected (0.01 sec)

mysql> Insert into DemoCheck(RollNO,Age) Values (01,45);

ERROR 3819 (HY000): Check constraint 'democheck_chk_1' is violated.
mysql> Insert into DemoCheck(RollNO,Age) Values (01,39);
Query OK, 1 row affected (0.01 sec)

mysql> Select * from DemoCheck;

+--------+-----+
| RollNo | Age |
+--------+-----+
| 1 | 39 |
+--------+-----+
1 row in set (0.00 sec)

NOT NULL
mysql> Create Table NT (RollNO int NOT NULL, Name varchar(90),Class
varchar(80));
Query OK, 0 rows affected (0.01 sec)

mysql> Desc NT;

+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| RollNO | int | NO | | NULL | |
| Name | varchar(90) | YES | | NULL | |
| Class | varchar(80) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> Insert into NT (Name, Class) Values ('MONU', 'BCA');

ERROR 1364 (HY000): Field 'RollNO' doesn't have a default value
mysql> Insert into NT (RollNo,Name Class) Values (01,'Aman','BCA');
Query OK, 1 row affected (0.00 sec)
mysql> Select * from NT;
+--------+------+-------+
| RollNO | Name | Class |
+--------+------+-------+
| 1 | Aman | BCA |
+--------+------+-------+
1 row in set (0.00 sec)

DEFAULT KEY
mysql> Create Table DemoDefault (RollNo int primary Key,Age int NOT NULL
Default 22);
Query OK, 0 rows affected (0.01 sec)

mysql> Insert into DemoDefault (RollNo) Values (01);

Query OK, 1 row affected (0.01 sec)

mysql> Select * from DemoDefault;

+--------+-----+
| RollNo | Age |
+--------+-----+
| 1 | 22 |
+--------+-----+
1 row in set (0.00 sec)
mysql> Insert into DemoDefault (RollNo,Age) Values (02,33);
Query OK, 1 row affected (0.01 sec)

mysql> Select * from DemoDefault;

+--------+-----+
| RollNo | Age |
+--------+-----+
| 1 | 22 |
| 2 | 33 |
+--------+-----+
2 rows in set (0.00 sec)
 Practical No.7

Arithmetic Oprator
Add
mysql> select 20+30;
+-------+
| 20+30 |
+-------+
| 50 |
+-------+
1 row in set (0.00 sec)

Divide
mysql> Select 175 / 5;
+---------+
| 175 / 5 |
+---------+
| 35.0000 |
+---------+
1 row in set (0.00 sec)

Comparison Oprator
mysql> Create Table Emp (Emp_Id int, Name varchar(90), Salary int);
Query OK, 0 rows affected (0.02 sec)
mysql> Insert into Emp (Emp_Id, Name, Salary) Values (01,'Rahul
Patil',30000),(02,'Priti Agrawal',60000),(03,'Risha
Khan',70000),(04,'Danish Shaikh',50000),(05,'Sonali Chaudhari',80000);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM Emp WHERE Salary != 80000;

+--------+---------------+--------+
| Emp_Id | Name | Salary |
+--------+---------------+--------+
| 1 | Rahul Patil | 30000 |
| 2 | Priti Agrawal | 60000 |
| 3 | Risha Khan | 70000 |
| 4 | Danish Shaikh | 50000 |
+--------+---------------+--------+

mysql> SELECT * FROM Emp WHERE Salary >= 60000;

+--------+------------------+--------+
| Emp_Id | Name | Salary |
+--------+------------------+--------+
| 1 | Priti Agrawal | 60000 |
| 2 | Risha Khan | 70000 |
| 3 | Sonali Chaudhari | 80000 |
+--------+------------------+--------+
4 rows in set (0.00 sec)

Logical Oprator
mysql> SELECT * FROM Emp WHERE Salary BETWEEN 60000 AND 80000;
+--------+------------------+--------+
| Emp_Id | Name | Salary |
+--------+------------------+--------+
| 2 | Priti Agrawal | 60000 |
| 3 | Risha Khan | 70000 |
| 5 | Sonali Chaudhari | 80000 |
+--------+------------------+--------+
3 rows in set (0.00 sec)
 Practical No.8

mysql> Create Table xyz(Id int,Name char(90),Contact varchar(90));

Query OK, 0 rows affected (0.08 sec)

mysql> Insert into xyz(Id,Name,Contact)

values(01,'Rahul','9836525425'),(3,'Sham','984792109');
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> create table abc (Id int,Dept varchar(20));

Query OK, 0 rows affected (0.01 sec)

mysql> Insert into abc(Id, Dept) Values (01,'HR'), (02,'RND');

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

mysql> select * from abc;

+------+------+
| Id | Dept |
+------+------+
| 1 | HR |
| 2 | RND |
+------+------+
2 rows in set (0.00 sec)

INNER JOIN
mysql> Select abc.Dept,xyz.Name,xyz.contact from abc Inner Join xyz on
abc.Id=xyz.Id;
+------+-------+------------+
| Dept | Name | contact |
+------+-------+------------+
| HR | Rahul | 9836525425 |
+------+-------+------------+
1 row in set (0.00 sec)

NATURAL JOIN
Select abc1.Dept,xyz.Name,xyz.contact from abc1 Natural Join xyz;
+------+-------+------------+
| Dept | Name | contact |
+------+-------+------------+
| HR | Rahul | 9836525425 |
+------+-------+------------+
1 row in set (0.00 sec)

OUTER JOIN
LEFT OUTER JOIN
mysql> Select Dept, Name from abc Left Outer Join xyz on abc.id=xyz.id;
+------+-------+
| Dept | Name |
+------+-------+
| HR | Rahul |
| RND | NULL |
+------+-------+
2 rows in set (0.00 sec)

RIGHT OUTER JOIN

mysql> Select Dept, Name from abc Right Outer Join xyz on abc.id=xyz.id;
+------+-------+
| Dept | Name |
+------+-------+
| HR | Rahul |
| NULL | Sham |
+------+-------+
2 rows in set (0.00 sec)

FULL OUTER JOIN

mysql> Select * from abc Full Join xyz;
+------+------+------+-------+------------+
| Id | Dept | Id | Name | Contact |
+------+------+------+-------+------------+
| 2 | RND | 1 | Rahul | 9836525425 |
| 1 | HR | 1 | Rahul | 9836525425 |
| 2 | RND | 3 | Sham | 984792109 |
| 1 | HR | 3 | Sham | 984792109 |
+------+------+------+-------+------------+
4 rows in set, 1 warning (0.00 sec)

Nested Query

mysql> Create Table Employee2(Id int, Name char(90),Age int);

Query OK, 0 rows affected (0.03 sec)

mysql> Insert into Employee2(Id,Name,Age) Values

(101,'Neha',28),(102,'Isha',35),(103,'Mina',42),(104,'Saniya',27),(105,'R
ahul',38);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> Create Table Sal (ID int,Salary int) ;

Query OK, 0 rows affected (0.01 sec)

mysql> Insert into Sal (ID,Salary) Values

(101,10000),(102,15000),(103,25000),(104,10000),(105,30000);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> Select Name from Employee2 WHERE ID IN (Select ID from Sal WHERE
Salary > 10000);
+-------+
| Name |
+-------+
| Isha |
| Mina |
| Rahul |
+-------+
3 rows in set (0.00 sec)
 Practical No.9

mysql> use gimr;

Database changed

mysql> Select * from Salary;

+--------+----------+------+------------+------------+
| Emp_ID | Emp_Name | Age | Address | Contact_No |
+--------+----------+------+------------+------------+
| 1 | Danish | 45 | Pune | 7845 |
| 2 | Vaibhav | 34 | Bhusawal | 9867 |
| 3 | Shivani | 28 | Aurangabad | 1167 |
| 4 | Amit | 55 | NULL | 9879 |
| 5 | sham | 58 | Jalgaon | 7685 |
| 6 | Pradip | 55 | Jamner | 8967 |
| 7 | Ranjana | 45 | Pachora | 9099 |
+--------+----------+------+------------+------------+
7 rows in set (0.00 sec)

WHERE Clause
mysql> Select * From Salary WHERE Age>=45;
+--------+----------+------+---------+------------+
| Emp_ID | Emp_Name | Age | Address | Contact_No |
+--------+----------+------+---------+------------+
| 1 | Danish | 45 | Pune | 7845 |
| 4 | Amit | 55 | NULL | 9879 |
| 5 | sham | 58 | Jalgaon | 7685 |
| 6 | Pradip | 55 | Jamner | 8967 |
| 7 | Ranjana | 45 | Pachora | 9099 |
+--------+----------+------+---------+------------+
5 rows in set (0.00 sec)

mysql> Select * From Salary WHERE Age<34;

+--------+----------+------+------------+------------+
| Emp_ID | Emp_Name | Age | Address | Contact_No |
+--------+----------+------+------------+------------+
| 3 | Shivani | 28 | Aurangabad | 1167 |
+--------+----------+------+------------+------------+
1 row in set (0.00 sec)

Order By Clause
mysql> Select * From Salary ORDER BY Age;
+--------+----------+------+------------+------------+
| Emp_ID | Emp_Name | Age | Address | Contact_No |
+--------+----------+------+------------+------------+
| 3 | Shivani | 28 | Aurangabad | 1167 |
| 2 | Vaibhav | 34 | Bhusawal | 9867 |
| 1 | Danish | 45 | Pune | 7845 |
| 7 | Ranjana | 45 | Pachora | 9099 |
| 4 | Amit | 55 | NULL | 9879 |
| 6 | Pradip | 55 | Jamner | 8967 |
| 5 | sham | 58 | Jalgaon | 7685 |
+--------+----------+------+------------+------------+
7 rows in set (0.00 sec)
mysql> Select * From Salary ORDER BY Age DESC;
+--------+----------+------+------------+------------+
| Emp_ID | Emp_Name | Age | Address | Contact_No |
+--------+----------+------+------------+------------+
| 5 | sham | 58 | Jalgaon | 7685 |
| 4 | Amit | 55 | NULL | 9879 |
| 6 | Pradip | 55 | Jamner | 8967 |
| 1 | Danish | 45 | Pune | 7845 |
| 7 | Ranjana | 45 | Pachora | 9099 |
| 2 | Vaibhav | 34 | Bhusawal | 9867 |
| 3 | Shivani | 28 | Aurangabad | 1167 |
+--------+----------+------+------------+------------+
7 rows in set (0.00 sec)

mysql> Select * From Salary ORDER BY Emp_Name,Age;

+--------+----------+------+------------+------------+
| Emp_ID | Emp_Name | Age | Address | Contact_No |
+--------+----------+------+------------+------------+
| 4 | Amit | 55 | NULL | 9879 |
| 1 | Danish | 45 | Pune | 7845 |
| 6 | Pradip | 55 | Jamner | 8967 |
| 7 | Ranjana | 45 | Pachora | 9099 |
| 5 | sham | 58 | Jalgaon | 7685 |
| 3 | Shivani | 28 | Aurangabad | 1167 |
| 2 | Vaibhav | 34 | Bhusawal | 9867 |
+--------+----------+------+------------+------------+
7 rows in set (0.00 sec)

HAVING Clause
mysql> Select * From Salary;
+--------+--------+------+---------+------------+
| Emp_ID | Name | Age | Address | Contact_No |
+--------+--------+------+---------+------------+
| 1 | Danish | 45 | Pune | 7845 |
| 2 | Ramesh | 55 | Pachora | 9879 |
| 3 | Sham | 58 | Jalgaon | 7685 |
| 4 | Pradip | 55 | Jamner | 8967 |
+--------+--------+------+---------+------------+
4 rows in set (0.00 sec)
mysql> Select Emp_ID, Age From Salary Group by Emp_ID HAVING Age > 45;
+--------+------+
| Emp_ID | Age |
+--------+------+
| 2 | 55 |
| 3 | 58 |
| 4 | 55 |
+--------+------+
3 rows in set (0.00 sec)
 Practical No.10

mysql> Create Table Prod(Product varchar(90),Company varchar(80),Qty

int,Rate int,Cost int);
Query OK, 0 rows affected (0.02 sec)

mysql> Insert into Prod(Product, Company,Qty,Cost) Values

('Soap','ABC',15,15000),('Shampoo','XYZ',45,30000),('Surf','PQR
',35,78000);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> Insert into Prod(Product, Company,Qty,Cost) Values

('Aala','EFG',64,50000),('Brush','RST',52,60000);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> Select * from Prod;

+---------+---------+------+------+-------+
| Product | Company | Qty | Rate | Cost |
+---------+---------+------+------+-------+
| Soap | ABC | 15 | 15 | 15000 |
| Shampoo | XYZ | 45 | 20 | 30000 |
| Surf | PQR | 35 | 10 | 78000 |
| Aala | EFG | 64 | 30 | 50000 |
| Brush | RST | 52 | 25 | 60000 |
+---------+---------+------+------+-------+
5 rows in set (0.00 sec)

Aggregate Function
COUNT
mysql> Select COUNT(*) from Prod;
+----------+
| COUNT(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)

SUM
mysql> Select SUM(Cost) from Prod;
+-----------+
| SUM(Cost) |
+-----------+
| 233000 |
+-----------+
1 row in set (0.00 sec)

AVG
mysql> Select AVG(Cost) from Prod;
+------------+
| AVG(Cost) |
+------------+
| 46600.0000 |
+------------+
1 row in set (0.01 sec)

mysql> Select AVG(Qty) from Prod;

+----------+
| AVG(Qty) |
+----------+
| 42.2000 |
+----------+
1 row in set (0.00 sec)

MAX
mysql> Select MAX(Rate) from Prod;
+-----------+
| MAX(Rate) |
+-----------+
| 30 |
+-----------+
1 row in set (0.00 sec)

MIN
mysql> Select Min(Rate) from Prod;
+-----------+
| Min(Rate) |
+-----------+
| 10 |
+-----------+
1 row in set (0.00 sec)

DATE FUNCTION

mysql> Select NOW();

+---------------------+
| NOW() |
+---------------------+
| 2024-03-18 15:14:24 |
+---------------------+
1 row in set (0.08 sec)
mysql> Select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 15:18:18 |
+-----------+
1 row in set (0.00 sec)

STRING FUNCTION

mysql> SELECT CHAR_LENGTH('I Love India');

+-----------------------------+
| CHAR_LENGTH('I Love India') |
+-----------------------------+
| 12 |
+-----------------------------+
1 row in set (0.00 sec)