SQL Tutorial Book Tags
CTRL K
    CTRL K
      • Book
        • Introduction
          • What is this textbook devoted to?
          • What do you need to work with the textbook?
          • How to use this textbook
          • Terminology used and implementation features
          • Acknowledgments
        • Common mistakes in solving SELECT statement exercises
          • Computer Firm Database
            • Exercise #1
            • Exercise #2
            • Exercise #3
            • Exercise #5
            • Exercise #6
            • Exercise #7
            • Exercise #8
            • Exercise #10
            • Exercise #11
            • Exercise 71
            • Exercise #13
            • Exercise 3 (rating)
            • Exercise #15
            • Exercise #16
            • Exercise #17
            • Exercise #18
            • Exercise #20
            • Exercise #23
            • Exercise #24
            • Exercise #25
            • Exercise #26
            • Exercise #27
            • Exercise #28
          • Database «Recycled materials company»
            • Exercise #30
            • Exercise #59
            • Exercise #60
            • Exercise 128
          • Database «Ships»
            • Exercise #70
            • Exercise #37
            • Exercise #38
            • Exercise #39
            • Exercise #151
            • Exercise #(-2)
            • Exercise #46
            • Exercise #51
            • Exercise #53
            • Exercise #54
            • Exercise #55
            • Exercise #56
            • Exercise #57
            • Exercise #32
            • Exercise 78
          • Database «Airport»
            • Exercise #77
            • Exercise 93
            • Exercise 124
          • Database «Painting»
          • DML-exercises mistakes
          • Tips and solutions
            • Exercise #2 (tips and solutions)
            • Exercise #6 (tips and solutions)
            • Exercise #7 (tips and solutions)
            • Exercise #8 (tips and solutions)
            • Exercise #10 (tips and solutions)
            • Exercise #11 (tips and solutions)
            • Exercise #15 (tips and solutions)
            • Exercise #16 (tips and solutions)
            • Exercise #17 (tips and solutions)
            • Exercise #18 (tips and solutions)
            • Exercise #23 (tips and solutions)
            • Exercise #26 (tips and solutions)
            • Exercise #27 (tips and solutions)
            • Exercise #30 (tips and solutions)
            • Exercise #46 (tips and solutions)
            • Exercise #37 (tips and solutions)
            • Exercise #39 (tips and solutions)
            • Exercise #151 (tips and solutions)
            • Exercise #(-2) (tips and solutions)
            • Exercise #70 (tips and solutions)
            • Exercise #51 (tips and solutions)
            • Exercise #53 (tips and solutions)
            • Exercise #54 (tips and solutions)
            • Exercise #55 (tips and solutions)
            • Exercise #56 (tips and solutions)
            • Exercise #57 (tips and solutions)
            • Exercise #59 (tips and solutions)
            • Exercise #60 (tips and solutions)
        • SQL data manipulation language
          • SELECT statement
            • Simple SELECT statement
            • Sorting in order of days of birth
            • Predicates I
              • Comparison predicates
              • BETWEEN predicate
              • IN predicate
            • Renaming the columns and calculations in the result set
            • Predicates II
              • LIKE predicate
              • Using NULL value in the search conditions
            • Getting summarizing values
              • GROUP BY clause
              • HAVING clause
              • Summarizing data using ROLLUP
              • Combination of detailed and aggregated data
              • The sorting and NULL values
              • Aggregate function to aggregate function
              • Product of column values
            • Using multiple tables in a query
              • Explicit join operations
              • Equi-joins
              • FULL JOIN and MySQL
            • Conventional set-theoretic operations and the SELECT statement
              • Cartesian product
              • Union
              • Intersect and Except
              • Sequence of fulfillment of UNION, EXCEPT, and INTERSECT operators
              • EXISTS predicate
            • Relational division
            • Using SOME(ANY) and ALL keywords with comparison predicates
              • Again about subqueries
            • Data type conversion and CAST function
            • CASE statement
          • Data modification statements
            • INSERT statement
              • Adding rows to a table with identity column
            • Table-values constructor
            • UPDATE statement
            • DELETE statement
        • Prepare for the second stage of testing
          • Transact-SQL string and datetime functions
            • T-SQL Datetime functions
              • DATEADD function
              • DATEDIFF function
              • DATEPART function
              • DATENAME function
              • The first day of the week
              • DATEFROMPARTS function
            • String functions in MS SQL SERVER
              • ASCII and CHAR functions
              • LEFT function
              • CHARINDEX and PATINDEX functions
              • RIGHT function
              • LEN function
                • LEN() function, trailing spaces and uniqueness
              • SUBSTRING function
              • REVERSE function
              • REPLACE function
              • REPLICATE and STUFF functions
              • STR, SPACE, LTRIM and RTRIM functions
              • LOWER, UPPER, SOUNDEX and DIFFERENCE functions
              • UNICODE and NCHAR functions
          • Numeric functions in SQL Server
            • ROUND function
            • CEILING and FLOOR functions
            • LOG and EXP functions
            • POWER and SQRT functions
          • Typical problems
            • The number-sequence generation
            • Numbering of a query result set
              • Numbering of rows in accordance with the order of values of a primary key
              • Numbering rows when there are duplicates in the results column
            • About the use of the operator CASE
              • WHERE clause
              • GROUP BY clause
              • HAVING clause
              • ORDER BY clause
            • Once more about NULL values
            • Three-valued logic and WHERE clause
              • NOT IN predicate
            • These “sly” outer joins
            • About implicit conversion of SQL Server data types
            • Random sampling of rows from a table in SQL Server
            • Correlated subqueries
              • Running totals
            • Grouping of time intervals
            • Conversion of the date to a string
            • Least-square method
        • New sql features
          • MERGE statement
          • Ranking functions
            • ROW_NUMBER function
            • RANK() and DENSE_RANK() functions
            • NTILE function
          • Window functions
            • Paging records
            • Other examples of using window functions
            • LAG and LEAD functions
            • FIRST_VALUE and LAST_VALUE functions
            • COUNT DISTINCT and window functions
          • CROSS APPLY / OUTER APPLY
          • CONCAT function
          • PIVOT and UNPIVOT Operators
            • PIVOT operator
            • UNPIVOT operator
            • PostgreSQL CROSSTAB
          • Common table expressions (CTE)
            • Recursive CTE
              • About generation of number sequences in SQL Server
          • EOMONTH function
          • STRING_AGG function
          • STRING_SPLIT function
          • CHOOSE function
          • TRANSLATE function
        • Notes about data types
          • CHAR and VARCHAR
          • Float(n)
          • Integer division
          • XML Data Type Methods
        • Data Definition Language (SQL DDL)
          • Creation of Base Tables
          • Entity Integrity
          • Check Constraints
          • ALTER TABLE statement
          • Default values
          • Referential integrity: FOREIGN KEY
          • Subqueries in Check Constraints
          • Table Level Check Constraints
          • INFORMATION_SCHEMA and Oracle
        • Optimizing
          • SQL Server query plan explanation
          • PostgreSQL query plan explanation
          • Oracle query plan explonation
          • MySQL. Usage of query variables
            • Equivalent of recursive CTEs
            • Equivalents for analytic functions
            • Performance
        • Graph databases
          • SQL Server graph databases
          • Querying graph database data
        • Appendixes
          • Appendix 1. Databases description
            • Computer firm
            • Recycled firm
            • Ships
            • Airport
            • Painting
            • Football
          • Appendix 2. Exercises list
          • Appendix 3. Chronicles of Torus
            • Torus Planet
        • Finally
        • Bibliography
      • Pages
        • Contacts
        • Links
        • Team
      • Introduction
        • What is this textbook devoted to?
        • What do you need to work with the textbook?
        • How to use this textbook
        • Terminology used and implementation features
        • Acknowledgments
      • Common mistakes in solving SELECT statement exercises
        • Computer Firm Database
          • Exercise #1
          • Exercise #2
          • Exercise #3
          • Exercise #5
          • Exercise #6
          • Exercise #7
          • Exercise #8
          • Exercise #10
          • Exercise #11
          • Exercise 71
          • Exercise #13
          • Exercise 3 (rating)
          • Exercise #15
          • Exercise #16
          • Exercise #17
          • Exercise #18
          • Exercise #20
          • Exercise #23
          • Exercise #24
          • Exercise #25
          • Exercise #26
          • Exercise #27
          • Exercise #28
        • Database «Recycled materials company»
          • Exercise #30
          • Exercise #59
          • Exercise #60
          • Exercise 128
        • Database «Ships»
          • Exercise #70
          • Exercise #37
          • Exercise #38
          • Exercise #39
          • Exercise #151
          • Exercise #(-2)
          • Exercise #46
          • Exercise #51
          • Exercise #53
          • Exercise #54
          • Exercise #55
          • Exercise #56
          • Exercise #57
          • Exercise #32
          • Exercise 78
        • Database «Airport»
          • Exercise #77
          • Exercise 93
          • Exercise 124
        • Database «Painting»
        • DML-exercises mistakes
        • Tips and solutions
          • Exercise #2 (tips and solutions)
          • Exercise #6 (tips and solutions)
          • Exercise #7 (tips and solutions)
          • Exercise #8 (tips and solutions)
          • Exercise #10 (tips and solutions)
          • Exercise #11 (tips and solutions)
          • Exercise #15 (tips and solutions)
          • Exercise #16 (tips and solutions)
          • Exercise #17 (tips and solutions)
          • Exercise #18 (tips and solutions)
          • Exercise #23 (tips and solutions)
          • Exercise #26 (tips and solutions)
          • Exercise #27 (tips and solutions)
          • Exercise #30 (tips and solutions)
          • Exercise #46 (tips and solutions)
          • Exercise #37 (tips and solutions)
          • Exercise #39 (tips and solutions)
          • Exercise #151 (tips and solutions)
          • Exercise #(-2) (tips and solutions)
          • Exercise #70 (tips and solutions)
          • Exercise #51 (tips and solutions)
          • Exercise #53 (tips and solutions)
          • Exercise #54 (tips and solutions)
          • Exercise #55 (tips and solutions)
          • Exercise #56 (tips and solutions)
          • Exercise #57 (tips and solutions)
          • Exercise #59 (tips and solutions)
          • Exercise #60 (tips and solutions)
      • SQL data manipulation language
        • SELECT statement
          • Simple SELECT statement
          • Sorting in order of days of birth
          • Predicates I
            • Comparison predicates
            • BETWEEN predicate
            • IN predicate
          • Renaming the columns and calculations in the result set
          • Predicates II
            • LIKE predicate
            • Using NULL value in the search conditions
          • Getting summarizing values
            • GROUP BY clause
            • HAVING clause
            • Summarizing data using ROLLUP
            • Combination of detailed and aggregated data
            • The sorting and NULL values
            • Aggregate function to aggregate function
            • Product of column values
          • Using multiple tables in a query
            • Explicit join operations
            • Equi-joins
            • FULL JOIN and MySQL
          • Conventional set-theoretic operations and the SELECT statement
            • Cartesian product
            • Union
            • Intersect and Except
            • Sequence of fulfillment of UNION, EXCEPT, and INTERSECT operators
            • EXISTS predicate
          • Relational division
          • Using SOME(ANY) and ALL keywords with comparison predicates
            • Again about subqueries
          • Data type conversion and CAST function
          • CASE statement
        • Data modification statements
          • INSERT statement
            • Adding rows to a table with identity column
          • Table-values constructor
          • UPDATE statement
          • DELETE statement
      • Prepare for the second stage of testing
        • Transact-SQL string and datetime functions
          • T-SQL Datetime functions
            • DATEADD function
            • DATEDIFF function
            • DATEPART function
            • DATENAME function
            • The first day of the week
            • DATEFROMPARTS function
          • String functions in MS SQL SERVER
            • ASCII and CHAR functions
            • LEFT function
            • CHARINDEX and PATINDEX functions
            • RIGHT function
            • LEN function
              • LEN() function, trailing spaces and uniqueness
            • SUBSTRING function
            • REVERSE function
            • REPLACE function
            • REPLICATE and STUFF functions
            • STR, SPACE, LTRIM and RTRIM functions
            • LOWER, UPPER, SOUNDEX and DIFFERENCE functions
            • UNICODE and NCHAR functions
        • Numeric functions in SQL Server
          • ROUND function
          • CEILING and FLOOR functions
          • LOG and EXP functions
          • POWER and SQRT functions
        • Typical problems
          • The number-sequence generation
          • Numbering of a query result set
            • Numbering of rows in accordance with the order of values of a primary key
            • Numbering rows when there are duplicates in the results column
          • About the use of the operator CASE
            • WHERE clause
            • GROUP BY clause
            • HAVING clause
            • ORDER BY clause
          • Once more about NULL values
          • Three-valued logic and WHERE clause
            • NOT IN predicate
          • These “sly” outer joins
          • About implicit conversion of SQL Server data types
          • Random sampling of rows from a table in SQL Server
          • Correlated subqueries
            • Running totals
          • Grouping of time intervals
          • Conversion of the date to a string
          • Least-square method
      • New sql features
        • MERGE statement
        • Ranking functions
          • ROW_NUMBER function
          • RANK() and DENSE_RANK() functions
          • NTILE function
        • Window functions
          • Paging records
          • Other examples of using window functions
          • LAG and LEAD functions
          • FIRST_VALUE and LAST_VALUE functions
          • COUNT DISTINCT and window functions
        • CROSS APPLY / OUTER APPLY
        • CONCAT function
        • PIVOT and UNPIVOT Operators
          • PIVOT operator
          • UNPIVOT operator
          • PostgreSQL CROSSTAB
        • Common table expressions (CTE)
          • Recursive CTE
            • About generation of number sequences in SQL Server
        • EOMONTH function
        • STRING_AGG function
        • STRING_SPLIT function
        • CHOOSE function
        • TRANSLATE function
      • Notes about data types
        • CHAR and VARCHAR
        • Float(n)
        • Integer division
        • XML Data Type Methods
      • Data Definition Language (SQL DDL)
        • Creation of Base Tables
        • Entity Integrity
        • Check Constraints
        • ALTER TABLE statement
        • Default values
        • Referential integrity: FOREIGN KEY
        • Subqueries in Check Constraints
        • Table Level Check Constraints
        • INFORMATION_SCHEMA and Oracle
      • Optimizing
        • SQL Server query plan explanation
        • PostgreSQL query plan explanation
        • Oracle query plan explonation
        • MySQL. Usage of query variables
          • Equivalent of recursive CTEs
          • Equivalents for analytic functions
          • Performance
      • Graph databases
        • SQL Server graph databases
        • Querying graph database data
      • Appendixes
        • Appendix 1. Databases description
          • Computer firm
          • Recycled firm
          • Ships
          • Airport
          • Painting
          • Football
        • Appendix 2. Exercises list
        • Appendix 3. Chronicles of Torus
          • Torus Planet
      • Finally
      • Bibliography
      • Русский
      • English
      Book
      Common mistakes in solving SELECT statement exercises
      Tips and solutions
      Exercise #27 (tips and solutions)

      Exercise #27 (tips and solutions)

      See Item 1.2.

      To return to discussion of exercise #27

      To solve a problem on SQL-EX.RU

      #Tips and Solutions#Exercise 27

      Exercise #26 (tips and solutions)Exercise #30 (tips and solutions)
      ContactsLinksTeam
      © 2025 SQL-EX