SQL Must Know 50 Questions (1 - 5)

This article has been synchronized to: Murakami Haruka

SQL1 retrieves all IDs from the Customers table

describe

The existing table Customers is as follows:

cust_id
A
B
C

question

Write an SQL statement to retrieve all cust_ids from the Customers table.

sample answer

Returns the contents of the cust_id column

cust_id
A
B
C

Example

 DROP TABLE IF EXISTS `Customers`;

CREATE TABLE IF NOT EXISTS `Customers`(
    cust_id VARCHAR(255) DEFAULT NULL
);

INSERT `Customers` VALUES ('A'),('B'),('C');

answer

Consider the simplest query statement, because there is only one column cust_id Customers the table, so we can use the following two solutions.

• The first way is to select a specific column for output, which is also a way we recommend to use in our work, describing all the column names that need to be output.

 SELECT cust_id FROM Customers;

• The second way, use * to output all columns in the table, because Customers there is only one column in the table, so this method can be used. But in daily work, even if the query result column contains all the fields of the database table, do not directly use * .

 SELECT * FROM Customers;

SQL2 retrieve and list a list of ordered products

describe

The table OrderItems contains a non-empty column prod_id representing the item id, which contains all ordered items (some have been ordered multiple times).

prod_id
a1
a2
a3
a4
a5
a6
a7

question

Write an SQL statement to retrieve and list a deduplicated list of all ordered items (prod_id).

Example

 DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
    prod_id VARCHAR(255) NOT NULL COMMENT '商品id'
);
INSERT `OrderItems` VALUES ('a1'),('a2'),('a3'),('a4'),('a5'),('a6'),('a6')

answer

To deduplicate the result, use the DISTINCT keyword. When using, follow the fields that need to be deduplicated to ensure that the query results of these deduplicated fields are not repeated.

 SELECT DISTINCT prod_id FROM OrderItems;

In addition, you can also use the GROUP BY keyword. Changing the keyword supports deduplication and synchronously return information of other fields.

 SELECT prod_id FROM OrderItems GROUP BY prod_id;

SQL3 retrieve all columns

describe

Now we have the Customers table (with columns cust_id for customer id and cust_name for customer name).

cust_id	cust_name
a1	andy
a2	ben
a3	tony
a4	tom
a5	an
a6	lee
a7	hex

question

Need to write SQL statement to retrieve all columns.

Sample result

Returns all columns cust_id and cust_name.

cust_id	cust_name
a1	andy
a2	ben
a3	tony
a4	tom
a5	an
a6	lee
a7	hex

Example

 DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
    cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
    cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('a1','andy'),('a2','ben'),('a3','tony'),('a4','tom'),('a5','an'),('a6','lee'),('a7','hex');

answer

Similar to the first question, the simplest query statement, but there is one more column than the first question, so two methods can also be used.

• Use * for all columns.

 SELECT * FROM Customers;

• List the columns that need to be printed out in detail.

 SELECT cust_id, cust_name FROM Customers;

SQL4 retrieve customer name and sort

describe

There is a table Customers, cust_id represents the customer id, and cust_name represents the customer name.

cust_id	cust_name
a1	andy
a2	ben
a3	tony
a4	tom
a5	an
a6	lee
a7	hex

question

Retrieves all customer names (cust_name) from Customers and displays the results from Z to A.

Sample result

Return customer name cust_name

cust_name
tony
tom
lee
hex
ben
andy
an

Example

 DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
    cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
    cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('a1','andy'),('a2','ben'),('a3','tony'),('a4','tom'),('a5','an'),('a6','lee'),('a7','hex');

answer

To sort the data in the database, the keyword ORDER BY is required. In addition, by default, the results of the columns in SQL are arranged in positive order by default, that is, the execution results of the following statements are the same in practice.

 SELECT cust_name FROM Customers;

 SELECT cust_name FROM Customers ORDER BY cust_name ASC;

To output the query results in reverse order, you need to use the DESC keyword to indicate reverse order output.

 SELECT cust_name FROM Customers ORDER BY cust_name DESC;

SQL5 sort customer ID and date

describe

Has Orders table

cust_id	order_num	order_date
andy	aaaa	2021-01-01 00:00:00
andy	bbbb	2021-01-01 12:00:00
bob	cccc	2021-01-10 12:00:00
dick	dddd	2021-01-11 00:00:00

question

Write a SQL statement to retrieve the customer ID (cust_id) and order number (order_num) from the Orders table, and sort the results first by customer ID and then in reverse order by order date.

sample answer

Returns 2 columns, cust_id and order_num

cust_id	order_num
andy	bbbb
andy	aaaa
bob	cccc
dick	dddd

Example parsing

First arrange according to cust_id, andy before bob and dick, and then arrange according to order_date, the order time of order number bbbb is "2021-01-01 12:00:00" greater than the time of order number aaaa "2021-01-01 00:00:00"

Example

 DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders` (
  `cust_id` varchar(255) NOT NULL COMMENT '顾客 ID',
  `order_num` varchar(255) NOT NULL COMMENT '订单号',
  `order_date` timestamp NOT NULL COMMENT '订单时间'
);
INSERT INTO `Orders` VALUES ('andy','aaaa','2021-01-01 00:00:00'),
('andy','bbbb','2021-01-01 12:00:00'),
('bob','cccc','2021-01-10 12:00:00'),
('dick','dddd','2021-01-11 00:00:00');

answer

To sort the columns, you need to use the ORDER BY keyword, in addition to the positive and reverse order output.

• ASC : Positive sequence output, which is also the default output.
• DESC : output in reverse order.

 SELECT cust_id, order_num FROM Orders ORDER BY cust_id, order_date DESC;

Thanks

Thanks for the list of topics provided by Niuke.com .