drop table personal;

select * FROM students;

create table students

id int not null auto_increment,

name varchar(50) not null,

age int not null,

city varchar(10) not null,

primary key (id)

);

insert into students (id, name, age, city)

values

(1, 'Ram Kumar', '19', 'Agra'),

(2, 'Salman Khan', '18', 'Bhopal'),

(3, 'Meera Khan', '19', 'Agra'),

(4, 'Sarita Kumari', '21', 'Delhi');

drop table city;

create table city

cid int not null auto_increment,

cityname varchar(50) not null,

primary key (cid)

);

select * FROM city;

drop table city;

insert into city (cityname)

values

('Agra'),

('Bhopal'),

('Jaipur'),

('Noida'),

('Delhi');

drop table students;

select * FROM students;

create table students

id int not null,

name varchar(50) not null,

age int not null,

city int not null,

primary key (id),

foreign key (city) references city(cid)

);

insert into students (id, name, age, city)

values

(1, 'Ram Kumar', 19, 1),

(2, 'Salman Khan', 18, 2),

(3, 'Meera Khan', 19, 1),

(4, 'Sarita Kumari', 21, 3),

(5, 'Anil Kapoor', 22, 1),

(6, 'John Abraham', 21, 2),

(7, 'Shahid Kapoor', 20,1);

SELECT * FROM personal;

create table personal

id INT NOT NULL UNIQUE,

name VARCHAR(50) NOT NULL,

percentage INT NOT NULL,

age INT NOT NULL,

gender CHAR(1) NOT NULL,

phone VARCHAR(10) NOT NULL UNIQUE,

city VARCHAR(50) NOT NULL DEFAULT "Dehradun"

);

insert into personal (id, name, percentage, age, gender, phone, city)

VALUES

(1, "Ram Kumar", "58", "19", "M", "9898988898", "Agra"),

(2, "Ram Kumar", "56", "19", "M", "9898989898", "Agra"),

(3, "Sarita Kumari", "62", "20", "F", "9797979797", "Agra"),

(4, "Salman Khan", "47", "20", "M", "9898989899", "Delhi"),

(5, "Juhi Chawla", "74", "2", "F", "9898989888", "Bhopal"),

(6, "Anil Kapoor", "64", "21", "M", "9898989887", "Agra"),

(7, "Jhon Abraham", "52", "27", "M", "8898989887", "Agra"),

(8, "Shahid Kapoor", "62", "51", "M", "8898989898", "Bhopal"),

(9, "Jitendra", "74", "52", "M", "7898989898", "Agra"),

(10, "Rishi Kapoor", "98", "48", "M", "9898989898", "Dehradun"),

(11, "Anil Singh", "97", "27", "M", "9998989898", "Dehradun"),

(12, "Prakash Tomar", "91", "62", "M", "9898999898", "Agra"),

(13, "Amit Kumar", "33", "42", "M", "9898989867", "Delhi"),

(14, "Ram Babu", "25", "19", "M", "9898989865", "Agra"),

(15, "Divya Rani", "10", "21", "F", "9898978898", "Bhopal"),

(16, "Varsha Singh", "58", "27", "F", "9898569898", "Agra"),

(17, "Hemant Bisht", "98", "38", "M", "9898990898", "Agra");

select * FROM employee

create table employee

name VARCHAR(50) not null,

age int not null check (age>=18),

gender varchar(6) not null,

Salary int not null

);

insert into employee (name, age, gender, Salary)

VALUES

("Ram Kumar", "19", "Male", 4500),

("Salman Khan", "18", "female", 5200),

("Meera Khan", "20", "female", 6000),

("Sarita Kumari", "21", "Male", 8500),

("Anil Kapoor", "20", "Male", 6300),

("Shahid Kapoor", "19", "Male", 4800),

("Virat Kholi", "21", "Male", 5700);

SELECT * FROM personal;

SELECT id, name, city FROM personal;

SELECT id AS ID, name AS "Student Name", city FROM personal;

SELECT Data with WHERE Clause (=,>,<,>=,<=,<> or !=, between, like, in)

SELECT * FROM personal WHERE gender="M";

SELECT * FROM personal WHERE age<21;

SELECT * FROM personal WHERE city != "Dehradun";

SELECT Data with AN & OR OPERATORS

SELECT * FROM personal WHERE age>=18 AND age<=21;

SELECT * FROM personal WHERE age=18 OR city="Agra";

SELECT * FROM personal WHERE (city="Agra" OR city="Bhopal") AND gender="M";

