Content
Login
SQL Problems and solutions
S. I. Moiseenko
22:37
Last added:
Least-square method
TRANSLATE function
Appendix 2. Exercises list page 2
Explicit join operations
Football
Exercise #17 (tips and solutions)
Exercise #17
UPDATE statement page 2
Exercise #151 (tips and solutions)
Exercise #151 page 4
Tags
tags search
aggregate functions
Airport
ALL
AND
AS keyword
ASCII
AVG
Battles
Bezhaev
Bismarck
C.J.Date
calculated columns
Cartesian product
CASE
cast
CHAR
CHARINDEX
Chebykin
check constraint
classes
COALESCE
common table expressions
comparison predicates
Computer firm
CONSTRAINT
CONVERT
correlated subqueries
COUNT
CROSS APPLY
CTE
data type conversion
data types
database schema
DATEADD
DATEDIFF
DATENAME
DATEPART
DATETIME
date_time functions
DDL
DEFAULT
DEFAULT VALUES
DELETE
DISTINCT
DML
duplicates
edge
equi-join
EXCEPT
exercise (-2)
More tags
exercise 19
exercise 23
exercise 32
exercise 37
exercise 39
exercise 46
exercise 54
exercise 55
exercise 56
exercise 57
exercise 7
exercise 70
exercise 8
exercises
EXISTS
FLOAT
FOREIGN KEY
FROM
FULL JOIN
GROUP BY
grouping
Guadalcanal
HAVING
head ships
IDENTITY
IN
income
INFORMATION_SCHEMA
inner join
INSERT
INTERSECT
IS NOT NULL
IS NULL
ISNULL
join operations
laptop
launched year
LEFT
LEFT OUTER JOIN
LEN
LIKE
LTRIM
MAX
MIN
mistakes
money
MySQL
NATURAL JOIN
node
NOT
NOT IN
NULL
NULLIF
number sequences
number-sequence generation
numbering
ON DELETE CASCADE
OR
Oracle
ORDER BY
outcome
Outcomes
outer joins
OVER
paging
Painting
PARTITION BY
Pass_in_trip
PATINDEX
PC
PIVOT
PostgreSQL
predicates
primary key
printer
Product
Ranking functions
recursive CTE
renaming columns
REPLACE
RIGHT
RIGHT JOIN
ROUND
rounding
ROW_NUMBER
ships
sorting
SQL Server
SQL Server 2012
SQL-92
sql-ex.ru
string functions
subquery
SUBSTRING
SUM
tables join
tips and solutions
Torus
Transact-SQL
Trip
TRUNCATE TABLE
type conversion
UNION
UNION ALL
UNKNOWN
UNPIVOT
UPDATE
varchar
WHERE
window functions
WITH
XML
XPath
XQuery
Купить прокси
высокого качества с поддержкой всех протоколов и неограниченным трафиком на Proxy5
Content:
Introduction
(1.1)
What is this textbook devoted to?
(1.2)
What do you need to work with the textbook?
(1.3)
How to use this textbook
(1.4)
Terminology used and implementation features
(1.5)
Acknowledgments
(Part I)
Common mistakes in solving SELECT statement exercises
(Chapter 1)
Computer Firm Database
(1.1)
Exercise #1
(1.2)
Exercise #2
(1.3)
Exercise #3
(1.4)
Exercise #5
(1.5)
Exercise #6
(1.6)
Exercise #7
(1.7)
Exercise #8
(1.8)
Exercise #10
(1.8)
Exercise #11
(1.8)
Exercise 71
(1.10)
Exercise #13
(1.21)
Exercise 3 (rating)
(1.11)
Exercise #15
(1.12)
Exercise #16
(1.13)
Exercise #17
(1.14)
Exercise #18
(1.15)
Exercise #20
(1.16)
Exercise #23
(1.17)
Exercise #24
(1.18)
Exercise #25
(1.19)
Exercise #26
(1.20)
Exercise #27
(1.21)
Exercise #28
(Chapter 2)
Database «Recycled materials company»
(2.1)
Exercise #30
(2.2)
Exercise #59
(2.3)
Exercise #60
(2.4)
Exercise 128
(Chapter 3)
Database «Ships»
(3.7.)
Exercise #70
(3.2.)
Exercise #37
(3.3.)
Exercise #38
(3.4.)
Exercise #39
(3.5.)
Exercise #151
(3.6.)
Exercise #(-2)
(3.1.)
Exercise #46
(3.8.)
Exercise #51
(3.9.)
Exercise #53
(3.10.)
Exercise #54
(3.11.)
Exercise #55
(3.12.)
Exercise #56
(3.13.)
Exercise #57
(3.14.)
Exercise #32
(3.15)
Exercise 78
(Chapter 14)
Database «Airport»
(9.1)
Exercise #77
(9.2)
Exercise 93
(9.3)
Exercise 124
(Chapter 15)
Database «Painting»
DML-exercises mistakes
(Chapter 4)
Tips and solutions
(4.1.)
Exercise #2 (tips and solutions)
(4.2.)
Exercise #6 (tips and solutions)
(4.3.)
Exercise #7 (tips and solutions)
(4.4.)
Exercise #8 (tips and solutions)
(4.5.)
Exercise #10 (tips and solutions)
(4.6.)
Exercise #11 (tips and solutions)
(4.7.)
Exercise #15 (tips and solutions)
(4.8.)
Exercise #16 (tips and solutions)
(4.9.)
Exercise #17 (tips and solutions)
(4.10.)
Exercise #18 (tips and solutions)
(4.11.)
Exercise #23 (tips and solutions)
(4.12.)
Exercise #26 (tips and solutions)
(4.13.)
Exercise #27 (tips and solutions)
(4.14.)
Exercise #30 (tips and solutions)
(4.15.)
Exercise #46 (tips and solutions)
(4.16.)
Exercise #37 (tips and solutions)
(4.17.)
Exercise #39 (tips and solutions)
(4.18.)
Exercise #151 (tips and solutions)
(4.19.)
Exercise #(-2) (tips and solutions)
(4.20.)
Exercise #70 (tips and solutions)
(4.21.)
Exercise #51 (tips and solutions)
(4.22.)
Exercise #53 (tips and solutions)
(4.23.)
Exercise #54 (tips and solutions)
(4.24.)
Exercise #55 (tips and solutions)
(4.25.)
Exercise #56 (tips and solutions)
(4.26.)
Exercise #57 (tips and solutions)
(4.27.)
Exercise #59 (tips and solutions)
(4.28.)
Exercise #60 (tips and solutions)
(Part II)
SQL data manipulation language
(Chapter 5)
SELECT statement
(5.1)
Simple SELECT statement
Sorting in order of days of birth
(5.2.)
Predicates I
(5.2.1.)
Comparison predicates
(5.2.2.)
BETWEEN predicate
(5.2.3.)
IN predicate
(5.3.)
Renaming the columns and calculations in the result set
(5.4.)
Predicates II
(5.4.1.)
LIKE predicate
(5.4.2.)
Using NULL value in the search conditions
(5.5.)
Getting summarizing values
(5.5.1.)
GROUP BY clause
(5.5.2.)
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
(5.6.)
Using multiple tables in a query
(5.6.1.)
Explicit join operations
Equi-joins
FULL JOIN and MySQL
(5.7)
Conventional set-theoretic operations and the SELECT statement
(5.7.1.)
Cartesian product
(5.7.2.)
Union
(5.7.3.)
Intersect and Except
(5.7.4)
Sequence of fulfillment of UNION, EXCEPT, and INTERSECT operators
(5.7.5.)
EXISTS predicate
Relational division
(5.8)
Using SOME(ANY) and ALL keywords with comparison predicates
(5.8.1.)
Again about subqueries
(5.9.)
Data type conversion and CAST function
(5.10.)
CASE statement
(Chapter 6)
Data modification statements
(6.1.)
INSERT statement
(6.1.1.)
Adding rows to a table with identity column
Table-values constructor
(6.2.)
UPDATE statement
(6.3.)
DELETE statement
(Part III)
Prepare for the second stage of testing
(Chapter 7)
Transact-SQL string and datetime functions
(7.1.)
T-SQL Datetime functions
(7.1.1)
DATEADD function
(7.1.2)
DATEDIFF function
(7.1.3)
DATEPART function
(7.1.4)
DATENAME function
(7.1.5)
The first day of the week
(7.1.6)
DATEFROMPARTS function
(7.2)
String functions in MS SQL SERVER
(7.2.1.)
ASCII and CHAR functions
(7.2.2.)
LEFT function
(7.2.3.)
CHARINDEX and PATINDEX functions
(7.2.4.)
RIGHT function
(7.2.5.)
LEN function
LEN() function, trailing spaces and uniqueness
(7.2.6.)
SUBSTRING function
(7.2.7.)
REVERSE function
(7.2.8.)
REPLACE function
(7.2.9.)
REPLICATE and STUFF functions
(7.2.10.)
STR, SPACE, LTRIM and RTRIM functions
(7.2.11.)
LOWER, UPPER, SOUNDEX and DIFFERENCE functions
(7.2.12.)
UNICODE and NCHAR functions
Numeric functions in SQL Server
ROUND function
CEILING and FLOOR functions
LOG and EXP functions
POWER and SQRT functions
(Chapter 8)
Typical problems
(8.1.)
The number-sequence generation
(8.2.)
Numbering of a query result set
(8.2.1.)
Numbering of rows in accordance with the order of values of a primary key
(8.2.2.)
Numbering rows when there are duplicates in the results column
(8.3.)
About the use of the operator CASE
(8.3.1.)
WHERE clause
(8.3.2.)
GROUP BY clause
(8.3.3.)
HAVING clause
(8.3.4.)
ORDER BY clause
(8.4.)
Once more about NULL values
(8.5.)
Three-valued logic and WHERE clause
(8.5.1.)
NOT IN predicate
(8.6.)
These “sly” outer joins
(8.7.)
About implicit conversion of SQL Server data types
(8.8.)
Random sampling of rows from a table in SQL Server
(8.9.)
Correlated subqueries
Running totals
Conversion of the date to a string
Least-square method
(Part IV)
New sql features
MERGE statement
(Chapter 9)
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
(Chapter 10)
PIVOT and UNPIVOT Operators
PIVOT operator
UNPIVOT operator
PostgreSQL CROSSTAB
(Chapter 11)
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
(Part V)
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
(1.1.)
Computer firm
(1.2.)
Recycled firm
(1.3.)
Ships
(1.4.)
Airport
(1.5.)
Painting
(1.6.)
Football
Appendix 2. Exercises list
Appendix 3. Chronicles of Torus
Torus Planet
Finally
Bibliography