SQL Chapter 1-3

How to View and Modify data of a table

However, for smaller tables such as Term table, the Management Studio provides an easy way to modify the data for the table.

Money

Monetary values, accurate to 4 decimals places.

SQL is

NOT CASE SENSITIVE.

A join

Lets you combine from two or more tables into a single result set.

The arithmetic operators in order of precedence

() Parentheses * Multiplication / Division % Modulo (Remainder) + Addition - Subtraction Whenever necessary, you can use parentheses to clarify or override the sequence of operations. Then, the operations in the innermost sets of parentheses are done first, followed by the operations in the next set and so on.

SQL coding recommendations

- Start each new clause on a new line. - Break long clauses into multiple lines and ident continued lines. - Capitalize the first letter of each keyword and each word in column and table names. - End each statement with a semicolon(;). - Use comments only for portions of code that are difficult to understand.

The three hardware components of a client/server system

-Clients -Server -Network

Common causes of errors

-Forgetting to select the correct database from the Available Databases combo box -Misspelling the name of a table or column -Misspelling a keyword -Omitting the closing quotation mark for a character string.

You can use the TOP clause with a SELECT clause to limit the number of rows included in the result set.

..

To store and manage the databases of the client/ server system each server requires

A Database management system (DBMS) like Microsoft SQL server.

Nchar, NVarchar

A string of letter, symbols, numbers, in UNICODE character set.

Char, Varchar

A string of letters, symbols and numbers in ASCII character set.

INSERT

Adds one or more new rows to a table.

You can tell what type of relationship exists between two tables

By looking at the end point of the link. The "One" side is represented by a key and the "Many" side is represented by an Infiniti symbol.

A statement that creates a new database

CREATE DATABASE AP;

UPDATE

Changes one or more existing rows in a table.

Datetime

Dates and Times.

Decimal

Decimal values, accurate to the least significant digit.

DELETE

Deletes one or more existing rows in a table.

=

Equal

Float

Floating point values, contain approximation of a decimal value.

How to View and Modify data of a table

For tables that have more than 200 rows, you will need to use SQL statements to modify the data for the table

>

Greater than

Int

Integer values of various sizes.

A relationship between tables that has a key symbol on one side and infinity symbol on the other

Is a One-to-Many relationship

SQL

Is a freeform language, meaning that you can include line breaks, space and indentation without affecting the way the database interprets the code.

A SELECT statement that joins data from the Vendors and Invoice tables

Is called a join because the data from the two tables is joined together in a single result set

SCHEMA

Is container that holds objects.

A inner join

Is the most common type of join. When you use an inner join, rows from the two table in the join are included in the result table only if their related columns match.

The easiest way to view the relationships between the tables in a database

Is to display a database diagram for the database.

The primary key for a table appears as a

Key icon

<

Less than

<>

Not Equal

In practice, the rows and columns of a relational database are

Often referred to by more traditional terms, RECORDS and FIELD.

SELECT

Retrieve data from one or more tables.

An Inner Join

Returns rows from both tables only if their related columns match.

Using the AS keyword (the preferred technique)

SELECT InvoiceNumber AS [Invoice Number], InvoiceDate AS [Date], InvoiceTotal AS Total FROM Invoices;

A SELECT statement that retrieves and sorts selected columns and rows

SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, FROM Invoices WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0 ORDER BY InvoiceDate;

The columns to be retrieved are listed the

SELECT clause. This statement does not include a WHERE clause, so all the rows are retrieved. Then, the order by clause causes the rows to be sorted by the InvoiceTotal column in ascending order.

The four clauses of the SELECT STATEMENT

SELECT describes the columns that will be included in the result set FROM names the table from which the query will retrieve the data WHERE specifies conditions that must be met for a row to be included in the result set. Optional ORDER BY specifies how the rows in the result set will be sorted. Optional

In some cases a result set will contain duplicate rows, or rows whose columns values are identical. If thats not what you want, you can include

The DISTINCT keyword in the SELECT clause to eliminate the duplicate rows and sort by its first column.

The management Studio and the Object Explorer

The qualifier indicates the SCHEMA that the tables belong to. IN SQL server, a SCHEMA is container that holds objects. If you do not specify a schema when you create an object, it is stored in the default schema.dbo.

The model for a relational database

States that the data is stored in one or more TABLES.

The SQL Server configuration Manager (Services)

The best way to display the Configuration Manager to Start & Stop SQL services Select Start->AllPrograms->Microsoft SQL server 2012->Configuration Tools->SQL Server Configuration Manager.

How to View and Modify data of a table

To do that, you can right-click on the table and select the Edit Top 200 Rows command. This displays the top 200 rows of the table in an editable grid

Differences between Query Editor and Query Designer

To enter and edit queries, the Management Studio provides a Query Editor. Keep in mind, that the best way you learn SQL is to code it yourself. The is why is emphasized to use the Query Editor. It can be difficult and sometimes impossible to create certain types of complex queries using the Query Designer

How to enter and execute a query

To enter and edit queries, the Management Studio provides a Query Editor. The Query editor is specifically designed for writing Tansact-SQL statements, but it works like most text editors.

How to enter and execute a query

To execute a query, you can press F5 or click the Execute button in the toolbar.

How to connect using SQL Server authentication

Use SQL authentication to let the SQL database server supply the appropriate username and password for access

How to connect using Windows authentication

Use Windows authentication to let the Operation System supply the appropriate username and password to access the SQL database server.

The management Studio with an error message

When you are entering a SQL statement, the Intenllis|Sense feature will display a way the red underlining beat any parts of the SQL statement that contain errors.

If a table contains one or more columns that uniquely identify each row the table,

You can define these columns as the PRIMARY KEY of the table.

When coding the SELECT clause,

You should include the columns needed. You shouldn't code an asterisk (*) to retrieve all the columns unless you need them all because of the amount of data retrieved can affect performance.