SELECT * FROM personal WHERE age>=18 AND gender="M";

SELECT * FROM personal WHERE age>=18 AND gender="M" AND city="Agra";

SELECT * FROM personal WHERE NOT city="Bhopal" OR city = "Agra";

SELECT * FROM personal WHERE NOT (city="Bhopal" OR city = "Agra");

SELECT * FROM personal WHERE NOT age>=20;

SELECT data with IN operator

SELECT * FROM personal WHERE age IN(18,21,25);

SELECT * FROM personal WHERE age NOT IN(18,21);

SELECT * FROM personal WHERE city IN ("Delhi", "Bhopal");

SELECT * FROM personal WHERE city NOT IN ("Delhi", "Bhopal");

SELECT data with BETWEEN, NOT BETWEEN operator

SELECT * FROM personal WHERE age BETWEEN 18 AND 20;

SELECT * FROM personal WHERE age NOT BETWEEN 18 AND 20;

SELECT * FROM personal WHERE id BETWEEN 2 AND 5;

SELECT * FROM personal WHERE id NOT BETWEEN 2 AND 5;

SELECT * FROM personal WHERE name BETWEEN "a" AND "k";

SELECT * FROM persons WHERE birth_date BETWEEN "1995=01-01" and "1995-06-30";

SELECT data with LIKE operator

% sign represent zero, one or multiple characters

_ sign represent single characters

SELECT * FROM personal WHERE name LIKE “s%”;

SELECT * FROM personal WHERE name LIKE “%ay”;

SELECT * FROM personal WHERE name LIKE “a%a”;

SELECT * FROM personal WHERE name LIKE “%ni%”;

SELECT * FROM personal WHERE name LIKE “r%” OR name LIKE “s%”;

SELECT * FROM personal WHERE name NOT LIKE “r%”;

SELECT * FROM personal WHERE BINARY name LIKE “r%”;

SELECT * FROM personal WHERE BINARY name LIKE “%r”;

SELECT * FROM personal WHERE phone LIKE “%14”;

SELECT * FROM personal WHERE phone NOT LIKE “%14”;

SELECT * FROM personal WHERE NAME LIKE “_un%”;

SELECT * FROM personal WHERE NAME LIKE “s_n%”;

SELECT data with Regular Expression

Sign Pattern Description

^ ‘^ra’ Beginning of String

$ ‘an$’ End of string

[…] ‘[rms]’ Any character listed between the square brackets

^[…] ‘^[rms]’ Begins with Any character listed between the square brackets
[a-z] ‘[a-h]e’ Match with in the range

p1|p2|p3 ‘tom|dick|harry’ Matches any of the patterns p1,p2, or p3

SELECT * FROM personal WHERE NAME REGEXP “sing$ | mari”;

SELECT * FROM personal WHERE NAME REGEXP ‘oh’;  Rohit: show all in string which will contain oh letter

SELECT * FROM personal WHERE NAME REGEXP ‘^vi ’;  Vijay: Beginning with Vi

SELECT * FROM personal WHERE NAME REGEXP ‘it$ ’;  Rohit: End with it

SELECT * FROM personal WHERE NAME REGEXP ‘ram| Kapoor| Khan ’;  Show all string which will contain ram
or Kapoor or khan

SELECT * FROM personal WHERE NAME REGEXP ‘^ram| poor| Khan$ ’;  Show all string which will contain ram
at beginging or poor or khan at last of the string.

SELECT * FROM personal WHERE NAME REGEXP ‘[is]’;  Show all string which will contain i or s in string
anywhere.

SELECT * FROM personal WHERE NAME REGEXP ‘[rm]a’;  check all and make combination of letter which
will contain ra or ma in a string.

SELECT * FROM personal WHERE NAME REGEXP ‘^[rs]’;  Show all string which will contain r or s starting of the
stirng.

SELECT * FROM personal WHERE NAME REGEXP ‘[rs]$’;  Show all string which will contain r or s ending of the
stirng.

SELECT * FROM personal WHERE NAME REGEXP ‘r[am]’;  it will make combination of all where ra or rm
is coming in the string.

SELECT * FROM personal WHERE NAME REGEXP ‘[a-j]r’; it will check all letter between a to j and also contain r
in the string.

SELECT data with ORDER BY or with DISTINCT

SELECT * FROM personal WHERE city =”Agra” ORDER BY name DESC;

SELECT * FROM personal WHERE city =”Agra” ORDER BY name;  Will show ascending order name by default

