use classicmodels;

/*Thực hành số 3*/

/*1*/ select * from employees where reportsTo is null;
/*2*/ select distinct customerNumber from orders;
/*3*/ select * from orders where requiredDate = "2003-01-18";
/*4*/ select * from orders where status = "Shipped" and year(orderDate) = 2005 and
month(orderDate) = 4;
/*5*/ select * from products where productLine = "Classic Cars";

/*Thực hành số 4*/

/*1*/ select * from customers where city in ("Nantes", "Lyon");
select * from customers where city = "Nantes" or city = "Lyon";
/*2*/ select * from orders where shippedDate between "2003-01-10" and "2003-03-10";
select * from orders where shippedDate >= "2003-01-10" and shippedDate <= "2003-03-
10";
/*3*/ select distinct * from products where productLine like "%Cars%";
/*4*/ select productCode, productName, quantityInStock from products order by
quantityInStock desc limit 10;
/*5*/ select productCode, productName, quantityInStock, buyPrice,
quantityInStock*buyPrice as priceOfInStock from products;

/*Thực hành số 5*/

/*1*/ select substring(productDescription, 1, 50) as 'Title of products' from
products;
/*2*/ select concat_ws(',', concat_ws(' ', firstName, lastName), concat(jobTitle,
'.')) as employeeDescription from employees;
/*3*/ SET SQL_SAFE_UPDATES = 0;
SET foreign_key_checks = 0;
update productlines set productLine = replace(productLine, 'Cars', 'Automobiles');
update products set productLine = replace (productLine, 'Cars', 'Automobiles');
SET SQL_SAFE_UPDATES = 1;
SET foreign_key_checks = 1;
/*4*/ select *, datediff(requiredDate, shippedDate) as dateDiff from orders order
by dateDiff desc limit 5;
/*5*/ select orderNumber, orderDate, shippedDate from orders where month(orderDate)
= 5 and year(orderDate) = 2005 and shippedDate is null;

/*Thực hành số 6*/

/*1*/ select city, count(*) as numOfCustomers from customers group by city;
/*2*/ select month(orderDate) as month, count(*) as quantity from orders where
year(orderDate)=2005 and month(orderDate)=3 group by month(orderDate);
/*2*/ select month(orderDate) as month, count(*) as quantity from orders where
year(orderDate)=2005 group by month(orderDate);
/*3*/ select orderNumber, sum(quantityOrdered * priceEach) as totalValue from
orderdetails group by orderNumber order by totalValue desc limit 10;
/*4*/ select productLine, sum(quantityInStock) as quantity from products group by
productLine;
/*5*/ select customerNumber, sum(amount) as totalConsumed from payments group by
customerNumber;

/*Thực hành số 7*/

/*1*/ select [Link] as officeName, e.* from employees e join offices o on
[Link] = [Link];
/*2*/ select p.*, [Link] from products p left join orderdetails o on
[Link] = [Link] where [Link] is null;
/*3*/ select [Link], [Link], [Link], ([Link] *
[Link]) as totalValue from orders o join orderdetails od on [Link] =
[Link] where month([Link]) = 3 and year([Link]) = 2003;
/*4*/ select [Link], sum([Link]) as totalProduct,
[Link] from products p join productlines pl on [Link] =
[Link] group by [Link] order by sum([Link]) desc;
/*5*/ select [Link], sum([Link] * [Link]) as
totalConsumption from customers c
join orders o on [Link] = [Link]
join orderdetails od on [Link] = [Link]
group by [Link];

/*Thực hành số 8*/

/*1*/ select * from products where productCode in
(select productCode from orderdetails where orderNumber in
(select orderNumber from orders where month(orderDate) = 3 and
year(orderDate) = 2005));
/*2*/ select * from orders o where
month([Link]) = (select month(max(orderDate)) from orders)
and year([Link]) = (select year(max(orderDate)) from orders);

/*3*/ select * from

orders o,
(select orderNumber, productCode, sum(priceEach*quantityOrdered) as
totalPrice
from orderdetails group by orderNumber) as od
where [Link] = [Link];
/*4*/
select cus_price.customerNumber, cus_price.customerName, (totalPrice - sum(amount))
as inDebt from
payments p,
(select [Link], [Link], sum(sumPrice) as totalPrice from
customers c,
(select [Link], sumPrice from
orders o,
(select orderNumber, sum(priceEach*quantityOrdered) as sumPrice
from orderdetails group by orderNumber) as od
where [Link] = [Link]) as cus_od
where [Link] = cus_od.customerNumber
group by cus_od.customerNumber) as cus_price
where cus_price.customerNumber = [Link]
group by cus_price.customerNumber;

/*Thực hành số 9*/

/*1*/
insert into orders (orderNumber, orderDate, requiredDate, status, customerNumber)
values
(10427, '2021-04-20', '2021-04-29', 'In Process', 103),
(10428,'2021-04-21', '2021-05-02', 'In Process',
(select customerNumber from customers where contactLastName = 'King'
and contactFirstName = 'Jean'));
insert into productlines (productLine, textDescription)
values
('Bicycle', 'New Bicycle product line for children under 5');
update customers
set phone = '+840126548753'
where customerNumber = 103;

set foreign_key_checks = 0;
delete products, productlines from products, productlines
where [Link] = [Link] and
[Link] = 'Ships';
set foreign_key_checks = 1;
/*2*/
create table temp_orderdetails like orderdetails;

insert into orderdetails (orderNumber, productCode, quantityOrdered, priceEach,

orderLineNumber)
values
(10428, 'S10_2016', 1, 68.99, 10);
insert into temp_orderdetails
select * from orderdetails where [Link] in
(select orderNumber from orders where [Link] = (select
max(orderDate) from orders));
/*3*/
set sql_safe_updates = 0;
update employees
set jobTitle = 'Sales Representatives'
where jobTitle = 'Sales Rep';
set sql_safe_updates = 1;

/*GK1*/
/*Cau 1*/
show databases;

use classicmodels;
show tables;

/*Cau 2*/
select distinct productCode from orderdetails;

select orderNumber, datediff(shippedDate, orderDate) as dateForward from orders

where shippedDate is not null order by dateForward asc limit 5;

/*Cau 3*/
select concat_ws(' ', addressLine1, if (addressLine2 is null, 'N/A', addressLine2))
address from customers;

/*Cau 4*/
alter table partner add constraint fk_partner_employees foreign key
(employeeNumber) references employees (employeeNumber) on delete restrict on update
cascade;