Structured Query Language - a query language that allows users to specify conditions to retrieve data or information from a database
SQL
Relatively easy to learn
Basic command set has vocabulary of less than 100 words
Nonprocedural language
ANSI prescribes a standard SQL
ANSI
American National Standards Institute - an organization that oversees the creation,promulgation and use of thousands of norms and guidelines that directly impact businesses
Query
A user request to retrieve data or information from a database with certain conditions
SQL
Allows users to specifyconditions to retrieve data or information from a database
Results of the query arestored in theform of a table
Provides statistical information of the data
Goes through all records in the database and selects those that satisfy the condition
SQL functions fit into two broad categories: data definition language and data manipulation language
Data definition language
SQL commands to create database objects, such as tables, indexes, and views, and define access rights to those objects
Data manipulation language
SQL commands to insert, update, delete, and retrieve data within database tables
Data definition vs data manipulation
CREATE TABLE VENDOR (V_CODE INTEGER NOT NULL UNIQUE, PRIMARY KEY (V_CODE)) - data definition
UPDATE PRODUCT SET P_INDATE = 18-JAN-2012 WHERE P_CODE = '13564' - data manipulation
SQL Server Management Studio is a tool used to administer and develop SQL Server database objects
SQL Server Management Studio
Has 13 tools available from the View menu, standard toolbar, and associated hotkeys
Some useful tools include Object Explorer, Template Explorer, and Solution Explorer
Creating a new database in SQL Server Management Studio
1. Right click on Databases in Object Explorer
2. Select New Database
3. Name the database
To do queries in SQL Server Management Studio, you need to connect to the database engine and set the Available Databases to the database you want to use
The basic SQL query syntax is: SELECT all(*) / select(name of columns) FROM tablename WHERE condition
Authentication
The process through which the DBMS verifies that only registered users are able to access the database
Schema
A group of database objects, such as tables and indexes, that are related to each other
Numeric data types in SQL
Int/Integer
Smallint
Tinyint
Decimal
Number
Date data types in SQL
Datetime
Smalldatetime
Date
Character string data types in SQL
Char
Varchar
Example data types for a product database
Product_Price - Number
Vendor_code - Integer
Product_quantity on hand - Smallint
Product_minimum units - Smallint
Product_discount - Number
The data is from January 1, 1900, through June 6, 2079, with an accuracy of one minute
Char (L)
Fixed-length character data with a maximum length of 8,000 characters. Remaining spaces will be left unused and stored as maximum specified. E.g. CHAR (25) – Smith and Van der Merwe stored as 25 characters
Varchar (L)
Variable-length data with a maximum of 8,000 characters. Will not leave unused spaces if max not used
Some more examples
V_code (4 digits product code)
V_name (35 characters product description)
V_contact (Name of contact person)
V_areacode (4 digit postal code)
V_phone (10 digit cellphone number)
V_country (Use abbreviation of 2 letters)
V_order (Use number from previous order)
V_code
4 digits, Integer
V_name
35 characters, Char(35)
V_contact
Name of contact person, Char(25)
V_areacode
4 digit postal code, Char/Integer(5)
V_phone
10 digit cellphone number, Char/Integer(25)
V_country
Use abbreviation of 2 letters, Char(2)
V_order
Use number from previous order, Char/Integer(1)
Creating Table Structures
1. Use one line per column (attribute) definition
2. Use spaces to line up attribute characteristics and constraints
NOT NULL constraint
Ensures that column does not accept nulls
UNIQUE constraint
Ensures that all values in column are unique
DEFAULT constraint
Assigns value to attribute when a new row is added to table
CHECK constraint
Validates data when attribute value is entered e.g min order
Primary key attributes contain both a NOT NULL and a UNIQUE specification
RDBMS will automatically enforce referential integrity for foreign keys