Practical #2
1) Viewing all Databases
mysql> show databases;
+——————–+
| Database |
+——————–+
| dv10 |
| dvprimarykey |
| information_schema |
| mysql |
| performance_schema |
| sys |
+——————–+
6 rows in set (0.00 sec)
2) Creating a Database
mysql> create database DV10
-> ;
Query OK, 1 row affected (0.01 sec)
mysql> use DV10
Database changed
3)Viewing all Tables in Database
mysql> show tables;
+—————-+
| Tables_in_dv10 |
+—————-+
| faculty |
| student |
+—————-+
2 rows in set (0.00 sec)
4) Create a Tables
mysql> create table Student(Roll_Number int, Name varchar(30), Class varchar(10), City varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> describe Student;
+————-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————-+————-+——+—–+———+——-+
| Roll_Number | int | YES | | NULL | |
| Name | varchar(30) | YES | | NULL | |
| Class | varchar(10) | YES | | NULL | |
| City | varchar(20) | YES | | NULL | |
+————-+————-+——+—–+———+——-+
4 rows in set (0.01 sec)
5) Inserting / Updating/ Deleting records in Table
mysql> insert into Student(Roll_Number ,Name ,Class ,City ) values(1, ‘Devraj’, ‘BSc.Cs’, ‘Pune’);
Query OK, 1 row affected (0.02 sec)
mysql> select * from Student;
+————-+——–+——–+——+
| Roll_Number | Name | Class | City |
+————-+——–+——–+——+
| 1 | Devraj | BSc.Cs | Pune |
+————-+——–+——–+——+
1 row in set (0.00 sec)
mysql> select * from Student;
+————-+———+——–+———–+
| Roll_Number | Name | Class | City |
+————-+———+——–+———–+
| 1 | Devraj | BSc.Cs | Pune |
| 2 | Shubham | BSc.Cs | Thane |
| 3 | Garvit | BSc.Cs | Rajasthan |
| 4 | Bhavesh | BSc.Cs | Mumbai |
| 5 | Yash | BSc.Cs | Mumbai |
+————-+———+——–+———–+
5 rows in set (0.00 sec)
mysql> describe student;
+————-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————-+————-+——+—–+———+——-+
| Roll_Number | int | YES | | NULL | |
| Name | varchar(30) | YES | | NULL | |
| Class | varchar(10) | YES | | NULL | |
| City | varchar(20) | YES | | NULL | |
| Subject | varchar(20) | YES | | NULL | |
+————-+————-+——+—–+———+——-+
5 rows in set (0.01 sec)
mysql> insert into datasheet(Roll_Number, Name, Class, City, Grade, Subject)values(7,’Ayush’,’BSc.CS’,’Mumbai’,’-B’,’NDA’);
Query OK, 1 row affected (0.01 sec)
mysql> select * from datasheet;
+————-+———+——–+———–+——-+——————+
| Roll_Number | Name | Class | City | Grade | Subject |
+————-+———+——–+———–+——-+——————+
| 1 | Devraj | BSc.Cs | Pune | NULL | Python |
| 2 | Shubham | BSc.Cs | Thane | NULL | Railway Engineer |
| 3 | Garvit | BSc.Cs | Rajasthan | NULL | Special Service |
| 4 | Bhavesh | BSc.Cs | Mumbai | NULL | Machine Learning |
| 5 | Yash | BSc.Cs | Mumbai | NULL | Full Stack Dev |
| 7 | Ayush | BSc.CS | Mumbai | -B | NDA |
+————-+———+——–+———–+——-+——————+
6 rows in set (0.00 sec)
mysql> delete from datasheet where Roll_Number=7;
Query OK, 1 row affected (0.01 sec)
mysql> select * from datasheet;
+————-+———+——–+———–+——-+——————+
| Roll_Number | Name | Class | City | Grade | Subject |
+————-+———+——–+———–+——-+——————+
| 1 | Devraj | BSc.Cs | Pune | NULL | Python |
| 2 | Shubham | BSc.Cs | Thane | NULL | Railway Engineer |
| 3 | Garvit | BSc.Cs | Rajasthan | NULL | Special Service |
| 4 | Bhavesh | BSc.Cs | Mumbai | NULL | Machine Learning |
| 5 | Yash | BSc.Cs | Mumbai | NULL | Full Stack Dev |
+————-+———+——–+———–+——-+——————+
5 rows in set (0.00 sec)
mysql> update student set Subject=’Python’ where Roll_number=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from Student;
+————-+———+——–+———–+——————+
| Roll_Number | Name | Class | City | Subject |
+————-+———+——–+———–+——————+
| 1 | Devraj | BSc.Cs | Pune | Python |
| 2 | Shubham | BSc.Cs | Thane | Railway Engineer |
| 3 | Garvit | BSc.Cs | Rajasthan | Service |
| 4 | Bhavesh | BSc.Cs | Mumbai | Machine Learning |
| 5 | Yash | BSc.Cs | Mumbai | Full Stack Dev |
+————-+———+——–+———–+——————+
5 rows in set (0.00 sec)
mysql> update student set Subject=’Special Service’ where Roll_Number=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+————-+———+——–+———–+——-+——————+
| Roll_Number | Name | Class | City | Grade | Subject |
+————-+———+——–+———–+——-+——————+
| 1 | Devraj | BSc.Cs | Pune | NULL | Python |
| 2 | Shubham | BSc.Cs | Thane | NULL | Railway Engineer |
| 3 | Garvit | BSc.Cs | Rajasthan | NULL | Special Service |
| 4 | Bhavesh | BSc.Cs | Mumbai | NULL | Machine Learning |
| 5 | Yash | BSc.Cs | Mumbai | NULL | Full Stack Dev |
+————-+———+——–+———–+——-+——————+
5 rows in set (0.00 sec)
Practical #3
1) Altering Table
mysql> alter table student ADD Subject varchar (20);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table Student ADD Grade varchar(20) after City;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table Student ADD primary key(Roll_Number);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe Student;
+————-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————-+————-+——+—–+———+——-+
| Roll_Number | int | NO | PRI | NULL | |
| Name | varchar(30) | YES | | NULL | |
| Class | varchar(10) | YES | | NULL | |
| City | varchar(20) | YES | | NULL | |
| Grade | varchar(20) | YES | | NULL | |
| Subject | varchar(20) | YES | | NULL | |
+————-+————-+——+—–+———+——-+
- rows in set (0.00 sec)
2) Dropping/ Truncate /Rename Table
mysql> alter table Student RENAME to DataSheet;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+—————-+
| Tables_in_dv10 |
+—————-+
| datasheet |
| faculty |
+—————-+
2 rows in set (0.00 sec)
mysql> select * from faculty;
+——+——–+———-+———+——-+
| ID | Name | DoB | Subject | Hours |
+——+——–+———-+———+——-+
| 101 | Amit | 24/12/72 | PM | 8 |
| 102 | Reva | 01/03/93 | IOT | 12 |
| 103 | Sachin | 03/02/80 | OS | 8 |
| 104 | Mahesh | 28/11/80 | DT | 16 |
| 105 | Nitin | 01/01/83 | P.Com | 10 |
+——+——–+———-+———+——-+
5 rows in set (0.00 sec)
mysql> truncate faculty;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from faculty;
Empty set (0.00 sec)
mysql> drop table leads;
Query OK, 0 rows affected (0.02 sec)
Practical #4
1) Simple Query
mysql> select * from faculty;
+——+——–+———-+———+——-+
| ID | Name | DoB | Subject | Hours |
+——+——–+———-+———+——-+
| 101 | Amit | 24/12/72 | PM | 8 |
| 102 | Reva | 01/03/93 | PM | 8 |
| 103 | Sachin | 03/02/80 | OS | 8 |
| 103 | Sachin | 03/02/80 | OS | 8 |
| 104 | mahesh | 28/11/86 | DT | 16 |
| 105 | Nitin | 01/01/85 | p com | 10 |
| 106 | om | 07/07/85 | SS | 12 |
| 107 | Nisha | 12/11/85 | DBMS | 8 |
+——+——–+———-+———+——-+
8 rows in set (0.00 sec)
mysql> select * from faculty where ID = 106;
+——+——+———-+———+——-+
| ID | Name | DoB | Subject | Hours |
+——+——+———-+———+——-+
| 106 | om | 07/07/85 | SS | 12 |
+——+——+———-+———+——-+
1 row in set (0.00 sec)
2) Simple Query With Aggregate Function
mysql> select min(Hours) from faculty;
+————+
| min(Hours) |
+————+
| 8 |
+————+
1 row in set (0.01 sec)
mysql> select max(Hours) from faculty;
+————+
| max(Hours) |
+————+
| 16 |
+————+
1 row in set (0.00 sec)
mysql> select count(Hours) from faculty;
+————–+
| count(Hours) |
+————–+
| 8 |
+————–+
1 row in set (0.00 sec)
mysql> select sum(Hours) from faculty;
+————+
| sum(Hours) |
+————+
| 78 |
+————+
1 row in set (0.00 sec)
mysql> select avg(Hours) from faculty;
+————+
| avg(Hours) |
+————+
| 9.7500 |
+————+
1 row in set (0.00 sec)
mysql> select * from faculty where Name LIKE ‘ac%’;
Empty set (0.01 sec)
mysql> select * from faculty where Name LIKE ‘a%’;
+——+——+———-+———+——-+
| ID | Name | DoB | Subject | Hours |
+——+——+———-+———+——-+
| 101 | Amit | 24/12/72 | PM | 8 |
+——+——+———-+———+——-+
1 row in set (0.00 sec)
mysql> select * from faculty where Name LIKE ‘%ac%’;
+——+——–+———-+———+——-+
| ID | Name | DoB | Subject | Hours |
+——+——–+———-+———+——-+
| 103 | Sachin | 03/02/80 | OS | 8 |
| 103 | Sachin | 03/02/80 | OS | 8 |
+——+——–+———-+———+——-+
2 rows in set (0.00 sec)
mysql> select * from faculty where Name LIKE ‘%a__%’;
+——+——–+———-+———+——-+
| ID | Name | DoB | Subject | Hours |
+——+——–+———-+———+——-+
| 101 | Amit | 24/12/72 | PM | 8 |
| 103 | Sachin | 03/02/80 | OS | 8 |
| 103 | Sachin | 03/02/80 | OS | 8 |
| 104 | mahesh | 28/11/86 | DT | 16 |
+——+——–+———-+———+——-+
4 rows in set (0.00 sec)
mysql> select * from faculty where Name LIKE ‘a__%’;
+——+——+———-+———+——-+
| ID | Name | DoB | Subject | Hours |
+——+——+———-+———+——-+
| 101 | Amit | 24/12/72 | PM | 8 |
+——+——+———-+———+——-+
1 row in set (0.00 sec)
Practical #5
1)DATE FUNCTION
mysql> select now();
+———————+
| now() |
+———————+
| 2024-02-24 10:52:38 |
+———————+
1 row in set (0.00 sec)
mysql> select day (now());
+————-+
| day (now()) |
+————-+
| 24 |
+————-+
1 row in set (0.00 sec)
mysql> select month (now());
+—————+
| month (now()) |
+—————+
| 2 |
+—————+
1 row in set (0.01 sec)
mysql> select year (now());
+————–+
| year (now()) |
+————–+
| 2024 |
+————–+
1 row in set (0.00 sec)
mysql> select hour (now());
+————–+
| hour (now()) |
+————–+
| 10 |
+————–+
1 row in set (0.00 sec)
mysql> select minute (now());
+—————-+
| minute (now()) |
+—————-+
| 55 |
+—————-+
1 row in set (0.00 sec)
mysql> select second (now());
+—————-+
| second (now()) |
+—————-+
| 10 |
+—————-+
1 row in set (0.00 sec)
2) STRING FUNCTION
select CONCAT(‘Hello ‘,’World’);
+————————–+
| CONCAT(‘Hello ‘,’World’) |
+————————–+
| Hello World |
+————————–+
1 row in set (0.01 sec)
select LENGTH(‘Good Morning!’);
+————————-+
| LENGTH(‘Good Morning!’) |
+————————-+
| 13 |
+————————-+
1 row in set (0.01 sec)
mysql> select LOWER(‘UPPER CASE STRING’);
+—————————-+
| LOWER(‘UPPER CASE STRING’) |
+—————————-+
| upper case string |
+—————————-+
1 row in set (0.01 sec)
mysql> select UPPER(‘lower case string’);
+—————————-+
| UPPER(‘lower case string’) |
+—————————-+
| LOWER CASE STRING |
+—————————-+
1 row in set (0.00 sec)
mysql> select REVERSE(‘This is a string’);
+—————————–+
| REVERSE(‘This is a string’) |
+—————————–+
| gnirts a si sihT |
+—————————–+
1 row in set (0.00 sec)
mysql> select LTRIM(‘ Hello World’) AS LeftTrimmedString;
+——————-+
| LeftTrimmedString |
+——————-+
| Hello World |
+——————-+
1 row in set (0.00 sec)
mysql> select RTRIM(‘THis is a sentence with spaces. ‘) AS RightTrimmedString;
+———————————+
| RightTrimmedString |
+———————————+
| THis is a sentence with spaces. |
+———————————+
1 row in set (0.00 sec)
3) MATH FUNCTION
mysql> select ROUND(45.664);
+—————+
| ROUND(45.664) |
+—————+
| 46 |
+—————+
1 row in set (0.01 sec)
mysql> select CEILING(45.8);
+—————+
| CEILING(45.8) |
+—————+
| 46 |
+—————+
1 row in set (0.01 sec)
mysql> select FLOOR(45.8);
+————-+
| FLOOR(45.8) |
+————-+
| 45 |
+————-+
1 row in set (0.00 sec)
mysql> select ABS(-5);
+———+
| ABS(-5) |
+———+
| 5 |
+———+
1 row in set (0.00 sec)
mysql> select MOD(4589,100);
+—————+
| MOD(4589,100) |
+—————+
| 89 |
+—————+
1 row in set (0.00 sec)
mysql> select POW(2,8);
+———-+
| POW(2,8) |
+———-+
| 256 |
+———-+
1 row in set (0.00 sec)
mysql> select SQRT(16);
+———-+
| SQRT(16) |
+———-+
| 4 |
+———-+
1 row in set (0.00 sec)
Practical #6
mysql> select * from orders;
+———+————-+——+
| OrderID | OrderNumber | ID |
+———+————-+——+
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 2 |
| 4 | 24562 | 1 |
+———+————-+——+
4 rows in set (0.01 sec)
mysql> select * from persons;
+—-+———–+———–+——+
| ID | LastName | FirstName | Age |
+—-+———–+———–+——+
| 1 | Hasen | Ola | 30 |
| 2 | Svendson | Tove | 23 |
| 3 | Petterson | Kari | 20 |
+—-+———–+———–+——+
3 rows in set (0.01 sec)
1) INNER JOIN
mysql> select orders.OrderNumber, persons.FirstName, orders.OrderID FROM orders INNER JOIN persons ON orders.ID=persons.ID;
+————-+———–+———+
| OrderNumber | FirstName | OrderID |
+————-+———–+———+
| 24562 | Ola | 4 |
| 22456 | Tove | 3 |
| 77895 | Kari | 1 |
| 44678 | Kari | 2 |
+————-+———–+———+
4 rows in set (0.01 sec)
2) LEFT (OUTER) JOIN
mysql> select orders.OrderNumber, persons.FirstName, orders.OrderID FROM orders LEFT JOIN persons ON orders.ID=persons.ID;
+————-+———–+———+
| OrderNumber | FirstName | OrderID |
+————-+———–+———+
| 77895 | Kari | 1 |
| 44678 | Kari | 2 |
| 22456 | Tove | 3 |
| 24562 | Ola | 4 |
+————-+———–+———+
4 rows in set (0.01 sec)
3) RIGHT (OUTER) JOIN
mysql> select orders.OrderNumber, persons.FirstName, orders.OrderID FROM orders RIGHT JOIN persons ON orders.ID=persons.ID;
+————-+———–+———+
| OrderNumber | FirstName | OrderID |
+————-+———–+———+
| 24562 | Ola | 4 |
| 22456 | Tove | 3 |
| 77895 | Kari | 1 |
| 44678 | Kari | 2 |
+————-+———–+———+
4 rows in set (0.00 sec)
Practical #7
mysql> select * from student;
+——-+——–+——+————+————+——+——–+
| Sr_No | Name | Age | Department | DOA | Fee | Gender |
+——-+——–+——+————+————+——+——–+
| 1 | Piyush | 24 | Computer | 1997-01-10 | 120 | Male |
| 2 | Shivam | 21 | History | 1998-03-24 | 200 | Male |
| 3 | Sudha | 22 | Hindi | 1996-12-12 | 300 | Female |
| 4 | Surya | 25 | History | 1999-07-01 | 400 | Male |
| 5 | Rohit | 22 | Hindi | 1997-09-05 | 250 | Male |
| 6 | Mohit | 30 | History | 1998-06-27 | 300 | Male |
| 7 | Sneha | 34 | Computer | 1997-02-25 | 210 | Female |
| 8 | Yash | 23 | Hindi | 1997-07-31 | 200 | Male |
+——-+——–+——+————+————+——+——–+
8 rows in set (0.01 sec)
1) With IN Clause
mysql> select * from student where Department IN (‘History’,’Computer’);
+——-+——–+——+————+————+——+——–+
| Sr_No | Name | Age | Department | DOA | Fee | Gender |
+——-+——–+——+————+————+——+——–+
| 1 | Piyush | 24 | Computer | 1997-01-10 | 120 | Male |
| 2 | Shivam | 21 | History | 1998-03-24 | 200 | Male |
| 4 | Surya | 25 | History | 1999-07-01 | 400 | Male |
| 6 | Mohit | 30 | History | 1998-06-27 | 300 | Male |
| 7 | Sneha | 34 | Computer | 1997-02-25 | 210 | Female |
+——-+——–+——+————+————+——+——–+
5 rows in set (0.00 sec)
mysql> select * from student where Department NOT IN (‘History’,’Computer’);
+——-+——-+——+————+————+——+——–+
| Sr_No | Name | Age | Department | DOA | Fee | Gender |
+——-+——-+——+————+————+——+——–+
| 3 | Sudha | 22 | Hindi | 1996-12-12 | 300 | Female |
| 5 | Rohit | 22 | Hindi | 1997-09-05 | 250 | Male |
| 8 | Yash | 23 | Hindi | 1997-07-31 | 200 | Male |
+——-+——-+——+————+————+——+——–+
3 rows in set (0.00 sec)
2)With EXISTS Clause
mysql> select FirstName, LastName, OrderTotal from persons where EXISTS (select OrderNumber from orders where persons.ID = Orders.ID AND OrderTotal < 600);
+———–+———-+————+
| FirstName | LastName | OrderTotal |
+———–+———-+————+
| Tove | Svendson | 510 |
+———–+———-+————+
1 row in set (0.00 sec)
mysql> select FirstName, LastName, OrderTotal from persons where NOT EXISTS (select OrderNumber from orders where persons.ID = Orders.ID AND OrderTotal < 600);
+———–+———–+————+
| FirstName | LastName | OrderTotal |
+———–+———–+————+
| Ola | Hasen | 850 |
| Kari | Petterson | 1440 |
+———–+———–+————+
2 rows in set (0.00 sec)
Practical #8
1)Views
mysql> select * from faculty;
+——+——–+———-+———+——-+
| ID | Name | DoB | Subject | Hours |
+——+——–+———-+———+——-+
| 101 | Amit | 24/12/72 | PM | 8 |
| 102 | Reva | 01/03/93 | PM | 8 |
| 103 | Sachin | 03/02/80 | OS | 8 |
| 103 | Sachin | 03/02/80 | OS | 8 |
| 104 | mahesh | 28/11/86 | DT | 16 |
| 105 | Nitin | 01/01/85 | p com | 10 |
| 106 | om | 07/07/85 | SS | 12 |
| 107 | Nisha | 12/11/85 | DBMS | 8 |
+——+——–+———-+———+——-+
8 rows in set (0.00 sec)
- CREATING VIEW WITHOUT CHECK OPTION
mysql> create view OS_Subject AS select ID, Name, Subject, Hours from faculty WHERE Subject = ‘OS’;
Query OK, 0 rows affected (0.01 sec)
- CREATING VIEW WITH CHECK OPTION
create view PM_Subject AS select ID, Name, Subject, Hours from faculty WHERE Subject = ‘PM’ WITH CHECK OPTION;
Query OK, 0 rows affected (0.01 sec)
- DROPPING VIEW
mysql> drop view PM_Subject;
Query OK, 0 rows affected (0.01 sec)
- SELECTION FROM A VIEW
mysql> select * from OS_Subject;
+——+——–+———+——-+
| ID | Name | Subject | Hours |
+——+——–+———+——-+
| 103 | Sachin | OS | 8 |
| 103 | Sachin | OS | 8 |
+——+——–+———+——-+
2 rows in set (0.00 sec)
Practical #9
DCL STATEMENTS
CREATING A USER IM MYSQL
mysql> create user ‘devraj_ADMIN’@’localhost’ IDENTIFIED BY ‘Blue@Balls’;
Query OK, 0 rows affected (0.03 sec)
1) GRANT AND REVOKE STATEMENTS
mysql> GRANT SELECT, INSERT ON dv10.* TO ‘devraj_ADMIN’@’localhost’;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL ON dv10.* TO ‘devraj_ADMIN’@’localhost’;
Query OK, 0 rows affected (0.01 sec)
mysql> REVOKE SELECT, INSERT ON dv10.* FROM ‘devraj_ADMIN’@’localhost’;
Query OK, 0 rows affected (0.01 sec)
mysql> REVOKE ALL ON dv10.* FROM ‘devraj_ADMIN’@’localhost’;
Query OK, 0 rows affected (0.01 sec)
2) SAVING (COMMIT) AND UNDOING(ROLLBACK)
mysql> create table customer( a INT, b varchar(10), INDEX(a));
Query OK, 0 rows affected (0.06 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into customer VALUES( 1, ‘Devraj’);
Query OK, 1 row affected (0.01 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)
mysql> SET autocommit= 0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into customer VALUES( 2, ‘Shubham’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into customer VALUES( 3, ‘Ayush’);
Query OK, 1 row affected (0.00 sec)
mysql> DELETE from customer WHERE b = ‘Shubham’;
Query OK, 1 row affected (0.01 sec)
mysql> ROLLBACK; #UNDOING THE DELETION OF DATA
Query OK, 0 rows affected (0.01 sec)
mysql> select * from customer;
+——+——–+
| a | b |
+——+——–+
| 1 | Devraj |
+——+——–+
1 row in set (0.00 sec)
Practical #10
1) Creating INDEXES on data tables
- CREATING INDEX
mysql> create INDEX ID_Sr ON faculty (ID);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
- DROPPING INDEX
mysql> ALTER TABLE faculty DROP INDEX ID_Sr;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0