SELECT * FROM personal ORDER BY age;  Will show ascending order age by default

SELECT * FROM personal ORDER BY name, city;  Will show ascending order name first and then city

SELECT DISTINCT city FROM personal;  Will show distinct city that no duplicate

SELECT DISTINCT age FROM personal;  Will show distinct age that no duplicate

SELECT DISTINCT age FROM personal ORDER BY age;  Will show distinct age that no duplicate in ascending
order

SELECT data with IS NULL or with IS NOT NULL

SELECT * FROM persons WHERE birth_date IS NULL;  Will show record of only those who have birth date null

SELECT * FROM persons WHERE name IS NULL;  Will show record of only those who have name is null

SELECT * FROM persons WHERE name IS NOT NULL;  Will show record of only those who have birth date not
null

SELECT data with LIMIT or OFF SET

SELECT * FROM personal WHERE city LIMIT 3;  it will show only 3 first record

SELECT * FROM personal WHERE city=”Agra” ORDER BY name LIMIT 3;  it will show only 3 first record order by
name where city is equal to Agra

SELECT * FROM personal WHERE name LIMIT 5;

LIMIT 3, 3  Here first 3 is OFFSET number and next 3 is LIMIT number

SELECT * FROM personal LIMIT 0, 3;

SELECT * FROM personal LIMIT 3, 3;

SELECT * FROM personal LIMIT 4, 3;

SELECT Data with AGGREGATE FUNCTIONS

COUNT(), MAX(), MIN(), SUM(), AVG()

SELECT COUNT (name) FROM personal ;  It will count the number of total row of name coloumn

select count(distinct city) FROM personal;  It will count the unique city total number

select count(distinct city) as "TOTAL COUNT" FROM personal;

select max(percentage) as "Maximum Percentage" FROM personal;

select min(percentage) as "Maximum Percentage", name, city FROM personal;

select sum(salary) as "Total Salary" FROM employee;

select avg(salary) as "Average" FROM employee;

Update Command

update personal set city = "Dehradun" where id = 1;

update personal set age=18 where id=5;

update personal set age=18, name="Hero", city="Mumbai" where id=9;

update personal set city="Dehradun" where id in(2,5);

Commit and Roll back

Note: Commit & Rollback works for INSERT, UPDATE & DELETE command only.

Commit means save to disk permnanently

Rollback means undo

Begin: For both roll back and commit first you have to use begin first then you have to commit or rollback.

If we will rollback it will rollback all command so before it we should run commit command to save previous
changes after that we can insert, update or delete it will rollback whole command . Commit command is used for
permanent save.

begin;

commit;

rollback;
Delete Command

delete FROM personal where gender=’F’;

delete FROM personal where id=’12’;

delete FROM personal where age>20;

Primary key & Foreign Key Command

List of Constraints in MySQL are:

NOT NULL, UNIQUE, DEFAULT, CHECK, PRIMARY KEY, FOREIGEN KEY

alter table table_name add primary key (column name);

table table_name add foreign key (column name) references Next_tabel_Name (column name);

What is PRIMARY KEY Constraint?

 Primary key always has unique data.

 A primary key cannot have null value.
 A table can contain only one primary key constraint.

What is FOREIGN KEY Constraint?

 A foreign key is a key used to link two tables together.

 A foreign key in one table used to point primary key in another table.

ALTER COMMAND:

With ALTER command we can do the following:

 Add column in a table

 Changing data type of a column
 Change column name
 Adding constraints to a column
 Changing column position
 Delete column
 Renaming Tables

For:
Add column Command will be:

alter table table_name add column_name datatype;

Modify existing column:

alter table table_name modify column_name datatype;

Delete column:

alter table table_name drop column column_name datatype;

Rename column:

alter table table_name change column_name New_Name datatype;

Rename Table:

alter table table_name rename New_table_name;

alter table students add email varchar(255);  it will add new column email to existing student table

alter table students modify email varchar(255) after name;  it will change the order of the email column and
bring after name column of students table.

alter table students modify email int(10);  it will change the datatype of email column students table.

alter table students add unique (Email);  it will change email column to unique

alter table table_name change existing_coloumn_name New_column_name datatype;

alter table students change Email Email_id varchar(255);

alter table table_name drop column column_name;

alter table students drop column Email_id;

alter table existing_table_name rename New_table_name;

alter table students rename studentssss;

alter table courses auto_increment=4;

Drop and Truncate command

Drop table table_name;

Truncate table table_name;