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

  

Kho Bài Lab

lấy link bài lab

Bài thực hành số 6:

Bài làm :

CREATE DATABASE 1812767_Database_Lab06;

USE 1812767_Database_Lab06;

-- Lấy ra các sản phẩm có đơn đặt hàng trong tháng 5/2003.

select products.productName,orders.*

from products

join orderdetails on products.productCode=orderdetails.productCode

join orders on orderdetails.orderNumber=orders.orderNumber

where month(orders.orderDate)=5 and year(orders.orderDate)=2003;

-- Đưa ra các thông tin về các đơn hàng trong các tháng của năm 2005

select *, month(orderDate) as Month

from orders

where year(orderDate)=2005

group by month(orderDate);

-- Đưa ra thông tin về các đơn hàng và tổng giá trị đơn hàng

select *, sum(priceEach*quantityOrdered) as Total

from orderdetails

group by orderNumber

order by Total asc;

-- Đưa ra thông tin về đơn hàng có tổng giá trị đơn hàng nhỏ nhất

select *, sum(priceEach*quantityOrdered) as total

from orderdetails

group by orderNumber

having total<=ALL(

select sum(priceEach*quantityOrdered) as total

from orderdetails

group by orderNumber

);

-- Với mỗi khách hàng, đưa ra tổng số hàng, và tổng số tiền họ đã thanh toán

select products.productCode, customers.customerName,

customers.customerNumber, sum(quantityInStock),

sum(orderdetails.priceEach*orderdetails.quantityOrdered) as total

from customers

join orders on customers.customerNumber=orders.customerNumber

join orderdetails on orderdetails.orderNumber=orders.orderNumber

join products on products.productCode=orderdetails.productCode

group by customers.customerNumber;

-- 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.customerNumber, customers.contactLastName,

customers.contactFirstName, count(orders.status), orders.requiredDate

from customers

join orders on customers.customerNumber=orders.customerNumber

where orders.status='Cancelled' and year(requiredDate)=2003

group by customers.contactLastName;

-- Đưa ra các mã đơn đặt hàng có giá trị lớn nhất

-- không thuộc dòng sản phẩm  “Classic Cars”.

select orderdetails.orderNumber,orders.orderDate, products.productLine,

sum(orderdetails.priceEach*orderdetails.quantityOrdered) as total

from orders

join orderdetails on orderdetails.orderNumber=orders.orderNumber

join products on orderdetails.productCode=products.productCode

where products.productLine<>'Classic Cars'

group by orderdetails.orderNumber

having sum(orderdetails.priceEach*orderdetails.quantityOrdered) >=all(

select

sum(orderdetails.priceEach*orderdetails.quantityOrdered) as total

from orders

join orderdetails on orderdetails.orderNumber=orders.orderNumber

join products on orderdetails.productCode=products.productCode

where products.productLine<>'Classic Cars'

group by orderdetails.orderNumber

);

--  Đư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 productCode, productName, productLine, sum(quantityInStock) as total

from products

group by productline;

-- Đư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 *, offices.city

from employees

join offices on offices.officeCode=employees.officeCode;

-- Đư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à nhỏ nhất.

select concat_ws(' ',employees.firstName, employees.lastName) as fullName,

offices.phone

from employees

join offices on employees.officeCode=offices.officeCode

where char_length(replace(offices.phone,' ',''))=

(select max(char_length(replace(offices.phone,' ','')))from offices);

-- Đưa ra thông tin về tên khách hàng và tên các sản phẩm họ đã mua không

-- có chứa chữ “Ford” nhưng có chứa chữ “A”.

select customers.customerNumber, customers.customerName, products.productName

from customers

join orders on customers.customerNumber=orders.customerNumber

join orderdetails on orderdetails.orderNumber=orders.orderNumber

join products on products.productCode=orderdetails.productCode

where products.productName not like '%Ford%' and products.productName like '%A%'

group by products.productName;

--  Đưa ra thông tin về các mặt hàng có ít nhất một người đặt mua trong năm

-- 2005 nhưng không mua trong năm 2004.

select orders.*, count(orders.orderDate) as total

from orderdetails

join orders on orderdetails.orderNumber=orders.orderNumber

where year(orders.orderDate)=2005 and year(orders.orderDate)<>2004

