To run sql in jupyter notebook
%load_ext sql
%sql mysql+mysqlconnector://root:root@localhost/test
run these first
Use this %%sql for each operation
When it shows error in the database or table , check whether we are using correct database and table
To check that : use this command:
%%sql
select database();
after executing this
%%sql
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name = 'test';
%%sql
use de_project;
question 1: employee database:
%%sql
create table employees(
employee_id int primary key,
first_name varchar(50) not null,
last_name varchar(50) not null,
hire_date date not null,
salary decimal(10,2)
);
%%sql
alter table employees add email varchar(100);
question 2: uber database
%%sql
create table [Link](
ride_id int,
driver_id int,
rider_id int,
pickup_location varchar(100),
dropoff_location varchar(100),
ride_date datetime,
fare decimal(10,2)
);
%%sql
insert into [Link](ride_id,driver_id,rider_id,pickup_location,dropoff_location,ride_date,fare) values
(1,10,11,'chennai','tambaram','2026-01-10 14:34:00',100.00),
(2,20,21,'chennai','guindy','2026-01-11 15:34:55',340.00),
(3,30,31,'kelambakkam','ssn','2026-02-2 02:22:09',340.00),
(4,40,41,'kgiri','kptm','2026-01-11 05:00:03',40.00),
(5,50,51,'rkottai','kptm','2024-01-09 05:18:07',50.00);
%%sql
select * from rides
where fare <='100'
order by fare asc;
--driver + rides---
%%sql
drop table if exists rides;
drop table if exists driver;
create table driver (
driver_id int primary key,
driver_name varchar(100) not null,
licence_no varchar(100) unique
);
create table rides(
ride_id int primary key,
driver_id int,
pickup_location varchar(100) not null,
dropoff_location varchar(100) not null,
ride_date datetime,
fare decimal(10,2) check(fare>0),
foreign key(driver_id) references driver (driver_id) on delete cascade
);
%%sql
insert into driver(driver_id, driver_name, licence_no)
values
(101,'john','xyzabc123'),
(102,'jerry','asdf123'),
(103,'tom','lkjh1234');
%%sql
insert into rides (ride_id,driver_id,pickup_location,dropoff_location,ride_date,fare)
values
(1,101,'chennai','tambaram','2025-02-02 13:03:00',500.00),
(3,102,'chennai','kelambakkam','2025-03-02 14:03:00',600.00);
%%sql
delete from driver where driver_id='101';
%%sql
insert into driver (driver_id,driver_name,licence_no) values (101,'sri','uioi123');
%%sql
alter table rides add column is_delete boolean default false;
%%sql
update rides set is_delete=True where driver_id='102';
question 3: ecommerce transactions
%%sql
create table de_project.customertransactions(
id int primary key,
login_device varchar(100),
customer_name varchar(100),
ip_address varchar(100),
product varchar(100),
amount decimal(10,2),
is_placed boolean,
is_viewed boolean,
transaction_status varchar(20)
);
%%sql
insert into customertransactions values
(1,'mobile','ravi','[Link]','laptop',50000.0,TRUE,FALSE,'completed'),
(2,'desktop','priya','[Link]','smartphone',20000.0,TRUE,TRUE,'completed'),
(3,'tablet','arjun','[Link]','headphones',1500.00,FALSE,TRUE,'failed'),
(4,'mobile','meena','[Link]','shoes',2500.00,TRUE,FALSE,'completed'),
(5,'desktop','karthick','[Link]','watch',50000.00,TRUE,TRUE,'completed'),
(6,'tablet','sowmya','[Link]','tablet',15000.00,TRUE,TRUE,'completed'),
(7,'tablet','ramesh','[Link]','smartphone',25000.00,FALSE,TRUE,'failed'),
(8,'desktop','divya','[Link]','laptop',60000.00,TRUE,FALSE,'completed'),
(9,'mobile','arun','[Link]','smartwatch',12000.00,TRUE,TRUE,'completed'),
(10,'tablet','deepa','[Link]','laptop',55000.00,FALSE,FALSE,'pending');
%%sql
select count(*) from customertransactions where transaction_status='completed' and product='laptop';
%%sql
select avg(amount) as avg_trans from customertransactions;
%%sql
select customer_name,
sum(amount) as total_transaction
from customertransactions
group by customer_name
having sum(amount) >20000;
question 4: customer data
%%sql
drop table if exists customerdata;
create table customerdata(
sn_id int,
customer_name varchar(100),
email varchar(100),
phonenumber varchar(10),
address varchar(100),
amount varchar(100)
);
%%sql
insert into customerdata
(sn_id, customer_name, email, phonenumber, address, amount)
values
(1,'ravi','ravi@[Link]','1234789098','chennai','5000.00'),
(2,'priya','priya@[Link]','3478986535','bangalore',NULL),
(3,'arjun',NULL,'7893457890','mumbai','1500.00'),
(4,'meena','meena@[Link]',NULL,'chennai','2500.00'),
(5,'karthick',NULL,'8903374394','mumbai','3000.00');
%%sql
select
customer_name,amount,
case
when amount>3000 then 'high spender'
when amount between 2000 AND 4000 then 'medium spender'
when amount <= 2000 then 'low spender'
else 'no data'
end as spending
from customerdata;
%%sql
select
customer_name,
case
when phonenumber is null then email
else phonenumber
end as contact
from customerdata
%%sql
select * from customerdata limit 2;
%%sql
select
sn_id,
customer_name,
amount,
coalesce (amount,'0.00')
from customerdata
%%sql
select length(customer_name) as namelength,
upper(address) as address_upper,
lower(customer_name) as cust_lower
from customerdata
%%sql
select length(customer_name) as namelength,
upper(address) as address_upper,
lower(customer_name) as cust_lower,
concat(customer_name,' - ',address,' - ',' TN') as name_city,
substring(customer_name,1,3) as name_prefix,
trim(' chennai ') as trimmed_city,
lpad(customer_name,10,'*') as left_padded_name
##rtrim to trim rightside
##ltrim to trim leftside
from customerdata
----string manipulation---
%%sql
##string operations:
select length(customer_name) as namelength,
upper(address) as address_upper,
lower(customer_name) as cust_lower,
#concat(customer_name,' - ',address,' - ',' TN') as name_city,
substring(customer_name,1,3) as name_prefix,
trim(' chennai ') as trimmed_city,
lpad(customer_name,15,'*') as left_padded_name,##ltrim to trim leftside
rpad(customer_name,15,'*') as right_padded_name,##rtrim to trim rightside
replace(customer_name,' ','_') as updated_name,
instr(customer_name,'a') as position_of_,
left(customer_name,5) as first_5_chars,#this does as same as substring but for substring we have to give
starting and ending position.
right(customer_name,5) as last_5_char,
reverse(customer_name)as reversed_name,
format(9876543210, 2) as formatted_numb
from customerdata
%%sql
##we can use one func inside the other func
select
length(upper(concat (customer_name,'-',address,'-','TN'))) as name_c
from customerdata
----question5 : table 1 -customer and table 2 orders----
%%sql
drop table if exists customerdata;
##table 1
create table customers(
customer_id int primary key,
customer_name varchar(100),
city varchar(100)
);
%%sql
insert into customers(customer_id,customer_name,city) values
(1,'kalai','chennai'),
(2,'sri','kptm'),
(3,'ram','erode'),
(4,'kumar','karur'),
(5,'selvi','chennai');
%%sql
#table 2
drop table if exists orders;
create table orders(
order_id int primary key,
customer_id int ,
order_amount decimal(10,2),
order_date date
);
%%sql
insert into orders(order_id,customer_id,order_amount,order_date) values
(101,1,5000.00,'2022-01-01'),
(102,2,10000.00,'2025-02-02'),
(103,3,3000.00,'2025-01-02'),
(104,4,1500.00,'2024-01-02'),
(105,1,7000.00,'2025-02-03'),
(106,5,8000.00,'2025-02-06');
%%sql
select customer_name,
(select max(order_amount) from orders) as max_order_amount
from customers
%%sql
## witting subqueries in where condition
select customer_id
from orders
where order_amount >=5000;
%%sql
select customer_name
from customers
where customer_id IN(
select distinct customer_id
from orders
where order_amount>=5000);
%%sql
##diff btw 'exists' and 'in' using along with where
# 'exists' shows all entity in the column if the given city is present in that column
#'in' shows the customer name with the given city name
select customer_name
from customers
where exists (
select * from customers where city='chennai');
--------line 168-untitled2-------
%%sql
##identify products with total sales exceeding 100
select p.product_name
from sales s
join products p on s.product_id = p.product_id
group by p.product_name
having sum(s.total_price)>100;