SQL

1
SQL

• SQL Server Overview
• SQL Overview
• Types of SQL
• Database Creation
• Tables Creation Manipulation
• Data Creation Manipulation
• Data Retrieving using SQL

2
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

3
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)

4
Data Definition Language

• Is used to create, alter and delete database
  objects
• The commands used are
• CREATE
• ALTER
• DROP

5
Data Manipulation Language

• Used to insert data into the database, modify and
  delete the data in the database
• Three DML statements
• INSERT
• UPDATE
• DELETE

6
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

7
Data Control Language

• The DCL consists of commands that control the
  users 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

8
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

9
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

10
Databases

• Collection of related data and manipulation of
  that data
• Can create database using SQL command
• CREATE DATABASE databasename

11
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

12
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) )

13
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

14
Data types

• char(n) - represents a fixed length of string of
  n characters where ngt0 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

15
Data Types

• float(n) - represents the floating point number
• int - represents a signed integer
• datetime - represents the date/time
• money - represents the currency

16
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

17
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)

18
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

19
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

20
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

21
Deleting of data from the table

• DELETE FROM table-name
• WHERE condition
• Depending on the condition the record will be
  deleted from the table

22
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

23
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

24

• All Columns
• SELECT FROM Table-name
• Qualified Retrieval
• SELECT FROM table-name
• WHERE condition
• can use all comparision operators (, ltgt, lt, gt,
  lt, gt) in the WHERE clause
• can contain multiple comparison with AND, OR, NOT
• Eliminating Duplicates
• SELECT DISTINCT column-name FROM table-name

25

• 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

26

• NOT BETWEEN
• SELECT FROM table-name
• WHERE column-name NOT BETWEEN val1 and
  val2
• LIKE
• SELECT FROM table-name
• WHERE column-name LIKE string
• LIKE _
• Escape Sequence
• SELECT FROM table-name
• WHERE column-name LIKE \_

27

• Computed Values
• SELECT column1, column2Expression FROM table-name
• WHERE condition
• NULLS
• SELECT FROM table-name
• WHERE column-name IS NULL
• ORDER BY
• SELECT FROM table-name
• ORDER BY column-name DESC