CS 112D Lecture Notes - SQL Server Queries and Commands (Chapter 2)

Writing and Storing Queries for SQL Server

• We have two primary ways of using VS.NET to store and run queries for SQL Server: via Server Explorer, or in a Database Project
• Queries and Scripts stored in a Database Project are saved as text files in that project's folder, not inside the database.
• a View is essentially a query that is stored as part of a SQL Server database (similar to a Query in Microsoft Access).
• The Server Explorer can be used to generate and run views in SQL Server (or MSDE). Graphical query building tools are available.

SQL Review

• There are two categories of Structured Query Language (SQL) commands:
• Data Definition Language (DDL) - used to create and alter tables, fields, indexes, etc. (Metadata)
• Data Manipulation Language (DML) - used to view and modify records stored inside a database. (The data)

Select Queries (DML)

• basic form: "SELECT ... FROM ..." can be supplimented by any of the following:
• WHERE - limits which records retrieved. Can include:
  • BETWEEN - within a range of values
  • LIKE - matching a pattern. Wildcards are different from Access:
    • % (percent sign) matches any number of characters
    • _ (underscore) matches just one character
  • IN - contained in a list of values
• ORDER BY - sorts results
• TOP - displays only the first x records retrieved (x being a number you specify). Can be combined with PERCENT.
• Joins - selecting from 2 different tables, matching on primary and foreign key
• INNER JOIN - returns only records where data is found in both tables with matching primary and foreign key. For example:

    SELECT FirstName, LastName, OrderDate
    FROM tblOrder INNER JOIN tblCustomer
    ON tblOrder.CustomerID = tblCustomer.ID
    

• OUTER JOIN - returns all data from one table, and matching data (if available) from other table. Outer Joins are specified as left or right, indicating which table has all of its records included. For example:

    SELECT FirstName, LastName, OrderDate
    FROM tblCustomer LEFT OUTER JOIN tblOrder
    ON tblCustomer.ID = tblOrder.CustomerID
    

• Calculations in queries - an arithmetic expression can be used on a selected field
• Aliasing (using AS) - renames a selected or calculated result
• GROUP BY, HAVING - allows results to be grouped, HAVING replaces SELECT.
• SUM and other aggregates - can produce calculations by group (e.g. sum of quantities for each order)
• UNION - combines two queries into one recordset
• Subquery - a query whose result is used in another query.

Manipulating Data (DML)

• UPDATE - modifies existing record(s) in a table, using SET
• DELETE - deletes record(s) from a table.
• INSERT - inserts data into an existing table
• SELECT INTO - creates a new table (DDL) and fills it with selected data (DML). e.g. to create a new table called tblCustomerDuplicate containing the same data as tblCustomer:

    SELECT * INTO tblCustomerDuplicate
    FROM tblCustomer
    

  All of the usual clauses for SELECT work with SELECT INTO as well (e.g. WHERE, JOINs, etc.) Note that SELECT INTO doesn't generate any constraints.

Data Definition Language (DDL)

• CREATE - create a new table, index, etc.
• CONSTRAINT used with CREATE to set up a unique, primary, or foreign key
• DROP - removes a table or other database element.
• ALTER - modify a table, such as adding a field or constraint. e.g. to add a primary key to tblCustomerDuplicate:

    ALTER TABLE tblCustomerDuplicate ADD
      CONSTRAINT [PK_tblCustomerDuplicate] PRIMARY KEY  CLUSTERED
      (
        [ID]
      )  ON [PRIMARY]
  
    

Return to the main CS 112D page