DATABASE MANAGEMENT
1. Create Database
Command:-
Create database class12_css;
Output:-
2. Use database
Commands:-
mysql> use class12_cs;
Output:
3. Create table
Command:-
mysql> create table students
-> (Roll_No int, Name varchar(20), Gender varchar(5), Marks int, Scode
int);
Query OK, 0 rows affected (0.07 sec)
Output:-
4. Insert data into table
Command:-
Output:-
5. Searching name of list given between command
Command:-
mysql> select name from students where marks between 50 and 80;
Output:-
6. Alter command (Add attribute):-
Command:-
mysql> alter table students add column (Subject varchar(10), Year int);
Output:-
7. Alter command (Modify the table)
Command:-
mysql> alter table students drop subject;
Output:-
8. Alter command (Modify database)
Previous attribute:-
Command:-
mysql> alter table students modify marks decimal;
Output:-
9. Update table to Modify data
Command:-
mysql> update students set year = 2022;
Output:-
10. Update table based on condition:-
Previous:-
Command:-
mysql> update students set year = 2021 where scode =325;
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
Output:-
11. ORDER By to display data in Ascending / Descending order
Ascending order Command:-
mysql> select * from students order by name Asc;
Output:-
Descending order Command:-
mysql> select * from students order by name Desc;
Output:-
12. Apply condition in descending order(ORDER By)
Command:- mysql> select name, marks from students order by marks Desc;
Output:-
13. Delete to remove tuples
Previous:-
Command:-
mysql> delete from students where marks = 65;
Query OK, 1 row affected (0.02 sec)
Output:-
14. GROUP By
Command:-
mysql> select gender, count(*) from students group by gender;
Output:-
15. GROUP By and HAVING command:
Command:-
mysql> select marks, count(*) from students group by marks having
count(*)<2;
Output:-
16. Max function
Command:-
mysql> select max(marks) from students;
Output:-
17. Find the min from table
Command:-
mysql> select min(marks) from students;
Output:-
18. Find the sum
Command:-
mysql> select sum(marks) from students;
Output:-
19. Count function
Command:-
mysql> select gender, count(*) from students;
Output:-
20. Average function
Command:-
mysql> select avg(marks) from students;
Output:-
21. Group By and Count function:
Command:-
mysql> select marks, count(*) from students group by marks;
Output:-
Other command:-
mysql> select gender, count(*) from students group by gender;
Output:-
22. Creating New table:
Syntax:-
mysql> create table streams
-> (Name varchar(20),
-> Stream varchar(10),
-> School varchar(10)
-> );
Query OK, 0 rows affected (0.08 sec)
Output:-
23. Inserting values into streams table:
24. Snapshot after inserting the values:
Command:-
mysql> select * from streams;
Output:-
25. Deleting column using drop command:
Command:- mysql> alter table streams drop school;
Output:-
26. Cartesian product of two tables:
Command:-
mysql> select * from students, streams;
Output:-
27. Joining of two tables:
Command:-
mysql> select Scode, stream from students, streams where
[Link]=[Link];
Output:-
28. Searching a specific result by joining list:
Command:- mysql> select Scode, stream from students, streams where
[Link]=[Link] and scode='455';
Output:-
29. Searching multiple result by joining list:
Command:- mysql> select Scode,Gender, marks, stream from students,
streams where [Link]=[Link] and marks>85 ;
Output:-