Untitled Quiz

SQL (Structured Query Language)

• SQL is a data manipulation language (DML)
• Includes commands for inserting, updating, deleting, and retrieving data within database tables.
• Also a data definition language (DDL) that creates database objects like tables, indexes, and views.
• Commands define access rights to database objects

SQL Data Types

• Define characteristics of data stored in database columns.
• Define possible values accepted.
• INT (Integer): Accepts whole numbers only.
• VARCHAR/NVARCHAR: Variable-length character strings.
• DATETIME: Stores dates and times.
• DECIMAL: Precise decimal values (numeric values with decimal point).
• FLOAT: Long numeric values that are approximations.
• BIT: Boolean values (Yes/No, True/False).

Reasons to use SQL Server Data Types

• Stores data consistently and in a known format.
• Data type allows for specific calculations and formulations.
• Data Types affect storage size (some values take up more space than others.)
• Affects query performance.

Commonly used SQL Server Data Types

• INT: Stores whole numbers (-2,147,483,648 to 2,147,483,647)
• VARCHAR/NVARCHAR: Stores variable length text (with maximum capacity of 8,000 characters for VARCHAR, 4,000 characters for NVARCHAR).
• DATETIME: Stores dates and times from January 1, 1753 to December 31, 9999.
• DECIMAL: Precise decimal values (precision and scale define number of total digits and digits after decimal point).
• FLOAT: Stores approximate numerical values (huge range of numerical values.).
• BIT: Stores boolean (True/False or 1/0) values.

Data Manipulation Commands

• INSERT: Used for inserting new rows into a table. Includes values for each column.
• UPDATE: Modifies data in a table, specifying the column and new values. Uses the WHERE clause to target specific rows, optionally using AND or OR to filter based on multiple conditions, in case more rows need updating.
• DELETE: Removes rows from a table. Uses the WHERE clause to target specific rows for deletion, optionally using AND or OR to filter based on multiple conditions.
• SELECT: Retrieves data from one or more tables.
• COMMIT: Permanently saves data changes.
• ROLLBACK: Restores data to their original values before the last COMMIT.

Special Operators

• BETWEEN: Checks if a value falls within a specified range of values.
• IS NULL: Checks if a value is null.
• LIKE: Checks if a value matches a given pattern (using wildcards such as % for any sequence of characters, and _ for a single character).
• IN: Checks if a value is present in a given list of values.
• EXISTS: Checks if a subquery returns any rows.

SQL Operators (AND, OR)

• AND: Returns TRUE if both conditions are TRUE.
• OR: Returns TRUE if either condition is TRUE (or both).

Data Types (considerations)

• CHAR, VARCHAR and VARBINARY data types that are padded with spaces in the SET ANSI_PADDING setting.
• TRUNCATE TABLE deletes rows from a table.
• DELETE statement deletes rows from a table based on a condition.
• DELETE without a WHERE clause deletes all rows from the table.

Additional Considerations

• Data Type Conversion: Database errors occur if the values have the wrong data type.
• Error Handling: TRY...CATCH statement can handle errors during INSERT, UPDATE, and DELETE operations.
• UNIQUE: constraint ensures unique values in each row.
• PRIMARY KEY: Marks a specific field.
• DEFAULT values, CHECK constraint and NOT NULL constraint.
• HAVING clause: Limits or sets conditions for the GROUP BY clause.

Useful Functions

• COUNT: Calculates the number of rows.
• SUM: Calculates the sum of values within a column.
• AVG: Calculates the average of values in a column.