SQL Queries 101

Learn to write single-table sql queries using management studio, switching databases with use, selecting all fields, filtering with where, and handling reserved keywords with square brackets.

Learn how to use criteria in SQL by building queries with where clauses, range operators (greater than, between), and field selection to retrieve employee and grant data.

Explore pattern matching in SQL Server queries using like and wildcards. Master percent, underscore, and bracket ranges to find names, emails, and other patterns in data.

Explore how to join tables in sql by linking employee and location data using inner joins, on clauses, and normalizing data to streamline queries.

Explore outer joins to show all records from a dominant table and matching or non-matching rows from related tables, using left, right, and full outer joins.

Learn how table aliases simplify joins and field qualification in SQL. Start with select star, then itemize fields, and use aliases like e and l to avoid ambiguity.

Explore joining employee and location tables with inner and left outer joins to reveal unmatched records, aliasing tables for clarity, including locations with no employees.

Learn how cross joins produce every combination of two tables, yielding a Cartesian product without an on clause, illustrated by pairing employees with required management classes.

Choose your starting point in the SQL lab by running reset scripts for a given lab, follow tutorial steps, and complete skill checks or refresh to the beginning.

Create and drop databases safely by switching to the master and using go. Check existence with if exists, verify via a catalog view, and refresh to see updates.

Create the TBO movie table in the DV movie database with fields _ID (integer, primary key), _title (varchar(30), not null), and _runtime (integer, not null).

Learn to insert data in SQL using single and multi-row insert statements, including row constructors, to populate the D-B Movie Database and validate with lab 6.1 self checker.

Learn to update data in sql using update statements with where clauses and joins, applying changes to movie titles and pay rates through hands-on labs.

Learn how to delete data in SQL by using delete statements with where clauses, removing long movies or specific records, and validating results with affected rows.

Execute and manage SQL server databases by saving, distributing, and running scripts that drop and create a DVD movie database, build tables, insert records, and run via command prompt.

Learn to alter tables by adding and dropping columns, setting default values, and renaming fields with sp_rename, while distinguishing delete from drop as DML and DDL operations.

Execute data import and export using bcp to manage a movie table, deleting test records and loading comma-delimited text files, with optional hash delimiters and trusted connections.

Create and execute stored procedures to generate frequent employee reports by joining employee and location data, using procedures like get Washington employees and get non Washington employees.

Learn how to declare and set variables in SQL, use var char and integer types, and adjust query criteria with variables to filter current products by price ranges.

Explore creating and calling parameterized stored procedures, pass city as a parameter to filter employees by city, use defaults, alter procedures, and test with sample calls.

Explain how explicit transactions use begin tran and commit tran to ensure atomicity of dependent dml statements, such as updating a location record and transferring funds.

Explore explicit transactions, commit and rollback, and how table hints such as read uncommitted, read committed, and no lock influence data visibility during updates in SQL Server.

Create and manage sql server logins, switch between windows and sql server authentication, and practice enabling, disabling, and viewing logins in management studio with Murray, Bernie, and Sarah.

Grant and test database permissions using DCL statements in SQL Server Management Studio. Learn to use alter any database and control server permissions to verify access across databases.

See how explicit grants and denies on a SQL server interact, as revoking alter any database and managing control server access reveals the resulting effective permissions.

Learn to use SQL code comments to describe queries, toggle code with -- and /* */, and work with inner joins between employee and location tables.

Learn automatic script generation to reproduce database actions, convert a drop table operation into a reusable create script, and duplicate a table's structure with an employee archive example.

Leverage the import export wizard to export the location table from the Jay pro-coal database to an Excel file, then review the results.