Class 1 SQL
Select*from ap.invoices;
Select invoice_id,invoice_total
from ap.invoices;
/*This is a comment*/
use ap;
Select*from invoices;
Select*from vendors;
Select max(invoice_total) from invoices;
Select min(invoice_total) from invoices;
/*identifying avg invoice total*/
SELECT
MAX(invoice_total), MIN(invoice_total), AVG(invoice_total)
FROM
invoices;
/* Aliases as cloumn name*/
select max(invoice_total) as maximum,
min(invoice_total) as minimum,
avg(invoice_total) as average
from invoices;
/* Where Clause*/
select*from invoices
where invoice_total>=1000;
/*Order By Clause*/
select*from invoices
where invoice_total>=1000
order by invoice_total
Limit 3;
/*exploring vendors table*/
Select*from vendors;
select count(distinct vendor_city) from vendors;
select count(distinct vendor_state) from vendors;
select*from vendors;
/*identifying vendors where address is null*/
select*from vendors
where vendor_address1 is null;
select*from vendors
where vendor_phone is null;
Class 2
use ap;
Select*from invoices
where invoice_total>=1000 and invoice_total<=2000;
/*between*/
select*from invoices
where invoice_total between 1000 and 2000;
Select*from vendors
where vendor_city="washington"
or vendor_city="New York"
or vendor_city="Dallas";
/*In statement*/
Select * from vendors
where vendor_city in
("washington","new York","dallas");
/*Group By*/
Select vendor_state, count(vendor_id) Numberofvendors
from vendors
where vendor_state not in ("NJ")
Group by vendor_state
having Numberofvendors>2
order by Numberofvendors desc;
/*Act on invoices table*/
/*Vendor_id and count of invoices*/
Select vendor_id, count(invoice_id)
from invoices
Group by vendor_id
order by count(invoice_id) desc;
Select distinct vendor_id from invoices;
/*subquery*/
Select* from invoices where
invoice_total> (Select avg(invoice_total) from invoices);
/*Another example of subquery*/
Select * from vendors where
vendor_id not in (select distinct vendor_id from invoices);
/*joins*/
/*inner join*/
select vendor_name, vendor_state, invoice_id, invoice_total
from invoices inner join vendors
on invoices.vendor_id=vendors.vendor_id;
/* difference between primary and foreign key- primary key is the unique key in its own table and
foreign in other table*/
/*left join*/
select vendor_name, vendor_state, invoice_id, invoice_total
from invoices left join vendors
on invoices.vendor_id=vendors.vendor_id;
/*right join*/
select vendor_name, vendor_state, invoice_id, invoice_total
from invoices right join vendors
on invoices.vendor_id=vendors.vendor_id;
class 3
use ap;
Select*from invoices
where invoice_total>=1000 and invoice_total<=2000;
/*between*/
select*from invoices
where invoice_total between 1000 and 2000;
Select*from vendors
where vendor_city="washington"
or vendor_city="New York"
or vendor_city="Dallas";
/*In statement*/
Select * from vendors
where vendor_city in
("washington","new York","dallas");
/*Group By*/
Select vendor_state, count(vendor_id) Numberofvendors
from vendors
where vendor_state not in ("NJ")
Group by vendor_state
having Numberofvendors>2
order by Numberofvendors desc;
/*Act on invoices table*/
/*Vendor_id and count of invoices*/
Select vendor_id, count(invoice_id)
from invoices
Group by vendor_id
order by count(invoice_id) desc;
Select distinct vendor_id from invoices;
/*subquery*/
Select* from invoices where
invoice_total> (Select avg(invoice_total) from invoices);
/*Another example of subquery*/
Select * from vendors where
vendor_id not in (select distinct vendor_id from invoices);
/*joins*/
/*inner join*/
select vendor_name, vendor_state, invoice_id, invoice_total
from invoices inner join vendors
on invoices.vendor_id=vendors.vendor_id;
/* difference between primary and foreign key- primary key is the unique key in its own table and
foreign in other table*/
/*left join*/
select vendor_name, vendor_state, invoice_id, invoice_total
from invoices left join vendors
on invoices.vendor_id=vendors.vendor_id;
/*right join*/
select vendor_name, vendor_state, invoice_id, invoice_total
from invoices right join vendors
on invoices.vendor_id=vendors.vendor_id;
/*alias in sql with joins*/
/*inner join*/
use ap;
select vendor_name, vendor_state, invoice_id, invoice_total,v.vendor_id
from invoices i inner join vendors v
on i.vendor_id=v.vendor_id
order by invoice_total desc;
/* difference between primary and foreign key- primary key is the unique key in its own table and
foreign in other table*/
/*right join*/
select vendor_name, vendor_state, invoice_id, invoice_total
from invoices i right join vendors v
on i.vendor_id=v.vendor_id;
/*case statement*/
Select invoice_id,invoice_date,invoice_total,
case when invoice_total>10000 then 'High Priority'
else 'Low Priority'
end as Priority
from invoices
order by Priority;
/*generate a case statement column for write off less than 1000 yes otherwise no*/
Select invoice_id,invoice_date,invoice_total,
case when invoice_total>10000 then 'High Priority'
else 'Low Priority'
end as Priority,
case when invoice_total<1000 then 'Yes'
else 'no'
end as writeoff
from invoices
order by Priority;
/*views- they are virtual tables with criteria, does not have physical existence, no physical table*/
/*tables are physical and views are non-physical*/
Create view Vendors1 as
Select * from vendors;
select*from vendors1
where vendor_state in ("NY","NJ");
Create view Vendors1_NY as
Select * from vendors
where vendor_state="NY";
Create view Vendors1_NJ as
Select * from vendors
where vendor_state="NJ";
Select* From ap.vendors1_ny;
/*drop statement*/
drop view vendors1_nj;
drop view vendors1_ny;
drop view vendors1;
Create table invoices_1 as
Select *from invoices;
Create table invoices2 as
Select invoice_id,invoice_date,invoice_total,
case when invoice_total>10000 then 'High Priority'
else 'Low Priority'
end as Priority,
case when invoice_total<1000 then 'Yes'
else 'no'
end as writeoff
from invoices
order by Priority;
drop table invoices2;
/*update command*/
Select* from terms ;
Create table terms1 as
Select* from terms;
Select* from ap.terms1;
update terms1
set terms_due_days=60
where terms_due_days=90;
/*creating table from scratch*/
Continue
Select* from ap.terms1;
use ap;
update terms1
set terms_due_days=60
where terms_due_days=90;
drop table terms1;
/*create database*/
drop database if exists cat;
create database cat;
use cat;
/*creating table from scratch*/
create table score
cat_id int primary key,
name varchar(50) Not NULL,
cat_score int Not NULL
);
/*insert*/
insert into score(cat_id, name, cat_score)
values
("901","Sunidhi",99);
insert into score(cat_id, name, cat_score)
values
("902","Alia Bhatt",38);
insert into score(cat_id, name, cat_score)
values
("903","Katrina kaif",52);
select*from score;
Class 4 SQL
use ap;
Select*from invoices
where invoice_total>=1000 and invoice_total<=2000;
/*between*/
select*from invoices
where invoice_total between 1000 and 2000;
Select*from vendors
where vendor_city="washington"
or vendor_city="New York"
or vendor_city="Dallas";
/*In statement*/
Select * from vendors
where vendor_city in
("washington","new York","dallas");
/*Group By*/
Select vendor_state, count(vendor_id) Numberofvendors
from vendors
where vendor_state not in ("NJ")
Group by vendor_state
having Numberofvendors>2
order by Numberofvendors desc;
/*Act on invoices table*/
/*Vendor_id and count of invoices*/
Select vendor_id, count(invoice_id)
from invoices
Group by vendor_id
order by count(invoice_id) desc;
Select distinct vendor_id from invoices;
/*subquery*/
Select* from invoices where
invoice_total> (Select avg(invoice_total) from invoices);
/*Another example of subquery*/
Select * from vendors where
vendor_id not in (select distinct vendor_id from invoices);
/*joins*/
/*inner join*/
select vendor_name, vendor_state, invoice_id, invoice_total
from invoices inner join vendors
on invoices.vendor_id=vendors.vendor_id;
/* difference between primary and foreign key- primary key is the unique key in its own table and
foreign in other table*/
/*left join*/
select vendor_name, vendor_state, invoice_id, invoice_total
from invoices left join vendors
on invoices.vendor_id=vendors.vendor_id;
/*right join*/
select vendor_name, vendor_state, invoice_id, invoice_total
from invoices right join vendors
on invoices.vendor_id=vendors.vendor_id;
/*alias in sql with joins*/
/*inner join*/
use ap;
select vendor_name, vendor_state, invoice_id, invoice_total,v.vendor_id
from invoices i inner join vendors v
on i.vendor_id=v.vendor_id
order by invoice_total desc;
/* difference between primary and foreign key- primary key is the unique key in its own table and
foreign in other table*/
/*right join*/
select vendor_name, vendor_state, invoice_id, invoice_total
from invoices i right join vendors v
on i.vendor_id=v.vendor_id;
/*case statement*/
Select invoice_id,invoice_date,invoice_total,
case when invoice_total>10000 then 'High Priority'
else 'Low Priority'
end as Priority
from invoices
order by Priority;
/*generate a case statement column for write off less than 1000 yes otherwise no*/
Select invoice_id,invoice_date,invoice_total,
case when invoice_total>10000 then 'High Priority'
else 'Low Priority'
end as Priority,
case when invoice_total<1000 then 'Yes'
else 'no'
end as writeoff
from invoices
order by Priority;
/*views- they are virtual tables with criteria, does not have physical existence, no physical table*/
/*tables are physical and views are non-physical*/
Create view Vendors1 as
Select * from vendors;
select*from vendors1
where vendor_state in ("NY","NJ");
Create view Vendors1_NY as
Select * from vendors
where vendor_state="NY";
Create view Vendors1_NJ as
Select * from vendors
where vendor_state="NJ";
Select* From ap.vendors1_ny;
/*drop statement*/
drop view vendors1_nj;
drop view vendors1_ny;
drop view vendors1;
Create table invoices_1 as
Select *from invoices;
Create table invoices2 as
Select invoice_id,invoice_date,invoice_total,
case when invoice_total>10000 then 'High Priority'
else 'Low Priority'
end as Priority,
case when invoice_total<1000 then 'Yes'
else 'no'
end as writeoff
from invoices
order by Priority;
drop table invoices2;
/*update command*/
Select* from terms ;
Create table terms1 as
Select* from terms;
Select* from ap.terms1;
use ap;
update terms1
set terms_due_days=60
where terms_due_days=90;
drop table terms1;
/*create database*/
drop database if exists cat;
create database cat;
use cat;
/*creating table from scratch*/
create table score
(
cat_id int primary key,
name varchar(50) Not NULL,
cat_score int Not NULL
);
/*insert*/
insert into score(cat_id, name, cat_score)
values
("901","Sunidhi",99);
insert into score(cat_id, name, cat_score)
values
("902","Alia Bhatt",38);
insert into score(cat_id, name, cat_score)
values
("903","Katrina kaif",52);
create table score1
cat_id int primary key,
name varchar(50) Not NULL,
cat_score int Not NULL
);
/*insert*/
insert into score1(cat_id, name, cat_score)
values
("906","Sam",99);
insert into score1(cat_id, name, cat_score)
values
("907","Ali",38);
insert into score1(cat_id, name, cat_score)
values
("908","Ram",52);
select*from score;
/*Union Query*/
use cat;
select*from score;
select*from score1;
Create table consolidated as
Select cat_id,name,cat_score from score
Union
Select cat_id,name,cat_score from score1;
/*union all duplicate values cities will be allowed, whereas union takes only distinct values*/
Select* from consolidated;
/*Delete Statement- deletes record based on conditions*/
Delete from consolidated
where name="Sam";
/*Drop will delete all the table with all records whereas delete will drop certain records based on
conditions*/
Select*from consolidated;
/*Delete with like option*/
delete from consolidated
where name like "Al%";
Select*from consolidated;
/*Stored procedures*/
use ap;
Call GetPriorityInvoices();
Call GetVendorsbyState("NY");
Call GetVendorsbyState("NJ");
Call GetVendorsbyState("CA");
Call GetVendorsbyState("OH");
Call GetVendorStateCount("NJ",@TotalVendorsinState);
Select @TotalVendorsinState;
Call GetVendorStateCount("CA",@TotalVendorsinState);
Select @TotalVendorsinState;
/*input catid and output variable cat score*/
use cat;
select*from randomcatfile;
Call CScore ("P212A022",@CatScore);
Select @catscore;
