Bài 08: Hệ quản trị cơ sở dữ liệu (Bài Lab5)

  

Kho Bài Lab

Lab05:

lấy link bài lab

Đề bài:

Bài làm:

CREATE DATABASE /*!32312 IF NOT EXISTS*/`1812767_database_lab05` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `1812767_database_lab05`;

/*************************************************************** */
/**************************Bài Làm ***************************** */
/*************************************************************** */
select * from `customers`;
select * from `employees`;
select * from `offices`;
select * from `orders`;
select * from `orderdetails`;
select * from `products`;
select * from `productlines`;
/*Đưa ra tên các thành phố và số lượng khách hàng tại từng thành phố. */
select city,count(city) as SoLuongKhachHang
from `customers`
group by city;
/*Lấy ra thông tin thành phố có số lượng khách hàng lớn nhất */
select city,count(city) as SoLuongKhachHang
from `customers`
group by city
having count(city) = (select count(city)
                   from `customers`
                   group by city
                   order by count(city) desc
                   limit 1
                  );
/*Tìm thành phố của khách hàng có nhiều đơn hàng nhất trong năm 2005. */
select city,count(`orders`.customerNumber) as TongDH
from `customers`,`orders`
where `customers`.customerNumber = `orders`.customerNumber
    and EXTRACT(YEAR FROM `orders`.orderDate) = 2005
group by city
having count(`orders`.customerNumber) = (select count(`orders`.customerNumber)
                   from `customers`,`orders`
                   where `customers`.customerNumber = `orders`.customerNumber
                        and EXTRACT(YEAR FROM `orders`.orderDate) = 2005
                   group by city
                   order by count(`orders`.customerNumber) desc
                   limit 1
                  );
/*Đưa ra số lượng các đơn đặt hàng trong tháng 5/2003*/
select * from `orders`;
select count(*) TongSoCacDonHangThang5_2003
from `orders`
where EXTRACT(year from orderDate) = 2003 and EXTRACT(month
from orderDate) = 5; 
/*Liệt kê họ tên khách hàng cùng số lượng các đơn bị hủy của họ trong năm 2003 */
select `customers`.customerName,`orders`.orderDate,
`orders`.requiredDate,`orders`.shippedDate,`orders`.status,
count(`orders`.customerNumber) as SoDonBiHuy
from `customers`,`orders`
where `customers`.customerNumber = `orders`.customerNumber
    and EXTRACT(year from orderDate) = 2003 
    and `orders`.status like 'Cancelled' 
group by  `customers`.customerName;
/*Đưa ra số lượng các đơn đặt hàng trong từng tháng của năm 2004. Nếu
tháng nào có số lượng nhỏ nhất thì xuất ra tháng đó cùng với họ tên của
khách hàng đã đặt */
select `customers`.customerName,orderDate,count(month(orderDate)) as SoDonHang
from `customers`,`orders`
where `customers`.customerNumber = `orders`.customerNumber
    and EXTRACT(year from orderDate) = 2004 
group by  month(orderDate)
having count(month(orderDate)) = (select count(month(orderDate))
                   from `customers`,`orders`
                   where `customers`.customerNumber = `orders`.customerNumber
                        and EXTRACT(year from orderDate) = 2004 
                   group by month(orderDate)
                   order by count(month(orderDate)) asc
                   limit 1
                  );
/*Đưa ra các mã đơn đặt hàng có giá trị lớn nhất của dòng sản phẩm “Classic Cars”. */
select orderNumber,(quantityOrdered * priceEach) as GiaTri
from `products`,`orderdetails`
where `products`.productCode = `orderdetails`.productCode
    and productLine like 'Classic Cars'
order by (quantityOrdered * priceEach) desc
limit 1;
/*Đưa ra mã nhóm hàng, tên nhóm hàng và tổng số lượng hàng hoá còn trong
kho của nhóm hàng đó */
select * from `products`;
select productCode,productName,productLine,quantityInStock
from `products`
group by productCode
order by quantityInStock desc;
/*Đưa ra thông tin về các nhân viên và tên văn phòng nơi họ làm việc.*/
select employeeNumber,firstName,lastName,email,jobTitle,city,phone,addressLine1,addressLine2
state,country
from `employees`
inner join `offices`on `employees`.officeCode=`offices`.officeCode;
/*Đưa ra thông tin về tên khách hàng và tên các sản phẩm họ đã mua có chứa
chữ “Ford” nhưng không chứa chữ “A”.*/
-- cách 1
select `customers`.customerNumber,`customers`.customerName,`customers`.contactLastName,
`customers`.contactFirstName,`products`.productCode,`products`.productName,`products`.productLine
from `customers`
INNER JOIN `orders` ON `customers`.customerNumber = `orders`.customerNumber
INNER JOIN `orderdetails` ON `orders`.orderNumber = `orderdetails`.orderNumber
INNER JOIN `products` ON `orderdetails`.productCode = `products`.productCode
where productName like '%Ford%'
    and productName not like '%A%';
-- cách 2 
select `customers`.customerNumber,`customers`.customerName,`customers`.contactLastName,
`customers`.contactFirstName,`products`.productCode,`products`.productName,`products`.productLine
from `customers`,`orders`,`orderdetails`,`products`
where `customers`.customerNumber = `orders`.customerNumber
    and `orders`.orderNumber = `orderdetails`.orderNumber
    and `orderdetails`.productCode = `products`.productCode
    and productName like '%Ford%'
    and productName not like '%A%';
/*Đưa ra thông tin về các mặt hàng chưa có ai đặt mua trong năm 2005 nhưng
có đặt trong năm 2004*/
select `orders`.orderNumber,`orders`.orderDate,`products`.productCode,`products`.productName,
`products`.productLine,`products`.productVendor
from `orders`
INNER JOIN `orderdetails` ON `orders`.orderNumber = `orderdetails`.orderNumber
INNER JOIN `products` ON `orderdetails`.productCode = `products`.productCode
where year(orderDate) = 2004 and year(orderDate) != 2005;
/* Đưa ra các đơn hàng trong tháng 5/2005 (gồm orderDate, requiredDate,
Status) và tổng giá trị của mỗi đơn hàng lớn hơn 10 000 nhưng không vượt
quá 50 000*/ 
select orderDate,requiredDate,status,(quantityOrdered * priceEach) as TongGT
from `orders`
INNER JOIN `orderdetails` ON `orders`.orderNumber = `orderdetails`.orderNumber
where month(orderDate) = 5
    and year(orderDate) = 2005
    and (quantityOrdered * priceEach) > 10000 
and (quantityOrdered * priceEach) < 50000;
/* Đưa ra thông tin về các dòng sản phẩm và số lượng sản phẩm của dòng sản
phẩm đó. Sắp xếp theo thứ tự số lượng giảm dần.*/
select `productlines`.productLine,`productlines`.textDescription,
    count(`products`.productLine) as SoLuongSP
from `productlines`
INNER JOIN `products` ON `products`.productLine = `productlines`.productLine
group by `products`.productLine
order by SoLuongSP desc;
/*Đưa ra thông tin về các sản phẩm của dòng sản phẩm Motorcycles mà số 
ký tự trong tên sản phẩm không vượt quá 25. */
select productCode,SUBSTRING(productName, 1, 25) AS productName,
productLine,productVendor,productDescription
from `products`;
/*Đưa ra thông tin về nhân viên mà văn phòng nơi họ làm việc có tổng các
chữ số thành phần của số điện thoại là lớn nhất*/
select * from `employees`;
select * from `offices`;

/*************************************************************** */
/**************************Hoàn thành bài Làm ***************************** */
/*************************************************************** */