Prev Next

SQL Syntax

Understanding SQL syntax is fundamental for writing effective queries and commands in MSSQL Server. SQL statements are composed of keywords, identifiers, variables, constants, and operators.

Key Topics

1. SQL Statements

SQL statements are the instructions used to communicate with the database to perform tasks, queries, and retrieve data.

Example: Basic SQL Statement Structure

SELECT column1, column2 FROM table_name WHERE condition;

Code Explanation: This statement selects column1 and column2 from table_name where a specific condition is met.

2. Basic Syntax Rules

  • SQL statements end with a semicolon (;).
  • SQL keywords are not case-sensitive, but it's a good practice to write them in uppercase.
  • Identifiers like table and column names are case-sensitive depending on the database settings.

3. SQL Keywords

SQL uses various keywords to perform different operations. Some common keywords include:

Keyword Description
SELECTRetrieves data from the database.
FROMSpecifies the table to retrieve data from.
WHEREFilters records based on a condition.
INSERTInserts new data into a table.
UPDATEModifies existing data in a table.
DELETERemoves data from a table.
JOINCombines rows from two or more tables.
INNER JOINReturns records that have matching values in both tables.
LEFT JOINReturns all records from the left table and matched records from the right table.
RIGHT JOINReturns all records from the right table and matched records from the left table.
FULL JOINReturns all records when there is a match in either left or right table.
GROUP BYGroups rows that have the same values into summary rows.
ORDER BYSorts the result-set in ascending or descending order.
HAVINGFilters records that work on summarized GROUP BY results.
CREATECreates a new table, database, index, or view.
ALTERModifies an existing database object.
DROPDeletes an entire table, a view of a table or other object in the database.
TRUNCATERemoves all records from a table, including all spaces allocated for the records.
UNIONCombines the result of two or more SELECT statements.
EXISTSChecks for the existence of any record in a subquery.
BETWEENFilters the result set within a certain range.
LIKESearches for a specified pattern in a column.
INSpecifies multiple possible values for a column.
ASAssigns a temporary name to a table or a column for the duration of a query.
NULLRepresents a missing or undefined value.
NOTNegates a condition.
ANDCombines multiple conditions; all must be true.
ORCombines multiple conditions; at least one must be true.
CASECreates conditional logic in queries.

4. Writing Queries

When writing SQL queries, clarity and readability are important. Proper indentation and formatting help in understanding and maintaining the code.

Example: Well-formatted SQL Query

SELECT 
    FirstName, 
    LastName, 
    BirthDate 
FROM 
    Employees 
WHERE 
    Salary > 50000;

Code Explanation: Selects the first name, last name, and birth date of employees whose salary is greater than 50,000.

Best Practices

  • Use uppercase for SQL keywords for better readability.
  • Properly format and indent your SQL code.
  • Comment your code to explain complex queries.

Key Takeaways

  • Understanding SQL syntax is crucial for database operations.
  • Proper formatting improves code readability and maintenance.
  • Familiarity with SQL keywords is essential for writing queries.
Prev Next