SQL SELECTBasic query concepts and more advanced techniques were covered in Chapters 4 and 5, respectively. Using the T-SQL SELECT statement is the most common way to access data. The majority of all data retrieval statements begin with these four main fundamental parts of a SELECT operation:
SELECT options must always be used in the correct sequence, which can be summarized by using a phrase to match the first letter of each clause in the SELECT . S F W G H O C (Select From Where Group Having Order Compute) is a mechanism used by this author for years , which goes somewhat like this:
DATEADD, DATEDIFF, DATENAME, DATEPARTThe date functions use part of the date you specify, such as month or day.
Table 3. Possible Values for date_part .
Inserting DataDespite the many ways to insert data into an existing table, such as using the Enterprise Manager, the exam deals with the primary coding method using the INSERT statement. We can summarize the syntax of the INSERT INTO statement as follows : INSERT [INTO] table_or_view [(column_list)] VALUES data_values Inserting Data Using SELECTThe SELECT INTO statement can perform a data insertion and create the table for the data in a single operation. The new table is populated from the data provided by a FROM clause. A simple example of its use follows: SELECT * INTO ObsoleteProducts FROM Products Where Discontinued = 1 A SELECT statement can be used within the INSERT statement to add values into a table from one or more other tables or views. INSERT INTO Northwind.dbo.Customers SELECT EmployeeID, 'Northwind', FirstName + ' ' + LastName, 'Employee', Address, City, Region, PostalCode, Country, HomePhone, NULL FROM Northwind.dbo.Employees Deleting DataData that is not needed can be deleted using the DELETE statement. The DELETE statement removes one or more records from a table based on a condition in the WHERE clause. A simplified version of the DELETE statement is DELETE table or view FROM table_sources WHERE search_condition Updating DataData that already exists might need to be modified with newer values as time passes ; this type of data modification is known as updating. Data can be updated similar to how data is deleted and inserted. Data can be updated using the UPDATE statement. An UPDATE execution is actually an INSERT and DELETE operation. The DELETE operation occurs when the old value is removed and the INSERT occurs when the new value is added, thus creating an UPDATE effect. Sometimes an update is performed as an in-place update. |