[SQL question brushing] day14 --- special exercise on SQL using subquery

Blogger nickname:
Blogger homepage link: Portal of blogger Homepage

Blog column page connection: Column portal - Network Security Technology
Original intention of creation: the original intention of this blog is to communicate with technical friends. Everyone's technology has shortcomings, and the blogger is the same. He is open-minded to seek advice, and hopes that all technical friends will give guidance.
Blogger's motto: find light, follow light, become light, scatter light;
Blogger research direction: penetration testing, machine learning;
Blogger's message: Thank you for your support. Your support is my driving force;

Learning website jump link: Niuke brush question net

preface

I recommend a very good question brushing software to you, Niuke question brushing Network - learn SQL together

Why do bloggers like to learn with this website?

There are three main reasons:

1. There are a large number of interview question banks inside

2. The industry coverage is relatively comprehensive

3. The title brushing process is from simple to difficult

1, SQL using subquery

(1) Subquery

Subquery is a query nested in another statement, such as: select, insert, update, delete

(2) Nested subquery

Subqueries can be nested in another subquery. SQL Server supports up to 32 nesting levels

(3) Relevant subprogram

① Related subqueries are subqueries that use the values of external queries. That is, it depends on the value of the external query

② Related subqueries cannot be executed independently as simple subqueries

③ Repeat the related sub query for each row evaluated by the external query. Related subqueries are also called repeated subqueries.

2, Brush questions

1. Brush question 1

(1) Title

Title: return the list of customers who purchase products with a price of $10 or more

Description: OrderItems represents the order goods table, which contains the field order No.: order_num, order price: item_price； The Orders table represents the order information table and contains the customer id: cust_id and order number: order_num

(2) Examples:

Input:

DROP TABLE IF EXISTS `OrderItems`;

  CREATE TABLE IF NOT EXISTS `OrderItems`(

    order_num VARCHAR(255) NOT NULL COMMENT 'Commodity order No',

    item_price INT(16) NOT NULL COMMENT 'Selling price'

  );

  INSERT `OrderItems` VALUES ('a1',10),('a2',1),('a2',1),('a4',2),('a5',5),('a2',1),('a7',7);


  DROP TABLE IF EXISTS `Orders`;

  CREATE TABLE IF NOT EXISTS `Orders`(

    order_num VARCHAR(255) NOT NULL COMMENT 'Commodity order No',

    cust_id VARCHAR(255) NOT NULL COMMENT 'customer id'

  );

  INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a2','cust1'),('a4','cust2'),('a5','cust5'),('a2','cust1'),('a7','cust7');


Output:


cust10

(3) Code

select cust_idfrom Orderswhere order_num in (

    select order_num

    from OrderItems

    where item_price >=10

)

(4) Operation results

2. Brush question 2

(1) Title

Title: determine which orders purchased prod_ Product with ID BR01 (I)

Description: the table OrderItems represents the order item information table, prod_id is the product id; The Orders table represents the order table and cust_id stands for customer id and order date_ date

(2) Examples:

Input:

DROP TABLE IF EXISTS `OrderItems`;

  CREATE TABLE IF NOT EXISTS `OrderItems`(

    prod_id VARCHAR(255) NOT NULL COMMENT 'product id',

    order_num VARCHAR(255) NOT NULL COMMENT 'Commodity order No'

  );

  INSERT `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');


  DROP TABLE IF EXISTS `Orders`;

  CREATE TABLE IF NOT EXISTS `Orders`(

    order_num VARCHAR(255) NOT NULL COMMENT 'Commodity order No',

    cust_id VARCHAR(255) NOT NULL COMMENT 'customer id',

    order_date TIMESTAMP NOT NULL COMMENT 'Order time '

  );

  INSERT `Orders` VALUES ('a0001','cust10','2022-01-01 00:00:00'),('a0002','cust1','2022-01-01 00:01:00'),('a0003','cust1','2022-01-02 00:00:00'),('a0013','cust2','2022-01-01 00:20:00');


Output:


cust10|2022-01-01 00:00:00

cust1|2022-01-01 00:01:00

(3) Code

select cust_id,order_datefrom Orderswhere order_num in

(select order_numfrom OrderItemswhere prod_id = 'BR01')

(4) Operation results

3. Brush question 3

(1) Title

Title: return to purchase prod_ Email of all customers of the product with ID BR01 (1)

Description: you want to know the date of ordering BR01 products. There is a table OrderItems representing the order product information table, prod_id is the product id; The Orders table represents the order table and cust_id stands for customer id and order date_ date； The Customers table contains cust_email, customer email and cust_id customer id

(2) Examples:

Input:

DROP TABLE IF EXISTS `OrderItems`;

  CREATE TABLE IF NOT EXISTS `OrderItems`(

    prod_id VARCHAR(255) NOT NULL COMMENT 'product id',

    order_num VARCHAR(255) NOT NULL COMMENT 'Commodity order No'

  );

  INSERT `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');


  DROP TABLE IF EXISTS `Orders`;

  CREATE TABLE IF NOT EXISTS `Orders`(

    order_num VARCHAR(255) NOT NULL COMMENT 'Commodity order No',

    cust_id VARCHAR(255) NOT NULL COMMENT 'customer id',

    order_date TIMESTAMP NOT NULL COMMENT 'Order time '

  );

  INSERT `Orders` VALUES ('a0001','cust10','2022-01-01 00:00:00'),('a0002','cust1','2022-01-01 00:01:00'),('a0003','cust1','2022-01-02 00:00:00'),('a0013','cust2','2022-01-01 00:20:00');

