create database sampledb;

show databases;
use sampledb;
show tables;
create table Student(
id int primary key auto_increment,
s_name varchar(50) not null,
age int not null default 18,
courseId int not null,
constraint fk_course foreign key (courseId) references course(courseid)
);

alter table student auto_increment=201;

desc Student;

create table course(

courseid int primary key auto_increment,
coursename varchar(50) not null
);

desc course;

alter table Student add column courseid int not null;

alter table Student add constraint foreign key(courseid) references
course(courseid);

alter table course alter column courseid set default 100;

create table dept(

deptid int primary key,
dept_name varchar(50) not null
);
create table emp(
id int primary key,
firstname varchar(50) not null,
lastname varchar(50) not null,
desgination varchar(50) not null,
dept_id int not null,
constraint fk_dept foreign key(dept_id) references dept(deptid)
);

rename table emp to employee;

alter table employee modify column desgination varchar(30);
alter table employee rename column desgination to job;
alter table employee add column doj date not null;
alter table employee add column salary double not null;
-- alter table emp drop constraint fk_dept;

alter table employee add column email_id varchar(50) not null;

alter table employee add constraint unique(email_id);

desc employee;

-- drop table emp;

select column_name, constraint_name, referenced_column_name, referenced_table_name

from information_schema.key_column_usage
where table_name='student';
-- drop database sampledb;
--
-----------------------------------------------------------------------------------
--------
-- DML
--
-----------------------------------------------------------------------------------
--------
insert into dept values(1001, 'QA');
insert into dept values (1002, 'Accounts'),
(1003, 'Management'), (1004, 'Development'), (1005, 'Design'), (1006,
'Maintenance');

Select * from dept;

