KE
  • dotNet Web 3.0
  • Engineering Management
    • Process Planning (SDLC)
      • Software development process
      • Basics of SDLC models
      • Scrum
      • Kanban
      • Scrum vs Kanban: applicability
      • Scrumban
    • Estimation
      • Scope Concept
      • Estimates, Targets, and Commitments
      • Overestimate vs Underestimate
      • Decomposition and Recomposition
      • Analogy-based estimations
      • Estimating in Agile
  • Requirements
    • Software Requirements Engineering
      • Requirement definition
      • Levels of Requirements
      • Most common requirements risks
      • Characteristics of Excellent Requirements
      • Benefits from a High-Quality Requirements Process
      • Root Causes of Project Success and Failure
  • Design
    • OOD
      • Abstraction
      • Encapsulation
      • Inheritance vs Aggregation
      • Modularity
      • Polymorphism
      • Abstraction Qualities (cohesion, coupling, etc)
      • Types vs. Classes
      • Separation of concerns principle
      • SOLID
      • Design Patterns
        • Structural patterns
        • Creational patterns
        • Behavioral patterns
      • Most often used design patterns
      • Software Architecture Patterns (structure, pros & cons)
      • Inversion of Control Containers and the Dependency Injection pattern
      • Domain-Driven Design patterns
      • Anti-patterns
    • DB Design
      • Relational Terminology: Entities
      • Relational terminology: Attributes
      • Relational terminology: Records (Tuples)
      • Relationships (One-to-One, One-to-Many)
      • Understanding ER notation
      • Understanding normalization concept
      • Data Integrity
    • Modeling
      • UML: Basic Diagram Types
      • UML: Use Case Diagram (Essentials)
      • UML: Class Diagram (Essentials)
      • Entity Relationship Diagrams
      • Data Flow Diagrams
    • Security
      • Information security concepts
      • Access Control Lists (ACLs)
      • Access Control Models
      • .NET Cryptography Model
      • ASP.NET Identity
      • OWASP Top 10
      • Cross-Site Request Forgery (XSRF)
      • Protecting against cross-site scripting attacks (XSS)
      • Protecting against buffer overrun attacks
      • Protecting against SQL-injection attacks
      • CSRF/XSRF protection
    • Algorithms
      • Algorithms complexity (understanding, big O notation, complexity of common algorithms)
      • Array sorting methods (bubble sort, quick sort, merge sort)
      • Tree structure (construction, traversal)
      • Binary search algorithm
      • Hash table (creating, collisions)
      • Stack, queue, linked list (construction, understanding, usage)
  • Construction Core
    • Programming language
      • Declare namespaces, classes, interfaces, static and instance class members
      • Types casting
      • Value and reference types. Class vs Struct usage.
      • Properties and automatic properties
      • Structured Exception Handling, Exception filters
      • Collections and Generics
      • Dictionaries. Comparison of Dictionaries
      • Building enumerable types
      • Building cloneable objects
      • Building comparable types
      • Nullable types
      • Delegates, events and lambdas
      • Indexers and operator overloading
      • Anonymous types
      • Extension methods. Practices.
      • Custom Type Conversions (implicit/explicit keywords)
      • Strings and StringBuilder. String concatenation practices. String Interpolation
      • Serialization
      • System.IO namespace
      • LINQ to Objects
      • General Coding conventions for C#
      • Static Using Statement
      • Type Reflection
      • Custom attributes
      • Dispose and Finalizable patterns
      • Garbage collection
      • .Net Diagnostics
      • Implementing logging
      • Exception handling guidelines
      • Regular Expressions
      • Span<T> struct
      • C# - What's new?
      • .NET Standard overview
    • Concurrency
      • Understand differences between Concurrency vs Multi-threading vs Asynchronous
      • Concurrency: An Overview
      • Async basics
      • Task Parallelism
      • Basic Synchronization in C#
      • Deadlock problem
      • QueueBackgroundWorkItem or IHostedService for .NET Core
      • How to run Background Tasks in ASP.NET
    • Refactoring
      • Refactoring Concept (what/when/why)
      • Smells Catalog and possible re-factorings
      • Moving Features Between Objects (basic)
      • Organizing Data (basic)
      • Composing Methods (basic)
      • Simplifying Conditional Expressions (basic)
      • Making Method Calls Simpler
      • Dealing with Generalization
    • Product deploying, software installation
      • Create, configure, and publish a web package (.NET Web Profile)
      • Publishing Web Services
      • Manage packages by using NuGet, NPM and Bower
    • Networking
      • Understanding networks: layers and protocols
      • Basic understanding of TCP/IP model and protocols
      • Defining internet, intranet and VPN
      • Basics of Firewalls and DMZ
      • Application layer protocols basics (HTTP, FTP, Telnet)
      • Understanding HTTP and WWW
      • Basic troubleshooting tools (ICMP, ping, traceroute)
      • Client/Server model
      • Sockets, IP and port addressing
      • Using proxy server
      • File transfer services: FTP, TFTP
      • Name resolution services: DNS, whois
      • Remote access services: Telnet, SSH, rdesktop, VNC
      • The basic difference between HTTP and HTTPS protocols
  • Construction Web
    • Web server applications
      • ASP.NET Core
        • Application startup
        • Middleware
        • Working with Static Files
        • Routing
        • Error Handling
        • Globalization and localization
        • Configuration
        • Logging
        • File Providers
        • Dependency Injection
        • Working with Multiple Environments
        • Hosting
        • Managing Application State
        • Request Features
      • ASP.NET Core MVC
        • MVC basics (Model, View, Controller, DI)
        • Model binding and validation
        • View (Razor compilation, Layout, Tag Helpers, Partial Views, DI, View components)
        • Controllers (Route to actions, File uploads)
      • Security and Identity (concepts understanding)
        • Authentication
        • Using identity
        • Authorization with roles
      • Bundle and Minify assets
      • Develop ASP.NET Core MVC apps
      • Advanced topics for ASP.NET Core MVC
        • Application model
        • Filters
        • Areas
        • Application Parts
        • Custom Model Building
        • IActionConstraint
      • Host and deploy ASP.NET Core
      • Migrate from ASP.NET to ASP.NET Core
      • Troubleshoot ASP.NET Core projects
      • Open Web Interface for .NET (OWIN)
      • Web server implementations in ASP.NET Core
    • Web Services
      • REST
      • ASP.NET Web API
        • Routing
        • Configuration
        • Basic error handling
      • Web API-based services
      • Web API Security
      • Token based security
      • SingalR
      • Serialization Frameworks
      • Implement caching
      • gRPC on ASP.NET Core
      • API versioning
      • API documentation
    • Microservices and Cloud
      • Microservices architecture
      • Dockerize a .NET Core application
      • Development workflow for Docker apps
    • JavaScript, HTML, CSS
      • JavaScript: Variables
      • JavaScript: Data types and types conversion
      • JavaScript: Operators
      • JavaScript: Control and Loop constructions
      • JavaScript: Functions, Execution Context and Variables scopes
      • JavaScript: Arrays
      • JavaScript: JS in WebBrowser and basic DOM manipulations
      • HTML: Basic elements
      • CSS: Simple Style rules
      • CSS: selectors
      • Box model
      • HTML: Standards and Browser compatibility
      • HTML: Page Layouts with divs
      • HTML: Frames
      • CSS: Elements positioning and layering
      • CSS: Tables properties
      • CSS: Flexbox
      • Different storage
      • JavaScript: Event Understanding (propagation, capturing, attach/detach)
      • JavaScript: Closure
      • AJAX/JSON
      • Ecma script 6: OOP
      • Promise
      • Strict mode of javascript
    • JavaScript Frameworks
      • Selecting elements
      • Operating on collection
      • Manipulating with elements, working with properties, attributes and data
      • Events
      • animation and effects
      • utilities and Ajax
      • SPA (SINGLE PAGE APPLICATIONS)
      • EcmaScript 6
      • UI frameworks basics:
      • NPM basics:
      • React basics
  • Construction DB
    • SQL
      • Tables, relationships, keys, constraints understanding
      • DDL, DML, DCL understanding
      • SQL data types
      • SQL operators, functions
      • Data manipulation (insert, update, delete)
      • Retrieving data (simple select statement)
      • Joins understanding
      • Creating, modifying, removing database objects
      • Aggregations (ORDER BY, GROUP BY, HAVING, SUM, COUNT, AVG, etc)
      • Combining the results of multiple queries (UNION, EXCEPT, INTERSECT, MINUS, subqueries)
      • Sessions, transactions, locks
      • Isolation levels understanding
      • Implementing stored procedures, user-defined functions, triggers
      • Cursors
    • Data Access Layer
      • Manage connection strings and objects
      • Working with data providers
      • Connect to a data source by using a generic data access interface
      • Handle and diagnose database connection exceptions
      • Manage exceptions when selecting, modifying data
      • Build command objects and query data from data sources
      • Retrieve data source by using the DataReader
      • Manage data by using the DataAdapter and TableAdapter
      • Updating data
      • Entity Framework
        • Query data sources by using EF
        • Code First to existing DB
        • Entity Data Modeling Fundamentals
        • Querying Data
        • Data modification
  • Verification
    • Code Quality
      • MSDN: Guidelines for Names
      • SDO Best Practices Catalog - Coding Standards
      • SDO Best Practices Catalog - Code Review Process
      • SDO Best Practices Catalog - Automatic Code Inspection
      • Automated coding standards enforcement (StyleCop, Resharper)
      • Code Reviews and Toolset
      • Use Work Items (TODO, BUG etc.)
      • Preemptive Error Detection
      • Desirable characteristics of a design (minimal complexity, ease of maintenance, minimal connectednes
      • Creating high quality classes
      • Creating high quality methods
      • Guidelines for initializing variables
      • Exceptions and error handling techniques
      • Best practices of working with data types
      • Code commenting practices
    • Automated Testing (principles, patterns, and practices)
      • Software testing basic concepts
      • Software testing concept
      • Test Case
      • Test Suite
      • Test Plan
      • Testing Levels
      • Naming standards for unit tests
      • Types of test doubles (Stub, Mock, Spy, Fake, Dummy)
      • Basic coverage criteria
      • Testing concepts (Unit vs Functional vs Integration)
      • Goals of Unit Testing, What Makes a Test Valuable?
      • Styles of Unit Testing (Output / State / Collaboration)
      • Good unit test properties
      • F.I.R.S.T Principles of unit testing
      • Test Pyramid concept
      • Testing Pyramid, Agile Testing Pyramid, Diamond
      • Breaking the dependency, Interaction testing
      • Strategies for isolating the database in tests
      • Test smells and how to avoid
      • Test Organization patterns
      • Fixture setup patterns
      • Test double patterns
      • Feature-driven development (FDD)
      • Behavior-driven development (BDD)
      • Test-driven development (TDD)
      • Acceptance testing, Acceptance Test Driven Development (ATDD)
      • Continuous testing
    • Automated Testing (Frameworks, Tools, Libraries)
      • .NET unit test frameworks overview
      • .NET Mocking Frameworks, a comparison
      • xUnit
        • Primary test framework attributes
        • Asserts
        • Exception Handling in Unit Tests
        • Skipping Tests
        • Initialization and Cleanup (Assembly, Class, Test)
        • Data-driven Tests
      • NSubstitute
        • Mocking Method Calls (Using Mock Object, Return Values, Argument Matching)
        • Behavior Verification (Method Was/Not Called, a Specific Number of Times, Getter/Setter Was Called)
        • Throwing exceptions
        • Raising Events from Mock Objects
        • Returning Different Results for Sequential Calls
      • AutoFixture
      • EF Core InMemory test
      • Integration tests in ASP.NET Core
      • Isolating database data in integration tests
      • Test ASP.NET Core MVC apps
  • Configuration Management
    • Product builds and Continuous Integration
      • Automated build concept
      • Dotnet cli
      • CI/CD Basic concepts
    • Managing Versions
      • Fundamental concepts: revisions, working copy, repository, branch, baseline, trunk
      • Versioning Models
      • Distributed Version Control basics
      • Distributed systems advantages and weak sides
      • VCS Management life-cycle on (one of) major tools (clone, commit, update, revert, merge, resolve, et
      • Branching/Merging strategies
      • Blaming (annotate)
      • Revision graph/log actions (Git)
      • Integrating with Issue Tracking Systems
      • Source control Best Practices
Powered by GitBook
On this page
  • String Functions
  • Math/Numeric Functions
  • Date Functions
  • Advanced Functions
  1. Construction DB
  2. SQL

SQL operators, functions

String Functions

Function

Description

Returns the ASCII value for the specific character

Returns the character based on the ASCII code

Returns the position of a substring in a string

Adds two or more strings together

Adds two or more strings together

Adds two or more strings together with a separator

Returns the number of bytes used to represent an expression

Compares two SOUNDEX values, and returns an integer value

Formats a value with the specified format

Extracts a number of characters from a string (starting from left)

Returns the length of a string

Converts a string to lower-case

Removes leading spaces from a string

Returns the Unicode character based on the number code

Returns the position of a pattern in a string

Returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier

Replaces all occurrences of a substring within a string, with a new substring

Repeats a string a specified number of times

Reverses a string and returns the result

Extracts a number of characters from a string (starting from right)

Removes trailing spaces from a string

Returns a four-character code to evaluate the similarity of two strings

Returns a string of the specified number of space characters

Returns a number as string

Deletes a part of a string and then inserts another part into the string, starting at a specified position

Extracts some characters from a string

Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified in the third argument.

Removes leading and trailing spaces (or other specified characters) from a string

Returns the Unicode value for the first character of the input expression

Converts a string to upper-case

Math/Numeric Functions

Function

Description

Returns the absolute value of a number

Returns the arc cosine of a number

Returns the arc sine of a number

Returns the arc tangent of a number

Returns the arc tangent of two numbers

Returns the average value of an expression

Returns the smallest integer value that is >= a number

Returns the number of records returned by a select query

Returns the cosine of a number

Returns the cotangent of a number

Converts a value in radians to degrees

Returns e raised to the power of a specified number

Returns the largest integer value that is <= to a number

Returns the natural logarithm of a number, or the logarithm of a number to a specified base

Returns the natural logarithm of a number to base 10

Returns the maximum value in a set of values

Returns the minimum value in a set of values

Returns the value of PI

Returns the value of a number raised to the power of another number

Converts a degree value into radians

Returns a random number

Rounds a number to a specified number of decimal places

Returns the sign of a number

Returns the sine of a number

Returns the square root of a number

Returns the square of a number

Calculates the sum of a set of values

Returns the tangent of a number

Date Functions

Function

Description

Returns the current date and time

Adds a time/date interval to a date and then returns the date

Returns the difference between two dates

Returns a date from the specified parts (year, month, and day values)

Returns a specified part of a date (as string)

Returns a specified part of a date (as integer)

Returns the day of the month for a specified date

Returns the current database system date and time

Returns the current database system UTC date and time

Checks an expression and returns 1 if it is a valid date, otherwise 0

Returns the month part for a specified date (a number from 1 to 12)

Returns the date and time of the SQL Server

Returns the year part for a specified date

Advanced Functions

Function

Description

Converts a value (of any type) into a specified datatype

Returns the first non-null value in a list

Converts a value (of any type) into a specified datatype

Returns the name of the current user in the SQL Server database

Returns a value if a condition is TRUE, or another value if a condition is FALSE

Return a specified value if the expression is NULL, otherwise return the expression

Tests whether an expression is numeric

Returns NULL if two expressions are equal

Returns the name of the current user in the SQL Server database

Returns the session settings for a specified option

Returns the login name for the current user

Returns the database user name based on the specified id

PreviousSQL data typesNextData manipulation (insert, update, delete)

Last updated 5 years ago

ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME