SQLtest: interactive SQL quiz

AdventureWorks Database (SQL Server)

The AdventureWorks Database is a sample database that demonstrates the capabilities of SQL Server. It includes data about fictional manufacturing company.

AdventureWorks DB ER diagram

The following is a list of these tables:

• Address - table of addresses.
• Customer - table of customers
• CustomerAddress - table of customer tot address relations.
• Product - table of products.
• ProductCategory - table of product categories.
• ProductDescription - table of product descriptions.
• ProductModel - table of product models.
• ProductModelProductDescription - table of product models descriptions.
• SalesOrderDetail - table of product sales orders.
• SalesOrderHeader - table of product sales orders details.

T-SQL Fundamentals by Itzik Ben-Gan

Master Transact-SQL's fundamentals, and write correct, robust code for querying and modifying data with modern Microsoft data technologies, including SQL Server 2022, Azure SQL Database, and Azure SQL Managed Instance.

Table Address

• AddressID - a unique identifier for each address.
• AddressLine1 - the first line of the address.
• AddressLine2 - the second line of the address.
• StateProvince - city.
• CountryRegion - country.
• PostalCode - postal code.
• rowguid - guid.
• ModifiedDate - timestamp of row creation or last update.

• PRIMARY KEY, btree (AddressID)

Table Customer

• CustomerID - a unique identifier for each customer.
• NameStyle - 0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order. Default: 0.
• Title - title.
• FirstName - name.
• MiddleName - middle name.
• LastName - last name.
• Suffix - suffix.
• CompanyName - company name.
• SalesPerson - SalesPerson.
• EmailAddress - E-mail.
• Phone - phone number.
• PasswordHash - password hash.
• PasswordSalt - salt.
• rowguid - rowguid.
• ModifiedDate - timestamp of row creation or last update.

• PRIMARY KEY, btree (CustomerID)

Table CustomerAddress

• CustomerID - unique identifier of client in the table Customer.
• AddressID - unique identifier of address in the table Address.
• AddressType - address type.
• rowguid - guid.
• ModifiedDate - timestamp of row creation or last update.

• PRIMARY KEY, btree (CustomerID, AddressID)

Table Product

• ProductID - a unique identifier for each product.
• Name - product name.
• ProductNumber - article number.
• Color - product color.
• StandardCost - product price.
• ListPrice - product price in the catalogue.
• Size - product size.
• Weight - product weight.
• ProductCategoryID - foreign key pointing to table ProductCategory - defines the product category.
• ProductModelID - foreign key pointing to table ProductModel - defines the product model.
• SellStartDate - timestamp of the sales start date.
• SellEndDate - timestamp of the sales end date.
• DiscontinuedDate - timestamp of the sales end date.
• ThumbNailPhoto - thumbnail photo of the product.
• ThumbnailPhotoFileName - name of the photo thumbnail file.
• rowguid - guid.
• ModifiedDate - timestamp of row creation or last update.

• PRIMARY KEY, btree (ProductID, ProductCategoryID, ProductModelID)

Table ProductCategory

• ProductCategoryID - a unique identifier for each product category.
• ParentProductCategoryID - identifier of the parent product category.
• Name - name of the product category.
• rowguid - guid.
• ModifiedDate - timestamp of row creation or last update.

• PRIMARY KEY, btree (ProductCategoryID)

Table ProductDescription

• ProductDescriptionID - a unique identifier for each product description.
• Description - product description.
• rowguid - guid.
• ModifiedDate - timestamp of row creation or last update.

• PRIMARY KEY, btree (ProductDescriptionID)

Table ProductModel

• ProductModelID - a unique identifier for each product model.
• Name - name of the product model.
• CatalogDescription - description in XML format.
• rowguid - guid.
• ModifiedDate - timestamp of row creation or last update.

• PRIMARY KEY, btree (ProductModelID)

Table ProductModelProductDescription

• ProductModelID - unique identifier of client in the table ProductModel.
• ProductDescriptionID - unique identifier of address in the table ProductDescription.
• Culture - language code in ISO format.
• rowguid - guid.
• ModifiedDate - timestamp of row creation or last update.

• PRIMARY KEY, btree (ProductModelID, ProductDescriptionID)

Table SalesOrderDetail

• SalesOrderID - foreign key referencing table SalesOrderHeader.
• SalesOrderDetailID - a unique identifier of record in the table.
• OrderQty - quantity.
• ProductID - a foreign key referencing the table Product.
• UnitPrice - price per unit of goods.
• UnitPriceDiscount - price per unit of product with a discount.
• LineTotal - Total.
• rowguid - guid.
• ModifiedDate - timestamp of row creation or last update.

• PRIMARY KEY, btree (SalesOrderID, SalesOrderDetailID, ProductID)

Table SalesOrderHeader

• SalesOrderID - a unique identifier of record in the table.
• RevisionNumber - revision number.
• OrderDate - timestamp for creating the order date.
• DueDate - timestamp of the order payment date.
• ShipDate - timestamp of the date the order was shipped.
• Status - order status.
• OnlineOrderFlag - online order (yes/no).
• SalesOrderNumber - order number.
• PurchaseOrderNumber - purchase number.
• AccountNumber - account number.
• CustomerID - foreign key referencing the table Customer - defines the client.
• ShipToAddressID - foreign key referencing table Address - defines the delivery address.
• BillToAddressID - foreign key referencing table Address - defines the account address.
• ShipMethod - delivery method.
• CreditCardApprovalCode - credit card confirmation code.
• SubTotal - subtotal.
• TaxAmt - taxes.
• Freight - delivery cost.
• TotalDue - total.
• Comment - comment.
• rowguid - guid.
• ModifiedDate - timestamp of row creation or last update.

• PRIMARY KEY, btree (SalesOrderID, CustomerID, ShipToAddressID, BillToAddressID)