group by orders.orderNumber;

--  Đưa ra thông tin về các đơn hàng trong tháng 3/2005 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á 40 000.

select orders.*, sum(A.priceEach*A.quantityOrdered) as total

from orderdetails A

join orders on A.orderNumber=orders.orderNumber

where year(orders.orderDate)=2005 and month(orders.orderDate)=3

and 10000<(A.priceEach*A.quantityOrdered)<40000

group by orders.orderNumber;

--  Đưa ra thông tin về các dòng sản phẩm và các sản phẩm của từng dòng sản phẩm đó.

Sắp xếp giảm dần theo thứ tự abc.

select productCode, productLine, productName

from products

group by productLine

order by productLine desc;

-- Đưa ra thông tin về các sản phẩm không thuộc dòng sản phẩm Motorcyclesmà

số ký tự trong tên sản phẩm không vượt quá 25.

select *

from products

where productLine <>'Motorcycles' and char_length(productName)<=25

group by productLine;

Bài thực hành số 7 

Giải:

CREATE DATABASE /*!32312 IF NOT EXISTS*/`1812767_database_lab07` /*

!40100 DEFAULT CHARACTER SET latin1 */;

USE `1812767_database_lab07`;

/*************************************************************** */

/**************************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. Tìm những nhân viên có tên là “Tom”

create fulltext index name

on employees(firstName);

select *

from `employees`

where match (firstName)

against('Tom');

-- b. Tìm các sản phẩm có chứa từ “Suzuki”

create fulltext index nameproduct

on products(productName);

select *

from products

where match (productName)

against('Suzuki');

-- c. Tìm các sản phẩm có chứa cả hai từ “1985” và “Supra”

create fulltext index nameproduct

on products(productName);

select *

from `products`

where match (productName)

against('+1985 +Supra' in boolean mode);

-- d. Tìm các sản phẩm có chứa từ “Ford” nhưng không có từ “truck”

create fulltext index productName

on products(productName);

select *

from products

where match (productName)

against('+Ford -truck' in boolean mode);

-- e. Tìm khách hàng có tên bắt đầu bằng từ “Au”

create fulltext index name

on customers(customerName);

select *

from customers

where match (customerName)

against('Au* ' in boolean mode);

-- f. Tìm các sản phẩm có chứa “1999” đồng thời với các sản

-- phẩm có từ “Yamaha” thì kết quả trả về trước tiên.

create fulltext index productName

on products(productName);

select *

from products

where match (productName)

against('+Yamaha 1999 ' in boolean mode);

-- g.Tìm các khách hàng mà địa chỉ có từ “Street” nhưng không có từ “North”,

-- hơn nữa với địa chỉ có từ “Allen” thì xếp hàng thấp hơn

create fulltext index addressLine

on customers(addressLine1);

select *

from customers

where match (addressLine1)

against('+Street -North ~Allen' in boolean mode);

-- h. Tìm các sản phẩm có các từ “Ford truck” và “Ford Phaeton”, ưu tiên trả

-- về kết quả với “Ford truck” lên trước tiên

create fulltext index productName

on products(productName);

select *

from products

where match (productName)

against('+Ford +(>truck <Phaeton)' in boolean mode);

--  i. Tìm các nhân viên không có tên là “Bow”. Kết quả trả về thì ưu tiên trả về

-- các nhân viên thuộc bộ phận Sale lên trước tiên

create fulltext index lastName5

on employees(jobTitle);

select *

from employees

where match (jobTitle)

against('Sales'  in boolean mode);

-- j. Tìm các sản phẩm có ít nhất hai từ “1969” hoặc “Dodge”, nhưng không có

-- từ “Chevrol

create fulltext index productName

on products(productName);

select *

from products

where match (productName)

against('1969 Dodge -Chevrol' in boolean mode);

-- k. Tìm các khách hàng có chứ từ “Mini” và đặt thứ hạng thấp hơn cho các

-- hàng có từ “Marseille” hay từ “Caravy”

create fulltext index customerName

on customers(customerName);

select *

from customers

where match (customerName)

against('+Mini ~Marseille ~Caravy' in boolean mode);

/*************************************************************** */

/************************** ///Bài Làm/// ***************************** */

/*************************************************************** */