This is the 23rd day of my participation in Gwen Challenge.

Hello, I am Wukong.

Star: github.com/Jackson0714… Personal website: www.passjava.cn

Database topics

  • Why NoSQL when you have MySQL?
  • TempDB usage and performance issues
  • Table expressions (1)
  • Table expressions (2)
  • Explain SQL set operations in detail
  • Explain SQL single table query
  • Explain join queries in SQL
  • Explain subqueries in SQL

This article mainly summarizes the common SQL query topics for a single table query.

First we must understand that the terms of an SQL query are logically processed in the following order:

  • 1.FROM

  • 2.WHERE

  • 3.Group BY

  • 4.HAVING

  • 5.SELECT

  • 6.ORDER BY

Before doing the following topic, we can first prepare the environment, the following SQL script can help you to create a database, create tables, insert data.

Download the script file: tsqlals2008.zip

Topic:

1. Return the order generated in June 2007

(30 row(s) affected)

This question examines the filter date range

Reference answer:

Solution a:

If you want to filter a date range (for example, a YEAR or a MONTH), the natural way is to use functions like YEAR and MONTH.

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE YEAR(orderdate)= 2007
AND MONTH(orderdate)=6;
Copy the code

One caveat, however, is that in most cases, indexes cannot be used in an efficient manner after some processing has been applied to the columns in the filter criteria. To potentially make effective use of indexes, you need to adjust this so that the columns in the filter criteria are not processed.

Use a range filter condition:

orderdate >= ‘20070601’

AND orderdate < ‘20070701’

Scheme 2:

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate > = '20070601'
  AND orderdate < '20070701';
Copy the code

2. Return orders generated on the last day of each month

The tables involved: the sales.Orders table

 

DATEADD and DATEDIFF

DATEADD:

Returns the date by adding the specified number interval (a signed integer) to the specified datepart of the specified date.

  1. grammar
DATEADD (_datepart_ , number , d_ate_ )
Copy the code
  1. Parameters:

datepart

Number is an expression that can be resolved to an int added to date’s datepart. User-defined variables are valid.

If you specify a value with a decimal, the decimal is truncated and not rounded.

Date is an expression that can be resolved to a time, date, Smalldatetime, datetime, datetime2, or datetimeoffset value. Date can be an expression, a column expression, a user-defined variable, or a string literal. If the expression is a string literal, it must be resolved to a datetime value. To avoid uncertainty, use a four-digit year. For information about two-digit years, see the Two Digit Year Cutoff option.

  1. The return value

Datepart parameters

Dayofyear, day, and weekday return the same value.

Each datePart and its abbreviation return the same value.

If the datePart is month and the date month has more days than the return month, the last day of the return month is returned because the date does not exist in the return month. For example, September has 30 days; Therefore, the following two statements return 2006-09-30 00:00:00.000:

SELECT DATEADD(month.1.'2006-08-30')
SELECT DATEADD(month.1.'2006-08-31')
Copy the code

Number parameter

The number argument cannot exceed the range of int. In the following statement, the argument to number is outside the int range of 1. The following error message is returned: “Arithmetic overflow error occurred while converting expression to data type int.”

SELECT DATEADD(year.2147483648.'2006-07-31');
SELECT DATEADD(year.- 2147483649..'2006-07-31');
Copy the code

The date parameter

The date parameter cannot be incremented to a value outside its data range. In the following statement, the number value added to the date value is outside the range of date data types. The following error message is returned: “Overflow caused by adding value to ‘datetime’ column.”

SELECT DATEADD(year.2147483647.'2006-07-31');
SELECT DATEADD(year.- 2147483647..'2006-07-31');
Copy the code

DATEDIFF

Returns the count (a signed integer) of the specified datePart boundary between the specified startDate and endDate.

Grammar:

DATEDIFF ( *datepart* , *startdate* , *enddate* )
Copy the code

parameter

datepart

Is part of the startDate and EndDate that specify the type of boundary to be crossed. The following table lists all valid DatePart parameters. The user – defined variable equivalent is invalid.

Startdate, enddate

Is an expression that can be resolved to a time, date, Smalldatetime, datetime, datetime2, or datetimeoffset value. Date can be an expression, a column expression, a user-defined variable, or a string literal. Subtract startDate from endDate.

To avoid uncertainty, use a four-digit year. For information about two-digit years, see the Two Digit Year Cutoff option.

The return type

int

The return value

Each datePart and its abbreviation return the same value.

If the value returned is outside the range of int (-2,147,483,648 to +2,147,483,647), an error is returned. For millisecond, the maximum difference between startDate and endDate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For Second, the maximum difference is 68 years.

If only a time value is specified for both startDate and EndDate, and datePart is not a time datepart, 0 is returned.

