Transcript SQLIntroduction - Free Stuff Jamaica

SQL







SQL Server : Overview
SQL : Overview
Types of SQL
Database : Creation
Tables : Creation & Manipulation
Data : Creation & Manipulation
Data : Retrieving using SQL
SQL : Overview




Is the standard command set used to
communicate with the relational database
management systems
Can do Creating databases, Creating tables,
Querying and Manipulating data and
granting access to the users
English like structure
is by nature flexible
Types of SQL Commands







SQL statements are divided into the
following categories
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Query Language (DQL)
Data Control Language (DCL)
Data Administration Statements (DAS)
Transaction Control Statements (TCS)
Data Definition Language


Is used to create, alter and delete database
objects
The commands used are



CREATE
ALTER
DROP
Data Manipulation Language


Used to insert data into the database,
modify and delete the data in the database
Three DML statements



INSERT
UPDATE
DELETE
Data Query Language



This statement enables you to query one or
more tables to get the information
commonly used SQL statements
SQL has only one data query statement

SELECT
Data Control Language



The DCL consists of commands that
control the user’s access to the database
objects
The DCL is mainly related to the security
issues
The DCL commands are


GRANT - Giving access to the data
REVOKE - Denying access to the data
Data Administration Statements



DASs allow the user to perform audits and
analysis on operations within the database.
Used to analyze the performance of the
system
Data Administration commands are


START AUDIT
STOP AUDIT
Transaction Control Statements


TCSs are statements, which manage all the
changes made by the DML statements
Some of the TCSs are




COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION
Databases


Collection of related data and manipulation
of that data
Can create database using SQL command

CREATE DATABASE databasename
Tables




Are the basic building blocks in any
RDBMS
contains rows and columns of data
using DDL commands, we can create , alter
and delete tables
Creation of table includes the properties of
the columns
Create statement
CREATE TABLE table-name
(column-1-definition
[,column-2-definition] …..
[,column-n-definition]
[,primary key (column name)]
[.alternate key (column name)]
[,Foreign key (column name) ]);
Column definition

columnName data-type [NULL | NOT NULL
[WITH DEFAULT | UNIQUE]]

NULL - RDBMS insert a null in that column if
the user does not specify a value
NOT NULL - column should have a value
WITH DEFAULT - the RDBMS will substitute
the default values
UNIQUE - no duplicate values will be allowed



Data types




char(n) - represents a fixed length of string of ‘n’
characters where n>0 and is an integer
varchar(n) - varying length string whose max
length is ‘n’
bit(n) - represents a fixed length string of exactly
‘n’ bits
decimal(p, q) - represents a decimal number, ‘p’
digits and with decimal point ‘q’ digits from
right
Data Types




float(n) - represents the floating point
number
int - represents a signed integer
datetime - represents the date/time
money - represents the currency
2nd form of CREATE


CREATE TABLE new-table-name LIKE
table-name
when a table is created from an existing
table only the structure is copied; the
primary, alternate and foreign key
definitions is not inherited
Modifying a Table



An existing table can be modified by using
the ALTER TABLE statement
ALTER TABLE table-name
ADD column definition
ALTER TABLE table-name
Add CONSTRAINT constraint name
Primary key (column name)
Deleting a table




An existing table can be deleted at any time
by using the DROP TABLE statement
DROP TABLE table-name
specified table is deleted from the system
all the data for that table also will be
deleted
Inserting rows into a table

INSERT INTO table-name
[[column [,column]….]]
values [literal[,literal]…]];


a single row is inserted into the table, having
specified columns
INSERT INTO table-name
[[column [,column]….]]
subquery;

the subquery is evaluated first and a copy of the
result(usually multiple rows) is inserted into the table
Updating fields in a row




UPDATE table-name
SET column-name = expr
[WHERE condition]
table-name : table for the data to be updated
SET clause : the set of new values to be set
WHERE clause : condition will be checked
and particular record gets updated
Deleting of data from the table


DELETE FROM table-name
WHERE condition
Depending on the condition the record will
be deleted from the table
SELECT statements


SELECT - A keyword that tells the
database this command is a query. All
queries begin with this word followed by a
space
the select command simply instructs the
database to retrieve information from a
table
Different features applied to a simple
statement







All columns
Qualified Retrieval
Eliminating Duplicates
Using Boolean(IN, BETWEEN, LIKE)
Using Escape clause
Computed values
Involving nulls

All Columns


Qualified Retrieval




SELECT * FROM Table-name
SELECT * FROM table-name
WHERE condition
can use all comparision operators (=, <>, <, >, <=,
>=) in the WHERE clause
can contain multiple comparison with AND, OR,
NOT
Eliminating Duplicates

SELECT DISTINCT column-name FROM tablename


Using Boolean Operators
IN


SELECT * FROM table-name
WHERE column-name IN (val1, val2, val3…);
BETWEEN



SELECT * FROM table-name
WHERE column-name BETWEEN val1 and val2
between is an inclusive operator
values matching either of the boundary values
cause the predicate to be true

NOT BETWEEN


LIKE



SELECT * FROM table-name
WHERE column-name NOT BETWEEN val1 and
val2
SELECT * FROM table-name
WHERE column-name LIKE ‘string%’
LIKE ‘_%’
Escape Sequence

SELECT * FROM table-name
WHERE column-name LIKE ‘%\_%’

Computed Values


NULLS


SELECT column1, column2Expression
FROM table-name
WHERE condition
SELECT * FROM table-name
WHERE column-name IS NULL
ORDER BY

SELECT * FROM table-name
ORDER BY column-name DESC