MySQL Practice

SQL Practice:
1. create database <database_name>;

Example: create database student;
2. show databases;
3. use <database_name>
Example: use student;
4. create table <table_name> (attribute_name1 datatype1,…………………..…n)
Example: create table parent(pid char(10),pname varchar(30),paddress varchar(30));
Example: create table student(sno int, sadmno int,sname varchar(30), sclass char(3),dob date,pid char(10),scode char(10));
5. show tables;
6. describe <table_name>; (or) desc <table_name>;
Example: describe student (or) desc student;
Example: describe parent (or) desc parent;
7. alter table <table_name> add primary key(attribute_name);
Example: alter table student add primary key(sno);
8. alter table <table_name> add unique (attribute_name);
Example: alter table student add unique (sadmno);
9. alter table <table_name> add <new attribute_name> <datatype>;
Example: alter table parent add pphone int;
10. alter table <table_name> change <old attribute_name> <new attribute_name> <datatype>;
Example: alter table student change sno srollno int;
11. alter table <table_name> modify <attribute_name> <new datatype>;
Example: alter table parent modify pphone char(12);
12. alter table <table_name> modify <attribute_name> <datatype> default <default_value>;
Example: alter table student modify scode char(10) default ‘130163’;
13. alter table <table_name> modify <Attribute_name> <datatype> not null;
Example: alter table student modify sadmno int not null;
alter table <table_name> modify <Attribute_name> <datatype> null;
Example: alter table student modify sadmno int null;
14. alter table <table_name> rename <new table_name>;
Example: alter table student rename stud;
15. alter table <table_name> drop <attribute_name>;
Example: alter table stud drop pid;
16. drop table <table_name>;
Example: drop table parent;
17. drop database <database_name>;
drop database stud;
18. insert into <table_name> values(attribute_value1, attribute_value2,……………….n);
Example: insert into stud values(12,2304,’kireeti’,’XII’,’12-09-2012’,default);
19. select * from <table_name>;

Example: select * from stud;
20. select attribute_name1, attribute_name2,………………………..……….n from <table_name>;
Example: select sadmno,sname,sclass from stud;
21. select <old attribute_name> as <new attribute_name> from <table_name>;
Example: select sname as stud_name from stud;
22. select distinct <attribute_name> from <table_name>;
Example: select distinct sclass from stud;
23. select * from <table_name> where <condition>;
Example: select * from stud where srollno>4;
24. select * from <table_name> where <attribute> between initial value and final value;
Example: select * from stud where srollno between 4 and 8;
select * from <table_name> where <attribute> not between initial_value and final_value;
Example: select * from stud where srollno between 4 and 8;
25. select * from <table_name> order by <attribute_name> desc;
Example: select * from stud order by srollno desc;
select * from <table_name> order by <attribute_name> asc; (or) select * from <table_name> order by <attribute_name>;
Example: select * from stud order by srollno asc (or) select * from stud order by srollno;
26. select <attribute_name> ,<aggregate_function>from <table_name> group by <attribute_name>;
Example: select sclass,count(sclass) from stud group by sclass;
select <attribute_name> ,<aggregate_function>from <table_name> group by <attribute_name> having <condition>;
Example: select sclass,count(sclass) from stud group by sclass having sclass=’XII’;
27. select * from <table_name> where <attribute_name> in (value1, valu2,……..value_n);
Example: select * from stud where sname in (‘ravi’,’kireeti’,’ramu’);
select * from <table_name> where <attribute_name> not in (value1, valu2,……..value_n);
Example: select * from stud where sname in (‘ravi’,’kireeti’,’ramu’);
28. select * from <table_name> where <attribute_name> is null;
Example: select * from stud where sadmno is null;
select * from <table_name> where <attribute_name> is not null;
Example: select * from stud where sadmno is not null;
29. select * from <table_name> where <attribute_name> like “pattern with characters”;
Example: select * from stud where sname like “%ar%”;
Example: select * from stud where sname like “_ag_%”;
select * from <table_name> where <attribute_name> not like “pattern with characters”;
Example: select * from stud where sname not like “%ar%”;
Example: select * from stud where sname not like “_ag_%”;
30. update <table_name> set <attribute1=value1,attribute2=value2,………………….…………n> where <condition>;
Example: update stud set sadmno=2124,dob=”2012-11-23” where srollno=23;
31. delete from <table_name> where <condition>;
Example: delete from stud where srollno=5;
32. create table customer(c_id int not null primary key, c_name varchar(30),c_mobile varchar(10));
desc customer;
create table orders(order_id int not null primary key, order_date date,customer_id int, amount float, foreign key(customer_id)
references customer(c_id));
desc orders;
insert into orders values(1005,'2022-09-20',6,3000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sales`.`orders`, CONSTRAINT `orders_ibfk_1`
FOREIGN KEY (`customer_id`) REFERENCES `customer` (`c_id`))

MySQL Practice

Uploaded by

Copyright:

Available Formats

MySQL Practice

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

MySQL Practice

Uploaded by

Copyright:

Available Formats

SQL Practice:

1. create database <database_name>;

19. select * from <table_name>;

You might also like