SQL stands for Structured Query Language and is pronounced either ess-que-el orsequel. It is the language used by relational database management systems (RDBMS) to access and manipulate data and to create, structure and destroy databases and database objects.

Brief History of SQL

IBM invented a computer language back in the 1970s designed specifically for database queries called SEQUEL, which stood for Structured English Query Language. IBM released SEQUEL into the public domain, where it became known as SQL.

Because of this heritage you can pronounce it as “sequel” or spell it out as “S-Q-L” when talking about it.

Various versions of SQL are used in today’s database engines. Microsoft SQL Server uses a version called Transact-SQL.

History in Year

1. 1987 – Sybase releases SQL Server for UNIX.
2. 1988 – Microsoft, Sybase, and Aston-Tate port SQL Server to OS/2.
3. 1989 – Microsoft, Sybase, and Aston-Tate release SQL Server 1.0 for OS/2.
4. 1990 – SQL Server 1.1 is released with support for Windows 3.0 clients.
5. Aston – Tate drops out of SQL Server development.
6. 2000 – Microsoft releases SQL Server 2000.
7. 2001 – Microsoft releases XML for SQL Server Web Release 1 (download).
8. 2002 – Microsoft releases SQLXML 2.0 (renamed from XML for SQL Server).
9. 2002 – Microsoft releases SQLXML 3.0.
10. 2005 – Microsoft releases SQL Server 2005 on November 7th, 2005.

What Is a Database?

SQL Server uses a type of database called a relational database. A relational database at its simplest is a set of tables used for storing data. Each table has a unique name and may relate to one or more other tables in the database through common values.

Relational Database Objects:

1. Tables — these are the objects that contain the data types and actual raw data.
2. Columns — these are the parts of the table holding the data. Columns must be assigned a data type and unique name.
3. Data types — there are various data types to choose from, such as character, numeric, or date. A single data type is assigned to a column within a table.
4. Stored procedures — these are like macros in that Transact-SQL code can be written and stored under a name. By executing the stored procedure, you actually run the Transact-SQL code within the procedure.
5. Triggers — Triggers are stored procedures that activate when data is added, modified, or deleted from the database. They are used to ensure that business rules or other data integrity rules are enforced in the database. For example, a trigger can ensure that every book in a bookstore has a valid publisher assigned to it.
6. Rules — Rules are assigned to columns so that data being entered must conform to standards you set. For example, rules can be used to make sure that a person’s phone number contains only numbers.
7. Constraints — Constraints are server-based, system-implemented data-integrity enforcement mechanisms.
8. Defaults — Defaults can be set on fields so that if no data is entered during an INSERT operation, default values will be used.
9. Views — Views are basically queries stored in the database that can reference one or many tables. You can create and save them so you can use them easily in the future.
10. Indexes — Indexes can help organize data so that queries run faster.

 

SQL Statements

Database Manipulation Language (DML)

DML statements are used to work with data in an existing database. The most common DML statements are:

1. SELECT
2. INSERT
3. UPDATE
4. DELETE

Database Definition Language (DDL)

DDL statements are used to structure objects in a database. The most common DDL statements are:

1. CREATE
2. ALTER
3. DROP

Database Control Language (DCL)

DCL statements are used for database administration. The most common DCL statements are:

1. GRANT
2. DENY (SQL Server Only)
3. REVOKE

 

Designing Relational Databases:

Interview with the Customer:

A good database design starts with a thorough understanding of the situation and desired outcome of the customer. That’s why the people who design new systems are called analysts—they analyze the problem in detail and try to think of ways to solve the problem.

Sometimes an old-fashioned interview is the best way to find out exactly what the customer wants, especially if you don’t fully understand what the current situation is and what the goal is.

Use questions like these to probe for your customer’s needs:

• What is working for you now?
• What parts of the current system would you most like to replace?
• Are there additional reports you would like to be able to generate?
• What items would you most like to keep track of?
• Is the data private or public?
• Who needs access to the data and what kind of access should each user or group have?
• Would you like the data posted on the Internet?
• Would you like the public to be able to look up things via the Internet?
• Do you have sufficient hardware in place to run both the database server and client software?
• If money and technology were no object, what would you like to incorporate into the new system?

By asking these kinds of questions you can quickly build a sense of why a database is needed. Although you might not be able to provide everything (given the limitations of the assigned budget, time frame, and hardware allowances), you will have the start of a long-term plan for growth and expansion of the database.

Organizing the Objects:

After the interview (you did take good notes didn’t you?) it is best to brainstorm about possible objects, including their names, types, and lengths. After the objects have been decided on, you can group them into related tables.

SQL Server supports several different data types, including those for characters, numbers, dates, and money. More detail on data types is provided on Day 9, “Creating Tables.” After you have decided on your tables, specify the properties (columns) within these tables. Keep column names simple yet descriptive.

Column lengths should satisfy all but the most extreme cases. When dealing with names, your limitation might be how many characters can fit onto a mailing label—not how many to store.

 

Normalizing the Data:

Now that you have decided on the columns, you must organize the data into related tables, which is referred to as normalizing the data.

Normalization is the process of organizing data into related tables. By normalizing the data you are attempting to eliminate redundant data.

The First Three Normalization Forms

There are rules established for the normalization of data. These rules are known as

• First Normal Form (FNF) — this rule states that a column cannot contain multiple values. For example, a person’s name must be broken down into last name, middle name, and first name to follow FNF.
• Second Normal Form (SNF) — this rule states that every nonkey column must depend on the entire key and not just a part of the primary key. For example, if you are using a customer ID and part number for a key, all the columns in that table must apply only to a particular customer and part number together. So, a part description would not belong in this table. A table must also comply with first normal form to be in second normal form.
• Third Normal Form (TNF) — this rule is much like the previous one and states that all nonkey columns must not depend on any other nonkey columns. For example, if you have a table that has addresses in it, the zip code must not depend on another nonkey field like state. It should depend on the entire primary key. Of course, the table must also comply with second normal form. The TNF is often violated for convenience sake.

 

Ref: http://www.informit.com