insert into employee values (3001, 'Prithvi', 'Raj', 'Accountant', 1002, 'lsfjk',
curdate());
insert into employee values (3002, 'Kanchan', 'K', 'Developer', 1004, 'dfgk',
'2020-12-01');
insert into employee values (3003, 'Manoj', 'M', 'Accountant', 1002, 'sdfsdf',
'2020-10-20');
insert into employee values (3004, 'Nayan', 'T', 'Tester', 1001, 'ddes', '2020-09-
09');
select * from employee;

insert into course(coursename) values ('Java');

insert into course(courseid, coursename) values (101, 'MySQL');
insert into course(courseid, coursename) values (102, 'Python');

select * from course;

insert into student(s_name, courseid) values ('Prithvi', 100);

insert into student values (203, 'Nayan', 19, 101);
insert into student(courseid, age, s_name) values (101, 20, 'Lavanya');
insert into student set id=206, courseid=100, s_name='Janhavi';

select * from student;

update employee set doj='2020-11-02' where id=3001;
update employee set salary=23451.00 where dept_id=1002;

-- truncate table student;

-- drop table student;
-- commit;

delete from student; -- deletes all the rows

delete from student where courseid=101; -- specific row(s) is deleted

create table product (

productId int primary key,
p_desc varchar(255) not null,
price decimal(10,2) not null check(price >= 100.00)
);

alter table product add column category varchar(30) not null;

insert into product values(101, 'android charger', 150.00, 'Accessory');

insert into product values(102, 'dell laptop', 58000.00, 'Laptop');
insert into product values(103, 'boat earphones', 2500.00, 'Accessory');
insert into product values(104, 'note 9 case', 250.00, 'Accessory');
select * from product;
-- delete from product;
--
-----------------------------------------------------------------------------------
-----------
-- SELECT STATEMENTS------------------------------
--
-----------------------------------------------------------------------------------
-----
select * from student;
select courseid, s_name, age from student;
select courseid, s_name, age from student where courseid=100;
select courseid, s_name, age from student where age in (18, 19);
select courseid, s_name, age from student where age >=18 and age <= 19;
select courseid, s_name, age from student where age not between 18 and 19;

select now();
select curdate();
select concat('Pallavi', ' ', 'Prasad');

select courseid, s_name, age from student order by s_name desc;

select courseid as 'Course Id', s_name as 'Student Name', age as Age

from student
where courseid=100
order by s_name;

select c.coursename, s.s_name, s.age from student s, course c

where c.courseid = s.courseid;

select course.coursename, s.s_name, s.age from student s, course

where course.courseid = s.courseid and course.courseid=100;

select c.coursename as 'Course', s.s_name as 'Student Name', s.age from student s,

course c
where c.courseid = s.courseid and c.courseid=100
order by s.s_name;

select * from student where s_name LIKE 'P%';

select * from student where s_name NOT LIKE '%i%';

select category, sum(price) as 'Total Value' from product where

category='Accessory';
select category, Max(price) as 'Highest' from product where category='Accessory';
select category as Category, count(*) as 'Products'
from product group by category;
select category, sum(price) as 'Total' from product
group by category
having Total > 50000.00;

select left(p_desc, 4) from product;

select s_name from student where instr(s_name, 'i') > 0 and instr(s_name, 't') > 0;
select s_name from student where s_name like '%i%' and s_name like '%t%';
select s_name, length(s_name) from student;
select s_name, replace(s_name, 'i', 'u') from student;
update student set s_name=replace(s_name, 'y', 'i');
select p_desc, locate('ro', p_desc) from product;
select p_desc, instr(p_desc, 'ro') from product;
select p_desc, price, substring(p_desc, 4, 6) from product where price > 2500.00;
select p_desc, substring(p_desc, instr(p_desc, 'o'), length(p_desc)) from product;

-- select category as Category, brand as Brand, count(*) as 'Num of Products'

-- from product
-- where price between 50000.00 and 200000.00
-- group by category, brand;

-- ------------------------------------------------------------------------------
use classicmodels;
select count(*) from customers;
select * from customers LIMIT 10; -- shows the top 10 records
select * from customers LIMIT 100, 5; -- shows 5 records starting from the 101th
select * from customers where country='USA' limit 10;
select customerNumber, customerName, concat(contactFirstName, ' ', contactLastName)
as 'Contact', creditLimit
from customers where country='Australia'
order by creditLimit desc
limit 5;

select customerNumber, customerName,

concat(contactFirstName, ' ', contactLastName) as 'Contact', max(creditLimit) as
Highest
from customers;

-- substring example
select substring('subsring examp', instr('subsring examp', 'r'), length('subsring
examp'));
-----------------------------------------------------------------------------------
----------------------------------------
create database sampledb;
show databases;
use sampledb;
show tables;
create table Student(
id int primary key auto_increment,
s_name varchar(50) not null,
age int not null default 18,
courseId int not null,
constraint fk_course foreign key (courseId) references course(courseid)
);

alter table student auto_increment=201;

desc Student;

create table course(

courseid int primary key auto_increment,
coursename varchar(50) not null
);

desc course;

alter table Student add column courseid int not null;

alter table Student add constraint foreign key(courseid) references
course(courseid);

alter table course alter column courseid set default 100;

create table dept(
deptid int primary key,
dept_name varchar(50) not null
);
create table emp(
id int primary key,
firstname varchar(50) not null,
lastname varchar(50) not null,
desgination varchar(50) not null,
dept_id int not null,
constraint fk_dept foreign key(dept_id) references dept(deptid)
);

rename table emp to employee;

alter table employee modify column desgination varchar(30);
alter table employee rename column desgination to job;
alter table employee add column doj date not null;
alter table employee add column salary double not null;
-- alter table emp drop constraint fk_dept;

alter table employee add column email_id varchar(50) not null;

alter table employee add constraint unique(email_id);

desc employee;

-- drop table emp;

select column_name, constraint_name, referenced_column_name, referenced_table_name

from information_schema.key_column_usage
where table_name='student';

-- drop database sampledb;

--
-----------------------------------------------------------------------------------
--------
-- DML
--
-----------------------------------------------------------------------------------
--------
insert into dept values(1001, 'QA');
insert into dept values (1002, 'Accounts'),
(1003, 'Management'), (1004, 'Development'), (1005, 'Design'), (1006,
'Maintenance');

Select * from dept;

insert into employee values (3001, 'Prithvi', 'Raj', 'Accountant', 1002, 'lsfjk',
curdate());
insert into employee values (3002, 'Kanchan', 'K', 'Developer', 1004, 'dfgk',
'2020-12-01');
insert into employee values (3003, 'Manoj', 'M', 'Accountant', 1002, 'sdfsdf',
'2020-10-20');
insert into employee values (3004, 'Nayan', 'T', 'Tester', 1001, 'ddes', '2020-09-
09');
select * from employee;

insert into course(coursename) values ('Java');

insert into course(courseid, coursename) values (101, 'MySQL');
insert into course(courseid, coursename) values (102, 'Python');

select * from course;

insert into student(s_name, courseid) values ('Prithvi', 100);

insert into student values (203, 'Nayan', 19, 101);
insert into student(courseid, age, s_name) values (101, 20, 'Lavanya');
insert into student set id=206, courseid=100, s_name='Janhavi';

select * from student;

update employee set doj='2020-11-02' where id=3001;
update employee set salary=23451.00 where dept_id=1002;

-- truncate table student;

-- drop table student;
-- commit;

delete from student; -- deletes all the rows

delete from student where courseid=101; -- specific row(s) is deleted

create table product (

productId int primary key,
p_desc varchar(255) not null,
price decimal(10,2) not null check(price >= 100.00)
);

insert into product values(101, 'charger', 150.00);

select * from product;

--
-----------------------------------------------------------------------------------
-----------
-- SELECT STATEMENTS------------------------------
--
-----------------------------------------------------------------------------------
-----
select * from student;
select courseid, s_name, age from student;
select courseid, s_name, age from student where courseid=100;
select courseid, s_name, age from student where age in (18, 19);
select courseid, s_name, age from student where age >=18 and age <= 19;
select courseid, s_name, age from student where age not between 18 and 19;

select now();
select curdate();
select concat('Pallavi', ' ', 'Prasad');

select courseid, s_name, age from student order by s_name desc;

select courseid as 'Course Id', s_name as 'Student Name', age as Age

from student
where courseid=100
order by s_name;

select c.coursename, s.s_name, s.age from student s, course c

where c.courseid = s.courseid;

select course.coursename, s.s_name, s.age from student s, course

where course.courseid = s.courseid and course.courseid=100;
select c.coursename as 'Course', s.s_name as 'Student Name', s.age from student s,
course c
where c.courseid = s.courseid and c.courseid=100
order by s.s_name;

select * from student where s_name LIKE 'P%';

select * from student where s_name NOT LIKE '%i%';