create database cwpc;

use cwpc;

CREATE TABLE p4n_emp (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(255),

LastName VARCHAR(255),

DateOfBirth DATE,

Gender VARCHAR(10),

Email VARCHAR(255),

Phone VARCHAR(15),

Address VARCHAR(255),

DepartmentID INT,

Position VARCHAR(255),

Salary DECIMAL(10, 2)

);

select * from p4n_emp;

INSERT INTO p4n_emp (EmployeeID, FirstName, LastName, DateOfBirth, Gender, Email, Phone, Address, DepartmentID, Position, Salary)

(1, 'John', 'Doe', '1990-05-15', 'Male', 'johndoe@example.com', '555-555-5555', '123 Main St, City, State', 101, 'Manager', 75000.00);

INSERT INTO p4n_emp (EmployeeID, FirstName, LastName, DateOfBirth, Gender, Email, Phone, Address, DepartmentID, Position, Salary)

(2, 'Jane', 'Smith', '1985-08-20', 'Female', 'janesmith@example.com', '555-555-5556', '456 Oak St, City, State', 102, 'Sales Representative', 60000.00);

INSERT INTO p4n_emp (EmployeeID, FirstName, LastName, DateOfBirth, Gender, Email, Phone, Address, DepartmentID, Position, Salary)

(3, 'Michael', 'Johnson', '1992-03-10', 'Male', 'michael@example.com', '555-555-5557', '789 Elm St, City, State', 103, 'Software Engineer', 80000.00);

INSERT INTO p4n_emp (EmployeeID, FirstName, LastName, DateOfBirth, Gender, Email, Phone, Address, DepartmentID, Position, Salary)

(4, 'Emily', 'Wilson', '1988-11-25', 'Female', 'emily@example.com', '555-555-5558', '101 Pine St, City, State', 102, 'Sales Manager', 90000.00);

INSERT INTO p4n_emp (EmployeeID, FirstName, LastName, DateOfBirth, Gender, Email, Phone, Address, DepartmentID, Position, Salary)

(5, 'David', 'Brown', '1995-07-05', 'Male', 'david@example.com', '555-555-5559', '222 Cedar St, City, State', 104, 'HR Specialist', 65000.00);

select * from p4n_emp;

delete from p4n_emp

where EmployeeID = 3;

update p4n_emp

set Salary = 45000

where EmployeeID = 4;

show databases;

use classicmodels;

select * from customers;

show tables;

select * from customers where country = "USA";

select * from customers where country = "USA" and state = "NY";

select * from customers where country = "USA" or creditlimit > 100000;

select * from customers where country = "USA" and creditlimit > 100000;

select * from customers where city = "NYC" and creditlimit < 100000;

select customerName,city,state,country,creditlimit, creditlimit+50000 as update_creditlimit

from customers where city = "NYC" ;

select * from customers

limit 5;

select * from customers order by creditlimit asc;

select * from customers order by creditlimit desc;

select * from customers order by customername asc;

select * from customers order by customername desc;

use classicmodels;

select * from customers;

select * from customers where customername like "a%";

select * from customers where customername like "%a%";

select * from customers where customername like "%e";

select * from customers limit 5;

select * from customers order by creditlimit asc;

select * from customers order by creditlimit desc;

select Country from customers group by country;

select country , count(Country) as total_country from customers group by country;

select distinct country from customers;

-- Unique Key

-- Primary Key

-- Foreign Key

select country, sum(creditlimit) as total_sum

from customers group by country;

select country, sum(creditlimit) as total_sum

from customers group by country having sum(creditlimit) > 200000;

create database p4n0091;

use p4n0091;

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

Email VARCHAR(100)

);

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,

CustomerID INT,

OrderDate DATE,

TotalAmount DECIMAL(10, 2)

);

INSERT INTO Customers (CustomerID, FirstName, LastName, Email)

(1, 'Pankaj', 'Sharma', 'pankaj@codeswithpankaj.com'),

(2, 'Nishant', 'Patel', 'nishant@codeswithpankaj.com'),

(3, 'Kiran', 'Desai', 'kiran@codeswithpankaj.com'),

(4, 'Tanvi', 'Mehta', 'tanvi@codeswithpankaj.com'),

(5, 'Kritek', 'Singh', 'kritek@codeswithpankaj.com'),

(7, 'pooja', 'Sharma', 'pooja@codeswithpankaj.com');

INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)

(101, 1, '2023-01-15', 250.00),

(102, 2, '2023-02-20', 120.50),

(103, 3, '2023-03-10', 320.75),

(105, 4, '2023-05-12', 210.00),

(106, 9, '2024-06-11', 910.00);

select * from customers;

select * from orders;

select customers.firstname,customers.email ,orders.orderdate , orders.totalamount

from customers left join orders

on customers.customerid = orders.customerid;

select customers.firstname,customers.email ,orders.orderdate , orders.totalamount

from customers right join orders

on customers.customerid = orders.customerid;

select customers.firstname,customers.email ,orders.orderdate , orders.totalamount

from customers inner join orders

on customers.customerid = orders.customerid;

select customers.firstname,customers.email ,orders.orderdate , orders.totalamount

from customers left join orders

on customers.customerid = orders.customerid

select customers.firstname,customers.email ,orders.orderdate , orders.totalamount

from customers right join orders

on customers.customerid = orders.customerid;

create database bank;

use bank;

create table accounts(

account_id int primary key,

account_name varchar(100),

balance decimal(10,2)

);

insert into accounts (account_id,account_name,balance) values

(1,'rina',1000.00),

(2,'Shivam',4566.45),

(3,'Neha',2000.56),

(4,'Pooja',9000.00),

(5,'Krish',5600.00),

(6,'Rohan',500.00),

(7,'Shubham',400.00);

select * from accounts;

Start transaction;

update accounts

set balance = balance - 200

where account_id = 4;

update accounts

set balance = balance + 200

where account_id = 3;

commit;

select * from accounts;

start transaction;

update accounts

set balance = balance - 1000

where account_id = 3;

rollback;

select * from accounts;