Pearson INF-201: Information Technology Specialist Databases

Install SQL Server 2022 Express back engine and SQL Server Management Studio front end, then connect, open a new query, and execute a select statement to explore databases.

Explore the select and from clauses by building a basic query and naming columns with as. Compare SQL Server and Oracle, and note differences like from dual and semicolon usage.

Explore the where clause with strings and dates, using single-quoted string literals, like with wildcards (% and _), and null checks (is null/not null) for create_date comparisons.

Explore how the group by clause creates totals with count, sum, min, max, and avg, handles nulls, uses distinct, and notes top and limit across SQL variants.

The having clause filters aggregated results after a group by, enabling conditions on counts and other aggregates; unlike where, it operates after grouping and cannot use select aliases.

Explore the order by clause and how to sort query results in ascending or descending order using aliases, multiple columns, and practical examples within a six-clause select statement.

Apply the having clause to filter grouped results by count, explain why where cannot precede group by, and use order by with aliases to sort by roles and object IDs.

Understand SQL server numeric data types, from tinyint to bigint, including bit booleans, storage implications, and integer division. Learn decimal/numeric with precision and scale, and money, float, and real types.

Explore date and time field types in SQL databases, including date, smalldatetime, datetime2, datetimeoffset, and time, their ranges, precision, and formatting.

Explain char, varchar, and their max variants, plus unicode options nchar and nvarchar, highlighting fixed vs variable length, storage limits, and performance trade-offs.

Round off the field types by exploring binary string, varbinary, varbinary(max), geography, geometry, cursor, uniqueidentifier (GUID), and XML, with practical memorization tips.

Create the transaction table with amount, date, and employee number using appropriate data types aligned with the employee table. Insert values, save the script, and run it.

Explore how relations are established between tables using primary and foreign keys, and distinguish one-to-many, many-to-many, and one-to-one relationships, with ad hoc and explicit linking.

Use a left join to identify missing data by comparing employees and transactions, filtering where the joined employee number is null to reveal employees without transactions.

Practice activity 4 demonstrates creating a database and using inner, left outer, and full outer joins to combine tblStampNames and tblStampPurchases, identify matches and nonmatches, with table aliasing.

Alter tables to change column definitions, add new columns, and drop or truncate tables, while managing not null constraints, primary keys, and backups.

Alter the table to add a rarity column as varchar, up to 20 characters, allowing nulls; then later change it to 30 characters and drop the column.

Learn how to create primary keys quickly with inline declarations, compare inline and out-of-line constraints, and note auto-generated names and limitations when altering existing columns.

Evaluate composite primary key options for the table transaction, considering employee number, date time, and amount, and implement an identity-based id transaction as the primary key.

Discover how to work with identity columns in sql, including dropping and re-adding to existing columns and using identity_insert to insert explicit values.

Alter tblStampPurchases to add an auto-generated int PurchaseID as the primary key, and compare adding a column then constraint with dropping and recreating constraints to achieve the same result.