DROP TABLE IF EXISTS `Customers`;CREATE TABLE IF NOT EXISTS `Customers`(

    cust_id VARCHAR(255) NOT NULL COMMENT 'customer id',

    cust_email VARCHAR(255) NOT NULL COMMENT 'customer email'

  );INSERT `Customers` VALUES ('cust10','cust10@cust.com'),('cust1','cust1@cust.com'),('cust2','cust2@cust.com');


Output:


cust10@cust.com

cust1@cust.com

(3) Code

select

    Customers.cust_emailfrom

    Ordersleft join OrderItems on OrderItems.order_num = Orders.order_numleft join Customers on Customers.cust_id = Orders.cust_idwhere

    OrderItems.prod_id = "BR01"

(4) Operation results

4. Brush question 4

(1) Title

Title: return the total amount of different orders of each customer

Description: we need a list of customer ID S, including the total amount they have ordered. The OrderItems table represents the order information. The OrderItems table has the order number: order_num and commodity selling price: item_price, commodity quantity: quantity.

(2) Examples:

Input:

DROP TABLE IF EXISTS `OrderItems`;CREATE TABLE IF NOT EXISTS `OrderItems`(

order_num VARCHAR(255) NOT NULL COMMENT 'Commodity order No',

item_price INT(16) NOT NULL COMMENT 'Selling price',

quantity INT(16) NOT NULL COMMENT 'Commodity quantity'

);INSERT `OrderItems` VALUES ('a0001',10,105),('a0002',1,1100),('a0002',1,200),('a0013',2,1121),('a0003',5,10),('a0003',1,19),('a0003',7,5);

DROP TABLE IF EXISTS `Orders`;CREATE TABLE IF NOT EXISTS `Orders`(

  order_num VARCHAR(255) NOT NULL COMMENT 'Commodity order No',

  cust_id VARCHAR(255) NOT NULL COMMENT 'customer id'

);INSERT `Orders` VALUES ('a0001','cust10'),('a0003','cust1'),('a0013','cust2');


Output:


cust2|2242.000

cust10|1050.000

cust1|104.000

(3) Code

SELECT 

    o.cust_id,

    sum(oi.item_price * oi.quantity) AS total_orderedFROM Orders o JOIN OrderItems oi

    USING (order_num)GROUP BY o.cust_idORDER BY total_ordered DESC

(4) Operation results

5. Brush question 5

(1) Title

Title: retrieve all product names and corresponding total sales from the Products table

Description: retrieve all product names from the Products table: prod_name. Product ID: prod_id

OrderItems represents the order commodity table, and the order product is prod_id, quantity sold: quantity

(2) Examples:

Input:

DROP TABLE IF EXISTS `Products`;CREATE TABLE IF NOT EXISTS `Products` (

`prod_id` VARCHAR(255) NOT NULL COMMENT 'product ID',

`prod_name` VARCHAR(255) NOT NULL COMMENT 'Product Name:'

);INSERT INTO `Products` VALUES ('a0001','egg'),

('a0002','sockets'),

('a0013','coffee'),

('a0003','cola');

DROP TABLE IF EXISTS `OrderItems`;CREATE TABLE IF NOT EXISTS `OrderItems`(

prod_id VARCHAR(255) NOT NULL COMMENT 'product id',

quantity INT(16) NOT NULL COMMENT 'Commodity quantity'

);INSERT `OrderItems` VALUES ('a0001',105),('a0002',1100),('a0002',200),('a0013',1121),('a0003',10),('a0003',19),('a0003',5);


Output:


egg|105.000

sockets|1300.000

coffee|1121.000

cola|34.000

(3) Code

select

  prod_name,

  sum(quantity) as quant_soldfrom

  Products,

  OrderItemswhere

  OrderItems.prod_id = Products.prod_idgroup by

  prod_name

(4) Operation results

This article is the 14th day of SQL question brushing

Welcome to click the link on the right to learn with the blogger Click to learn

last

I know that most junior and middle-level Java engineers want to improve their skills, often by groping for growth or signing up for classes, but for training institutions, the tuition fee is nearly 10000 yuan, which is really a lot of pressure. The self-study effect that is not systematic is inefficient and long, and it is easy to encounter the stagnation of ceiling technology!

Therefore, I have collected and sorted out a complete set of learning materials for Java development and sent it to you. The original intention is also very simple, that is, I hope to help friends who want to improve themselves by self-study but do not know where to start, and at the same time reduce everyone's burden.

Xiaobian has been encrypted: ahr0chm6ly9kb2nzlnfxlmnvbs9kb2mvrvrvm9asgxqzuvstlkwunc = = for security reasons, we have encoded the website through base64. You can get the website through base64 decoding.

Tags: Database SQL search engine Interview Eclipse

Posted by rcmehta_14 on Fri, 09 Sep 2022 02:26:41 +0930

Programs Wiki

[SQL question brushing] day14 --- special exercise on SQL using subquery

preface

1, SQL using subquery

(1) Subquery

(2) Nested subquery

(3) Relevant subprogram

2, Brush questions

1. Brush question 1

(1) Title

(2) Examples:

(3) Code

(4) Operation results

2. Brush question 2

(1) Title

(2) Examples:

(3) Code

(4) Operation results

3. Brush question 3

(1) Title

(2) Examples:

(3) Code

(4) Operation results

4. Brush question 4

(1) Title

(2) Examples:

(3) Code

(4) Operation results

5. Brush question 5

(1) Title

(2) Examples:

(3) Code

(4) Operation results

last

Popular Tags