The time zone offset portion of startDate or endate is not used when calculating the return value.

Because Smalldatetime is only accurate to minutes, when a Smalldatetime value is used as startDate or endDate, the seconds and milliseconds in the return value will always be set to 0.

If you specify a time value only for a variable of a date data type, the value of the missing date portion is set to the default: 1900-01-01. If only a date value is specified for a variable of a time or date data type, the value of the missing time portion is set to the default value: 00:00:00. If either startDate or EndDate contains only a time part and the other contains only a date part, the missing time and date parts are set to their respective defaults.

If startDate and EndDate belong to different date data types, and the time part or second fraction part of one is more accurate than the other, the missing part of the other is set to 0.

Reference answer:

Solution a:

SELECT * FROM Sales.Orders

WHERE MONTH(DATEADD(DAY.1,orderdate)) <> MONTH(orderdate)
Copy the code

Scheme 2:

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
WHERE orderdate = DATEADD(month, DATEDIFF(month.'20051231', orderdate), '20051231');
Copy the code

Get the last day of the month by date:

DATEADD(month, DATEDIFF(month, '20051231', orderdate), '20051231')
Copy the code

3. Return employees whose last name contains the letter ‘a’ twice or more times

The tables involved: the hr.employees table

The wildcard % (percent sign) is used as a wildcard

A percent sign represents strings of any length, including empty strings.

Reference answer:

SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname LIKE '%a%a%';
Copy the code

4. Return all orders with total price (quantity * unit price) greater than 10000 and sort by total price

Tables involved: the sales.OrderDetails table

 

Reference answer:

SELECT orderid,
SUM(unitprice*qty) AS totalValue
  FROM Sales.OrderDetails
  GROUP BY orderid
  HAVING SUM(unitprice*qty)>10000
  ORDER BY totalValue desc;
Copy the code

5. Return to the shipping country with the highest average freight in 2007

The tables involved: the sales.Orders table

Reference answer:

SELECT TOP(3) shipcountry, AVG(freight) AS avgfreight
FROM Sales.Orders
WHERE orderdate > = '20070101' AND orderdate < '20080101'
GROUP BY shipcountry
ORDER BY avgfreight DESC;Copy the code

6. Calculate the line number of each customer’s order separately in order of order date (with the order ID as an additional attribute).

The tables involved: the sales.Orders table

(830 row(s) affected)

Reference answer:

SELECT  custid ,
        orderdate ,
        orderid ,
        ROW_NUMBER(a)OVER ( PARTITION BY custid ORDER BY orderdate, orderid ) AS rownum
FROM    Sales.Orders
ORDER BY custid ,
        rownum;
Copy the code

7. Construct a SELECT statement that returns the gender of each employee based on their friendly title. For ‘Ms.’ and ‘Mrs’, return Female: for ‘Mr’, return ‘Male’; For other cases (for example, ‘Dr.’, ‘Unknown’ is returned.

The tables involved: the hr.employees table

  1. CASE expression in search format
SELECT  empid ,
        firstname ,
        lastname ,
        titleofcourtesy ,
        CASE WHEN titleofcourtesy IN ( 'Ms.'.'Mrs.' ) THEN 'Female'
             WHEN titleofcourtesy = 'Mr.' THEN 'Male'
             ELSE 'Unknown'
        END AS gender
FROM    HR.Employees
Copy the code
  1. Simple CASE expression format
SELECT  empid ,
        firstname ,
        lastname ,
        titleofcourtesy ,
        CASE titleofcourtesy
          WHEN 'Ms.' THEN 'Female'
          WHEN 'Mrs.' THEN 'Female'
          WHEN 'Mr.' THEN 'Male'
          ELSE 'Unknown'
        END AS gender;
FROM    HR.Employees
Copy the code

8. Return the customer ID and region of each customer. The rows in the output are sorted by region, with NULL values last (after all non-NULL values).

Note that the default behavior of NULL values in T-SQL is to prioritize NULL values (before all non-NULL values).

The tables involved: Sales.Customers table.

Reference answer:

SELECT  custid ,
        region
FROM    Sales.Customers
ORDER BY CASE WHEN region IS NULL THEN 1
              ELSE 0
         END ,
        region;
Copy the code

References:

SQL2008 technical Insider: T-SQL Language Basics

Welcome to follow my official account: “Wukong Chat Framework”

My lot

About the author: 8-year Veteran of Internet workplace | full stack engineer | super dad after 90 | open source practitioner | owner of public number 10,000 fans original number. Blue Bridge signed the author, the author of “JVM performance tuning practice” column, handwritten a set of 70,000 words SpringCloud practice summary and 30,000 words distributed algorithm summary. Welcome to follow my public account “Wukong Chat framework”, free access to learning materials.

I am Wukong, strive to become stronger, become super Saiya people!