We may not always want all records we can select, e.g., LastName = ‘Brown’. We may not always want every column let’s say we just want and. It will retrieve every column (*) and every row (19,972 rows) from table. This is probably the simplest form of SQL statement. Once we have installed SQL Server 2012 Express and attached AdventureWorks Sample database, we can launch Microsoft SQL Server Management Studio, where we can start using SQL statements. This article will focus on how to use SQL statements to retrieve data, and will not cover database architecture or design. They can be downloaded (FREE) from the following sites: Microsoft® SQL Server® 2012 Express AdventureWorks sample database We are going to use Microsoft® SQL Server® 2012 Express and a sample database, AdventureWorks. We are going to learn what SQL is and how we can use SQL to manage data. Most enterprise data is stored in databases, and IT uses structured query language (SQL) to process data held in a relational database management system (RDBMS). So how does IT retrieve data from the systems? It would be great if we could retrieve data ourselves. It can take days or even weeks before they can obtain the additional data. ![]() When they need more data, they have to go back to their IT department. Most analysts receive the data from their IT department. Production.Product Columns - ProductID (PK, int, not null) Name (Name(nvarchar(50)), not null) Product Number (nvarchar(25), not null) MakeFlag (Flag(bit), not null) Finished GoodsFlag (Flag(bit), not null) Color (nvarchar(15), null) SafetyStockLevel (smallint, not null) ReorderPoint (smallint, not null) StandardCost (money, not null) ListPrice (money, not null) Size (nvarchar(5), null) - SizeUnitMeasureCode (FK, nchar(3), null) - WeightUnitMeasureCode (FK, nchar(3), null) Weight (decimal(8,2), null) DaysToManufacture (int, not null) ProductLine (nchar(2), null) Class (nchar(2), null) Style (nchar(2), null) w ProductSubcategoryID (FK, int, null) - ProductModellD (FK, int, null) SellStartDate (datetime, not null) SellEndDate (datetime, null) DiscontinuedDate (datetime, null) rowguid (uniqueidentifier, not null) ModifiedDate (datetime, not null)Ġ Production.Excel is a global standard analytical tool that is used in every industry. Credit Card Columns To Credit CardID (PK, int, not null) CardType (nvarchar(50), not null) CardNumber (nvarchar(25), not null) ExpMonth (tinyint, not null) ExpYear (smallint, not null) ModifiedDate (datetime, not null) Keys Constraints Triggers Indexes Statistics Sales.Currency Sales.Currency Rate Sales.Customer Sales.PersonCredit Card Sales SalesOrderDetail Sales Sales OrderHeader Columns SalesOrderID (PK, int, not null) Revision Number (tinyint, not null) OrderDate (datetime, not null) DueDate (datetime, not null) ShipDate (datetime, null) Status (tinyint, not null) OnlineOrderFlag (Flag(bit), not null) 3 Sales OrderNumber (Computed, nvarchar(25), Purchase OrderNumber (OrderNumber(nvarch Account Number (Account Number(nvarchar( - CustomerID (FK, int, not null) Sales PersonlD (FK, int, null) - TerritoryID (FK, int, null) - BillToAddressID (FK, int, not null) - ShipToAddressID (FK, int, not null) - ShipMethodID (FK, int, not null) O Credit CardID (FK, int, null) CreditCard Approval Code (varchar(15), null) CurrencyRatelD (FK, int, null) Sub Total (money, not null) TaxAmt (money, not null) Freight (money, not null) TotalDue (Computed, money, not null) Comment (nvarchar(128), null) rowguid (uniqueidentifier, not null) ModifiedDate (datetime, not null) Production.Product and the Production.Workorder tables. ![]() Sort by Scrapped Quantity, and then by Product ID. Order ID, Order Quantity, Scrapped Quantity, and Scrap Reason ID. List the Product ID, Product Name, Product Number, Work Scrapped quantity greater than 20 and an order quantity greater Write a query to find out how many products had a Number, Expiration Month, Expiration Year, Sales Order ID, andĬustomer ID. ![]() Sort by expiration year, and thenĮxpiration month. ![]() List the CreditĬard ID, Card Type, Card Number, Expiration Month, Expiration Year, Write a query to find out how many Vista credit cardsĮxpire in 2008 or more recent (2009, 2010, etc.). Require the access of at least 2 tables, maybe more – use the JOINĬommand to connect the tables). Using the Adventureworks 2012 database (downloaded from
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |